Issue Details (XML | Word | Printable)

Key: CORE-6322
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Adriano dos Santos Fernandes
Reporter: Mark Rotteveel
Votes: 0
Watchers: 2
Operations

If you were logged in you would be able to see more operations.
Firebird Core

IN predicate and quantified comparison predicates behave incorrectly with NULL

Created: 30/May/20 06:47 AM   Updated: 09/Jun/20 10:27 AM
Component/s: Engine
Affects Version/s: 3.0.5, 4.0 Beta 2
Fix Version/s: None

Issue Links:
Relate
 

QA Status: No test


 Description  « Hide
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

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
There are no comments yet on this issue.