Submitted by: @mrotteveel

Is related to CORE6327

The IN predicate and quantified comparison predicates behave incorrectly with NULL. According to SQL:2016-2, the result of `null in (non-empty list or query)`, `null = any (non-empty query)`, `null = some (non-empty query)` and `(null = any (non-empty query))` should be `null` (see SQL:2016-2, 8.4 <in predicate> and 8.9 <quantified comparison predicate>).

However the actual behaviour is a combination of NULL and FALSE:

For example in ISQL:

SET LIST;

select
(null in (select 'a' from RDB$DATABASE)) "IN",
(null in (select 'a' from RDB$DATABASE)) is null "IN_ISNULL",
(null in (select 'a' from RDB$DATABASE)) is false "IN_ISFALSE",
not (null in (select 'a' from RDB$DATABASE)) "NOT_IN",
not (not (null in (select 'a' from RDB$DATABASE))) "NOT_NOT_IN",
(null = any (select 'a' from RDB$DATABASE)) "ANY",
(null = any (select 'a' from RDB$DATABASE)) is null "ANY_ISNULL",
(null = any (select 'a' from RDB$DATABASE)) is false "ANY_ISFALSE",
not (null = any (select 'a' from RDB$DATABASE)) "NOT_ANY",
not (not (null = any (select 'a' from RDB$DATABASE))) "NOT_NOT_ANY",
(null = all (select 'a' from RDB$DATABASE)) "ALL",
(null = all (select 'a' from RDB$DATABASE)) is null "ALL_ISNULL",
(null = all (select 'a' from RDB$DATABASE)) is false "ALL_ISFALSE",
not (null = all (select 'a' from RDB$DATABASE)) "NOT_ALL",
not (not (null = all (select 'a' from RDB$DATABASE))) "NOT_NOT_ALL"
from RDB$DATABASE;

results in:
IN <false>
IN_ISNULL <false>
IN_ISFALSE <true>
NOT_IN <false>
NOT_NOT_IN <false>
ANY <false>
ANY_ISNULL <false>
ANY_ISFALSE <true>
NOT_ANY <false>
NOT_NOT_ANY <false>
ALL <false>
ALL_ISNULL <false>
ALL_ISFALSE <true>
NOT_ALL <false>
NOT_NOT_ALL <false>

The *_ISNULL columns should be TRUE, and the *_ISFALSE should be FALSE, all other columns should be NULL.

On firebird-devel, Vlad suggested that possibly the result is NULL, but not marked as nullable. However with XSQLDA_DISPLAY ON, all columns are marked as nullable.

This effect is also visible for:

'a' in (select 'b' from RDB$DATABASE union all select null from RDB$DATABASE) => false (should be null)
not ('a' in (select 'b' from RDB$DATABASE union all select null from RDB$DATABASE)) => false (should be null)

Interestingly enough, IN with an actual list behaves correctly:

null in ('a') => null
not(null in ('a')) => null
'a' in ('b', null) => null
not('a' in ('b', null)) => null

Commits: 596d397