This is an automated email from the ASF dual-hosted git repository.
mbudiu 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 3eac1f7d56 [CALCITE-7272] Subqueries cannot be decorrelated if have
set op
3eac1f7d56 is described below
commit 3eac1f7d567217e7fce13857712c7e0b6a1ab1e3
Author: iwanttobepowerful <[email protected]>
AuthorDate: Mon Dec 15 17:32:41 2025 +0800
[CALCITE-7272] Subqueries cannot be decorrelated if have set op
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 216 +++-
.../calcite/sql2rel/RelDecorrelatorTest.java | 415 +++++++
.../org/apache/calcite/test/RelOptRulesTest.xml | 10 +-
core/src/test/resources/sql/sub-query.iq | 1185 ++++++++++++++++++++
4 files changed, 1779 insertions(+), 47 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 db647da89c..c5986208d2 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -44,6 +44,7 @@
import org.apache.calcite.rel.core.JoinRelType;
import org.apache.calcite.rel.core.Project;
import org.apache.calcite.rel.core.RelFactories;
+import org.apache.calcite.rel.core.SetOp;
import org.apache.calcite.rel.core.Sort;
import org.apache.calcite.rel.logical.LogicalAggregate;
import org.apache.calcite.rel.logical.LogicalCorrelate;
@@ -817,8 +818,7 @@ protected RexNode removeCorrelationExpr(
}
}
- if (rel.getGroupType() == Aggregate.Group.SIMPLE
- && rel.getGroupSet().isEmpty()
+ if ((rel.hasEmptyGroup() || rel.getGroupSet().isEmpty())
&& !frame.corDefOutputs.isEmpty()
&& !parentPropagatesNullValues) {
newRel = rewriteScalarAggregate(rel, newRel, outputMap, corDefOutputs);
@@ -930,71 +930,63 @@ 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();
+ final CorelMap localCorelMap = new CorelMapBuilder().build(oldRel);
+ final List<CorRef> corVarList = new
ArrayList<>(localCorelMap.mapRefRelToCorRef.values());
+ Collections.sort(corVarList);
- 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();
+ final NavigableMap<CorDef, Integer> valueGenCorDefOutputs = new
TreeMap<>();
+ final RelNode valueGen =
+ requireNonNull(createValueGenerator(corVarList, 0,
valueGenCorDefOutputs));
+ final int valueGenFieldCount = valueGen.getRowType().getFieldCount();
- // 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`.
+ // Build join conditions
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));
+ final CorDef corDef = corDefOutput.getKey();
+ final int leftPos = requireNonNull(valueGenCorDefOutputs.get(corDef));
+ final int rightPos = corDefOutput.getValue();
+ final RelDataType leftType =
valueGen.getRowType().getFieldList().get(leftPos).getType();
+ final RelDataType rightType =
newRel.getRowType().getFieldList().get(rightPos).getType();
+ final RexNode leftRef = new RexInputRef(leftPos, leftType);
+ final RexNode rightRef = new RexInputRef(valueGenFieldCount + rightPos,
rightType);
+ conditions.add(relBuilder.isNotDistinctFrom(leftRef, rightRef));
+ newProjectMap.put(valueGenFieldCount + rightPos, leftRef);
+ }
+ final RexNode joinCond =
RexUtil.composeConjunction(relBuilder.getRexBuilder(), conditions);
// 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();
+ final RelNode join = relBuilder.push(valueGen).push(newRel)
+ .join(JoinRelType.LEFT, joinCond).build();
+ RelDataType joinRowType = join.getRowType();
+ RexBuilder rexBuilder = relBuilder.getRexBuilder();
+ // 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`.
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)));
+ final RexInputRef ref = RexInputRef.of(index + valueGenFieldCount,
joinRowType);
+ ImmutableList<RexNode> exprs =
+ ImmutableList.of(relBuilder.isNotNull(ref), ref,
relBuilder.literal(0));
+ RexNode specificCountValue =
rexBuilder.makeCall(SqlStdOperatorTable.CASE, exprs);
newProjectMap.put(ref.getIndex(), specificCountValue);
}
}
+ // Build [07] LogicalProject(DEPTNO=[$0], EXPR$0=[CASE(IS NOT NULL($2),
$2, 0)])
+ // to handle COUNT function by converting nulls to zero.
final List<RexNode> newProjects = new ArrayList<>();
- for (int index : ImmutableBitSet.range(groupKeySize,
join.getRowType().getFieldCount())) {
+ for (int index : ImmutableBitSet.range(valueGenFieldCount,
joinRowType.getFieldCount())) {
if (newProjectMap.containsKey(index)) {
newProjects.add(requireNonNull(newProjectMap.get(index)));
} else {
- newProjects.add(RexInputRef.of(index, join.getRowType()));
+ newProjects.add(RexInputRef.of(index, joinRowType));
}
}
- // 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();
@@ -1184,6 +1176,144 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
return null;
}
+ /**
+ * Given the SQL:
+ * SELECT ename,
+ * (SELECT sum(c)
+ * FROM
+ * (SELECT deptno AS c
+ * FROM dept
+ * WHERE dept.deptno = emp.deptno
+ * UNION ALL
+ * SELECT 2 AS c
+ * FROM bonus
+ * WHERE bonus.job = emp.job) AS union_subquery
+ * ) AS correlated_sum
+ * FROM emp;
+ *
+ * <p>from:
+ * LogicalUnion(all=[true])
+ * LogicalProject(C=[CAST($0):INTEGER NOT NULL])
+ * LogicalFilter(condition=[=($0, $cor0.DEPTNO)])
+ * LogicalTableScan(table=[[scott, DEPT]])
+ * LogicalProject(C=[2])
+ * LogicalFilter(condition=[=($1, $cor0.JOB)])
+ * LogicalTableScan(table=[[scott, BONUS]])
+ *
+ * <p>to:
+ * LogicalUnion(all=[true])
+ * LogicalProject(JOB=[$0], DEPTNO=[$1], C=[$2])
+ * LogicalJoin(condition=[IS NOT DISTINCT FROM($1, $3)],
joinType=[inner])
+ * LogicalAggregate(group=[{0, 1}])
+ * LogicalProject(JOB=[$2], DEPTNO=[$7])
+ * LogicalTableScan(table=[[scott, EMP]])
+ * LogicalProject(C=[CAST($0):INTEGER NOT NULL], DEPTNO=[$0])
+ * LogicalTableScan(table=[[scott, DEPT]])
+ * LogicalProject(JOB=[$0], DEPTNO=[$1], C=[$2])
+ * LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $3)],
joinType=[inner])
+ * LogicalAggregate(group=[{0, 1}])
+ * LogicalProject(JOB=[$2], DEPTNO=[$7])
+ * LogicalTableScan(table=[[scott, EMP]])
+ * LogicalProject(C=[2], JOB=[$1])
+ * LogicalFilter(condition=[IS NOT NULL($1)])
+ * LogicalTableScan(table=[[scott, BONUS]])
+ */
+ public @Nullable Frame decorrelateRel(SetOp rel, boolean isCorVarDefined,
+ boolean parentPropagatesNullValues) {
+ if (!isCorVarDefined) {
+ return decorrelateRel((RelNode) rel, false, parentPropagatesNullValues);
+ }
+
+ final CorelMap localCorelMap = new CorelMapBuilder().build(rel);
+ final List<CorRef> corVarList = new
ArrayList<>(localCorelMap.mapRefRelToCorRef.values());
+ Collections.sort(corVarList);
+
+ final NavigableMap<CorDef, Integer> valueGenCorDefOutputs = new
TreeMap<>();
+ final RelNode valueGen =
+ requireNonNull(createValueGenerator(corVarList, 0,
valueGenCorDefOutputs));
+ final int valueGenFieldCount = valueGen.getRowType().getFieldCount();
+ // Original SetOp payload width.
+ final int payloadFieldCount = rel.getRowType().getFieldCount();
+ final List<RelNode> newInputs = new ArrayList<>();
+ final Map<Integer, Integer> setOpOldToNewOutputs = new HashMap<>();
+ final NavigableMap<CorDef, Integer> setOpCorDefOutputs = new TreeMap<>();
+
+ for (int i = 0; i < rel.getInputs().size(); i++) {
+ RelNode oldInput = rel.getInput(i);
+ Frame frame = getInvoke(oldInput, true, rel, parentPropagatesNullValues);
+ if (frame == null) {
+ // If input has not been rewritten, do not rewrite this rel.
+ return null;
+ }
+
+ // Build join conditions: for each CorDef of this branch that belongs
+ // to the current outFrameCorrId, equate valueGen(col) with branch(col).
+ final List<RexNode> conditions = new ArrayList<>();
+ for (Map.Entry<CorDef, Integer> e : frame.corDefOutputs.entrySet()) {
+ final CorDef corDef = e.getKey();
+ final int leftPos = requireNonNull(valueGenCorDefOutputs.get(corDef));
+ final int rightPos = e.getValue();
+ final RelDataType leftType =
valueGen.getRowType().getFieldList().get(leftPos).getType();
+ final RelDataType rightType =
frame.r.getRowType().getFieldList().get(rightPos).getType();
+ final RexNode leftRef = new RexInputRef(leftPos, leftType);
+ final RexNode rightRef = new RexInputRef(valueGenFieldCount +
rightPos, rightType);
+ conditions.add(relBuilder.isNotDistinctFrom(leftRef, rightRef));
+ }
+ final RexNode joinCondition =
+ RexUtil.composeConjunction(relBuilder.getRexBuilder(), conditions);
+ RelNode join = relBuilder.push(valueGen).push(frame.r)
+ .join(JoinRelType.INNER, joinCondition).build();
+
+ final List<RelDataTypeField> joinFields =
join.getRowType().getFieldList();
+
+ // Build the final projection for this branch:
+ // all correlated columns (from valueGen), original payload columns
(from branch)
+ final PairList<RexNode, String> projects = PairList.of();
+ final Map<Integer, Integer> childOldToNew = new HashMap<>();
+ final NavigableMap<CorDef, Integer> childCorDefOutputs = new TreeMap<>();
+
+ // a) Correlated columns, in the order of valueGenCorDefOutputs.
+ int newPos = 0;
+ for (Map.Entry<CorDef, Integer> e : valueGenCorDefOutputs.entrySet()) {
+ final int srcIndex = e.getValue();
+ RexInputRef inputRef = RexInputRef.of(srcIndex, join.getRowType());
+ String name = joinFields.get(srcIndex).getName();
+
+ projects.add(inputRef, name);
+ childCorDefOutputs.put(e.getKey(), newPos);
+ newPos++;
+ }
+
+ // b) Original SetOp payload columns.
+ for (int oldIndex = 0; oldIndex < payloadFieldCount; oldIndex++) {
+ final Integer srcInFrame =
requireNonNull(frame.oldToNewOutputs.get(oldIndex));
+ final int srcInJoin = valueGenFieldCount + srcInFrame;
+ RexInputRef inputRef = RexInputRef.of(srcInJoin, join.getRowType());
+ String name = joinFields.get(srcInJoin).getName();
+
+ projects.add(inputRef, name);
+ childOldToNew.put(oldIndex, newPos);
+ newPos++;
+ }
+
+ final RelNode newInput = relBuilder.push(join)
+ .projectNamed(projects.leftList(), projects.rightList(), true)
+ .build();
+ newInputs.add(newInput);
+
+ register(oldInput, newInput, childOldToNew, childCorDefOutputs);
+
+ // Use the first branch as prototype for the SetOp's frame mappings.
+ if (i == 0) {
+ setOpOldToNewOutputs.putAll(childOldToNew);
+ setOpCorDefOutputs.putAll(childCorDefOutputs);
+ }
+ }
+
+ final SetOp newSetOp = rel.copy(rel.getTraitSet(), newInputs, rel.all);
+ return register(rel, newSetOp, setOpOldToNewOutputs, setOpCorDefOutputs);
+ }
+
public @Nullable Frame decorrelateRel(LogicalProject rel, boolean
isCorVarDefined,
boolean parentPropagatesNullValues) {
return decorrelateRel((Project) rel, isCorVarDefined,
parentPropagatesNullValues);
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 b92606be5b..80a3dda646 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -425,6 +425,421 @@ public static Frameworks.ConfigBuilder config() {
assertThat(after, hasTree(planAfter));
}
+ @Test void testCorrelationInSetOp0() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = "SELECT ename,\n"
+ + " (SELECT sum(c)\n"
+ + " FROM\n"
+ + " (SELECT deptno AS c\n"
+ + " FROM dept\n"
+ + " WHERE dept.deptno = emp.deptno\n"
+ + " UNION ALL\n"
+ + " SELECT 2 AS c\n"
+ + " FROM bonus) AS union_subquery\n"
+ + " ) AS correlated_sum\n"
+ + "FROM emp\n"
+ + "ORDER BY ename";
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(
+ ImmutableList.of(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalSort(sort0=[$0], dir0=[ASC])\n"
+ + " LogicalProject(ENAME=[$1], CORRELATED_SUM=[$8])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{7}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])\n"
+ + " LogicalUnion(all=[true])\n"
+ + " LogicalProject(C=[CAST($0):INTEGER NOT NULL])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.DEPTNO)])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(C=[2])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ // Verify plan
+ final String planAfter = ""
+ + "LogicalSort(sort0=[$0], dir0=[ASC])\n"
+ + " LogicalProject(ENAME=[$1], CORRELATED_SUM=[$9])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($7, $8)],
joinType=[left])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[SUM($1)])\n"
+ + " LogicalProject(DEPTNO=[$0], C=[$1])\n"
+ + " LogicalUnion(all=[true])\n"
+ + " LogicalProject(DEPTNO=[$0], C=[$1])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $2)],
joinType=[inner])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(DEPTNO=[$7])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(C=[CAST($0):INTEGER NOT NULL],
DEPTNO=[$0])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(DEPTNO=[$0], C=[$1])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(DEPTNO=[$7])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(C=[2])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7272";>[CALCITE-7272]
+ * Subqueries cannot be decorrelated if have set op</a>. */
+ @Test void testCorrelationInSetOp1() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = "SELECT ename,\n"
+ + " (SELECT sum(c)\n"
+ + " FROM\n"
+ + " (SELECT deptno AS c\n"
+ + " FROM dept\n"
+ + " WHERE dept.deptno = emp.deptno\n"
+ + " UNION ALL\n"
+ + " SELECT 2 AS c\n"
+ + " FROM bonus\n"
+ + " WHERE bonus.job = emp.job) AS union_subquery\n"
+ + " ) AS correlated_sum\n"
+ + "FROM emp\n"
+ + "ORDER BY ename";
+
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(
+ ImmutableList.of(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalSort(sort0=[$0], dir0=[ASC])\n"
+ + " LogicalProject(ENAME=[$1], CORRELATED_SUM=[$8])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{2, 7}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])\n"
+ + " LogicalUnion(all=[true])\n"
+ + " LogicalProject(C=[CAST($0):INTEGER NOT NULL])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.DEPTNO)])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(C=[2])\n"
+ + " LogicalFilter(condition=[=($1, $cor0.JOB)])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ // Verify plan
+ final String planAfter = ""
+ + "LogicalSort(sort0=[$0], dir0=[ASC])\n"
+ + " LogicalProject(ENAME=[$1], CORRELATED_SUM=[$10])\n"
+ + " LogicalJoin(condition=[AND(IS NOT DISTINCT FROM($2, $8), IS NOT
DISTINCT FROM($7, $9))], joinType=[left])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{0, 1}], EXPR$0=[SUM($2)])\n"
+ + " LogicalProject(JOB=[$0], DEPTNO=[$1], C=[$2])\n"
+ + " LogicalUnion(all=[true])\n"
+ + " LogicalProject(JOB=[$0], DEPTNO=[$1], C=[$2])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($1, $3)],
joinType=[inner])\n"
+ + " LogicalAggregate(group=[{0, 1}])\n"
+ + " LogicalProject(JOB=[$2], DEPTNO=[$7])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(C=[CAST($0):INTEGER NOT NULL],
DEPTNO=[$0])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(JOB=[$0], DEPTNO=[$1], C=[$2])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $3)],
joinType=[inner])\n"
+ + " LogicalAggregate(group=[{0, 1}])\n"
+ + " LogicalProject(JOB=[$2], DEPTNO=[$7])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(C=[2], JOB=[$1])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($1)])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7272";>[CALCITE-7272]
+ * Subqueries cannot be decorrelated if have set op</a>. */
+ @Test void testCorrelationInSetOp2() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = "SELECT d.dname\n"
+ + "FROM dept d\n"
+ + "WHERE EXISTS (\n"
+ + " SELECT 1\n"
+ + " FROM emp e\n"
+ + " WHERE e.deptno = d.deptno\n"
+ + " AND (\n"
+ + " SELECT SUM(x)\n"
+ + " FROM (\n"
+ + " SELECT COUNT(*) as x\n"
+ + " FROM bonus b\n"
+ + " WHERE b.ename = e.ename\n"
+ + " UNION ALL\n"
+ + " SELECT COUNT(*) as x\n"
+ + " FROM emp e2\n"
+ + " WHERE e2.deptno = d.deptno\n"
+ + " ) t\n"
+ + " ) > 5)";
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(
+ ImmutableList.of(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalProject(DNAME=[$1])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(i=[true])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n"
+ + " LogicalFilter(condition=[AND(=($7, $cor0.DEPTNO), >($8,
5))])\n"
+ + " LogicalCorrelate(correlation=[$cor1],
joinType=[left], requiredColumns=[{1}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])\n"
+ + " LogicalUnion(all=[true])\n"
+ + " LogicalAggregate(group=[{}], X=[COUNT()])\n"
+ + " LogicalFilter(condition=[=($0,
$cor1.ENAME)])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n"
+ + " LogicalAggregate(group=[{}], X=[COUNT()])\n"
+ + " LogicalFilter(condition=[=($7,
$cor0.DEPTNO)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ // Verify plan
+ final String planAfter = ""
+ + "LogicalProject(DNAME=[$1])\n"
+ + " LogicalJoin(condition=[=($0, $3)], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(DEPTNO0=[$0], $f1=[true])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(DEPTNO0=[$8])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7],
DEPTNO0=[CAST($8):TINYINT], ENAME0=[$9], EXPR$0=[CAST($10):BIGINT])\n"
+ + " LogicalJoin(condition=[AND(IS NOT DISTINCT FROM($1,
$9), =($7, $8))], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[>($2, 5)])\n"
+ + " LogicalAggregate(group=[{0, 1}],
EXPR$0=[SUM($2)])\n"
+ + " LogicalProject(DEPTNO=[$0], ENAME=[$1], X=[$2])\n"
+ + " LogicalUnion(all=[true])\n"
+ + " LogicalProject(DEPTNO=[$0], ENAME=[$1],
X=[$3])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT
FROM($1, $2)], joinType=[inner])\n"
+ + " LogicalJoin(condition=[true],
joinType=[inner])\n"
+ + " LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalTableScan(table=[[scott,
DEPT]])\n"
+ + " LogicalProject(ENAME=[$1])\n"
+ + " LogicalTableScan(table=[[scott,
EMP]])\n"
+ + " LogicalProject(ENAME=[$0], X=[CASE(IS NOT
NULL($2), $2, 0)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT
FROM($0, $1)], joinType=[left])\n"
+ + " LogicalProject(ENAME=[$1])\n"
+ + " LogicalTableScan(table=[[scott,
EMP]])\n"
+ + " LogicalAggregate(group=[{0}],
X=[COUNT()])\n"
+ + " LogicalProject(ENAME=[$0])\n"
+ + " LogicalFilter(condition=[IS NOT
NULL($0)])\n"
+ + " LogicalTableScan(table=[[scott,
BONUS]])\n"
+ + " LogicalProject(DEPTNO=[$0], ENAME=[$1],
X=[$3])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT
FROM($0, $2)], joinType=[inner])\n"
+ + " LogicalJoin(condition=[true],
joinType=[inner])\n"
+ + " LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalTableScan(table=[[scott,
DEPT]])\n"
+ + " LogicalProject(ENAME=[$1])\n"
+ + " LogicalTableScan(table=[[scott,
EMP]])\n"
+ + " LogicalProject(DEPTNO=[$0], X=[CASE(IS
NOT NULL($2), $2, 0)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT
FROM($0, $1)], joinType=[left])\n"
+ + " LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalTableScan(table=[[scott,
DEPT]])\n"
+ + " LogicalAggregate(group=[{0}],
X=[COUNT()])\n"
+ + " LogicalProject(DEPTNO=[$7])\n"
+ + " LogicalFilter(condition=[IS NOT
NULL($7)])\n"
+ + " LogicalTableScan(table=[[scott,
EMP]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7272";>[CALCITE-7272]
+ * Subqueries cannot be decorrelated if have set op</a>. */
+ @Test void testCorrelationInSetOp3() {
+ final FrameworkConfig frameworkConfig = config().build();
+ final RelBuilder builder = RelBuilder.create(frameworkConfig);
+ final RelOptCluster cluster = builder.getCluster();
+ final Planner planner = Frameworks.getPlanner(frameworkConfig);
+ final String sql = "SELECT d.dname\n"
+ + "FROM dept d\n"
+ + "WHERE EXISTS (\n"
+ + " SELECT 1\n"
+ + " FROM emp e\n"
+ + " WHERE (\n"
+ + " SELECT SUM(x)\n"
+ + " FROM (\n"
+ + " SELECT COUNT(*) as x\n"
+ + " FROM bonus b\n"
+ + " WHERE b.ename = e.ename\n"
+ + " UNION ALL\n"
+ + " SELECT COUNT(*) as x\n"
+ + " FROM emp e2\n"
+ + " WHERE e2.deptno = d.deptno\n"
+ + " ) t\n"
+ + " ) > 5)";
+ final RelNode originalRel;
+ try {
+ final SqlNode parse = planner.parse(sql);
+ final SqlNode validate = planner.validate(parse);
+ originalRel = planner.rel(validate).rel;
+ } catch (Exception e) {
+ throw TestUtil.rethrow(e);
+ }
+
+ final HepProgram hepProgram = HepProgram.builder()
+ .addRuleCollection(
+ ImmutableList.of(
+ // SubQuery program rules
+ CoreRules.FILTER_SUB_QUERY_TO_CORRELATE,
+ CoreRules.PROJECT_SUB_QUERY_TO_CORRELATE,
+ CoreRules.JOIN_SUB_QUERY_TO_CORRELATE))
+ .build();
+ final Program program =
+ Programs.of(hepProgram, true,
+ requireNonNull(cluster.getMetadataProvider()));
+ final RelNode before =
+ program.run(cluster.getPlanner(), originalRel, cluster.traitSet(),
+ Collections.emptyList(), Collections.emptyList());
+ final String planBefore = ""
+ + "LogicalProject(DNAME=[$1])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])\n"
+ + " LogicalCorrelate(correlation=[$cor1], joinType=[inner],
requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(i=[true])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n"
+ + " LogicalFilter(condition=[>($8, 5)])\n"
+ + " LogicalCorrelate(correlation=[$cor0],
joinType=[left], requiredColumns=[{1}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[SUM($0)])\n"
+ + " LogicalUnion(all=[true])\n"
+ + " LogicalAggregate(group=[{}], X=[COUNT()])\n"
+ + " LogicalFilter(condition=[=($0,
$cor0.ENAME)])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\n"
+ + " LogicalAggregate(group=[{}], X=[COUNT()])\n"
+ + " LogicalFilter(condition=[=($7,
$cor1.DEPTNO)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(before, hasTree(planBefore));
+
+ // Decorrelate without any rules, just "purely" decorrelation algorithm on
RelDecorrelator
+ final RelNode after =
+ RelDecorrelator.decorrelateQuery(before, builder,
RuleSets.ofList(Collections.emptyList()),
+ RuleSets.ofList(Collections.emptyList()));
+ // Verify plan
+ final String planAfter = ""
+ + "LogicalProject(DNAME=[$1])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $3)],
joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(DEPTNO0=[$0], $f1=[true])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(DEPTNO0=[$9])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], ENAME0=[$8],
DEPTNO0=[CAST($9):TINYINT], EXPR$0=[CAST($10):BIGINT])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT FROM($1, $8)],
joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalFilter(condition=[>($2, 5)])\n"
+ + " LogicalAggregate(group=[{0, 1}],
EXPR$0=[SUM($2)])\n"
+ + " LogicalProject(ENAME=[$0], DEPTNO=[$1], X=[$2])\n"
+ + " LogicalUnion(all=[true])\n"
+ + " LogicalProject(ENAME=[$0], DEPTNO=[$1],
X=[$3])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT
FROM($0, $2)], joinType=[inner])\n"
+ + " LogicalJoin(condition=[true],
joinType=[inner])\n"
+ + " LogicalProject(ENAME=[$1])\n"
+ + " LogicalTableScan(table=[[scott,
EMP]])\n"
+ + " LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalTableScan(table=[[scott,
DEPT]])\n"
+ + " LogicalProject(ENAME=[$0], X=[CASE(IS NOT
NULL($2), $2, 0)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT
FROM($0, $1)], joinType=[left])\n"
+ + " LogicalProject(ENAME=[$1])\n"
+ + " LogicalTableScan(table=[[scott,
EMP]])\n"
+ + " LogicalAggregate(group=[{0}],
X=[COUNT()])\n"
+ + " LogicalProject(ENAME=[$0])\n"
+ + " LogicalFilter(condition=[IS NOT
NULL($0)])\n"
+ + " LogicalTableScan(table=[[scott,
BONUS]])\n"
+ + " LogicalProject(ENAME=[$0], DEPTNO=[$1],
X=[$3])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT
FROM($1, $2)], joinType=[inner])\n"
+ + " LogicalJoin(condition=[true],
joinType=[inner])\n"
+ + " LogicalProject(ENAME=[$1])\n"
+ + " LogicalTableScan(table=[[scott,
EMP]])\n"
+ + " LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalTableScan(table=[[scott,
DEPT]])\n"
+ + " LogicalProject(DEPTNO=[$0], X=[CASE(IS
NOT NULL($2), $2, 0)])\n"
+ + " LogicalJoin(condition=[IS NOT DISTINCT
FROM($0, $1)], joinType=[left])\n"
+ + " LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalTableScan(table=[[scott,
DEPT]])\n"
+ + " LogicalAggregate(group=[{0}],
X=[COUNT()])\n"
+ + " LogicalProject(DEPTNO=[$7])\n"
+ + " LogicalFilter(condition=[IS NOT
NULL($7)])\n"
+ + " LogicalTableScan(table=[[scott,
EMP]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
/**
* Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-6468";>[CALCITE-6468]
RelDecorrelator
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 e651fcd74e..2eafe44bd8 100644
--- a/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/RelOptRulesTest.xml
@@ -17372,8 +17372,9 @@ LogicalProject(EXPR$0=[CAST(OR(AND(IS TRUE(>($0, $9)),
IS NOT TRUE(OR(IS NULL($1
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
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}])
+ LogicalProject(JOB=[$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]])
@@ -21626,8 +21627,9 @@ LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
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}])
+ LogicalProject(JOB=[$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]])
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 9f320c7ef0..47f8f6497d 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -5619,4 +5619,1189 @@ where c+d=a*c);
(2 rows)
!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH
+ customers(id, name, city) AS (
+ VALUES
+ (1, 'Alice', 'New York'),
+ (2, 'Bob', 'San Francisco'),
+ (3, 'Charlie', 'Los Angeles')
+ ),
+ orders(id, customer_id, total_amount) AS (
+ VALUES
+ (100, 1, 500.00),
+ (101, 2, 150.00),
+ (102, 1, 300.00)
+ ),
+ lineitems(id, order_id, product_name, price) AS (
+ VALUES
+ (1, 100, 'Laptop', 1000.00),
+ (2, 100, 'Mouse', 20.00),
+ (3, 100, 'Keyboard', 50.00),
+ (4, 101, 'Monitor', 150.00)
+ ),
+ payments(id, customer_id, amount) AS (
+ VALUES
+ (1, 1, 200.00),
+ (2, 1, 200.00),
+ (3, 1, 100.00),
+ (4, 2, 150.00)
+ )
+SELECT c.id, c.name
+FROM customers c
+WHERE EXISTS (
+ SELECT 1
+ FROM orders o
+ WHERE o.customer_id = c.id
+ AND (
+ SELECT SUM(cnt)
+ FROM (
+ SELECT COUNT(*) AS cnt
+ FROM lineitems li
+ WHERE li.order_id = o.id
+ UNION ALL
+ SELECT COUNT(*) AS cnt
+ FROM payments p
+ WHERE p.customer_id = c.id
+ ) AS union_sub
+ ) > 5
+);
++----+---------+
+| ID | NAME |
++----+---------+
+| 1 | Alice |
++----+---------+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+SELECT d.dname
+FROM dept d
+WHERE EXISTS (
+ SELECT 1
+ FROM emp e
+ WHERE e.deptno = d.deptno
+ AND (
+ SELECT SUM(x)
+ FROM (
+ SELECT COUNT(*) as x
+ FROM bonus b
+ WHERE b.ename = e.ename
+ UNION ALL
+ SELECT COUNT(*) as x
+ FROM emp e2
+ WHERE e2.deptno = d.deptno
+ ) t
+ ) > 5
+);
++-------+
+| DNAME |
++-------+
+| SALES |
++-------+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+SELECT d.dname
+FROM dept d
+WHERE EXISTS (
+ SELECT 1
+ FROM emp e
+ WHERE (
+ SELECT SUM(x)
+ FROM (
+ SELECT COUNT(*) as x
+ FROM bonus b
+ WHERE b.ename = e.ename
+ UNION ALL
+ SELECT COUNT(*) as x
+ FROM emp e2
+ WHERE e2.deptno = d.deptno
+ ) t
+ ) > 5
+);
++-------+
+| DNAME |
++-------+
+| SALES |
++-------+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+SELECT ename,
+ (SELECT sum(c)
+ FROM
+ (SELECT deptno AS c
+ FROM dept
+ WHERE dept.deptno = emp.deptno
+ UNION ALL
+ SELECT 2 AS c
+ FROM bonus
+ WHERE bonus.job = emp.job) AS union_subquery
+ ) AS correlated_sum
+FROM emp
+ORDER BY ename;
++--------+----------------+
+| ENAME | CORRELATED_SUM |
++--------+----------------+
+| ADAMS | 20 |
+| ALLEN | 30 |
+| BLAKE | 30 |
+| CLARK | 10 |
+| FORD | 20 |
+| JAMES | 30 |
+| JONES | 20 |
+| KING | 10 |
+| MARTIN | 30 |
+| MILLER | 10 |
+| SCOTT | 20 |
+| SMITH | 20 |
+| TURNER | 30 |
+| WARD | 30 |
++--------+----------------+
+(14 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+SELECT ename,
+ (SELECT sum(c)
+ FROM
+ (SELECT deptno AS c
+ FROM dept
+ WHERE dept.deptno = emp.deptno
+ UNION ALL
+ SELECT 2 AS c
+ FROM bonus) AS union_subquery
+ ) AS correlated_sum
+FROM emp
+ORDER BY ename;
++--------+----------------+
+| ENAME | CORRELATED_SUM |
++--------+----------------+
+| ADAMS | 20 |
+| ALLEN | 30 |
+| BLAKE | 30 |
+| CLARK | 10 |
+| FORD | 20 |
+| JAMES | 30 |
+| JONES | 20 |
+| KING | 10 |
+| MARTIN | 30 |
+| MILLER | 10 |
+| SCOTT | 20 |
+| SMITH | 20 |
+| TURNER | 30 |
+| WARD | 30 |
++--------+----------------+
+(14 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+SELECT *,
+ (SELECT COUNT(*)
+ FROM (
+ SELECT * FROM emp WHERE emp.deptno = dept.deptno
+ UNION ALL
+ SELECT * FROM emp) AS sub
+ GROUP BY deptno) AS num_dept_groups
+FROM dept;
+more than one value in agg SINGLE_VALUE
+!error
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 1 | 1 |
++-----+-----+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | 7 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ UNION ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 5 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ UNION ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ UNION ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 2 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ UNION ALL
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 1 |
+| 2 | -2 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 1 | 1 |
++-----+-----+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ UNION DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | 7 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ UNION DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 5 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ UNION DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 8 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ UNION DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 2 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ UNION DISTINCT
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 1 |
+| 2 | -1 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
++-----+-----+
+(0 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ INTERSECT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 0 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ INTERSECT ALL
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 0 |
+| 2 | -1 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
++-----+-----+
+(0 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ INTERSECT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ INTERSECT DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 0 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ INTERSECT DISTINCT
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 0 |
+| 2 | -1 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 1 | 1 |
++-----+-----+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ EXCEPT ALL
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT ALL
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 1 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ EXCEPT ALL
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT * FROM t0 WHERE t0a <
+(SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 1 | 1 |
++-----+-----+
+(1 row)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2a = t0a) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(c) FROM
+ (SELECT t1c as c
+ FROM t1
+ WHERE t1a > t0a
+ EXCEPT DISTINCT
+ SELECT t2c as c
+ FROM t2
+ WHERE t2b <= t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 3 |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT count(t1c) FROM
+ (SELECT t1c
+ FROM t1
+ WHERE t1a = t0a
+ EXCEPT DISTINCT
+ SELECT t2c
+ FROM t2
+ WHERE t2b = t0b) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | 1 |
+| 2 | 0 |
++-----+--------+
+(2 rows)
+
+!ok
+
+# [CALCITE-7272] Subqueries cannot be decorrelated if have set op
+WITH t0(t0a, t0b) AS (VALUES (1, 1), (2, 0)),
+ t1(t1a, t1b, t1c) AS (VALUES (1, 1, 3)),
+ t2(t2a, t2b, t2c) AS (VALUES (1, 1, 5), (2, 2, 7))
+SELECT t0a, (SELECT sum(d) FROM
+ (SELECT t1a - t0a as d
+ FROM t1
+ EXCEPT DISTINCT
+ SELECT t2a - t0a as d
+ FROM t2) as tmp
+)
+FROM t0;
++-----+--------+
+| T0A | EXPR$1 |
++-----+--------+
+| 1 | |
+| 2 | |
++-----+--------+
+(2 rows)
+
+!ok
+
# End sub-query.iq
