You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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;
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
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
The text was updated successfully, but these errors were encountered: