FirebirdSQL logo

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 [nullguide-udfs]), 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.

NULL support in Firebird SQL

Only a few language elements are purposely designed to give an unambiguous result with NULL (unambiguous in the sense that some specific action is taken and/or a non-NULL result is returned).They are discussed in the following paragraphs.

Disallowing NULL

In a column or domain definition, you can specify that only non-NULL values may be entered by adding NOT NULL to the definition:

create table MyTable ( i int not null )
create domain DTown as varchar( 32 ) not null
alter table Sales add TransTime timestamp not null

Adding a NOT NULL column to an existing table that already contains records requires special care.This operation will be discussed in detail in the section [nullguide-alter-pop-tables].