[
https://issues.apache.org/jira/browse/CALCITE-6737?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Krisztian Kasa reassigned CALCITE-6737:
---------------------------------------
Assignee: Krisztian Kasa
> LoptOptimizeJoinRule can not identify selfjoin on unique join keys
> ------------------------------------------------------------------
>
> Key: CALCITE-6737
> URL: https://issues.apache.org/jira/browse/CALCITE-6737
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Krisztian Kasa
> Assignee: Krisztian Kasa
> Priority: Major
>
> Let's see the following query
> {code}
> select e.empno from emp e
> inner join dept d on d.deptno = e.deptno
> inner join emp e2 on e.empno = e2.empno;
> {code}
> The query contains two inner joins: one joining the {{emp}} table twice
> (self-joins) and one joining the {{dept}} table.
> Based on the implementation of {{LoptOptimizeJoinRule}} the final join order
> is also affected by self-joins:
> https://github.com/apache/calcite/blob/dbf4ff87ae34d7cb9e08037c15bd6fddfcaaef24/core/src/main/java/org/apache/calcite/rel/rules/LoptOptimizeJoinRule.java#L702-L705
> So the join order of above query should look like:
> {code}
> LogicalJoin(condition=[=($18, $7)], joinType=[inner])
> LogicalJoin(condition=[=($9, $0)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> However, identifying self-join is broken after CALCITE-4251 so we end up with
> the following join order
> {code}
> LogicalJoin(condition=[=($9, $0)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalJoin(condition=[=($9, $7)], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
> {code}
> I bumped into this while I was investigating query plan changes caused by
> upgrading Calcite to 1.33 in Hive. (Hive currently uses Calcite 1.25)
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
