This is an automated email from the ASF dual-hosted git repository.
iwanttobepowerful 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 17bf870d32 [CALCITE-7540] Correlated outer reference in HAVING of
grouped subquery is incorrectly reported as not grouped
17bf870d32 is described below
commit 17bf870d32d1362055e1df0050eec4e7b0f7e03c
Author: Weihua Zhang <[email protected]>
AuthorDate: Wed Jun 3 11:08:38 2026 +0800
[CALCITE-7540] Correlated outer reference in HAVING of grouped subquery is
incorrectly reported as not grouped
---
.../apache/calcite/sql/validate/AggChecker.java | 18 +++++
.../apache/calcite/sql2rel/RelDecorrelator.java | 15 +++-
.../calcite/sql2rel/RelDecorrelatorTest.java | 79 ++++++++++++++++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 10 +++
core/src/test/resources/sql/sub-query.iq | 26 +++++++
5 files changed, 145 insertions(+), 3 deletions(-)
diff --git a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
index 21253ef31f..23928b83ac 100644
--- a/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
+++ b/core/src/main/java/org/apache/calcite/sql/validate/AggChecker.java
@@ -139,6 +139,12 @@ && isMeasureExp(id)) {
if (isGroupExpr(fqId.identifier)) {
return null;
}
+
+ // Outer references are not governed by the GROUP BY list of this
+ // aggregate query; they are validated in their own enclosing scope.
+ if (isOuterReference(firstScope, fqId)) {
+ return null;
+ }
SqlNode originalExpr = validator.getOriginal(id);
final String exprString = originalExpr.toString();
throw validator.newValidationError(originalExpr,
@@ -147,6 +153,18 @@ && isMeasureExp(id)) {
: RESOURCE.notGroupExpr(exprString));
}
+ /** Returns whether an identifier refers to a scope outside this aggregate.
*/
+ private boolean isOuterReference(SqlValidatorScope scope, SqlQualified fqId)
{
+ if (!(scope instanceof AggregatingSelectScope) || fqId.prefixLength <= 0) {
+ return false;
+ }
+
+ final SqlValidatorScope currentSelectScope = ((AggregatingSelectScope)
scope).parent;
+ final SqlValidatorScope.ResolvedImpl resolved = new
SqlValidatorScope.ResolvedImpl();
+ scope.resolve(fqId.prefix(), validator.catalogReader.nameMatcher(), false,
resolved);
+ return resolved.count() == 1 &&
!resolved.only().scope.isWithin(currentSelectScope);
+ }
+
@Override public Void visit(SqlCall call) {
final SqlValidatorScope scope =
requireNonNull(scopes.peek(), () -> "scope for " + call);
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 a450a659dc..14d72f8dfe 100644
--- a/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
+++ b/core/src/main/java/org/apache/calcite/sql2rel/RelDecorrelator.java
@@ -1634,11 +1634,20 @@ private Frame
decorrelateInputWithValueGenerator(RelNode rel, Frame inputFrame)
findCorrelationEquivalent(correlation, ((Filter)
rel).getCondition());
} catch (Util.FoundOne e) {
Object node = requireNonNull(e.getNode(), "e.getNode()");
- if (node instanceof RexInputRef) {
- map.put(def, ((RexInputRef) node).getIndex());
+ // findCorrelationEquivalent returns an expression from the original
+ // Filter condition, so its input refs are still in the
pre-decorrelation
+ // coordinate system. Correlation outputs are recorded against the
+ // decorrelated input; translate the expression first, otherwise a
+ // correlated field may be bound to a column with the same ordinal
but
+ // different meaning in the new RelNode.
+ final RexNode newNode = node instanceof RexInputRef
+ ? getNewForOldInputRef(rel, this.map, (RexInputRef) node)
+ : decorrelateExpr(rel, this.map, cm, (RexNode) node);
+ if (newNode instanceof RexInputRef) {
+ map.put(def, ((RexInputRef) newNode).getIndex());
} else {
map.put(def, inputFrame.r.getRowType().getFieldCount() +
projects.size());
- projects.add((RexNode) node);
+ projects.add(newNode);
}
}
}
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 15f96eaa9e..245b248157 100644
--- a/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
+++ b/core/src/test/java/org/apache/calcite/sql2rel/RelDecorrelatorTest.java
@@ -1831,6 +1831,85 @@ public static Frameworks.ConfigBuilder config() {
assertThat(after, hasTree(planAfter));
}
+ /** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7540";>[CALCITE-7540]
+ * Correlated outer reference in HAVING of grouped subquery is incorrectly
reported as not
+ * grouped</a>. */
+ @Test void test7540() {
+ 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"
+ + "FROM dept d\n"
+ + "INNER JOIN emp e\n"
+ + " ON e.sal < (\n"
+ + " SELECT MAX(e2.sal)\n"
+ + " FROM emp e2\n"
+ + " WHERE e2.job = e.job\n"
+ + " GROUP BY e2.job\n"
+ + " HAVING MIN(e2.deptno) = d.deptno\n"
+ + " )";
+ 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(DEPTNO=[$0], DNAME=[$1], LOC=[$2], EMPNO=[$3],
ENAME=[$4], JOB=[$5], MGR=[$6], HIREDATE=[$7], SAL=[$8], COMM=[$9],
DEPTNO0=[$10])\n"
+ + " LogicalProject(DEPTNO=[$0], DNAME=[$1], LOC=[$2], EMPNO=[$3],
ENAME=[$4], JOB=[$5], MGR=[$6], HIREDATE=[$7], SAL=[$8], COMM=[$9],
DEPTNO0=[$10])\n"
+ + " LogicalFilter(condition=[<($8, $11)])\n"
+ + " LogicalCorrelate(correlation=[$cor1], joinType=[left],
requiredColumns=[{0, 5}])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{}], agg#0=[SINGLE_VALUE($0)])\n"
+ + " LogicalProject(EXPR$0=[$1])\n"
+ + " LogicalFilter(condition=[=($2, $cor1.DEPTNO)])\n"
+ + " LogicalAggregate(group=[{0}], EXPR$0=[MAX($1)],
agg#1=[MIN($2)])\n"
+ + " LogicalProject(JOB=[$2], SAL=[$5], DEPTNO=[$7])\n"
+ + " LogicalFilter(condition=[=($2, $cor1.JOB)])\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(DEPTNO=[$0], DNAME=[$1], LOC=[$2], EMPNO=[$3],
ENAME=[$4], JOB=[$5], MGR=[$6], HIREDATE=[$7], SAL=[$8], COMM=[$9],
DEPTNO0=[$10])\n"
+ + " LogicalJoin(condition=[AND(=($0, $11), =($5, $12), <($8, $13))],
joinType=[inner])\n"
+ + " LogicalJoin(condition=[true], joinType=[inner])\n"
+ + " LogicalTableScan(table=[[scott, DEPT]])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n"
+ + " LogicalAggregate(group=[{0, 1}], agg#0=[SINGLE_VALUE($2)])\n"
+ + " LogicalProject($f3=[$3], JOB3=[$1], EXPR$0=[$2])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($3)])\n"
+ + " LogicalAggregate(group=[{0, 1}], EXPR$0=[MAX($2)],
agg#1=[MIN($3)])\n"
+ + " LogicalProject(JOB=[$2], JOB3=[$2], SAL=[$5],
DEPTNO=[$7])\n"
+ + " LogicalFilter(condition=[IS NOT NULL($2)])\n"
+ + " LogicalTableScan(table=[[scott, EMP]])\n";
+ assertThat(after, hasTree(planAfter));
+ }
+
/** Test case for <a
href="https://issues.apache.org/jira/browse/CALCITE-7442";>[CALCITE-7442]
* Getting Wrong index of Correlated variable inside Subquery after
FilterJoinRule</a>. */
@Test void testCorrelatedVariableIndexForInClause() {
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 9a080efbf8..1cba948097 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -6569,6 +6569,16 @@ void testReturnsCorrectRowTypeOnCombinedJoin() {
sql("select sum(sal + sal) from emp having sum(sal) > 10").ok();
sql("SELECT deptno FROM emp GROUP BY deptno HAVING ^sal^ > 10")
.fails("Expression 'SAL' is not being grouped");
+ sql("SELECT *\n"
+ + "FROM dept d\n"
+ + "INNER JOIN emp e\n"
+ + " ON e.sal < (\n"
+ + " SELECT MAX(e2.sal)\n"
+ + " FROM emp e2\n"
+ + " WHERE e2.job = e.job\n"
+ + " GROUP BY e2.job\n"
+ + " HAVING MIN(e2.deptno) = d.deptno\n"
+ + " )").ok();
}
@Test void testHavingBetween() {
diff --git a/core/src/test/resources/sql/sub-query.iq
b/core/src/test/resources/sql/sub-query.iq
index 2860493795..7c7e9fb053 100644
--- a/core/src/test/resources/sql/sub-query.iq
+++ b/core/src/test/resources/sql/sub-query.iq
@@ -9379,4 +9379,30 @@ FROM (SELECT deptno, SUM(sal) AS s FROM emp GROUP BY
deptno) t;
!ok
+# [CALCITE-7540] Correlated outer reference in HAVING of grouped subquery is
incorrectly reported as not grouped
+SELECT *
+FROM dept d
+INNER JOIN emp e
+ ON e.sal < (
+ SELECT MAX(e2.sal)
+ FROM emp e2
+ WHERE e2.job = e.job
+ GROUP BY e2.job
+ HAVING MIN(e2.deptno) = d.deptno
+ );
++--------+------------+----------+-------+--------+----------+------+------------+---------+---------+---------+
+| DEPTNO | DNAME | LOC | EMPNO | ENAME | JOB | MGR | HIREDATE
| SAL | COMM | DEPTNO0 |
++--------+------------+----------+-------+--------+----------+------+------------+---------+---------+---------+
+| 10 | ACCOUNTING | NEW YORK | 7369 | SMITH | CLERK | 7902 |
1980-12-17 | 800.00 | | 20 |
+| 10 | ACCOUNTING | NEW YORK | 7698 | BLAKE | MANAGER | 7839 |
1981-01-05 | 2850.00 | | 30 |
+| 10 | ACCOUNTING | NEW YORK | 7782 | CLARK | MANAGER | 7839 |
1981-06-09 | 2450.00 | | 10 |
+| 10 | ACCOUNTING | NEW YORK | 7876 | ADAMS | CLERK | 7788 |
1987-05-23 | 1100.00 | | 20 |
+| 10 | ACCOUNTING | NEW YORK | 7900 | JAMES | CLERK | 7698 |
1981-12-03 | 950.00 | | 30 |
+| 30 | SALES | CHICAGO | 7521 | WARD | SALESMAN | 7698 |
1981-02-22 | 1250.00 | 500.00 | 30 |
+| 30 | SALES | CHICAGO | 7654 | MARTIN | SALESMAN | 7698 |
1981-09-28 | 1250.00 | 1400.00 | 30 |
+| 30 | SALES | CHICAGO | 7844 | TURNER | SALESMAN | 7698 |
1981-09-08 | 1500.00 | 0.00 | 30 |
++--------+------------+----------+-------+--------+----------+------+------------+---------+---------+---------+
+(8 rows)
+
+!ok
# End sub-query.iq
