FirebirdSQL logo

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.

FIRST, SKIP and ROWS

The following two directives crash a Firebird 1.5.n or lower server if given a NULL argument.In Firebird 2, they treat NULL as the value 0:

  • FIRST

  • SKIP

This new Firebird 2 directive returns an empty set if any argument is NULL:

  • ROWS

In new code, use ROWS, not FIRST and SKIP.