[CALCITE-1055] SubQueryRemoveRule should create Correlate, not Join, for correlated sub-queries
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/cecef9d3 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/cecef9d3 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/cecef9d3 Branch: refs/heads/master Commit: cecef9d3209cbd092a6e9b578bc063a52007b99f Parents: 898fdfc Author: maryannxue <[email protected]> Authored: Thu Jan 14 13:46:07 2016 -0800 Committer: Julian Hyde <[email protected]> Committed: Thu Jan 14 14:05:17 2016 -0800 ---------------------------------------------------------------------- .../calcite/rel/rules/SubQueryRemoveRule.java | 4 +- .../apache/calcite/test/RelOptRulesTest.java | 8 ++++ .../org/apache/calcite/test/RelOptRulesTest.xml | 39 ++++++++++++++++++-- 3 files changed, 46 insertions(+), 5 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/cecef9d3/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java index 56b362d..6eec2fc 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java @@ -156,7 +156,7 @@ public abstract class SubQueryRemoveRule extends RelOptRule { builder.aggregateCall(SqlStdOperatorTable.SINGLE_VALUE, false, null, null, builder.field(0))); } - builder.join(JoinRelType.LEFT); + builder.join(JoinRelType.LEFT, builder.literal(true), variablesSet); return field(builder, inputCount, offset); case IN: @@ -247,7 +247,7 @@ public abstract class SubQueryRemoveRule extends RelOptRule { builder.aggregateCall(SqlStdOperatorTable.COUNT, false, null, "ck", builder.fields())); builder.as("ct"); - builder.join(JoinRelType.INNER); + builder.join(JoinRelType.INNER, builder.literal(true), variablesSet); offset += 2; builder.push(e.rel); break; http://git-wip-us.apache.org/repos/asf/calcite/blob/cecef9d3/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java index 6300579..f59b9e7 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -2216,6 +2216,14 @@ public class RelOptRulesTest extends RelOptTestBase { checkSubQuery(sql).check(); } + @Test public void testExpandWhereComparisonCorrelated() throws Exception { + final String sql = "select empno\n" + + "from sales.emp as e\n" + + "where sal = (\n" + + " select max(sal) from sales.emp e2 where e2.empno = e.empno)"; + checkSubQuery(sql).check(); + } + } // End RelOptRulesTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/cecef9d3/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml ---------------------------------------------------------------------- diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml index e45b82c..45ea111 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -1064,6 +1064,39 @@ LogicalValues(tuples=[[]]) ]]> </Resource> </TestCase> + <TestCase name="testExpandWhereComparisonCorrelated"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp as e +where sal = ( + select max(sal) from sales.emp e2 where e2.empno = e.empno)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalFilter(condition=[=($5, $SCALAR_QUERY({ +LogicalAggregate(group=[{}], EXPR$0=[MAX($0)]) + LogicalProject(SAL=[$5]) + LogicalFilter(condition=[=($0, $cor0.EMPNO)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}))], variablesSet=[[$cor0]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[=($5, $9)]) + LogicalCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{0}]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{}], EXPR$0=[MAX($0)]) + LogicalProject(SAL=[$5]) + LogicalFilter(condition=[=($0, $cor0.EMPNO)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testPushSumConstantThroughUnion"> <Resource name="sql"> <![CDATA[select ename, sum(u) from @@ -4944,9 +4977,9 @@ LogicalFilter(condition=[<($0, 20)]) })]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> - </Resource> - <Resource name="planAfter"> - <![CDATA[ + </Resource> + <Resource name="planAfter"> + <![CDATA[ LogicalProject(EMPNO=[$0]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) LogicalJoin(condition=[true], joinType=[inner])
