Repository: calcite Updated Branches: refs/heads/master 16f1fcf25 -> d7c4627c7
[CALCITE-2028] SubQueryRemoveRule should create Join, not Correlate, for un-correlated sub-queries (Liao Xintao) Add a couple of tests in sub-query.iq; add a test case (disabled) for [CALCITE-2032] in misc.iq. Close apache/calcite#556 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/d7c4627c Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/d7c4627c Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/d7c4627c Branch: refs/heads/master Commit: d7c4627c78d39627c39452dcc8ee53518632dac9 Parents: 16f1fcf Author: chiyou <[email protected]> Authored: Fri Nov 3 15:02:13 2017 +0800 Committer: Julian Hyde <[email protected]> Committed: Fri Nov 3 16:45:23 2017 -0700 ---------------------------------------------------------------------- .../calcite/rel/rules/SubQueryRemoveRule.java | 4 +- .../apache/calcite/test/RelOptRulesTest.java | 13 +++++ .../org/apache/calcite/test/RelOptRulesTest.xml | 59 ++++++++++++++++++++ core/src/test/resources/sql/misc.iq | 15 +++++ core/src/test/resources/sql/sub-query.iq | 40 +++++++++++++ 5 files changed, 130 insertions(+), 1 deletion(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/d7c4627c/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 d1847c0..1961999 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 @@ -110,7 +110,9 @@ public abstract class SubQueryRemoveRule extends RelOptRule { final RelOptUtil.Logic logic = LogicVisitor.find(RelOptUtil.Logic.TRUE, ImmutableList.of(c), e); - final RexNode target = apply(e, filter.getVariablesSet(), logic, + final Set<CorrelationId> variablesSet = + RelOptUtil.getVariablesUsed(e.rel); + final RexNode target = apply(e, variablesSet, logic, builder, 1, builder.peek().getRowType().getFieldCount()); final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target); c = c.accept(shuttle); http://git-wip-us.apache.org/repos/asf/calcite/blob/d7c4627c/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 9b988ee..c6bc035 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -3342,6 +3342,19 @@ public class RelOptRulesTest extends RelOptTestBase { } /** Test case for + * <a href="https://issues.apache.org/jira/browse/CALCITE-2028";>[CALCITE-2028] + * Un-correlated IN sub-query should be converted into a Join, + * rather than a Correlate without correlation variables </a>. */ + @Test public void testDecorrelateUncorrelatedInAndCorrelatedExists() throws Exception { + final String sql = "select * from sales.emp\n" + + "WHERE job in (\n" + + " select job from emp ee where ee.sal=34)" + + "AND EXISTS (\n" + + " select * from emp e where emp.deptno = e.deptno)\n"; + checkSubQuery(sql).withLateDecorrelation(true).check(); + } + + /** Test case for * <a href="https://issues.apache.org/jira/browse/CALCITE-1537";>[CALCITE-1537] * Unnecessary project expression in multi-sub-query plan</a>. */ @Test public void testDecorrelateTwoIn() throws Exception { http://git-wip-us.apache.org/repos/asf/calcite/blob/d7c4627c/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 cccedb0..b8e1131 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -487,6 +487,65 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ ]]> </Resource> </TestCase> + <TestCase name="testDecorrelateUncorrelatedInAndCorrelatedExists"> + <Resource name="sql"> + <![CDATA["select * from sales.emp +WHERE job in ( + select job from emp ee where ee.sal=34) +AND EXISTS ( + select * from emp e where emp.deptno = e.deptno)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[AND(IN($2, { +LogicalProject(JOB=[$2]) + LogicalFilter(condition=[=($5, 34)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), EXISTS({ +LogicalFilter(condition=[=($cor0.DEPTNO, $7)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}))], variablesSet=[[$cor0]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planMid"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{7}]) + LogicalJoin(condition=[=($2, $9)], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0}]) + LogicalProject(JOB=[$2]) + LogicalFilter(condition=[=($5, 34)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0}]) + LogicalProject(i=[true]) + LogicalFilter(condition=[=($cor0.DEPTNO, $7)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalJoin(condition=[=($7, $10)], joinType=[inner]) + LogicalJoin(condition=[=($2, $9)], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0}]) + LogicalProject(JOB=[$0]) + LogicalProject(JOB=[$2]) + LogicalFilter(condition=[=($5, 34)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$0], $f1=[true]) + LogicalAggregate(group=[{0}]) + LogicalProject(DEPTNO=[$1], i=[$0]) + LogicalProject(i=[true], DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testDecorrelateTwoIn"> <Resource name="sql"> <![CDATA[select sal http://git-wip-us.apache.org/repos/asf/calcite/blob/d7c4627c/core/src/test/resources/sql/misc.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq index de9e2dc..9a79bb7 100644 --- a/core/src/test/resources/sql/misc.iq +++ b/core/src/test/resources/sql/misc.iq @@ -1984,4 +1984,19 @@ select multiset[1,null,2,2-1] as m from (values (1)); !ok +!if (false) { +# [CALCITE-2032] Error when implicitly converting character literal to date literal +select * +from "scott".emp +WHERE hiredate = '1980-12-17'; ++-------+-------+-------+------+------------+--------+------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+-------+-------+------+------------+--------+------+--------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | ++-------+-------+-------+------+------------+--------+------+--------+ +(1 row) + +!ok +!} + # End misc.iq http://git-wip-us.apache.org/repos/asf/calcite/blob/d7c4627c/core/src/test/resources/sql/sub-query.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq index e31319e..f7b5a58 100644 --- a/core/src/test/resources/sql/sub-query.iq +++ b/core/src/test/resources/sql/sub-query.iq @@ -645,4 +645,44 @@ from ( !ok +# [CALCITE-2028] Un-correlated IN sub-query should be converted into a Join +# rather than a Correlate without correlation variables +SELECT * +FROM "scott".emp +WHERE job in (select job from "scott".emp ee where ee.hiredate = DATE '1980-12-17') +AND EXISTS (select * from "scott".emp e where emp.deptno = e.deptno); ++-------+--------+-------+------+------------+---------+------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+--------+-------+------+------------+---------+------+--------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | +| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | +| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | +| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | ++-------+--------+-------+------+------------+---------+------+--------+ +(4 rows) + +!ok + +# Variant of [CALCITE-2028] above +SELECT * +FROM "scott".emp +WHERE job in (select job from "scott".emp ee where ee.hiredate = DATE '1980-12-17') +OR EXISTS (select * from "scott".emp e where emp.deptno = e.deptno + 20); ++-------+--------+----------+------+------------+---------+---------+--------+ +| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | ++-------+--------+----------+------+------------+---------+---------+--------+ +| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 | +| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 | +| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 | +| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 | +| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 | +| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 | +| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 | +| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 | +| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 | ++-------+--------+----------+------+------------+---------+---------+--------+ +(9 rows) + +!ok + # End sub-query.iq
