Considering NULL - Firebird

NULL can be quite a sticking point for folks who have previously worked with desktop databases that conveniently swallow NULLs by storing them as “zero values”: empty strings, 0 for numerics, false for logicals, and so on. In SQL, any data item in a nullable column (that is, a column that does not have the NOT NULL constraint) will be stored with a NULL token if no value is ever provided for it in a DML statement or through a column default.

All column definitions in Firebird default to being nullable. Unless your database has been consciously designed to prevent NULLs from being stored, your expressions need to be prepared to encounter them.

NULL in Expressions

NULL is not a value, so it cannot be “equal to” any value. For example, a predicate such as WHERE (COL1 = NULL) will return an error because the equivalence operator is not valid for NULLs. NULL is a state and the correct predicator for a NULL test is IS NULL. The corrected predicate for the preceding failed test would be

WHERE (COL1 IS NULL) You can also test for NOT NULL: WHERE (COL1 IS NOT NULL)

Two NULLs are not equal to each other, either. When constructing expressions, be mindful of the cases when a predicate might resolve to

WHERE <NULL result> = <NULL result>

because false is always the result when two NULLs are compared.

An expression like

WHERE COL1 > NULL

will fail because an arithmetic operator is not valid for NULL.

NULL in Calculations

In an expression where a column identifier “stands in” for the current value of a data item, a NULL operand in a calculation will produce NULL as the result of the calculation. For example, the following:

UPDATE TABLEA SET COL4 = COL4 + COL5;

will set COL4 to NULL if COL5 is NULL.

In aggregate expressions using operators like SUM( )and AVG( ) and COUNT (<specific_column_name>), rows containing NULL in the targeted column are ignored for the aggregation. AVG() forms the numerator by aggregating the non-null values and the denominator by counting the rows containing non-null values.

Gotchas with True and False

Semantically, if a predicate returns “unknown,” it is neither false nor true. However, in SQL, assertions resolve as either “true” or “false”—an assertion that does not evaluate as “true” shakes out as “false.” The “IF” condition implicit in search predicates can trip you up when the NOT predicator is used on an embedded assertion:

NOT <condition evaluating to false> evaluates to TRUE

Whereas

NOT <condition evaluating to null> evaluates to NULL

To take an example of where our assumptions can bite, consider this:

WHERE

NOT (COLUMNA = COLUMNB)

If both COLUMNA and COLUMNB have values and they are not equal, the inner predicate evaluates to false. The assertion NOT(FALSE) returns true, the flip side of false.

However, if either of the columns is NULL, the inner predicate evaluates to NULL, standing for the semantic meaning “unknown” (“not proven,” “unknowable”). The assertion that is finally tested is NOT(NULL) and the result returns NULL. Take note also that NOT(NULL) is not the same as IS NOT NULL—a pure binary predicate that never returns “unknown.”

The lesson in this is to be careful with SQL logic and always to test your expressions hard. Cover the null conditions and, if you can do so, avoid NOT assertions altogether in nested predicates.

NULL and External Functions (UDFs)

NULL cannot be passed as either input to or output from functions in many external function libraries, because they follow the InterBase convention of passing arguments by reference or by value. Most of the UDF libraries available use the InterBase convention.

The Firebird engine is capable of passing arguments to UDFs by descriptor, a mechanism that standardizes arguments on Firebird data types, making it possible to pass NULL as an argument to host code, although not to receive NULL as a return value. The functions in the library fbudf (in the /UDF directory of your server installation) use descriptors.

Setting a Value to NULL

A data item can be made NULL only in a column that is not subject to the NOT NULL constraint. Refer to the section “The NOT NULL Constraint”

In an UPDATE statement the assignment symbol is “=”:

UPDATE FOO SET COL3 = NULL WHERE COL2 = 4;

In an INSERT statement, pass the keyword NULL in place of a value:

INSERT INTO FOO (COL1, COL2, COL3) VALUES (1, 2, NULL);

Another way to cause NULL to be stored by an INSERT statement is to omit the nullable column from the input list. For example, the following statement has the same effect as the previous one, as long as no default is defined for the column COL3:

INSERT INTO FOO (COL1, COL2) VALUES (1, 2);

In PSQL (stored procedure language), use the “=” symbol as the assignment operator when assigning NULL to a variable and use IS [NOT ] NULL in the predicate of an IF test:

... DECLARE VARIABLE foobar integer; .. IF (COL1 IS NOT NULL) THEN FOOBAR = NULL; ...

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics