This is an automated email from the ASF dual-hosted git repository.
suibianwanwan pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/calcite.git
The following commit(s) were added to refs/heads/main by this push:
new fd57e660f2 [CALCITE-7010] The well-known count bug
fd57e660f2 is described below
commit fd57e660f2dd76af0f68b36a09be6c0a9cd9ca0e
Author: suibianwanwan <[email protected]>
AuthorDate: Sat Jun 21 18:14:59 2025 +0800
[CALCITE-7010] The well-known count bug
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 303 ++++++++++++++++++---
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 14 -
.../calcite/sql2rel/RelDecorrelatorTest.java | 34 ++-
.../org/apache/calcite/test/RelOptRulesTest.xml | 50 ++--
.../apache/calcite/test/SqlToRelConverterTest.xml | 162 ++++++-----
core/src/test/resources/sql/blank.iq | 16 +-
core/src/test/resources/sql/sub-query.iq | 214 +++++++++------
7 files changed, 554 insertions(+), 239 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
index 06312d67b0..b5500c871b 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -45,7 +45,6 @@
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.RelFactories;
import org.apache.calcite.rel.core.Sort;
-import org.apache.calcite.rel.core.Values;
import org.apache.calcite.rel.logical.LogicalAggregate;
import org.apache.calcite.rel.logical.LogicalCorrelate;
import org.apache.calcite.rel.logical.LogicalFilter;
@@ -113,9 +112,11 @@
import org.slf4j.Logger;
import java.math.BigDecimal;
+import java.util.ArrayDeque;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Collections;
+import java.util.Deque;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
@@ -160,11 +161,16 @@ public class RelDecorrelator implements ReflectiveVisitor
{
// map built during translation
protected CorelMap cm;
+ /** Stack maintaining visible Frames to the currently invoked RelNode during
top-down traversal.
+ * Each entry maps a CorrelationId to the Frame where its correlated
variables originate. */
+ protected final Deque<Pair<CorrelationId, Frame>> frameStack = new
ArrayDeque<>();
+
@SuppressWarnings("method.invocation.invalid")
protected final ReflectUtil.MethodDispatcher<@Nullable Frame> dispatcher =
ReflectUtil.<RelNode, @Nullable Frame>createMethodDispatcher(
Frame.class, getVisitor(), "decorrelateRel",
RelNode.class,
+ boolean.class,
boolean.class);
// The rel which is being visited
@@ -332,7 +338,7 @@ protected RelNode decorrelate(RelNode root) {
// Perform decorrelation.
map.clear();
- final Frame frame = getInvoke(root, false, null);
+ final Frame frame = getInvoke(root, false, null, true);
if (frame != null) {
// Check if the frame has more fields than the original and discard the
extra ones
RelNode result = frame.r;
@@ -498,14 +504,16 @@ protected RexNode removeCorrelationExpr(
}
/** Fallback if none of the other {@code decorrelateRel} methods match. */
- public @Nullable Frame decorrelateRel(RelNode rel, boolean isCorVarDefined) {
+ public @Nullable Frame decorrelateRel(RelNode rel, boolean isCorVarDefined,
+ boolean parentPropagatesNullValues) {
RelNode newRel = rel.copy(rel.getTraitSet(), rel.getInputs());
if (!rel.getInputs().isEmpty()) {
List<RelNode> oldInputs = rel.getInputs();
List<RelNode> newInputs = new ArrayList<>();
for (int i = 0; i < oldInputs.size(); ++i) {
- final Frame frame = getInvoke(oldInputs.get(i), isCorVarDefined, rel);
+ final Frame frame =
+ getInvoke(oldInputs.get(i), isCorVarDefined, rel,
parentPropagatesNullValues);
if (frame == null || !frame.corDefOutputs.isEmpty()) {
// if input is not rewritten, or if it produces correlated
// variables, terminate rewrite
@@ -526,7 +534,8 @@ protected RexNode removeCorrelationExpr(
ImmutableSortedMap.of());
}
- public @Nullable Frame decorrelateRel(Sort rel, boolean isCorVarDefined) {
+ public @Nullable Frame decorrelateRel(Sort rel, boolean isCorVarDefined,
+ boolean parentPropagatesNullValues) {
//
// Rewrite logic:
//
@@ -543,7 +552,7 @@ protected RexNode removeCorrelationExpr(
// need to call propagateExpr.
final RelNode oldInput = rel.getInput();
- final Frame frame = getInvoke(oldInput, isCorVarDefined, rel);
+ final Frame frame = getInvoke(oldInput, isCorVarDefined, rel, true);
if (frame == null) {
// If input has not been rewritten, do not rewrite this rel.
return null;
@@ -581,16 +590,13 @@ protected RexNode removeCorrelationExpr(
return register(rel, newSort, frame.oldToNewOutputs, frame.corDefOutputs);
}
- public @Nullable Frame decorrelateRel(Values rel, boolean isCorVarDefined) {
- // There are no inputs, so rel does not need to be changed.
- return null;
- }
-
- public @Nullable Frame decorrelateRel(LogicalAggregate rel, boolean
isCorVarDefined) {
- return decorrelateRel((Aggregate) rel, isCorVarDefined);
+ public @Nullable Frame decorrelateRel(LogicalAggregate rel, boolean
isCorVarDefined,
+ boolean parentPropagatesNullValues) {
+ return decorrelateRel((Aggregate) rel, isCorVarDefined,
parentPropagatesNullValues);
}
- public @Nullable Frame decorrelateRel(Aggregate rel, boolean
isCorVarDefined) {
+ public @Nullable Frame decorrelateRel(Aggregate rel, boolean isCorVarDefined,
+ boolean parentPropagatesNullValues) {
//
// Rewrite logic:
//
@@ -604,7 +610,7 @@ protected RexNode removeCorrelationExpr(
assert !cm.mapRefRelToCorRef.containsKey(rel);
final RelNode oldInput = rel.getInput();
- final Frame frame = getInvoke(oldInput, isCorVarDefined, rel);
+ final Frame frame = getInvoke(oldInput, isCorVarDefined, rel,
parentPropagatesNullValues);
if (frame == null) {
// If input has not been rewritten, do not rewrite this rel.
return null;
@@ -782,11 +788,196 @@ protected RexNode removeCorrelationExpr(
RelNode newRel = relBuilder.build();
+ for (AggregateCall aggCall : rel.getAggCallList()) {
+ if (aggCall.getAggregation() instanceof SqlCountAggFunction) {
+ parentPropagatesNullValues = false;
+ break;
+ }
+ }
+
+ if (rel.getGroupType() == Aggregate.Group.SIMPLE
+ && rel.getGroupSet().isEmpty()
+ && !frame.corDefOutputs.isEmpty()
+ && !parentPropagatesNullValues) {
+ newRel = rewriteScalarAggregate(rel, newRel, outputMap, corDefOutputs);
+ }
+
// Aggregate does not change input ordering so corVars will be
// located at the same position as the input newProject.
return register(rel, newRel, outputMap, corDefOutputs);
}
+ /**
+ * Special case where the group by is static (i.e., aggregation functions
without group by).
+ *
+ * <p>Background:
+ * For the query:
+ * SELECT SUM(salary), COUNT(name) FROM A;
+ * When table A is empty, it returns [null, 0].
+ * But for
+ * SELECT SUM(salary), COUNT(name) FROM A group by id
+ * When table A is empty, it returns empty. This causes result mismatch.
+ * In the general decorrelation framework, we add corVar as an additional
groupKey to
+ * rewrite Correlate as JOIN. (See the code above for details) This means
that when the input
+ * is empty, the result produced using a JOIN is incorrect.
+ *
+ * <p>We refer to this situation as: `The well-known count bug`,
+ * More details about this issue: Optimization of Nested SQL Queries
Revisited
+ * (https://dl.acm.org/doi/pdf/10.1145/38714.38723)
+ *
+ * <p>To handle this situation, we using a LEFT JOIN to ensure that an
output is always produced.
+ *
+ * <p>Given the SQL:
+ * SELECT deptno FROM dept d
+ * WHERE 0 = (SELECT COUNT(*) FROM emp e WHERE d.deptno = e.deptno)
+ * Corresponding plan:
+ * LogicalProject(DEPTNO=[$0])
+ * LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
+ * LogicalProject(DEPTNO=[$0])
+ * LogicalTableScan(table=[[scott, DEPT]])
+ * LogicalProject(cs=[true])
+ * LogicalFilter(condition=[=(0, $0)])
+ * LogicalAggregate(group=[{}], EXPR$0=[COUNT()])
+ * LogicalFilter(condition=[=($cor0.DEPTNO, $7)])
+ * LogicalTableScan(table=[[scott, EMP]])
+ *
+ * <p>Rewriting this as:
+ * SELECT d.deptno FROM dept d
+ * JOIN (
+ * SELECT true, e.deptno FROM emp e WHERE e.deptno IS NOT NULL
+ * GROUP BY e.deptno HAVING COUNT(*) = 0
+ * ) AS d0 ON d.deptno = d0.deptno
+ * produces an incorrect result.
+ * Corresponding plan:
+ * LogicalProject(DEPTNO=[$0])
+ * LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ * LogicalProject(DEPTNO=[$0])
+ * LogicalTableScan(table=[[scott, DEPT]])
+ * LogicalProject(cs=[true], DEPTNO=[$0])
+ * LogicalFilter(condition=[=(0, $1)])
+ * LogicalAggregate(group=[{0}], EXPR$0=[COUNT()]) // corresponds
to {@code oldRel}
+ * LogicalProject(DEPTNO=[$7])
+ * LogicalFilter(condition=[IS NOT NULL($7)])
+ * LogicalTableScan(table=[[scott, EMP]])
+ * We can clearly observe that due to the presence of the GROUP BY clause,
+ * COUNT(*) = 0 will never evaluate to true, since rows with zero records
won't appear
+ * in the GROUP BY results. This produced incorrect results.
+ *
+ * <p>Rewrite Aggregate as:
+ * SELECT d.deptno FROM dept d
+ * JOIN (
+ * SELECT true AS cs, deptno
+ * FROM (
+ * SELECT d2.deptno,
+ * CASE WHEN cnt0 IS NOT NULL THEN cnt0 ELSE 0 END AS cnt
+ * FROM (SELECT deptno FROM dept GROUP BY deptno) d2
+ * LEFT JOIN (
+ * SELECT deptno, COUNT(e.empno) cnt0
+ * FROM emp
+ * WHERE deptno IS NOT NULL
+ * GROUP BY deptno) e
+ * ON d2.deptno IS NOT DISTINCT FROM e.deptno
+ * ) AS case_count
+ * WHERE cnt = 0
+ * ) AS d0 ON d.deptno = d0.deptno
+ * Corresponding plan:
+ * [01] LogicalProject(DEPTNO=[$0])
+ * [02] LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ * [03] LogicalProject(DEPTNO=[$0])
+ * [04] LogicalTableScan(table=[[scott, DEPT]])
+ * [05] LogicalProject(cs=[true], DEPTNO=[$0])
+ * [06] LogicalFilter(condition=[=(0, $1)])
+ * [07] LogicalProject(DEPTNO=[$0], EXPR$0=[CASE(IS NOT NULL($2),
$2, 0)])
+ * [08] LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ * [09] LogicalAggregate(group=[{0}])
+ * [10] LogicalProject(DEPTNO=[$0])
+ * [11] LogicalTableScan(table=[[scott, DEPT]])
+ * [12] LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])
+ * [13] LogicalProject(DEPTNO=[$7])
+ * [14] LogicalFilter(condition=[IS NOT NULL($7)])
+ * [15] LogicalTableScan(table=[[scott, EMP]])
+ *
+ * <p>Here we perform an early join, preserving all possible CorVar sets
from the outer scope
+ * and their corresponding aggregation results. This ensures that for any
row from the left
+ * input of the Correlation, there is always an aggregation result available
for join output.
+ *
+ * <p>Implementation based on: Improving Unnesting of Complex Queries
+ *
(https://dl.gi.de/server/api/core/bitstreams/c1918e8c-6a87-4da2-930a-bfed289f2388/content)
+ */
+ private RelNode rewriteScalarAggregate(Aggregate oldRel,
+ RelNode newRel,
+ Map<Integer, Integer> outputMap,
+ NavigableMap<CorDef, Integer> corDefOutputs) {
+ final Pair<CorrelationId, Frame> outerFramePair =
requireNonNull(this.frameStack.peek());
+ final Frame outFrame = outerFramePair.right;
+ RexBuilder rexBuilder = relBuilder.getRexBuilder();
+
+ int groupKeySize = (int) corDefOutputs.keySet().stream()
+ .filter(a -> a.corr.equals(outerFramePair.left))
+ .count();
+ List<RelDataTypeField> newRelFields = newRel.getRowType().getFieldList();
+ ImmutableBitSet.Builder corFieldBuilder = ImmutableBitSet.builder();
+
+ // Here we record the mapping between the original index and the new
project.
+ // For the count, we map it as `case when x is null then 0 else x`.
+ final Map<Integer, RexNode> newProjectMap = new HashMap<>();
+ final List<RexNode> conditions = new ArrayList<>();
+ for (Map.Entry<CorDef, Integer> corDefOutput : corDefOutputs.entrySet()) {
+ CorDef corDef = corDefOutput.getKey();
+ Integer corIndex = corDefOutput.getValue();
+ if (corDef.corr.equals(outerFramePair.left)) {
+ int newIdx =
requireNonNull(outFrame.oldToNewOutputs.get(corDef.field));
+ corFieldBuilder.set(newIdx);
+
+ RelDataType type =
outFrame.r.getRowType().getFieldList().get(newIdx).getType();
+ RexNode left = new RexInputRef(corFieldBuilder.cardinality() - 1,
type);
+ newProjectMap.put(corIndex + groupKeySize, left);
+ conditions.add(
+ relBuilder.isNotDistinctFrom(left,
+ new RexInputRef(corIndex + groupKeySize,
+ newRelFields.get(corIndex).getType())));
+ }
+ }
+
+ ImmutableBitSet groupSet = corFieldBuilder.build();
+ // Build [09] LogicalAggregate(group=[{0}]) to obtain the distinct set of
+ // corVar from outFrame.
+ relBuilder.push(outFrame.r)
+ .aggregate(relBuilder.groupKey(groupSet));
+
+ // Build [08] LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ // to ensure each corVar's aggregate result is output.
+ final RelNode join = relBuilder.push(newRel)
+ .join(JoinRelType.LEFT, conditions).build();
+
+ for (int i1 = 0; i1 < oldRel.getAggCallList().size(); i1++) {
+ AggregateCall aggCall = oldRel.getAggCallList().get(i1);
+ if (aggCall.getAggregation() instanceof SqlCountAggFunction) {
+ int index = requireNonNull(outputMap.get(i1 +
oldRel.getGroupSet().size()));
+ final RexInputRef ref = RexInputRef.of(index + groupKeySize,
join.getRowType());
+ RexNode specificCountValue =
+ rexBuilder.makeCall(SqlStdOperatorTable.CASE,
+ ImmutableList.of(relBuilder.isNotNull(ref), ref,
relBuilder.literal(0)));
+ newProjectMap.put(ref.getIndex(), specificCountValue);
+ }
+ }
+
+ final List<RexNode> newProjects = new ArrayList<>();
+ for (int index : ImmutableBitSet.range(groupKeySize,
join.getRowType().getFieldCount())) {
+ if (newProjectMap.containsKey(index)) {
+ newProjects.add(requireNonNull(newProjectMap.get(index)));
+ } else {
+ newProjects.add(RexInputRef.of(index, join.getRowType()));
+ }
+ }
+
+ // Build [07] LogicalProject(DEPTNO=[$0], EXPR$0=[CASE(IS NOT NULL($2),
$2, 0)])
+ // to handle COUNT function by converting nulls to zero.
+ return relBuilder.push(join)
+ .project(newProjects, newRel.getRowType().getFieldNames())
+ .build();
+ }
+
/**
* Shift the mapping to fixed offset from the {@code startIndex}.
*
@@ -803,8 +994,15 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
}
}
- public @Nullable Frame getInvoke(RelNode r, boolean isCorVarDefined,
@Nullable RelNode parent) {
- final Frame frame = dispatcher.invoke(r, isCorVarDefined);
+ /**
+ * Invokes decorrelation logic for a given relational expression.
+ *
+ * @param parentPropagatesNullValues True if the parent RelNode produces null
+ * when all of its inputs fields are null.
+ */
+ public @Nullable Frame getInvoke(RelNode r, boolean isCorVarDefined,
+ @Nullable RelNode parent, boolean parentPropagatesNullValues) {
+ final Frame frame = dispatcher.invoke(r, isCorVarDefined,
parentPropagatesNullValues);
currentRel = parent;
if (frame != null) {
map.put(r, frame);
@@ -964,19 +1162,26 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
return null;
}
- public @Nullable Frame decorrelateRel(LogicalProject rel, boolean
isCorVarDefined) {
- return decorrelateRel((Project) rel, isCorVarDefined);
+ public @Nullable Frame decorrelateRel(LogicalProject rel, boolean
isCorVarDefined,
+ boolean parentPropagatesNullValues) {
+ return decorrelateRel((Project) rel, isCorVarDefined,
parentPropagatesNullValues);
}
- public @Nullable Frame decorrelateRel(Project rel, boolean isCorVarDefined) {
+ public @Nullable Frame decorrelateRel(Project rel, boolean isCorVarDefined,
+ boolean parentPropagatesNullValues) {
//
// Rewrite logic:
//
// 1. Pass along any correlated variables coming from the input.
//
+ for (RexNode project : rel.getProjects()) {
+ if (!Strong.isStrong(project)) {
+ parentPropagatesNullValues = false;
+ }
+ }
final RelNode oldInput = rel.getInput();
- Frame frame = getInvoke(oldInput, isCorVarDefined, rel);
+ Frame frame = getInvoke(oldInput, isCorVarDefined, rel,
parentPropagatesNullValues);
if (frame == null) {
// If input has not been rewritten, do not rewrite this rel.
return null;
@@ -1326,25 +1531,29 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
&& type.getPrecision() >= type1.getPrecision();
}
- public @Nullable Frame decorrelateRel(LogicalSnapshot rel, boolean
isCorVarDefined) {
+ public @Nullable Frame decorrelateRel(LogicalSnapshot rel, boolean
isCorVarDefined,
+ boolean parentPropagatesNullValues) {
if (RexUtil.containsCorrelation(rel.getPeriod())) {
return null;
}
- return decorrelateRel((RelNode) rel, isCorVarDefined);
+ return decorrelateRel((RelNode) rel, isCorVarDefined,
parentPropagatesNullValues);
}
- public @Nullable Frame decorrelateRel(LogicalTableFunctionScan rel, boolean
isCorVarDefined) {
+ public @Nullable Frame decorrelateRel(LogicalTableFunctionScan rel, boolean
isCorVarDefined,
+ boolean parentPropagatesNullValues) {
if (RexUtil.containsCorrelation(rel.getCall())) {
return null;
}
- return decorrelateRel((RelNode) rel, isCorVarDefined);
+ return decorrelateRel((RelNode) rel, isCorVarDefined,
parentPropagatesNullValues);
}
- public @Nullable Frame decorrelateRel(LogicalFilter rel, boolean
isCorVarDefined) {
- return decorrelateRel((Filter) rel, isCorVarDefined);
+ public @Nullable Frame decorrelateRel(LogicalFilter rel, boolean
isCorVarDefined,
+ boolean parentPropagatesNullValues) {
+ return decorrelateRel((Filter) rel, isCorVarDefined,
parentPropagatesNullValues);
}
- public @Nullable Frame decorrelateRel(Filter rel, boolean isCorVarDefined) {
+ public @Nullable Frame decorrelateRel(Filter rel, boolean isCorVarDefined,
+ boolean parentPropagatesNullValues) {
//
// Rewrite logic:
//
@@ -1362,7 +1571,7 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
//
final RelNode oldInput = rel.getInput();
- Frame frame = getInvoke(oldInput, isCorVarDefined, rel);
+ Frame frame = getInvoke(oldInput, isCorVarDefined, rel,
parentPropagatesNullValues);
if (frame == null) {
// If input has not been rewritten, do not rewrite this rel.
return null;
@@ -1393,11 +1602,13 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
frame.corDefOutputs);
}
- public @Nullable Frame decorrelateRel(LogicalCorrelate rel, boolean
isCorVarDefined) {
- return decorrelateRel((Correlate) rel, isCorVarDefined);
+ public @Nullable Frame decorrelateRel(LogicalCorrelate rel, boolean
isCorVarDefined,
+ boolean parentPropagatesNullValues) {
+ return decorrelateRel((Correlate) rel, isCorVarDefined,
parentPropagatesNullValues);
}
- public @Nullable Frame decorrelateRel(Correlate rel, boolean
isCorVarDefined) {
+ public @Nullable Frame decorrelateRel(Correlate rel, boolean isCorVarDefined,
+ boolean parentPropagatesNullValues) {
//
// Rewrite logic:
//
@@ -1411,15 +1622,17 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
final RelNode oldLeft = rel.getInput(0);
final RelNode oldRight = rel.getInput(1);
- final Frame leftFrame = getInvoke(oldLeft, isCorVarDefined, rel);
- final Frame rightFrame = getInvoke(oldRight, true, rel);
-
- if (leftFrame == null || rightFrame == null) {
- // If any input has not been rewritten, do not rewrite this rel.
+ final Frame leftFrame = getInvoke(oldLeft, isCorVarDefined, rel,
parentPropagatesNullValues);
+ if (leftFrame == null) {
+ // If input has not been rewritten, do not rewrite this rel.
return null;
}
- if (rightFrame.corDefOutputs.isEmpty()) {
+ frameStack.push(Pair.of(rel.getCorrelationId(), leftFrame));
+ final Frame rightFrame = getInvoke(oldRight, true, rel,
parentPropagatesNullValues);
+ frameStack.pop();
+
+ if (rightFrame == null || rightFrame.corDefOutputs.isEmpty()) {
return null;
}
@@ -1504,16 +1717,18 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
return register(rel, newJoin, mapOldToNewOutputs, corDefOutputs);
}
- public @Nullable Frame decorrelateRel(LogicalJoin rel, boolean
isCorVarDefined) {
- return decorrelateRel((Join) rel, isCorVarDefined);
+ public @Nullable Frame decorrelateRel(LogicalJoin rel, boolean
isCorVarDefined,
+ boolean parentPropagatesNullValues) {
+ return decorrelateRel((Join) rel, isCorVarDefined,
parentPropagatesNullValues);
}
- public @Nullable Frame decorrelateRel(Join rel, boolean isCorVarDefined) {
+ public @Nullable Frame decorrelateRel(Join rel, boolean isCorVarDefined,
+ boolean parentPropagatesNullValues) {
// For SEMI/ANTI join decorrelate it's input directly,
// because the correlate variables can only be propagated from
// the left side, which is not supported yet.
if (!rel.getJoinType().projectsRight()) {
- return decorrelateRel((RelNode) rel, isCorVarDefined);
+ return decorrelateRel((RelNode) rel, isCorVarDefined,
parentPropagatesNullValues);
}
//
// Rewrite logic:
@@ -1525,8 +1740,8 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
final RelNode oldLeft = rel.getInput(0);
final RelNode oldRight = rel.getInput(1);
- final Frame leftFrame = getInvoke(oldLeft, isCorVarDefined, rel);
- final Frame rightFrame = getInvoke(oldRight, isCorVarDefined, rel);
+ final Frame leftFrame = getInvoke(oldLeft, isCorVarDefined, rel,
parentPropagatesNullValues);
+ final Frame rightFrame = getInvoke(oldRight, isCorVarDefined, rel,
parentPropagatesNullValues);
if (leftFrame == null || rightFrame == null) {
// If any input has not been rewritten, do not rewrite this rel.
diff --git
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 7872eaa485..968f3ae80e 100644
---
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -7505,20 +7505,6 @@ private void checkLiteral2(String expression, String
expected) {
callsUnparseCallOnSqlSelect[0], is(true));
}
- @Test void testCorrelate() {
- final String sql = "select d.\"department_id\", d_plusOne "
- + "from \"department\" as d, "
- + " lateral (select d.\"department_id\" + 1 as d_plusOne"
- + " from (values(true)))";
-
- final String expected = "SELECT \"$cor0\".\"department_id\",
\"t1\".\"D_PLUSONE\"\n"
- + "FROM (SELECT \"department_id\", \"department_description\",
\"department_id\" + 1 AS \"$f2\"\n"
- + "FROM \"foodmart\".\"department\") AS \"$cor0\",\n"
- + "LATERAL (SELECT \"$cor0\".\"$f2\" AS \"D_PLUSONE\"\n"
- + "FROM (VALUES (TRUE)) AS \"t\" (\"EXPR$0\")) AS \"t1\"";
- sql(sql).ok(expected);
- }
-
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-3651";>[CALCITE-3651]
* NullPointerException when convert relational algebra that correlates
TableFunctionScan</a>. */
diff --git
a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
index ab25202c24..40b6212778 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -269,17 +269,23 @@ public static Frameworks.ConfigBuilder config() {
// Verify plan
final String planAfter = ""
+ "LogicalProject(EXPR$0=[1])\n"
- + " LogicalJoin(condition=[AND(=($0, $2), >($1, $3))],
joinType=[inner])\n"
+ + " LogicalJoin(condition=[AND(IS NOT DISTINCT FROM($0, $2), >($1,
$3))], joinType=[inner])\n"
+ " LogicalAggregate(group=[{0}], TOTAL=[SUM($1)])\n"
+ " LogicalProject(DEPTNO=[$7], SAL=[$5])\n"
+ " LogicalTableScan(table=[[scott, EMP]])\n"
- + " LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])\n"
- + " LogicalProject(DEPTNO=[$0], TOTAL=[$1])\n"
- + " LogicalAggregate(group=[{0}], TOTAL=[SUM($1)])\n"
- + " LogicalProject(DEPTNO=[$0], SAL=[$1])\n"
- + " LogicalFilter(condition=[IS NOT NULL($0)])\n"
- + " LogicalProject(DEPTNO=[$7], SAL=[$5])\n"
- + " LogicalTableScan(table=[[scott, EMP]])\n";
+ + " LogicalProject(DEPTNO=[$0], EXPR$0=[$2])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])\n"
+ + " LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalAggregate(group=[{0}], TOTAL=[SUM($1)])\n"
+ + " LogicalProject(DEPTNO=[$7], SAL=[$5])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[AVG($1)])\n"
+ + " LogicalProject(DEPTNO=[$0], TOTAL=[$1])\n"
+ + " LogicalAggregate(group=[{0}], TOTAL=[SUM($1)])\n"
+ + " LogicalProject(DEPTNO=[$0], SAL=[$1])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($0)])\n"
+ + " LogicalProject(DEPTNO=[$7], SAL=[$5])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
assertThat(after, hasTree(planAfter));
}
@@ -360,11 +366,15 @@ public static Frameworks.ConfigBuilder config() {
RelDecorrelator.decorrelateQuery(original, builder, noRules);
final String planDecorrelatedNoRules = ""
+ "LogicalProject(EXPR$0=[ROW($9, $1)])\n"
- + " LogicalJoin(condition=[=($7, $8)], joinType=[left])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($7, $8)],
joinType=[left])\n"
+ " LogicalTableScan(table=[[scott, EMP]])\n"
- + " LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])\n"
- + " LogicalProject(DEPTNO1=[$0], DEPTNO=[$0])\n"
- + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ + " LogicalProject(DEPTNO1=[$0], $f1=[$2])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])\n"
+ + " LogicalAggregate(group=[{7}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])\n"
+ + " LogicalProject(DEPTNO1=[$0], DEPTNO=[$0])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
assertThat(decorrelatedNoRules, hasTree(planDecorrelatedNoRules));
}
}
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 d74fdacc52..c00c7dc012 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -16122,10 +16122,13 @@ LogicalProject(EXPR$0=[CAST(OR(AND(IS TRUE(>($0,
$9)), IS NOT TRUE(OR(IS NULL($1
LogicalProject(EXPR$0=[CAST(OR(AND(IS TRUE(>($0, $9)), IS NOT TRUE(OR(IS
NULL($12), =($10, 0)))), AND(IS TRUE(>($10, $11)), null, IS NOT TRUE(OR(IS
NULL($12), =($10, 0))), IS NOT TRUE(>($0, $9))), AND(>($0, $9), IS NOT
TRUE(OR(IS NULL($12), =($10, 0))), IS NOT TRUE(>($0, $9)), IS NOT TRUE(>($10,
$11))))):BOOLEAN NOT NULL])
LogicalJoin(condition=[=($2, $13)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(m=[$1], c=[$2], d=[$2], trueLiteral=[$3], NAME=[$0])
- LogicalAggregate(group=[{0}], m=[MIN($1)], c=[COUNT()],
trueLiteral=[LITERAL_AGG(true)])
- LogicalProject(NAME=[$1], DEPTNO=[$0])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(m=[$2], c=[CASE(IS NOT NULL($3), $3, 0)], d=[CASE(IS NOT
NULL($3), $3, 0)], trueLiteral=[$4], NAME=[$0])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[left])
+ LogicalAggregate(group=[{2}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], m=[MIN($1)], c=[COUNT()],
trueLiteral=[LITERAL_AGG(true)])
+ LogicalProject(NAME=[$1], DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -19393,13 +19396,16 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
<Resource name="planAfter">
<![CDATA[
LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8])
- LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], m=[CAST($9):INTEGER],
c=[CAST($10):BIGINT], d=[CAST($11):BIGINT], trueLiteral=[CAST($12):BOOLEAN],
NAME=[CAST($13):VARCHAR(10)])
+ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], HIREDATE=[$4],
SAL=[$5], COMM=[$6], DEPTNO=[$7], SLACKER=[$8], m=[$9], c=[CAST($10):BIGINT],
d=[CAST($11):BIGINT], trueLiteral=[$12], NAME=[CAST($13):VARCHAR(10)])
LogicalJoin(condition=[AND(=($2, $13), OR(AND(>($0, $9), IS NOT TRUE(OR(IS
NULL($12), =($10, 0)))), AND(>($0, $9), IS NOT TRUE(OR(IS NULL($12), =($10,
0))), IS NOT TRUE(>($0, $9)), IS NOT TRUE(>($10, $11)))))], joinType=[inner])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(m=[$1], c=[$2], d=[$2], trueLiteral=[$3], NAME=[$0])
- LogicalAggregate(group=[{0}], m=[MIN($1)], c=[COUNT()],
trueLiteral=[LITERAL_AGG(true)])
- LogicalProject(NAME=[$1], DEPTNO=[$0])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(m=[$2], c=[CASE(IS NOT NULL($3), $3, 0)], d=[CASE(IS NOT
NULL($3), $3, 0)], trueLiteral=[$4], NAME=[$0])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)], joinType=[left])
+ LogicalAggregate(group=[{2}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], m=[MIN($1)], c=[COUNT()],
trueLiteral=[LITERAL_AGG(true)])
+ LogicalProject(NAME=[$1], DEPTNO=[$0])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -19592,10 +19598,13 @@ LogicalProject(SAL=[$5])
LogicalJoin(condition=[AND(=($0, $12), =($2, $14))], joinType=[left])
LogicalJoin(condition=[=($2, $11)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(c=[$1], ck=[$1], NAME=[$0])
- LogicalAggregate(group=[{0}], c=[COUNT()])
- LogicalProject(NAME=[$1])
- LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalProject(c=[CASE(IS NOT NULL($2), $2, 0)], ck=[CASE(IS NOT
NULL($2), $2, 0)], NAME=[$0])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ LogicalAggregate(group=[{2}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], c=[COUNT()])
+ LogicalProject(NAME=[$1])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
LogicalProject(DEPTNO=[$0], i=[true], NAME=[$1])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
@@ -19665,12 +19674,15 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalJoin(condition=[AND(=($0, $12), =($1, $14))], joinType=[left])
LogicalJoin(condition=[=($1, $11)], joinType=[left])
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
- LogicalProject(c=[$1], ck=[$1], ENAME=[$0])
- LogicalAggregate(group=[{0}], c=[COUNT()])
- LogicalProject(ENAME=[$0])
- LogicalFilter(condition=[>($2, 2)])
- LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(c=[CASE(IS NOT NULL($2), $2, 0)], ck=[CASE(IS NOT
NULL($2), $2, 0)], ENAME=[$0])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ LogicalAggregate(group=[{1}])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalAggregate(group=[{0}], c=[COUNT()])
+ LogicalProject(ENAME=[$0])
+ LogicalFilter(condition=[>($2, 2)])
+ LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
LogicalProject(EMPNO=[$1], i=[true], ENAME=[$0])
LogicalFilter(condition=[>($2, 2)])
LogicalProject(ENAME=[$1], EMPNO=[$0], R=[$5])
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 3c3c646159..105ba0f9e4 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1491,14 +1491,20 @@ LogicalAggregate(group=[{}], EXPR$0=[COUNT()],
EXPR$1=[SUM($0)])
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], D=[$1], C0=[$2], F=[$3])
- LogicalProject(C=[$0], D=[$1], C0=[$4], F=[$5])
- LogicalCorrelate(correlation=[$cor3], joinType=[inner],
requiredColumns=[{2, 3}])
- LogicalProject(C=[$0], D=[$1], C0=[$0], $f3=[+($0, $1)])
- LogicalValues(tuples=[[{ 4, 5 }]])
- LogicalProject(C=[$cor3.C], F=[*($0, $cor3.C)])
- LogicalFilter(condition=[=($cor3.$f3, *($0, $cor3.C))])
+LogicalProject(C=[$0], D=[$1], C0=[$4], F=[$5])
+ LogicalJoin(condition=[AND(=($0, $6), =($3, $7))], joinType=[inner])
+ LogicalProject(C=[$0], D=[$1], C0=[$0], $f3=[+($0, $1)])
+ LogicalValues(tuples=[[{ 4, 5 }]])
+ LogicalProject(C=[$3], F=[*($0, $3)], C0=[$3], $f3=[$2])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalJoin(condition=[=($2, *($0, $1))], joinType=[inner])
LogicalValues(tuples=[[{ 2 }]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(C=[$0], $f3=[+($0, $1)])
+ LogicalValues(tuples=[[{ 4, 5 }]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(C=[$0])
+ LogicalValues(tuples=[[{ 4, 5 }]])
]]>
</Resource>
</TestCase>
@@ -1515,18 +1521,19 @@ cross join lateral
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], N=[$1])
- LogicalProject(C=[$0], N=[$3])
- LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{1, 2}])
- LogicalProject(C=[$0], C0=[$0], $f2=[+($0, 1)])
- LogicalValues(tuples=[[{ 4 }]])
- LogicalProject(N=[$0])
- LogicalFilter(condition=[=($cor1.C, $0)])
- LogicalProject(EXPR$0=[+($0, $1)])
- LogicalJoin(condition=[=($0, $1)], joinType=[inner])
- LogicalValues(tuples=[[{ 2 }]])
- LogicalProject(EXPR$0=[$cor1.$f2])
- LogicalValues(tuples=[[{ 3 }]])
+LogicalProject(C=[$0], N=[$3])
+ LogicalJoin(condition=[AND(=($0, $4), =($2, $5))], joinType=[inner])
+ LogicalProject(C=[$0], C0=[$0], $f2=[+($0, 1)])
+ LogicalValues(tuples=[[{ 4 }]])
+ LogicalProject(N=[+($0, $1)], EXPR$0=[+($0, $1)], $f2=[$2])
+ LogicalJoin(condition=[=($0, $1)], joinType=[inner])
+ LogicalValues(tuples=[[{ 2 }]])
+ LogicalProject(EXPR$0=[$1], $f2=[$1])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalValues(tuples=[[{ 3 }]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject($f2=[+($0, 1)])
+ LogicalValues(tuples=[[{ 4 }]])
]]>
</Resource>
</TestCase>
@@ -4508,11 +4515,14 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
<Resource name="plan">
<![CDATA[
LogicalProject(C=[$0], D=[$1])
- LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
+ LogicalJoin(condition=[=($0, $3)], joinType=[left])
LogicalValues(tuples=[[{ 4 }]])
- LogicalFilter(condition=[=($cor0.C, $1)])
- LogicalProject(C=[$cor0.C], EXPR$1=[*($0, $cor0.C)])
- LogicalValues(tuples=[[{ 2 }]])
+ LogicalFilter(condition=[=($2, $1)])
+ LogicalProject(C=[$1], EXPR$1=[*($0, $1)], C2=[$1])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalValues(tuples=[[{ 2 }]])
+ LogicalAggregate(group=[{0}])
+ LogicalValues(tuples=[[{ 4 }]])
]]>
</Resource>
</TestCase>
@@ -4525,11 +4535,14 @@ LogicalProject(C=[$0], D=[$1])
<Resource name="plan">
<![CDATA[
LogicalProject(C=[$0], D=[$1])
- LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
+ LogicalJoin(condition=[=($0, $3)], joinType=[left])
LogicalValues(tuples=[[{ 4 }]])
- LogicalFilter(condition=[=($cor0.C, $1)])
- LogicalProject(C=[$cor0.C], EXPR$1=[*($0, $cor0.C)])
- LogicalValues(tuples=[[{ 2 }]])
+ LogicalFilter(condition=[=($2, $1)])
+ LogicalProject(C=[$1], EXPR$1=[*($0, $1)], C2=[$1])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalValues(tuples=[[{ 2 }]])
+ LogicalAggregate(group=[{0}])
+ LogicalValues(tuples=[[{ 4 }]])
]]>
</Resource>
</TestCase>
@@ -4543,11 +4556,14 @@ LogicalProject(C=[$0], D=[$1])
<Resource name="plan">
<![CDATA[
LogicalProject(C=[$0], D=[$1], C0=[$2])
- LogicalCorrelate(correlation=[$cor0], joinType=[left], requiredColumns=[{0}])
+ LogicalJoin(condition=[=($0, $3)], joinType=[left])
LogicalValues(tuples=[[{ 4 }]])
- LogicalFilter(condition=[=($cor0.C, $1)])
- LogicalProject(C=[$cor0.C], EXPR$1=[*($0, $cor0.C)])
- LogicalValues(tuples=[[{ 2 }]])
+ LogicalFilter(condition=[=($2, $1)])
+ LogicalProject(C=[$1], EXPR$1=[*($0, $1)], C2=[$1])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalValues(tuples=[[{ 2 }]])
+ LogicalAggregate(group=[{0}])
+ LogicalValues(tuples=[[{ 4 }]])
]]>
</Resource>
</TestCase>
@@ -4560,14 +4576,16 @@ LogicalProject(C=[$0], D=[$1], C0=[$2])
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], D=[$1], D0=[$2], C0=[$3])
- LogicalProject(C=[$0], D=[$1], C0=[$4], EXPR$1=[$5])
- LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{2, 3}])
- LogicalProject(C=[$0], D=[$1], $f2=[+($0, $1)], C0=[$0])
- LogicalValues(tuples=[[{ 4, 5 }]])
- LogicalFilter(condition=[=($cor0.$f2, $1)])
- LogicalProject(C=[$cor0.C], EXPR$1=[*($0, $cor0.C)])
- LogicalValues(tuples=[[{ 2 }]])
+LogicalProject(C=[$0], D=[$1], D0=[$4], C0=[$5])
+ LogicalJoin(condition=[AND(=($0, $6), =($2, $5))], joinType=[left])
+ LogicalProject(C=[$0], D=[$1], $f2=[+($0, $1)], C0=[$0])
+ LogicalValues(tuples=[[{ 4, 5 }]])
+ LogicalProject(C=[$1], EXPR$1=[*($0, $1)], C2=[$1])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalValues(tuples=[[{ 2 }]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(C=[$0])
+ LogicalValues(tuples=[[{ 4, 5 }]])
]]>
</Resource>
</TestCase>
@@ -4583,17 +4601,19 @@ as r(n) on c=n]]>
</Resource>
<Resource name="plan">
<![CDATA[
-LogicalProject(C=[$0], N=[$1])
- LogicalProject(C=[$0], EXPR$0=[$3])
- LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1, 2}])
- LogicalProject(C=[$0], C0=[$0], $f2=[+($0, 1)])
- LogicalValues(tuples=[[{ 4 }]])
- LogicalFilter(condition=[=($cor0.C, $0)])
- LogicalProject(EXPR$0=[+($0, $1)])
- LogicalJoin(condition=[=($0, $1)], joinType=[inner])
- LogicalValues(tuples=[[{ 2 }]])
- LogicalProject(EXPR$0=[$cor0.$f2])
- LogicalValues(tuples=[[{ 3 }]])
+LogicalProject(C=[$0], N=[$3])
+ LogicalJoin(condition=[AND(=($0, $3), =($2, $4))], joinType=[left])
+ LogicalProject(C=[$0], C0=[$0], $f2=[+($0, 1)])
+ LogicalValues(tuples=[[{ 4 }]])
+ LogicalProject(EXPR$0=[+($0, $1)], $f2=[$2])
+ LogicalJoin(condition=[=($0, $1)], joinType=[inner])
+ LogicalValues(tuples=[[{ 2 }]])
+ LogicalProject(EXPR$0=[$1], $f2=[$1])
+ LogicalJoin(condition=[true], joinType=[inner])
+ LogicalValues(tuples=[[{ 3 }]])
+ LogicalAggregate(group=[{0}])
+ LogicalProject($f2=[+($0, 1)])
+ LogicalValues(tuples=[[{ 4 }]])
]]>
</Resource>
</TestCase>
@@ -5235,11 +5255,16 @@ LogicalProject(D2=[$0], D3=[$1])
LogicalJoin(condition=[=($0, $1)], joinType=[left])
LogicalProject(D1=[+($0, 1)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
- LogicalProject(D4=[$0], D6=[$2], $f0=[true])
- LogicalFilter(condition=[=($1, $0)])
- LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0,
6)])
+ LogicalProject(D4=[$0], D6=[$2], $f2=[$3])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(D1=[+($0, 1)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
+ LogicalProject(D4=[$0], D6=[$2], $f0=[true])
+ LogicalFilter(condition=[=($1, $0)])
+ LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)],
D6=[+($0, 6)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -5265,11 +5290,16 @@ LogicalProject(D2=[$0], D3=[$1])
LogicalJoin(condition=[=($0, $1)], joinType=[left])
LogicalProject(D1=[+($0, 1)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
- LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
- LogicalProject(D4=[$0], D6=[$2], $f0=[true])
- LogicalFilter(condition=[=($1, $0)])
- LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)], D6=[+($0,
6)])
+ LogicalProject(D4=[$0], D6=[$2], $f2=[$3])
+ LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ LogicalAggregate(group=[{0}])
+ LogicalProject(D1=[+($0, 1)])
LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
+ LogicalAggregate(group=[{0, 1}], agg#0=[MIN($2)])
+ LogicalProject(D4=[$0], D6=[$2], $f0=[true])
+ LogicalFilter(condition=[=($1, $0)])
+ LogicalProject(D4=[+($0, 4)], D5=[+($0, 5)],
D6=[+($0, 6)])
+ LogicalTableScan(table=[[CATALOG, SALES, DEPT]])
]]>
</Resource>
</TestCase>
@@ -9697,15 +9727,13 @@ select * from t where exists (
<Resource name="plan">
<![CDATA[
LogicalProject(A=[$0], B=[$1])
- LogicalProject(EXPR$0=[$0], EXPR$1=[$1], $f0=[$2])
- LogicalFilter(condition=[IS NOT NULL($2)])
- LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])
- LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
- LogicalValues(tuples=[[{ 1, 2 }]])
- LogicalAggregate(group=[{}], agg#0=[MIN($0)])
- LogicalProject($f0=[true])
- LogicalFilter(condition=[=($7, $cor0.A)])
- LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1], DEPTNO=[CAST($2):INTEGER],
$f1=[CAST($3):BOOLEAN])
+ LogicalJoin(condition=[=($0, $2)], joinType=[inner])
+ LogicalProject(EXPR$0=[$0], EXPR$1=[$1])
+ LogicalValues(tuples=[[{ 1, 2 }]])
+ LogicalAggregate(group=[{0}], agg#0=[MIN($1)])
+ LogicalProject(DEPTNO=[$7], $f0=[true])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
diff --git a/core/src/test/resources/sql/blank.iq
b/core/src/test/resources/sql/blank.iq
index c8151c14f5..882d430412 100644
--- a/core/src/test/resources/sql/blank.iq
+++ b/core/src/test/resources/sql/blank.iq
@@ -92,13 +92,15 @@ select i, j from table1 where table1.j NOT IN (select i
from table2 where table1
EnumerableCalc(expr#0..7=[{inputs}], expr#8=[0], expr#9=[=($t3, $t8)],
expr#10=[IS NULL($t1)], expr#11=[IS NOT NULL($t7)], expr#12=[<($t4, $t3)],
expr#13=[OR($t10, $t11, $t12)], expr#14=[IS NOT TRUE($t13)], expr#15=[OR($t9,
$t14)], proj#0..1=[{exprs}], $condition=[$t15])
EnumerableMergeJoin(condition=[AND(=($0, $6), =($1, $5))], joinType=[left])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
- EnumerableMergeJoin(condition=[=($0, $2)], joinType=[left])
- EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableTableScan(table=[[BLANK, TABLE1]])
- EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
- EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)],
proj#0..1=[{exprs}], $condition=[$t2])
- EnumerableTableScan(table=[[BLANK, TABLE2]])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $2)],
joinType=[left])
+ EnumerableTableScan(table=[[BLANK, TABLE1]])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NOT NULL($t2)],
expr#5=[0], expr#6=[CASE($t4, $t2, $t5)], expr#7=[IS NOT NULL($t3)],
expr#8=[CASE($t7, $t3, $t5)], J=[$t0], c=[$t6], ck=[$t8])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ EnumerableAggregate(group=[{0}])
+ EnumerableTableScan(table=[[BLANK, TABLE1]])
+ EnumerableAggregate(group=[{1}], c=[COUNT()], ck=[COUNT($0)])
+ EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)],
proj#0..1=[{exprs}], $condition=[$t2])
+ EnumerableTableScan(table=[[BLANK, TABLE2]])
EnumerableSort(sort0=[$1], sort1=[$0], dir0=[ASC], dir1=[ASC])
EnumerableAggregate(group=[{0, 1}], i=[LITERAL_AGG(true)])
EnumerableCalc(expr#0..1=[{inputs}], expr#2=[IS NOT NULL($t1)],
expr#3=[IS NOT NULL($t0)], expr#4=[AND($t2, $t3)], proj#0..1=[{exprs}],
$condition=[$t4])
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 9feb458e53..cddd77cf4c 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -535,16 +535,24 @@ EnumerableCalc(expr#0..9=[{inputs}], expr#10=[0],
expr#11=[=($t5, $t10)], expr#1
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableAggregate(group=[{3}], c=[COUNT()], ck=[COUNT($1)])
- EnumerableNestedLoopJoin(condition=[>($2, $3)], joinType=[inner])
- EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2],
DEPTNO=[$t7])
- EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..3=[{inputs}], expr#4=[IS NOT NULL($t2)],
expr#5=[0], expr#6=[CASE($t4, $t2, $t5)], expr#7=[IS NOT NULL($t3)],
expr#8=[CASE($t7, $t3, $t5)], DEPTNO0=[$t0], c=[$t6], ck=[$t8])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
EnumerableAggregate(group=[{1}])
EnumerableHashJoin(condition=[=($1, $2)], joinType=[semi])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0],
DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableAggregate(group=[{3}], c=[COUNT()], ck=[COUNT($1)])
+ EnumerableNestedLoopJoin(condition=[>($2, $3)],
joinType=[inner])
+ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], JOB=[$t2],
DEPTNO=[$t7])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableAggregate(group=[{1}])
+ EnumerableHashJoin(condition=[=($1, $2)], joinType=[semi])
+ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0],
DEPTNO=[$t7])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..2=[{inputs}], DEPTNO=[$t0])
+ EnumerableTableScan(table=[[scott, DEPT]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t0)],
proj#0..2=[{exprs}], $condition=[$t3])
EnumerableAggregate(group=[{1, 3}], i=[LITERAL_AGG(true)])
@@ -895,23 +903,19 @@ where sal + 100 not in (
# [CALCITE-356] AssertionError while translating query with WITH and
correlated sub-query
with t (a, b) as (select * from (values (1, 2)))
select * from t where exists (select 1 from "scott".emp where deptno = t.a);
-EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
- EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
- EnumerableValues(tuples=[[{ 1, 2 }]])
- EnumerableAggregate(group=[{0}])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[true],
expr#9=[CAST($t7):INTEGER], expr#10=[$cor0], expr#11=[$t10.A], expr#12=[=($t9,
$t11)], i=[$t8], $condition=[$t12])
- EnumerableTableScan(table=[[scott, EMP]])
+EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[2], A=[$t1], B=[$t2])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER],
expr#9=[1], expr#10=[=($t9, $t8)], DEPTNO0=[$t8], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
# Similar query, identical plan
with t as (select * from (values (1, 2)) as t(a, b))
select * from t where exists (select 1 from "scott".emp where deptno = t.a);
-EnumerableCalc(expr#0..2=[{inputs}], proj#0..1=[{exprs}])
- EnumerableCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])
- EnumerableValues(tuples=[[{ 1, 2 }]])
- EnumerableAggregate(group=[{0}])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[true],
expr#9=[CAST($t7):INTEGER], expr#10=[$cor0], expr#11=[$t10.A], expr#12=[=($t9,
$t11)], i=[$t8], $condition=[$t12])
- EnumerableTableScan(table=[[scott, EMP]])
+EnumerableCalc(expr#0=[{inputs}], expr#1=[1], expr#2=[2], A=[$t1], B=[$t2])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[CAST($t7):INTEGER],
expr#9=[1], expr#10=[=($t9, $t8)], DEPTNO0=[$t8], $condition=[$t10])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
# Uncorrelated
@@ -2624,13 +2628,16 @@ EnumerableAggregate(group=[{}], C=[COUNT()])
EnumerableMergeJoin(condition=[AND(=($3, $5), =($4, $6))], joinType=[left])
EnumerableSort(sort0=[$3], sort1=[$4], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..6=[{inputs}], expr#7=[100], expr#8=[+($t2,
$t7)], expr#9=[CAST($t1):VARCHAR(14)], SAL=[$t2], c=[$t4], ck=[$t5], $f5=[$t8],
ENAME0=[$t9])
- EnumerableMergeJoin(condition=[=($3, $6)], joinType=[left])
- EnumerableSort(sort0=[$3], dir0=[ASC])
- EnumerableCalc(expr#0..7=[{inputs}],
expr#8=[CAST($t1):VARCHAR(14)], proj#0..1=[{exprs}], SAL=[$t5], ENAME0=[$t8])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableSort(sort0=[$2], dir0=[ASC])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1:BIGINT],
expr#4=[IS NOT NULL($t1)], c=[$t3], ck=[$t3], DNAME=[$t1], $condition=[$t4])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($3, $6)],
joinType=[left])
+ EnumerableCalc(expr#0..7=[{inputs}],
expr#8=[CAST($t1):VARCHAR(14)], proj#0..1=[{exprs}], SAL=[$t5], ENAME0=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[IS NOT NULL($t2)],
expr#4=[0], expr#5=[CASE($t3, $t2, $t4)], c=[$t5], ck=[$t5], DNAME=[$t0])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0,
$1)], joinType=[left])
+ EnumerableAggregate(group=[{0}])
+ EnumerableCalc(expr#0..7=[{inputs}],
expr#8=[CAST($t1):VARCHAR(14)], ENAME0=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[1:BIGINT],
expr#4=[IS NOT NULL($t1)], DNAME=[$t1], $f1=[$t3], $condition=[$t4])
+ EnumerableTableScan(table=[[scott, DEPT]])
EnumerableSort(sort0=[$0], sort1=[$1], dir0=[ASC], dir1=[ASC])
EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):DECIMAL(13, 2)
NOT NULL], expr#4=[true], expr#5=[IS NOT NULL($t1)], DEPTNO=[$t3], DNAME=[$t1],
i=[$t4], $condition=[$t5])
EnumerableTableScan(table=[[scott, DEPT]])
@@ -2640,12 +2647,16 @@ EnumerableAggregate(group=[{}], C=[COUNT()])
select empno from "scott".emp as e
where e.empno > ANY(
select 2 from "scott".dept e2 where e2.deptno = e.deptno) ;
-EnumerableCalc(expr#0..1=[{inputs}], EMPNO=[$t0])
- EnumerableHashJoin(condition=[AND(=($1, $6), >($0, $2))], joinType=[semi])
+EnumerableCalc(expr#0..6=[{inputs}], EMPNO=[$t5])
+ EnumerableNestedLoopJoin(condition=[AND(IS NOT DISTINCT FROM($6, $4),
OR(AND(>($5, $0), IS NOT TRUE(OR(IS NULL($3), =($1, 0)))), AND(>($5, $0), IS
NOT TRUE(OR(IS NULL($3), =($1, 0))), IS NOT TRUE(>($5, $0)), <=($1, $2))))],
joinType=[inner])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NOT NULL($t3)],
expr#6=[0], expr#7=[CASE($t5, $t3, $t6)], m=[$t2], c=[$t7], d=[$t7],
trueLiteral=[$t4], DEPTNO=[$t0])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ EnumerableAggregate(group=[{7}])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], expr#4=[1:BIGINT],
expr#5=[true], DEPTNO=[$t0], EXPR$0=[$t3], $f2=[$t4], $f3=[$t5])
+ EnumerableTableScan(table=[[scott, DEPT]])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[2], expr#4=[1:BIGINT],
expr#5=[true], m=[$t3], c=[$t4], d=[$t4], trueLiteral=[$t5], DEPTNO=[$t0])
- EnumerableTableScan(table=[[scott, DEPT]])
!plan
EMPNO
-------
@@ -2677,10 +2688,13 @@ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[>($t1,
$t2)], expr#8=[IS TRUE($t7)]
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0], DEPTNO=[$t7])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$4], dir0=[ASC])
- EnumerableCalc(expr#0..3=[{inputs}], m=[$t1], c=[$t2], d=[$t2],
trueLiteral=[$t3], DEPTNO0=[$t0])
- EnumerableAggregate(group=[{0}], m=[MIN($1)], c=[COUNT()],
trueLiteral=[LITERAL_AGG(true)])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):SMALLINT NOT
NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[IS NOT NULL($t3)],
expr#6=[0], expr#7=[CASE($t5, $t3, $t6)], m=[$t2], c=[$t7], d=[$t7],
trueLiteral=[$t4], DEPTNO0=[$t0])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableAggregate(group=[{0}], m=[MIN($1)], c=[COUNT()],
trueLiteral=[LITERAL_AGG(true)])
+ EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):SMALLINT
NOT NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
EMPNO | EXPR$1
-------+--------
@@ -2746,11 +2760,15 @@ select empno
from "scott".emp emp1
where empno <> some (select emp2.empno from "scott".emp emp2 where emp2.empno
= emp1.empno);
EnumerableCalc(expr#0..6=[{inputs}], expr#7=[<>($t2, $t1)], expr#8=[1],
expr#9=[<=($t3, $t8)], expr#10=[<>($t0, $t4)], expr#11=[IS NULL($t5)],
expr#12=[0], expr#13=[=($t1, $t12)], expr#14=[OR($t11, $t13)], expr#15=[IS NOT
TRUE($t14)], expr#16=[AND($t7, $t9, $t10, $t15)], expr#17=[=($t3, $t8)],
expr#18=[IS NOT NULL($t3)], expr#19=[AND($t7, $t18)], expr#20=[IS NOT
TRUE($t19)], expr#21=[AND($t17, $t10, $t15, $t20)], expr#22=[AND($t7, $t9)],
expr#23=[IS NOT TRUE($t22)], expr#24=[IS NOT TRUE( [...]
- EnumerableMergeJoin(condition=[=($0, $6)], joinType=[left])
+ EnumerableHashJoin(condition=[=($0, $6)], joinType=[left])
EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
EnumerableTableScan(table=[[scott, EMP]])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1:BIGINT], expr#9=[true],
c=[$t8], d=[$t8], dd=[$t8], m=[$t0], trueLiteral=[$t9], EMPNO1=[$t0])
- EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
expr#7=[0], expr#8=[CASE($t6, $t2, $t7)], expr#9=[IS NOT NULL($t3)],
expr#10=[CASE($t9, $t3, $t7)], c=[$t8], d=[$t8], dd=[$t10], m=[$t4],
trueLiteral=[$t5], EMPNO1=[$t0])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[1:BIGINT], expr#9=[true],
EMPNO1=[$t0], $f1=[$t8], $f2=[$t8], EMPNO=[$t0], $f4=[$t9])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
+-------+
| EMPNO |
@@ -2790,16 +2808,18 @@ select *
from "scott".emp emp1
where empno <> some (select comm from "scott".emp where deptno = emp1.deptno);
EnumerableCalc(expr#0..13=[{inputs}], expr#14=[<>($t10, $t9)], expr#15=[1],
expr#16=[<=($t11, $t15)], expr#17=[AND($t14, $t16)], expr#18=[=($t11, $t15)],
expr#19=[OR($t17, $t18)], expr#20=[<>($t0, $t12)], expr#21=[IS NULL($t13)],
expr#22=[0], expr#23=[=($t9, $t22)], expr#24=[OR($t21, $t23)], expr#25=[IS NOT
TRUE($t24)], expr#26=[AND($t19, $t20, $t25)], expr#27=[IS NOT TRUE($t19)],
expr#28=[AND($t25, $t27)], expr#29=[OR($t26, $t28)], proj#0..7=[{exprs}],
$condition=[$t29])
- EnumerableMergeJoin(condition=[=($7, $8)], joinType=[left])
- EnumerableSort(sort0=[$7], dir0=[ASC])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t1):BIGINT NOT NULL],
expr#7=[CAST($t2):BIGINT NOT NULL], expr#8=[CAST($t5):BOOLEAN NOT NULL],
DEPTNO=[$t0], c=[$t6], d=[$t7], dd=[$t3], m=[$t4], trueLiteral=[$t8])
- EnumerableAggregate(group=[{1}], c=[MIN($2) FILTER $7], d=[MIN($3)
FILTER $7], dd=[COUNT($0) FILTER $6], m=[MIN($4) FILTER $7],
trueLiteral=[MIN(true, $5) FILTER $7])
- EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t6,
$t7)], expr#9=[2], expr#10=[=($t6, $t9)], proj#0..5=[{exprs}], $g_0=[$t8],
$g_2=[$t10])
- EnumerableAggregate(group=[{6, 7}], groups=[[{6, 7}, {7}]],
c=[COUNT()], d=[COUNT($6)], m=[MAX($6)], trueLiteral=[LITERAL_AGG(true)],
$g=[GROUPING($6, $7)])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t7)],
proj#0..7=[{exprs}], $condition=[$t8])
- EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($7, $8)],
joinType=[left])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS NOT NULL($t2)],
expr#8=[0], expr#9=[CASE($t7, $t2, $t8)], expr#10=[IS NOT NULL($t3)],
expr#11=[CASE($t10, $t3, $t8)], expr#12=[IS NOT NULL($t4)], expr#13=[CASE($t12,
$t4, $t8)], DEPTNO=[$t0], c=[$t9], d=[$t11], dd=[$t13], m=[$t5],
trueLiteral=[$t6])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ EnumerableAggregate(group=[{7}])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t1):BIGINT NOT
NULL], expr#7=[CAST($t2):BIGINT NOT NULL], expr#8=[CAST($t5):BOOLEAN NOT NULL],
DEPTNO=[$t0], c=[$t6], d=[$t7], dd=[$t3], m=[$t4], trueLiteral=[$t8])
+ EnumerableAggregate(group=[{1}], c=[MIN($2) FILTER $7], d=[MIN($3)
FILTER $7], dd=[COUNT($0) FILTER $6], m=[MIN($4) FILTER $7],
trueLiteral=[MIN(true, $5) FILTER $7])
+ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t6,
$t7)], expr#9=[2], expr#10=[=($t6, $t9)], proj#0..5=[{exprs}], $g_0=[$t8],
$g_2=[$t10])
+ EnumerableAggregate(group=[{6, 7}], groups=[[{6, 7}, {7}]],
c=[COUNT()], d=[COUNT($6)], m=[MAX($6)], trueLiteral=[LITERAL_AGG(true)],
$g=[GROUPING($6, $7)])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT
NULL($t7)], proj#0..7=[{exprs}], $condition=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
@@ -2848,12 +2868,16 @@ EnumerableCalc(expr#0..13=[{inputs}], expr#14=[<>($t9,
$t8)], expr#15=[1], expr#
EnumerableMergeJoin(condition=[=($0, $13)], joinType=[left])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$5], dir0=[ASC])
- EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):BIGINT NOT NULL],
expr#6=[CAST($t3):INTEGER NOT NULL], expr#7=[CAST($t4):BOOLEAN NOT NULL],
c=[$t5], d=[$t5], dd=[$t2], m=[$t6], trueLiteral=[$t7], DEPTNO0=[$t0])
- EnumerableAggregate(group=[{0}], c=[MIN($2) FILTER $6], dd=[COUNT($1)
FILTER $5], m=[MIN($3) FILTER $6], trueLiteral=[MIN(true, $4) FILTER $6])
- EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5,
$t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7],
$g_1=[$t9])
- EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
c=[COUNT()], m=[MAX($1)], trueLiteral=[LITERAL_AGG(true)], $g=[GROUPING($0,
$1)])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):SMALLINT
NOT NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
expr#7=[0], expr#8=[CASE($t6, $t2, $t7)], expr#9=[IS NOT NULL($t3)],
expr#10=[CASE($t9, $t3, $t7)], c=[$t8], d=[$t8], dd=[$t10], m=[$t4],
trueLiteral=[$t5], DEPTNO0=[$t0])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):BIGINT NOT
NULL], expr#6=[CAST($t3):INTEGER NOT NULL], expr#7=[CAST($t4):BOOLEAN NOT
NULL], DEPTNO0=[$t0], c=[$t5], dd=[$t2], m=[$t6], trueLiteral=[$t7])
+ EnumerableAggregate(group=[{0}], c=[MIN($2) FILTER $6],
dd=[COUNT($1) FILTER $5], m=[MIN($3) FILTER $6], trueLiteral=[MIN(true, $4)
FILTER $6])
+ EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5,
$t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7],
$g_1=[$t9])
+ EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
c=[COUNT()], m=[MAX($1)], trueLiteral=[LITERAL_AGG(true)], $g=[GROUPING($0,
$1)])
+ EnumerableCalc(expr#0..2=[{inputs}],
expr#3=[CAST($t0):SMALLINT NOT NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
+-------+-------+-----+-----+----------+-----+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
@@ -2896,12 +2920,16 @@ EnumerableCalc(expr#0..13=[{inputs}], expr#14=[<>($t9,
$t8)], expr#15=[1], expr#
EnumerableMergeJoin(condition=[=($0, $13)], joinType=[left])
EnumerableTableScan(table=[[scott, EMP]])
EnumerableSort(sort0=[$5], dir0=[ASC])
- EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):BIGINT NOT NULL],
expr#6=[CAST($t3):INTEGER NOT NULL], expr#7=[CAST($t4):BOOLEAN NOT NULL],
c=[$t5], d=[$t5], dd=[$t2], m=[$t6], trueLiteral=[$t7], DEPTNO0=[$t0])
- EnumerableAggregate(group=[{0}], c=[MIN($2) FILTER $6], dd=[COUNT($1)
FILTER $5], m=[MIN($3) FILTER $6], trueLiteral=[MIN(true, $4) FILTER $6])
- EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5,
$t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7],
$g_1=[$t9])
- EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
c=[COUNT()], m=[MAX($1)], trueLiteral=[LITERAL_AGG(true)], $g=[GROUPING($0,
$1)])
- EnumerableCalc(expr#0..2=[{inputs}], expr#3=[CAST($t0):SMALLINT
NOT NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4])
- EnumerableTableScan(table=[[scott, DEPT]])
+ EnumerableCalc(expr#0..5=[{inputs}], expr#6=[IS NOT NULL($t2)],
expr#7=[0], expr#8=[CASE($t6, $t2, $t7)], expr#9=[IS NOT NULL($t3)],
expr#10=[CASE($t9, $t3, $t7)], c=[$t8], d=[$t8], dd=[$t10], m=[$t4],
trueLiteral=[$t5], DEPTNO0=[$t0])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ EnumerableCalc(expr#0..7=[{inputs}], EMPNO=[$t0])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..4=[{inputs}], expr#5=[CAST($t1):BIGINT NOT
NULL], expr#6=[CAST($t3):INTEGER NOT NULL], expr#7=[CAST($t4):BOOLEAN NOT
NULL], DEPTNO0=[$t0], c=[$t5], dd=[$t2], m=[$t6], trueLiteral=[$t7])
+ EnumerableAggregate(group=[{0}], c=[MIN($2) FILTER $6],
dd=[COUNT($1) FILTER $5], m=[MIN($3) FILTER $6], trueLiteral=[MIN(true, $4)
FILTER $6])
+ EnumerableCalc(expr#0..5=[{inputs}], expr#6=[0], expr#7=[=($t5,
$t6)], expr#8=[1], expr#9=[=($t5, $t8)], proj#0..4=[{exprs}], $g_0=[$t7],
$g_1=[$t9])
+ EnumerableAggregate(group=[{0, 1}], groups=[[{0, 1}, {0}]],
c=[COUNT()], m=[MAX($1)], trueLiteral=[LITERAL_AGG(true)], $g=[GROUPING($0,
$1)])
+ EnumerableCalc(expr#0..2=[{inputs}],
expr#3=[CAST($t0):SMALLINT NOT NULL], expr#4=[2], DEPTNO0=[$t3], EXPR$0=[$t4])
+ EnumerableTableScan(table=[[scott, DEPT]])
!plan
+-------+-------+-----+-----+----------+-----+------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
@@ -2941,16 +2969,18 @@ select *
from "scott".emp emp1
where emp1.comm <> some (select comm from "scott".emp emp2 where emp2.sal =
emp1.sal);
EnumerableCalc(expr#0..13=[{inputs}], expr#14=[<>($t10, $t9)], expr#15=[1],
expr#16=[<=($t11, $t15)], expr#17=[AND($t14, $t16)], expr#18=[=($t11, $t15)],
expr#19=[OR($t17, $t18)], expr#20=[<>($t6, $t12)], expr#21=[IS NULL($t13)],
expr#22=[IS NULL($t6)], expr#23=[0], expr#24=[=($t9, $t23)], expr#25=[OR($t21,
$t22, $t24)], expr#26=[IS NOT TRUE($t25)], expr#27=[AND($t19, $t20, $t26)],
expr#28=[IS NOT TRUE($t19)], expr#29=[AND($t26, $t28)], expr#30=[OR($t27,
$t29)], proj#0..7=[{exprs}], $con [...]
- EnumerableMergeJoin(condition=[=($5, $8)], joinType=[left])
- EnumerableSort(sort0=[$5], dir0=[ASC])
- EnumerableTableScan(table=[[scott, EMP]])
- EnumerableSort(sort0=[$0], dir0=[ASC])
- EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t1):BIGINT NOT NULL],
expr#7=[CAST($t2):BIGINT NOT NULL], expr#8=[CAST($t5):BOOLEAN NOT NULL],
SAL=[$t0], c=[$t6], d=[$t7], dd=[$t3], m=[$t4], trueLiteral=[$t8])
- EnumerableAggregate(group=[{0}], c=[MIN($2) FILTER $7], d=[MIN($3)
FILTER $7], dd=[COUNT($1) FILTER $6], m=[MIN($4) FILTER $7],
trueLiteral=[MIN(true, $5) FILTER $7])
- EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t6,
$t7)], expr#9=[1], expr#10=[=($t6, $t9)], proj#0..5=[{exprs}], $g_0=[$t8],
$g_1=[$t10])
- EnumerableAggregate(group=[{5, 6}], groups=[[{5, 6}, {5}]],
c=[COUNT()], d=[COUNT($6)], m=[MAX($6)], trueLiteral=[LITERAL_AGG(true)],
$g=[GROUPING($5, $6)])
- EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT NULL($t5)],
proj#0..7=[{exprs}], $condition=[$t8])
- EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($5, $8)],
joinType=[left])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[IS NOT NULL($t2)],
expr#8=[0], expr#9=[CASE($t7, $t2, $t8)], expr#10=[IS NOT NULL($t3)],
expr#11=[CASE($t10, $t3, $t8)], expr#12=[IS NOT NULL($t4)], expr#13=[CASE($t12,
$t4, $t8)], SAL=[$t0], c=[$t9], d=[$t11], dd=[$t13], m=[$t5], trueLiteral=[$t6])
+ EnumerableNestedLoopJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
+ EnumerableAggregate(group=[{5}])
+ EnumerableTableScan(table=[[scott, EMP]])
+ EnumerableCalc(expr#0..5=[{inputs}], expr#6=[CAST($t1):BIGINT NOT
NULL], expr#7=[CAST($t2):BIGINT NOT NULL], expr#8=[CAST($t5):BOOLEAN NOT NULL],
SAL=[$t0], c=[$t6], d=[$t7], dd=[$t3], m=[$t4], trueLiteral=[$t8])
+ EnumerableAggregate(group=[{0}], c=[MIN($2) FILTER $7], d=[MIN($3)
FILTER $7], dd=[COUNT($1) FILTER $6], m=[MIN($4) FILTER $7],
trueLiteral=[MIN(true, $5) FILTER $7])
+ EnumerableCalc(expr#0..6=[{inputs}], expr#7=[0], expr#8=[=($t6,
$t7)], expr#9=[1], expr#10=[=($t6, $t9)], proj#0..5=[{exprs}], $g_0=[$t8],
$g_1=[$t10])
+ EnumerableAggregate(group=[{5, 6}], groups=[[{5, 6}, {5}]],
c=[COUNT()], d=[COUNT($6)], m=[MAX($6)], trueLiteral=[LITERAL_AGG(true)],
$g=[GROUPING($5, $6)])
+ EnumerableCalc(expr#0..7=[{inputs}], expr#8=[IS NOT
NULL($t5)], proj#0..7=[{exprs}], $condition=[$t8])
+ EnumerableTableScan(table=[[scott, EMP]])
!plan
+-------+--------+----------+------+------------+---------+---------+--------+
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
@@ -4275,21 +4305,53 @@ FROM EMP i1 ORDER BY COMM;
| 300.00 | true |
| 500.00 | true |
| 1400.00 | true |
-| | |
-| | |
-| | |
-| | |
-| | |
-| | |
-| | |
-| | |
-| | |
-| | |
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+| | false |
+---------+--------+
(14 rows)
!ok
+SELECT deptno
+FROM dept d
+WHERE 0 IN (
+ SELECT COUNT(*)
+ FROM emp e
+ WHERE d.deptno = e.deptno
+);
++--------+
+| DEPTNO |
++--------+
+| 40 |
++--------+
+(1 row)
+
+!ok
+
+SELECT deptno
+FROM dept d
+WHERE 'Regular' IN (
+ SELECT CASE WHEN SUM(sal) > 10 then 'VIP' else 'Regular' END expr
+ FROM emp e
+ WHERE d.deptno = e.deptno
+);
++--------+
+| DEPTNO |
++--------+
+| 40 |
++--------+
+(1 row)
+
+!ok
+
# Test case for [CALCITE-5789]
select deptno from dept d1 where exists (
select 1 from dept d2 where d2.deptno = d1.deptno and exists (
