[
https://issues.apache.org/jira/browse/CALCITE-4691?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17380395#comment-17380395
]
duan xiong edited comment on CALCITE-4691 at 7/14/21, 8:41 AM:
---------------------------------------------------------------
After some test in other datasource.
{code:java}
SELECT * FROM orders WHERE order_id NOT IN(SELECT order_id from
order_items){code}
is equals to
```
{code:java}
select * from orders left anti join order_items on ((orders.order_id =
order_items.order_id) or isnull(orders.order_id = order_items.order_id)){code}
```
So we need to make some improvemt to make NOT IN ---->LEFT ANTI JOIN WITH
CONDITION
NOW: Calcite treats Unknown As False.
{code:java}
RexSimplify.simplifyUnknownAsFalse(condition){code}
was (Author: nobigo):
After some test in other datasource.
{code:java}
SELECT * FROM orders WHERE order_id NOT IN(SELECT order_id from
order_items){code}
is equals to
```
{code:java}
select * from orders left anti join order_items on ((orders.order_id =
order_items.order_id) or isnull(orders.order_id = order_items.order_id)){code}
```
So we need to make some improvemt to make NOT IN ---->LEFT ANTI JOIN WITH
CONDITION
> 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)
