[
https://issues.apache.org/jira/browse/OPTIQ-373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14104620#comment-14104620
]
Julian Hyde commented on OPTIQ-373:
-----------------------------------
Here is a rewrite that seems to produce the same values as "IN" in all cases.
{code}
# original query using IN
select e.deptno,
e.deptno IN (select deptno from v)
from e;
# rewritten to use just (outer) joins and 2-valued logic
select e.deptno,
case
when ct.c = 0 then false
when dt.i is not null then true
when e.deptno is null or ct.ck < ct.c then null
else false
end
from e
cross join (select count(*) as c, count(deptno) as ck from v) as ct
left join (select distinct deptno, true as i from v) as dt on e.deptno =
dt.deptno;
{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)
