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 f451b2af9c [CALCITE-7379] LHS correlated variables are shadowed by
nullable RHS outputs in LEFT JOIN
f451b2af9c is described below
commit f451b2af9ce5062abbd565d227f484b13003cd4f
Author: iwanttobepowerful <[email protected]>
AuthorDate: Fri Jan 16 11:46:45 2026 +0800
[CALCITE-7379] LHS correlated variables are shadowed by nullable RHS
outputs in LEFT JOIN
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 286 ++++++++--
.../calcite/sql2rel/RelDecorrelatorTest.java | 166 ++++++
core/src/test/resources/sql/sub-query.iq | 588 +++++++++++++++++++++
3 files changed, 990 insertions(+), 50 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 80f1f95335..9813ef27d1 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -959,18 +959,19 @@ private RelNode rewriteScalarAggregate(Aggregate oldRel,
// Build join conditions
final Map<Integer, RexNode> newProjectMap = new HashMap<>();
- final List<RexNode> conditions = new ArrayList<>();
for (Map.Entry<CorDef, Integer> corDefOutput : corDefOutputs.entrySet()) {
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 List<RexNode> conditions =
+ buildCorDefJoinConditions(valueGenCorDefOutputs, corDefOutputs,
+ valueGen, newRel, relBuilder);
+
final RexNode joinCond =
RexUtil.composeConjunction(relBuilder.getRexBuilder(), conditions);
// Build [08] LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[left])
@@ -1277,21 +1278,13 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
// 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 List<RexNode> conditions =
+ buildCorDefJoinConditions(valueGenCorDefOutputs, frame.corDefOutputs,
+ valueGen, frame.r, relBuilder);
final RexNode joinCondition =
RexUtil.composeConjunction(relBuilder.getRexBuilder(), conditions);
RelNode join = relBuilder.push(valueGen).push(frame.r)
- .join(JoinRelType.INNER, joinCondition).build();
+ .join(JoinRelType.INNER, joinCondition).build();
final List<RelDataTypeField> joinFields =
join.getRowType().getFieldList();
@@ -1935,10 +1928,16 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
return decorrelateRel((RelNode) rel, isCorVarDefined,
parentPropagatesNullValues);
}
//
- // Rewrite logic:
+ // For other join types (INNER, LEFT, RIGHT, FULL):
//
- // 1. rewrite join condition.
- // 2. map output positions and produce corVars if any.
+ // 1. Decorrelates the left and right inputs recursively.
+ // 2. Ensures that required correlated variables are present in the
inputs, adding
+ // value generators if necessary (e.g., for the nullable side of an
outer join).
+ // 3. Constructs a new join condition that includes the original condition
and
+ // equality conditions for the correlated variables.
+ // 4. For {@link JoinRelType#FULL}, adds a projection on top of the join
to coalesce
+ // correlated variables that might be null on one side due to the join
nature.
+ // 5. Updates the output mapping to reflect the new join structure.
//
final RelNode oldLeft = rel.getInput(0);
@@ -1952,53 +1951,178 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
return null;
}
+ // 1. Collect all CorRefs involved
+ final CorelMap localCorelMap = new CorelMapBuilder().build(rel);
+ final List<CorRef> corVarList = new
ArrayList<>(localCorelMap.mapRefRelToCorRef.values());
+ Collections.sort(corVarList);
+
+ // 2. Ensure CorVars are present in inputs (adding ValueGenerators if
needed)
Frame newLeftFrame = leftFrame;
- boolean joinConditionContainsFieldAccess =
RexUtil.containsFieldAccess(rel.getCondition());
- if (joinConditionContainsFieldAccess && isCorVarDefined) {
- final CorelMap localCorelMap = new CorelMapBuilder().build(rel);
- final List<CorRef> corVarList = new
ArrayList<>(localCorelMap.mapRefRelToCorRef.values());
- Collections.sort(corVarList);
+ Frame newRightFrame = rightFrame;
+ final NavigableMap<CorDef, Integer> leftCorDefOutputs = new TreeMap<>();
+ final NavigableMap<CorDef, Integer> rightCorDefOutputs = new TreeMap<>();
+ boolean generatesNullsOnRight = rel.getJoinType().generatesNullsOnRight();
+ boolean generatesNullsOnLeft = rel.getJoinType().generatesNullsOnLeft();
+
+ if (isCorVarDefined) {
+ // ensure CorVars are present in left input
+ if (generatesNullsOnRight ||
RexUtil.containsFieldAccess(rel.getCondition())) {
+ newLeftFrame = supplyMissingCorVars(oldLeft, leftFrame, corVarList,
leftCorDefOutputs);
+ rightCorDefOutputs.putAll(rightFrame.corDefOutputs);
+ }
+ // ensure CorVars are present in right input
+ if (generatesNullsOnLeft) {
+ newRightFrame = supplyMissingCorVars(oldRight, rightFrame, corVarList,
rightCorDefOutputs);
+ leftCorDefOutputs.putAll(leftFrame.corDefOutputs);
+ }
+ } else {
+ leftCorDefOutputs.putAll(leftFrame.corDefOutputs);
+ rightCorDefOutputs.putAll(rightFrame.corDefOutputs);
+ }
+
+ // 3. Build Join Conditions
+ final List<RexNode> joinConditions = new ArrayList<>();
+ RexNode originalCond = decorrelateExpr(castNonNull(currentRel), map, cm,
rel.getCondition());
+ if (!originalCond.isAlwaysTrue()) {
+ joinConditions.add(originalCond);
+ }
- final NavigableMap<CorDef, Integer> corDefOutputs = new TreeMap<>();
- newLeftFrame = createFrameWithValueGenerator(oldLeft, leftFrame,
corVarList, corDefOutputs);
+ if (generatesNullsOnLeft || generatesNullsOnRight) {
+ List<RexNode> conds =
+ buildCorDefJoinConditions(leftCorDefOutputs, rightCorDefOutputs,
+ newLeftFrame.r, newRightFrame.r, relBuilder);
+ joinConditions.addAll(conds);
}
+ RexNode finalCondition = joinConditions.isEmpty()
+ ? relBuilder.literal(true)
+ : RexUtil.composeConjunction(relBuilder.getRexBuilder(),
joinConditions);
+
RelNode newJoin = relBuilder
.push(newLeftFrame.r)
- .push(rightFrame.r)
- .join(rel.getJoinType(),
- decorrelateExpr(castNonNull(currentRel), map, cm,
rel.getCondition()),
- ImmutableSet.of())
+ .push(newRightFrame.r)
+ .join(rel.getJoinType(), finalCondition, ImmutableSet.of())
.build();
- // Create the mapping between the output of the old correlation rel
- // and the new join rel
- Map<Integer, Integer> mapOldToNewOutputs = new HashMap<>();
-
- int oldLeftFieldCount = oldLeft.getRowType().getFieldCount();
+ // 4. Handle Full Join Projections (Coalesce)
+ NavigableMap<CorDef, Integer> corDefOutputs = new
TreeMap<>(newLeftFrame.corDefOutputs);
int newLeftFieldCount = newLeftFrame.r.getRowType().getFieldCount();
+ if (rel.getJoinType() == JoinRelType.FULL && isCorVarDefined) {
+ //
+ // SELECT
+ // d.dname,
+ // (
+ // SELECT COUNT(sub.empno)
+ // FROM (
+ // SELECT * FROM emp e2 WHERE e2.deptno = d.deptno
+ // ) sub
+ // FULL JOIN emp e
+ // ON sub.mgr = e.mgr
+ // ) as matched_subordinate_count
+ // FROM dept d
+ // order by d.dname;
+ //
+ // LogicalJoin(condition=[=($3, $11)], joinType=[full])
+ // LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], ...)
+ // LogicalFilter(condition=[=($7, $cor0.DEPTNO)])
+ // LogicalTableScan(table=[[scott, EMP]])
+ // LogicalTableScan(table=[[scott, EMP]])
+ //
+ // convert to:
+ //
+ // LogicalProject(_cor_$cor0_0=[COALESCE($8, $17)], EMPNO=[$0])
+ // LogicalJoin(condition=[AND(=($3, $12), IS NOT DISTINCT FROM($8,
$17))], joinType=[full])
+ // LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3], ...)
+ // LogicalFilter(condition=[IS NOT NULL($7)])
+ // LogicalTableScan(table=[[scott, EMP]])
+ // LogicalJoin(condition=[true], joinType=[inner])
+ // LogicalTableScan(table=[[scott, EMP]])
+ // LogicalProject(DEPTNO=[$0])
+ // LogicalTableScan(table=[[scott, DEPT]])
+ List<RelDataTypeField> joinFields = newJoin.getRowType().getFieldList();
+
+ // 4.1. Pass through existing fields
+ final PairList<RexNode, String> projects = PairList.of();
+ for (int i = 0; i < joinFields.size(); i++) {
+ RexInputRef.add2(projects, i, joinFields);
+ }
+
+ // 4.2. Build Coalesced CorVars
+ NavigableMap<CorDef, Integer> mergedCorDefOutputs = new
TreeMap<>(corDefOutputs);
+ int projectedIndex = joinFields.size();
+ boolean appended = false;
+
+ for (CorRef corRef : corVarList) {
+ CorDef corDef = corRef.def();
+
+ Integer leftPos = leftCorDefOutputs.get(corDef);
+ Integer rightPos = rightCorDefOutputs.get(corDef);
+
+ // If missing on both sides, nothing to coalesce or project
+ if (leftPos == null && rightPos == null) {
+ continue;
+ }
+
+ // Create references
+ RexNode leftRef = null;
+ if (leftPos != null) {
+ leftRef = new RexInputRef(leftPos,
joinFields.get(leftPos).getType());
+ }
+
+ RexNode rightRef = null;
+ if (rightPos != null) {
+ // Right side indices are offset by the left field count in the join
+ int actualRightIndex = rightPos + newLeftFieldCount;
+ rightRef = new RexInputRef(actualRightIndex,
joinFields.get(actualRightIndex).getType());
+ }
+
+ // Determine the expression
+ RexNode expr;
+ if (leftRef == null) {
+ expr = rightRef;
+ } else if (rightRef == null) {
+ expr = leftRef;
+ } else {
+ // Both exist, create COALESCE
+ expr = relBuilder.call(SqlStdOperatorTable.COALESCE, leftRef,
rightRef);
+ }
+ String name = "_cor_" + corDef.corr.getName() + "_" + corDef.field;
+ projects.add(requireNonNull(expr, "expr"), name);
+ mergedCorDefOutputs.put(corDef, projectedIndex++);
+ appended = true;
+ }
+
+ if (appended) {
+ newJoin = relBuilder.push(newJoin)
+ .projectNamed(projects.leftList(), projects.rightList(), true)
+ .build();
+ corDefOutputs.clear();
+ corDefOutputs.putAll(mergedCorDefOutputs);
+ }
+ } else {
+ // Standard output mapping for non-Full Join (or Full Join without
CorVars)
+ // Right input positions are shifted.
+ for (Map.Entry<CorDef, Integer> entry :
newRightFrame.corDefOutputs.entrySet()) {
+ final int shifted = entry.getValue() + newLeftFieldCount;
+ if (rel.getJoinType().generatesNullsOnRight()) {
+ corDefOutputs.putIfAbsent(entry.getKey(), shifted);
+ } else {
+ corDefOutputs.put(entry.getKey(), shifted);
+ }
+ }
+ }
+
+ // 5. Output Mapping
+ int oldLeftFieldCount = oldLeft.getRowType().getFieldCount();
int oldRightFieldCount = oldRight.getRowType().getFieldCount();
- //noinspection AssertWithSideEffects
- assert rel.getRowType().getFieldCount()
- == oldLeftFieldCount + oldRightFieldCount;
- // Left input positions are not changed.
- mapOldToNewOutputs.putAll(newLeftFrame.oldToNewOutputs);
- // Right input positions are shifted by newLeftFieldCount.
+ Map<Integer, Integer> mapOldToNewOutputs = new
HashMap<>(newLeftFrame.oldToNewOutputs);
for (int i = 0; i < oldRightFieldCount; i++) {
mapOldToNewOutputs.put(i + oldLeftFieldCount,
- requireNonNull(rightFrame.oldToNewOutputs.get(i)) +
newLeftFieldCount);
+ requireNonNull(newRightFrame.oldToNewOutputs.get(i)) +
newLeftFieldCount);
}
- final NavigableMap<CorDef, Integer> corDefOutputs =
- new TreeMap<>(newLeftFrame.corDefOutputs);
- // Right input positions are shifted by newLeftFieldCount.
- for (Map.Entry<CorDef, Integer> entry
- : rightFrame.corDefOutputs.entrySet()) {
- corDefOutputs.put(entry.getKey(),
- entry.getValue() + newLeftFieldCount);
- }
return register(rel, newJoin, mapOldToNewOutputs, corDefOutputs);
}
@@ -3719,6 +3843,68 @@ private static boolean isFieldNotNullRecursive(RelNode
rel, int index) {
}
}
+ /**
+ * Ensures that the correlated variables in {@code allCorDefs} are present
+ * in the output of the frame.
+ * If any are missing, it creates a value generator to produce them and
joins it with the frame.
+ *
+ * @param oldInput The original input RelNode
+ * @param frame The current frame for the input
+ * @param corVarList List of all correlated variables
+ * @param corDefOutputs Map to populate with the output positions of the
correlated variables
+ * @return A new Frame with all required correlated variables, or the
original frame
+ * if all were present
+ */
+ private Frame supplyMissingCorVars(RelNode oldInput, Frame frame,
+ List<CorRef> corVarList, NavigableMap<CorDef, Integer> corDefOutputs) {
+ final ImmutableSortedSet<CorDef> haves = frame.corDefOutputs.keySet();
+ if (hasAll(corVarList, haves)) {
+ corDefOutputs.putAll(frame.corDefOutputs);
+ return frame;
+ }
+
+ final List<CorRef> miss = new ArrayList<>();
+ for (CorRef r : corVarList) {
+ if (!haves.contains(r.def())) {
+ miss.add(r);
+ }
+ }
+
+ return createFrameWithValueGenerator(oldInput, frame, miss, corDefOutputs);
+ }
+
+ /**
+ * Builds join conditions to equate correlated variables that are present in
both left
+ * and right inputs.
+ *
+ * @param leftCorDefOutputs Map of CorDefs to output positions in the left
input
+ * @param rightCorDefOutputs Map of CorDefs to output positions in the right
input
+ * @param leftRel The left input RelNode
+ * @param rightRel The right input RelNode
+ * @param relBuilder RelBuilder for creating expressions
+ * @return A list of join conditions (IS NOT DISTINCT FROM) for matching
correlated variables
+ */
+ private List<RexNode> buildCorDefJoinConditions(
+ NavigableMap<CorDef, Integer> leftCorDefOutputs,
+ NavigableMap<CorDef, Integer> rightCorDefOutputs,
+ RelNode leftRel, RelNode rightRel, RelBuilder relBuilder) {
+ List<RexNode> joinConditions = new ArrayList<>();
+ int leftFieldCount = leftRel.getRowType().getFieldCount();
+ for (Map.Entry<CorDef, Integer> leftEntry : leftCorDefOutputs.entrySet()) {
+ CorDef corDef = leftEntry.getKey();
+ if (rightCorDefOutputs.containsKey(corDef)) {
+ int leftPos = leftEntry.getValue();
+ int rightPos = rightCorDefOutputs.get(corDef);
+ final RelDataType leftType =
leftRel.getRowType().getFieldList().get(leftPos).getType();
+ final RelDataType rightType =
rightRel.getRowType().getFieldList().get(rightPos).getType();
+ final RexNode leftRef = new RexInputRef(leftPos, leftType);
+ final RexNode rightRef = new RexInputRef(leftFieldCount + rightPos,
rightType);
+ joinConditions.add(relBuilder.isNotDistinctFrom(leftRef, rightRef));
+ }
+ }
+ return joinConditions;
+ }
+
// -------------------------------------------------------------------------
// Getter/Setter
// -------------------------------------------------------------------------
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 45ef07cc45..6d1d2a974b 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -1272,4 +1272,170 @@ public static Frameworks.ConfigBuilder config() {
+ " LogicalTableScan(table=[[scott, BONUS]])\n";
assertThat(after, hasTree(planAfter));
}
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7379";>[CALCITE-7379]
+ * LHS correlated variables are shadowed by nullable RHS outputs in LEFT
JOIN</a>. */
+ @Test void testDecorrelateLeftJoinCorVarShadowing() {
+ 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 = ""
+ + "WITH\n"
+ + " t1(a, b, c) AS (VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4)),\n"
+ + " t2(a, b, c) AS (VALUES (1, 1, 1), (3, 3, 3), (4, 4, 4)),\n"
+ + " t3(a, b, c) AS (VALUES (1, 1, 1), (2, 2, 2), (4, 4, 4))\n"
+ + "SELECT * FROM t1 WHERE EXISTS (\n"
+ + "SELECT * FROM t2\n"
+ + "LEFT JOIN\n"
+ + "(SELECT * FROM t3 WHERE t3.a = t1.a) foo\n"
+ + "ON t2.a = foo.a)";
+ 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(A=[$0], B=[$1], C=[$2])\n"
+ + " LogicalProject(EXPR$0=[$0], EXPR$1=[$1], EXPR$2=[$2])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[inner],
requiredColumns=[{0}])\n"
+ + " LogicalValues(tuples=[[{ 2, 2, 2 }, { 3, 3, 3 }, { 4, 4, 4
}]])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(i=[true])\n"
+ + " LogicalJoin(condition=[=($0, $3)], joinType=[left])\n"
+ + " LogicalValues(tuples=[[{ 1, 1, 1 }, { 3, 3, 3 }, { 4,
4, 4 }]])\n"
+ + " LogicalProject(A=[$0], B=[$1], C=[$2])\n"
+ + " LogicalFilter(condition=[=($0, $cor0.A)])\n"
+ + " LogicalValues(tuples=[[{ 1, 1, 1 }, { 2, 2, 2 }, {
4, 4, 4 }]])\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()));
+ // The plan before fix:
+ //
+ // LogicalProject(A=[$0], B=[$1], C=[$2])
+ // LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $3)], joinType=[inner])
+ // LogicalValues(tuples=[[{ 2, 2, 2 }, { 3, 3, 3 }, { 4, 4, 4 }]])
+ // LogicalProject(EXPR$00=[$0], $f1=[true])
+ // LogicalAggregate(group=[{0}])
+ // LogicalProject(EXPR$00=[$6])
+ // LogicalJoin(condition=[=($0, $3)], joinType=[left])
+ // LogicalValues(tuples=[[{ 1, 1, 1 }, { 3, 3, 3 }, { 4, 4, 4
}]])
+ // LogicalProject(A=[$0], B=[$1], C=[$2], EXPR$0=[$0])
+ // LogicalValues(tuples=[[{ 1, 1, 1 }, { 2, 2, 2 }, { 4, 4, 4
}]])
+ final String planAfter = ""
+ + "LogicalProject(A=[$0], B=[$1], C=[$2])\n"
+ + " LogicalJoin(condition=[=($0, $3)], joinType=[inner])\n"
+ + " LogicalValues(tuples=[[{ 2, 2, 2 }, { 3, 3, 3 }, { 4, 4, 4
}]])\n"
+ + " LogicalProject(EXPR$00=[$0], $f1=[true])\n"
+ + " LogicalAggregate(group=[{0}])\n"
+ + " LogicalProject(EXPR$00=[$3])\n"
+ + " LogicalJoin(condition=[AND(=($0, $4), IS NOT DISTINCT
FROM($3, $7))], joinType=[left])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalValues(tuples=[[{ 1, 1, 1 }, { 3, 3, 3 }, { 4,
4, 4 }]])\n"
+ + " LogicalProject(EXPR$0=[$0])\n"
+ + " LogicalValues(tuples=[[{ 2, 2, 2 }, { 3, 3, 3 }, {
4, 4, 4 }]])\n"
+ + " LogicalProject(A=[$0], B=[$1], C=[$2], EXPR$0=[$0])\n"
+ + " LogicalValues(tuples=[[{ 1, 1, 1 }, { 2, 2, 2 }, { 4,
4, 4 }]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7379";>[CALCITE-7379]
+ * LHS correlated variables are shadowed by nullable RHS outputs in LEFT
JOIN</a>. */
+ @Test void testDecorrelateFullJoinCorVarShadowing() {
+ 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\n"
+ + " d.dname,\n"
+ + " (SELECT COUNT(sub.empno)\n"
+ + " FROM (\n"
+ + " SELECT * FROM emp e2 WHERE e2.deptno = d.deptno\n"
+ + " ) sub\n"
+ + " FULL JOIN emp e\n"
+ + " ON sub.mgr = e.mgr\n"
+ + " ) as matched_subordinate_count\n"
+ + "FROM dept d";
+ 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], MATCHED_SUBORDINATE_COUNT=[$3])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{}], EXPR$0=[COUNT($0)])\n"
+ + " LogicalProject(EMPNO=[$0])\n"
+ + " LogicalJoin(condition=[=($3, $11)], joinType=[full])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n"
+ + " LogicalFilter(condition=[=($7, $cor0.DEPTNO)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\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()));
+ final String planAfter = ""
+ + "LogicalProject(DNAME=[$1], MATCHED_SUBORDINATE_COUNT=[$4])\n"
+ + " LogicalJoin(condition=[=($0, $3)], joinType=[left])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(_cor_$cor0_0=[$0], EXPR$0=[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}], EXPR$0=[COUNT($1)])\n"
+ + " LogicalProject(_cor_$cor0_0=[COALESCE($8, $17)],
EMPNO=[$0])\n"
+ + " LogicalJoin(condition=[AND(=($3, $12), IS NOT DISTINCT
FROM($8, $17))], joinType=[full])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2],
MGR=[$3], HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], DEPTNO8=[$7])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($7)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
}
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 73b8c939b4..9095ff3059 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -7880,4 +7880,592 @@ WHERE deptno NOT IN (
# Reset to default value 20
!set trimfields true
+!use scott
+# [CALCITE-7379] LHS correlated variables are shadowed by nullable RHS outputs
in LEFT JOIN
+# Correlated scalar subquery with LEFT JOIN.
+# The correlation variable (d.deptno) is used in the RHS of the join.
+SELECT
+ d.dname,
+ (
+ SELECT COUNT(sub.empno)
+ FROM emp e
+ LEFT JOIN (
+ SELECT * FROM emp e2 WHERE e2.deptno = d.deptno
+ ) sub
+ ON e.mgr = sub.mgr
+ ) as matched_subordinate_count
+FROM dept d;
++------------+---------------------------+
+| DNAME | MATCHED_SUBORDINATE_COUNT |
++------------+---------------------------+
+| ACCOUNTING | 4 |
+| OPERATIONS | 0 |
+| RESEARCH | 9 |
+| SALES | 28 |
++------------+---------------------------+
+(4 rows)
+
+!ok
+
+# Correlated scalar subquery with RIGHT JOIN.
+# The correlation variable (d.deptno) is used in the LHS of the join.
+SELECT
+ d.dname,
+ (
+ SELECT COUNT(sub.empno)
+ FROM (
+ SELECT * FROM emp e2 WHERE e2.deptno = d.deptno
+ ) sub
+ RIGHT JOIN emp e
+ ON sub.mgr = e.mgr
+ ) as matched_subordinate_count
+FROM dept d;
++------------+---------------------------+
+| DNAME | MATCHED_SUBORDINATE_COUNT |
++------------+---------------------------+
+| ACCOUNTING | 4 |
+| OPERATIONS | 0 |
+| RESEARCH | 9 |
+| SALES | 28 |
++------------+---------------------------+
+(4 rows)
+
+!ok
+
+# Correlated scalar subquery with FULL JOIN.
+# The correlation variable (d.deptno) is used in the LHS of the join.
+SELECT
+ d.dname,
+ (
+ SELECT COUNT(sub.empno)
+ FROM (
+ SELECT * FROM emp e2 WHERE e2.deptno = d.deptno
+ ) sub
+ FULL JOIN emp e
+ ON sub.mgr = e.mgr
+ ) as matched_subordinate_count
+FROM dept d
+order by d.dname;
++------------+---------------------------+
+| DNAME | MATCHED_SUBORDINATE_COUNT |
++------------+---------------------------+
+| ACCOUNTING | 5 |
+| OPERATIONS | 0 |
+| RESEARCH | 9 |
+| SALES | 28 |
++------------+---------------------------+
+(4 rows)
+
+!ok
+
+# Correlated NOT EXISTS subquery with LEFT JOIN.
+# The correlation variable (d.deptno) is used in the RHS of the join.
+SELECT * FROM dept d
+WHERE NOT EXISTS (
+ SELECT 1
+ FROM emp e
+ LEFT JOIN (
+ SELECT * FROM emp e3 WHERE e3.deptno = d.deptno
+ ) foo
+ ON e.empno = foo.mgr
+);
++--------+-------+-----+
+| DEPTNO | DNAME | LOC |
++--------+-------+-----+
++--------+-------+-----+
+(0 rows)
+
+!ok
+
+# Correlated NOT EXISTS subquery with RIGHT JOIN.
+# The correlation variable (d.deptno) is used in the LHS of the join.
+SELECT * FROM dept d
+WHERE NOT EXISTS (
+ SELECT 1
+ FROM (
+ SELECT * FROM emp e3 WHERE e3.deptno = d.deptno
+ ) foo
+ RIGHT JOIN emp e
+ ON foo.mgr = e.empno
+);
++--------+-------+-----+
+| DEPTNO | DNAME | LOC |
++--------+-------+-----+
++--------+-------+-----+
+(0 rows)
+
+!ok
+
+# Correlated NOT EXISTS subquery with FULL JOIN.
+# The correlation variable (d.deptno) is used in the LHS of the join.
+SELECT * FROM dept d
+WHERE NOT EXISTS (
+ SELECT 1
+ FROM (
+ SELECT * FROM emp e3 WHERE e3.deptno = d.deptno
+ ) foo
+ FULL JOIN emp e
+ ON foo.mgr = e.empno
+);
++--------+-------+-----+
+| DEPTNO | DNAME | LOC |
++--------+-------+-----+
++--------+-------+-----+
+(0 rows)
+
+!ok
+
+# Correlated EXISTS subquery with LEFT JOIN involving 'bonus' table.
+# The correlation variable (e.ename) is used in the RHS of the join.
+SELECT e.ename, e.job, e.sal
+FROM emp e
+WHERE EXISTS (
+ SELECT 1
+ FROM dept d
+ LEFT JOIN (
+ SELECT * FROM bonus b WHERE b.ename = e.ename
+ ) foo
+ ON d.loc = foo.job
+);
++--------+-----------+---------+
+| ENAME | JOB | SAL |
++--------+-----------+---------+
+| ADAMS | CLERK | 1100.00 |
+| ALLEN | SALESMAN | 1600.00 |
+| BLAKE | MANAGER | 2850.00 |
+| CLARK | MANAGER | 2450.00 |
+| FORD | ANALYST | 3000.00 |
+| JAMES | CLERK | 950.00 |
+| JONES | MANAGER | 2975.00 |
+| KING | PRESIDENT | 5000.00 |
+| MARTIN | SALESMAN | 1250.00 |
+| MILLER | CLERK | 1300.00 |
+| SCOTT | ANALYST | 3000.00 |
+| SMITH | CLERK | 800.00 |
+| TURNER | SALESMAN | 1500.00 |
+| WARD | SALESMAN | 1250.00 |
++--------+-----------+---------+
+(14 rows)
+
+!ok
+
+# Correlated EXISTS subquery with RIGHT JOIN involving 'bonus' table.
+# The correlation variable (e.ename) is used in the LHS of the join.
+SELECT e.ename, e.job, e.sal
+FROM emp e
+WHERE EXISTS (
+ SELECT 1
+ FROM (
+ SELECT * FROM bonus b WHERE b.ename = e.ename
+ ) foo
+ RIGHT JOIN dept d
+ ON foo.job = d.loc
+);
++--------+-----------+---------+
+| ENAME | JOB | SAL |
++--------+-----------+---------+
+| ADAMS | CLERK | 1100.00 |
+| ALLEN | SALESMAN | 1600.00 |
+| BLAKE | MANAGER | 2850.00 |
+| CLARK | MANAGER | 2450.00 |
+| FORD | ANALYST | 3000.00 |
+| JAMES | CLERK | 950.00 |
+| JONES | MANAGER | 2975.00 |
+| KING | PRESIDENT | 5000.00 |
+| MARTIN | SALESMAN | 1250.00 |
+| MILLER | CLERK | 1300.00 |
+| SCOTT | ANALYST | 3000.00 |
+| SMITH | CLERK | 800.00 |
+| TURNER | SALESMAN | 1500.00 |
+| WARD | SALESMAN | 1250.00 |
++--------+-----------+---------+
+(14 rows)
+
+!ok
+
+# Correlated EXISTS subquery with FULL JOIN involving 'bonus' table.
+# The correlation variable (e.ename) is used in the LHS of the join.
+SELECT e.ename, e.job, e.sal
+FROM emp e
+WHERE EXISTS (
+ SELECT 1
+ FROM (
+ SELECT * FROM bonus b WHERE b.ename = e.ename
+ ) foo
+ FULL JOIN dept d
+ ON foo.job = d.loc
+);
++--------+-----------+---------+
+| ENAME | JOB | SAL |
++--------+-----------+---------+
+| ADAMS | CLERK | 1100.00 |
+| ALLEN | SALESMAN | 1600.00 |
+| BLAKE | MANAGER | 2850.00 |
+| CLARK | MANAGER | 2450.00 |
+| FORD | ANALYST | 3000.00 |
+| JAMES | CLERK | 950.00 |
+| JONES | MANAGER | 2975.00 |
+| KING | PRESIDENT | 5000.00 |
+| MARTIN | SALESMAN | 1250.00 |
+| MILLER | CLERK | 1300.00 |
+| SCOTT | ANALYST | 3000.00 |
+| SMITH | CLERK | 800.00 |
+| TURNER | SALESMAN | 1500.00 |
+| WARD | SALESMAN | 1250.00 |
++--------+-----------+---------+
+(14 rows)
+
+!ok
+
+# Correlated EXISTS subquery with LEFT JOIN and complex correlation condition.
+# The correlation variable (e1.sal, e1.comm) is used in the RHS of the join.
+SELECT empno FROM emp e1
+WHERE EXISTS (
+ SELECT 1
+ FROM dept d
+ LEFT JOIN (
+ SELECT * FROM emp e2
+ WHERE e2.sal > (e1.sal + COALESCE(e1.comm, 0))
+ ) foo
+ ON d.deptno = foo.deptno
+);
++-------+
+| EMPNO |
++-------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7698 |
+| 7782 |
+| 7788 |
+| 7839 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7902 |
+| 7934 |
++-------+
+(14 rows)
+
+!ok
+
+# Correlated EXISTS subquery with RIGHT JOIN and complex correlation condition.
+# The correlation variable (e1.sal, e1.comm) is used in the LHS of the join.
+SELECT empno FROM emp e1
+WHERE EXISTS (
+ SELECT 1
+ FROM (
+ SELECT * FROM emp e2
+ WHERE e2.sal > (e1.sal + COALESCE(e1.comm, 0))
+ ) foo
+ RIGHT JOIN dept d
+ ON foo.deptno = d.deptno
+);
++-------+
+| EMPNO |
++-------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7698 |
+| 7782 |
+| 7788 |
+| 7839 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7902 |
+| 7934 |
++-------+
+(14 rows)
+
+!ok
+
+# Correlated EXISTS subquery with FULL JOIN and complex correlation condition.
+# The correlation variable (e1.sal, e1.comm) is used in the LHS of the join.
+SELECT empno FROM emp e1
+WHERE EXISTS (
+ SELECT 1
+ FROM (
+ SELECT * FROM emp e2
+ WHERE e2.sal > (e1.sal + COALESCE(e1.comm, 0))
+ ) foo
+ FULL JOIN dept d
+ ON foo.deptno = d.deptno
+);
++-------+
+| EMPNO |
++-------+
+| 7369 |
+| 7499 |
+| 7521 |
+| 7566 |
+| 7654 |
+| 7698 |
+| 7782 |
+| 7788 |
+| 7839 |
+| 7844 |
+| 7876 |
+| 7900 |
+| 7902 |
+| 7934 |
++-------+
+(14 rows)
+
+!ok
+
+# Correlated EXISTS subquery with LEFT JOIN on TRUE condition.
+# The correlation variable (d.deptno) is used in the RHS of the join.
+SELECT d.deptno
+FROM dept d
+WHERE EXISTS (
+ SELECT 1
+ FROM emp e
+ LEFT JOIN (
+ SELECT deptno FROM emp WHERE deptno = d.deptno
+ ) foo
+ ON TRUE
+ WHERE foo.deptno IS NOT DISTINCT FROM d.deptno
+);
++--------+
+| DEPTNO |
++--------+
+| 10 |
+| 20 |
+| 30 |
++--------+
+(3 rows)
+
+!ok
+
+# Correlated EXISTS subquery with RIGHT JOIN on TRUE condition.
+# The correlation variable (d.deptno) is used in the LHS of the join.
+SELECT d.deptno
+FROM dept d
+WHERE EXISTS (
+ SELECT 1
+ FROM (
+ SELECT deptno FROM emp WHERE deptno = d.deptno
+ ) foo
+ RIGHT JOIN emp e
+ ON TRUE
+ WHERE foo.deptno IS NOT DISTINCT FROM d.deptno
+);
++--------+
+| DEPTNO |
++--------+
+| 10 |
+| 20 |
+| 30 |
++--------+
+(3 rows)
+
+!ok
+
+# Correlated EXISTS subquery with FULL JOIN on TRUE condition.
+# The correlation variable (d.deptno) is used in the LHS of the join.
+SELECT d.deptno
+FROM dept d
+WHERE EXISTS (
+ SELECT 1
+ FROM (
+ SELECT deptno FROM emp WHERE deptno = d.deptno
+ ) foo
+ FULL JOIN emp e
+ ON TRUE
+ WHERE foo.deptno IS NOT DISTINCT FROM d.deptno
+);
++--------+
+| DEPTNO |
++--------+
+| 10 |
+| 20 |
+| 30 |
++--------+
+(3 rows)
+
+!ok
+
+# Correlated EXISTS subquery with LEFT JOIN.
+# The correlation variable (dept.deptno) is used in the RHS of the join.
+SELECT * FROM dept
+WHERE EXISTS (
+ SELECT * FROM emp
+ LEFT JOIN (
+ SELECT * FROM emp e_sub
+ WHERE e_sub.deptno = dept.deptno
+ ) foo
+ ON emp.deptno = foo.deptno
+);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
+| 40 | OPERATIONS | BOSTON |
++--------+------------+----------+
+(4 rows)
+
+!ok
+
+# Correlated EXISTS subquery with RIGHT JOIN.
+# The correlation variable (dept.deptno) is used in the LHS of the join.
+SELECT * FROM dept
+WHERE EXISTS (
+ SELECT * FROM (
+ SELECT * FROM emp e_sub
+ WHERE e_sub.deptno = dept.deptno
+ ) foo
+ RIGHT JOIN emp
+ ON foo.deptno = emp.deptno
+);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
+| 40 | OPERATIONS | BOSTON |
++--------+------------+----------+
+(4 rows)
+
+!ok
+
+# Correlated EXISTS subquery with FULL JOIN.
+# The correlation variable (dept.deptno) is used in the LHS of the join.
+SELECT * FROM dept
+WHERE EXISTS (
+ SELECT * FROM (
+ SELECT * FROM emp e_sub
+ WHERE e_sub.deptno = dept.deptno
+ ) foo
+ FULL JOIN emp
+ ON foo.deptno = emp.deptno
+);
++--------+------------+----------+
+| DEPTNO | DNAME | LOC |
++--------+------------+----------+
+| 10 | ACCOUNTING | NEW YORK |
+| 20 | RESEARCH | DALLAS |
+| 30 | SALES | CHICAGO |
+| 40 | OPERATIONS | BOSTON |
++--------+------------+----------+
+(4 rows)
+
+!ok
+
+# Correlated EXISTS subquery with LEFT JOIN using VALUES clause.
+# The correlation variable (t1.a) is used in the RHS of the join.
+WITH
+ t1(a, b, c) AS (VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4)),
+ t2(a, b, c) AS (VALUES (1, 1, 1), (3, 3, 3), (4, 4, 4)),
+ t3(a, b, c) AS (VALUES (1, 1, 1), (2, 2, 2), (4, 4, 4))
+SELECT * FROM t1 WHERE EXISTS (
+SELECT * FROM t2
+LEFT JOIN
+(SELECT * FROM t3 WHERE t3.a = t1.a) foo
+ON t2.a = foo.a
+);
++---+---+---+
+| A | B | C |
++---+---+---+
+| 2 | 2 | 2 |
+| 3 | 3 | 3 |
+| 4 | 4 | 4 |
++---+---+---+
+(3 rows)
+
+!ok
+
+# Correlated EXISTS subquery with LEFT JOIN using VALUES clause.
+# The correlation variable (t1.a) is used in the LHS of the join.
+WITH
+ t1(a, b, c) AS (VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4)),
+ t2(a, b, c) AS (VALUES (1, 1, 1), (3, 3, 3), (4, 4, 4)),
+ t3(a, b, c) AS (VALUES (1, 1, 1), (2, 2, 2), (4, 4, 4))
+SELECT * FROM t1 WHERE EXISTS (
+SELECT * FROM (SELECT * FROM t3 WHERE t3.a = t1.a) foo
+LEFT JOIN t2
+ON foo.a = t2.a
+);
++---+---+---+
+| A | B | C |
++---+---+---+
+| 2 | 2 | 2 |
+| 4 | 4 | 4 |
++---+---+---+
+(2 rows)
+
+!ok
+
+# Correlated EXISTS subquery with RIGHT JOIN using VALUES clause.
+# The correlation variable (t1.a) is used in the LHS of the join.
+WITH
+ t1(a, b, c) AS (VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4)),
+ t2(a, b, c) AS (VALUES (1, 1, 1), (3, 3, 3), (4, 4, 4)),
+ t3(a, b, c) AS (VALUES (1, 1, 1), (2, 2, 2), (4, 4, 4))
+SELECT * FROM t1 WHERE EXISTS (
+SELECT * FROM (SELECT * FROM t3 WHERE t3.a = t1.a) foo
+RIGHT JOIN t2
+ON foo.a = t2.a
+);
++---+---+---+
+| A | B | C |
++---+---+---+
+| 2 | 2 | 2 |
+| 3 | 3 | 3 |
+| 4 | 4 | 4 |
++---+---+---+
+(3 rows)
+
+!ok
+
+# Correlated EXISTS subquery with FULL JOIN using VALUES clause.
+# The correlation variable (t1.a) is used in the LHS of the join.
+WITH
+ t1(a, b, c) AS (VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4)),
+ t2(a, b, c) AS (VALUES (1, 1, 1), (3, 3, 3), (4, 4, 4)),
+ t3(a, b, c) AS (VALUES (1, 1, 1), (2, 2, 2), (4, 4, 4))
+SELECT * FROM t1 WHERE EXISTS (
+SELECT * FROM (SELECT * FROM t3 WHERE t3.a = t1.a) foo
+FULL JOIN t2
+ON foo.a = t2.a
+);
++---+---+---+
+| A | B | C |
++---+---+---+
+| 2 | 2 | 2 |
+| 3 | 3 | 3 |
+| 4 | 4 | 4 |
++---+---+---+
+(3 rows)
+
+!ok
+
+# Correlated EXISTS subquery with INNER JOIN using VALUES clause.
+# The correlation variable (t1.a) is used in the RHS of the join.
+WITH
+ t1(a, b, c) AS (VALUES (2, 2, 2), (3, 3, 3), (4, 4, 4)),
+ t2(a, b, c) AS (VALUES (1, 1, 1), (3, 3, 3), (4, 4, 4)),
+ t3(a, b, c) AS (VALUES (1, 1, 1), (2, 2, 2), (4, 4, 4))
+SELECT * FROM t1 WHERE EXISTS (
+SELECT * FROM t2
+INNER JOIN
+(SELECT * FROM t3 WHERE t3.a = t1.a) foo
+ON t2.a = foo.a
+);
++---+---+---+
+| A | B | C |
++---+---+---+
+| 4 | 4 | 4 |
++---+---+---+
+(1 row)
+
+!ok
# End sub-query.iq
