meta data for this page
A: NULL-related bugs in Firebird
Attention: both historic and current bugs are listed in the sections below. Always look if and when a bug has been fixed before assuming that it exists in your version of Firebird.
Bugs that crash the server
EXECUTE STATEMENT with NULL argument
EXECUTE STATEMENT with a NULL argument crashed Firebird 1.5 and 1.5.1 servers. Fixed in 1.5.2.
EXTRACT from NULL date
In 1.0.0, EXTRACT from a NULL date would crash the server. Fixed in 1.0.2.
FIRST and SKIP with NULL argument
FIRST and SKIP crash a Firebird 1.5.n or lower server if given a NULL argument. Fixed in 2.0.
LIKE with NULL escape
Using LIKE with a NULL escape character would crash the server. Fixed in 1.5.1.
Other bugs
NULLs in NOT NULL columns
NULLs can exist in NOT NULL columns in the following situations:
- If you make an existing column NOT NULL, any NULLs already present in the column will remain in that state.
Firebird allows these NULLs to stay, also backs them up, but refuses to restore them with gbak. See Adding a NOT NULL field and Making an existing column NOT NULL.
Illegal NULLs returned as 0, '', etc.
If a NOT NULL column contains NULLs (see previous bug), the server will still describe it as nonnullable to the client. Since most clients don't question this assurance from the server, they will present these NULLs as 0 (or equivalent) to the user. See False reporting of NULLs as zeroes.
Primary key with NULL entries
The following bug appeared in Firebird 1.5: if you had a table with some rows and you added a NOT NULL column (which automatically creates NULL entries in the existing rows – see above), you could make that column the primary key even though it had NULL entries. In 1.0 this didn't work because of the stricter rules for UNIQUE indices. Fixed in 2.0.
SUBSTRING results described as non-nullable
The engine describes SUBSTRING result columns as non-nullable in the following two cases:
- If the first argument is a string literal, as in SUBSTRING( 'Ootchie-coo' FROM 5 FOR 2 ).
- If the first argument is a NOT NULL column.
This is incorrect because even with a known string, substrings may be NULL, namely if the one of the other arguments is NULL. In versions 1.* this bug didn't bite: the FROM and FOR args had to be literal values, so they could never be NULL. But as from Firebird 2, any expression that resolves to the required data type is allowed. And although the engine correctly returns NULL whenever any argument is NULL, it describes the result column as non-nullable, so most clients show the result as an empty string.
This bug seems to be fixed in 2.1.
Gbak -n restoring NOT NULL
Gbak -n[o_validity] restored NOT NULL constraints in early Firebird versions. Fixed in 1.5.1.
IN, =ANY and =SOME with indexed subselect
Let A be the expression on the left-hand side and S the result set of the subselect. In versions prior to 2.0, IN, =ANY and =SOME return false instead of NULL if an index is active on the subselect column and:
- either A is NULL and S doesn't contain any NULLs;
- or A is not NULL, A is not found in S, and S contains at least one NULL.
See the warnings in the IN and ANY sections. Workaround: use <> ALL instead. Fixed in 2.0.
ALL with indexed subselect
With every operator except “<>”, ALL may return wrong results if an index is active on the subselect column. This can happen with our without NULLs involved. See the ALL bug warning. Fixed in 2.0.
SELECT DISTINCT with wrong NULLS FIRST | LAST ordering
Firebird 2.0 has the following bug: if a SELECT DISTINCT is combined with an [ASC] NULLS LAST or DESC NULLS FIRST ordering, and the ordering field(s) form(s) the beginning (but not the whole) of the select list, every field in the ORDER BY clause that is followed by a field with a different (or no) ordering gets the NULLs placed at the default relative location, ignoring the NULLS XXX directive.
Fixed in 2.0.1 and 2.1.
UDFs returning values when they should return NULL
This should definitely be considered a bug. If an angle is unknown, don't tell me that its cosine is 1! Although the history of these functions is known and we can understand why they behave like they do (see User-Defined Functions), it's still wrong. Incorrect results are returned and this should not happen. Most of the math functions in ib_udf, as well as some others, have this bug.
UDFs returning NULL when they should return a value
This is the complement of the previous bug. LPAD for instance returns NULL if you want to pad an empty string with 10 dots. This function and others are fixed in 2.0, with the annotation that you must explicitly declare them with the NULL keyword or they'll show the old – buggy – behaviour. LTRIM? and RTRIM? trim empty strings to NULL in Firebird 1.0.n. This is fixed in 1.5 at the expense of returning '' when trimming a NULL string, and only fully fixed in 2.0 (if declared with the NULL keyword).
SINGULAR inconsistent with NULL results
NOT SINGULAR sometimes returns NULL where SINGULAR returns true or false. Fixed in 2.0.
SINGULAR may wrongly return NULL, in an inconsistent but reproducible manner. Fixed in 2.1.
See the section on SINGULAR.