Firebird Documentation Index → Firebird Null Guide → NULL in operations |
As many of us have found out to our chagrin, NULL
is contagious: use
it in a numerical, string or date/time operation, and the result will invariably be
NULL
. With boolean operators, the outcome depends on the type of operation
and the value of the other operand.
Please remember that in Firebird versions prior to 2.0 it is mostly illegal to use the
constant NULL
directly in operations or comparisons. Wherever you see
NULL
in the expressions below, read it as “a field, variable or other
expression that resolves to NULL
”. In Firebird 2 and above this
expression may also be a NULL
literal.
The operations in this list always return
NULL
:
1 + 2 + 3 +
NULL
5 *
NULL
- 7
'Home ' || 'sweet ' ||
NULL
MyField =
NULL
MyField <>
NULL
NULL
= NULL
If you have difficulty understanding why, remember that NULL
means
“unknown”. You can also look at the following table where per-case explanations
are provided. In the table we don't write NULL
in the expressions (as
said, this is often illegal); instead, we use two entities A and B that are both
NULL
. A and B may be fields, variables, or even composite subexpressions
– as long as they're NULL
, they'll all behave the same in the enclosing
expressions.
Table 1. Operations on null entities A and B
If A and B are NULL, then: | Is: | Because: |
---|---|---|
1 + 2 + 3 + A |
|
If A is unknown, then 6 + A is also unknown. |
5 * A - 7 |
|
If A is unknown, then 5 * A is also unknown. Subtract 7 and you end up with another unknown. |
'Home ' || 'sweet ' || A |
|
If A is unknown, 'Home sweet ' || A is unknown. |
MyField = A |
|
If A is unknown, you can't tell if MyField has the same value... |
MyField <> A |
|
...but you also can't tell if MyField has a different value! |
A = B |
|
With A and B unknown, it's impossible to know if they are equal. |
Here is the complete list of math and string operators that return
NULL
if at least one operand is NULL
:
+
,
-
,
*
,
and
/
!=
,
~=
,
and ^=
(synonyms of <>
)
<
,
<=
,
>
,
and
>=
!<
,
~<
,
and ^<
(low-precedence synonyms of
>=
)
!>
,
~>
,
and ^>
(low-precedence synonyms of
<=
)
||
[NOT] BETWEEN
[NOT] STARTING WITH
[NOT] LIKE
[NOT] CONTAINING
The explanations all follow the same pattern: if A is unknown, you can't tell if it's greater than B; if string S1 is unknown, you can't tell if it contains S2; etcetera.
Using LIKE with a NULL
escape character would
crash the server in Firebird versions up to and including 1.5. This bug was fixed in v. 1.5.1.
From that version onward, such a statement will yield an empty result set.
All the operators examined so far return NULL
if any operand is
NULL
. With boolean operators, things are a bit more complex:
not
NULL
= NULL
NULL
or false
=
NULL
NULL
or true
=
true
NULL
or NULL
=
NULL
NULL
and false
=
false
NULL
and true
=
NULL
NULL
and NULL
=
NULL
In reality, Firebird SQL doesn't have a boolean data type; nor are
true
and false
existing constants. In the leftmost
column of the explanatory table below, “true
” and
“false
” represent expressions (fields, variables,
composites...) that evaluate to true
/false
.
Table 2. Boolean operations on null entity A
If A is NULL , then:
|
Is: | Because: |
---|---|---|
not A |
|
If A is unknown, its inverse is also unknown. |
A or false |
|
“A or ” always has
the same value as A – which is unknown.
|
A or true |
|
“A or ” is always
true – A's value doesn't matter.
|
A or A |
|
“A or A ” always equals A – which is
NULL .
|
A and false |
|
“A and ” is always
false – A's value doesn't matter.
|
A and true |
|
“A and ” always has
the same value as A – which is unknown.
|
A and A |
|
“A and A ” always equals A – which is
NULL .
|
All these results are in accordance with boolean logic. The fact that you don't need to
know X's value to compute “X or true
” and “X and
false
” is also the basis of a feature found in various programming
languages: short-circuit boolean evaluation.
The above results can be generalised as follows for expressions with one type of binary
boolean operator (and
| or
) and any number of
operands:
If at least one operand is true
, the result is
true
.
Else, if at least one operand is NULL
, the result is
NULL
.
Else (i.e. if all operands are false
) the result is
false
.
If at least one operand is false
, the result is
false
.
Else, if at least one operand is NULL
, the result is
NULL
.
Else (i.e. if all operands are true
) the result is
true
.
Or, shorter:
TRUE
beats NULL
in a disjunction
(OR-operation);
FALSE
beats NULL
in a conjunction
(AND-operation);
In all other cases, NULL
wins.
If you have trouble remembering which constant rules which operation, look at the second letter: tRue prevails with oR — fAlse with And.
The short-circuit results obtained above may lead you to the following ideas:
0 times x
equals 0 for every x
. Hence, even if
x
's value is unknown, 0 * x
is 0. (Note: this only
holds if x
's datatype only contains numbers, not
NaN
or infinities.)
The empty string is ordered lexicographically before every other string. Therefore,
S >= ''
is true whatever the value of S.
Every value equals itself, whether it's unknown or not. So, although A =
B
justifiably returns NULL
if A and B are different
NULL
entities, A = A
should always return
true
, even if A is NULL
. The same goes for
A <= A
and A >= A
.
By analogous logic, A <> A
should always be
false
, as well as A < A
and A >
A
.
Every string contains itself, starts with
itself and is like itself. So, “S CONTAINING
S
”, “S STARTING WITH S
” and
“S LIKE S
” should always return
true
.
How is this reflected in Firebird SQL? Well, I'm sorry I have to inform you that despite
this compelling logic – and the analogy with the boolean results discussed above – the
following expressions all resolve to NULL
:
0 * NULL
NULL
>= ''
and
'' <=
NULL
A = A
,
A <= A
and
A >= A
A <> A
,
A <
A
and
A > A
S CONTAINING S
,
S STARTING WITH
S
and
S LIKE S
So much for consistency.
Firebird Documentation Index → Firebird Null Guide → NULL in operations |