[
https://issues.apache.org/jira/browse/CALCITE-4691?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17379602#comment-17379602
]
duan xiong edited comment on CALCITE-4691 at 7/13/21, 2:53 PM:
---------------------------------------------------------------
Looks like the calcite can't right handle:
the result of any comparison with NULL as Unknown. So NULL = NULL, NULL <> 10
and NULL < ‘Hello’ are all Unknown.
This query should return UNKNOWN but return *TRUE*.
{code:java}
select 'T' not in ('S', NULL);
{code}
was (Author: nobigo):
This query should return UNKNOWN but return *TRUE*.
{code:java}
select 'T' not in ('S', NULL);
{code}
> 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)
