FirebirdSQL logo

IN, = ANY and = SOME with indexed subselect

Let A be the expression on the left-hand side and S the result set of the subselect.In versions prior to 2.0, “IN”, “= ANY” and “= SOME” return false instead of NULL if an index is active on the subselect column and:

  • either A is NULL and S doesn’t contain any NULLs;

  • or A is not NULL, A is not found in S, and S contains at least one NULL.

See the warnings in the IN and ANY sections.Workaround: use “<> ALL” instead.Fixed in 2.0.

ALL with indexed subselect

With every operator except ‘<>’, ALL may return wrong results if an index is active on the subselect column.This can happen with our without NULLs involved.See the ALL bug warning.Fixed in 2.0.

SELECT DISTINCT with wrong NULLS FIRST|LAST ordering

Firebird 2.0 has the following bug: if a SELECT DISTINCT is combined with an [ASC] NULLS LAST or DESC NULLS FIRST ordering, and the ordering field(s) form(s) the beginning (but not the whole) of the select list, every field in the ORDER BY clause that is followed by a field with a different (or no) ordering gets the NULLs placed at the default relative location, ignoring the NULLS XXX directive.Fixed in 2.0.1 and 2.1.

UDFs returning values when they should return NULL

This should definitely be considered a bug.If an angle is unknown, don’t tell me that its cosine is 1!Although the history of these functions is known and we can understand why they behave like they do (see [nullguide-udfs]), it’s still wrong.Incorrect results are returned and this should not happen.Most of the math functions in ib_udf, as well as some others, have this bug.