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 3b416debe7 [CALCITE-7297] The result is incorrect when the GROUP BY
key in a subquery is a RexFieldAccess
3b416debe7 is described below
commit 3b416debe7127a72afe36a716ec9d62ec56c7319
Author: iwanttobepowerful <[email protected]>
AuthorDate: Mon Dec 8 22:36:52 2025 +0800
[CALCITE-7297] The result is incorrect when the GROUP BY key in a subquery
is a RexFieldAccess
---
.../apache/calcite/sql2rel/RelDecorrelator.java | 12 +---
.../calcite/sql2rel/RelDecorrelatorTest.java | 69 ++++++++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 14 ++--
core/src/test/resources/sql/sub-query.iq | 75 ++++++++++++++++++++++
4 files changed, 151 insertions(+), 19 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 e45edff7e5..db647da89c 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -1217,9 +1217,7 @@ private static void shiftMapping(Map<Integer, Integer>
mapping, int startIndex,
// If this Project has correlated reference, create value generator
// and produce the correlated variables in the new output.
- if (cm.mapRefRelToCorRef.containsKey(rel)) {
- frame = decorrelateInputWithValueGenerator(rel, frame);
- }
+ frame = maybeAddValueGenerator(rel, frame);
// Project projects the original expressions
final Map<Integer, Integer> mapOldToNewOutputs = new HashMap<>();
@@ -1609,13 +1607,7 @@ private static boolean isWidening(RelDataType type,
RelDataType type1) {
// If this Filter has correlated reference, create value generator
// and produce the correlated variables in the new output.
- if (false) {
- if (cm.mapRefRelToCorRef.containsKey(rel)) {
- frame = decorrelateInputWithValueGenerator(rel, frame);
- }
- } else {
- frame = maybeAddValueGenerator(rel, frame);
- }
+ frame = maybeAddValueGenerator(rel, frame);
final CorelMap cm2 = new CorelMapBuilder().build(rel);
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 f6504fdef9..b92606be5b 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -356,6 +356,75 @@ public static Frameworks.ConfigBuilder config() {
assertThat(after, hasTree(planAfter));
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7297";>[CALCITE-7297]
+ * The result is incorrect when the GROUP BY key in a subquery is a
RexFieldAccess</a>. */
+ @Test void testSkipsRedundantValueGenerator() {
+ 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"
+ + " (SELECT COUNT(*)\n"
+ + " FROM \n"
+ + " (\n"
+ + " SELECT empno, ename, job\n"
+ + " FROM emp\n"
+ + " WHERE emp.deptno = dept.deptno) AS sub\n"
+ + " GROUP BY deptno) AS num_dept_groups\n"
+ + "FROM dept";
+ 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 = ""
+ + "LogicalCorrelate(correlation=[$cor1], joinType=[left],
requiredColumns=[{0}])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])\n"
+ + " LogicalProject(EXPR$0=[$1])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[COUNT()])\n"
+ + " LogicalProject($f0=[$cor1.DEPTNO])\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(DEPTNO=[$0], DNAME=[$1], LOC=[$2], $f1=[$4])\n"
+ + " LogicalJoin(condition=[=($0, $3)], joinType=[left])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalAggregate(group=[{0}], agg#0=[SINGLE_VALUE($1)])\n"
+ + " LogicalProject(DEPTNO=[$1], EXPR$0=[$2])\n"
+ + " LogicalAggregate(group=[{0, 1}], EXPR$0=[COUNT()])\n"
+ + " LogicalProject($f0=[$7], 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/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index a1c7f101b3..7cdde32314 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -1601,15 +1601,11 @@ LogicalProject(C=[$0], D=[$1], C0=[$4], F=[$5])
LogicalJoin(condition=[AND(=($0, $6), =($3, $7))], joinType=[inner])
LogicalProject(C=[$0], D=[$1], C0=[$0], $f3=[+($0, $1)])
LogicalValues(tuples=[[{ 4, 5 }]])
- LogicalProject(C=[$3], F=[*($0, $3)], C0=[$3], $f3=[$2])
- LogicalJoin(condition=[true], joinType=[inner])
- LogicalJoin(condition=[=($2, *($0, $1))], joinType=[inner])
- LogicalValues(tuples=[[{ 2 }]])
- LogicalAggregate(group=[{0, 1}])
- LogicalProject(C=[$0], $f3=[+($0, $1)])
- LogicalValues(tuples=[[{ 4, 5 }]])
- LogicalAggregate(group=[{0}])
- LogicalProject(C=[$0])
+ LogicalProject(C=[$1], F=[*($0, $1)], C2=[$1], $f3=[$2])
+ LogicalJoin(condition=[=($2, *($0, $1))], joinType=[inner])
+ LogicalValues(tuples=[[{ 2 }]])
+ LogicalAggregate(group=[{0, 1}])
+ LogicalProject(C=[$0], $f3=[+($0, $1)])
LogicalValues(tuples=[[{ 4, 5 }]])
]]>
</Resource>
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 59fda9fca2..a12b087ebb 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -5544,4 +5544,79 @@ WHERE EXISTS
!ok
+# [CALCITE-7297] The result is incorrect when the GROUP BY key in a subquery
is a RexFieldAccess
+SELECT *,
+ (SELECT COUNT(*)
+ FROM
+ (
+ SELECT empno, ename, job
+ FROM emp
+ WHERE emp.deptno = dept.deptno) AS sub
+ GROUP BY deptno) AS num_dept_groups
+FROM dept;
++--------+------------+----------+-----------------+
+| DEPTNO | DNAME | LOC | NUM_DEPT_GROUPS |
++--------+------------+----------+-----------------+
+| 10 | ACCOUNTING | NEW YORK | 3 |
+| 20 | RESEARCH | DALLAS | 5 |
+| 30 | SALES | CHICAGO | 6 |
+| 40 | OPERATIONS | BOSTON | |
++--------+------------+----------+-----------------+
+(4 rows)
+
+!ok
+
+# [CALCITE-7297] The result is incorrect when the GROUP BY key in a subquery
is a RexFieldAccess
+SELECT *,
+ (SELECT COUNT(*)
+ FROM
+ (
+ SELECT empno, ename, job, comm
+ FROM emp
+ WHERE emp.deptno = dept.deptno
+ ORDER BY empno LIMIT 1
+ ) AS sub
+ GROUP BY sub.comm
+ ) AS num_dept_groups
+FROM dept;
++--------+------------+----------+-----------------+
+| DEPTNO | DNAME | LOC | NUM_DEPT_GROUPS |
++--------+------------+----------+-----------------+
+| 10 | ACCOUNTING | NEW YORK | 1 |
+| 20 | RESEARCH | DALLAS | 1 |
+| 30 | SALES | CHICAGO | 1 |
+| 40 | OPERATIONS | BOSTON | |
++--------+------------+----------+-----------------+
+(4 rows)
+
+!ok
+
+# [CALCITE-7297] The result is incorrect when the GROUP BY key in a subquery
is a RexFieldAccess
+select * from (values (4, 5)) as t(c, d)
+cross join lateral
+(select c, a*c as f
+from (values 2) as s(a)
+where c+d=a*c);
++---+---+----+---+
+| C | D | C0 | F |
++---+---+----+---+
++---+---+----+---+
+(0 rows)
+
+!ok
+
+# [CALCITE-7297] The result is incorrect when the GROUP BY key in a subquery
is a RexFieldAccess
+select * from (values (2,2), (2,2)) as t(c,d)
+cross join lateral
+(select c,a*c as f from (values 2) as s(a)
+where c+d=a*c);
++---+---+----+---+
+| C | D | C0 | F |
++---+---+----+---+
+| 2 | 2 | 2 | 4 |
+| 2 | 2 | 2 | 4 |
++---+---+----+---+
+(2 rows)
+
+!ok
# End sub-query.iq
