Appendix 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

`NULL`s in `NOT NULL` columns

NULLs can exist in NOT NULL columns in the following situations:

If you add a NOT NULL column to a populated table, the fields in the newly added column will all be NULL.
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 `NULL`s 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).