On 29-05-2020 21:43, Mark Rotteveel wrote:
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>).
It would have been more correct to say it should be Unknown, but as null
and unknown are equivalent according to the SQL standard, that shouldn't
matter.
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?
Similar problems apply for `(null = any (..))`, this should produce TRUE
when the subquery is empty, but NULL when non-empty.
However, the plot thickens:
select 1 from RDB$DATABASE where (null = any (select 'a' from
RDB$DATABASE)) is null;
does not return a row, so here it doesn't behave as NULL/UNKNOWN, and
select 1 from RDB$DATABASE where (null = any (select 'a' from
RDB$DATABASE)) is false;
returns a row, so here it behaves as FALSE, while it does behave as NULL in
select 1 from RDB$DATABASE where not (null = any (select 'a' from
RDB$DATABASE));
Mark
--
Mark Rotteveel
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
