Repository: calcite Updated Branches: refs/heads/master 79af1c9ba -> ac1b63d18
[CALCITE-2555] RexSimplify: >=(..., null) could be simplified to null (pengzhiwei) fixes #831 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/ac1b63d1 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/ac1b63d1 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/ac1b63d1 Branch: refs/heads/master Commit: ac1b63d182dc1871b5215eb9dde7052ab771c284 Parents: 79af1c9 Author: pengzhiwei <[email protected]> Authored: Mon Sep 17 12:32:55 2018 +0800 Committer: Vladimir Sitnikov <[email protected]> Committed: Wed Sep 19 12:26:44 2018 +0300 ---------------------------------------------------------------------- .../java/org/apache/calcite/plan/Strong.java | 5 +- .../org/apache/calcite/rex/RexInterpreter.java | 9 ++ .../org/apache/calcite/rex/RexSimplify.java | 14 ++- .../apache/calcite/test/RelOptRulesTest.java | 2 +- .../calcite/test/RexProgramBuilderBase.java | 8 ++ .../org/apache/calcite/test/RexProgramTest.java | 18 ++++ .../org/apache/calcite/test/RelOptRulesTest.xml | 3 +- core/src/test/resources/sql/sub-query.iq | 104 +++++++------------ 8 files changed, 89 insertions(+), 74 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/ac1b63d1/core/src/main/java/org/apache/calcite/plan/Strong.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/plan/Strong.java b/core/src/main/java/org/apache/calcite/plan/Strong.java index 39a4515..0c2bb9e 100644 --- a/core/src/main/java/org/apache/calcite/plan/Strong.java +++ b/core/src/main/java/org/apache/calcite/plan/Strong.java @@ -31,7 +31,6 @@ import java.util.ArrayList; import java.util.EnumMap; import java.util.List; import java.util.Map; -import java.util.Objects; /** Utilities for strong predicates. * @@ -87,7 +86,7 @@ public class Strong { /** Returns how to deduce whether a particular kind of expression is null, * given whether its arguments are null. */ public static Policy policy(SqlKind kind) { - return Objects.requireNonNull(MAP.get(kind), kind.toString()); + return MAP.getOrDefault(kind, Policy.AS_IS); } /** Returns whether an expression is definitely not true. */ @@ -106,7 +105,7 @@ public class Strong { * expressions, and you may override methods to test hypotheses such as * "if {@code x} is null, is {@code x + y} null? */ public boolean isNull(RexNode node) { - final Policy policy = MAP.get(node.getKind()); + final Policy policy = policy(node.getKind()); switch (policy) { case NOT_NULL: return false; http://git-wip-us.apache.org/repos/asf/calcite/blob/ac1b63d1/core/src/main/java/org/apache/calcite/rex/RexInterpreter.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexInterpreter.java b/core/src/main/java/org/apache/calcite/rex/RexInterpreter.java index a0455e2..0d4f0b2 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexInterpreter.java +++ b/core/src/main/java/org/apache/calcite/rex/RexInterpreter.java @@ -176,6 +176,15 @@ public class RexInterpreter implements RexVisitor<Comparable> { case PLUS: return containsNull(values) ? N : number(values.get(0)).add(number(values.get(1))); + case MINUS: + return containsNull(values) ? N + : number(values.get(0)).subtract(number(values.get(1))); + case TIMES: + return containsNull(values) ? N + : number(values.get(0)).multiply(number(values.get(1))); + case DIVIDE: + return containsNull(values) ? N + : number(values.get(0)).divide(number(values.get(1))); case CAST: return cast(call, values); case COALESCE: http://git-wip-us.apache.org/repos/asf/calcite/blob/ac1b63d1/core/src/main/java/org/apache/calcite/rex/RexSimplify.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java index 1179547..5fda7b9 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java +++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java @@ -28,6 +28,7 @@ import org.apache.calcite.sql.SqlKind; import org.apache.calcite.sql.SqlOperator; import org.apache.calcite.sql.fun.SqlStdOperatorTable; import org.apache.calcite.sql.type.SqlTypeName; +import org.apache.calcite.sql.type.SqlTypeUtil; import org.apache.calcite.util.Bug; import org.apache.calcite.util.Pair; import org.apache.calcite.util.Util; @@ -61,6 +62,7 @@ public class RexSimplify { final boolean unknownAsFalse; final boolean predicateElimination; private final RexExecutor executor; + private final Strong strong; /** * Creates a RexSimplify. @@ -85,6 +87,7 @@ public class RexSimplify { this.predicateElimination = predicateElimination; this.paranoid = paranoid; this.executor = Objects.requireNonNull(executor); + this.strong = new Strong(); } @Deprecated // to be removed before 2.0 @@ -176,6 +179,12 @@ public class RexSimplify { } private RexNode simplify_(RexNode e) { + if (strong.isNull(e)) { + if (unknownAsFalse) { + return rexBuilder.makeLiteral(false); + } + return rexBuilder.makeNullLiteral(e.getType()); + } switch (e.getKind()) { case AND: return simplifyAnd((RexCall) e); @@ -251,7 +260,8 @@ public class RexSimplify { // "1 != '1'" is unchanged because the types are not the same. if (o0.isA(SqlKind.LITERAL) && o1.isA(SqlKind.LITERAL) - && o0.getType().equals(o1.getType())) { + && SqlTypeUtil.equalSansNullability(rexBuilder.getTypeFactory(), + o0.getType(), o1.getType())) { final C v0 = ((RexLiteral) o0).getValueAs(clazz); final C v1 = ((RexLiteral) o1).getValueAs(clazz); if (v0 == null || v1 == null) { @@ -483,7 +493,7 @@ public class RexSimplify { final RexNode arg = ((RexCall) a).operands.get(0); return simplify_(rexBuilder.makeCall(notKind, arg)); } - RexNode a2 = simplify_(a); + RexNode a2 = withUnknownAsFalse(false).simplify_(a); if (a != a2) { return rexBuilder.makeCall(RexUtil.op(kind), ImmutableList.of(a2)); } http://git-wip-us.apache.org/repos/asf/calcite/blob/ac1b63d1/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java index 38038a4..4dc1fb2 100644 --- a/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java +++ b/core/src/test/java/org/apache/calcite/test/RelOptRulesTest.java @@ -1875,7 +1875,7 @@ public class RelOptRulesTest extends RelOptTestBase { // Rule should not fire, but there should be no NPE final String sql = "select * from (values (1,2)) where 1 + 2 > 3 + CAST(NULL AS INTEGER)"; - checkPlanUnchanged(new HepPlanner(program), sql); + checkPlanning(new HepPlanner(program), sql); } @Test public void testAlreadyFalseEliminatesFilter() throws Exception { http://git-wip-us.apache.org/repos/asf/calcite/blob/ac1b63d1/core/src/test/java/org/apache/calcite/test/RexProgramBuilderBase.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RexProgramBuilderBase.java b/core/src/test/java/org/apache/calcite/test/RexProgramBuilderBase.java index e3c651f..ab2b739 100644 --- a/core/src/test/java/org/apache/calcite/test/RexProgramBuilderBase.java +++ b/core/src/test/java/org/apache/calcite/test/RexProgramBuilderBase.java @@ -288,6 +288,10 @@ public abstract class RexProgramBuilderBase { return rexBuilder.makeCall(SqlStdOperatorTable.PLUS, n1, n2); } + protected RexNode mul(RexNode n1, RexNode n2) { + return rexBuilder.makeCall(SqlStdOperatorTable.MULTIPLY, n1, n2); + } + protected RexNode coalesce(RexNode... nodes) { return rexBuilder.makeCall(SqlStdOperatorTable.COALESCE, nodes); } @@ -296,6 +300,10 @@ public abstract class RexProgramBuilderBase { return rexBuilder.makeCall(SqlStdOperatorTable.DIVIDE_INTEGER, n1, n2); } + protected RexNode div(RexNode n1, RexNode n2) { + return rexBuilder.makeCall(SqlStdOperatorTable.DIVIDE, n1, n2); + } + protected RexNode sub(RexNode n1, RexNode n2) { return rexBuilder.makeCall(SqlStdOperatorTable.MINUS, n1, n2); } http://git-wip-us.apache.org/repos/asf/calcite/blob/ac1b63d1/core/src/test/java/org/apache/calcite/test/RexProgramTest.java ---------------------------------------------------------------------- diff --git a/core/src/test/java/org/apache/calcite/test/RexProgramTest.java b/core/src/test/java/org/apache/calcite/test/RexProgramTest.java index b76500a..b504c7f 100644 --- a/core/src/test/java/org/apache/calcite/test/RexProgramTest.java +++ b/core/src/test/java/org/apache/calcite/test/RexProgramTest.java @@ -1217,6 +1217,24 @@ public class RexProgramTest extends RexProgramBuilderBase { "IS NOT NULL(?0.int1)"); } + @Test public void simplifyStrong() { + checkSimplify2(ge(trueLiteral, falseLiteral), "true", "true"); + checkSimplify2(ge(trueLiteral, nullBool), "null", "false"); + checkSimplify2(ge(nullBool, nullBool), "null", "false"); + checkSimplify2(gt(trueLiteral, nullBool), "null", "false"); + checkSimplify2(le(trueLiteral, nullBool), "null", "false"); + checkSimplify2(lt(trueLiteral, nullBool), "null", "false"); + + checkSimplify2(not(nullBool), "null", "false"); + checkSimplify2(ne(vInt(), nullBool), "null", "false"); + checkSimplify2(eq(vInt(), nullBool), "null", "false"); + + checkSimplify2(plus(vInt(), nullInt), "null", "false"); + checkSimplify2(sub(vInt(), nullInt), "null", "false"); + checkSimplify2(mul(vInt(), nullInt), "null", "false"); + checkSimplify2(div(vInt(), nullInt), "null", "false"); + } + @Test public void testSimplifyFilter() { final RelDataType booleanType = typeFactory.createSqlType(SqlTypeName.BOOLEAN); http://git-wip-us.apache.org/repos/asf/calcite/blob/ac1b63d1/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 1824e51..262adb7 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -5798,8 +5798,7 @@ LogicalProject(EXPR$0=[$0], EXPR$1=[$1]) <Resource name="planAfter"> <![CDATA[ LogicalProject(EXPR$0=[$0], EXPR$1=[$1]) - LogicalFilter(condition=[>(+(1, 2), +(3, null))]) - LogicalValues(tuples=[[{ 1, 2 }]]) + LogicalValues(tuples=[[]]) ]]> </Resource> </TestCase> http://git-wip-us.apache.org/repos/asf/calcite/blob/ac1b63d1/core/src/test/resources/sql/sub-query.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/sub-query.iq b/core/src/test/resources/sql/sub-query.iq index 5b9d843..5c60a2a 100644 --- a/core/src/test/resources/sql/sub-query.iq +++ b/core/src/test/resources/sql/sub-query.iq @@ -830,7 +830,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[123], expr#5=[null], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t5)], expr#8=[OR($t6, $t7)], cs=[$t3], $condition=[$t8]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -866,7 +866,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[1], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t4)], expr#8=[OR($t6, $t7)], cs=[$t3], $condition=[$t8]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -902,7 +902,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[=($t4, $t0)], expr#6=[IS NULL($t4)], expr#7=[OR($t5, $t6)], cs=[$t3], $condition=[$t7]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -938,7 +938,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[CAST($t0):TINYINT], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t4)], expr#8=[OR($t6, $t7)], cs=[$t3], $condition=[$t8]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1080,7 +1080,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[123], expr#5=[null], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t5)], expr#8=[OR($t6, $t7)], cs=[$t3], $condition=[$t8]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1116,7 +1116,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[1], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t4)], expr#8=[OR($t6, $t7)], cs=[$t3], $condition=[$t8]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1152,7 +1152,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[=($t4, $t0)], expr#6=[IS NULL($t4)], expr#7=[OR($t5, $t6)], cs=[$t3], $condition=[$t7]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1188,7 +1188,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[CAST($t0):TINYINT], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t4)], expr#8=[OR($t6, $t7)], cs=[$t3], $condition=[$t8]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1294,7 +1294,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[=($t4, $t0)], expr#6=[IS NULL($t4)], expr#7=[OR($t5, $t6)], cs=[$t3], $condition=[$t7]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1321,13 +1321,7 @@ select sal from "scott".emp (0 rows) !ok -EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t2]) - EnumerableJoin(condition=[true], joinType=[inner]) - EnumerableAggregate(group=[{0}]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[123], expr#5=[null], expr#6=[=($t4, $t5)], cs=[$t3], $condition=[$t6]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) - EnumerableTableScan(table=[[scott, EMP]]) +EnumerableValues(tuples=[[]]) !plan # Test filter null IN literal non-correlated @@ -1340,13 +1334,7 @@ select sal from "scott".emp (0 rows) !ok -EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t2]) - EnumerableJoin(condition=[true], joinType=[inner]) - EnumerableAggregate(group=[{0}]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[1], expr#6=[=($t4, $t5)], cs=[$t3], $condition=[$t6]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) - EnumerableTableScan(table=[[scott, EMP]]) +EnumerableValues(tuples=[[]]) !plan # Test filter null IN required @@ -1359,13 +1347,7 @@ select sal from "scott".emp (0 rows) !ok -EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t2]) - EnumerableJoin(condition=[true], joinType=[inner]) - EnumerableAggregate(group=[{0}]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) - EnumerableTableScan(table=[[scott, EMP]]) +EnumerableValues(tuples=[[]]) !plan # Test filter null IN nullable @@ -1378,13 +1360,7 @@ select sal from "scott".emp (0 rows) !ok -EnumerableCalc(expr#0..2=[{inputs}], SAL=[$t2]) - EnumerableJoin(condition=[true], joinType=[inner]) - EnumerableAggregate(group=[{0}]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[CAST($t0):TINYINT], expr#6=[=($t4, $t5)], cs=[$t3], $condition=[$t6]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) - EnumerableTableScan(table=[[scott, EMP]]) +EnumerableValues(tuples=[[]]) !plan # Test filter literal IN required @@ -1470,7 +1446,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[null], expr#5=[=($t4, $t4)], expr#6=[IS NULL($t4)], expr#7=[OR($t5, $t6)], cs=[$t3], $condition=[$t7]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1512,7 +1488,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[1], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t4)], expr#8=[OR($t6, $t7)], cs=[$t3], $condition=[$t8]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1533,7 +1509,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[=($t4, $t0)], expr#6=[IS NULL($t4)], expr#7=[OR($t5, $t6)], cs=[$t3], $condition=[$t7]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1554,7 +1530,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[CAST($t0):TINYINT], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t5)], expr#8=[IS NULL($t4)], expr#9=[OR($t6, $t7, $t8)], cs=[$t3], $condition=[$t9]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[CAST($t0):TINYINT], expr#5=[IS NULL($t4)], expr#6=[null], expr#7=[IS NULL($t6)], expr#8=[OR($t5, $t7)], cs=[$t3], $condition=[$t8]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1631,7 +1607,7 @@ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr EnumerableLimit(fetch=[1]) EnumerableSort(sort0=[$0], dir0=[DESC]) EnumerableAggregate(group=[{0}], c=[COUNT()]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[=($t4, $t0)], expr#6=[IS NULL($t4)], expr#7=[OR($t5, $t6)], cs=[$t3], $condition=[$t7]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], cs=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1647,7 +1623,11 @@ select sal from "scott".emp e (0 rows) !ok -EnumerableValues(tuples=[[]]) +EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t1]) + EnumerableCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{}]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableValues(tuples=[[]]) !plan # Test filter literal IN null correlated @@ -1660,12 +1640,7 @@ select sal from "scott".emp e (0 rows) !ok -EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[123], expr#4=[null], expr#5=[=($t3, $t4)], DEPTNO=[$t0], $condition=[$t5]) - EnumerableTableScan(table=[[scott, DEPT]]) - EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) - EnumerableTableScan(table=[[scott, EMP]]) +EnumerableValues(tuples=[[]]) !plan # Test filter null IN literal correlated @@ -1678,12 +1653,11 @@ select sal from "scott".emp e (0 rows) !ok -EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[null], expr#4=[1], expr#5=[=($t3, $t4)], DEPTNO=[$t0], $condition=[$t5]) - EnumerableTableScan(table=[[scott, DEPT]]) +EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t1]) + EnumerableCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{}]) EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) EnumerableTableScan(table=[[scott, EMP]]) + EnumerableValues(tuples=[[]]) !plan # Test filter null IN required correlated @@ -1696,12 +1670,11 @@ select sal from "scott".emp e (0 rows) !ok -EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[null], expr#4=[=($t3, $t0)], DEPTNO=[$t0], $condition=[$t4]) - EnumerableTableScan(table=[[scott, DEPT]]) +EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t1]) + EnumerableCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{}]) EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) EnumerableTableScan(table=[[scott, EMP]]) + EnumerableValues(tuples=[[]]) !plan # Test filter null IN nullable correlated @@ -1714,12 +1687,11 @@ select sal from "scott".emp e (0 rows) !ok -EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2]) - EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[null], expr#4=[CAST($t0):TINYINT], expr#5=[=($t3, $t4)], DEPTNO=[$t0], $condition=[$t5]) - EnumerableTableScan(table=[[scott, DEPT]]) +EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t1]) + EnumerableCorrelate(correlation=[$cor0], joinType=[inner], requiredColumns=[{}]) EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) EnumerableTableScan(table=[[scott, EMP]]) + EnumerableValues(tuples=[[]]) !plan # Test filter literal IN required correlated @@ -1796,7 +1768,7 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t4, $t5)], expr# EnumerableJoin(condition=[=($2, $3)], joinType=[left]) EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) EnumerableTableScan(table=[[scott, EMP]]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[123], expr#5=[null], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t5)], expr#8=[OR($t6, $t7)], DEPTNO=[$t0], $f1=[$t3], $condition=[$t8]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[false], expr#4=[null], expr#5=[IS NULL($t4)], DEPTNO=[$t0], $f1=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1814,7 +1786,7 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t4, $t5)], expr# EnumerableJoin(condition=[=($2, $3)], joinType=[left]) EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) EnumerableTableScan(table=[[scott, EMP]]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[1], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t4)], expr#8=[OR($t6, $t7)], DEPTNO=[$t0], $f1=[$t3], $condition=[$t8]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], DEPTNO=[$t0], $f1=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1832,7 +1804,7 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t4, $t5)], expr# EnumerableJoin(condition=[=($2, $3)], joinType=[left]) EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) EnumerableTableScan(table=[[scott, EMP]]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[=($t4, $t0)], expr#6=[IS NULL($t4)], expr#7=[OR($t5, $t6)], DEPTNO1=[$t0], $f1=[$t3], $condition=[$t7]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], DEPTNO1=[$t0], $f1=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1850,7 +1822,7 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t4, $t5)], expr# EnumerableJoin(condition=[=($2, $3)], joinType=[left]) EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) EnumerableTableScan(table=[[scott, EMP]]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[CAST($t0):TINYINT], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t4)], expr#8=[OR($t6, $t7)], DEPTNO=[$t0], $f1=[$t3], $condition=[$t8]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], DEPTNO=[$t0], $f1=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan @@ -1940,7 +1912,7 @@ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t4, $t5)], expr# EnumerableJoin(condition=[=($2, $3)], joinType=[left]) EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) EnumerableTableScan(table=[[scott, EMP]]) - EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[=($t4, $t0)], expr#6=[IS NULL($t4)], expr#7=[OR($t5, $t6)], DEPTNO1=[$t0], $f1=[$t3], $condition=[$t7]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[null], expr#5=[IS NULL($t4)], DEPTNO1=[$t0], $f1=[$t3], $condition=[$t5]) EnumerableTableScan(table=[[scott, DEPT]]) !plan
