Firebird Documentation Index → Firebird Null Guide → Conditional statements and loops |
If 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.
If you use Firebird 2 or higher, you can avoid all the pitfalls discussed here, simply
by using [NOT] DISTINCT instead of the
“=
” and “<>
”
operators!
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.
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
Equality tests, 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:
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.
Firebird 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
.
When 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.
To avoid any possible confusion, let us emphasise here that FOR
loops in Firebird PSQL have a totally different function than WHILE
loops, or for
loops in general programming
languages. Firebird FOR loops have the form:
for<select-statement>
into<var-list>
do<code-block>
and they will keep executing the code block until all the rows from the result set have
been retrieved, unless an exception occurs or a BREAK,
LEAVE or EXIT statement is encountered. Fetching a
NULL
, or even row after row filled with NULL
s, does
not terminate the loop!
Firebird Documentation Index → Firebird Null Guide → Conditional statements and loops |