calcite-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From jh...@apache.org
Subject [21/50] [abbrv] calcite git commit: [CALCITE-816] Represent sub-query as a RexNode
Date Sat, 23 Jan 2016 00:30:39 GMT
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/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 553f275..764dde2 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -233,7 +233,9 @@ LogicalProject(DEPTNO=[$0], NAME=[$1], EMPSET=[$2])
 ]]>
         </Resource>
         <Resource name="sql">
-            <![CDATA[select *,         multiset(select * from emp where deptno=dept.deptno)
               as empset      from dept]]>
+            <![CDATA[select *,
+  multiset(select * from emp where deptno=dept.deptno) as empset
+from dept]]>
         </Resource>
     </TestCase>
     <TestCase name="testExists">
@@ -1411,7 +1413,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$
     </TestCase>
     <TestCase name="testLateralDecorrelate">
         <Resource name="sql">
-            <![CDATA[select * from emp, LATERAL (select * from dept where emp.deptno=dept.deptno)]]>
+            <![CDATA[select * from emp,
+ LATERAL (select * from dept where emp.deptno=dept.deptno)]]>
         </Resource>
         <Resource name="plan">
             <![CDATA[
@@ -1459,7 +1462,8 @@ LogicalProject(D2=[$0], D3=[$1])
     </TestCase>
     <TestCase name="testNestedCorrelationsDecorrelated">
         <Resource name="sql">
-            <![CDATA[select * from (select 2+deptno d2, 3+deptno d3 from emp) e
+            <![CDATA[select *
+from (select 2+deptno d2, 3+deptno d3 from emp) e
  where exists (select 1 from (select deptno+1 d1 from dept) d
  where d1=e.d2 and exists (select 2 from (select deptno+4 d4, deptno+5 d5, deptno+6 d6 from
dept)
  where d4=d.d1 and d5=d.d1 and d6=e.d3))]]>
@@ -1537,7 +1541,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$
     </TestCase>
     <TestCase name="testExistsCorrelatedDecorrelate">
         <Resource name="sql">
-            <![CDATA[select*from emp where exists (select 1 from dept where emp.deptno=dept.deptno)]]>
+            <![CDATA[select*from emp where exists (
+  select 1 from dept where emp.deptno=dept.deptno)]]>
         </Resource>
         <Resource name="plan">
             <![CDATA[
@@ -1586,16 +1591,20 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$
         <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=[$9])
-    LogicalFilter(condition=[IS NOT NULL($9)])
-      LogicalCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{7}])
+  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=[{}], agg#0=[MIN($0)])
-          LogicalProject($f0=[true])
-            LogicalSort(fetch=[1])
-              LogicalProject(EXPR$0=[1])
-                LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
-                  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+        LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+          LogicalProject(DEPTNO0=[$1], $f0=[$0])
+            LogicalProject($f0=[true], DEPTNO0=[$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]])
 ]]>
         </Resource>
     </TestCase>
@@ -2478,7 +2487,8 @@ LogicalAggregate(group=[{0}], EMPID=[MIN($1)])
     </TestCase>
     <TestCase name="testCorrelationScalarAggAndFilter">
         <Resource name="sql">
-            <![CDATA[SELECT e1.empno FROM emp e1, dept d1 where e1.deptno = d1.deptno
+            <![CDATA[SELECT e1.empno
+FROM emp e1, dept d1 where e1.deptno = d1.deptno
 and e1.deptno < 10 and d1.deptno < 15
 and e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno)]]>
         </Resource>
@@ -2510,7 +2520,8 @@ LogicalProject(EMPNO=[$0])
     </TestCase>
     <TestCase name="testCorrelationExistsAndFilter">
         <Resource name="sql">
-            <![CDATA[SELECT e1.empno FROM emp e1, dept d1 where e1.deptno = d1.deptno
+            <![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>
@@ -2670,4 +2681,545 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$
 ]]>
         </Resource>
     </TestCase>
+    <TestCase name="testWithInsideScalarSubqueryRex">
+        <Resource name="sql">
+            <![CDATA[select (
+ with dept2 as (select * from dept where deptno > 10) select count(*) from dept2) as c
+from emp]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(C=[$SCALAR_QUERY({
+LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
+  LogicalProject($f0=[0])
+    LogicalProject(DEPTNO=[$0], NAME=[$1])
+      LogicalFilter(condition=[>($0, 10)])
+        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testWithInsideWhereExistsRex">
+        <Resource name="sql">
+            <![CDATA[select * from emp
+where exists (
+  with dept2 as (select * from dept where dept.deptno >= emp.deptno)
+  select 1 from dept2 where deptno <= emp.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])
+  LogicalFilter(condition=[EXISTS({
+LogicalFilter(condition=[<=($0, $cor1.DEPTNO)])
+  LogicalProject(DEPTNO=[$0], NAME=[$1])
+    LogicalFilter(condition=[>=($0, $cor1.DEPTNO)])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})], variablesSet=[[$cor1]])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testInUncorrelatedSubqueryInSelectRex">
+        <Resource name="sql">
+            <![CDATA[select name, deptno in (
+  select case when true then deptno else null end from emp)
+from dept]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(NAME=[$1], EXPR$1=[IN($0, {
+LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testNotInUncorrelatedSubqueryInSelectNotNullRex">
+        <Resource name="sql">
+            <![CDATA[select empno, deptno not in (
+  select deptno from dept)
+from emp]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0], EXPR$1=[NOT(IN($7, {
+LogicalProject(DEPTNO=[$0])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+}))])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testNotInUncorrelatedSubqueryRex">
+        <Resource name="sql">
+            <![CDATA[select empno from emp where deptno not in (select deptno from dept)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0])
+  LogicalFilter(condition=[NOT(IN($7, {
+LogicalProject(DEPTNO=[$0])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+}))])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testNotInUncorrelatedSubqueryInSelectRex">
+        <Resource name="sql">
+            <![CDATA[select empno, deptno not in (
+  select case when true then deptno else null end from dept)
+from emp]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0], EXPR$1=[NOT(IN($7, {
+LogicalProject(EXPR$0=[CASE(true, CAST($0):INTEGER, null)])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+}))])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testInUncorrelatedSubqueryRex">
+        <Resource name="sql">
+            <![CDATA[select empno from emp where deptno in (select deptno from dept)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0])
+  LogicalFilter(condition=[IN($7, {
+LogicalProject(DEPTNO=[$0])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testWithInsideWhereExistsDecorrelateRex">
+        <Resource name="sql">
+            <![CDATA[select * from emp
+where exists (
+  with dept2 as (select * from dept where dept.deptno >= emp.deptno)
+  select 1 from dept2 where deptno <= emp.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])
+  LogicalFilter(condition=[EXISTS({
+LogicalFilter(condition=[<=($0, $cor1.DEPTNO)])
+  LogicalProject(DEPTNO=[$0], NAME=[$1])
+    LogicalFilter(condition=[>=($0, $cor1.DEPTNO)])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})], variablesSet=[[$cor1]])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testCompositeInUncorrelatedSubqueryRex">
+        <Resource name="sql">
+            <![CDATA[select empno from emp where (empno, deptno) in (select deptno - 10,
deptno from dept)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0])
+  LogicalFilter(condition=[IN($0, $7, {
+LogicalProject(EXPR$0=[-($0, 10)], DEPTNO=[$0])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testJoinOnInSubQuery">
+        <Resource name="sql">
+            <![CDATA[select * from emp left join dept
+on emp.empno = 1
+or dept.deptno in (select deptno from emp where empno > 5)]]>
+        </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=[OR(=($0, 1), IN($9, {
+LogicalProject(DEPTNO=[$7])
+  LogicalFilter(condition=[>($0, 5)])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))], joinType=[left])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testJoinOnExists">
+        <Resource name="sql">
+            <![CDATA[select * from emp left join dept
+on emp.empno = 1
+or exists (select deptno from emp where empno > dept.deptno + 5)]]>
+        </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=[OR(=($0, 1), EXISTS({
+LogicalFilter(condition=[>($0, +($cor0.DEPTNO, 5))])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))], joinType=[left])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testInUncorrelatedSubqueryInHavingRex">
+        <Resource name="sql">
+            <![CDATA[select sum(sal) as s
+from emp
+group by deptno
+having count(*) > 2
+and deptno in (
+  select case when true then deptno else null end from emp)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(S=[$1])
+  LogicalFilter(condition=[AND(>($2, 2), IN($0, {
+LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+}))])
+    LogicalAggregate(group=[{0}], S=[SUM($1)], agg#1=[COUNT()])
+      LogicalProject(DEPTNO=[$7], SAL=[$5])
+        LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testUncorrelatedScalarSubqueryInGroupOrderRex">
+        <Resource name="sql">
+            <![CDATA[select sum(sal) as s
+from emp
+group by deptno
+order by (select case when true then deptno else null end from emp) desc,
+  count(*)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(S=[$0])
+  LogicalSort(sort0=[$1], sort1=[$2], dir0=[DESC], dir1=[ASC])
+    LogicalProject(S=[$1], EXPR$1=[$SCALAR_QUERY({
+LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})], EXPR$2=[$2])
+      LogicalAggregate(group=[{0}], S=[SUM($1)], agg#1=[COUNT()])
+        LogicalProject(DEPTNO=[$7], SAL=[$5])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testUncorrelatedScalarSubqueryInOrderRex">
+        <Resource name="sql">
+            <![CDATA[select ename
+from emp
+order by (select case when true then deptno else null end from emp) desc,
+  ename]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(ENAME=[$0])
+  LogicalSort(sort0=[$1], sort1=[$0], dir0=[DESC], dir1=[ASC])
+    LogicalProject(ENAME=[$1], EXPR$1=[$SCALAR_QUERY({
+LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)])
+  LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testUncorrelatedScalarSubqueryInAggregateRex">
+        <Resource name="sql">
+            <![CDATA[select sum((select min(deptno) from emp)) as s
+from emp
+group by deptno
+]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(S=[$1])
+  LogicalAggregate(group=[{0}], S=[SUM($1)])
+    LogicalProject(DEPTNO=[$7], $f1=[$SCALAR_QUERY({
+LogicalAggregate(group=[{}], EXPR$0=[MIN($0)])
+  LogicalProject(DEPTNO=[$7])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testWhereInCorrelated">
+        <Resource name="sql">
+            <![CDATA[select empno from emp as e
+join dept as d using (deptno)
+where e.sal in (
+  select e2.sal from emp as e2 where e2.deptno > e.deptno)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0])
+  LogicalFilter(condition=[IN($5, {
+LogicalProject(SAL=[$5])
+  LogicalFilter(condition=[>($7, $cor0.DEPTNO)])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})], variablesSet=[[$cor0]])
+    LogicalJoin(condition=[=($7, $9)], joinType=[inner])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testLateralDecorrelateRex">
+        <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])
+        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+        LogicalAggregate(group=[{0}])
+          LogicalProject(DEPTNO=[$7])
+            LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testExistsCorrelatedDecorrelateRex">
+        <Resource name="sql">
+            <![CDATA[select*from emp where exists (
+  select 1 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])
+  LogicalFilter(condition=[EXISTS({
+LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
+  LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})], variablesSet=[[$cor0]])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testNestedCorrelationsDecorrelatedRex">
+        <Resource name="sql">
+            <![CDATA[select *
+from (select 2+deptno d2, 3+deptno d3 from emp) e
+ where exists (select 1 from (select deptno+1 d1 from dept) d
+ where d1=e.d2 and exists (select 2 from (select deptno+4 d4, deptno+5 d5, deptno+6 d6 from
dept)
+ where d4=d.d1 and d5=d.d1 and d6=e.d3))]]>
+        </Resource>
+        <Resource name="plan">
+            <![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])
+      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]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testJoinUnnestRex">
+        <Resource name="sql">
+            <![CDATA[select*from dept as d, unnest(multiset[d.deptno * 2])]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1], EXPR$0=[$2])
+  LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{0}])
+    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+    Uncollect
+      LogicalProject(EXPR$0=[$SLICE($0)])
+        Collect(field=[EXPR$0])
+          LogicalUnion(all=[true])
+            LogicalProject(EXPR$0=[*($cor0.DEPTNO, 2)])
+              LogicalValues(tuples=[[{ 0 }]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testCorrelationScalarAggAndFilterRex">
+        <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 e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0])
+  LogicalFilter(condition=[AND(=($7, $9), <($7, 10), <($9, 15), >($5, $SCALAR_QUERY({
+LogicalAggregate(group=[{}], EXPR$0=[AVG($0)])
+  LogicalProject(SAL=[$5])
+    LogicalFilter(condition=[=($cor0.EMPNO, $0)])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+})))], variablesSet=[[$cor0]])
+    LogicalJoin(condition=[true], joinType=[inner])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testExistsCorrelatedLimitDecorrelateRex">
+        <Resource name="sql">
+            <![CDATA[select*from emp where exists (
+  select 1 from dept where emp.deptno=dept.deptno limit 1)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6],
DEPTNO=[$7], SLACKER=[$8])
+  LogicalFilter(condition=[EXISTS({
+LogicalSort(fetch=[1])
+  LogicalProject(EXPR$0=[1])
+    LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
+      LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+})], variablesSet=[[$cor0]])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testUnnestSelectRex">
+        <Resource name="sql">
+            <![CDATA[select*from unnest(select multiset[deptno] from dept)]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EXPR$0=[$0])
+  Uncollect
+    LogicalProject(EXPR$0=[$SLICE($2)])
+      LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{0}])
+        LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+        Collect(field=[EXPR$0])
+          LogicalUnion(all=[true])
+            LogicalProject(EXPR$0=[$cor0.DEPTNO])
+              LogicalValues(tuples=[[{ 0 }]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testCorrelationExistsAndFilterRex">
+        <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])
+          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(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]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testCorrelationJoinRex">
+        <Resource name="sql">
+            <![CDATA[select *,
+  multiset(select * from emp where deptno=dept.deptno) as empset
+from dept]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(DEPTNO=[$0], NAME=[$1], EMPSET=[$2])
+  LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{0}])
+    LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+    Collect(field=[EXPR$0])
+      LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5],
COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
+        LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
+          LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testMultisetOfColumnsRex">
+        <Resource name="sql">
+            <![CDATA[select 'abc',multiset[deptno,sal] from emp]]>
+        </Resource>
+        <Resource name="plan">
+            <![CDATA[
+LogicalProject(EXPR$0=['abc'], EXPR$1=[$SLICE($9)])
+  LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{5, 7}])
+    LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+    Collect(field=[EXPR$0])
+      LogicalUnion(all=[true])
+        LogicalProject(EXPR$0=[$cor0.DEPTNO])
+          LogicalValues(tuples=[[{ 0 }]])
+        LogicalProject(EXPR$0=[$cor0.SAL])
+          LogicalValues(tuples=[[{ 0 }]])
+]]>
+        </Resource>
+    </TestCase>
+    <TestCase name="testNotExistsCorrelated">
+        <Resource name="sql">
+            <![CDATA[select * from emp where not exists (
+  select 1 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])
+  LogicalFilter(condition=[NOT(IS NOT NULL($9))])
+    LogicalCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{7}])
+      LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+      LogicalAggregate(group=[{}], agg#0=[MIN($0)])
+        LogicalProject($f0=[true])
+          LogicalProject(EXPR$0=[1])
+            LogicalFilter(condition=[=($cor0.DEPTNO, $0)])
+              LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+]]>
+        </Resource>
+    </TestCase>
 </Root>

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/resources/sql/join.iq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/join.iq b/core/src/test/resources/sql/join.iq
index 357ddc5..aef20e0 100644
--- a/core/src/test/resources/sql/join.iq
+++ b/core/src/test/resources/sql/join.iq
@@ -195,15 +195,17 @@ join "scott".emp emp3 on (emp1.deptno + emp2.deptno = emp3.deptno +
10);
 
 !ok
 
-EnumerableCalc(expr#0..1=[{inputs}], DEPTNO0=[$t1], ENAME=[$t0])
-  EnumerableAggregate(group=[{1, 16}])
-    EnumerableJoin(condition=[=($8, $25)], joinType=[inner])
-      EnumerableCalc(expr#0..7=[{inputs}], expr#8=[10], expr#9=[+($t7, $t8)], proj#0..7=[{exprs}],
$f8=[$t9])
+EnumerableCalc(expr#0..1=[{inputs}], DEPTNO=[$t1], ENAME=[$t0])
+  EnumerableAggregate(group=[{1, 3}])
+    EnumerableJoin(condition=[=($2, $4)], joinType=[inner])
+      EnumerableCalc(expr#0..7=[{inputs}], expr#8=[10], expr#9=[+($t7, $t8)], proj#0..1=[{exprs}],
$f8=[$t9])
         EnumerableTableScan(table=[[scott, EMP]])
-      EnumerableCalc(expr#0..15=[{inputs}], expr#16=[+($t7, $t15)], expr#17=[CAST($t16):INTEGER],
proj#0..15=[{exprs}], $f16=[$t17])
-        EnumerableJoin(condition=[=($7, $15)], joinType=[inner])
-          EnumerableTableScan(table=[[scott, EMP]])
-          EnumerableTableScan(table=[[scott, EMP]])
+      EnumerableCalc(expr#0..3=[{inputs}], expr#4=[+($t1, $t3)], expr#5=[CAST($t4):INTEGER],
DEPTNO=[$t1], $f16=[$t5])
+        EnumerableJoin(condition=[=($1, $3)], joinType=[inner])
+          EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
+            EnumerableTableScan(table=[[scott, EMP]])
+          EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
+            EnumerableTableScan(table=[[scott, EMP]])
 !plan
 
 

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/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 10d69fd..7612f22 100644
--- a/core/src/test/resources/sql/misc.iq
+++ b/core/src/test/resources/sql/misc.iq
@@ -257,6 +257,90 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t2):VARCHAR(1) CHARACTER
SET
       EnumerableTableScan(table=[[hr, depts]])
 !plan
 
+# Un-correlated EXISTS
+select "deptno" from "hr"."depts"
+where exists (select 1 from "hr"."emps");
++--------+
+| deptno |
++--------+
+|     10 |
+|     30 |
+|     40 |
++--------+
+(3 rows)
+
+!ok
+EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t1])
+  EnumerableJoin(condition=[true], joinType=[inner])
+    EnumerableCalc(expr#0=[{inputs}], expr#1=[IS NOT NULL($t0)], $f0=[$t0], $condition=[$t1])
+      EnumerableAggregate(group=[{}], agg#0=[MIN($0)])
+        EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5])
+          EnumerableTableScan(table=[[hr, emps]])
+    EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
+      EnumerableTableScan(table=[[hr, depts]])
+!plan
+
+# Un-correlated NOT EXISTS
+select "deptno" from "hr"."depts"
+where not exists (select 1 from "hr"."emps");
++--------+
+| deptno |
++--------+
++--------+
+(0 rows)
+
+!ok
+EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)], expr#3=[NOT($t2)], deptno=[$t0],
$condition=[$t3])
+  EnumerableJoin(condition=[true], joinType=[left])
+    EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
+      EnumerableTableScan(table=[[hr, depts]])
+    EnumerableAggregate(group=[{}], agg#0=[MIN($0)])
+      EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5])
+        EnumerableTableScan(table=[[hr, emps]])
+!plan
+
+# Un-correlated EXISTS (table empty)
+select "deptno" from "hr"."depts"
+where exists (select 1 from "hr"."emps" where "empid" < 0);
++--------+
+| deptno |
++--------+
++--------+
+(0 rows)
+
+!ok
+EnumerableCalc(expr#0..1=[{inputs}], deptno=[$t1])
+  EnumerableJoin(condition=[true], joinType=[inner])
+    EnumerableCalc(expr#0=[{inputs}], expr#1=[IS NOT NULL($t0)], $f0=[$t0], $condition=[$t1])
+      EnumerableAggregate(group=[{}], agg#0=[MIN($0)])
+        EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], expr#6=[0], expr#7=[<($t0,
$t6)], $f0=[$t5], $condition=[$t7])
+          EnumerableTableScan(table=[[hr, emps]])
+    EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
+      EnumerableTableScan(table=[[hr, depts]])
+!plan
+
+# Un-correlated NOT EXISTS (table empty)
+select "deptno" from "hr"."depts"
+where not exists (select 1 from "hr"."emps" where "empid" < 0);
++--------+
+| deptno |
++--------+
+|     10 |
+|     30 |
+|     40 |
++--------+
+(3 rows)
+
+!ok
+EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)], expr#3=[NOT($t2)], deptno=[$t0],
$condition=[$t3])
+  EnumerableJoin(condition=[true], joinType=[left])
+    EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
+      EnumerableTableScan(table=[[hr, depts]])
+    EnumerableAggregate(group=[{}], agg#0=[MIN($0)])
+      EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], expr#6=[0], expr#7=[<($t0, $t6)],
$f0=[$t5], $condition=[$t7])
+        EnumerableTableScan(table=[[hr, emps]])
+!plan
+
 # EXISTS
 select * from "hr"."emps"
 where exists (
@@ -273,10 +357,10 @@ where exists (
 !ok
 EnumerableSemiJoin(condition=[=($1, $5)], joinType=[inner])
   EnumerableTableScan(table=[[hr, emps]])
-  EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], deptno0=[$t0], $f0=[$t5])
-    EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
-      EnumerableAggregate(group=[{1}])
-        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]])
 !plan
 
@@ -297,11 +381,12 @@ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS NOT NULL($t6)], expr#8=[NOT($t7)
   EnumerableJoin(condition=[=($1, $5)], joinType=[left])
     EnumerableTableScan(table=[[hr, emps]])
     EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
-      EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0])
+      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]])
-          EnumerableTableScan(table=[[hr, depts]])
+          EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
+            EnumerableTableScan(table=[[hr, depts]])
 !plan
 
 # NOT EXISTS .. OR NOT EXISTS
@@ -328,24 +413,26 @@ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t5)], expr#9=[NOT($t8)
       EnumerableJoin(condition=[=($1, $5)], joinType=[left])
         EnumerableTableScan(table=[[hr, emps]])
         EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
-          EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0])
+          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]])
-              EnumerableTableScan(table=[[hr, depts]])
+              EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])
+                EnumerableTableScan(table=[[hr, depts]])
     EnumerableAggregate(group=[{1}], agg#0=[MIN($0)])
-      EnumerableCalc(expr#0..6=[{inputs}], expr#7=[true], $f0=[$t7], empid=[$t0])
-        EnumerableJoin(condition=[=($1, $6)], joinType=[inner])
+      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}])
-              EnumerableSemiJoin(condition=[=($1, $6)], joinType=[inner])
-                EnumerableTableScan(table=[[hr, emps]])
-                EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], $f0=[$t5], deptno0=[$t0])
-                  EnumerableJoin(condition=[=($0, $1)], joinType=[inner])
-                    EnumerableAggregate(group=[{1}])
-                      EnumerableTableScan(table=[[hr, emps]])
+              EnumerableSemiJoin(condition=[=($1, $2)], joinType=[inner])
+                EnumerableCalc(expr#0..4=[{inputs}], proj#0..1=[{exprs}])
+                  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]])
-          EnumerableCalc(expr#0..3=[{inputs}], expr#4=[90], expr#5=[+($t0, $t4)], proj#0..3=[{exprs}],
$f4=[$t5])
+          EnumerableCalc(expr#0..3=[{inputs}], expr#4=[90], expr#5=[+($t0, $t4)], deptno=[$t0],
$f1=[$t5])
             EnumerableTableScan(table=[[hr, depts]])
 !plan
 

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/resources/sql/subquery.iq
----------------------------------------------------------------------
diff --git a/core/src/test/resources/sql/subquery.iq b/core/src/test/resources/sql/subquery.iq
index b69b669..b9964b6 100644
--- a/core/src/test/resources/sql/subquery.iq
+++ b/core/src/test/resources/sql/subquery.iq
@@ -32,25 +32,24 @@ where t1.x not in (select t2.x from t2);
 (0 rows)
 
 !ok
-EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t1, $t6)], expr#8=[false], expr#9=[IS
NOT NULL($t5)], expr#10=[true], expr#11=[IS NULL($t3)], expr#12=[null], expr#13=[<($t2,
$t1)], expr#14=[CASE($t7, $t8, $t9, $t10, $t11, $t12, $t13, $t12, $t8)], expr#15=[NOT($t14)],
X=[$t0], $condition=[$t15])
-  EnumerableJoin(condition=[=($3, $4)], joinType=[left])
-    EnumerableCalc(expr#0..2=[{inputs}], $f0=[$t2], $f1=[$t0], $f2=[$t1], $f3=[$t2])
-      EnumerableJoin(condition=[true], joinType=[inner])
-        EnumerableAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
-          EnumerableUnion(all=[true])
-            EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
-              EnumerableValues(tuples=[[{ 0 }]])
-            EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null],
expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5])
-              EnumerableValues(tuples=[[{ 0 }]])
+EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], expr#6=[=($t1, $t5)], expr#7=[false], expr#8=[IS
NOT NULL($t4)], expr#9=[true], expr#10=[IS NULL($t0)], expr#11=[null], expr#12=[<($t2,
$t1)], expr#13=[CASE($t6, $t7, $t8, $t9, $t10, $t11, $t12, $t9, $t7)], expr#14=[NOT($t13)],
EXPR$0=[$t0], $condition=[$t14])
+  EnumerableJoin(condition=[=($0, $3)], joinType=[left])
+    EnumerableJoin(condition=[true], joinType=[inner])
+      EnumerableUnion(all=[true])
+        EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
+          EnumerableValues(tuples=[[{ 0 }]])
+        EnumerableCalc(expr#0=[{inputs}], expr#1=[2], EXPR$0=[$t1])
+          EnumerableValues(tuples=[[{ 0 }]])
+        EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null],
expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5])
+          EnumerableValues(tuples=[[{ 0 }]])
+      EnumerableAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)])
         EnumerableUnion(all=[true])
           EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
             EnumerableValues(tuples=[[{ 0 }]])
-          EnumerableCalc(expr#0=[{inputs}], expr#1=[2], EXPR$0=[$t1])
-            EnumerableValues(tuples=[[{ 0 }]])
           EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[=($t1, $t1)], expr#3=[null],
expr#4=[3], expr#5=[CASE($t2, $t3, $t4)], EXPR$0=[$t5])
             EnumerableValues(tuples=[[{ 0 }]])
-    EnumerableAggregate(group=[{0}], agg#0=[MIN($1)])
-      EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+    EnumerableCalc(expr#0=[{inputs}], expr#1=[true], proj#0..1=[{exprs}])
+      EnumerableAggregate(group=[{0}])
         EnumerableUnion(all=[true])
           EnumerableCalc(expr#0=[{inputs}], expr#1=[1], EXPR$0=[$t1])
             EnumerableValues(tuples=[[{ 0 }]])
@@ -275,4 +274,47 @@ GROUP BY emp.deptno;
 
 !ok
 
+# Correlated IN sub-query in WHERE clause of JOIN
+select empno from "scott".emp as e
+join "scott".dept as d using (deptno)
+where e.job in (
+  select e2.job from "scott".emp as e2 where e2.deptno > e.deptno);
+ EMPNO
+-------
+  7369
+  7566
+  7782
+  7876
+  7934
+(5 rows)
+
+!ok
+EnumerableCalc(expr#0..5=[{inputs}], EMPNO=[$t0])
+  EnumerableJoin(condition=[=($2, $5)], joinType=[inner])
+    EnumerableCalc(expr#0..4=[{inputs}], EMPNO=[$t2], JOB=[$t3], DEPTNO=[$t4], JOB0=[$t0],
DEPTNO0=[$t1])
+      EnumerableJoin(condition=[AND(=($1, $4), =($0, $3))], joinType=[inner])
+        EnumerableCalc(expr#0..1=[{inputs}], JOB=[$t1], DEPTNO=[$t0])
+          EnumerableAggregate(group=[{0, 2}])
+            EnumerableCalc(expr#0..3=[{inputs}], expr#4=[>($t3, $t0)], proj#0..3=[{exprs}],
$condition=[$t4])
+              EnumerableJoin(condition=[true], joinType=[inner])
+                EnumerableAggregate(group=[{7}])
+                  EnumerableTableScan(table=[[scott, EMP]])
+                EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], DEPTNO=[$t7])
+                  EnumerableTableScan(table=[[scott, EMP]])
+        EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], DEPTNO=[$t7])
+          EnumerableTableScan(table=[[scott, EMP]])
+    EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
+      EnumerableTableScan(table=[[scott, DEPT]])
+!plan
+
+!if (fixed.calcite1045) {
+# Correlated NOT IN sub-query in WHERE clause of JOIN
+select empno from "scott".emp as e
+join "scott".dept as d using (deptno)
+where e.job not in (
+  select e2.job from "scott".emp as e2 where e2.deptno > e.deptno);
+!ok
+!plan
+!}
+
 # End subquery.iq

http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/site/_docs/reference.md
----------------------------------------------------------------------
diff --git a/site/_docs/reference.md b/site/_docs/reference.md
index dbf2974..7bc9bc3 100644
--- a/site/_docs/reference.md
+++ b/site/_docs/reference.md
@@ -173,16 +173,17 @@ functions, or a combination of constants and aggregate
 functions. Aggregate and grouping functions may only appear in an
 aggregate query, and only in a SELECT, HAVING or ORDER BY clause.
 
-A scalar sub-query is a sub-query used as an expression. It can occur
-in most places where an expression can occur (such as the SELECT
-clause, WHERE clause, or as an argument to an aggregate
-function). If the sub-query returns no rows, the value is NULL; if it
+A scalar sub-query is a sub-query used as an expression.
+If the sub-query returns no rows, the value is NULL; if it
 returns more than one row, it is an error.
 
-A sub-query can occur in the FROM clause of a query and also in IN
-and EXISTS expressions.  A sub-query that occurs in IN and
-EXISTS expressions may be correlated; that is, refer to tables in
-the FROM clause of an enclosing query.
+IN, EXISTS and scalar sub-queries can occur
+in any place where an expression can occur (such as the SELECT clause,
+WHERE clause, ON clause of a JOIN, or as an argument to an aggregate
+function).
+
+An IN, EXISTS or scalar sub-query may be correlated; that is, it
+may refer to tables in the FROM clause of an enclosing query.
 
 ## Keywords
 


Mime
View raw message