FirebirdSQL logo

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.

Predicates

Predicates are statements about objects that return a boolean result: true, false or unknown (= NULL).In computer code you typically find predicates in places where a yes/no type of decision has to be taken.For Firebird SQL, that means in WHERE, HAVING, CHECK, CASE WHEN, IF and WHILE clauses.

Comparisons such as “x > y” also return boolean results, but they are generally not called predicates, although this is mainly a matter of form.An expression like Greater( x, y ) that does exactly the same would immediately qualify as a predicate.(Mathematicians like predicates to have a name — such as “Greater” or just plain “G” — and a pair of parentheses to hold the arguments.)

Firebird supports the following SQL predicates: IN, ANY, SOME, ALL, EXISTS and SINGULAR.

Note

It is also perfectly defensible to call “IS [NOT] NULL” and “IS [NOT] DISTINCT FROM” predicates, despite the absence of parentheses.But, predicates or not, they have already been introduced and won’t be discussed in this section.