You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2017/11/04 03:22:59 UTC
calcite git commit: [CALCITE-2028] SubQueryRemoveRule should create
Join, not Correlate, for un-correlated sub-queries (Liao Xintao)
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 <ch...@taobao.com>
Authored: Fri Nov 3 15:02:13 2017 +0800
Committer: Julian Hyde <jh...@apache.org>
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