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
