[jira] [Created] (DERBY-6024) Incorrect handling of NULL in IN lists

["Knut Anders Hatlen (JIRA)" <ji...@apache.org>]

Knut Anders Hatlen created DERBY-6024:
-----------------------------------------

             Summary: Incorrect handling of NULL in IN lists
                 Key: DERBY-6024
                 URL: https://issues.apache.org/jira/browse/DERBY-6024
             Project: Derby
          Issue Type: Bug
          Components: SQL
    Affects Versions: 10.9.1.0
            Reporter: Knut Anders Hatlen


I believe the following query should return null instead of false, as the IN list contains an unknown value (null), so one cannot say definitely that it doesn't contain 1:

ij> values 1 in (cast(null as int), 2);
1    
-----
false

1 row selected

Per SQL:2003, part 2, 8.4 <in predicate>, syntax rules 2 and 5, the query is equivalent to these two queries:

ij> values 1 in (values cast(null as int), 2);
1    
-----
NULL 

1 row selected
ij> values 1 = any (values cast(null as int), 2);
1    
-----
NULL 

1 row selected

They both return NULL, which I believe is the correct result for the first query too.

--
This message is automatically generated by JIRA.
If you think it was sent incorrectly, please contact your JIRA administrators
For more information on JIRA, see: http://www.atlassian.com/software/jira