[jira] [Updated] (CALCITE-3170) ANTI join on conditions push down generates wrong plan
[
https://issues.apache.org/jira/browse/CALCITE-3170?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
ASF GitHub Bot updated CALCITE-3170:
Labels: pull-request-available (was: )
> 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
>
> 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)
[jira] [Updated] (CALCITE-3170) ANTI join on conditions push down generates wrong plan
[
https://issues.apache.org/jira/browse/CALCITE-3170?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Danny Chan updated CALCITE-3170:
Summary: ANTI join on conditions push down generates wrong plan (was: join
condition of ANTI join can not be pushed down)
> 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
>
> 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)
