Conditional statements and loops

`IF` statements

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 NULLs creep into your IF statements.

Tip: 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 "Equal2" 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.

`CASE` statements

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.

`WHILE` loops

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.

`FOR` loops

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 NULLs, does not terminate the loop!