[
https://issues.apache.org/jira/browse/CALCITE-4691?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17381268#comment-17381268
]
Julian Hyde commented on CALCITE-4691:
--------------------------------------
Calcite treats unknown as false only in the WHERE clause. Not always. Which is
why I suggested the SELECT test case.
There is a lot of work on making IN and NOT return UNKNOWN correctly. Please
find that work and build on it.
I don't think LEFT ANTI JOIN is the answer. It doesn't help with many cases,
e.g. SELECT clause, OR in WHERE.
> Incorrect handling of NOT IN and NULL values
> --------------------------------------------
>
> Key: CALCITE-4691
> URL: https://issues.apache.org/jira/browse/CALCITE-4691
> Project: Calcite
> Issue Type: Bug
> Reporter: Thomas Rebele
> Priority: Major
>
> It seems that Calcite does not handle WHERE foo NOT IN (values) correctly, if
> values contains NULLS. All DBMS on [SQL
> Fiddle|http://sqlfiddle.com/#!9/5e4c4d/1] produce an empty result in that
> case.
> {code:java}
> -- schema
> create table tab(v varchar(20));
> insert into tab(v) values('R');
> -- queries
> select 'R' from tab where 'R' in ('S'); -- empty
> select 'R' from tab where 'R' in ('S', NULL); -- empty
> select 'R' from tab where 'R' not in ('S'); -- 'R'
> select 'R' from tab where 'R' not in ('S', NULL); -- empty{code}
> However, a similar query with Calcite sqlline produces a different output:
> {code:java}
> select 'R' from (values ('R')) AS tab(v) where v in ('S'); -- empty
> select 'R' from (values ('R')) AS tab(v) where v in ('S', NULL); -- empty
> select 'R' from (values ('R')) AS tab(v) where v not in ('S'); -- 'R'
> select 'R' from (values ('R')) AS tab(v) where v not in ('S', NULL); --
> 'R'{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
