[
https://issues.apache.org/jira/browse/OPTIQ-373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14101125#comment-14101125
]
Julian Hyde edited comment on OPTIQ-373 at 8/18/14 7:28 PM:
------------------------------------------------------------
And another:
{code}
create table e(deptno int, ename varchar(5));
insert into e values (10, 'Alice');
insert into e values (null, 'Bob');
create table d(deptno int, dname varchar(10));
insert into d values (10, 'Sales');
insert into d values (20, 'Marketing');
insert into d values (null, 'Null');
select * from d where deptno not in (select deptno from e);
deptno | dname
--------+-------
(0 rows)
select deptno,
deptno not in (select deptno from e)
from d;
deptno | ?column?
--------+----------
10 | FALSE
20 | UNKNOWN
NULL | UNKNOWN
(3 rows)
{code}
was (Author: julianhyde):
And another:
{code}
create table e(deptno int, ename varchar(5));
insert into e values (10, 'Alice');
insert into e values (null, 'Bob');
create table d(deptno int, dname varchar(10));
insert into d values (10, 'Sales');
insert into d values (20, 'Marketing');
insert into d values (null, 'Null');
select * from d where deptno not in (select deptno from e);
deptno | dname
--------+-------
(0 rows)
select deptno,
deptno not in (select deptno from e where deptno is not null)
from d;
deptno | ?column?
--------+----------
10 | FALSE
20 | TRUE
NULL | UNKNOWN
(3 rows)
select deptno,
deptno not in (select deptno from e)
from d;
deptno | ?column?
--------+----------
10 | FALSE
20 | UNKNOWN
NULL | UNKNOWN
(3 rows)
{code}
> NOT IN and NULL values
> ----------------------
>
> Key: OPTIQ-373
> URL: https://issues.apache.org/jira/browse/OPTIQ-373
> Project: Optiq
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: Julian Hyde
> Attachments: OPTIQ-373.1.patch
>
>
> Write a test to check that NOT IN evaluates to unknown, and therefore returns
> all rows, if the sub-query returns a null.
> Fix if necessary. Also test composite keys.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
