FirebirdSQL logo

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 [nullguide-add-not-null-field] and [nullguide-make-column-not-null].

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 [nullguide-pred-singular].

Illegal NULLs returned as 0, '', etc.

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 [nullguide-nulls-reported-as-zeroes].