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 2018/06/13 03:21:02 UTC
[2/4] calcite git commit: [CALCITE-2329] Improve rewrite for
"constant IN (sub-query)"
[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 <vv...@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