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/11/20 22:39:40 UTC
[2/2] calcite git commit: [CALCITE-1483] Generate simpler logic for
NOT IN if we can deduce that the key is never null
[CALCITE-1483] Generate simpler logic for NOT IN if we can deduce that the key is never null
Project: http://git-wip-us.apache.org/repos/asf/calcite/repo
Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/b196b26d
Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/b196b26d
Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/b196b26d
Branch: refs/heads/master
Commit: b196b26d093028565cb59ab36507d1520eed2a9c
Parents: dccbb55
Author: Julian Hyde <jh...@apache.org>
Authored: Wed Nov 2 10:23:44 2016 -0700
Committer: Julian Hyde <jh...@apache.org>
Committed: Sun Nov 20 14:39:15 2016 -0800
----------------------------------------------------------------------
.../org/apache/calcite/plan/RelOptUtil.java | 158 +++++++++++++------
.../calcite/sql2rel/SqlToRelConverter.java | 106 ++++++-------
.../calcite/test/SqlToRelConverterTest.java | 37 +++++
.../calcite/test/SqlToRelConverterTest.xml | 106 +++++++++++--
4 files changed, 293 insertions(+), 114 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/calcite/blob/b196b26d/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
index 23147c2..9491220 100644
--- a/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
+++ b/core/src/main/java/org/apache/calcite/plan/RelOptUtil.java
@@ -43,6 +43,7 @@ import org.apache.calcite.rel.logical.LogicalCalc;
import org.apache.calcite.rel.logical.LogicalFilter;
import org.apache.calcite.rel.logical.LogicalJoin;
import org.apache.calcite.rel.logical.LogicalProject;
+import org.apache.calcite.rel.metadata.RelMetadataQuery;
import org.apache.calcite.rel.rules.AggregateProjectPullUpConstantsRule;
import org.apache.calcite.rel.rules.DateRangeRules;
import org.apache.calcite.rel.rules.FilterMergeRule;
@@ -60,6 +61,7 @@ import org.apache.calcite.rel.type.RelDataTypeSystem;
import org.apache.calcite.rex.RexBuilder;
import org.apache.calcite.rex.RexCall;
import org.apache.calcite.rex.RexCorrelVariable;
+import org.apache.calcite.rex.RexExecutorImpl;
import org.apache.calcite.rex.RexFieldAccess;
import org.apache.calcite.rex.RexInputRef;
import org.apache.calcite.rex.RexLiteral;
@@ -473,70 +475,77 @@ public abstract class RelOptUtil {
* from emp' or 'values (1,2,3)' or '('Foo', 34)'.
* @param subqueryType Sub-query type
* @param logic Whether to use 2- or 3-valued boolean logic
- * @param needsOuterJoin Whether query needs outer join
+ * @param notIn Whether the operator is NOT IN
*
* @return A pair of a relational expression which outer joins a boolean
* condition column, and a numeric offset. The offset is 2 if column 0 is
* the number of rows and column 1 is the number of rows with not-null keys;
* 0 otherwise.
*/
- public static Pair<RelNode, Boolean> createExistsPlan(
+ public static Exists createExistsPlan(
RelNode seekRel,
SubqueryType subqueryType,
Logic logic,
- boolean needsOuterJoin) {
+ boolean notIn) {
switch (subqueryType) {
case SCALAR:
- return Pair.of(seekRel, false);
- default:
- RelNode ret = seekRel;
- final RelOptCluster cluster = seekRel.getCluster();
- final RexBuilder rexBuilder = cluster.getRexBuilder();
- final RelDataTypeFactory typeFactory = rexBuilder.getTypeFactory();
-
- final int keyCount = ret.getRowType().getFieldCount();
- if (!needsOuterJoin) {
- return Pair.<RelNode, Boolean>of(
- LogicalAggregate.create(ret, false,
- ImmutableBitSet.range(keyCount), null,
- ImmutableList.<AggregateCall>of()),
- false);
+ return new Exists(seekRel, false, true);
+ }
+
+ switch (logic) {
+ case TRUE_FALSE_UNKNOWN:
+ case UNKNOWN_AS_TRUE:
+ if (!containsNullableFields(seekRel)) {
+ logic = Logic.TRUE_FALSE;
}
+ }
+ RelNode ret = seekRel;
+ final RelOptCluster cluster = seekRel.getCluster();
+ final RexBuilder rexBuilder = cluster.getRexBuilder();
+ final int keyCount = ret.getRowType().getFieldCount();
+ final boolean outerJoin = notIn
+ || logic == RelOptUtil.Logic.TRUE_FALSE_UNKNOWN;
+ if (!outerJoin) {
+ final LogicalAggregate aggregate =
+ LogicalAggregate.create(ret, false,
+ ImmutableBitSet.range(keyCount), null,
+ ImmutableList.<AggregateCall>of());
+ return new Exists(aggregate, false, false);
+ }
- // for IN/NOT IN, it needs to output the fields
- final List<RexNode> exprs = new ArrayList<>();
- if (subqueryType == SubqueryType.IN) {
- for (int i = 0; i < keyCount; i++) {
- exprs.add(rexBuilder.makeInputRef(ret, i));
- }
+ // for IN/NOT IN, it needs to output the fields
+ final List<RexNode> exprs = new ArrayList<>();
+ if (subqueryType == SubqueryType.IN) {
+ for (int i = 0; i < keyCount; i++) {
+ exprs.add(rexBuilder.makeInputRef(ret, i));
}
+ }
- final int projectedKeyCount = exprs.size();
- exprs.add(rexBuilder.makeLiteral(true));
+ final int projectedKeyCount = exprs.size();
+ exprs.add(rexBuilder.makeLiteral(true));
- ret = createProject(ret, exprs, null);
+ ret = createProject(ret, exprs, null);
- final AggregateCall aggCall =
- AggregateCall.create(SqlStdOperatorTable.MIN,
- false,
- ImmutableList.of(projectedKeyCount),
- -1,
- projectedKeyCount,
- ret,
- null,
- null);
+ final AggregateCall aggCall =
+ AggregateCall.create(SqlStdOperatorTable.MIN,
+ false,
+ ImmutableList.of(projectedKeyCount),
+ -1,
+ projectedKeyCount,
+ ret,
+ null,
+ null);
- ret = LogicalAggregate.create(ret, false,
- ImmutableBitSet.range(projectedKeyCount), null,
- ImmutableList.of(aggCall));
+ ret = LogicalAggregate.create(ret, false,
+ ImmutableBitSet.range(projectedKeyCount), null,
+ ImmutableList.of(aggCall));
- switch (logic) {
- case TRUE_FALSE_UNKNOWN:
- case UNKNOWN_AS_TRUE:
- return Pair.of(ret, true);
- default:
- return Pair.of(ret, false);
- }
+ switch (logic) {
+ case TRUE_FALSE_UNKNOWN:
+ case UNKNOWN_AS_TRUE:
+ return new Exists(ret, true, true);
+ default:
+ return new Exists(ret, false, true);
}
}
@@ -3262,6 +3271,51 @@ public abstract class RelOptUtil {
}
}
+ /**
+ * Determines whether any of the fields in a given relational expression may
+ * contain null values, taking into account constraints on the field types and
+ * also deduced predicates.
+ */
+ private static boolean containsNullableFields(RelNode r) {
+ final RexBuilder rexBuilder = r.getCluster().getRexBuilder();
+ final RelDataType rowType = r.getRowType();
+ final List<RexNode> list = new ArrayList<>();
+ for (RelDataTypeField field : rowType.getFieldList()) {
+ if (field.getType().isNullable()) {
+ list.add(
+ rexBuilder.makeCall(SqlStdOperatorTable.IS_NOT_NULL,
+ rexBuilder.makeInputRef(field.getType(), field.getIndex())));
+ }
+ }
+ if (list.isEmpty()) {
+ // All columns are declared NOT NULL.
+ return false;
+ }
+ final RelOptPredicateList predicates =
+ RelMetadataQuery.instance().getPulledUpPredicates(r);
+ if (predicates.pulledUpPredicates.isEmpty()) {
+ // We have no predicates, so cannot deduce that any of the fields
+ // declared NULL are really NOT NULL.
+ return true;
+ }
+ RexExecutorImpl rexImpl =
+ (RexExecutorImpl) r.getCluster().getPlanner().getExecutor();
+ final RexImplicationChecker checker =
+ new RexImplicationChecker(rexBuilder, rexImpl, rowType);
+ final RexNode first =
+ RexUtil.composeConjunction(rexBuilder, predicates.pulledUpPredicates,
+ false);
+ final RexNode second =
+ RexUtil.composeConjunction(rexBuilder, list, false);
+ // Suppose we have EMP(empno INT NOT NULL, mgr INT),
+ // and predicates [empno > 0, mgr > 0].
+ // We make first: "empno > 0 AND mgr > 0"
+ // and second: "mgr IS NOT NULL"
+ // and ask whether first implies second.
+ // It does, so we have no nullable columns.
+ return !checker.implies(first, second);
+ }
+
//~ Inner Classes ----------------------------------------------------------
/** Visitor that finds all variables used but not stopped in an expression. */
@@ -3592,6 +3646,20 @@ public abstract class RelOptUtil {
return result;
}
}
+
+ /** Result of calling
+ * {@link org.apache.calcite.plan.RelOptUtil#createExistsPlan} */
+ public static class Exists {
+ public final RelNode r;
+ public final boolean indicator;
+ public final boolean outerJoin;
+
+ private Exists(RelNode r, boolean indicator, boolean outerJoin) {
+ this.r = r;
+ this.indicator = indicator;
+ this.outerJoin = outerJoin;
+ }
+ }
}
// End RelOptUtil.java
http://git-wip-us.apache.org/repos/asf/calcite/blob/b196b26d/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
----------------------------------------------------------------------
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
index 1adec8a..0624645 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/SqlToRelConverter.java
@@ -996,7 +996,7 @@ public class SqlToRelConverter {
final SqlBasicCall call;
final RelNode rel;
final SqlNode query;
- final Pair<RelNode, Boolean> converted;
+ final RelOptUtil.Exists converted;
switch (subQuery.node.getKind()) {
case CURSOR:
convertCursor(bb, subQuery);
@@ -1071,22 +1071,19 @@ public class SqlToRelConverter {
// where emp.deptno <> null
// and q.indicator <> TRUE"
//
- final boolean outerJoin = bb.subqueryNeedsOuterJoin
- || notIn
- || subQuery.logic == RelOptUtil.Logic.TRUE_FALSE_UNKNOWN;
final RelDataType targetRowType =
SqlTypeUtil.promoteToRowType(typeFactory,
validator.getValidatedNodeType(leftKeyNode), null);
converted =
convertExists(query, RelOptUtil.SubqueryType.IN, subQuery.logic,
- outerJoin, targetRowType);
- if (converted.right) {
+ notIn, targetRowType);
+ if (converted.indicator) {
// Generate
// emp CROSS JOIN (SELECT COUNT(*) AS c,
// COUNT(deptno) AS ck FROM dept)
final RelDataType longType =
typeFactory.createSqlType(SqlTypeName.BIGINT);
- final RelNode seek = converted.left.getInput(0); // fragile
+ final RelNode seek = converted.r.getInput(0); // fragile
final int keyCount = leftKeys.size();
final List<Integer> args = ImmutableIntList.range(0, keyCount);
LogicalAggregate aggregate =
@@ -1101,11 +1098,20 @@ public class SqlToRelConverter {
ImmutableSet.<CorrelationId>of(), JoinRelType.INNER);
bb.setRoot(join, false);
}
- RexNode rex =
- bb.register(converted.left,
- outerJoin ? JoinRelType.LEFT : JoinRelType.INNER, leftKeys);
-
- subQuery.expr = translateIn(subQuery, bb.root, rex);
+ final RexNode rex =
+ bb.register(converted.r,
+ converted.outerJoin ? JoinRelType.LEFT : JoinRelType.INNER,
+ leftKeys);
+
+ RelOptUtil.Logic logic = subQuery.logic;
+ switch (logic) {
+ case TRUE_FALSE_UNKNOWN:
+ case UNKNOWN_AS_TRUE:
+ if (!converted.indicator) {
+ logic = RelOptUtil.Logic.TRUE_FALSE;
+ }
+ }
+ subQuery.expr = translateIn(logic, bb.root, rex);
if (notIn) {
subQuery.expr =
rexBuilder.makeCall(SqlStdOperatorTable.NOT, subQuery.expr);
@@ -1129,11 +1135,11 @@ public class SqlToRelConverter {
}
converted = convertExists(query, RelOptUtil.SubqueryType.EXISTS,
subQuery.logic, true, null);
- assert !converted.right;
- if (convertNonCorrelatedSubQuery(subQuery, bb, converted.left, true)) {
+ assert !converted.indicator;
+ if (convertNonCorrelatedSubQuery(subQuery, bb, converted.r, true)) {
return;
}
- subQuery.expr = bb.register(converted.left, JoinRelType.LEFT);
+ subQuery.expr = bb.register(converted.r, JoinRelType.LEFT);
return;
case SCALAR_QUERY:
@@ -1146,11 +1152,11 @@ public class SqlToRelConverter {
query = call.operand(0);
converted = convertExists(query, RelOptUtil.SubqueryType.SCALAR,
subQuery.logic, true, null);
- assert !converted.right;
- if (convertNonCorrelatedSubQuery(subQuery, bb, converted.left, false)) {
+ assert !converted.indicator;
+ if (convertNonCorrelatedSubQuery(subQuery, bb, converted.r, false)) {
return;
}
- rel = convertToSingleValueSubq(query, converted.left);
+ rel = convertToSingleValueSubq(query, converted.r);
subQuery.expr = bb.register(rel, JoinRelType.LEFT);
return;
@@ -1161,8 +1167,8 @@ public class SqlToRelConverter {
//
converted = convertExists(subQuery.node, RelOptUtil.SubqueryType.SCALAR,
subQuery.logic, true, null);
- assert !converted.right;
- subQuery.expr = bb.register(converted.left, JoinRelType.LEFT);
+ assert !converted.indicator;
+ subQuery.expr = bb.register(converted.r, JoinRelType.LEFT);
return;
default:
@@ -1170,12 +1176,13 @@ public class SqlToRelConverter {
}
}
- private RexNode translateIn(SubQuery subQuery, RelNode root,
+ private RexNode translateIn(RelOptUtil.Logic logic, RelNode root,
final RexNode rex) {
- switch (subQuery.logic) {
+ switch (logic) {
case TRUE:
return rexBuilder.makeLiteral(true);
+ case TRUE_FALSE:
case UNKNOWN_AS_FALSE:
assert rex instanceof RexRangeRef;
final int fieldCount = rex.getType().getFieldCount();
@@ -1220,9 +1227,6 @@ public class SqlToRelConverter {
final Project left = (Project) join.getLeft();
final RelNode leftLeft = ((Join) left.getInput()).getLeft();
final int leftLeftCount = leftLeft.getRowType().getFieldCount();
- final RelDataType nullableBooleanType =
- typeFactory.createTypeWithNullability(
- typeFactory.createSqlType(SqlTypeName.BOOLEAN), true);
final RelDataType longType =
typeFactory.createSqlType(SqlTypeName.BIGINT);
final RexNode cRef = rexBuilder.makeInputRef(root, leftLeftCount);
@@ -1252,13 +1256,10 @@ public class SqlToRelConverter {
unknownLiteral,
falseLiteral);
- return rexBuilder.makeCall(
- nullableBooleanType,
- SqlStdOperatorTable.CASE,
- args.build());
+ return rexBuilder.makeCall(SqlStdOperatorTable.CASE, args.build());
default:
- throw new AssertionError(subQuery.logic);
+ throw new AssertionError(logic);
}
}
@@ -1467,15 +1468,15 @@ public class SqlToRelConverter {
* @param logic Whether the answer needs to be in full 3-valued logic (TRUE,
* FALSE, UNKNOWN) will be required, or whether we can accept an
* approximation (say representing UNKNOWN as FALSE)
- * @param needsOuterJoin Whether an outer join is needed
+ * @param notIn Whether the operation is NOT IN
* @return join expression
* @pre extraExpr == null || extraName != null
*/
- private Pair<RelNode, Boolean> convertExists(
+ private RelOptUtil.Exists convertExists(
SqlNode seek,
RelOptUtil.SubqueryType subqueryType,
RelOptUtil.Logic logic,
- boolean needsOuterJoin,
+ boolean notIn,
RelDataType targetDataType) {
final SqlValidatorScope seekScope =
(seek instanceof SqlSelect)
@@ -1484,8 +1485,7 @@ public class SqlToRelConverter {
final Blackboard seekBb = createBlackboard(seekScope, null, false);
RelNode seekRel = convertQueryOrInList(seekBb, seek, targetDataType);
- return RelOptUtil.createExistsPlan(seekRel, subqueryType, logic,
- needsOuterJoin);
+ return RelOptUtil.createExistsPlan(seekRel, subqueryType, logic, notIn);
}
private RelNode convertQueryOrInList(
@@ -1711,13 +1711,6 @@ public class SqlToRelConverter {
break;
}
if (node instanceof SqlCall) {
- if (kind == SqlKind.OR
- || kind == SqlKind.NOT) {
- // It's always correct to outer join subquery with
- // containing query; however, when predicates involve Or
- // or NOT, outer join might be necessary.
- bb.subqueryNeedsOuterJoin = true;
- }
for (SqlNode operand : ((SqlCall) node).getOperandList()) {
if (operand != null) {
// In the case of an IN expression, locate scalar
@@ -1744,13 +1737,15 @@ public class SqlToRelConverter {
// register the scalar subqueries first so they can be converted
// before the IN expression is converted.
if (kind == SqlKind.IN) {
- if (logic == RelOptUtil.Logic.TRUE_FALSE_UNKNOWN
- && !validator.getValidatedNodeType(node).isNullable()) {
- logic = RelOptUtil.Logic.UNKNOWN_AS_FALSE;
- }
- // TODO: This conversion is only valid in the WHERE clause
- if (logic == RelOptUtil.Logic.UNKNOWN_AS_FALSE
- && !bb.subqueryNeedsOuterJoin) {
+ switch (logic) {
+ case TRUE_FALSE_UNKNOWN:
+ if (validator.getValidatedNodeType(node).isNullable()) {
+ break;
+ } else if (true) {
+ break;
+ }
+ // fall through
+ case UNKNOWN_AS_FALSE:
logic = RelOptUtil.Logic.TRUE;
}
bb.registerSubQuery(node, logic);
@@ -3651,8 +3646,6 @@ public class SqlToRelConverter {
*/
private final Set<SubQuery> subQueryList = new LinkedHashSet<>();
- private boolean subqueryNeedsOuterJoin;
-
/**
* Workspace for building aggregates.
*/
@@ -3694,7 +3687,6 @@ public class SqlToRelConverter {
this.scope = scope;
this.nameToNodeMap = nameToNodeMap;
this.top = top;
- subqueryNeedsOuterJoin = false;
}
public RexNode register(
@@ -4110,12 +4102,10 @@ public class SqlToRelConverter {
switch (kind) {
case CURSOR:
case IN:
- subQuery = getSubQuery(expr);
-
- assert subQuery != null;
- rex = subQuery.expr;
- assert rex != null : "rex != null";
- return rex;
+ subQuery = Preconditions.checkNotNull(getSubQuery(expr));
+ rex = Preconditions.checkNotNull(subQuery.expr);
+ return StandardConvertletTable.castToValidatedType(expr, rex,
+ validator, rexBuilder);
case SELECT:
case EXISTS:
http://git-wip-us.apache.org/repos/asf/calcite/blob/b196b26d/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 bfaa527..d7fa30c 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -1044,6 +1044,43 @@ public class SqlToRelConverterTest extends SqlToRelTestBase {
sql(sql).ok();
}
+ /** Since 'deptno NOT IN (SELECT mgr FROM emp)' can be null, we need a more
+ * complex plan, including counts of null and not-null keys. */
+ @Test public void testNotInUncorrelatedSubQueryInSelectMayBeNull() {
+ final String sql = "select empno, deptno not in (\n"
+ + " select mgr from emp)\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
+ /** Even though "mgr" allows nulls, we can deduce from the WHERE clause that
+ * it will never be null. Therefore we can generate a simpler plan. */
+ @Test public void testNotInUncorrelatedSubQueryInSelectDeduceNotNull() {
+ final String sql = "select empno, deptno not in (\n"
+ + " select mgr from emp where mgr > 5)\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
+ /** Similar to {@link #testNotInUncorrelatedSubQueryInSelectDeduceNotNull()},
+ * using {@code IS NOT NULL}. */
+ @Test public void testNotInUncorrelatedSubQueryInSelectDeduceNotNull2() {
+ final String sql = "select empno, deptno not in (\n"
+ + " select mgr from emp where mgr is not null)\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
+ /** Similar to {@link #testNotInUncorrelatedSubQueryInSelectDeduceNotNull()},
+ * using {@code IN}. */
+ @Test public void testNotInUncorrelatedSubQueryInSelectDeduceNotNull3() {
+ final String sql = "select empno, deptno not in (\n"
+ + " select mgr from emp where mgr in (\n"
+ + " select mgr from emp where deptno = 10))\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
@Test public void testNotInUncorrelatedSubQueryInSelectNotNullRex() {
final String sql = "select empno, deptno not in (\n"
+ " select deptno from dept)\n"
http://git-wip-us.apache.org/repos/asf/calcite/blob/b196b26d/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
----------------------------------------------------------------------
diff --git a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 500ee91..4315a43 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -710,7 +710,7 @@ LogicalProject(EMPNO=[$0])
<Resource name="plan">
<![CDATA[
LogicalProject(NAME=[$0])
- LogicalTableFunctionScan(invocation=[DEDUP($0, $1, 'NAME')], rowType=[RecordType(VARCHAR(1024) NAME)])
+ LogicalTableFunctionScan(invocation=[DEDUP(CAST($0):CURSOR NOT NULL, CAST($1):CURSOR NOT NULL, 'NAME')], rowType=[RecordType(VARCHAR(1024) NAME)])
LogicalProject(ENAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(NAME=[$1])
@@ -1865,15 +1865,10 @@ LogicalProject(EMPNO=[$0], EXPR$1=[NOT(CASE(=($9, 0), false, IS NOT NULL($13), t
<Resource name="plan">
<![CDATA[
LogicalProject(EMPNO=[$0])
- LogicalFilter(condition=[NOT(CASE(=($9, 0), false, IS NOT NULL($13), true, IS NULL($11), null, <($10, $9), null, false))])
- LogicalJoin(condition=[=($11, $12)], joinType=[left])
- LogicalProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$9], $f10=[$10], $f11=[$7])
- LogicalJoin(condition=[true], joinType=[inner])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
- LogicalProject($f0=[$0], $f1=[true])
- LogicalProject(DEPTNO=[$0])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalFilter(condition=[NOT(AND(IS TRUE($11), IS NOT NULL($9)))])
+ LogicalJoin(condition=[=($9, $10)], joinType=[left])
+ LogicalProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
LogicalProject($f0=[$0], $f1=[true])
LogicalProject(DEPTNO=[$0])
@@ -1889,7 +1884,7 @@ from emp]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(EMPNO=[$0], EXPR$1=[NOT(true)])
+LogicalProject(EMPNO=[$0], EXPR$1=[IS FALSE($11)])
LogicalJoin(condition=[=($9, $10)], joinType=[left])
LogicalProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$7])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
@@ -1927,6 +1922,95 @@ LogicalAggregate(group=[{0}], EXPR$1=[MAX($1)], EXPR$2=[MAX($2)])
]]>
</Resource>
</TestCase>
+ <TestCase name="testNotInUncorrelatedSubQueryInSelectDeduceNotNull">
+ <Resource name="sql">
+ <![CDATA[select empno, deptno not in (
+ select mgr from emp where mgr > 5)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], EXPR$1=[CAST(NOT(AND(IS TRUE($11), IS NOT NULL($9)))):BOOLEAN])
+ LogicalJoin(condition=[=($9, $10)], joinType=[left])
+ LogicalProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject($f0=[$0], $f1=[true])
+ LogicalProject(MGR=[$3])
+ LogicalFilter(condition=[>($3, 5)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInUncorrelatedSubQueryInSelectDeduceNotNull2">
+ <Resource name="sql">
+ <![CDATA[select empno, deptno not in (
+ select mgr from emp where mgr is not null)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], EXPR$1=[CAST(NOT(AND(IS TRUE($11), IS NOT NULL($9)))):BOOLEAN])
+ LogicalJoin(condition=[=($9, $10)], joinType=[left])
+ LogicalProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject($f0=[$0], $f1=[true])
+ LogicalProject(MGR=[$3])
+ LogicalFilter(condition=[IS NOT NULL($3)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInUncorrelatedSubQueryInSelectDeduceNotNull3">
+ <Resource name="sql">
+ <![CDATA[select empno, deptno not in (
+ select mgr from emp where mgr in (
+ select mgr from emp where deptno = 10))
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], EXPR$1=[CAST(NOT(AND(IS TRUE($11), IS NOT NULL($9)))):BOOLEAN])
+ LogicalJoin(condition=[=($9, $10)], joinType=[left])
+ LogicalProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$7])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject($f0=[$0], $f1=[true])
+ LogicalProject(MGR=[$3])
+ LogicalJoin(condition=[=($3, $9)], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(MGR=[$3])
+ LogicalFilter(condition=[=($7, 10)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testNotInUncorrelatedSubQueryInSelectMayBeNull">
+ <Resource name="sql">
+ <![CDATA[select empno, deptno not in (
+ select mgr from emp)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EMPNO=[$0], EXPR$1=[NOT(CASE(=($9, 0), false, IS NOT NULL($13), true, IS NULL($11), null, <($10, $9), null, false))])
+ LogicalJoin(condition=[=($11, $12)], joinType=[left])
+ LogicalProject($f0=[$0], $f1=[$1], $f2=[$2], $f3=[$3], $f4=[$4], $f5=[$5], $f6=[$6], $f7=[$7], $f8=[$8], $f9=[$9], $f10=[$10], $f11=[$7])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{}], agg#0=[COUNT()], agg#1=[COUNT($0)])
+ LogicalProject($f0=[$0], $f1=[true])
+ LogicalProject(MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject($f0=[$0], $f1=[true])
+ LogicalProject(MGR=[$3])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
<TestCase name="testSingletonGroupingSet">
<Resource name="sql">
<![CDATA[select sum(sal) from emp group by grouping sets (deptno)]]>