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)