FirebirdSQL logo

Result values

If the subselect returns an empty set, ALL returns true and ANY|SOME return false, even if the left-hand side expression is NULL.This follows from the definitions and the rules of formal logic.(Math-heads will already have noticed that ALL is equivalent to the universal (“A”) quantifier and ANY|SOME to the existential (“E”) quantifier.)

For non-empty sets, you can write out “A <op> {ANY|SOME} (<subselect>)” as

A <op> E1 or A <op> E2 or ... or A <op> En

with <op> the operator used and E1, E2 etc. the items returned by the subquery.

Likewise, “A <op> ALL (<subselect>)” is the same as

A <op> E1 and A <op> E2 and ... and A <op> En

This should look familiar.The first writeout is equal to that of the IN predicate, except that the operator may now be something other than ‘=’.The second is different but has the same general form.We can now work out how nullness of A and/or nullness of subselect results affect the outcome of ANY|SOME and ALL.This is done in the same way as earlier with IN, so instead of including all the steps here we will just present the result tables.Again, read the questions in the left column from top to bottom.As soon as you answer a question with “Yes”, read the result from the second column and you’re done.

Table 1. Results for “A <op> ANY|SOME (<subselect>)

Conditions

Result

ANY|SOME

Does the subselect return an empty set?

false

Else, is A NULL?

NULL

Else, does at least one comparison return true?

true

Else, does at least one comparison return NULL?

NULL

Else (i.e. all comparisons return false)

false

If you think these results look a lot like what we saw with IN(), you’re right: with the ‘=’ operator, ANY is the same as IN.In the same way, “<> ALL” is equivalent to NOT IN.

Warning
Bug alert (revisited)

In versions before 2.0, “= ANY” suffers from the same bug as IN.Under the “right” circumstances, this can lead to wrong results with expressions of the type “NOT A = ANY( …​ )”.

On the bright side, “<> ALL” is not affected and will always return the right result.

Table 2. Results for “A <op> ALL (<subselect>)

Conditions

Result

ALL

Does the subselect return an empty set?

true

Else, is A NULL?

NULL

Else, does at least one comparison return false?

false

Else, does at least one comparison return NULL?

NULL

Else (i.e. all comparisons return true)

true

Warning
ALL bug

Although “<> ALL” always works as it should, ALL should nevertheless be considered broken in all pre-2.0 versions of Firebird: with every operator other than “<>”, wrong results may be returned if an index is active on the subselect — with or without NULLs around.

Note

Strictly speaking, the second question in both tables (“is A NULL?”) is redundant and can be dropped.If A is NULL, all the comparisons return NULL, so that situation will be caught a little later.And while we’re at it, we could drop the first question too: the “empty set” situation is just a special case of the final “else”.The whole thing then once again boils down to “true beats NULL beats false” in disjunctions (ANY|SOME) and “false beats NULL beats true” in conjunctions (ALL).

The reason we included those questions is convenience: you can see if a set is empty at a glance, and it’s also easier to check if the left-hand side expression is NULL than to evaluate each and every comparison result.But do feel free to skip them, or to skip just the second.Do not, however, skip the first question and start with the second: this will lead to a wrong conclusion if the set is empty!

EXISTS and SINGULAR

The EXISTS and SINGULAR predicates return information about a subquery, usually a correlated subquery.You can use them in WHERE, HAVING, CHECK, CASE, IF and WHILE clauses (the latter two are only available in PSQL, Firebird’s stored procedure and trigger language).

EXISTS

EXISTS tells you whether a subquery returns at least one row of data.Suppose you want a list of farmers who are also landowners.You could get one like this:

SELECT Farmer FROM Farms WHERE EXISTS
  (SELECT * FROM Landowners
   WHERE Landowners.Name = Farms.Farmer)

This query returns the names of all farmers who also figure in the Landowners table.The EXISTS predicate returns true if the result set of the subselect contains at least one row.If it is empty, EXISTS returns false. EXISTS never returns NULL, because a result set always either has rows, or hasn’t.Of course the subselect’s search condition may evolve to NULL for certain rows, but that doesn’t cause any uncertainty: such a row won’t be included in the subresult set.

Note

In reality, the subselect doesn’t return a result set at all.The engine simply steps through the Landowners records one by one and applies the search condition.If it evaluates to true, EXISTS returns true immediately and the remaining records aren’t checked.If it evaluates to false or NULL, the search continues.If all the records have been searched and there hasn’t been a single true result, EXISTS returns false.

NOT EXISTS always returns the opposite of EXISTS: false or true, never NULL.NOT EXISTS returns false immediately if it gets a true result on the subquery’s search condition.Before returning true it must step through the entire set.

SINGULAR

SINGULAR is an InterBase/Firebird extension to the SQL standard.It is often described as returning true if exactly one row in the subquery meets the search condition.By analogy with EXISTS this would make you expect that SINGULAR too will only ever return true or false.After all, a result set has either exactly 1 row or a different number of rows.Unfortunately, all versions of Firebird up to and including 2.0 have a bug that causes NULL results in a number of cases.The behaviour is pretty inconsistent, but at the same time fully reproducible.For instance, on a column A containing (1, NULL, 1), a SINGULAR test with subselect “A=1” returns NULL, but the same test on a column with (1, 1, NULL) returns false.Notice that only the insertion order is different here!

To make matters worse, all versions prior to 2.0 sometimes return NULL for NOT SINGULAR where false or true is returned for SINGULAR.In 2.0, this at least doesn’t happen anymore: it’s either false vs. true or twice NULL.

The code has been fixed for Firebird 2.1; from that version onward SINGULAR will return:

  • false if the search condition is never true (this includes the empty-set case);

  • true if the search condition is true for exactly 1 row;

  • false if the search condition is true for more than 1 row.

Whether the other rows yield false, NULL or a combination thereof, is irrelevant.

NOT SINGULAR will always return the opposite of SINGULAR (as is already the case in 2.0).

In the meantime, if there’s any chance that the search condition may evolve to NULL for one or more rows, you should always add an IS NOT NULL condition to your [NOT] SINGULAR clauses, e.g.like this:

... SINGULAR( SELECT * from MyTable
              WHERE MyField > 38
              AND MyField IS NOT NULL )