You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@calcite.apache.org by "Julian Hyde (JIRA)" <ji...@apache.org> on 2014/08/18 21:07:20 UTC
[jira] [Comment Edited] (OPTIQ-373) NOT IN and NULL values
[ https://issues.apache.org/jira/browse/OPTIQ-373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14101098#comment-14101098 ]
Julian Hyde edited comment on OPTIQ-373 at 8/18/14 7:07 PM:
------------------------------------------------------------
The test has proved that we do have a problem. Here is another query, run on postgres, that indicates when the sub-query returns nulls and false:
{code}
with
t1(x) as (select * from (values (1),(2), (case when 1 = 1 then null else 3 end)) as t(x)),
t2(x) as (select * from (values (1),(case when 1 = 1 then null else 3 end)) as t(x))
select t1.x, (t1.x not in (select t2.x from t2)) from t1;
x | ?column?
-----+----------
1 | FALSE
2 | UNKNOWN
NULL | UNKNOWN
(3 rows)
{code}
was (Author: julianhyde):
The test has proved that we do have a problem. Here is another query, run on postgres, that indicates when the sub-query returns nulls and false:
{code}
with
t1(x) as (select * from (values (1),(2), (case when 1 = 1 then null else 3 end)) as t(x)),
t2(x) as (select * from (values (1),(case when 1 = 1 then null else 3 end)) as t(x))
select t1.x, (t1.x not in (select t2.x from t2)) from t1;
x | ?column?
------+----------
1 | FALSE
2 | 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)