Thomas Rebele created CALCITE-4691:
--------------------------------------
Summary: 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
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 (1)) AS tab(v) where 'R' in ('S'); -- empty
select 'R' from (values (1)) AS tab(v) where 'R' in ('S', NULL); -- empty
select 'R' from (values (1)) AS tab(v) where 'R' not in ('S'); -- 'R'
select 'R' from (values (1)) AS tab(v) where 'R' not in ('S', NULL); --
'R'{code}
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
