calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [1/2] calcite git commit: [CALCITE-1494] More efficient plan for correlated sub-queries, omitting value-generating scans where possible
Date Fri, 24 Feb 2017 20:16:36 GMT
Repository: calcite
Updated Branches:
  refs/heads/master 052f85459 -> 73e437fe5


http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java
----------------------------------------------------------------------
diff --git a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java
index 96a7eb0..8efec08 100644
--- a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java
+++ b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java
@@ -36,11 +36,7 @@ public class EnumerableCorrelateTest {
             + "  EnumerableSemiJoin(condition=[=($1, $3)], joinType=[inner])\n"
             + "    EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])\n"
             + "      EnumerableTableScan(table=[[s, emps]])\n"
-            + "    EnumerableJoin(condition=[=($0, $1)], joinType=[inner])\n"
-            + "      EnumerableAggregate(group=[{1}])\n"
-            + "        EnumerableTableScan(table=[[s, emps]])\n"
-            + "      EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])\n"
-            + "        EnumerableTableScan(table=[[s, depts]])")
+            + "    EnumerableTableScan(table=[[s, depts]])")
         .returnsUnordered(
             "empid=100; name=Bill",
             "empid=110; name=Theodore",

http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/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 6c03478..cc6b417 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -217,15 +217,11 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
   LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
     LogicalJoin(condition=[=($7, $9)], joinType=[inner])
       LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-      LogicalProject(DEPTNO0=[$0], $f1=[true])
+      LogicalProject(DEPTNO=[$0], $f1=[true])
         LogicalAggregate(group=[{0}])
-          LogicalProject(DEPTNO0=[$1], i=[$0])
-            LogicalProject(i=[true], DEPTNO0=[$9])
-              LogicalJoin(condition=[=($9, $7)], joinType=[inner])
-                LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                LogicalAggregate(group=[{0}])
-                  LogicalProject(DEPTNO=[$7])
-                    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+          LogicalProject(DEPTNO=[$1], i=[$0])
+            LogicalProject(i=[true], DEPTNO=[$7])
+              LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
         <Resource name="planMid">
@@ -288,25 +284,17 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
       LogicalJoin(condition=[=($2, $11)], joinType=[left])
         LogicalJoin(condition=[=($7, $9)], joinType=[inner])
           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-          LogicalProject(DEPTNO0=[$0], $f1=[true])
+          LogicalProject(DEPTNO=[$0], $f1=[true])
             LogicalAggregate(group=[{0}])
-              LogicalProject(DEPTNO0=[$1], i=[$0])
-                LogicalProject(i=[true], DEPTNO0=[$9])
-                  LogicalJoin(condition=[=($9, $7)], joinType=[inner])
-                    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                    LogicalAggregate(group=[{0}])
-                      LogicalProject(DEPTNO=[$7])
-                        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-        LogicalProject(JOB0=[$0], $f1=[true])
+              LogicalProject(DEPTNO=[$1], i=[$0])
+                LogicalProject(i=[true], DEPTNO=[$7])
+                  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(JOB=[$0], $f1=[true])
           LogicalAggregate(group=[{0}])
-            LogicalProject(JOB0=[$1], i=[$0])
-              LogicalProject(i=[true], JOB0=[$9])
-                LogicalJoin(condition=[=($2, $9)], joinType=[inner])
-                  LogicalFilter(condition=[=($5, 34)])
-                    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                  LogicalAggregate(group=[{0}])
-                    LogicalProject(JOB=[$2])
-                      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+            LogicalProject(JOB=[$1], i=[$0])
+              LogicalProject(i=[true], JOB=[$2])
+                LogicalFilter(condition=[=($5, 34)])
+                  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
@@ -358,18 +346,10 @@ LogicalProject(SAL=[$5])
     LogicalJoin(condition=[AND(=($1, $12), =($0, $11))], joinType=[inner])
       LogicalJoin(condition=[AND(=($2, $10), =($0, $9))], joinType=[inner])
         LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-        LogicalProject(DEPTNO=[$0], JOB=[$2])
-          LogicalJoin(condition=[=($2, $1)], joinType=[inner])
-            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-            LogicalAggregate(group=[{0}])
-              LogicalProject(JOB=[$2])
-                LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-      LogicalProject(EMPNO=[$0], ENAME0=[$9])
-        LogicalJoin(condition=[=($9, $1)], joinType=[inner])
-          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-          LogicalAggregate(group=[{0}])
-            LogicalProject(ENAME=[$1])
-              LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(DEPTNO=[$0], NAME=[$1])
+          LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+      LogicalProject(EMPNO=[$0], ENAME=[$1])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
@@ -4118,26 +4098,18 @@ LogicalProject(DEPTNO=[$0], NAME=[$1])
   LogicalJoin(condition=[=($0, $2)], joinType=[inner])
     LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
     LogicalAggregate(group=[{0}])
-      LogicalProject(DEPTNO0=[$2], $f0=[true])
-        LogicalJoin(condition=[=($1, $2)], joinType=[inner])
-          LogicalProject(SAL=[$5], DEPTNO=[$7])
-            LogicalFilter(condition=[>($5, 100)])
-              LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-          LogicalProject(DEPTNO=[$0])
-            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+      LogicalProject(DEPTNO9=[$7], $f0=[true])
+        LogicalFilter(condition=[>($5, 100)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
         <Resource name="planAfter">
             <![CDATA[
 SemiJoin(condition=[=($0, $2)], joinType=[inner])
   LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-  LogicalProject(DEPTNO0=[$2], $f0=[true])
-    LogicalJoin(condition=[=($1, $2)], joinType=[inner])
-      LogicalProject(SAL=[$5], DEPTNO=[$7])
-        LogicalFilter(condition=[>($5, 100)])
-          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-      LogicalProject(DEPTNO=[$0])
-        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+  LogicalProject(DEPTNO9=[$7], $f0=[true])
+    LogicalFilter(condition=[>($5, 100)])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
@@ -4154,12 +4126,9 @@ LogicalProject(DEPTNO=[$0])
   LogicalJoin(condition=[=($0, $2)], joinType=[inner])
     SemiJoin(condition=[=($0, $2)], joinType=[inner])
       LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-      LogicalProject(DEPTNO0=[$9], $f0=[true])
-        LogicalJoin(condition=[=($7, $9)], joinType=[inner])
-          LogicalFilter(condition=[>($5, 100)])
-            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-          LogicalProject(DEPTNO=[$0])
-            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+      LogicalProject(DEPTNO9=[$7], $f0=[true])
+        LogicalFilter(condition=[>($5, 100)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
     LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
 ]]>
         </Resource>
@@ -4170,13 +4139,10 @@ LogicalProject(DEPTNO=[$0])
     SemiJoin(condition=[=($0, $1)], joinType=[inner])
       LogicalProject(DEPTNO=[$0])
         LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-      LogicalProject(DEPTNO0=[$2])
-        LogicalJoin(condition=[=($1, $2)], joinType=[inner])
-          LogicalFilter(condition=[>($0, 100)])
-            LogicalProject(SAL=[$5], DEPTNO=[$7])
-              LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-          LogicalProject(DEPTNO=[$0])
-            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+      LogicalProject(DEPTNO9=[$1])
+        LogicalFilter(condition=[>($0, 100)])
+          LogicalProject(SAL=[$5], DEPTNO=[$7])
+            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
     LogicalProject(ACCTNO=[$0])
       LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]])
 ]]>
@@ -6627,6 +6593,45 @@ LogicalProject(EMPNO=[$0])
     </TestCase>
     <TestCase name="testWhereInCorrelated">
         <Resource name="sql">
+            <![CDATA[select sal from emp where empno IN (
+  select deptno from dept where emp.job = dept.name)]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(SAL=[$5])
+  LogicalFilter(condition=[IN($0, {
+LogicalProject(DEPTNO=[$0])
+  LogicalFilter(condition=[=($cor0.JOB, $1)])
+    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})], variablesSet=[[$cor0]])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planMid">
+            <![CDATA[
+LogicalProject(SAL=[$5])
+  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+    LogicalFilter(condition=[=($0, $9)])
+      LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{2}])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(DEPTNO=[$0])
+          LogicalFilter(condition=[=($cor0.JOB, $1)])
+            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(SAL=[$5])
+  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+    LogicalJoin(condition=[AND(=($2, $10), =($0, $9))], joinType=[inner])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+      LogicalProject(DEPTNO=[$0], NAME=[$1])
+        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testWhereInJoinCorrelated">
+        <Resource name="sql">
             <![CDATA[select empno from emp as e
 join dept as d using (deptno)
 where e.sal in (
@@ -6759,6 +6764,104 @@ LogicalProject(JOB=[$0], EMPNO=[10], SAL=[$1], S=[$2])
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testWhereExpressionInCorrelated">
+        <Resource name="sql">
+            <![CDATA[select ename from (
+  select ename, deptno, sal + 1 as salPlus from emp) as e
+where deptno in (
+  select deptno from emp where sal + 1 = e.salPlus)]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(ENAME=[$0])
+  LogicalFilter(condition=[IN($1, {
+LogicalProject(DEPTNO=[$7])
+  LogicalFilter(condition=[=(+($5, 1), $cor0.SALPLUS)])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})], variablesSet=[[$cor0]])
+    LogicalProject(ENAME=[$1], DEPTNO=[$7], SALPLUS=[+($5, 1)])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planMid">
+            <![CDATA[
+LogicalProject(ENAME=[$0])
+  LogicalProject(ENAME=[$0], DEPTNO=[$1], SALPLUS=[$2])
+    LogicalFilter(condition=[=($1, $3)])
+      LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{2}])
+        LogicalProject(ENAME=[$1], DEPTNO=[$7], SALPLUS=[+($5, 1)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalAggregate(group=[{0}])
+          LogicalProject(DEPTNO=[$7])
+            LogicalFilter(condition=[=(+($5, 1), $cor0.SALPLUS)])
+              LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(ENAME=[$0])
+  LogicalProject(ENAME=[$0], DEPTNO=[$1], SALPLUS=[$2])
+    LogicalJoin(condition=[AND(=($2, $4), =($1, $3))], joinType=[inner])
+      LogicalProject(ENAME=[$1], DEPTNO=[$7], SALPLUS=[+($5, 1)])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+      LogicalAggregate(group=[{0, 1}])
+        LogicalProject(DEPTNO=[$0], $f9=[$1])
+          LogicalProject(DEPTNO=[$7], $f9=[$9])
+            LogicalFilter(condition=[=(+($5, 1), $9)])
+              LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($5, 1)])
+                LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testWhereExpressionInCorrelated2">
+        <Resource name="sql">
+            <![CDATA[select name from (
+  select name, deptno, deptno - 10 as deptnoMinus from dept) as d
+where deptno in (
+  select deptno from emp where sal + 1 = d.deptnoMinus)]]>
+        </Resource>
+        <Resource name="planBefore">
+            <![CDATA[
+LogicalProject(NAME=[$0])
+  LogicalFilter(condition=[IN($1, {
+LogicalProject(DEPTNO=[$7])
+  LogicalFilter(condition=[=(+($5, 1), $cor0.DEPTNOMINUS)])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})], variablesSet=[[$cor0]])
+    LogicalProject(NAME=[$1], DEPTNO=[$0], DEPTNOMINUS=[-($0, 10)])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+        <Resource name="planMid">
+            <![CDATA[
+LogicalProject(NAME=[$0])
+  LogicalProject(NAME=[$0], DEPTNO=[$1], DEPTNOMINUS=[$2])
+    LogicalFilter(condition=[=($1, $3)])
+      LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{2}])
+        LogicalProject(NAME=[$1], DEPTNO=[$0], DEPTNOMINUS=[-($0, 10)])
+          LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+        LogicalAggregate(group=[{0}])
+          LogicalProject(DEPTNO=[$7])
+            LogicalFilter(condition=[=(+($5, 1), $cor0.DEPTNOMINUS)])
+              LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+        <Resource name="planAfter">
+            <![CDATA[
+LogicalProject(NAME=[$0])
+  LogicalProject(NAME=[$0], DEPTNO=[$1], DEPTNOMINUS=[$2])
+    LogicalJoin(condition=[AND(=($2, $4), =($1, $3))], joinType=[inner])
+      LogicalProject(NAME=[$1], DEPTNO=[$0], DEPTNOMINUS=[-($0, 10)])
+        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+      LogicalAggregate(group=[{0, 1}])
+        LogicalProject(DEPTNO=[$0], $f9=[$1])
+          LogicalProject(DEPTNO=[$7], $f9=[$9])
+            LogicalFilter(condition=[=(+($5, 1), $9)])
+              LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($5, 1)])
+                LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
     <TestCase name="testWhereNotInCorrelated">
         <Resource name="sql">
             <![CDATA[select sal from emp
@@ -6782,27 +6885,16 @@ LogicalProject(DEPTNO=[$0])
 LogicalProject(SAL=[$5])
   LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
     LogicalFilter(condition=[NOT(CASE(=($10, 0), false, IS NOT NULL($13), true, <($11, $10), true, false))])
-      LogicalJoin(condition=[AND(=($0, $15), =($2, $14))], joinType=[left])
+      LogicalJoin(condition=[AND(=($0, $12), =($2, $14))], joinType=[left])
         LogicalJoin(condition=[=($2, $9)], joinType=[left])
           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
           LogicalAggregate(group=[{0}], c=[COUNT()], ck=[COUNT($1)])
-            LogicalProject(JOB=[$1], DEPTNO=[$0])
-              LogicalProject(DEPTNO=[$0], JOB=[$2])
-                LogicalJoin(condition=[=($2, $1)], joinType=[inner])
-                  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-                  LogicalAggregate(group=[{0}])
-                    LogicalProject(JOB=[$2])
-                      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-        LogicalJoin(condition=[=($3, $0)], joinType=[inner])
-          LogicalProject(DEPTNO=[$0], i=[true], JOB=[$1])
-            LogicalProject(DEPTNO=[$0], JOB=[$2])
-              LogicalJoin(condition=[=($2, $1)], joinType=[inner])
+            LogicalProject(NAME=[$1], DEPTNO=[$0])
+              LogicalProject(DEPTNO=[$0], NAME=[$1])
                 LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-                LogicalAggregate(group=[{0}])
-                  LogicalProject(JOB=[$2])
-                    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-          LogicalProject(EMPNO=[$0])
-            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(DEPTNO=[$0], i=[true], NAME=[$1])
+          LogicalProject(DEPTNO=[$0], NAME=[$1])
+            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
         <Resource name="planMid">
@@ -6866,4 +6958,66 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testWhereNotInCorrelated2">
+        <Resource name="sql">
+            <![CDATA[select * from emp e1
+  where e1.empno NOT IN
+   (select empno from (select ename, empno, sal as r from emp) e2
+    where r > 2 and e1.ename= e2.ename)]]>
+        </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=[NOT(IN($0, {
+LogicalProject(EMPNO=[$1])
+  LogicalFilter(condition=[AND(>($2, 2), =($cor0.ENAME, $0))])
+    LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5])
+      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])
+    LogicalFilter(condition=[NOT(CASE(=($9, 0), false, IS NOT NULL($12), true, <($10, $9), true, false))])
+      LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])
+        LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+          LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
+            LogicalProject(EMPNO=[$1])
+              LogicalFilter(condition=[AND(>($2, 2), =($cor0.ENAME, $0))])
+                LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5])
+                  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalFilter(condition=[=($cor0.EMPNO, $0)])
+          LogicalProject(EMPNO=[$0], i=[true])
+            LogicalProject(EMPNO=[$1])
+              LogicalFilter(condition=[AND(>($2, 2), =($cor0.ENAME, $0))])
+                LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5])
+                  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])
+    LogicalFilter(condition=[NOT(CASE(=($10, 0), false, IS NOT NULL($13), true, <($11, $10), true, false))])
+      LogicalJoin(condition=[AND(=($0, $12), =($1, $14))], joinType=[left])
+        LogicalJoin(condition=[=($1, $9)], joinType=[left])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+          LogicalAggregate(group=[{0}], c=[COUNT()], ck=[COUNT($1)])
+            LogicalProject(ENAME=[$1], EMPNO=[$0])
+              LogicalProject(EMPNO=[$1], ENAME=[$0])
+                LogicalFilter(condition=[>($2, 2)])
+                  LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5])
+                    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+        LogicalProject(EMPNO=[$0], i=[true], ENAME=[$1])
+          LogicalProject(EMPNO=[$1], ENAME=[$0])
+            LogicalFilter(condition=[>($2, 2)])
+              LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5])
+                LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
 </Root>

http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index bc52b14..e58617d 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1531,12 +1531,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
 LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10])
   LogicalJoin(condition=[=($7, $11)], joinType=[inner])
     LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-    LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$2])
-      LogicalJoin(condition=[=($2, $0)], joinType=[inner])
-        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-        LogicalAggregate(group=[{0}])
-          LogicalProject(DEPTNO=[$7])
-            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+    LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO2=[$0])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
     </TestCase>
@@ -1583,39 +1579,34 @@ from (select 2+deptno d2, 3+deptno d3 from emp) e
             <![CDATA[
 LogicalProject(D2=[$0], D3=[$1])
   LogicalProject(D2=[$0], D3=[$1], $f0=[$4])
-    LogicalProject(D2=[$0], D3=[$1], D20=[CAST($2):INTEGER], D30=[$3], $f2=[CAST($4):BOOLEAN])
+    LogicalProject(D2=[$0], D3=[$1], D1=[CAST($2):INTEGER], D30=[$3], $f2=[CAST($4):BOOLEAN])
       LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
         LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
         LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
-          LogicalProject(D2=[$1], D3=[$2], $f0=[$0])
-            LogicalProject($f0=[true], D2=[$1], D3=[$2])
-              LogicalProject(EXPR$0=[1], D2=[$3], D3=[$2])
-                LogicalJoin(condition=[=($0, $3)], joinType=[inner])
-                  LogicalFilter(condition=[IS NOT NULL($1)])
-                    LogicalProject(D1=[$0], $f0=[$4], D3=[$3])
-                      LogicalJoin(condition=[AND(=($0, $1), =($0, $2))], joinType=[left])
-                        LogicalProject(D1=[+($0, 1)])
-                          LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-                        LogicalAggregate(group=[{0, 1, 2}], agg#0=[MIN($3)])
-                          LogicalProject(D1=[$1], D12=[$2], D3=[$3], $f0=[$0])
-                            LogicalProject($f0=[true], D1=[$1], D12=[$2], D3=[$3])
-                              LogicalProject(EXPR$0=[2], D1=[$3], D12=[$3], D3=[$4])
-                                LogicalJoin(condition=[AND(=($0, $3), =($1, $3), =($2, $4))], joinType=[inner])
-                                  LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0, 6)])
-                                    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-                                  LogicalJoin(condition=[true], joinType=[inner])
-                                    LogicalAggregate(group=[{0}])
-                                      LogicalProject(D1=[+($0, 1)])
-                                        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-                                    LogicalAggregate(group=[{0}])
-                                      LogicalProject(D3=[$1])
-                                        LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
-                                          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                  LogicalAggregate(group=[{0}])
-                    LogicalProject(D2=[$0])
-                      LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
-                        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+          LogicalProject(D1=[$1], D3=[$2], $f0=[$0])
+            LogicalProject($f0=[true], D1=[$1], D3=[$2])
+              LogicalProject(EXPR$0=[1], D1=[$0], D3=[$2])
+                LogicalFilter(condition=[IS NOT NULL($1)])
+                  LogicalProject(D1=[$0], $f0=[$3], D3=[$2])
+                    LogicalJoin(condition=[=($0, $1)], joinType=[left])
+                      LogicalProject(D1=[+($0, 1)])
+                        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+                      LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
+                        LogicalProject(D1=[$1], D3=[$2], $f0=[$0])
+                          LogicalProject($f0=[true], D1=[$1], D3=[$2])
+                            LogicalProject(EXPR$0=[2], D1=[$3], D3=[$4])
+                              LogicalJoin(condition=[AND(=($0, $3), =($1, $3), =($2, $4))], joinType=[inner])
+                                LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0, 6)])
+                                  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+                                LogicalJoin(condition=[true], joinType=[inner])
+                                  LogicalAggregate(group=[{0}])
+                                    LogicalProject(D1=[+($0, 1)])
+                                      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+                                  LogicalAggregate(group=[{0}])
+                                    LogicalProject(D3=[$1])
+                                      LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
+                                        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
@@ -1629,24 +1620,21 @@ where exists (
         <Resource name="plan">
             <![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], $f0=[$11])
-    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO1=[CAST($9):INTEGER], DEPTNO0=[CAST($10):INTEGER], $f2=[CAST($11):BOOLEAN])
-      LogicalJoin(condition=[AND(=($7, $9), =($7, $10))], joinType=[inner])
+  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$10])
+    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[CAST($9):INTEGER], $f1=[CAST($10):BOOLEAN])
+      LogicalJoin(condition=[=($7, $9)], joinType=[inner])
         LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-        LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
-          LogicalProject(DEPTNO1=[$1], DEPTNO0=[$2], $f0=[$0])
-            LogicalProject($f0=[true], DEPTNO1=[$1], DEPTNO0=[$2])
-              LogicalProject(EXPR$0=[1], DEPTNO1=[$3], DEPTNO0=[$2])
-                LogicalJoin(condition=[<=($0, $3)], joinType=[inner])
+        LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+          LogicalProject(DEPTNO0=[$1], $f0=[$0])
+            LogicalProject($f0=[true], DEPTNO0=[$1])
+              LogicalProject(EXPR$0=[1], DEPTNO0=[$2])
+                LogicalFilter(condition=[<=($0, $2)])
                   LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$2])
                     LogicalJoin(condition=[>=($0, $2)], joinType=[inner])
                       LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
                       LogicalAggregate(group=[{0}])
                         LogicalProject(DEPTNO=[$7])
                           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                  LogicalAggregate(group=[{0}])
-                    LogicalProject(DEPTNO=[$7])
-                      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
@@ -1663,14 +1651,10 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
       LogicalJoin(condition=[=($7, $9)], joinType=[inner])
         LogicalTableScan(table=[[CATALOG, SALES, EMP]])
         LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
-          LogicalProject(DEPTNO0=[$1], $f0=[$0])
-            LogicalProject($f0=[true], DEPTNO0=[$1])
-              LogicalProject(EXPR$0=[1], DEPTNO0=[$2])
-                LogicalJoin(condition=[=($2, $0)], joinType=[inner])
-                  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-                  LogicalAggregate(group=[{0}])
-                    LogicalProject(DEPTNO=[$7])
-                      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+          LogicalProject(DEPTNO=[$1], $f0=[$0])
+            LogicalProject($f0=[true], DEPTNO=[$1])
+              LogicalProject(EXPR$0=[1], DEPTNO=[$0])
+                LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
     </TestCase>
@@ -1707,15 +1691,11 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$
       LogicalJoin(condition=[=($7, $9)], joinType=[inner])
         LogicalTableScan(table=[[CATALOG, SALES, EMP]])
         LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
-          LogicalProject(DEPTNO0=[$1], $f0=[$0])
-            LogicalProject($f0=[true], DEPTNO0=[$1])
+          LogicalProject(DEPTNO=[$1], $f0=[$0])
+            LogicalProject($f0=[true], DEPTNO=[$1])
               LogicalSort(fetch=[1])
-                LogicalProject(EXPR$0=[1], DEPTNO0=[$2])
-                  LogicalJoin(condition=[=($2, $0)], joinType=[inner])
-                    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-                    LogicalAggregate(group=[{0}])
-                      LogicalProject(DEPTNO=[$7])
-                        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+                LogicalProject(EXPR$0=[1], DEPTNO=[$0])
+                  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
 ]]>
         </Resource>
     </TestCase>
@@ -2997,17 +2977,9 @@ LogicalProject(EMPNO=[$0])
           LogicalFilter(condition=[<($0, 15)])
             LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
         LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
-          LogicalProject(EMPNO0=[$1], SAL=[$0])
-            LogicalProject(SAL=[$5], EMPNO0=[$9])
-              LogicalJoin(condition=[=($9, $0)], joinType=[inner])
-                LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                LogicalAggregate(group=[{0}])
-                  LogicalProject(EMPNO=[$0])
-                    LogicalJoin(condition=[=($7, $9)], joinType=[inner])
-                      LogicalFilter(condition=[<($7, 10)])
-                        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                      LogicalFilter(condition=[<($0, 15)])
-                        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+          LogicalProject(EMPNO=[$1], SAL=[$0])
+            LogicalProject(SAL=[$5], EMPNO=[$0])
+              LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
@@ -3025,21 +2997,15 @@ LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])
   LogicalProject(EMPNO=[$0])
     LogicalProject(EMPNO=[$0])
       LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EXPR$0=[$12])
-        LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], DEPTNO00=[CAST($11):INTEGER], EXPR$0=[CAST($12):INTEGER])
+        LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], DEPTNO1=[CAST($11):INTEGER], EXPR$0=[CAST($12):INTEGER])
           LogicalJoin(condition=[AND(=($9, $11), >($5, $12))], joinType=[inner])
             LogicalJoin(condition=[=($7, $9)], joinType=[inner])
               LogicalTableScan(table=[[CATALOG, SALES, EMP]])
               LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
             LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])
-              LogicalProject(DEPTNO0=[$1], SAL=[$0])
-                LogicalProject(SAL=[$5], DEPTNO0=[$9])
-                  LogicalJoin(condition=[=($7, $9)], joinType=[inner])
-                    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                    LogicalAggregate(group=[{0}])
-                      LogicalProject(DEPTNO0=[$9])
-                        LogicalJoin(condition=[=($7, $9)], joinType=[inner])
-                          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                          LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+              LogicalProject(DEPTNO=[$1], SAL=[$0])
+                LogicalProject(SAL=[$5], DEPTNO=[$7])
+                  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
@@ -3054,7 +3020,7 @@ and exists (select * from emp e2 where e1.empno = e2.empno)]]>
             <![CDATA[
 LogicalProject(EMPNO=[$0])
   LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], $f0=[$12])
-    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EMPNO0=[CAST($11):INTEGER], $f1=[CAST($12):BOOLEAN])
+    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EMPNO9=[CAST($11):INTEGER], $f1=[CAST($12):BOOLEAN])
       LogicalJoin(condition=[=($0, $11)], joinType=[inner])
         LogicalJoin(condition=[=($7, $9)], joinType=[inner])
           LogicalFilter(condition=[<($7, 10)])
@@ -3062,18 +3028,10 @@ LogicalProject(EMPNO=[$0])
           LogicalFilter(condition=[<($0, 15)])
             LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
         LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
-          LogicalProject(EMPNO0=[$1], $f0=[$0])
-            LogicalProject($f0=[true], EMPNO0=[$9])
-              LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9])
-                LogicalJoin(condition=[=($9, $0)], joinType=[inner])
-                  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                  LogicalAggregate(group=[{0}])
-                    LogicalProject(EMPNO=[$0])
-                      LogicalJoin(condition=[=($7, $9)], joinType=[inner])
-                        LogicalFilter(condition=[<($7, 10)])
-                          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                        LogicalFilter(condition=[<($0, 15)])
-                          LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+          LogicalProject(EMPNO9=[$1], $f0=[$0])
+            LogicalProject($f0=[true], EMPNO9=[$9])
+              LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO9=[$0])
+                LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
@@ -3096,18 +3054,10 @@ LogicalProject(EMPNO=[$0])
           LogicalFilter(condition=[<($0, 15)])
             LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
         LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
-          LogicalProject(EMPNO0=[$1], $f0=[$0])
-            LogicalProject($f0=[true], EMPNO0=[$9])
-              LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9])
-                LogicalJoin(condition=[=($9, $0)], joinType=[inner])
-                  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                  LogicalAggregate(group=[{0}])
-                    LogicalProject(EMPNO=[$0])
-                      LogicalJoin(condition=[=($7, $9)], joinType=[inner])
-                        LogicalFilter(condition=[<($7, 10)])
-                          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                        LogicalFilter(condition=[<($0, 15)])
-                          LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+          LogicalProject(EMPNO9=[$1], $f0=[$0])
+            LogicalProject($f0=[true], EMPNO9=[$9])
+              LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO9=[$0])
+                LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
@@ -3539,8 +3489,23 @@ LogicalProject(SAL=[$5])
 LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10])
   LogicalJoin(condition=[=($7, $11)], joinType=[inner])
     LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+    LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO2=[$0])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testLateralDecorrelateThetaRex">
+        <Resource name="sql">
+            <![CDATA[select * from emp,
+ LATERAL (select * from dept where emp.deptno < dept.deptno)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10])
+  LogicalJoin(condition=[=($7, $11)], joinType=[inner])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
     LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$2])
-      LogicalJoin(condition=[=($2, $0)], joinType=[inner])
+      LogicalJoin(condition=[<($2, $0)], joinType=[inner])
         LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
         LogicalAggregate(group=[{0}])
           LogicalProject(DEPTNO=[$7])
@@ -3576,39 +3541,34 @@ from (select 2+deptno d2, 3+deptno d3 from emp) e
             <![CDATA[
 LogicalProject(D2=[$0], D3=[$1])
   LogicalProject(D2=[$0], D3=[$1], $f0=[$4])
-    LogicalProject(D2=[$0], D3=[$1], D20=[CAST($2):INTEGER], D30=[$3], $f2=[CAST($4):BOOLEAN])
+    LogicalProject(D2=[$0], D3=[$1], D1=[CAST($2):INTEGER], D30=[$3], $f2=[CAST($4):BOOLEAN])
       LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner])
         LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
           LogicalTableScan(table=[[CATALOG, SALES, EMP]])
         LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
-          LogicalProject(D2=[$1], D3=[$2], $f0=[$0])
-            LogicalProject($f0=[true], D2=[$1], D3=[$2])
-              LogicalProject(EXPR$0=[1], D2=[$3], D3=[$2])
-                LogicalJoin(condition=[=($0, $3)], joinType=[inner])
-                  LogicalFilter(condition=[IS NOT NULL($1)])
-                    LogicalProject(D1=[$0], $f0=[$4], D3=[$3])
-                      LogicalJoin(condition=[AND(=($0, $1), =($0, $2))], joinType=[left])
-                        LogicalProject(D1=[+($0, 1)])
-                          LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-                        LogicalAggregate(group=[{0, 1, 2}], agg#0=[MIN($3)])
-                          LogicalProject(D1=[$1], D12=[$2], D3=[$3], $f0=[$0])
-                            LogicalProject($f0=[true], D1=[$1], D12=[$2], D3=[$3])
-                              LogicalProject(EXPR$0=[2], D1=[$3], D12=[$3], D3=[$4])
-                                LogicalJoin(condition=[AND(=($0, $3), =($1, $3), =($2, $4))], joinType=[inner])
-                                  LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0, 6)])
-                                    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-                                  LogicalJoin(condition=[true], joinType=[inner])
-                                    LogicalAggregate(group=[{0}])
-                                      LogicalProject(D1=[+($0, 1)])
-                                        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
-                                    LogicalAggregate(group=[{0}])
-                                      LogicalProject(D3=[$1])
-                                        LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
-                                          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
-                  LogicalAggregate(group=[{0}])
-                    LogicalProject(D2=[$0])
-                      LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
-                        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+          LogicalProject(D1=[$1], D3=[$2], $f0=[$0])
+            LogicalProject($f0=[true], D1=[$1], D3=[$2])
+              LogicalProject(EXPR$0=[1], D1=[$0], D3=[$2])
+                LogicalFilter(condition=[IS NOT NULL($1)])
+                  LogicalProject(D1=[$0], $f0=[$3], D3=[$2])
+                    LogicalJoin(condition=[=($0, $1)], joinType=[left])
+                      LogicalProject(D1=[+($0, 1)])
+                        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+                      LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
+                        LogicalProject(D1=[$1], D3=[$2], $f0=[$0])
+                          LogicalProject($f0=[true], D1=[$1], D3=[$2])
+                            LogicalProject(EXPR$0=[2], D1=[$3], D3=[$4])
+                              LogicalJoin(condition=[AND(=($0, $3), =($1, $3), =($2, $4))], joinType=[inner])
+                                LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0, 6)])
+                                  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+                                LogicalJoin(condition=[true], joinType=[inner])
+                                  LogicalAggregate(group=[{0}])
+                                    LogicalProject(D1=[+($0, 1)])
+                                      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+                                  LogicalAggregate(group=[{0}])
+                                    LogicalProject(D3=[$1])
+                                      LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)])
+                                        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
 ]]>
         </Resource>
     </TestCase>
@@ -3699,6 +3659,32 @@ and exists (select * from emp e2 where e1.empno = e2.empno)]]>
             <![CDATA[
 LogicalProject(EMPNO=[$0])
   LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], $f0=[$12])
+    LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EMPNO9=[CAST($11):INTEGER], $f1=[CAST($12):BOOLEAN])
+      LogicalJoin(condition=[=($0, $11)], joinType=[inner])
+        LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+          LogicalFilter(condition=[<($7, 10)])
+            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+          LogicalFilter(condition=[<($0, 15)])
+            LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+        LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+          LogicalProject(EMPNO9=[$1], $f0=[$0])
+            LogicalProject($f0=[true], EMPNO9=[$9])
+              LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO9=[$0])
+                LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testCorrelationExistsAndFilterThetaRex">
+        <Resource name="sql">
+            <![CDATA[SELECT e1.empno
+FROM emp e1, dept d1 where e1.deptno = d1.deptno
+and e1.deptno < 10 and d1.deptno < 15
+and exists (select * from emp e2 where e1.empno < e2.empno)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0])
+  LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], $f0=[$12])
     LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EMPNO0=[CAST($11):INTEGER], $f1=[CAST($12):BOOLEAN])
       LogicalJoin(condition=[=($0, $11)], joinType=[inner])
         LogicalJoin(condition=[=($7, $9)], joinType=[inner])
@@ -3710,7 +3696,7 @@ LogicalProject(EMPNO=[$0])
           LogicalProject(EMPNO0=[$1], $f0=[$0])
             LogicalProject($f0=[true], EMPNO0=[$9])
               LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9])
-                LogicalJoin(condition=[=($9, $0)], joinType=[inner])
+                LogicalJoin(condition=[<($9, $0)], joinType=[inner])
                   LogicalTableScan(table=[[CATALOG, SALES, EMP]])
                   LogicalAggregate(group=[{0}])
                     LogicalProject(EMPNO=[$0])

http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/core/src/test/resources/sql/blank.iq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/blank.iq b/core/src/test/resources/sql/blank.iq
index c305b9d..6375030 100644
--- a/core/src/test/resources/sql/blank.iq
+++ b/core/src/test/resources/sql/blank.iq
@@ -73,24 +73,17 @@ insert into table2 values (NULL, 1), (2, 1);
 # Checked on Oracle
 !set lateDecorrelate true
 select i, j from table1 where table1.j NOT IN (select i from table2 where table1.i=table2.j);
-EnumerableCalc(expr#0..8=[{inputs}], expr#9=[0], expr#10=[=($t3, $t9)], expr#11=[false], expr#12=[IS NOT NULL($t7)], expr#13=[true], expr#14=[IS NULL($t1)], expr#15=[null], expr#16=[<($t4, $t3)], expr#17=[CASE($t10, $t11, $t12, $t13, $t14, $t15, $t16, $t13, $t11)], expr#18=[NOT($t17)], proj#0..1=[{exprs}], $condition=[$t18])
-  EnumerableJoin(condition=[AND(=($0, $6), =($1, $8))], joinType=[left])
+EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0], expr#9=[=($t3, $t8)], expr#10=[false], expr#11=[IS NOT NULL($t7)], expr#12=[true], expr#13=[IS NULL($t1)], expr#14=[null], expr#15=[<($t4, $t3)], expr#16=[CASE($t9, $t10, $t11, $t12, $t13, $t14, $t15, $t12, $t10)], expr#17=[NOT($t16)], proj#0..1=[{exprs}], $condition=[$t17])
+  EnumerableJoin(condition=[AND(=($0, $6), =($1, $5))], joinType=[left])
     EnumerableJoin(condition=[=($0, $2)], joinType=[left])
       EnumerableTableScan(table=[[BLANK, TABLE1]])
-      EnumerableAggregate(group=[{0}], c=[COUNT()], ck=[COUNT($1)])
-        EnumerableJoin(condition=[=($0, $2)], joinType=[inner])
-          EnumerableAggregate(group=[{0}])
-            EnumerableTableScan(table=[[BLANK, TABLE1]])
+      EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
+        EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)], proj#0..1=[{exprs}], $condition=[$t2])
+          EnumerableTableScan(table=[[BLANK, TABLE2]])
+    EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}])
+      EnumerableAggregate(group=[{0, 1}])
+        EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)], expr#3=[IS NOT NULL($t0)], expr#4=[AND($t2, $t3)], proj#0..1=[{exprs}], $condition=[$t4])
           EnumerableTableScan(table=[[BLANK, TABLE2]])
-    EnumerableJoin(condition=[=($0, $3)], joinType=[inner])
-      EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], I=[$t1], I0=[$t0], $f2=[$t2])
-        EnumerableAggregate(group=[{0, 1}])
-          EnumerableJoin(condition=[=($0, $2)], joinType=[inner])
-            EnumerableAggregate(group=[{0}])
-              EnumerableTableScan(table=[[BLANK, TABLE1]])
-            EnumerableTableScan(table=[[BLANK, TABLE2]])
-      EnumerableAggregate(group=[{1}])
-        EnumerableTableScan(table=[[BLANK, TABLE1]])
 !plan
 +---+---+
 | I | J |

http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/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 271c43b..b28caac 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -422,11 +422,7 @@ where exists (
 !ok
 EnumerableSemiJoin(condition=[=($1, $5)], joinType=[inner])
   EnumerableTableScan(table=[[hr, emps]])
-  EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
-    EnumerableAggregate(group=[{1}])
-      EnumerableTableScan(table=[[hr, emps]])
-    EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
-      EnumerableTableScan(table=[[hr, depts]])
+  EnumerableTableScan(table=[[hr, depts]])
 !plan
 
 # NOT EXISTS
@@ -446,12 +442,8 @@ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS NULL($t6)], proj#0..4=[{exprs}],
   EnumerableJoin(condition=[=($1, $5)], joinType=[left])
     EnumerableTableScan(table=[[hr, emps]])
     EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
-      EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], $f0=[$t2], deptno0=[$t0])
-        EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
-          EnumerableAggregate(group=[{1}])
-            EnumerableTableScan(table=[[hr, emps]])
-          EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
-            EnumerableTableScan(table=[[hr, depts]])
+      EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], $f0=[$t4], deptno=[$t0])
+        EnumerableTableScan(table=[[hr, depts]])
 !plan
 
 # NOT EXISTS .. OR NOT EXISTS
@@ -478,20 +470,11 @@ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NULL($t5)], expr#9=[IS NULL($t7)
       EnumerableJoin(condition=[=($1, $5)], joinType=[left])
         EnumerableTableScan(table=[[hr, emps]])
         EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
-          EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], $f0=[$t2], deptno0=[$t0])
-            EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
-              EnumerableAggregate(group=[{1}])
-                EnumerableTableScan(table=[[hr, emps]])
-              EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
-                EnumerableTableScan(table=[[hr, depts]])
-    EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
-      EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], $f0=[$t4], empid=[$t0])
-        EnumerableJoin(condition=[=($1, $3)], joinType=[inner])
-          EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):INTEGER NOT NULL], proj#0..1=[{exprs}])
-            EnumerableAggregate(group=[{0}])
-              EnumerableTableScan(table=[[hr, emps]])
-          EnumerableCalc(expr#0..3=[{inputs}], expr#4=[90], expr#5=[+($t0, $t4)], deptno=[$t0], $f1=[$t5])
+          EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], $f0=[$t4], deptno=[$t0])
             EnumerableTableScan(table=[[hr, depts]])
+    EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
+      EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], expr#5=[90], expr#6=[+($t0, $t5)], expr#7=[CAST($t6):INTEGER NOT NULL], expr#8=[=($t6, $t7)], $f0=[$t4], $f4=[$t6], $condition=[$t8])
+        EnumerableTableScan(table=[[hr, depts]])
 !plan
 
 # Left join to a relation with one row is recognized as a trivial semi-join

http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/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 ff1b824..f61ffc6 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -583,4 +583,48 @@ from "scott".dept as d;
 !ok
 !}
 
+# [CALCITE-1494] Inefficient plan for correlated sub-queries
+# Plan must have only one scan each of emp and dept.
+select sal
+from "scott".emp
+where empno IN (
+  select deptno
+  from "scott".dept
+  where emp.job = dept.dname);
++-----+
+| SAL |
++-----+
++-----+
+(0 rows)
+
+!ok
+EnumerableCalc(expr#0..4=[{inputs}], SAL=[$t4])
+  EnumerableJoin(condition=[AND(=($1, $3), =($0, $2))], joinType=[inner])
+    EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t1):VARCHAR(14) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#4=[=($t3, $t1)], proj#0..1=[{exprs}], $condition=[$t4])
+      EnumerableTableScan(table=[[scott, DEPT]])
+    EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], SAL=[$t5])
+      EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
+# As above, but for EXISTS
+select *
+from "scott".dept as d
+where exists (
+  select 0 from "scott".emp
+  where deptno = d.deptno
+  and ename = 'SMITH');
++--------+----------+--------+
+| DEPTNO | DNAME    | LOC    |
++--------+----------+--------+
+|     20 | RESEARCH | DALLAS |
++--------+----------+--------+
+(1 row)
+
+!ok
+EnumerableSemiJoin(condition=[=($0, $10)], joinType=[inner])
+  EnumerableTableScan(table=[[scott, DEPT]])
+  EnumerableCalc(expr#0..7=[{inputs}], expr#8=[=($t7, $t7)], expr#9=['SMITH'], expr#10=[=($t1, $t9)], expr#11=[AND($t8, $t10)], proj#0..7=[{exprs}], $condition=[$t11])
+    EnumerableTableScan(table=[[scott, EMP]])
+!plan
+
 # End sub-query.iq


Mime
View raw message