[CALCITE-2329] Improve rewrite for "constant IN (sub-query)" Improve and refactor SubQueryRemoveRule.
Add tests for partially-null right-hand side. (Julian Hyde) Close apache/calcite#700 Project: http://git-wip-us.apache.org/repos/asf/calcite/repo Commit: http://git-wip-us.apache.org/repos/asf/calcite/commit/1ae6a526 Tree: http://git-wip-us.apache.org/repos/asf/calcite/tree/1ae6a526 Diff: http://git-wip-us.apache.org/repos/asf/calcite/diff/1ae6a526 Branch: refs/heads/master Commit: 1ae6a52603006741a4cb6bf558c58d9ac08e866a Parents: 03dce13 Author: Volodymyr Vysotskyi <vvo...@gmail.com> Authored: Mon May 21 20:38:03 2018 +0300 Committer: Julian Hyde <jh...@apache.org> Committed: Tue Jun 12 19:01:21 2018 -0500 ---------------------------------------------------------------------- .../calcite/rel/rules/SubQueryRemoveRule.java | 684 ++++++---- .../java/org/apache/calcite/rex/RexUtil.java | 17 +- .../org/apache/calcite/test/RelOptRulesTest.xml | 4 +- core/src/test/resources/sql/blank.iq | 2 +- core/src/test/resources/sql/sub-query.iq | 1270 +++++++++++++++++- 5 files changed, 1722 insertions(+), 255 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/calcite/blob/1ae6a526/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java index 12ee805..628219f 100644 --- a/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java +++ b/core/src/main/java/org/apache/calcite/rel/rules/SubQueryRemoveRule.java @@ -52,6 +52,7 @@ import com.google.common.collect.ImmutableSet; import java.util.ArrayList; import java.util.List; import java.util.Set; +import java.util.stream.Collectors; /** * Transform that converts IN, EXISTS and scalar sub-queries into joins. @@ -65,88 +66,13 @@ import java.util.Set; */ public abstract class SubQueryRemoveRule extends RelOptRule { public static final SubQueryRemoveRule PROJECT = - new SubQueryRemoveRule( - operand(Project.class, null, RexUtil.SubQueryFinder.PROJECT_PREDICATE, - any()), - RelFactories.LOGICAL_BUILDER, "SubQueryRemoveRule:Project") { - public void onMatch(RelOptRuleCall call) { - final Project project = call.rel(0); - final RelBuilder builder = call.builder(); - final RexSubQuery e = - RexUtil.SubQueryFinder.find(project.getProjects()); - assert e != null; - final RelOptUtil.Logic logic = - LogicVisitor.find(RelOptUtil.Logic.TRUE_FALSE_UNKNOWN, - project.getProjects(), e); - builder.push(project.getInput()); - final int fieldCount = builder.peek().getRowType().getFieldCount(); - final RexNode target = apply(e, ImmutableSet.<CorrelationId>of(), - logic, builder, 1, fieldCount); - final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target); - builder.project(shuttle.apply(project.getProjects()), - project.getRowType().getFieldNames()); - call.transformTo(builder.build()); - } - }; + new SubQueryProjectRemoveRule(RelFactories.LOGICAL_BUILDER); public static final SubQueryRemoveRule FILTER = - new SubQueryRemoveRule( - operand(Filter.class, null, RexUtil.SubQueryFinder.FILTER_PREDICATE, - any()), - RelFactories.LOGICAL_BUILDER, "SubQueryRemoveRule:Filter") { - public void onMatch(RelOptRuleCall call) { - final Filter filter = call.rel(0); - final RelBuilder builder = call.builder(); - builder.push(filter.getInput()); - int count = 0; - RexNode c = filter.getCondition(); - for (;;) { - final RexSubQuery e = RexUtil.SubQueryFinder.find(c); - if (e == null) { - assert count > 0; - break; - } - ++count; - final RelOptUtil.Logic logic = - LogicVisitor.find(RelOptUtil.Logic.TRUE, ImmutableList.of(c), - e); - final Set<CorrelationId> variablesSet = - RelOptUtil.getVariablesUsed(e.rel); - final RexNode target = apply(e, variablesSet, logic, - builder, 1, builder.peek().getRowType().getFieldCount()); - final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target); - c = c.accept(shuttle); - } - builder.filter(c); - builder.project(fields(builder, filter.getRowType().getFieldCount())); - call.transformTo(builder.build()); - } - }; + new SubQueryFilterRemoveRule(RelFactories.LOGICAL_BUILDER); public static final SubQueryRemoveRule JOIN = - new SubQueryRemoveRule( - operand(Join.class, null, RexUtil.SubQueryFinder.JOIN_PREDICATE, - any()), RelFactories.LOGICAL_BUILDER, "SubQueryRemoveRule:Join") { - public void onMatch(RelOptRuleCall call) { - final Join join = call.rel(0); - final RelBuilder builder = call.builder(); - final RexSubQuery e = - RexUtil.SubQueryFinder.find(join.getCondition()); - assert e != null; - final RelOptUtil.Logic logic = - LogicVisitor.find(RelOptUtil.Logic.TRUE, - ImmutableList.of(join.getCondition()), e); - builder.push(join.getLeft()); - builder.push(join.getRight()); - final int fieldCount = join.getRowType().getFieldCount(); - final RexNode target = apply(e, ImmutableSet.<CorrelationId>of(), - logic, builder, 2, fieldCount); - final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target); - builder.join(join.getJoinType(), shuttle.apply(join.getCondition())); - builder.project(fields(builder, join.getRowType().getFieldCount())); - call.transformTo(builder.build()); - } - }; + new SubQueryJoinRemoveRule(RelFactories.LOGICAL_BUILDER); /** * Creates a SubQueryRemoveRule. @@ -166,137 +92,297 @@ public abstract class SubQueryRemoveRule extends RelOptRule { RelBuilder builder, int inputCount, int offset) { switch (e.getKind()) { case SCALAR_QUERY: - builder.push(e.rel); - final RelMetadataQuery mq = e.rel.getCluster().getMetadataQuery(); - final Boolean unique = mq.areColumnsUnique(builder.peek(), - ImmutableBitSet.of()); - if (unique == null || !unique) { - builder.aggregate(builder.groupKey(), - builder.aggregateCall(SqlStdOperatorTable.SINGLE_VALUE, false, - false, null, null, builder.field(0))); - } - builder.join(JoinRelType.LEFT, builder.literal(true), variablesSet); - return field(builder, inputCount, offset); - + return rewriteScalarQuery(e, variablesSet, builder, inputCount, offset); case SOME: - // Most general case, where the left and right keys might have nulls, and - // caller requires 3-valued logic return. - // - // select e.deptno, e.deptno < some (select deptno from emp) as v - // from emp as e - // - // becomes - // - // select e.deptno, - // case - // when q.c = 0 then false // sub-query is empty - // when (e.deptno < q.m) is true then true - // when q.c > q.d then unknown // sub-query has at least one null - // else e.deptno < q.m - // end as v - // from emp as e - // cross join ( - // select max(deptno) as m, count(*) as c, count(deptno) as d - // from emp) as q - // - final SqlQuantifyOperator op = (SqlQuantifyOperator) e.op; - builder.push(e.rel) - .aggregate(builder.groupKey(), - op.comparisonKind == SqlKind.GREATER_THAN - || op.comparisonKind == SqlKind.GREATER_THAN_OR_EQUAL - ? builder.min("m", builder.field(0)) - : builder.max("m", builder.field(0)), - builder.count(false, "c"), - builder.count(false, "d", builder.field(0))) - .as("q") - .join(JoinRelType.INNER); - return builder.call(SqlStdOperatorTable.CASE, - builder.call(SqlStdOperatorTable.EQUALS, - builder.field("q", "c"), builder.literal(0)), - builder.literal(false), - builder.call(SqlStdOperatorTable.IS_TRUE, - builder.call(RelOptUtil.op(op.comparisonKind, null), - e.operands.get(0), builder.field("q", "m"))), - builder.literal(true), - builder.call(SqlStdOperatorTable.GREATER_THAN, - builder.field("q", "c"), builder.field("q", "d")), - builder.literal(null), - builder.call(RelOptUtil.op(op.comparisonKind, null), - e.operands.get(0), builder.field("q", "m"))); - + return rewriteSome(e, builder); case IN: + return rewriteIn(e, variablesSet, logic, builder, offset); case EXISTS: - // Most general case, where the left and right keys might have nulls, and - // caller requires 3-valued logic return. - // - // select e.deptno, e.deptno in (select deptno from emp) - // from emp as e - // - // becomes - // - // select e.deptno, - // case - // when ct.c = 0 then false - // when dt.i is not null then true - // when e.deptno is null then null - // when ct.ck < ct.c then null - // else false - // end - // from emp as e - // left join ( - // (select count(*) as c, count(deptno) as ck from emp) as ct - // cross join (select distinct deptno, true as i from emp)) as dt - // on e.deptno = dt.deptno - // - // If keys are not null we can remove "ct" and simplify to - // - // select e.deptno, - // case - // when dt.i is not null then true - // else false - // end - // from emp as e - // left join (select distinct deptno, true as i from emp) as dt - // on e.deptno = dt.deptno - // - // We could further simplify to - // - // select e.deptno, - // dt.i is not null - // from emp as e - // left join (select distinct deptno, true as i from emp) as dt - // on e.deptno = dt.deptno - // - // but have not yet. - // - // If the logic is TRUE we can just kill the record if the condition - // evaluates to FALSE or UNKNOWN. Thus the query simplifies to an inner - // join: - // - // select e.deptno, - // true - // from emp as e - // inner join (select distinct deptno from emp) as dt - // on e.deptno = dt.deptno - // - - builder.push(e.rel); - final List<RexNode> fields = new ArrayList<>(); - switch (e.getKind()) { - case IN: - fields.addAll(builder.fields()); - } + return rewriteExists(e, variablesSet, logic, builder); + default: + throw new AssertionError(e.getKind()); + } + } + + /** + * Rewrites a scalar sub-query into an + * {@link org.apache.calcite.rel.core.Aggregate}. + * + * @param e IN sub-query to rewrite + * @param variablesSet A set of variables used by a relational + * expression of the specified RexSubQuery + * @param builder Builder + * @param offset Offset to shift {@link RexInputRef} + * + * @return Expression that may be used to replace the RexSubQuery + */ + private RexNode rewriteScalarQuery(RexSubQuery e, Set<CorrelationId> variablesSet, + RelBuilder builder, int inputCount, int offset) { + builder.push(e.rel); + final RelMetadataQuery mq = e.rel.getCluster().getMetadataQuery(); + final Boolean unique = mq.areColumnsUnique(builder.peek(), + ImmutableBitSet.of()); + if (unique == null || !unique) { + builder.aggregate(builder.groupKey(), + builder.aggregateCall(SqlStdOperatorTable.SINGLE_VALUE, false, + false, null, null, builder.field(0))); + } + builder.join(JoinRelType.LEFT, builder.literal(true), variablesSet); + return field(builder, inputCount, offset); + } + + /** + * Rewrites a SOME sub-query into a {@link Join}. + * + * @param e SOME sub-query to rewrite + * @param builder Builder + * + * @return Expression that may be used to replace the RexSubQuery + */ + private RexNode rewriteSome(RexSubQuery e, RelBuilder builder) { + // Most general case, where the left and right keys might have nulls, and + // caller requires 3-valued logic return. + // + // select e.deptno, e.deptno < some (select deptno from emp) as v + // from emp as e + // + // becomes + // + // select e.deptno, + // case + // when q.c = 0 then false // sub-query is empty + // when (e.deptno < q.m) is true then true + // when q.c > q.d then unknown // sub-query has at least one null + // else e.deptno < q.m + // end as v + // from emp as e + // cross join ( + // select max(deptno) as m, count(*) as c, count(deptno) as d + // from emp) as q + // + final SqlQuantifyOperator op = (SqlQuantifyOperator) e.op; + builder.push(e.rel) + .aggregate(builder.groupKey(), + op.comparisonKind == SqlKind.GREATER_THAN + || op.comparisonKind == SqlKind.GREATER_THAN_OR_EQUAL + ? builder.min("m", builder.field(0)) + : builder.max("m", builder.field(0)), + builder.count(false, "c"), + builder.count(false, "d", builder.field(0))) + .as("q") + .join(JoinRelType.INNER); + return builder.call(SqlStdOperatorTable.CASE, + builder.call(SqlStdOperatorTable.EQUALS, + builder.field("q", "c"), builder.literal(0)), + builder.literal(false), + builder.call(SqlStdOperatorTable.IS_TRUE, + builder.call(RelOptUtil.op(op.comparisonKind, null), + e.operands.get(0), builder.field("q", "m"))), + builder.literal(true), + builder.call(SqlStdOperatorTable.GREATER_THAN, + builder.field("q", "c"), builder.field("q", "d")), + builder.literal(null), + builder.call(RelOptUtil.op(op.comparisonKind, null), + e.operands.get(0), builder.field("q", "m"))); + } - // First, the cross join + /** + * Rewrites an EXISTS RexSubQuery into a {@link Join}. + * + * @param e EXISTS sub-query to rewrite + * @param variablesSet A set of variables used by a relational + * expression of the specified RexSubQuery + * @param logic Logic for evaluating + * @param builder Builder + * + * @return Expression that may be used to replace the RexSubQuery + */ + private RexNode rewriteExists(RexSubQuery e, Set<CorrelationId> variablesSet, + RelOptUtil.Logic logic, RelBuilder builder) { + builder.push(e.rel); + + builder.project(builder.alias(builder.literal(true), "i")); + switch (logic) { + case TRUE: + // Handles queries with single EXISTS in filter condition: + // select e.deptno from emp as e + // where exists (select deptno from emp) + builder.aggregate(builder.groupKey(0)); + builder.as("dt"); + builder.join(JoinRelType.INNER, builder.literal(true), variablesSet); + return builder.literal(true); + default: + builder.distinct(); + } + + builder.as("dt"); + + builder.join(JoinRelType.LEFT, builder.literal(true), variablesSet); + + return builder.isNotNull(Util.last(builder.fields())); + } + + /** + * Rewrites an IN RexSubQuery into a {@link Join}. + * + * @param e IN sub-query to rewrite + * @param variablesSet A set of variables used by a relational + * expression of the specified RexSubQuery + * @param logic Logic for evaluating + * @param builder Builder + * @param offset Offset to shift {@link RexInputRef} + * + * @return Expression that may be used to replace the RexSubQuery + */ + private RexNode rewriteIn(RexSubQuery e, Set<CorrelationId> variablesSet, + RelOptUtil.Logic logic, RelBuilder builder, int offset) { + // Most general case, where the left and right keys might have nulls, and + // caller requires 3-valued logic return. + // + // select e.deptno, e.deptno in (select deptno from emp) + // from emp as e + // + // becomes + // + // select e.deptno, + // case + // when ct.c = 0 then false + // when e.deptno is null then null + // when dt.i is not null then true + // when ct.ck < ct.c then null + // else false + // end + // from emp as e + // left join ( + // (select count(*) as c, count(deptno) as ck from emp) as ct + // cross join (select distinct deptno, true as i from emp)) as dt + // on e.deptno = dt.deptno + // + // If keys are not null we can remove "ct" and simplify to + // + // select e.deptno, + // case + // when dt.i is not null then true + // else false + // end + // from emp as e + // left join (select distinct deptno, true as i from emp) as dt + // on e.deptno = dt.deptno + // + // We could further simplify to + // + // select e.deptno, + // dt.i is not null + // from emp as e + // left join (select distinct deptno, true as i from emp) as dt + // on e.deptno = dt.deptno + // + // but have not yet. + // + // If the logic is TRUE we can just kill the record if the condition + // evaluates to FALSE or UNKNOWN. Thus the query simplifies to an inner + // join: + // + // select e.deptno, + // true + // from emp as e + // inner join (select distinct deptno from emp) as dt + // on e.deptno = dt.deptno + // + + builder.push(e.rel); + final List<RexNode> fields = new ArrayList<>(builder.fields()); + + // for the case when IN has only literal operands, it may be handled + // in the simpler way: + // + // select e.deptno, 123456 in (select deptno from emp) + // from emp as e + // + // becomes + // + // select e.deptno, + // case + // when dt.c IS NULL THEN FALSE + // when e.deptno IS NULL THEN NULL + // when dt.cs IS FALSE THEN NULL + // when dt.cs IS NOT NULL THEN TRUE + // else false + // end + // from emp AS e + // cross join ( + // select distinct deptno is not null as cs, count(*) as c + // from emp + // where deptno = 123456 or deptno is null or e.deptno is null + // order by cs desc limit 1) as dt + // + + boolean allLiterals = RexUtil.allLiterals(e.getOperands()); + final List<RexNode> expressionOperands = new ArrayList<>(e.getOperands()); + + final List<RexNode> keyIsNulls = e.getOperands().stream() + .filter(operand -> operand.getType().isNullable()) + .map(builder::isNull) + .collect(Collectors.toList()); + + if (allLiterals) { + final List<RexNode> conditions = + Pair.zip(expressionOperands, fields).stream() + .map(pair -> builder.equals(pair.left, pair.right)) + .collect(Collectors.toList()); + switch (logic) { + case TRUE: + case TRUE_FALSE: + builder.filter(conditions); + builder.project(builder.alias(builder.literal(true), "cs")); + builder.distinct(); + break; + default: + List<RexNode> isNullOpperands = fields.stream() + .map(builder::isNull) + .collect(Collectors.toList()); + // uses keyIsNulls conditions in the filter to avoid empty results + isNullOpperands.addAll(keyIsNulls); + builder.filter( + builder.or( + builder.and(conditions), + builder.or( + isNullOpperands + ))); + RexNode project = builder.and( + fields.stream() + .map(builder::isNotNull) + .collect(Collectors.toList())); + builder.project(builder.alias(project, "cs")); + + if (variablesSet.isEmpty()) { + builder.aggregate(builder.groupKey(builder.field("cs")), + builder.count(false, "c")); + + // sorts input with desc order since we are interested + // only in the case when one of the values is true. + // When true value is absent then we are interested + // only in false value. + builder.sortLimit(0, 1, + ImmutableList.of( + builder.call(SqlStdOperatorTable.DESC, + builder.field("cs")))); + } else { + builder.distinct(); + } + } + // clears expressionOperands and fields lists since + // all expressions were used in the filter + expressionOperands.clear(); + fields.clear(); + } else { switch (logic) { + case TRUE: + builder.aggregate(builder.groupKey(fields)); + break; case TRUE_FALSE_UNKNOWN: case UNKNOWN_AS_TRUE: - // Since EXISTS/NOT EXISTS are not affected by presence of - // null keys we do not need to generate count(*), count(c) - if (e.getKind() == SqlKind.EXISTS) { - logic = RelOptUtil.Logic.TRUE_FALSE; - break; - } + // Builds the cross join builder.aggregate(builder.groupKey(), builder.count(false, "c"), builder.aggregateCall(SqlStdOperatorTable.COUNT, false, false, null, @@ -309,76 +395,78 @@ public abstract class SubQueryRemoveRule extends RelOptRule { } offset += 2; builder.push(e.rel); - break; - } - - // Now the left join - switch (logic) { - case TRUE: - if (fields.isEmpty()) { - builder.project(builder.alias(builder.literal(true), "i")); - builder.aggregate(builder.groupKey(0)); - } else { - builder.aggregate(builder.groupKey(fields)); - } - break; + // fall through default: fields.add(builder.alias(builder.literal(true), "i")); builder.project(fields); builder.distinct(); } - builder.as("dt"); - final List<RexNode> conditions = new ArrayList<>(); - for (Pair<RexNode, RexNode> pair - : Pair.zip(e.getOperands(), builder.fields())) { - conditions.add( - builder.equals(pair.left, RexUtil.shift(pair.right, offset))); - } - switch (logic) { - case TRUE: - builder.join(JoinRelType.INNER, builder.and(conditions), variablesSet); - return builder.literal(true); - } - builder.join(JoinRelType.LEFT, builder.and(conditions), variablesSet); + } - final List<RexNode> keyIsNulls = new ArrayList<>(); - for (RexNode operand : e.getOperands()) { - if (operand.getType().isNullable()) { - keyIsNulls.add(builder.isNull(operand)); + builder.as("dt"); + int refOffset = offset; + final List<RexNode> conditions = + Pair.zip(expressionOperands, builder.fields()).stream() + .map(pair -> builder.equals(pair.left, RexUtil.shift(pair.right, refOffset))) + .collect(Collectors.toList()); + switch (logic) { + case TRUE: + builder.join(JoinRelType.INNER, builder.and(conditions), variablesSet); + return builder.literal(true); + } + // Now the left join + builder.join(JoinRelType.LEFT, builder.and(conditions), variablesSet); + + final ImmutableList.Builder<RexNode> operands = ImmutableList.builder(); + Boolean b = true; + switch (logic) { + case TRUE_FALSE_UNKNOWN: + b = null; + // fall through + case UNKNOWN_AS_TRUE: + if (allLiterals) { + // Considers case when right side of IN is empty + // for the case of non-correlated sub-queries + if (variablesSet.isEmpty()) { + operands.add( + builder.isNull(builder.field("c")), + builder.literal(false)); } - } - final ImmutableList.Builder<RexNode> operands = ImmutableList.builder(); - switch (logic) { - case TRUE_FALSE_UNKNOWN: - case UNKNOWN_AS_TRUE: + operands.add( + builder.equals(builder.field("cs"), builder.literal(false)), + builder.literal(b)); + } else { operands.add( builder.equals(builder.field("ct", "c"), builder.literal(0)), builder.literal(false)); - break; } + break; + } + + if (!keyIsNulls.isEmpty()) { + operands.add(builder.or(keyIsNulls), builder.literal(null)); + } + + if (allLiterals) { + operands.add(builder.isNotNull(builder.field("cs")), + builder.literal(true)); + } else { operands.add(builder.isNotNull(Util.last(builder.fields())), builder.literal(true)); - if (!keyIsNulls.isEmpty()) { - operands.add(builder.or(keyIsNulls), builder.literal(null)); - } - Boolean b = true; + } + + if (!allLiterals) { switch (logic) { case TRUE_FALSE_UNKNOWN: - b = null; - // fall through case UNKNOWN_AS_TRUE: operands.add( builder.call(SqlStdOperatorTable.LESS_THAN, builder.field("ct", "ck"), builder.field("ct", "c")), builder.literal(b)); - break; } - operands.add(builder.literal(false)); - return builder.call(SqlStdOperatorTable.CASE, operands.build()); - - default: - throw new AssertionError(e.getKind()); } + operands.add(builder.literal(false)); + return builder.call(SqlStdOperatorTable.CASE, operands.build()); } /** Returns a reference to a particular field, by offset, across several @@ -404,6 +492,102 @@ public abstract class SubQueryRemoveRule extends RelOptRule { return projects; } + /** Rule that converts sub-queries from project expressions into + * {@link Correlate} instances. */ + public static class SubQueryProjectRemoveRule extends SubQueryRemoveRule { + public SubQueryProjectRemoveRule(RelBuilderFactory relBuilderFactory) { + super( + operand(Project.class, null, RexUtil.SubQueryFinder.PROJECT_PREDICATE, + any()), relBuilderFactory, "SubQueryRemoveRule:Project"); + } + + public void onMatch(RelOptRuleCall call) { + final Project project = call.rel(0); + final RelBuilder builder = call.builder(); + final RexSubQuery e = + RexUtil.SubQueryFinder.find(project.getProjects()); + assert e != null; + final RelOptUtil.Logic logic = + LogicVisitor.find(RelOptUtil.Logic.TRUE_FALSE_UNKNOWN, + project.getProjects(), e); + builder.push(project.getInput()); + final int fieldCount = builder.peek().getRowType().getFieldCount(); + final RexNode target = apply(e, ImmutableSet.of(), + logic, builder, 1, fieldCount); + final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target); + builder.project(shuttle.apply(project.getProjects()), + project.getRowType().getFieldNames()); + call.transformTo(builder.build()); + } + } + + /** Rule that converts a sub-queries from filter expressions into + * {@link Correlate} instances. */ + public static class SubQueryFilterRemoveRule extends SubQueryRemoveRule { + public SubQueryFilterRemoveRule(RelBuilderFactory relBuilderFactory) { + super( + operand(Filter.class, null, RexUtil.SubQueryFinder.FILTER_PREDICATE, + any()), relBuilderFactory, "SubQueryRemoveRule:Filter"); + } + + public void onMatch(RelOptRuleCall call) { + final Filter filter = call.rel(0); + final RelBuilder builder = call.builder(); + builder.push(filter.getInput()); + int count = 0; + RexNode c = filter.getCondition(); + while (true) { + final RexSubQuery e = RexUtil.SubQueryFinder.find(c); + if (e == null) { + assert count > 0; + break; + } + ++count; + final RelOptUtil.Logic logic = + LogicVisitor.find(RelOptUtil.Logic.TRUE, ImmutableList.of(c), e); + final Set<CorrelationId> variablesSet = + RelOptUtil.getVariablesUsed(e.rel); + final RexNode target = apply(e, variablesSet, logic, + builder, 1, builder.peek().getRowType().getFieldCount()); + final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target); + c = c.accept(shuttle); + } + builder.filter(c); + builder.project(fields(builder, filter.getRowType().getFieldCount())); + call.transformTo(builder.build()); + } + } + + /** Rule that converts sub-queries from join expressions into + * {@link Correlate} instances. */ + public static class SubQueryJoinRemoveRule extends SubQueryRemoveRule { + public SubQueryJoinRemoveRule(RelBuilderFactory relBuilderFactory) { + super( + operand(Join.class, null, RexUtil.SubQueryFinder.JOIN_PREDICATE, + any()), relBuilderFactory, "SubQueryRemoveRule:Join"); + } + + public void onMatch(RelOptRuleCall call) { + final Join join = call.rel(0); + final RelBuilder builder = call.builder(); + final RexSubQuery e = + RexUtil.SubQueryFinder.find(join.getCondition()); + assert e != null; + final RelOptUtil.Logic logic = + LogicVisitor.find(RelOptUtil.Logic.TRUE, + ImmutableList.of(join.getCondition()), e); + builder.push(join.getLeft()); + builder.push(join.getRight()); + final int fieldCount = join.getRowType().getFieldCount(); + final RexNode target = apply(e, ImmutableSet.of(), + logic, builder, 2, fieldCount); + final RexShuttle shuttle = new ReplaceSubQueryShuttle(e, target); + builder.join(join.getJoinType(), shuttle.apply(join.getCondition())); + builder.project(fields(builder, join.getRowType().getFieldCount())); + call.transformTo(builder.build()); + } + } + /** Shuttle that replaces occurrences of a given * {@link org.apache.calcite.rex.RexSubQuery} with a replacement * expression. */ http://git-wip-us.apache.org/repos/asf/calcite/blob/1ae6a526/core/src/main/java/org/apache/calcite/rex/RexUtil.java ---------------------------------------------------------------------- diff --git a/core/src/main/java/org/apache/calcite/rex/RexUtil.java b/core/src/main/java/org/apache/calcite/rex/RexUtil.java index 6f467be..1e0fb15 100644 --- a/core/src/main/java/org/apache/calcite/rex/RexUtil.java +++ b/core/src/main/java/org/apache/calcite/rex/RexUtil.java @@ -260,7 +260,7 @@ public class RexUtil { */ public static boolean isLiteral(RexNode node, boolean allowCast) { assert node != null; - if (node instanceof RexLiteral) { + if (node.isA(SqlKind.LITERAL)) { return true; } if (allowCast) { @@ -276,6 +276,21 @@ public class RexUtil { } /** + * Returns whether every expression in a list is a literal. + * + * @param expressionOperands list of expressions to check + * @return true if every expression from the specified list is literal. + */ + public static boolean allLiterals(List<RexNode> expressionOperands) { + for (RexNode rexNode : expressionOperands) { + if (!isLiteral(rexNode, true)) { + return false; + } + } + return true; + } + + /** * Returns whether a node represents an input reference or field access. * * @param node The node, never null. http://git-wip-us.apache.org/repos/asf/calcite/blob/1ae6a526/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 af39d3c..36ef8ab 100644 --- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml +++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml @@ -7378,7 +7378,7 @@ LogicalFilter(condition=[<($0, 20)]) </Resource> <Resource name="planAfter"> <![CDATA[ -LogicalProject(EMPNO=[$0], D=[CASE(IS NOT NULL($9), true, false)]) +LogicalProject(EMPNO=[$0], D=[IS NOT NULL($9)]) LogicalJoin(condition=[true], joinType=[left]) LogicalTableScan(table=[[CATALOG, SALES, EMP]]) LogicalAggregate(group=[{0}]) @@ -7839,7 +7839,7 @@ LogicalProject(DEPTNO=[$1]) </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)]) +LogicalProject(EMPNO=[$0], D=[CASE(=($2, 0), false, IS NULL($1), null, IS NOT NULL($5), true, <($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)]) http://git-wip-us.apache.org/repos/asf/calcite/blob/1ae6a526/core/src/test/resources/sql/blank.iq ---------------------------------------------------------------------- diff --git a/core/src/test/resources/sql/blank.iq b/core/src/test/resources/sql/blank.iq index bcaa9e8..620f123 100644 --- a/core/src/test/resources/sql/blank.iq +++ b/core/src/test/resources/sql/blank.iq @@ -73,7 +73,7 @@ insert into table2 values (NULL, 1), (2, 1); # Checked on Oracle !set lateDecorrelate true select i, j from table1 where table1.j NOT IN (select i from table2 where table1.i=table2.j); -EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0], expr#9=[=($t3, $t8)], expr#10=[false], expr#11=[IS NOT NULL($t7)], expr#12=[true], expr#13=[IS NULL($t1)], expr#14=[null], expr#15=[<($t4, $t3)], expr#16=[CASE($t9, $t10, $t11, $t12, $t13, $t14, $t15, $t12, $t10)], expr#17=[NOT($t16)], proj#0..1=[{exprs}], $condition=[$t17]) +EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0], expr#9=[=($t3, $t8)], expr#10=[false], expr#11=[IS NULL($t1)], expr#12=[null], expr#13=[IS NOT NULL($t7)], expr#14=[true], expr#15=[<($t4, $t3)], expr#16=[CASE($t9, $t10, $t11, $t12, $t13, $t14, $t15, $t14, $t10)], expr#17=[NOT($t16)], proj#0..1=[{exprs}], $condition=[$t17]) EnumerableJoin(condition=[AND(=($0, $6), =($1, $5))], joinType=[left]) EnumerableJoin(condition=[=($0, $2)], joinType=[left]) EnumerableTableScan(table=[[BLANK, TABLE1]]) http://git-wip-us.apache.org/repos/asf/calcite/blob/1ae6a526/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 c9142ea..5048445 100644 --- a/core/src/test/resources/sql/sub-query.iq +++ b/core/src/test/resources/sql/sub-query.iq @@ -32,7 +32,7 @@ where t1.x not in (select t2.x from t2); (0 rows) !ok -EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], expr#6=[=($t1, $t5)], expr#7=[false], expr#8=[IS NOT NULL($t4)], expr#9=[true], expr#10=[IS NULL($t0)], expr#11=[null], expr#12=[<($t2, $t1)], expr#13=[CASE($t6, $t7, $t8, $t9, $t10, $t11, $t12, $t9, $t7)], expr#14=[NOT($t13)], EXPR$0=[$t0], $condition=[$t14]) +EnumerableCalc(expr#0..4=[{inputs}], expr#5=[0], expr#6=[=($t1, $t5)], expr#7=[false], expr#8=[IS NULL($t0)], expr#9=[null], expr#10=[IS NOT NULL($t4)], expr#11=[true], expr#12=[<($t2, $t1)], expr#13=[CASE($t6, $t7, $t8, $t9, $t10, $t11, $t12, $t11, $t7)], expr#14=[NOT($t13)], EXPR$0=[$t0], $condition=[$t14]) EnumerableJoin(condition=[=($0, $3)], joinType=[left]) EnumerableJoin(condition=[true], joinType=[inner]) EnumerableUnion(all=[true]) @@ -757,4 +757,1272 @@ and empno in (7876, 7698, 7900); !ok +!set outputformat psql + +!set expand false + +# [CALCITE-2329] Enhance SubQueryRemoveRule to rewrite IN operator with the constant from the left side more optimally +# Test project null IN null +select sal, + cast(null as int) IN ( + select cast(null as int) + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | null + 1250.00 | null + 1250.00 | null + 1300.00 | null + 1500.00 | null + 1600.00 | null + 2450.00 | null + 2850.00 | null + 2975.00 | null + 3000.00 | null + 3000.00 | null + 5000.00 | null + 800.00 | null + 950.00 | null +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t2)], expr#11=[true], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t7, $t10, $t11, $t5)], SAL=[$t1], EXPR$1=[$t12]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project literal IN null non-correlated +select sal, + 123 IN ( + select cast(null as int) + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | null + 1250.00 | null + 1250.00 | null + 1300.00 | null + 1500.00 | null + 1600.00 | null + 2450.00 | null + 2850.00 | null + 2975.00 | null + 3000.00 | null + 3000.00 | null + 5000.00 | null + 800.00 | null + 950.00 | null +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[IS NOT NULL($t2)], expr#9=[true], expr#10=[CASE($t4, $t5, $t6, $t7, $t8, $t9, $t5)], SAL=[$t1], EXPR$1=[$t10]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project null IN literal non-correlated +select sal, + cast(null as int) IN ( + select 1 + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | null + 1250.00 | null + 1250.00 | null + 1300.00 | null + 1500.00 | null + 1600.00 | null + 2450.00 | null + 2850.00 | null + 2975.00 | null + 3000.00 | null + 3000.00 | null + 5000.00 | null + 800.00 | null + 950.00 | null +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t2)], expr#11=[true], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t7, $t10, $t11, $t5)], SAL=[$t1], EXPR$1=[$t12]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project null IN required +select sal, + cast(null as int) IN ( + select deptno + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | null + 1250.00 | null + 1250.00 | null + 1300.00 | null + 1500.00 | null + 1600.00 | null + 2450.00 | null + 2850.00 | null + 2975.00 | null + 3000.00 | null + 3000.00 | null + 5000.00 | null + 800.00 | null + 950.00 | null +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t2)], expr#11=[true], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t7, $t10, $t11, $t5)], SAL=[$t1], EXPR$1=[$t12]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project null IN nullable +select sal, + cast(null as int) IN ( + select case when true then deptno else null end + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | null + 1250.00 | null + 1250.00 | null + 1300.00 | null + 1500.00 | null + 1600.00 | null + 2450.00 | null + 2850.00 | null + 2975.00 | null + 3000.00 | null + 3000.00 | null + 5000.00 | null + 800.00 | null + 950.00 | null +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t2)], expr#11=[true], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t7, $t10, $t11, $t5)], SAL=[$t1], EXPR$1=[$t12]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project literal IN required +select sal, + 10 IN ( + select deptno + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | true + 1250.00 | true + 1250.00 | true + 1300.00 | true + 1500.00 | true + 1600.00 | true + 2450.00 | true + 2850.00 | true + 2975.00 | true + 3000.00 | true + 3000.00 | true + 5000.00 | true + 800.00 | true + 950.00 | true +(14 rows) + +!ok +EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t2)], SAL=[$t1], EXPR$1=[$t3]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableAggregate(group=[{0}]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project literal IN nullable +select sal, + 10 IN ( + select case when true then deptno else null end + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | true + 1250.00 | true + 1250.00 | true + 1300.00 | true + 1500.00 | true + 1600.00 | true + 2450.00 | true + 2850.00 | true + 2975.00 | true + 3000.00 | true + 3000.00 | true + 5000.00 | true + 800.00 | true + 950.00 | true +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[IS NOT NULL($t2)], expr#9=[true], expr#10=[CASE($t4, $t5, $t6, $t7, $t8, $t9, $t5)], SAL=[$t1], EXPR$1=[$t10]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableLimit(fetch=[1]) + EnumerableSort(sort0=[$0], dir0=[DESC]) + EnumerableAggregate(group=[{0}], c=[COUNT()]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[CAST($t0):TINYINT], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t5)], expr#8=[OR($t6, $t7)], cs=[$t3], $condition=[$t8]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project null NOT IN null non-correlated +select sal, + cast(null as int) NOT IN ( + select cast(null as int) + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | null + 1250.00 | null + 1250.00 | null + 1300.00 | null + 1500.00 | null + 1600.00 | null + 2450.00 | null + 2850.00 | null + 2975.00 | null + 3000.00 | null + 3000.00 | null + 5000.00 | null + 800.00 | null + 950.00 | null +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t2)], expr#11=[true], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t7, $t10, $t11, $t5)], expr#13=[NOT($t12)], SAL=[$t1], EXPR$1=[$t13]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project literal NOT IN null non-correlated +select sal, + 123 NOT IN ( + select cast(null as int) + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | null + 1250.00 | null + 1250.00 | null + 1300.00 | null + 1500.00 | null + 1600.00 | null + 2450.00 | null + 2850.00 | null + 2975.00 | null + 3000.00 | null + 3000.00 | null + 5000.00 | null + 800.00 | null + 950.00 | null +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[IS NOT NULL($t2)], expr#9=[true], expr#10=[CASE($t4, $t5, $t6, $t7, $t8, $t9, $t5)], expr#11=[NOT($t10)], SAL=[$t1], EXPR$1=[$t11]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project null NOT IN literal non-correlated +select sal, + cast(null as int) NOT IN ( + select 1 + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | null + 1250.00 | null + 1250.00 | null + 1300.00 | null + 1500.00 | null + 1600.00 | null + 2450.00 | null + 2850.00 | null + 2975.00 | null + 3000.00 | null + 3000.00 | null + 5000.00 | null + 800.00 | null + 950.00 | null +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t2)], expr#11=[true], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t7, $t10, $t11, $t5)], expr#13=[NOT($t12)], SAL=[$t1], EXPR$1=[$t13]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project null NOT IN required +select sal, + cast(null as int) NOT IN ( + select deptno + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | null + 1250.00 | null + 1250.00 | null + 1300.00 | null + 1500.00 | null + 1600.00 | null + 2450.00 | null + 2850.00 | null + 2975.00 | null + 3000.00 | null + 3000.00 | null + 5000.00 | null + 800.00 | null + 950.00 | null +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t2)], expr#11=[true], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t7, $t10, $t11, $t5)], expr#13=[NOT($t12)], SAL=[$t1], EXPR$1=[$t13]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project null NOT IN nullable +select sal, + cast(null as int) NOT IN ( + select case when true then deptno else null end + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | null + 1250.00 | null + 1250.00 | null + 1300.00 | null + 1500.00 | null + 1600.00 | null + 2450.00 | null + 2850.00 | null + 2975.00 | null + 3000.00 | null + 3000.00 | null + 5000.00 | null + 800.00 | null + 950.00 | null +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t2)], expr#11=[true], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t7, $t10, $t11, $t5)], expr#13=[NOT($t12)], SAL=[$t1], EXPR$1=[$t13]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project literal NOT IN required +select sal, + 10 NOT IN ( + select deptno + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | false + 1250.00 | false + 1250.00 | false + 1300.00 | false + 1500.00 | false + 1600.00 | false + 2450.00 | false + 2850.00 | false + 2975.00 | false + 3000.00 | false + 3000.00 | false + 5000.00 | false + 800.00 | false + 950.00 | false +(14 rows) + +!ok +EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t2)], expr#4=[true], expr#5=[false], expr#6=[CASE($t3, $t4, $t5)], expr#7=[NOT($t6)], SAL=[$t1], EXPR$1=[$t7]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableAggregate(group=[{0}]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project literal NOT IN nullable +select sal, + 10 NOT IN ( + select case when true then deptno else null end + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | false + 1250.00 | false + 1250.00 | false + 1300.00 | false + 1500.00 | false + 1600.00 | false + 2450.00 | false + 2850.00 | false + 2975.00 | false + 3000.00 | false + 3000.00 | false + 5000.00 | false + 800.00 | false + 950.00 | false +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[IS NOT NULL($t2)], expr#9=[true], expr#10=[CASE($t4, $t5, $t6, $t7, $t8, $t9, $t5)], expr#11=[NOT($t10)], SAL=[$t1], EXPR$1=[$t11]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableLimit(fetch=[1]) + EnumerableSort(sort0=[$0], dir0=[DESC]) + EnumerableAggregate(group=[{0}], c=[COUNT()]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[CAST($t0):TINYINT], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t5)], expr#8=[OR($t6, $t7)], cs=[$t3], $condition=[$t8]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test project null IN required is unknown +select sal, + cast(null as int) IN ( + select deptno + from "scott".dept) is unknown +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | true + 1250.00 | true + 1250.00 | true + 1300.00 | true + 1500.00 | true + 1600.00 | true + 2450.00 | true + 2850.00 | true + 2975.00 | true + 3000.00 | true + 3000.00 | true + 5000.00 | true + 800.00 | true + 950.00 | true +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t2)], expr#11=[true], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t7, $t10, $t11, $t5)], expr#13=[IS NULL($t12)], SAL=[$t1], EXPR$1=[$t13]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter null IN null +select sal from "scott".emp + where cast(null as int) IN ( + select cast(null as int) + from "scott".dept); + SAL +----- +(0 rows) + +!ok +EnumerableValues(tuples=[[]]) +!plan + +# Test filter literal IN null non-correlated +select sal from "scott".emp + where 123 IN ( + select cast(null as int) + from "scott".dept); + SAL +----- +(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]]) +!plan + +# Test filter null IN literal non-correlated +select sal from "scott".emp + where cast(null as int) IN ( + select 1 + from "scott".dept); + SAL +----- +(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]]) +!plan + +# Test filter null IN required +select sal from "scott".emp + where cast(null as int) IN ( + select deptno + from "scott".dept); + SAL +----- +(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]]) +!plan + +# Test filter null IN nullable +select sal from "scott".emp + where cast(null as int) IN ( + select case when true then deptno else null end + from "scott".dept); + SAL +----- +(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]]) +!plan + +# Test filter literal IN required +select sal from "scott".emp + where 10 IN ( + select deptno + from "scott".dept); + SAL +--------- + 1100.00 + 1250.00 + 1250.00 + 1300.00 + 1500.00 + 1600.00 + 2450.00 + 2850.00 + 2975.00 + 3000.00 + 3000.00 + 5000.00 + 800.00 + 950.00 +(14 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=[10], 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]]) +!plan + +# Test filter literal IN nullable +select sal from "scott".emp + where 10 IN ( + select case when true then deptno else null end + from "scott".dept); + SAL +--------- + 1100.00 + 1250.00 + 1250.00 + 1300.00 + 1500.00 + 1600.00 + 2450.00 + 2850.00 + 2975.00 + 3000.00 + 3000.00 + 5000.00 + 800.00 + 950.00 +(14 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=[10], 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]]) +!plan + +# Test filter null NOT IN null non-correlated +select sal from "scott".emp + where cast(null as int) NOT IN ( + select cast(null as int) + from "scott".dept); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[true], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[null], expr#11=[IS NOT NULL($t2)], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t10, $t11, $t7, $t5)], expr#13=[NOT($t12)], SAL=[$t1], $condition=[$t13]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter literal NOT IN null non-correlated +select sal from "scott".emp + where 123 NOT IN ( + select cast(null as int) + from "scott".dept); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[true], expr#8=[IS NOT NULL($t2)], expr#9=[CASE($t4, $t5, $t6, $t7, $t8, $t7, $t5)], expr#10=[NOT($t9)], SAL=[$t1], $condition=[$t10]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter null NOT IN literal non-correlated +select sal from "scott".emp + where cast(null as int) NOT IN ( + select 1 + from "scott".dept); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[true], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[null], expr#11=[IS NOT NULL($t2)], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t10, $t11, $t7, $t5)], expr#13=[NOT($t12)], SAL=[$t1], $condition=[$t13]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter null NOT IN required +select sal from "scott".emp + where cast(null as int) NOT IN ( + select deptno + from "scott".dept); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[true], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[null], expr#11=[IS NOT NULL($t2)], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t10, $t11, $t7, $t5)], expr#13=[NOT($t12)], SAL=[$t1], $condition=[$t13]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter null NOT IN nullable +select sal from "scott".emp + where cast(null as int) NOT IN ( + select case when true then deptno else null end + from "scott".dept); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[true], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[null], expr#11=[IS NOT NULL($t2)], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t10, $t11, $t7, $t5)], expr#13=[NOT($t12)], SAL=[$t1], $condition=[$t13]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter literal NOT IN required +select sal from "scott".emp + where 10 NOT IN ( + select deptno + from "scott".dept); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[true], expr#8=[IS NOT NULL($t2)], expr#9=[CASE($t4, $t5, $t6, $t7, $t8, $t7, $t5)], expr#10=[NOT($t9)], SAL=[$t1], $condition=[$t10]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableLimit(fetch=[1]) + EnumerableSort(sort0=[$0], dir0=[DESC]) + EnumerableAggregate(group=[{0}], c=[COUNT()]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[=($t4, $t0)], cs=[$t3], $condition=[$t5]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter literal NOT IN nullable +select sal from "scott".emp + where 10 NOT IN ( + select case when true then deptno else null end + from "scott".dept); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[true], expr#8=[IS NOT NULL($t2)], expr#9=[CASE($t4, $t5, $t6, $t7, $t8, $t7, $t5)], expr#10=[NOT($t9)], SAL=[$t1], $condition=[$t10]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + EnumerableLimit(fetch=[1]) + EnumerableSort(sort0=[$0], dir0=[DESC]) + EnumerableAggregate(group=[{0}], c=[COUNT()]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[true], expr#4=[10], expr#5=[CAST($t0):TINYINT], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t5)], expr#8=[OR($t6, $t7)], cs=[$t3], $condition=[$t8]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter null IN required is unknown +select sal from "scott".emp + where cast(null as int) IN ( + select deptno + from "scott".dept) is unknown; + SAL +--------- + 1100.00 + 1250.00 + 1250.00 + 1300.00 + 1500.00 + 1600.00 + 2450.00 + 2850.00 + 2975.00 + 3000.00 + 3000.00 + 5000.00 + 800.00 + 950.00 +(14 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NULL($t3)], expr#5=[false], expr#6=[=($t2, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t2)], expr#11=[true], expr#12=[CASE($t4, $t5, $t6, $t7, $t9, $t7, $t10, $t11, $t5)], expr#13=[IS NULL($t12)], SAL=[$t1], $condition=[$t13]) + EnumerableJoin(condition=[true], joinType=[left]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5]) + EnumerableTableScan(table=[[scott, EMP]]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +#------------------------------- + +# Test filter null IN null correlated +select sal from "scott".emp e + where cast(null as int) IN ( + select cast(null as int) + from "scott".dept d where e.deptno=d.deptno); + SAL +----- +(0 rows) + +!ok +EnumerableValues(tuples=[[]]) +!plan + +# Test filter literal IN null correlated +select sal from "scott".emp e + where 123 IN ( + select cast(null as int) + from "scott".dept d where e.deptno=d.deptno); + SAL +----- +(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]]) +!plan + +# Test filter null IN literal correlated +select sal from "scott".emp e + where cast(null as int) IN ( + select 1 + from "scott".dept d where e.deptno=d.deptno); + SAL +----- +(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..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Test filter null IN required correlated +select sal from "scott".emp e + where cast(null as int) IN ( + select deptno + from "scott".dept d where e.deptno=d.deptno); + SAL +----- +(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..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Test filter null IN nullable correlated +select sal from "scott".emp e + where cast(null as int) IN ( + select case when true then deptno else null end + from "scott".dept d where e.deptno=d.deptno); + SAL +----- +(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..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Test filter literal IN required correlated +select sal from "scott".emp e + where 10 IN ( + select deptno + from "scott".dept d where e.deptno=d.deptno); + SAL +--------- + 1300.00 + 2450.00 + 5000.00 +(3 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2]) + EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[=($t3, $t0)], DEPTNO=[$t0], $condition=[$t4]) + EnumerableTableScan(table=[[scott, DEPT]]) + EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], SAL=[$t5], DEPTNO=[$t7]) + EnumerableTableScan(table=[[scott, EMP]]) +!plan + +# Test filter literal IN nullable correlated +select sal from "scott".emp e + where 10 IN ( + select case when true then deptno else null end + from "scott".dept d where e.deptno=d.deptno); + SAL +--------- + 1300.00 + 2450.00 + 5000.00 +(3 rows) + +!ok +EnumerableCalc(expr#0..3=[{inputs}], SAL=[$t2]) + EnumerableJoin(condition=[=($0, $3)], joinType=[inner]) + EnumerableCalc(expr#0..2=[{inputs}], expr#3=[10], expr#4=[CAST($t0):TINYINT], 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]]) +!plan + +# Test filter null NOT IN null correlated +select sal from "scott".emp e + where cast(null as int) NOT IN ( + select cast(null as int) + from "scott".dept d where e.deptno=d.deptno); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t3, $t5)], expr#7=[true], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[null], expr#11=[IS NOT NULL($t3)], expr#12=[CASE($t6, $t7, $t9, $t10, $t11, $t7, $t5)], expr#13=[NOT($t12)], SAL=[$t1], $condition=[$t13]) + EnumerableJoin(condition=[=($2, $4)], 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=[null], expr#5=[IS NULL($t4)], cs=[$t3], DEPTNO=[$t0], $condition=[$t5]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter literal NOT IN null correlated +select sal from "scott".emp e + where 123 NOT IN ( + select cast(null as int) + from "scott".dept d where e.deptno=d.deptno); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t3, $t5)], expr#7=[true], expr#8=[IS NOT NULL($t3)], expr#9=[CASE($t6, $t7, $t8, $t7, $t5)], expr#10=[NOT($t9)], SAL=[$t1], $condition=[$t10]) + EnumerableJoin(condition=[=($2, $4)], 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)], cs=[$t3], DEPTNO=[$t0], $condition=[$t8]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter null NOT IN literal correlated +select sal from "scott".emp e + where cast(null as int) NOT IN ( + select 1 + from "scott".dept d where e.deptno=d.deptno); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t4, $t5)], expr#7=[true], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[null], expr#11=[IS NOT NULL($t4)], expr#12=[CASE($t6, $t7, $t9, $t10, $t11, $t7, $t5)], expr#13=[NOT($t12)], SAL=[$t1], $condition=[$t13]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter null NOT IN required correlated +select sal from "scott".emp e + where cast(null as int) NOT IN ( + select deptno + from "scott".dept d where e.deptno=d.deptno); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t4, $t5)], expr#7=[true], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[null], expr#11=[IS NOT NULL($t4)], expr#12=[CASE($t6, $t7, $t9, $t10, $t11, $t7, $t5)], expr#13=[NOT($t12)], SAL=[$t1], $condition=[$t13]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter null NOT IN nullable correlated +select sal from "scott".emp e + where cast(null as int) NOT IN ( + select case when true then deptno else null end + from "scott".dept d where e.deptno=d.deptno); + SAL +----- +(0 rows) + +!ok +EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t3, $t5)], expr#7=[true], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[null], expr#11=[IS NOT NULL($t3)], expr#12=[CASE($t6, $t7, $t9, $t10, $t11, $t7, $t5)], expr#13=[NOT($t12)], SAL=[$t1], $condition=[$t13]) + EnumerableJoin(condition=[=($2, $4)], 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($t5)], expr#8=[IS NULL($t4)], expr#9=[OR($t6, $t7, $t8)], cs=[$t3], DEPTNO=[$t0], $condition=[$t9]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter literal NOT IN required correlated +select sal from "scott".emp e + where 10 NOT IN ( + select deptno + from "scott".dept d where e.deptno=d.deptno); + SAL +--------- + 1100.00 + 1250.00 + 1250.00 + 1500.00 + 1600.00 + 2850.00 + 2975.00 + 3000.00 + 3000.00 + 800.00 + 950.00 +(11 rows) + +!ok +EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t4, $t5)], expr#7=[true], expr#8=[IS NOT NULL($t4)], expr#9=[CASE($t6, $t7, $t8, $t7, $t5)], expr#10=[NOT($t9)], SAL=[$t1], $condition=[$t10]) + 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=[10], expr#5=[=($t4, $t0)], DEPTNO1=[$t0], $f1=[$t3], $condition=[$t5]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter literal NOT IN nullable correlated +select sal from "scott".emp e + where 10 NOT IN ( + select case when true then deptno else null end + from "scott".dept d where e.deptno=d.deptno); + SAL +--------- + 1100.00 + 1250.00 + 1250.00 + 1500.00 + 1600.00 + 2850.00 + 2975.00 + 3000.00 + 3000.00 + 800.00 + 950.00 +(11 rows) + +!ok +EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t3, $t5)], expr#7=[true], expr#8=[IS NOT NULL($t3)], expr#9=[CASE($t6, $t7, $t8, $t7, $t5)], expr#10=[NOT($t9)], SAL=[$t1], $condition=[$t10]) + EnumerableJoin(condition=[=($2, $4)], 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=[10], expr#5=[CAST($t0):TINYINT], expr#6=[=($t4, $t5)], expr#7=[IS NULL($t5)], expr#8=[OR($t6, $t7)], cs=[$t3], DEPTNO=[$t0], $condition=[$t8]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + +# Test filter null IN required is unknown correlated +select sal from "scott".emp e + where cast(null as int) IN ( + select deptno + from "scott".dept d where e.deptno=d.deptno) is unknown; + SAL +--------- + 1100.00 + 1250.00 + 1250.00 + 1300.00 + 1500.00 + 1600.00 + 2450.00 + 2850.00 + 2975.00 + 3000.00 + 3000.00 + 5000.00 + 800.00 + 950.00 +(14 rows) + +!ok +EnumerableCalc(expr#0..4=[{inputs}], expr#5=[false], expr#6=[=($t4, $t5)], expr#7=[null], expr#8=[null], expr#9=[IS NULL($t8)], expr#10=[IS NOT NULL($t4)], expr#11=[true], expr#12=[CASE($t6, $t7, $t9, $t7, $t10, $t11, $t5)], expr#13=[IS NULL($t12)], SAL=[$t1], $condition=[$t13]) + 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]) + EnumerableTableScan(table=[[scott, DEPT]]) +!plan + + +# Test project constant IN an expression that is sometimes null +select sal, + 20 IN ( + select case when deptno > 10 then deptno else null end + from "scott".dept) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | true + 1250.00 | true + 1250.00 | true + 1300.00 | true + 1500.00 | true + 1600.00 | true + 2450.00 | true + 2850.00 | true + 2975.00 | true + 3000.00 | true + 3000.00 | true + 5000.00 | true + 800.00 | true + 950.00 | true +(14 rows) + +!ok + +# Test project constant IN an nullable expression in an empty relation +select sal, + 20 IN ( + select case when deptno > 10 then deptno else null end + from "scott".dept + where deptno < 0) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | false + 1250.00 | false + 1250.00 | false + 1300.00 | false + 1500.00 | false + 1600.00 | false + 2450.00 | false + 2850.00 | false + 2975.00 | false + 3000.00 | false + 3000.00 | false + 5000.00 | false + 800.00 | false + 950.00 | false +(14 rows) + +!ok + +# Test project null IN an nullable expression in an empty relation +select sal, + cast(null as integer) IN ( + select case when deptno > 10 then deptno else null end + from "scott".dept + where deptno < 0) +from "scott".emp; + SAL | EXPR$1 +---------+-------- + 1100.00 | false + 1250.00 | false + 1250.00 | false + 1300.00 | false + 1500.00 | false + 1600.00 | false + 2450.00 | false + 2850.00 | false + 2975.00 | false + 3000.00 | false + 3000.00 | false + 5000.00 | false + 800.00 | false + 950.00 | false +(14 rows) + +!ok + # End sub-query.iq
