FirebirdSQL logo

Boolean operations

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 version 2.5 and earlier, 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 1. Boolean operations on null entity A
If A is NULL, then: Is: Because:

not A

NULL

If A is unknown, its inverse is also unknown.

A or false

NULL

A or false” always has the same value as A — which is unknown.

A or true

true

A or true” is always true — A's value doesn’t matter.

A or A

NULL

A or A” always equals A — which is NULL.

A and false

false

A and false” is always false — A's value doesn’t matter.

A and true

NULL

A and true” always has the same value as A — which is unknown.

A and A

NULL

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:

Disjunctions (“A or B or C or D or …​”)

  1. If at least one operand is true, the result is true.

  2. Else, if at least one operand is NULL, the result is NULL.

  3. Else (i.e. if all operands are false) the result is false.

Conjunctions (“A and B and C and D and …​”)

  1. If at least one operand is false, the result is false.

  2. Else, if at least one operand is NULL, the result is NULL.

  3. 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.

More logic (or not)

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.

Internal functions

The following built-in functions return NULL if at least one argument is NULL:

  • CAST()

  • EXTRACT()

  • GEN_ID()

  • SUBSTRING()

  • UPPER()

  • LOWER()

  • BIT_LENGTH()

  • CHAR[ACTER]_LENGTH()

  • OCTET_LENGTH()

  • TRIM()

Note
Notes
  • In 1.0.0, EXTRACT from a NULL date would crash the server.Fixed in 1.0.2.

  • If the first argument to GEN_ID is a valid generator name and the second argument is NULL, the named generator keeps its current value.

  • In versions up to and including 2.0, SUBSTRING results are sometimes returned as “false emptystrings”.These strings are in fact NULL, but are described by the server as non-nullable.Therefore, most clients show them as empty strings.See the bugs list for a detailed description.