Internal functions and directives
Conditional statements and loops
Changing the nullability of a domain
Testing for NULL and equality in practice
Firebird Null Guide: NULL behaviour and pitfalls in Firebird SQL
IF
statementsIf the test expression of an IF
statement resolves to NULL
, the THEN
clause is skipped and the ELSE
clause — if present — executed.In other words, NULL
and false
have the same effect in this context.So in situations where you would logically expect false
but NULL
is returned, no harm will be done.However, we’ve already seen examples of NULL
being returned where you would expect true
, and that does affect the flow of the code!
Below are some examples of the seemingly paradoxical (but perfectly correct) results you can get if NULL
s creep into your IF
statements.
Tip
|
If you use Firebird 2 or higher, you can avoid all the pitfalls discussed here, simply by using |
Equals (‘=
’)
if (a = b) then
MyVariable = 'Equal';
else
MyVariable = 'Not equal';
If a
and b
are both NULL
, MyVariable
will yet be “Not equal” after executing this code.The reason is that the expression “a = b
” yields NULL
if at least one of them is NULL
.With a NULL
test expression, the THEN
block is skipped and the ELSE
block executed.
Not equals (‘<>
’)
if (a <> b) then
MyVariable = 'Not equal';
else
MyVariable = 'Equal';
Here, MyVariable
will be “Equal” if a
is NULL
and b
isn’t, or vice versa.The explanation is analogous to that of the previous example.
So how should you set up equality tests that do give the logical result under all circumstances, even with NULL
operands?In Firebird 2 you can use DISTINCT
, as already shown (see Testing DISTINCTness). With earlier versions, you’ll have to write some more code.This is discussed in the section [nullguide-testing-equality], later on in this guide.For now, just remember that you have to be very careful with IF
conditions that may resolve to NULL
.
Another aspect you shouldn’t forget is the following: a NULL
test expression may behave like false
in an IF
condition, but it doesn’t have the value false
.It’s still NULL
, and that means that its inverse will also be NULL
— not “true
”.As a consequence, inverting the test expression and swapping the THEN
and ELSE
blocks may change the behaviour of the IF
statement.In binary logic, where only true
and false
can occur, such a thing could never happen.
To illustrate this, let’s refactor the last example:
Not not equals (“not (.. <> ..)
”)
if (not (a <> b)) then
MyVariable = 'Equal';
else
MyVariable = 'Not equal';
In the original version, if one operand was NULL
and the other wasn’t (so they were intuitively unequal), the result was “Equal”.Here, it’s “Not equal”.The explanation: one operand is NULL
, therefore “a <> b
” is NULL
, therefore “not(a <> b)
” is NULL
, therefore ELSE
is executed.While this result is correct where the original had it wrong, there’s no reason to rejoice: in the refactored version, the result is also “Not equal” if both operands are NULL
— something that the original version “got right”.
Of course, as long as no operand in the test expression can ever be NULL
, you can happily formulate your IF
statements like above.Also, refactoring by inverting the test expression and swapping the THEN
and ELSE
blocks will always preserve the functionality, regardless of the complexity of the expressions — as long as they aren’t NULL
.What’s especially treacherous is when the operands are almost always non-NULL
, so in the vast majority of cases the results will be correct.In such a situation those rare NULL
cases may go unnoticed for a long time, silently corrupting your data.
CASE
expressionFirebird introduced the CASE
construct in version 1.5, with two syntactic variants.The first one is called the simple syntax:
case <expression>
when <exp1> then <result1>
when <exp2> then <result2>
...
[else <defaultresult>]
end
This one works more or less like a Pascal case
or a C switch
construct: <expression>
is compared to <exp1>
, <exp2>
etc., until a match is found, in which case the corresponding result is returned.If there is no match and there is an ELSE
clause, <defaultresult>
is returned.If there is no match and no ELSE
clause, NULL
is returned.
It is important to know that the comparisons are done with the ‘=
’ operator, so a null <expression>
will not match a null <expN>
.If <expression>
is NULL
, the only way to get a non-NULL
result is via the ELSE
clause.
It is OK to specify NULL
(or any other valid NULL
expression) as a result.
The second, or searched syntax is:
case
when <condition1> then <result1>
when <condition2> then <result2>
...
[else <defaultresult>]
end
Here, the <conditionN>
s are tests that give a ternary boolean result: true
, false
, or NULL
.Once again, only true
is good enough, so a condition like “A = 3
” — or even “A = null
” — is not satisfied when A
is NULL
.Remember though that “IS [NOT] NULL
” never returns NULL
: if A
is NULL
, the condition “A is null
” returns true
and the corresponding <resultN>
will be returned.In Firebird 2+ you can also use “IS [NOT] DISTINCT FROM
” in your conditions — this operator too will never return NULL
.
WHILE
loopsWhen evaluating the condition of a WHILE
loop, NULL
has the same effect as in an IF
statement: if the condition resolves to NULL
, the loop is not (re)entered — just as if it were false
.Again, watch out with inversion using NOT
: a condition like
while ( Counter > 12 ) do
will skip the loop block if Counter
is NULL
, which is probably what you want, but:
while ( not Counter > 12 ) do
will also skip if Counter
is NULL
.Maybe this is also exactly what you want — just be aware that these seemingly complementary tests both exclude NULL
counters.