Appendix A: NULL-related bugs in Firebird

<< Summary | Firebird Null Guide | B: Document history >>

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 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 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 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.


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.

back to top of page
<< Summary | Firebird Null Guide | B: Document history >>