IN predicate and quantified comparison predicates behave incorrectly with NULL
------------------------------------------------------------------------------
Key: CORE-6322
URL: http://tracker.firebirdsql.org/browse/CORE-6322
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 4.0 Beta 2, 3.0.5
Reporter: Mark Rotteveel
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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
