[
https://issues.apache.org/jira/browse/CALCITE-2948?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=16800209#comment-16800209
]
Haisheng Yuan commented on CALCITE-2948:
----------------------------------------
It looks like the problem still exists.
{code:java}
private Sql checkSubQuery(String sql) {
final HepProgram program = new HepProgramBuilder()
.addRuleInstance(SubQueryRemoveRule.PROJECT)
.addRuleInstance(SubQueryRemoveRule.FILTER)
.addRuleInstance(SubQueryRemoveRule.JOIN)
.build();
return sql(sql).with(new HepPlanner(program)).expand(false);
}
@Test public void testWhereInCorrelated() {
final String sql = "select deptno\n"
+ "from EMP e\n"
+ "where deptno in (select deptno\n"
+ "from EMP where empno=e.empno+1)";
checkSubQuery(sql).withDecorrelation(false).withLateDecorrelation(true).check();
}
{code}
Plan before:
{code:java}
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[IN($7, {
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[=($0, +($cor0.EMPNO, 1))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
})], variablesSet=[[$cor0]])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
Plan Mid:
{code:java}
LogicalProject(DEPTNO=[$7])
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
LogicalFilter(condition=[=($7, $9)])
LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}])
LogicalProject(DEPTNO=[$7])
LogicalFilter(condition=[=($0, +($cor0.EMPNO, 1))])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
{code}
Plan After:
{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}
> SqlToRelConverter generates complicated logical plan for subquery
> -----------------------------------------------------------------
>
> Key: CALCITE-2948
> URL: https://issues.apache.org/jira/browse/CALCITE-2948
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Haisheng Yuan
> Priority: Major
>
> 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)
