Repository: calcite Updated Branches: refs/heads/master 052f85459 -> 73e437fe5
http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java index 96a7eb0..8efec08 100644 --- a/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java +++ b/core/src/test/java/org/apache/calcite/test/enumerable/EnumerableCorrelateTest.java @@ -36,11 +36,7 @@ public class EnumerableCorrelateTest { + " EnumerableSemiJoin(condition=[=($1, $3)], joinType=[inner])\n" + " EnumerableCalc(expr#0..4=[{inputs}], proj#0..2=[{exprs}])\n" + " EnumerableTableScan(table=[[s, emps]])\n" - + " EnumerableJoin(condition=[=($0, $1)], joinType=[inner])\n" - + " EnumerableAggregate(group=[{1}])\n" - + " EnumerableTableScan(table=[[s, emps]])\n" - + " EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0])\n" - + " EnumerableTableScan(table=[[s, depts]])") + + " EnumerableTableScan(table=[[s, depts]])") .returnsUnordered( "empid=100; name=Bill", "empid=110; name=Theodore", http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml ---------------------------------------------------------------------- diff --git a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml index 6c03478..cc6b417 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -217,15 +217,11 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) LogicalJoin(condition=[=($7, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(DEPTNO0=[$0], $f1=[true]) + LogicalProject(DEPTNO=[$0], $f1=[true]) LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO0=[$1], i=[$0]) - LogicalProject(i=[true], DEPTNO0=[$9]) - LogicalJoin(condition=[=($9, $7)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$1], i=[$0]) + LogicalProject(i=[true], DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planMid"> @@ -288,25 +284,17 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ LogicalJoin(condition=[=($2, $11)], joinType=[left]) LogicalJoin(condition=[=($7, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(DEPTNO0=[$0], $f1=[true]) + LogicalProject(DEPTNO=[$0], $f1=[true]) LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO0=[$1], i=[$0]) - LogicalProject(i=[true], DEPTNO0=[$9]) - LogicalJoin(condition=[=($9, $7)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(JOB0=[$0], $f1=[true]) + LogicalProject(DEPTNO=[$1], i=[$0]) + LogicalProject(i=[true], DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(JOB=[$0], $f1=[true]) LogicalAggregate(group=[{0}]) - LogicalProject(JOB0=[$1], i=[$0]) - LogicalProject(i=[true], JOB0=[$9]) - LogicalJoin(condition=[=($2, $9)], joinType=[inner]) - LogicalFilter(condition=[=($5, 34)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(JOB=[$2]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(JOB=[$1], i=[$0]) + LogicalProject(i=[true], JOB=[$2]) + LogicalFilter(condition=[=($5, 34)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -358,18 +346,10 @@ LogicalProject(SAL=[$5]) LogicalJoin(condition=[AND(=($1, $12), =($0, $11))], joinType=[inner]) LogicalJoin(condition=[AND(=($2, $10), =($0, $9))], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(DEPTNO=[$0], JOB=[$2]) - LogicalJoin(condition=[=($2, $1)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalAggregate(group=[{0}]) - LogicalProject(JOB=[$2]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0], ENAME0=[$9]) - LogicalJoin(condition=[=($9, $1)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(ENAME=[$1]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$0], NAME=[$1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(EMPNO=[$0], ENAME=[$1]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -4118,26 +4098,18 @@ LogicalProject(DEPTNO=[$0], NAME=[$1]) LogicalJoin(condition=[=($0, $2)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO0=[$2], $f0=[true]) - LogicalJoin(condition=[=($1, $2)], joinType=[inner]) - LogicalProject(SAL=[$5], DEPTNO=[$7]) - LogicalFilter(condition=[>($5, 100)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(DEPTNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(DEPTNO9=[$7], $f0=[true]) + LogicalFilter(condition=[>($5, 100)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> <Resource name="planAfter"> <![CDATA[ SemiJoin(condition=[=($0, $2)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalProject(DEPTNO0=[$2], $f0=[true]) - LogicalJoin(condition=[=($1, $2)], joinType=[inner]) - LogicalProject(SAL=[$5], DEPTNO=[$7]) - LogicalFilter(condition=[>($5, 100)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(DEPTNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(DEPTNO9=[$7], $f0=[true]) + LogicalFilter(condition=[>($5, 100)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -4154,12 +4126,9 @@ LogicalProject(DEPTNO=[$0]) LogicalJoin(condition=[=($0, $2)], joinType=[inner]) SemiJoin(condition=[=($0, $2)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalProject(DEPTNO0=[$9], $f0=[true]) - LogicalJoin(condition=[=($7, $9)], joinType=[inner]) - LogicalFilter(condition=[>($5, 100)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(DEPTNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(DEPTNO9=[$7], $f0=[true]) + LogicalFilter(condition=[>($5, 100)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]]) ]]> </Resource> @@ -4170,13 +4139,10 @@ LogicalProject(DEPTNO=[$0]) SemiJoin(condition=[=($0, $1)], joinType=[inner]) LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalProject(DEPTNO0=[$2]) - LogicalJoin(condition=[=($1, $2)], joinType=[inner]) - LogicalFilter(condition=[>($0, 100)]) - LogicalProject(SAL=[$5], DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(DEPTNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(DEPTNO9=[$1]) + LogicalFilter(condition=[>($0, 100)]) + LogicalProject(SAL=[$5], DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalProject(ACCTNO=[$0]) LogicalTableScan(table=[[CATALOG, CUSTOMER, ACCOUNT]]) ]]> @@ -6627,6 +6593,45 @@ LogicalProject(EMPNO=[$0]) </TestCase> <TestCase name="testWhereInCorrelated"> <Resource name="sql"> + <![CDATA[select sal from emp where empno IN ( + select deptno from dept where emp.job = dept.name)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(SAL=[$5]) + LogicalFilter(condition=[IN($0, { +LogicalProject(DEPTNO=[$0]) + LogicalFilter(condition=[=($cor0.JOB, $1)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +})], variablesSet=[[$cor0]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planMid"> + <![CDATA[ +LogicalProject(SAL=[$5]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[=($0, $9)]) + LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{2}]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$0]) + LogicalFilter(condition=[=($cor0.JOB, $1)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(SAL=[$5]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalJoin(condition=[AND(=($2, $10), =($0, $9))], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$0], NAME=[$1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testWhereInJoinCorrelated"> + <Resource name="sql"> <![CDATA[select empno from emp as e join dept as d using (deptno) where e.sal in ( @@ -6759,6 +6764,104 @@ LogicalProject(JOB=[$0], EMPNO=[10], SAL=[$1], S=[$2]) ]]> </Resource> </TestCase> + <TestCase name="testWhereExpressionInCorrelated"> + <Resource name="sql"> + <![CDATA[select ename from ( + select ename, deptno, sal + 1 as salPlus from emp) as e +where deptno in ( + select deptno from emp where sal + 1 = e.salPlus)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(ENAME=[$0]) + LogicalFilter(condition=[IN($1, { +LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[=(+($5, 1), $cor0.SALPLUS)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})], variablesSet=[[$cor0]]) + LogicalProject(ENAME=[$1], DEPTNO=[$7], SALPLUS=[+($5, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planMid"> + <![CDATA[ +LogicalProject(ENAME=[$0]) + LogicalProject(ENAME=[$0], DEPTNO=[$1], SALPLUS=[$2]) + LogicalFilter(condition=[=($1, $3)]) + LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{2}]) + LogicalProject(ENAME=[$1], DEPTNO=[$7], SALPLUS=[+($5, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0}]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[=(+($5, 1), $cor0.SALPLUS)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(ENAME=[$0]) + LogicalProject(ENAME=[$0], DEPTNO=[$1], SALPLUS=[$2]) + LogicalJoin(condition=[AND(=($2, $4), =($1, $3))], joinType=[inner]) + LogicalProject(ENAME=[$1], DEPTNO=[$7], SALPLUS=[+($5, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}]) + LogicalProject(DEPTNO=[$0], $f9=[$1]) + LogicalProject(DEPTNO=[$7], $f9=[$9]) + LogicalFilter(condition=[=(+($5, 1), $9)]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($5, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testWhereExpressionInCorrelated2"> + <Resource name="sql"> + <![CDATA[select name from ( + select name, deptno, deptno - 10 as deptnoMinus from dept) as d +where deptno in ( + select deptno from emp where sal + 1 = d.deptnoMinus)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(NAME=[$0]) + LogicalFilter(condition=[IN($1, { +LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[=(+($5, 1), $cor0.DEPTNOMINUS)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})], variablesSet=[[$cor0]]) + LogicalProject(NAME=[$1], DEPTNO=[$0], DEPTNOMINUS=[-($0, 10)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planMid"> + <![CDATA[ +LogicalProject(NAME=[$0]) + LogicalProject(NAME=[$0], DEPTNO=[$1], DEPTNOMINUS=[$2]) + LogicalFilter(condition=[=($1, $3)]) + LogicalCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{2}]) + LogicalProject(NAME=[$1], DEPTNO=[$0], DEPTNOMINUS=[-($0, 10)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0}]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[=(+($5, 1), $cor0.DEPTNOMINUS)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(NAME=[$0]) + LogicalProject(NAME=[$0], DEPTNO=[$1], DEPTNOMINUS=[$2]) + LogicalJoin(condition=[AND(=($2, $4), =($1, $3))], joinType=[inner]) + LogicalProject(NAME=[$1], DEPTNO=[$0], DEPTNOMINUS=[-($0, 10)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0, 1}]) + LogicalProject(DEPTNO=[$0], $f9=[$1]) + LogicalProject(DEPTNO=[$7], $f9=[$9]) + LogicalFilter(condition=[=(+($5, 1), $9)]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f9=[+($5, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testWhereNotInCorrelated"> <Resource name="sql"> <![CDATA[select sal from emp @@ -6782,27 +6885,16 @@ LogicalProject(DEPTNO=[$0]) LogicalProject(SAL=[$5]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) LogicalFilter(condition=[NOT(CASE(=($10, 0), false, IS NOT NULL($13), true, <($11, $10), true, false))]) - LogicalJoin(condition=[AND(=($0, $15), =($2, $14))], joinType=[left]) + LogicalJoin(condition=[AND(=($0, $12), =($2, $14))], joinType=[left]) LogicalJoin(condition=[=($2, $9)], joinType=[left]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}], c=[COUNT()], ck=[COUNT($1)]) - LogicalProject(JOB=[$1], DEPTNO=[$0]) - LogicalProject(DEPTNO=[$0], JOB=[$2]) - LogicalJoin(condition=[=($2, $1)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalAggregate(group=[{0}]) - LogicalProject(JOB=[$2]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalJoin(condition=[=($3, $0)], joinType=[inner]) - LogicalProject(DEPTNO=[$0], i=[true], JOB=[$1]) - LogicalProject(DEPTNO=[$0], JOB=[$2]) - LogicalJoin(condition=[=($2, $1)], joinType=[inner]) + LogicalProject(NAME=[$1], DEPTNO=[$0]) + LogicalProject(DEPTNO=[$0], NAME=[$1]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalAggregate(group=[{0}]) - LogicalProject(JOB=[$2]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(EMPNO=[$0]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$0], i=[true], NAME=[$1]) + LogicalProject(DEPTNO=[$0], NAME=[$1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> <Resource name="planMid"> @@ -6866,4 +6958,66 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ ]]> </Resource> </TestCase> + <TestCase name="testWhereNotInCorrelated2"> + <Resource name="sql"> + <![CDATA[select * from emp e1 + where e1.empno NOT IN + (select empno from (select ename, empno, sal as r from emp) e2 + where r > 2 and e1.ename= e2.ename)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[NOT(IN($0, { +LogicalProject(EMPNO=[$1]) + LogicalFilter(condition=[AND(>($2, 2), =($cor0.ENAME, $0))]) + LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}))], variablesSet=[[$cor0]]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planMid"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[NOT(CASE(=($9, 0), false, IS NOT NULL($12), true, <($10, $9), true, false))]) + LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}]) + LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{1}]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)]) + LogicalProject(EMPNO=[$1]) + LogicalFilter(condition=[AND(>($2, 2), =($cor0.ENAME, $0))]) + LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalFilter(condition=[=($cor0.EMPNO, $0)]) + LogicalProject(EMPNO=[$0], i=[true]) + LogicalProject(EMPNO=[$1]) + LogicalFilter(condition=[AND(>($2, 2), =($cor0.ENAME, $0))]) + LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[NOT(CASE(=($10, 0), false, IS NOT NULL($13), true, <($11, $10), true, false))]) + LogicalJoin(condition=[AND(=($0, $12), =($1, $14))], joinType=[left]) + LogicalJoin(condition=[=($1, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0}], c=[COUNT()], ck=[COUNT($1)]) + LogicalProject(ENAME=[$1], EMPNO=[$0]) + LogicalProject(EMPNO=[$1], ENAME=[$0]) + LogicalFilter(condition=[>($2, 2)]) + LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EMPNO=[$0], i=[true], ENAME=[$1]) + LogicalProject(EMPNO=[$1], ENAME=[$0]) + LogicalFilter(condition=[>($2, 2)]) + LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> </Root> http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml ---------------------------------------------------------------------- diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml index bc52b14..e58617d 100644 --- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml @@ -1531,12 +1531,8 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10]) LogicalJoin(condition=[=($7, $11)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$2]) - LogicalJoin(condition=[=($2, $0)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO2=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> </TestCase> @@ -1583,39 +1579,34 @@ from (select 2+deptno d2, 3+deptno d3 from emp) e <![CDATA[ LogicalProject(D2=[$0], D3=[$1]) LogicalProject(D2=[$0], D3=[$1], $f0=[$4]) - LogicalProject(D2=[$0], D3=[$1], D20=[CAST($2):INTEGER], D30=[$3], $f2=[CAST($4):BOOLEAN]) + LogicalProject(D2=[$0], D3=[$1], D1=[CAST($2):INTEGER], D30=[$3], $f2=[CAST($4):BOOLEAN]) LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner]) LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)]) - LogicalProject(D2=[$1], D3=[$2], $f0=[$0]) - LogicalProject($f0=[true], D2=[$1], D3=[$2]) - LogicalProject(EXPR$0=[1], D2=[$3], D3=[$2]) - LogicalJoin(condition=[=($0, $3)], joinType=[inner]) - LogicalFilter(condition=[IS NOT NULL($1)]) - LogicalProject(D1=[$0], $f0=[$4], D3=[$3]) - LogicalJoin(condition=[AND(=($0, $1), =($0, $2))], joinType=[left]) - LogicalProject(D1=[+($0, 1)]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalAggregate(group=[{0, 1, 2}], agg#0=[MIN($3)]) - LogicalProject(D1=[$1], D12=[$2], D3=[$3], $f0=[$0]) - LogicalProject($f0=[true], D1=[$1], D12=[$2], D3=[$3]) - LogicalProject(EXPR$0=[2], D1=[$3], D12=[$3], D3=[$4]) - LogicalJoin(condition=[AND(=($0, $3), =($1, $3), =($2, $4))], joinType=[inner]) - LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0, 6)]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalJoin(condition=[true], joinType=[inner]) - LogicalAggregate(group=[{0}]) - LogicalProject(D1=[+($0, 1)]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalAggregate(group=[{0}]) - LogicalProject(D3=[$1]) - LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(D2=[$0]) - LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(D1=[$1], D3=[$2], $f0=[$0]) + LogicalProject($f0=[true], D1=[$1], D3=[$2]) + LogicalProject(EXPR$0=[1], D1=[$0], D3=[$2]) + LogicalFilter(condition=[IS NOT NULL($1)]) + LogicalProject(D1=[$0], $f0=[$3], D3=[$2]) + LogicalJoin(condition=[=($0, $1)], joinType=[left]) + LogicalProject(D1=[+($0, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)]) + LogicalProject(D1=[$1], D3=[$2], $f0=[$0]) + LogicalProject($f0=[true], D1=[$1], D3=[$2]) + LogicalProject(EXPR$0=[2], D1=[$3], D3=[$4]) + LogicalJoin(condition=[AND(=($0, $3), =($1, $3), =($2, $4))], joinType=[inner]) + LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0, 6)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalAggregate(group=[{0}]) + LogicalProject(D1=[+($0, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0}]) + LogicalProject(D3=[$1]) + LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -1629,24 +1620,21 @@ where exists ( <Resource name="plan"> <![CDATA[ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$11]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO1=[CAST($9):INTEGER], DEPTNO0=[CAST($10):INTEGER], $f2=[CAST($11):BOOLEAN]) - LogicalJoin(condition=[AND(=($7, $9), =($7, $10))], joinType=[inner]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], $f0=[$10]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[CAST($9):INTEGER], $f1=[CAST($10):BOOLEAN]) + LogicalJoin(condition=[=($7, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)]) - LogicalProject(DEPTNO1=[$1], DEPTNO0=[$2], $f0=[$0]) - LogicalProject($f0=[true], DEPTNO1=[$1], DEPTNO0=[$2]) - LogicalProject(EXPR$0=[1], DEPTNO1=[$3], DEPTNO0=[$2]) - LogicalJoin(condition=[<=($0, $3)], joinType=[inner]) + LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) + LogicalProject(DEPTNO0=[$1], $f0=[$0]) + LogicalProject($f0=[true], DEPTNO0=[$1]) + LogicalProject(EXPR$0=[1], DEPTNO0=[$2]) + LogicalFilter(condition=[<=($0, $2)]) LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$2]) LogicalJoin(condition=[>=($0, $2)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) LogicalAggregate(group=[{0}]) LogicalProject(DEPTNO=[$7]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -1663,14 +1651,10 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ LogicalJoin(condition=[=($7, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) - LogicalProject(DEPTNO0=[$1], $f0=[$0]) - LogicalProject($f0=[true], DEPTNO0=[$1]) - LogicalProject(EXPR$0=[1], DEPTNO0=[$2]) - LogicalJoin(condition=[=($2, $0)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$1], $f0=[$0]) + LogicalProject($f0=[true], DEPTNO=[$1]) + LogicalProject(EXPR$0=[1], DEPTNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> </TestCase> @@ -1707,15 +1691,11 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ LogicalJoin(condition=[=($7, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) - LogicalProject(DEPTNO0=[$1], $f0=[$0]) - LogicalProject($f0=[true], DEPTNO0=[$1]) + LogicalProject(DEPTNO=[$1], $f0=[$0]) + LogicalProject($f0=[true], DEPTNO=[$1]) LogicalSort(fetch=[1]) - LogicalProject(EXPR$0=[1], DEPTNO0=[$2]) - LogicalJoin(condition=[=($2, $0)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO=[$7]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(EXPR$0=[1], DEPTNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> </TestCase> @@ -2997,17 +2977,9 @@ LogicalProject(EMPNO=[$0]) LogicalFilter(condition=[<($0, 15)]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)]) - LogicalProject(EMPNO0=[$1], SAL=[$0]) - LogicalProject(SAL=[$5], EMPNO0=[$9]) - LogicalJoin(condition=[=($9, $0)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(EMPNO=[$0]) - LogicalJoin(condition=[=($7, $9)], joinType=[inner]) - LogicalFilter(condition=[<($7, 10)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalFilter(condition=[<($0, 15)]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(EMPNO=[$1], SAL=[$0]) + LogicalProject(SAL=[$5], EMPNO=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3025,21 +2997,15 @@ LogicalAggregate(group=[{}], EXPR$0=[SUM($0)]) LogicalProject(EMPNO=[$0]) LogicalProject(EMPNO=[$0]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EXPR$0=[$12]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], DEPTNO00=[CAST($11):INTEGER], EXPR$0=[CAST($12):INTEGER]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], DEPTNO1=[CAST($11):INTEGER], EXPR$0=[CAST($12):INTEGER]) LogicalJoin(condition=[AND(=($9, $11), >($5, $12))], joinType=[inner]) LogicalJoin(condition=[=($7, $9)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)]) - LogicalProject(DEPTNO0=[$1], SAL=[$0]) - LogicalProject(SAL=[$5], DEPTNO0=[$9]) - LogicalJoin(condition=[=($7, $9)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(DEPTNO0=[$9]) - LogicalJoin(condition=[=($7, $9)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(DEPTNO=[$1], SAL=[$0]) + LogicalProject(SAL=[$5], DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3054,7 +3020,7 @@ and exists (select * from emp e2 where e1.empno = e2.empno)]]> <![CDATA[ LogicalProject(EMPNO=[$0]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], $f0=[$12]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EMPNO0=[CAST($11):INTEGER], $f1=[CAST($12):BOOLEAN]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EMPNO9=[CAST($11):INTEGER], $f1=[CAST($12):BOOLEAN]) LogicalJoin(condition=[=($0, $11)], joinType=[inner]) LogicalJoin(condition=[=($7, $9)], joinType=[inner]) LogicalFilter(condition=[<($7, 10)]) @@ -3062,18 +3028,10 @@ LogicalProject(EMPNO=[$0]) LogicalFilter(condition=[<($0, 15)]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) - LogicalProject(EMPNO0=[$1], $f0=[$0]) - LogicalProject($f0=[true], EMPNO0=[$9]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9]) - LogicalJoin(condition=[=($9, $0)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(EMPNO=[$0]) - LogicalJoin(condition=[=($7, $9)], joinType=[inner]) - LogicalFilter(condition=[<($7, 10)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalFilter(condition=[<($0, 15)]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(EMPNO9=[$1], $f0=[$0]) + LogicalProject($f0=[true], EMPNO9=[$9]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO9=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3096,18 +3054,10 @@ LogicalProject(EMPNO=[$0]) LogicalFilter(condition=[<($0, 15)]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) - LogicalProject(EMPNO0=[$1], $f0=[$0]) - LogicalProject($f0=[true], EMPNO0=[$9]) - LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9]) - LogicalJoin(condition=[=($9, $0)], joinType=[inner]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(EMPNO=[$0]) - LogicalJoin(condition=[=($7, $9)], joinType=[inner]) - LogicalFilter(condition=[<($7, 10)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalFilter(condition=[<($0, 15)]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalProject(EMPNO9=[$1], $f0=[$0]) + LogicalProject($f0=[true], EMPNO9=[$9]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO9=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3539,8 +3489,23 @@ LogicalProject(SAL=[$5]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10]) LogicalJoin(condition=[=($7, $11)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO2=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + </TestCase> + <TestCase name="testLateralDecorrelateThetaRex"> + <Resource name="sql"> + <![CDATA[select * from emp, + LATERAL (select * from dept where emp.deptno < dept.deptno)]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10]) + LogicalJoin(condition=[=($7, $11)], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalProject(DEPTNO=[$0], NAME=[$1], DEPTNO0=[$2]) - LogicalJoin(condition=[=($2, $0)], joinType=[inner]) + LogicalJoin(condition=[<($2, $0)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) LogicalAggregate(group=[{0}]) LogicalProject(DEPTNO=[$7]) @@ -3576,39 +3541,34 @@ from (select 2+deptno d2, 3+deptno d3 from emp) e <![CDATA[ LogicalProject(D2=[$0], D3=[$1]) LogicalProject(D2=[$0], D3=[$1], $f0=[$4]) - LogicalProject(D2=[$0], D3=[$1], D20=[CAST($2):INTEGER], D30=[$3], $f2=[CAST($4):BOOLEAN]) + LogicalProject(D2=[$0], D3=[$1], D1=[CAST($2):INTEGER], D30=[$3], $f2=[CAST($4):BOOLEAN]) LogicalJoin(condition=[AND(=($0, $2), =($1, $3))], joinType=[inner]) LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)]) - LogicalProject(D2=[$1], D3=[$2], $f0=[$0]) - LogicalProject($f0=[true], D2=[$1], D3=[$2]) - LogicalProject(EXPR$0=[1], D2=[$3], D3=[$2]) - LogicalJoin(condition=[=($0, $3)], joinType=[inner]) - LogicalFilter(condition=[IS NOT NULL($1)]) - LogicalProject(D1=[$0], $f0=[$4], D3=[$3]) - LogicalJoin(condition=[AND(=($0, $1), =($0, $2))], joinType=[left]) - LogicalProject(D1=[+($0, 1)]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalAggregate(group=[{0, 1, 2}], agg#0=[MIN($3)]) - LogicalProject(D1=[$1], D12=[$2], D3=[$3], $f0=[$0]) - LogicalProject($f0=[true], D1=[$1], D12=[$2], D3=[$3]) - LogicalProject(EXPR$0=[2], D1=[$3], D12=[$3], D3=[$4]) - LogicalJoin(condition=[AND(=($0, $3), =($1, $3), =($2, $4))], joinType=[inner]) - LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0, 6)]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalJoin(condition=[true], joinType=[inner]) - LogicalAggregate(group=[{0}]) - LogicalProject(D1=[+($0, 1)]) - LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) - LogicalAggregate(group=[{0}]) - LogicalProject(D3=[$1]) - LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) - LogicalAggregate(group=[{0}]) - LogicalProject(D2=[$0]) - LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)]) - LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(D1=[$1], D3=[$2], $f0=[$0]) + LogicalProject($f0=[true], D1=[$1], D3=[$2]) + LogicalProject(EXPR$0=[1], D1=[$0], D3=[$2]) + LogicalFilter(condition=[IS NOT NULL($1)]) + LogicalProject(D1=[$0], $f0=[$3], D3=[$2]) + LogicalJoin(condition=[=($0, $1)], joinType=[left]) + LogicalProject(D1=[+($0, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)]) + LogicalProject(D1=[$1], D3=[$2], $f0=[$0]) + LogicalProject($f0=[true], D1=[$1], D3=[$2]) + LogicalProject(EXPR$0=[2], D1=[$3], D3=[$4]) + LogicalJoin(condition=[AND(=($0, $3), =($1, $3), =($2, $4))], joinType=[inner]) + LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0, 6)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalAggregate(group=[{0}]) + LogicalProject(D1=[+($0, 1)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0}]) + LogicalProject(D3=[$1]) + LogicalProject(D2=[+(2, $7)], D3=[+(3, $7)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> </TestCase> @@ -3699,6 +3659,32 @@ and exists (select * from emp e2 where e1.empno = e2.empno)]]> <![CDATA[ LogicalProject(EMPNO=[$0]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], $f0=[$12]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EMPNO9=[CAST($11):INTEGER], $f1=[CAST($12):BOOLEAN]) + LogicalJoin(condition=[=($0, $11)], joinType=[inner]) + LogicalJoin(condition=[=($7, $9)], joinType=[inner]) + LogicalFilter(condition=[<($7, 10)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalFilter(condition=[<($0, 15)]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0}], agg#0=[MIN($1)]) + LogicalProject(EMPNO9=[$1], $f0=[$0]) + LogicalProject($f0=[true], EMPNO9=[$9]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO9=[$0]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testCorrelationExistsAndFilterThetaRex"> + <Resource name="sql"> + <![CDATA[SELECT e1.empno +FROM emp e1, dept d1 where e1.deptno = d1.deptno +and e1.deptno < 10 and d1.deptno < 15 +and exists (select * from emp e2 where e1.empno < e2.empno)]]> + </Resource> + <Resource name="plan"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], $f0=[$12]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], EMPNO0=[CAST($11):INTEGER], $f1=[CAST($12):BOOLEAN]) LogicalJoin(condition=[=($0, $11)], joinType=[inner]) LogicalJoin(condition=[=($7, $9)], joinType=[inner]) @@ -3710,7 +3696,7 @@ LogicalProject(EMPNO=[$0]) LogicalProject(EMPNO0=[$1], $f0=[$0]) LogicalProject($f0=[true], EMPNO0=[$9]) LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], EMPNO0=[$9]) - LogicalJoin(condition=[=($9, $0)], joinType=[inner]) + LogicalJoin(condition=[<($9, $0)], joinType=[inner]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}]) LogicalProject(EMPNO=[$0]) http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/core/src/test/resources/sql/blank.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/blank.iq b/core/src/test/resources/sql/blank.iq index c305b9d..6375030 100644 --- a/core/src/test/resources/sql/blank.iq +++ b/core/src/test/resources/sql/blank.iq @@ -73,24 +73,17 @@ insert into table2 values (NULL, 1), (2, 1); # Checked on Oracle !set lateDecorrelate true select i, j from table1 where table1.j NOT IN (select i from table2 where table1.i=table2.j); -EnumerableCalc(expr#0..8=[{inputs}], expr#9=[0], expr#10=[=($t3, $t9)], expr#11=[false], expr#12=[IS NOT NULL($t7)], expr#13=[true], expr#14=[IS NULL($t1)], expr#15=[null], expr#16=[<($t4, $t3)], expr#17=[CASE($t10, $t11, $t12, $t13, $t14, $t15, $t16, $t13, $t11)], expr#18=[NOT($t17)], proj#0..1=[{exprs}], $condition=[$t18]) - EnumerableJoin(condition=[AND(=($0, $6), =($1, $8))], joinType=[left]) +EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0], expr#9=[=($t3, $t8)], expr#10=[false], expr#11=[IS NOT NULL($t7)], expr#12=[true], expr#13=[IS NULL($t1)], expr#14=[null], expr#15=[<($t4, $t3)], expr#16=[CASE($t9, $t10, $t11, $t12, $t13, $t14, $t15, $t12, $t10)], expr#17=[NOT($t16)], proj#0..1=[{exprs}], $condition=[$t17]) + EnumerableJoin(condition=[AND(=($0, $6), =($1, $5))], joinType=[left]) EnumerableJoin(condition=[=($0, $2)], joinType=[left]) EnumerableTableScan(table=[[BLANK, TABLE1]]) - EnumerableAggregate(group=[{0}], c=[COUNT()], ck=[COUNT($1)]) - EnumerableJoin(condition=[=($0, $2)], joinType=[inner]) - EnumerableAggregate(group=[{0}]) - EnumerableTableScan(table=[[BLANK, TABLE1]]) + EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)]) + EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)], proj#0..1=[{exprs}], $condition=[$t2]) + EnumerableTableScan(table=[[BLANK, TABLE2]]) + EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], proj#0..2=[{exprs}]) + EnumerableAggregate(group=[{0, 1}]) + EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)], expr#3=[IS NOT NULL($t0)], expr#4=[AND($t2, $t3)], proj#0..1=[{exprs}], $condition=[$t4]) EnumerableTableScan(table=[[BLANK, TABLE2]]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], I=[$t1], I0=[$t0], $f2=[$t2]) - EnumerableAggregate(group=[{0, 1}]) - EnumerableJoin(condition=[=($0, $2)], joinType=[inner]) - EnumerableAggregate(group=[{0}]) - EnumerableTableScan(table=[[BLANK, TABLE1]]) - EnumerableTableScan(table=[[BLANK, TABLE2]]) - EnumerableAggregate(group=[{1}]) - EnumerableTableScan(table=[[BLANK, TABLE1]]) !plan +---+---+ | I | J | http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/core/src/test/resources/sql/misc.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/misc.iq b/core/src/test/resources/sql/misc.iq index 271c43b..b28caac 100644 --- a/core/src/test/resources/sql/misc.iq +++ b/core/src/test/resources/sql/misc.iq @@ -422,11 +422,7 @@ where exists ( !ok EnumerableSemiJoin(condition=[=($1, $5)], joinType=[inner]) EnumerableTableScan(table=[[hr, emps]]) - EnumerableJoin(condition=[=($0, $1)], joinType=[inner]) - EnumerableAggregate(group=[{1}]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0]) - EnumerableTableScan(table=[[hr, depts]]) + EnumerableTableScan(table=[[hr, depts]]) !plan # NOT EXISTS @@ -446,12 +442,8 @@ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS NULL($t6)], proj#0..4=[{exprs}], EnumerableJoin(condition=[=($1, $5)], joinType=[left]) EnumerableTableScan(table=[[hr, emps]]) EnumerableAggregate(group=[{1}], agg#0=[MIN($0)]) - EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], $f0=[$t2], deptno0=[$t0]) - EnumerableJoin(condition=[=($0, $1)], joinType=[inner]) - EnumerableAggregate(group=[{1}]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0]) - EnumerableTableScan(table=[[hr, depts]]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], $f0=[$t4], deptno=[$t0]) + EnumerableTableScan(table=[[hr, depts]]) !plan # NOT EXISTS .. OR NOT EXISTS @@ -478,20 +470,11 @@ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NULL($t5)], expr#9=[IS NULL($t7) EnumerableJoin(condition=[=($1, $5)], joinType=[left]) EnumerableTableScan(table=[[hr, emps]]) EnumerableAggregate(group=[{1}], agg#0=[MIN($0)]) - EnumerableCalc(expr#0..1=[{inputs}], expr#2=[true], $f0=[$t2], deptno0=[$t0]) - EnumerableJoin(condition=[=($0, $1)], joinType=[inner]) - EnumerableAggregate(group=[{1}]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableCalc(expr#0..3=[{inputs}], deptno=[$t0]) - EnumerableTableScan(table=[[hr, depts]]) - EnumerableAggregate(group=[{1}], agg#0=[MIN($0)]) - EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], $f0=[$t4], empid=[$t0]) - EnumerableJoin(condition=[=($1, $3)], joinType=[inner]) - EnumerableCalc(expr#0=[{inputs}], expr#1=[CAST($t0):INTEGER NOT NULL], proj#0..1=[{exprs}]) - EnumerableAggregate(group=[{0}]) - EnumerableTableScan(table=[[hr, emps]]) - EnumerableCalc(expr#0..3=[{inputs}], expr#4=[90], expr#5=[+($t0, $t4)], deptno=[$t0], $f1=[$t5]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], $f0=[$t4], deptno=[$t0]) EnumerableTableScan(table=[[hr, depts]]) + EnumerableAggregate(group=[{1}], agg#0=[MIN($0)]) + EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], expr#5=[90], expr#6=[+($t0, $t5)], expr#7=[CAST($t6):INTEGER NOT NULL], expr#8=[=($t6, $t7)], $f0=[$t4], $f4=[$t6], $condition=[$t8]) + EnumerableTableScan(table=[[hr, depts]]) !plan # Left join to a relation with one row is recognized as a trivial semi-join http://git-wip-us.apache.org/repos/asf/calcite/blob/73e437fe/core/src/test/resources/sql/sub-query.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq index ff1b824..f61ffc6 100644 --- a/core/src/test/resources/sql/sub-query.iq +++ b/core/src/test/resources/sql/sub-query.iq @@ -583,4 +583,48 @@ from "scott".dept as d; !ok !} +# [CALCITE-1494] Inefficient plan for correlated sub-queries +# Plan must have only one scan each of emp and dept. +select sal +from "scott".emp +where empno IN ( + select deptno + from "scott".dept + where emp.job = dept.dname); ++-----+ +| SAL | ++-----+ ++-----+ +(0 rows) + +!ok +EnumerableCalc(expr#0..4=[{inputs}], SAL=[$t4]) + EnumerableJoin(condition=[AND(=($1, $3), =($0, $2))], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t1):VARCHAR(14) CHARACTER SET "ISO-8859-1" COLLATE "ISO-8859-1$en_US$primary"], expr#4=[=($t3, $t1)], proj#0..1=[{exprs}], $condition=[$t4]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# As above, but for EXISTS +select * +from "scott".dept as d +where exists ( + select 0 from "scott".emp + where deptno = d.deptno + and ename = 'SMITH'); ++--------+----------+--------+ +| DEPTNO | DNAME | LOC | ++--------+----------+--------+ +| 20 | RESEARCH | DALLAS | ++--------+----------+--------+ +(1 row) + +!ok +EnumerableSemiJoin(condition=[=($0, $10)], joinType=[inner]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], expr#8=[=($t7, $t7)], expr#9=['SMITH'], expr#10=[=($t1, $t9)], expr#11=[AND($t8, $t10)], proj#0..7=[{exprs}], $condition=[$t11]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + # End sub-query.iq
