You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@calcite.apache.org by jh...@apache.org on 2016/01/21 23:38:58 UTC
[21/50] [abbrv] calcite git commit: [CALCITE-816] Represent sub-query
as a RexNode
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