[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16828874#comment-16828874
]
Haisheng Yuan commented on CALCITE-2948:
----------------------------------------
SubqueryRemoveRule transform correlated subquery to Correlate. RelDecorrelator
will decorrelate Correlate into Join. They are doing different task. The
Correlate plan generated by SubqueryRemoveRule is complex and wrong. The first
problem we need to solve is not decorrelating, but generate a simple Correlate
plan, with the plan shape I mentioned earlier.
The plan is wrong:
{code:java}
LogicalProject(DEPTNO=[$0])
LogicalJoin(condition=[=($0, $3)], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalFilter(condition=[=($1, $0)])
LogicalAggregate(group=[{0, 1}])
LogicalProject(DEPTNO=[$7], $f9=[-($0, 1)])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
Because the filter should comparing column from 2 different tables, but you are
comparing 2 columns from the same table.
In addition, if you transform a=$b+1 to $b=a-1, if there is an index on column
a of inner relation, we will never get a chance to utilize the index.
> Complicated logical plan generated for in subquery with non-equi condition
> --------------------------------------------------------------------------
>
> Key: CALCITE-2948
> URL: https://issues.apache.org/jira/browse/CALCITE-2948
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Haisheng Yuan
> Assignee: Danny Chan
> Priority: Major
> Labels: pull-request-available, sub-query
> Time Spent: 1h 50m
> Remaining Estimate: 0h
>
> Repro:
> Add the following test to SqlToRelConverterTest.java.
> {code:java}
> @Test public void testSubQueryIN() {
> final String sql = "select deptno\n"
> + "from EMP e\n"
> + "where deptno in (select deptno\n"
> + "from EMP where empno=e.empno+1)";
> sql(sql).ok();
> }
> {code}
> Plan:
> {code:java}
> LogicalProject(DEPTNO=[$7])
> LogicalJoin(condition=[AND(=($0, $10), =($7, $9))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalAggregate(group=[{0, 1}])
> LogicalProject(DEPTNO=[$7], EMPNO0=[$9])
> LogicalJoin(condition=[=($0, +($9, 1))], joinType=[inner])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> LogicalProject(EMPNO=[$0])
> LogicalTableScan(table=[[CATALOG, SALES, EMP]])
> {code}
> One join would suffice.
--
This message was sent by Atlassian JIRA
(v7.6.3#76005)
