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 fcb86f0753 [CALCITE-7257] Subqueries cannot be decorrelated if join
condition contains RexFieldAccess
fcb86f0753 is described below
commit fcb86f0753d4e38d58bf1da6c8b1754301499b07
Author: iwanttobepowerful <[email protected]>
AuthorDate: Thu Dec 25 10:03:20 2025 +0800
[CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 78 +++--
.../calcite/sql2rel/RelDecorrelatorTest.java | 74 +++++
core/src/test/resources/sql/sub-query.iq | 361 +++++++++++++++++++++
3 files changed, 489 insertions(+), 24 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 ad6d12ca98..80f1f95335 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -1543,17 +1543,17 @@ private RelNode getCorRel(CorRef corVar) {
/** Adds a value generator to satisfy the correlating variables used by
* a relational expression, if those variables are not already provided by
* its input. */
- private Frame maybeAddValueGenerator(RelNode rel, Frame frame) {
- final CorelMap cm1 = new CorelMapBuilder().build(frame.r, rel);
+ private Frame maybeAddValueGenerator(RelNode rel, Frame inputFrame) {
+ final CorelMap cm1 = new CorelMapBuilder().build(inputFrame.r, rel);
if (!cm1.mapRefRelToCorRef.containsKey(rel)) {
- return frame;
+ return inputFrame;
}
final Collection<CorRef> needs = cm1.mapRefRelToCorRef.get(rel);
- final ImmutableSortedSet<CorDef> haves = frame.corDefOutputs.keySet();
+ final ImmutableSortedSet<CorDef> haves = inputFrame.corDefOutputs.keySet();
if (hasAll(needs, haves)) {
- return frame;
+ return inputFrame;
}
- return decorrelateInputWithValueGenerator(rel, frame);
+ return decorrelateInputWithValueGenerator(rel, inputFrame);
}
/** Returns whether all of a collection of {@link CorRef}s are satisfied
@@ -1579,13 +1579,13 @@ private static boolean has(Collection<CorDef> corDefs,
CorRef corr) {
return false;
}
- private Frame decorrelateInputWithValueGenerator(RelNode rel, Frame frame) {
+ private Frame decorrelateInputWithValueGenerator(RelNode rel, Frame
inputFrame) {
// currently only handles one input
assert rel.getInputs().size() == 1;
- RelNode oldInput = frame.r;
+ RelNode oldInput = inputFrame.r;
final NavigableMap<CorDef, Integer> corDefOutputs =
- new TreeMap<>(frame.corDefOutputs);
+ new TreeMap<>(inputFrame.corDefOutputs);
final Collection<CorRef> corVarList = cm.mapRefRelToCorRef.get(rel);
@@ -1606,8 +1606,7 @@ private Frame decorrelateInputWithValueGenerator(RelNode
rel, Frame frame) {
if (node instanceof RexInputRef) {
map.put(def, ((RexInputRef) node).getIndex());
} else {
- map.put(def,
- frame.r.getRowType().getFieldCount() + projects.size());
+ map.put(def, inputFrame.r.getRowType().getFieldCount() +
projects.size());
projects.add((RexNode) node);
}
}
@@ -1615,7 +1614,7 @@ private Frame decorrelateInputWithValueGenerator(RelNode
rel, Frame frame) {
// If all correlation variables are now satisfied, skip creating a value
// generator.
if (map.size() == corVarList.size()) {
- map.putAll(frame.corDefOutputs);
+ map.putAll(inputFrame.corDefOutputs);
final RelNode r;
if (!projects.isEmpty()) {
relBuilder.push(oldInput)
@@ -1624,17 +1623,40 @@ private Frame
decorrelateInputWithValueGenerator(RelNode rel, Frame frame) {
} else {
r = oldInput;
}
- return register(rel.getInput(0), r,
- frame.oldToNewOutputs, map);
+ return register(rel.getInput(0), r, inputFrame.oldToNewOutputs, map);
}
}
- int leftInputOutputCount = frame.r.getRowType().getFieldCount();
+ return createFrameWithValueGenerator(rel.getInput(0), inputFrame,
corVarList, corDefOutputs);
+ }
+
+ /**
+ * Creates a new {@link Frame} for the given rel by joining its current
+ * decorrelated rel with a value generator that produces the required
+ * correlation variables.
+ *
+ * <p>The value generator is built from {@code corVarList} and joined with
+ * {@code frame.r} using an INNER join. The provided
+ * {@code corDefOutputs} map is updated to reflect the positions of all
+ * correlation definitions in the join output, and the resulting frame is
+ * registered for {@code rel}.
+ *
+ * @param rel target RelNode whose frame is updated to use the join of
+ * {@code frame.r} and the value generator
+ * @param frame existing Frame of the rel
+ * @param corVarList correlated variables that still need to be produced
+ * @param corDefOutputs mapping from {@link CorDef} to output positions;
updated in place
+ * to include positions in the new join
+ * @return a new Frame describing {@code rel} after attaching the value
generator
+ */
+ private Frame createFrameWithValueGenerator(RelNode rel, Frame frame,
+ Collection<CorRef> corVarList, NavigableMap<CorDef, Integer>
corDefOutputs) {
+ int leftFieldCount = frame.r.getRowType().getFieldCount();
// can directly add positions into corDefOutputs since join
// does not change the output ordering from the inputs.
final RelNode valueGen =
- createValueGenerator(corVarList, leftInputOutputCount, corDefOutputs);
+ createValueGenerator(corVarList, leftFieldCount, corDefOutputs);
requireNonNull(valueGen, "valueGen");
RelNode join =
@@ -1647,8 +1669,7 @@ private Frame decorrelateInputWithValueGenerator(RelNode
rel, Frame frame) {
// Join or Filter does not change the old input ordering. All
// input fields from newLeftInput (i.e. the original input to the old
// Filter) are in the output and in the same position.
- return register(rel.getInput(0), join, frame.oldToNewOutputs,
- corDefOutputs);
+ return register(rel, join, frame.oldToNewOutputs, corDefOutputs);
}
/** Finds a {@link RexInputRef} that is equivalent to a {@link CorRef},
@@ -1931,8 +1952,19 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
return null;
}
+ 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);
+
+ final NavigableMap<CorDef, Integer> corDefOutputs = new TreeMap<>();
+ newLeftFrame = createFrameWithValueGenerator(oldLeft, leftFrame,
corVarList, corDefOutputs);
+ }
+
RelNode newJoin = relBuilder
- .push(leftFrame.r)
+ .push(newLeftFrame.r)
.push(rightFrame.r)
.join(rel.getJoinType(),
decorrelateExpr(castNonNull(currentRel), map, cm,
rel.getCondition()),
@@ -1944,7 +1976,7 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
Map<Integer, Integer> mapOldToNewOutputs = new HashMap<>();
int oldLeftFieldCount = oldLeft.getRowType().getFieldCount();
- int newLeftFieldCount = leftFrame.r.getRowType().getFieldCount();
+ int newLeftFieldCount = newLeftFrame.r.getRowType().getFieldCount();
int oldRightFieldCount = oldRight.getRowType().getFieldCount();
//noinspection AssertWithSideEffects
@@ -1952,8 +1984,7 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
== oldLeftFieldCount + oldRightFieldCount;
// Left input positions are not changed.
- mapOldToNewOutputs.putAll(leftFrame.oldToNewOutputs);
-
+ mapOldToNewOutputs.putAll(newLeftFrame.oldToNewOutputs);
// Right input positions are shifted by newLeftFieldCount.
for (int i = 0; i < oldRightFieldCount; i++) {
mapOldToNewOutputs.put(i + oldLeftFieldCount,
@@ -1961,8 +1992,7 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
}
final NavigableMap<CorDef, Integer> corDefOutputs =
- new TreeMap<>(leftFrame.corDefOutputs);
-
+ new TreeMap<>(newLeftFrame.corDefOutputs);
// Right input positions are shifted by newLeftFieldCount.
for (Map.Entry<CorDef, Integer> entry
: rightFrame.corDefOutputs.entrySet()) {
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 83bf5780cc..45ef07cc45 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -1198,4 +1198,78 @@ public static Frameworks.ConfigBuilder config() {
+ " LogicalTableScan(table=[[scott, EMP]])\n";
assertThat(after, hasTree(planAfter));
}
+
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7257";>[CALCITE-7257]
+ * Subqueries cannot be decorrelated if join condition contains
RexFieldAccess</a>. */
+ @Test void testJoinConditionContainsRexFieldAccess() {
+ 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 E1.* \n"
+ + "FROM\n"
+ + " EMP E1\n"
+ + "WHERE\n"
+ + " E1.EMPNO = (\n"
+ + " SELECT D1.DEPTNO FROM DEPT D1\n"
+ + " WHERE E1.ENAME IN (SELECT B1.ENAME FROM BONUS B1))";
+ 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(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n"
+ + " LogicalFilter(condition=[=($0, CAST($8):SMALLINT)])\n"
+ + " LogicalCorrelate(correlation=[$cor0], joinType=[left],
requiredColumns=[{1}])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])\n"
+ + " LogicalProject(DEPTNO=[$0])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2])\n"
+ + " LogicalJoin(condition=[=($cor0.ENAME, $3)],
joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(ENAME=[$0])\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()));
+ final String planAfter = ""
+ + "LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7])\n"
+ + " LogicalProject(EMPNO=[$0], ENAME=[$1], JOB=[$2], MGR=[$3],
HIREDATE=[$4], SAL=[$5], COMM=[$6], DEPTNO=[$7], ENAME0=[$8],
$f1=[CAST($9):TINYINT])\n"
+ + " LogicalJoin(condition=[AND(=($1, $8), =($0,
CAST($9):SMALLINT))], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])\n"
+ + " LogicalProject(ENAME=[$3], DEPTNO=[$0])\n"
+ + " LogicalJoin(condition=[=($3, $4)], joinType=[inner])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalProject(ENAME=[$1])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalProject(ENAME=[$0])\n"
+ + " LogicalTableScan(table=[[scott, BONUS]])\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 f64feb0a79..6aa385690a 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -6798,6 +6798,367 @@ FROM t0;
!ok
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.ENAME
+FROM EMP E1
+WHERE
+ E1.SAL > (SELECT D1.DEPTNO FROM
+ DEPT D1 JOIN EMP E2
+ ON E1.DEPTNO = E2.DEPTNO);
+more than one value in agg SINGLE_VALUE
+!error
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.ENAME
+FROM EMP E1
+WHERE
+ E1.SAL > (SELECT D1.DEPTNO FROM
+ DEPT D1 LEFT JOIN EMP E2
+ ON E1.DEPTNO = E2.DEPTNO);
+more than one value in agg SINGLE_VALUE
+!error
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT
+ E1.*
+FROM
+ EMP E1
+WHERE
+ E1.EMPNO = (
+ SELECT D1.DEPTNO FROM DEPT D1
+ WHERE E1.ENAME IN (SELECT B1.ENAME FROM BONUS B1));
++-------+-------+-----+-----+----------+-----+------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+-------+-----+-----+----------+-----+------+--------+
++-------+-------+-----+-----+----------+-----+------+--------+
+(0 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.ENAME
+FROM EMP E1
+WHERE EXISTS (
+ SELECT 1
+ FROM DEPT D
+ JOIN BONUS B
+ ON D.LOC = 'NEW YORK' OR B.JOB = E1.JOB
+);
++-------+
+| ENAME |
++-------+
++-------+
+(0 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.ENAME
+FROM EMP E1
+WHERE EXISTS (
+ SELECT 1
+ FROM DEPT D
+ LEFT JOIN BONUS B
+ ON D.LOC = 'NEW YORK' OR B.JOB = E1.JOB
+);
++--------+
+| ENAME |
++--------+
+| ADAMS |
+| ALLEN |
+| BLAKE |
+| CLARK |
+| FORD |
+| JAMES |
+| JONES |
+| KING |
+| MARTIN |
+| MILLER |
+| SCOTT |
+| SMITH |
+| TURNER |
+| WARD |
++--------+
+(14 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.EMPNO, E1.SAL
+FROM EMP E1
+WHERE E1.COMM > (
+ SELECT COUNT(*)
+ FROM BONUS B
+ JOIN DEPT D
+ ON B.SAL > E1.SAL AND D.DEPTNO = 10
+);
++-------+---------+
+| EMPNO | SAL |
++-------+---------+
+| 7499 | 1600.00 |
+| 7521 | 1250.00 |
+| 7654 | 1250.00 |
++-------+---------+
+(3 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.EMPNO, E1.SAL
+FROM EMP E1
+WHERE E1.COMM > (
+ SELECT COUNT(*)
+ FROM BONUS B
+ LEFT JOIN DEPT D
+ ON B.SAL > E1.SAL AND D.DEPTNO = 10
+);
++-------+---------+
+| EMPNO | SAL |
++-------+---------+
+| 7499 | 1600.00 |
+| 7521 | 1250.00 |
+| 7654 | 1250.00 |
++-------+---------+
+(3 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.ENAME
+FROM EMP E1
+WHERE EXISTS (
+ SELECT 1
+ FROM DEPT D
+ JOIN BONUS B
+ ON D.DNAME = B.ENAME AND B.JOB = E1.JOB
+ WHERE B.ENAME IS NULL
+);
++-------+
+| ENAME |
++-------+
++-------+
+(0 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.ENAME
+FROM EMP E1
+WHERE EXISTS (
+ SELECT 1
+ FROM DEPT D
+ LEFT JOIN BONUS B
+ ON D.DNAME = B.ENAME AND B.JOB = E1.JOB
+ WHERE B.ENAME IS NULL
+);
++--------+
+| ENAME |
++--------+
+| ADAMS |
+| ALLEN |
+| BLAKE |
+| CLARK |
+| FORD |
+| JAMES |
+| JONES |
+| KING |
+| MARTIN |
+| MILLER |
+| SCOTT |
+| SMITH |
+| TURNER |
+| WARD |
++--------+
+(14 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.ENAME
+FROM EMP E1
+WHERE EXISTS (
+ SELECT 1
+ FROM DEPT D
+ LEFT JOIN BONUS B
+ ON D.DNAME = B.ENAME AND B.JOB = E1.JOB
+);
++--------+
+| ENAME |
++--------+
+| ADAMS |
+| ALLEN |
+| BLAKE |
+| CLARK |
+| FORD |
+| JAMES |
+| JONES |
+| KING |
+| MARTIN |
+| MILLER |
+| SCOTT |
+| SMITH |
+| TURNER |
+| WARD |
++--------+
+(14 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.ENAME
+FROM EMP E1
+WHERE E1.SAL IN (
+ SELECT B.SAL
+ FROM BONUS B
+ JOIN DEPT D
+ ON D.DEPTNO = E1.DEPTNO
+ AND B.SAL = (CASE WHEN E1.COMM IS NULL THEN 0 ELSE E1.COMM END + 100)
+);
++-------+
+| ENAME |
++-------+
++-------+
+(0 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.ENAME
+FROM EMP E1
+WHERE E1.SAL IN (
+ SELECT B.SAL
+ FROM BONUS B
+ LEFT JOIN DEPT D
+ ON D.DEPTNO = E1.DEPTNO
+ AND B.SAL = (CASE WHEN E1.COMM IS NULL THEN 0 ELSE E1.COMM END + 100)
+);
++-------+
+| ENAME |
++-------+
++-------+
+(0 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.*
+FROM EMP E1
+WHERE NOT EXISTS (
+ SELECT 1
+ FROM EMP E2
+ JOIN BONUS B
+ ON E2.SAL = E1.SAL AND B.JOB = E1.JOB
+ WHERE E2.EMPNO <> E1.EMPNO
+);
++-------+--------+-----------+------+------------+---------+---------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+--------+-----------+------+------------+---------+---------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 |
++-------+--------+-----------+------+------------+---------+---------+--------+
+(14 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+SELECT E1.*
+FROM EMP E1
+WHERE NOT EXISTS (
+ SELECT 1
+ FROM EMP E2
+ JOIN BONUS B
+ ON E2.SAL = E1.SAL AND B.JOB = E1.JOB
+ WHERE E2.EMPNO <> E1.EMPNO
+);
++-------+--------+-----------+------+------------+---------+---------+--------+
+| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
++-------+--------+-----------+------+------------+---------+---------+--------+
+| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800.00 | | 20 |
+| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600.00 | 300.00 | 30 |
+| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250.00 | 500.00 | 30 |
+| 7566 | JONES | MANAGER | 7839 | 1981-02-04 | 2975.00 | | 20 |
+| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250.00 | 1400.00 | 30 |
+| 7698 | BLAKE | MANAGER | 7839 | 1981-01-05 | 2850.00 | | 30 |
+| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450.00 | | 10 |
+| 7788 | SCOTT | ANALYST | 7566 | 1987-04-19 | 3000.00 | | 20 |
+| 7839 | KING | PRESIDENT | | 1981-11-17 | 5000.00 | | 10 |
+| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500.00 | 0.00 | 30 |
+| 7876 | ADAMS | CLERK | 7788 | 1987-05-23 | 1100.00 | | 20 |
+| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950.00 | | 30 |
+| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000.00 | | 20 |
+| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300.00 | | 10 |
++-------+--------+-----------+------+------------+---------+---------+--------+
+(14 rows)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+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(t1c) FROM
+ (SELECT t1c
+ FROM t1 JOIN t2 ON (t1a = t0a AND t2b = t1b))
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 1 | 1 |
++-----+-----+
+(1 row)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+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(t1c) FROM
+ (SELECT t1c
+ FROM t1 JOIN t2 ON (t1a < t0a AND t2b >= t1b))
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 2 | 0 |
++-----+-----+
+(1 row)
+
+!ok
+
+# [CALCITE-7257] Subqueries cannot be decorrelated if join condition contains
RexFieldAccess
+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(t1c) FROM
+ (SELECT t1c
+ FROM t1 LEFT JOIN t2 ON (t1a = t0a AND t2b = t0b))
+);
++-----+-----+
+| T0A | T0B |
++-----+-----+
+| 1 | 1 |
+| 2 | 0 |
++-----+-----+
+(2 rows)
+
+!ok
+
# [CALCITE-6942] Support decorrelated for sub-queries with LIMIT 1 and OFFSET
select * from emp
where exists (
