[jira] [Resolved] (CALCITE-3170) ANTI join on conditions push down generates wrong plan

["Danny Chan (JIRA)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/CALCITE-3170?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Danny Chan resolved CALCITE-3170.
---------------------------------
       Resolution: Fixed
    Fix Version/s: 1.21.0

Fixed in [cb364ce|https://github.com/apache/calcite/commit/cb364ce8109f92c80c17bb37d1cb1a03e6222bbe] !

> ANTI join on conditions push down generates wrong plan
> ------------------------------------------------------
>
>                 Key: CALCITE-3170
>                 URL: https://issues.apache.org/jira/browse/CALCITE-3170
>             Project: Calcite
>          Issue Type: Bug
>          Components: core
>    Affects Versions: 1.19.0
>            Reporter: godfrey he
>            Assignee: Danny Chan
>            Priority: Major
>              Labels: pull-request-available
>             Fix For: 1.21.0
>
>          Time Spent: 2h 20m
>  Remaining Estimate: 0h
>
> create table A (a int, b int);
> create table B (c int, d int);
> insert into A values (1, 1);
> insert into A values (2, 2);
> sql: 
> {code:sql}
> select * from A where A.a not in (select B.c from B where A.b > 1);
> {code}
> the equivalent logical tree:
> {code}
> LogicalProject(a=[$0], b=[$1])
> +- LogicalProject(a=[$0], b=[$1])
>    +- LogicalJoin(condition=[AND(OR(=($0, $3), IS NULL($0), IS NULL($3)), $2)], joinType=[anti])
>       :- LogicalProject(a=[$0], b=[$1], $f2=[>($1, 1)])
>       :  +- LogicalTableScan(table=[[A]])
>       +- LogicalProject(c=[$0])
>          +- LogicalFilter(condition=[true])
>             +- LogicalTableScan(table=[[B]])
> {code}
> the correct result is: (1, 1), (2, 2)
> while if the predicate ($2 in join condition) is pushed into left side, the result is (2, 2) which is incorrect.



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)