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

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.

If a NOT NULL column contains NULLs (see previous bug), the server will still describe it as non-nullable 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.

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.

The engine describes SUBSTRING result columns as non-nullable in the following two cases:

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[o_validity] restored NOT NULL constraints in early Firebird versions. Fixed in 1.5.1.

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:

See the warnings in the IN and ANY sections. Workaround: use “<> ALL” instead. Fixed in 2.0.

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.

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.

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

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.