I was just testing some claims in the Firebird Language Reference
against Firebird 3 and 4, and ran into the following oddity:
The result of `null in (non-empty list or query)` and `null = any
(non-empty query)` should be `null` (see SQL:2016-2, 8.4 <in predicate>
and 8.9 <quantified comparison predicate>).
This is the case for
select (null in ('a')) from RDB$DATABASE
However, it is not the case for:
select (null in (select 'a' from RDB$DATABASE)) from RDB$DATABASE
select (null = any (select 'a' from RDB$DATABASE)) from RDB$DATABASE
These both, incorrectly, return false
However, when applied in the the WHERE clause, they all correctly behave
as NULL:
select 1 from RDB$DATABASE where not (null in ('a'));
select 1 from RDB$DATABASE where not (null in (select 'a' from
RDB$DATABASE));
select 1 from RDB$DATABASE where not (null = any (select 'a' from
RDB$DATABASE));
This on the basis that `not (null)` => unknown, so no result, where as
`not (false)` => true, so would have produced a result.
Any idea what might be happening here?
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
