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