[jira] [Created] (CALCITE-3170) join condition of ANTI join can not be pushed down

["godfrey he (JIRA)" <ji...@apache.org>]

godfrey he created CALCITE-3170:
-----------------------------------

             Summary: join condition of ANTI join can not be pushed down
                 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


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)