http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java index 156a973..7f191a3 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptTestBase.java @@ -170,6 +170,36 @@ abstract class RelOptTestBase extends SqlToRelTestBase { } SqlToRelTestBase.assertValid(relAfter); } + + /** Sets the SQL statement for a test. */ + Sql sql(String sql) { + return new Sql(sql, null, true); + } + + /** Allows fluent testing. */ + class Sql { + private final String sql; + private final HepPlanner hepPlanner; + private final boolean expand; + + public Sql(String sql, HepPlanner hepPlanner, boolean expand) { + this.sql = sql; + this.hepPlanner = hepPlanner; + this.expand = expand; + } + + public Sql with(HepPlanner hepPlanner) { + return new Sql(sql, hepPlanner, expand); + } + + public Sql expand(boolean expand) { + return new Sql(sql, hepPlanner, expand); + } + + public void check() { + checkPlanning(tester.withExpand(expand), null, hepPlanner, sql); + } + } } // End RelOptTestBase.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java b/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java index 22bb852..0f88f0c 100644 --- a/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java +++ b/core/src/test/java/org/apache/calcite/test/RexTransformerTest.java @@ -18,6 +18,7 @@ package org.apache.calcite.test; import org.apache.calcite.jdbc.JavaTypeFactoryImpl; import org.apache.calcite.plan.RelOptUtil; +import org.apache.calcite.plan.RelOptUtil.Logic; import org.apache.calcite.rel.RelNode; import org.apache.calcite.rel.logical.LogicalJoin; import org.apache.calcite.rel.logical.LogicalProject; @@ -25,6 +26,7 @@ import org.apache.calcite.rel.type.RelDataType; import org.apache.calcite.rel.type.RelDataTypeFactory; import org.apache.calcite.rel.type.RelDataTypeField; import org.apache.calcite.rel.type.RelDataTypeSystem; +import org.apache.calcite.rex.LogicVisitor; import org.apache.calcite.rex.RexBuilder; import org.apache.calcite.rex.RexInputRef; import org.apache.calcite.rex.RexLiteral; @@ -104,16 +106,10 @@ public class RexTransformerTest { if (null == encapsulateType) { root = node; } else if (encapsulateType.equals(Boolean.TRUE)) { - root = - rexBuilder.makeCall( - SqlStdOperatorTable.IS_TRUE, - node); + root = isTrue(node); } else { // encapsulateType.equals(Boolean.FALSE) - root = - rexBuilder.makeCall( - SqlStdOperatorTable.IS_FALSE, - node); + root = isFalse(node); } RexTransformer transformer = new RexTransformer(root, rexBuilder); @@ -126,6 +122,59 @@ public class RexTransformerTest { } } + private RexNode lessThan(RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN, a0, a1); + } + + private RexNode lessThanOrEqual(RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.LESS_THAN_OR_EQUAL, a0, a1); + } + + private RexNode greaterThan(RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.GREATER_THAN, a0, a1); + } + + private RexNode greaterThanOrEqual(RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, a0, + a1); + } + + private RexNode equals(RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.EQUALS, a0, a1); + } + + private RexNode notEquals(RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.NOT_EQUALS, a0, a1); + } + + private RexNode and(RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.AND, a0, a1); + } + + private RexNode or(RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.OR, a0, a1); + } + + private RexNode not(RexNode a0) { + return rexBuilder.makeCall(SqlStdOperatorTable.NOT, a0); + } + + private RexNode plus(RexNode a0, RexNode a1) { + return rexBuilder.makeCall(SqlStdOperatorTable.PLUS, a0, a1); + } + + private RexNode isNotNull(RexNode a0) { + return rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL, a0); + } + + private RexNode isFalse(RexNode node) { + return rexBuilder.makeCall(SqlStdOperatorTable.IS_FALSE, node); + } + + private RexNode isTrue(RexNode node) { + return rexBuilder.makeCall(SqlStdOperatorTable.IS_TRUE, node); + } + @Test public void testPreTests() { // can make variable nullable? RexNode node = @@ -147,11 +196,7 @@ public class RexTransformerTest { } @Test public void testNonBooleans() { - RexNode node = - rexBuilder.makeCall( - SqlStdOperatorTable.PLUS, - x, - y); + RexNode node = plus(x, y); String expected = node.toString(); check(Boolean.TRUE, node, expected); check(Boolean.FALSE, node, expected); @@ -165,11 +210,7 @@ public class RexTransformerTest { * could be produced */ @Test public void testOrUnchanged() { - RexNode node = - rexBuilder.makeCall( - SqlStdOperatorTable.OR, - x, - y); + RexNode node = or(x, y); String expected = node.toString(); check(Boolean.TRUE, node, expected); check(Boolean.FALSE, node, expected); @@ -177,11 +218,7 @@ public class RexTransformerTest { } @Test public void testSimpleAnd() { - RexNode node = - rexBuilder.makeCall( - SqlStdOperatorTable.AND, - x, - y); + RexNode node = and(x, y); check( Boolean.FALSE, node, @@ -189,11 +226,7 @@ public class RexTransformerTest { } @Test public void testSimpleEquals() { - RexNode node = - rexBuilder.makeCall( - SqlStdOperatorTable.EQUALS, - x, - y); + RexNode node = equals(x, y); check( Boolean.TRUE, node, @@ -201,11 +234,7 @@ public class RexTransformerTest { } @Test public void testSimpleNotEquals() { - RexNode node = - rexBuilder.makeCall( - SqlStdOperatorTable.NOT_EQUALS, - x, - y); + RexNode node = notEquals(x, y); check( Boolean.FALSE, node, @@ -213,11 +242,7 @@ public class RexTransformerTest { } @Test public void testSimpleGreaterThan() { - RexNode node = - rexBuilder.makeCall( - SqlStdOperatorTable.GREATER_THAN, - x, - y); + RexNode node = greaterThan(x, y); check( Boolean.TRUE, node, @@ -225,11 +250,7 @@ public class RexTransformerTest { } @Test public void testSimpleGreaterEquals() { - RexNode node = - rexBuilder.makeCall( - SqlStdOperatorTable.GREATER_THAN_OR_EQUAL, - x, - y); + RexNode node = greaterThanOrEqual(x, y); check( Boolean.FALSE, node, @@ -237,11 +258,7 @@ public class RexTransformerTest { } @Test public void testSimpleLessThan() { - RexNode node = - rexBuilder.makeCall( - SqlStdOperatorTable.LESS_THAN, - x, - y); + RexNode node = lessThan(x, y); check( Boolean.TRUE, node, @@ -249,11 +266,7 @@ public class RexTransformerTest { } @Test public void testSimpleLessEqual() { - RexNode node = - rexBuilder.makeCall( - SqlStdOperatorTable.LESS_THAN_OR_EQUAL, - x, - y); + RexNode node = lessThanOrEqual(x, y); check( Boolean.FALSE, node, @@ -261,17 +274,9 @@ public class RexTransformerTest { } @Test public void testOptimizeNonNullLiterals() { - RexNode node = - rexBuilder.makeCall( - SqlStdOperatorTable.LESS_THAN_OR_EQUAL, - x, - trueRex); + RexNode node = lessThanOrEqual(x, trueRex); check(Boolean.TRUE, node, "AND(IS NOT NULL($0), <=($0, true))"); - node = - rexBuilder.makeCall( - SqlStdOperatorTable.LESS_THAN_OR_EQUAL, - trueRex, - x); + node = lessThanOrEqual(trueRex, x); check(Boolean.FALSE, node, "AND(IS NOT NULL($0), <=(true, $0))"); } @@ -282,16 +287,8 @@ public class RexTransformerTest { @Test public void testMixed1() { // x=true AND y - RexNode op1 = - rexBuilder.makeCall( - SqlStdOperatorTable.EQUALS, - x, - trueRex); - RexNode and = - rexBuilder.makeCall( - SqlStdOperatorTable.AND, - op1, - y); + RexNode op1 = equals(x, trueRex); + RexNode and = and(op1, y); check( Boolean.FALSE, and, @@ -300,21 +297,9 @@ public class RexTransformerTest { @Test public void testMixed2() { // x!=true AND y>z - RexNode op1 = - rexBuilder.makeCall( - SqlStdOperatorTable.NOT_EQUALS, - x, - trueRex); - RexNode op2 = - rexBuilder.makeCall( - SqlStdOperatorTable.GREATER_THAN, - y, - z); - RexNode and = - rexBuilder.makeCall( - SqlStdOperatorTable.AND, - op1, - op2); + RexNode op1 = notEquals(x, trueRex); + RexNode op2 = greaterThan(y, z); + RexNode and = and(op1, op2); check( Boolean.FALSE, and, @@ -323,21 +308,9 @@ public class RexTransformerTest { @Test public void testMixed3() { // x=y AND false>z - RexNode op1 = - rexBuilder.makeCall( - SqlStdOperatorTable.EQUALS, - x, - y); - RexNode op2 = - rexBuilder.makeCall( - SqlStdOperatorTable.GREATER_THAN, - falseRex, - z); - RexNode and = - rexBuilder.makeCall( - SqlStdOperatorTable.AND, - op1, - op2); + RexNode op1 = equals(x, y); + RexNode op2 = greaterThan(falseRex, z); + RexNode and = and(op1, op2); check( Boolean.TRUE, and, @@ -396,6 +369,37 @@ public class RexTransformerTest { assertThat(leftJoinKeys.isEmpty(), is(true)); assertThat(rightJoinKeys.isEmpty(), is(true)); } + + /** Test case for {@link org.apache.calcite.rex.LogicVisitor}. */ + @Test public void testLogic() { + // x > FALSE AND ((y = z) IS NOT NULL) + final RexNode node = and(greaterThan(x, falseRex), isNotNull(equals(y, z))); + assertThat(deduceLogic(node, x, Logic.TRUE_FALSE), + is(Logic.TRUE_FALSE)); + assertThat(deduceLogic(node, y, Logic.TRUE_FALSE), + is(Logic.TRUE_FALSE_UNKNOWN)); + assertThat(deduceLogic(node, z, Logic.TRUE_FALSE), + is(Logic.TRUE_FALSE_UNKNOWN)); + + // TRUE means that a value of FALSE or UNKNOWN will kill the row + // (therefore we can safely use a semijoin) + assertThat(deduceLogic(and(x, y), x, Logic.TRUE), is(Logic.TRUE)); + assertThat(deduceLogic(and(x, y), y, Logic.TRUE), is(Logic.TRUE)); + assertThat(deduceLogic(and(x, and(y, z)), z, Logic.TRUE), is(Logic.TRUE)); + assertThat(deduceLogic(and(x, not(y)), x, Logic.TRUE), is(Logic.TRUE)); + assertThat(deduceLogic(and(x, not(y)), y, Logic.TRUE), + is(Logic.UNKNOWN_AS_TRUE)); + assertThat(deduceLogic(and(x, not(and(y, z))), z, Logic.TRUE), + is(Logic.UNKNOWN_AS_TRUE)); + assertThat(deduceLogic(or(x, y), x, Logic.TRUE), is(Logic.TRUE_FALSE)); + } + + private Logic deduceLogic(RexNode root, RexNode seek, Logic logic) { + final List<Logic> list = new ArrayList<>(); + LogicVisitor.collect(root, seek, logic, list); + assertThat(list.size(), is(1)); + return list.get(0); + } } // End RexTransformerTest.java http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java index c24e6af..2e18dc7 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java @@ -138,6 +138,20 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { "${plan}"); } + @Test public void testJoinOnInSubQuery() { + final String sql = "select * from emp left join dept\n" + + "on emp.empno = 1\n" + + "or dept.deptno in (select deptno from emp where empno > 5)"; + sql(sql).expand(false).convertsTo("${plan}"); + } + + @Test public void testJoinOnExists() { + final String sql = "select * from emp left join dept\n" + + "on emp.empno = 1\n" + + "or exists (select deptno from emp where empno > dept.deptno + 5)"; + sql(sql).expand(false).convertsTo("${plan}"); + } + @Test public void testJoinUsing() { check("SELECT * FROM emp JOIN dept USING (deptno)", "${plan}"); } @@ -408,7 +422,8 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { @Test public void testDuplicateColumnsInSubQuery() { String sql = "select \"e\" from (\n" + "select empno as \"e\", deptno as d, 1 as \"e\" from EMP)"; - tester.assertConvertsTo(sql, "${plan}"); + sql(sql) + .convertsTo("${plan}"); } @Test public void testOrder() { @@ -629,27 +644,64 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { } @Test public void testWithInsideWhereExists() { - tester.withDecorrelation(false).assertConvertsTo("select * from emp\n" - + "where exists (\n" - + " with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n" - + " select 1 from dept2 where deptno <= emp.deptno)", - "${plan}"); + final String sql = "select * from emp\n" + + "where exists (\n" + + " with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n" + + " select 1 from dept2 where deptno <= emp.deptno)"; + sql(sql) + .decorrelate(false) + .convertsTo("${plan}"); + } + + @Test public void testWithInsideWhereExistsRex() { + final String sql = "select * from emp\n" + + "where exists (\n" + + " with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n" + + " select 1 from dept2 where deptno <= emp.deptno)"; + sql(sql) + .decorrelate(false) + .expand(false) + .convertsTo("${plan}"); } @Test public void testWithInsideWhereExistsDecorrelate() { - tester.withDecorrelation(true).assertConvertsTo("select * from emp\n" - + "where exists (\n" - + " with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n" - + " select 1 from dept2 where deptno <= emp.deptno)", - "${plan}"); + final String sql = "select * from emp\n" + + "where exists (\n" + + " with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n" + + " select 1 from dept2 where deptno <= emp.deptno)"; + sql(sql) + .decorrelate(true) + .convertsTo("${plan}"); + } + + @Test public void testWithInsideWhereExistsDecorrelateRex() { + final String sql = "select * from emp\n" + + "where exists (\n" + + " with dept2 as (select * from dept where dept.deptno >= emp.deptno)\n" + + " select 1 from dept2 where deptno <= emp.deptno)"; + sql(sql) + .decorrelate(true) + .expand(false) + .convertsTo("${plan}"); } @Test public void testWithInsideScalarSubquery() { - check("select (\n" - + " with dept2 as (select * from dept where deptno > 10)" - + " select count(*) from dept2) as c\n" - + "from emp", - "${plan}"); + final String sql = "select (\n" + + " with dept2 as (select * from dept where deptno > 10)" + + " select count(*) from dept2) as c\n" + + "from emp"; + sql(sql) + .convertsTo("${plan}"); + } + + @Test public void testWithInsideScalarSubqueryRex() { + final String sql = "select (\n" + + " with dept2 as (select * from dept where deptno > 10)" + + " select count(*) from dept2) as c\n" + + "from emp"; + sql(sql) + .expand(false) + .convertsTo("${plan}"); } @Test public void testTableExtend() { @@ -719,7 +771,8 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { @Test public void testCollectionTableWithCursorParam() { tester.withDecorrelation(false).assertConvertsTo( - "select * from table(dedup(" + "cursor(select ename from emp)," + "select * from table(dedup(" + + "cursor(select ename from emp)," + " cursor(select name from dept), 'NAME'))", "${plan}"); } @@ -757,18 +810,28 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { } @Test public void testMultisetOfColumns() { - check( - "select 'abc',multiset[deptno,sal] from emp", - "${plan}"); + sql("select 'abc',multiset[deptno,sal] from emp") + .expand(true) + .convertsTo("${plan}"); + } + + @Test public void testMultisetOfColumnsRex() { + sql("select 'abc',multiset[deptno,sal] from emp") + .convertsTo("${plan}"); } @Test public void testCorrelationJoin() { - check( - "select *," - + " multiset(select * from emp where deptno=dept.deptno) " - + " as empset" - + " from dept", - "${plan}"); + final String sql = "select *,\n" + + " multiset(select * from emp where deptno=dept.deptno) as empset\n" + + "from dept"; + sql(sql).convertsTo("${plan}"); + } + + @Test public void testCorrelationJoinRex() { + final String sql = "select *,\n" + + " multiset(select * from emp where deptno=dept.deptno) as empset\n" + + "from dept"; + sql(sql).expand(false).convertsTo("${plan}"); } @Test public void testExists() { @@ -783,24 +846,49 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { "${plan}"); } + @Test public void testNotExistsCorrelated() { + final String sql = "select * from emp where not exists (\n" + + " select 1 from dept where emp.deptno=dept.deptno)"; + tester.withDecorrelation(false).assertConvertsTo(sql, "${plan}"); + } + @Test public void testExistsCorrelatedDecorrelate() { - tester.withDecorrelation(true).assertConvertsTo( - "select*from emp where exists (select 1 from dept where emp.deptno=dept.deptno)", - "${plan}"); + final String sql = "select*from emp where exists (\n" + + " select 1 from dept where emp.deptno=dept.deptno)"; + tester.withDecorrelation(true).assertConvertsTo(sql, "${plan}"); + } + + @Test public void testExistsCorrelatedDecorrelateRex() { + final String sql = "select*from emp where exists (\n" + + " select 1 from dept where emp.deptno=dept.deptno)"; + tester.withDecorrelation(true).withExpand(false) + .assertConvertsTo(sql, "${plan}"); } @Test public void testExistsCorrelatedLimit() { - tester.withDecorrelation(false).assertConvertsTo( - "select*from emp where exists (\n" - + " select 1 from dept where emp.deptno=dept.deptno limit 1)", - "${plan}"); + final String sql = "select*from emp where exists (\n" + + " select 1 from dept where emp.deptno=dept.deptno limit 1)"; + sql(sql) + .decorrelate(false) + .convertsTo("${plan}"); } @Test public void testExistsCorrelatedLimitDecorrelate() { - tester.withDecorrelation(true).assertConvertsTo( - "select*from emp where exists (\n" - + " select 1 from dept where emp.deptno=dept.deptno limit 1)", - "${plan}"); + final String sql = "select*from emp where exists (\n" + + " select 1 from dept where emp.deptno=dept.deptno limit 1)"; + sql(sql) + .decorrelate(true) + .expand(true) + .convertsTo("${plan}"); + } + + @Test public void testExistsCorrelatedLimitDecorrelateRex() { + final String sql = "select*from emp where exists (\n" + + " select 1 from dept where emp.deptno=dept.deptno limit 1)"; + sql(sql) + .decorrelate(true) + .expand(false) + .convertsTo("${plan}"); } @Test public void testInValueListShort() { @@ -816,60 +904,166 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { } @Test public void testInUncorrelatedSubquery() { - check( - "select empno from emp where deptno in" - + " (select deptno from dept)", - "${plan}"); + final String sql = "select empno from emp where deptno in" + + " (select deptno from dept)"; + sql(sql) + .convertsTo("${plan}"); + } + + @Test public void testInUncorrelatedSubqueryRex() { + final String sql = "select empno from emp where deptno in" + + " (select deptno from dept)"; + sql(sql) + .expand(false) + .convertsTo("${plan}"); + } + + @Test public void testCompositeInUncorrelatedSubqueryRex() { + final String sql = "select empno from emp where (empno, deptno) in" + + " (select deptno - 10, deptno from dept)"; + sql(sql) + .expand(false) + .convertsTo("${plan}"); } @Test public void testNotInUncorrelatedSubquery() { - check( - "select empno from emp where deptno not in" - + " (select deptno from dept)", - "${plan}"); + final String sql = "select empno from emp where deptno not in" + + " (select deptno from dept)"; + sql(sql) + .convertsTo("${plan}"); + } + + @Test public void testNotInUncorrelatedSubqueryRex() { + final String sql = "select empno from emp where deptno not in" + + " (select deptno from dept)"; + sql(sql) + .expand(false) + .convertsTo("${plan}"); + } + + @Test public void testWhereInCorrelated() { + final String sql = "select empno from emp as e\n" + + "join dept as d using (deptno)\n" + + "where e.sal in (\n" + + " select e2.sal from emp as e2 where e2.deptno > e.deptno)"; + sql(sql) + .expand(false) + .convertsTo("${plan}"); } @Test public void testInUncorrelatedSubqueryInSelect() { // In the SELECT clause, the value of IN remains in 3-valued logic // -- it's not forced into 2-valued by the "... IS TRUE" wrapper as in the // WHERE clause -- so the translation is more complicated. - check( - "select name, deptno in (\n" - + " select case when true then deptno else null end from emp)\n" - + "from dept", - "${plan}"); + final String sql = "select name, deptno in (\n" + + " select case when true then deptno else null end from emp)\n" + + "from dept"; + sql(sql) + .convertsTo("${plan}"); + } + + @Test public void testInUncorrelatedSubqueryInSelectRex() { + // In the SELECT clause, the value of IN remains in 3-valued logic + // -- it's not forced into 2-valued by the "... IS TRUE" wrapper as in the + // WHERE clause -- so the translation is more complicated. + final String sql = "select name, deptno in (\n" + + " select case when true then deptno else null end from emp)\n" + + "from dept"; + sql(sql) + .expand(false) + .convertsTo("${plan}"); + } + + @Test public void testInUncorrelatedSubqueryInHavingRex() { + final String sql = "select sum(sal) as s\n" + + "from emp\n" + + "group by deptno\n" + + "having count(*) > 2\n" + + "and deptno in (\n" + + " select case when true then deptno else null end from emp)"; + sql(sql).expand(false).convertsTo("${plan}"); + } + + @Test public void testUncorrelatedScalarSubqueryInOrderRex() { + final String sql = "select ename\n" + + "from emp\n" + + "order by (select case when true then deptno else null end from emp) desc,\n" + + " ename"; + sql(sql).expand(false).convertsTo("${plan}"); + } + + @Test public void testUncorrelatedScalarSubqueryInGroupOrderRex() { + final String sql = "select sum(sal) as s\n" + + "from emp\n" + + "group by deptno\n" + + "order by (select case when true then deptno else null end from emp) desc,\n" + + " count(*)"; + sql(sql).expand(false).convertsTo("${plan}"); + } + + @Test public void testUncorrelatedScalarSubqueryInAggregateRex() { + final String sql = "select sum((select min(deptno) from emp)) as s\n" + + "from emp\n" + + "group by deptno\n"; + sql(sql).expand(false).convertsTo("${plan}"); } /** Plan should be as {@link #testInUncorrelatedSubqueryInSelect}, but with * an extra NOT. Both queries require 3-valued logic. */ @Test public void testNotInUncorrelatedSubqueryInSelect() { - check( - "select empno, deptno not in (\n" - + " select case when true then deptno else null end from dept)\n" - + "from emp", - "${plan}"); + final String sql = "select empno, deptno not in (\n" + + " select case when true then deptno else null end from dept)\n" + + "from emp"; + sql(sql) + .convertsTo("${plan}"); + } + + @Test public void testNotInUncorrelatedSubqueryInSelectRex() { + final String sql = "select empno, deptno not in (\n" + + " select case when true then deptno else null end from dept)\n" + + "from emp"; + sql(sql) + .expand(false) + .convertsTo("${plan}"); } /** Since 'deptno NOT IN (SELECT deptno FROM dept)' can not be null, we * generate a simpler plan. */ @Test public void testNotInUncorrelatedSubqueryInSelectNotNull() { - check( - "select empno, deptno not in (\n" - + " select deptno from dept)\n" - + "from emp", - "${plan}"); + final String sql = "select empno, deptno not in (\n" + + " select deptno from dept)\n" + + "from emp"; + sql(sql) + .convertsTo("${plan}"); + } + + @Test public void testNotInUncorrelatedSubqueryInSelectNotNullRex() { + final String sql = "select empno, deptno not in (\n" + + " select deptno from dept)\n" + + "from emp"; + sql(sql) + .expand(false) + .convertsTo("${plan}"); } @Test public void testUnnestSelect() { - check( - "select*from unnest(select multiset[deptno] from dept)", - "${plan}"); + final String sql = "select*from unnest(select multiset[deptno] from dept)"; + sql(sql).expand(true).convertsTo("${plan}"); + } + + @Test public void testUnnestSelectRex() { + final String sql = "select*from unnest(select multiset[deptno] from dept)"; + sql(sql).expand(false).convertsTo("${plan}"); } @Test public void testJoinUnnest() { - check( - "select*from dept as d, unnest(multiset[d.deptno * 2])", - "${plan}"); + final String sql = "select*from dept as d, unnest(multiset[d.deptno * 2])"; + sql(sql).convertsTo("${plan}"); + } + + @Test public void testJoinUnnestRex() { + final String sql = "select*from dept as d, unnest(multiset[d.deptno * 2])"; + sql(sql).expand(false).convertsTo("${plan}"); } @Test public void testLateral() { @@ -879,9 +1073,16 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { } @Test public void testLateralDecorrelate() { - tester.withDecorrelation(true).assertConvertsTo( - "select * from emp, LATERAL (select * from dept where emp.deptno=dept.deptno)", - "${plan}"); + final String sql = "select * from emp,\n" + + " LATERAL (select * from dept where emp.deptno=dept.deptno)"; + tester.withDecorrelation(true).withExpand(true) + .assertConvertsTo(sql, "${plan}"); + } + + @Test public void testLateralDecorrelateRex() { + final String sql = "select * from emp,\n" + + " LATERAL (select * from dept where emp.deptno=dept.deptno)"; + tester.withDecorrelation(true).assertConvertsTo(sql, "${plan}"); } @Test public void testNestedCorrelations() { @@ -894,12 +1095,22 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { } @Test public void testNestedCorrelationsDecorrelated() { - tester.withDecorrelation(true).assertConvertsTo( - "select * from (select 2+deptno d2, 3+deptno d3 from emp) e\n" - + " where exists (select 1 from (select deptno+1 d1 from dept) d\n" - + " where d1=e.d2 and exists (select 2 from (select deptno+4 d4, deptno+5 d5, deptno+6 d6 from dept)\n" - + " where d4=d.d1 and d5=d.d1 and d6=e.d3))", - "${plan}"); + final String sql = "select *\n" + + "from (select 2+deptno d2, 3+deptno d3 from emp) e\n" + + " where exists (select 1 from (select deptno+1 d1 from dept) d\n" + + " where d1=e.d2 and exists (select 2 from (select deptno+4 d4, deptno+5 d5, deptno+6 d6 from dept)\n" + + " where d4=d.d1 and d5=d.d1 and d6=e.d3))"; + tester.withDecorrelation(true).withExpand(true) + .assertConvertsTo(sql, "${plan}"); + } + + @Test public void testNestedCorrelationsDecorrelatedRex() { + final String sql = "select *\n" + + "from (select 2+deptno d2, 3+deptno d3 from emp) e\n" + + " where exists (select 1 from (select deptno+1 d1 from dept) d\n" + + " where d1=e.d2 and exists (select 2 from (select deptno+4 d4, deptno+5 d5, deptno+6 d6 from dept)\n" + + " where d4=d.d1 and d5=d.d1 and d6=e.d3))"; + tester.withDecorrelation(true).assertConvertsTo(sql, "${plan}"); } @Test public void testElement() { @@ -1432,11 +1643,21 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { * When de-correlating, push join condition into subquery</a>. */ @Test public void testCorrelationScalarAggAndFilter() { - tester.withDecorrelation(true).assertConvertsTo( - "SELECT e1.empno FROM emp e1, dept d1 where e1.deptno = d1.deptno\n" - + "and e1.deptno < 10 and d1.deptno < 15\n" - + "and e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno)", - "${plan}"); + final String sql = "SELECT e1.empno\n" + + "FROM emp e1, dept d1 where e1.deptno = d1.deptno\n" + + "and e1.deptno < 10 and d1.deptno < 15\n" + + "and e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno)"; + tester.withDecorrelation(true).withExpand(true) + .assertConvertsTo(sql, "${plan}"); + } + + @Test public void testCorrelationScalarAggAndFilterRex() { + final String sql = "SELECT e1.empno\n" + + "FROM emp e1, dept d1 where e1.deptno = d1.deptno\n" + + "and e1.deptno < 10 and d1.deptno < 15\n" + + "and e1.sal > (select avg(sal) from emp e2 where e1.empno = e2.empno)"; + tester.withDecorrelation(true).withExpand(false) + .assertConvertsTo(sql, "${plan}"); } /** @@ -1445,11 +1666,21 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { * When de-correlating, push join condition into subquery</a>. */ @Test public void testCorrelationExistsAndFilter() { - tester.withDecorrelation(true).assertConvertsTo( - "SELECT e1.empno FROM emp e1, dept d1 where e1.deptno = d1.deptno\n" - + "and e1.deptno < 10 and d1.deptno < 15\n" - + "and exists (select * from emp e2 where e1.empno = e2.empno)", - "${plan}"); + final String sql = "SELECT e1.empno\n" + + "FROM emp e1, dept d1 where e1.deptno = d1.deptno\n" + + "and e1.deptno < 10 and d1.deptno < 15\n" + + "and exists (select * from emp e2 where e1.empno = e2.empno)"; + tester.withDecorrelation(true).withExpand(true) + .assertConvertsTo(sql, "${plan}"); + } + + @Test public void testCorrelationExistsAndFilterRex() { + final String sql = "SELECT e1.empno\n" + + "FROM emp e1, dept d1 where e1.deptno = d1.deptno\n" + + "and e1.deptno < 10 and d1.deptno < 15\n" + + "and exists (select * from emp e2 where e1.empno = e2.empno)"; + tester.withDecorrelation(true) + .assertConvertsTo(sql, "${plan}"); } /** @@ -1484,9 +1715,17 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { /** Allows fluent testing. */ public class Sql { private final String sql; + private final boolean expand; + private final boolean decorrelate; Sql(String sql) { + this(sql, true, true); + } + + Sql(String sql, boolean expand, boolean decorrelate) { this.sql = sql; + this.expand = expand; + this.decorrelate = decorrelate; } public void ok() { @@ -1494,7 +1733,17 @@ public class SqlToRelConverterTest extends SqlToRelTestBase { } public void convertsTo(String plan) { - tester.assertConvertsTo(sql, plan); + tester.withExpand(expand) + .withDecorrelation(decorrelate) + .assertConvertsTo(sql, plan, false); + } + + public Sql expand(boolean expand) { + return new Sql(sql, expand, decorrelate); + } + + public Sql decorrelate(boolean decorrelate) { + return new Sql(sql, expand, decorrelate); } } } http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java b/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java index e4d1394..9cb4240 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java +++ b/core/src/test/java/org/apache/calcite/test/SqlToRelTestBase.java @@ -92,7 +92,7 @@ public abstract class SqlToRelTestBase { } protected Tester createTester() { - return new TesterImpl(getDiffRepos(), true, false, null); + return new TesterImpl(getDiffRepos(), false, false, true, null); } /** @@ -209,6 +209,13 @@ public abstract class SqlToRelTestBase { /** Returns a tester that optionally decorrelates queries. */ Tester withDecorrelation(boolean enable); + /** Returns a tester that optionally expands sub-queries. + * If {@code expand} is false, the plan contains a + * {@link org.apache.calcite.rex.RexSubQuery} for each sub-query. + * + * @see Prepare#THREAD_EXPAND */ + Tester withExpand(boolean expand); + Tester withCatalogReaderFactory( Function<RelDataTypeFactory, Prepare.CatalogReader> factory); @@ -453,6 +460,7 @@ public abstract class SqlToRelTestBase { private final DiffRepository diffRepos; private final boolean enableDecorrelate; private final boolean enableTrim; + private final boolean enableExpand; private final Function<RelDataTypeFactory, Prepare.CatalogReader> catalogReaderFactory; private RelDataTypeFactory typeFactory; @@ -463,15 +471,17 @@ public abstract class SqlToRelTestBase { * @param diffRepos Diff repository * @param enableDecorrelate Whether to decorrelate * @param enableTrim Whether to trim unused fields + * @param enableExpand Whether to expand sub-queries * @param catalogReaderFactory Function to create catalog reader, or null */ protected TesterImpl(DiffRepository diffRepos, boolean enableDecorrelate, - boolean enableTrim, + boolean enableTrim, boolean enableExpand, Function<RelDataTypeFactory, Prepare.CatalogReader> catalogReaderFactory) { this.diffRepos = diffRepos; this.enableDecorrelate = enableDecorrelate; this.enableTrim = enableTrim; + this.enableExpand = enableExpand; this.catalogReaderFactory = catalogReaderFactory; } @@ -495,6 +505,7 @@ public abstract class SqlToRelTestBase { catalogReader, typeFactory); converter.setTrimUnusedFields(true); + converter.setExpand(enableExpand); final SqlNode validatedQuery = validator.validate(sqlQuery); RelRoot root = converter.convertQuery(validatedQuery, false, true); @@ -645,19 +656,30 @@ public abstract class SqlToRelTestBase { } public TesterImpl withDecorrelation(boolean enable) { - return this.enableDecorrelate == enable ? this - : new TesterImpl(diffRepos, enable, enableTrim, catalogReaderFactory); + return this.enableDecorrelate == enable + ? this + : new TesterImpl(diffRepos, enable, enableTrim, enableExpand, + catalogReaderFactory); } public Tester withTrim(boolean enable) { - return this.enableTrim == enable ? this - : new TesterImpl(diffRepos, enableDecorrelate, enable, + return this.enableTrim == enable + ? this + : new TesterImpl(diffRepos, enableDecorrelate, enable, enableExpand, + catalogReaderFactory); + } + + public Tester withExpand(boolean expand) { + return this.enableExpand == expand + ? this + : new TesterImpl(diffRepos, enableDecorrelate, enableTrim, expand, catalogReaderFactory); } public Tester withCatalogReaderFactory( Function<RelDataTypeFactory, Prepare.CatalogReader> factory) { - return new TesterImpl(diffRepos, enableDecorrelate, false, factory); + return new TesterImpl(diffRepos, enableDecorrelate, false, enableExpand, + factory); } } http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java index 2f4f60e..2584dd2 100644 --- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java +++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java @@ -5362,6 +5362,43 @@ public class SqlValidatorTest extends SqlValidatorTestCase { "require alias"); } + @Test public void testJoinOnIn() { + final String sql = "select * from emp join dept\n" + + "on dept.deptno in (select deptno from emp)"; + sql(sql).ok(); + } + + @Test public void testJoinOnInCorrelated() { + final String sql = "select * from emp as e join dept\n" + + "on dept.deptno in (select deptno from emp where deptno < e.deptno)"; + sql(sql).ok(); + } + + @Test public void testJoinOnInCorrelatedFails() { + final String sql = "select * from emp as e join dept as d\n" + + "on d.deptno in (select deptno from emp where deptno < d.^empno^)"; + sql(sql).fails("Column 'EMPNO' not found in table 'D'"); + } + + @Test public void testJoinOnExistsCorrelated() { + final String sql = "select * from emp as e join dept\n" + + "on exists (select 1, 2 from emp where deptno < e.deptno)"; + sql(sql).ok(); + } + + @Test public void testJoinOnScalarCorrelated() { + final String sql = "select * from emp as e join dept d\n" + + "on d.deptno = (select 1 from emp where deptno < e.deptno)"; + sql(sql).ok(); + } + + @Test public void testJoinOnScalarFails() { + final String sql = "select * from emp as e join dept d\n" + + "on d.deptno = (^select 1, 2 from emp where deptno < e.deptno^)"; + sql(sql).fails( + "(?s)Cannot apply '\\$SCALAR_QUERY' to arguments of type '\\$SCALAR_QUERY\\(<RECORDTYPE\\(INTEGER EXPR\\$0, INTEGER EXPR\\$1\\)>\\)'\\. Supported form\\(s\\).*"); + } + @Test public void testJoinUsingThreeWay() { check("select *\n" + "from emp as e\n" http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/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 bfb1e75..101a6ae 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 @@ -31,38 +31,37 @@ public class EnumerableCorrelateTest { tester(true, new JdbcTest.HrSchema()) .query( "select empid, name from emps e where exists (select 1 from depts d where d.deptno=e.deptno)") - .explainContains( - "EnumerableCalc(expr#0..4=[{inputs}], empid=[$t0], name=[$t2])\n" - + " EnumerableSemiJoin(condition=[=($1, $5)], joinType=[inner])\n" - + " EnumerableTableScan(table=[[s, emps]])\n" - + " EnumerableCalc(expr#0..4=[{inputs}], expr#5=[true], deptno0=[$t0], $f0=[$t5])\n" - + " EnumerableJoin(condition=[=($0, $1)], joinType=[inner])\n" - + " EnumerableAggregate(group=[{1}])\n" - + " EnumerableTableScan(table=[[s, emps]])\n" + .explainContains("" + + "EnumerableCalc(expr#0..2=[{inputs}], empid=[$t0], name=[$t2])\n" + + " 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]])") .returnsUnordered( "empid=100; name=Bill", "empid=110; name=Theodore", "empid=150; name=Sebastian"); - } @Test public void simpleCorrelate() { tester(false, new JdbcTest.HrSchema()) .query( "select empid, name from emps e where exists (select 1 from depts d where d.deptno=e.deptno)") - .explainContains( - "EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t5)], empid=[$t0], name=[$t2], $condition=[$t6])\n" - + " EnumerableCorrelate(correlation=[$cor0], joinType=[LEFT], requiredColumns=[{1}])\n" + .explainContains("" + + "EnumerableCalc(expr#0..5=[{inputs}], empid=[$t0], name=[$t2])\n" + + " EnumerableCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{1}])\n" + " EnumerableTableScan(table=[[s, emps]])\n" - + " EnumerableAggregate(group=[{}], agg#0=[MIN($0)])\n" - + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], expr#5=[$cor0], expr#6=[$t5.deptno], expr#7=[=($t0, $t6)], $f0=[$t4], $condition=[$t7])\n" + + " EnumerableAggregate(group=[{0}])\n" + + " EnumerableCalc(expr#0..3=[{inputs}], expr#4=[true], expr#5=[$cor0], expr#6=[$t5.deptno], expr#7=[=($t0, $t6)], i=[$t4], $condition=[$t7])\n" + " EnumerableTableScan(table=[[s, depts]])") .returnsUnordered( "empid=100; name=Bill", "empid=110; name=Theodore", "empid=150; name=Sebastian"); - } private CalciteAssert.AssertThat tester(boolean forceDecorrelate, http://git-wip-us.apache.org/repos/asf/calcite/blob/505a9064/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 c9c9342..e45b82c 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -217,6 +217,19 @@ LogicalProject(SAL=[$0]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(SAL=[$0]) + SemiJoin(condition=[=($1, $2)], joinType=[inner]) + LogicalFilter(condition=[=($1, 200)]) + LogicalProject(SAL=[$5], DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$1]) + LogicalFilter(condition=[=($0, 100)]) + LogicalProject(SAL=[$5], DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> </TestCase> <TestCase name="testFullOuterJoinSimplificationToLeftOuter"> <Resource name="sql"> @@ -405,6 +418,15 @@ LogicalProject(DDEPTNO=[$0], DNAME=[$1], C=[$2]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DDEPTNO=[$0], DNAME=[$1], C=[$2]) + LogicalProject(DDEPTNO=[CASE($2, null, $0)], DNAME=[CASE($3, null, $1)], C=[$4]) + LogicalFilter(condition=[=(CASE($3, null, $1), 'Charlie')]) + LogicalAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}, {}]], indicator=[true], C=[COUNT()]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> </TestCase> <TestCase name="testPushFilterPastAggWithGroupingSets2"> <Resource name="sql"> @@ -623,6 +645,13 @@ LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[8]) + LogicalFilter(condition=[AND(=($0, 7), =($0, 8))]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> </TestCase> <TestCase name="testReduceConstantsEliminatesFilter"> <Resource name="sql"> @@ -3140,6 +3169,14 @@ LogicalProject(ENAME=[$0], R=[$1]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(ENAME=[$0], R=[$1]) + LogicalFilter(condition=[<($1, 2)]) + LogicalProject(ENAME=[$1], R=[RANK() OVER (PARTITION BY $7 ORDER BY $5 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> </TestCase> <TestCase name="testPushFilterWithRankExpr"> <Resource name="sql"> @@ -3157,6 +3194,14 @@ LogicalProject(ENAME=[$0], R=[$1]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(ENAME=[$0], R=[$1]) + LogicalFilter(condition=[<($1, 2)]) + LogicalProject(ENAME=[$1], R=[+(RANK() OVER (PARTITION BY $7 ORDER BY $5 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 1)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> </TestCase> <TestCase name="testDistinctCount1"> <Resource name="sql"> @@ -3483,6 +3528,13 @@ LogicalProject(EXPR$0=[$0], EXPR$1=[$1]) LogicalValues(tuples=[[{ 1, 2 }]]) ]]> </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EXPR$0=[$0], EXPR$1=[$1]) + LogicalFilter(condition=[>(+(1, 2), +(3, null))]) + LogicalValues(tuples=[[{ 1, 2 }]]) +]]> + </Resource> </TestCase> <TestCase name="testExpressionInWindowFunction"> <Resource name="sql"> @@ -3580,6 +3632,16 @@ LogicalAggregate(group=[{0, 9}]) LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalAggregate(group=[{0, 9}]) + LogicalJoin(condition=[<($0, $9)], joinType=[inner]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[=($0, 10)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> </TestCase> <TestCase name="testPushAggregateSumThroughJoin"> <Resource name="sql"> @@ -3927,6 +3989,15 @@ LogicalProject(DEPTNO=[$0]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) ]]> </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(DEPTNO=[$0]) + LogicalFilter(condition=[>($1, 1)]) + LogicalAggregate(group=[{0}], agg#0=[COUNT()]) + LogicalProject(DEPTNO=[$7]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> </TestCase> <TestCase name="testPushAggregateFunctionsThroughJoin"> <Resource name="sql"> @@ -4042,6 +4113,34 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ ]]> </Resource> </TestCase> + <TestCase name="testExpandProjectScalar"> + <Resource name="sql"> + <![CDATA[select empno, + (select deptno from sales.emp where empno < 20) as d +from sales.emp]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0], D=[$SCALAR_QUERY({ +LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0], D=[$9]) + LogicalJoin(condition=[true], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testSortJoinTranspose2"> <Resource name="sql"> <![CDATA[select * from sales.emp e right join ( @@ -4086,6 +4185,16 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$ LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) ]]> </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10]) + LogicalSort(sort0=[$5], sort1=[$10], dir0=[ASC], dir1=[ASC], fetch=[10]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalProject(DEPTNO=[$0], NAME=[$1]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> </TestCase> <TestCase name="testSortJoinTranspose4"> <Resource name="sql"> @@ -4148,6 +4257,375 @@ LogicalSort(sort0=[$0], dir0=[ASC], fetch=[10]) ]]> </Resource> </TestCase> + <TestCase name="testExpandProjectIn"> + <Resource name="sql"> + <![CDATA[select empno, + deptno in (select deptno from sales.emp where empno < 20) as d +from sales.emp]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0], D=[IN($7, { +LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0], D=[CASE(IS NOT NULL($10), true, false)]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}]) + LogicalProject(DEPTNO=[$0], i=[true]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandFilterInComposite"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp +where (empno, deptno) in ( + select empno, deptno from sales.emp where empno < 20) +or emp.sal < 100]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalFilter(condition=[OR(IN($0, $7, { +LogicalProject(EMPNO=[$0], DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), <($5, 100))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[OR(CASE(IS NOT NULL($11), true, false), <($5, 100))]) + LogicalJoin(condition=[AND(=($0, $9), =($7, $10))], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1, 2}]) + LogicalProject(EMPNO=[$0], DEPTNO=[$1], i=[true]) + LogicalProject(EMPNO=[$0], DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandJoinIn"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp left join sales.dept +on emp.deptno in (select deptno from sales.emp where empno < 20)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalJoin(condition=[IN($7, { +LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![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]) + LogicalJoin(condition=[true], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalJoin(condition=[=($7, $11)], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0}]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandFilterIn"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp +where deptno in (select deptno from sales.emp where empno < 20) +or emp.sal < 100]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalFilter(condition=[OR(IN($7, { +LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), <($5, 100))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[OR(CASE(IS NOT NULL($10), true, false), <($5, 100))]) + LogicalJoin(condition=[=($7, $9)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}]) + LogicalProject(DEPTNO=[$0], i=[true]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandJoinExists"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp left join sales.dept +on exists (select deptno from sales.emp where empno < 20)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalJoin(condition=[EXISTS({ +LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![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]) + LogicalJoin(condition=[true], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0}]) + LogicalProject(i=[true]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandProjectExists"> + <Resource name="sql"> + <![CDATA[select empno, + exists (select deptno from sales.emp where empno < 20) as d +from sales.emp]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0], D=[EXISTS({ +LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0], D=[CASE(IS NOT NULL($9), true, false)]) + LogicalJoin(condition=[true], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0}]) + LogicalProject(i=[true]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandJoinScalar"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp left join sales.dept +on (select deptno from sales.emp where empno < 20) + < (select deptno from sales.emp where empno > 100)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalJoin(condition=[<($SCALAR_QUERY({ +LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), $SCALAR_QUERY({ +LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[>($0, 100)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}))], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![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]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], DEPTNO0=[$9], NAME=[$10], $f0=[$11]) + LogicalJoin(condition=[<($2, $3)], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalJoin(condition=[true], joinType=[left]) + LogicalJoin(condition=[true], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[>($0, 100)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandFilterExists"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp +where exists (select deptno from sales.emp where empno < 20) +or emp.sal < 100]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalFilter(condition=[OR(EXISTS({ +LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), <($5, 100))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[OR(CASE(IS NOT NULL($9), true, false), <($5, 100))]) + LogicalJoin(condition=[true], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0}]) + LogicalProject(i=[true]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandProjectInComposite"> + <Resource name="sql"> + <![CDATA[select empno, (empno, deptno) in ( + select empno, deptno from sales.emp where empno < 20) as d +from sales.emp]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0], D=[IN($0, $7, { +LogicalProject(EMPNO=[$0], DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0], D=[CASE(IS NOT NULL($11), true, false)]) + LogicalJoin(condition=[AND(=($0, $9), =($7, $10))], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1, 2}]) + LogicalProject(EMPNO=[$0], DEPTNO=[$1], i=[true]) + LogicalProject(EMPNO=[$0], DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandJoinInComposite"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp left join sales.dept +on (emp.empno, dept.deptno) in ( + select empno, deptno from sales.emp where empno < 20)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalJoin(condition=[IN($0, $9, { +LogicalProject(EMPNO=[$0], DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) +]]> + </Resource> + <Resource name="planAfter"> + <![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]) + LogicalJoin(condition=[true], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalJoin(condition=[AND(=($0, $11), =($9, $12))], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0, 1}]) + LogicalProject(EMPNO=[$0], DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandFilterScalar"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp +where (select deptno from sales.emp where empno < 20) + < (select deptno from sales.emp where empno > 100) +or emp.sal < 100]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalFilter(condition=[OR(<($SCALAR_QUERY({ +LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), $SCALAR_QUERY({ +LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[>($0, 100)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})), <($5, 100))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + 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=[OR(<($9, $10), <($5, 100))]) + LogicalJoin(condition=[true], joinType=[left]) + LogicalJoin(condition=[true], joinType=[left]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)]) + LogicalProject(DEPTNO=[$7]) + LogicalFilter(condition=[>($0, 100)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> <TestCase name="testReduceConstantsDup2"> <Resource name="sql"> <![CDATA[select * @@ -4319,4 +4797,232 @@ LogicalProject(JOB=[$1]) ]]> </Resource> </TestCase> + <TestCase name="testExpandProjectInNullable"> + <Resource name="sql"> + <![CDATA[with e2 as ( + select empno, case when true then deptno else null end as deptno + from sales.emp) +select empno, + deptno in (select deptno from e2 where empno < 20) as d +from e2]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0], D=[IN($1, { +LogicalProject(DEPTNO=[$1]) + LogicalFilter(condition=[<($0, 20)]) + LogicalProject(EMPNO=[$0], DEPTNO=[CASE(true, CAST($7):INTEGER, null)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})]) + LogicalProject(EMPNO=[$0], DEPTNO=[CASE(true, CAST($7):INTEGER, null)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0], D=[CASE(=($2, 0), false, IS NOT NULL($5), true, IS NULL($1), null, <($3, $2), null, false)]) + LogicalJoin(condition=[=($1, $4)], joinType=[left]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalProject(EMPNO=[$0], DEPTNO=[CASE(true, CAST($7):INTEGER, null)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)]) + LogicalProject(DEPTNO=[$1]) + LogicalFilter(condition=[<($0, 20)]) + LogicalProject(EMPNO=[$0], DEPTNO=[CASE(true, CAST($7):INTEGER, null)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}]) + LogicalProject(DEPTNO=[$0], i=[true]) + LogicalProject(DEPTNO=[$1]) + LogicalFilter(condition=[<($0, 20)]) + LogicalProject(EMPNO=[$0], DEPTNO=[CASE(true, CAST($7):INTEGER, null)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandFilterIn3Value"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp +where empno + < case deptno in (select case when true then deptno else null end + from sales.emp where empno < 20) + when true then 10 + when false then 20 + else 30 + end]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalFilter(condition=[<($0, CASE(=(IN($7, { +LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), true), 10, =(IN($7, { +LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), false), 20, 30))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[<($0, CASE(=(CASE(=($9, 0), false, IS NOT NULL($12), true, <($10, $9), null, false), true), 10, =(CASE(=($9, 0), false, IS NOT NULL($12), true, <($10, $9), null, false), false), 20, 30))]) + LogicalJoin(condition=[=($7, $11)], joinType=[left]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)]) + LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}]) + LogicalProject(EXPR$0=[$0], i=[true]) + LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandFilterExists3Value"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp +where empno + < case exists (select deptno from sales.emp where empno < 20) + when true then 10 when false then 20 else 30 end]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalFilter(condition=[<($0, CASE(=(IN($7, { +LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), true), 10, =(IN($7, { +LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), false), 20, 30))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[<($0, CASE(=(CASE(=($9, 0), false, IS NOT NULL($12), true, <($10, $9), null, false), true), 10, =(CASE(=($9, 0), false, IS NOT NULL($12), true, <($10, $9), null, false), false), 20, 30))]) + LogicalJoin(condition=[=($7, $11)], joinType=[left]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{}], c=[COUNT()], ck=[COUNT($0)]) + LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0, 1}]) + LogicalProject(EXPR$0=[$0], i=[true]) + LogicalProject(EXPR$0=[CASE(true, CAST($7):INTEGER, null)]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandFilterExistsSimple"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp +where exists (select deptno from sales.emp where empno < 20)]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalFilter(condition=[EXISTS({ +LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +})]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0}]) + LogicalProject(i=[true]) + LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> + <TestCase name="testExpandFilterExistsSimpleAnd"> + <Resource name="sql"> + <![CDATA[select empno +from sales.emp +where exists (select deptno from sales.emp where empno < 20) +and emp.sal < 100]]> + </Resource> + <Resource name="planBefore"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalFilter(condition=[AND(EXISTS({ +LogicalFilter(condition=[<($0, 20)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +}), <($5, 100))]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + <Resource name="planAfter"> + <![CDATA[ +LogicalProject(EMPNO=[$0]) + LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8]) + LogicalFilter(condition=[<($5, 100)]) + LogicalJoin(condition=[true], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalAggregate(group=[{0}]) + LogicalProject(i=[true]) + LogicalFilter(condition=[<($0, 20)]) + 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="planBefore"> + <![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> + <Resource name="planAfter"> + <![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]) + LogicalFilter(condition=[=($5, $11)]) + LogicalCorrelate(correlation=[$cor0], joinType=[INNER], requiredColumns=[{7}]) + LogicalJoin(condition=[=($7, $9)], joinType=[inner]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) + LogicalTableScan(table=[[CATALOG, SALES, DEPT]]) + LogicalAggregate(group=[{0}]) + LogicalProject(SAL=[$5]) + LogicalFilter(condition=[>($7, $cor0.DEPTNO)]) + LogicalTableScan(table=[[CATALOG, SALES, EMP]]) +]]> + </Resource> + </TestCase> </Root>
