(calcite) branch main updated: [CALCITE-7655] RelToSqlConverter incorrectly removes a subquery when grouping by a window function result

[xuzifu666]

This is an automated email from the ASF dual-hosted git repository.

xuzifu666 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 f0528628cc [CALCITE-7655] RelToSqlConverter incorrectly removes a 
subquery when grouping by a window function result
f0528628cc is described below

commit f0528628cc532af4058620b72db71a02ee60b366
Author: zzwqqq <wqq...@163.com>
AuthorDate: Fri Jul 17 10:47:41 2026 +0800

    [CALCITE-7655] RelToSqlConverter incorrectly removes a subquery when 
grouping by a window function result
---
 .../apache/calcite/rel/rel2sql/SqlImplementor.java | 19 ++++++++++++
 .../calcite/rel/rel2sql/RelToSqlConverterTest.java | 34 ++++++++++++++++++++++
 2 files changed, 53 insertions(+)

diff --git 
a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java 
b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
index d8c02a56f3..c74da741d7 100644
--- a/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
+++ b/core/src/main/java/org/apache/calcite/rel/rel2sql/SqlImplementor.java
@@ -626,6 +626,21 @@ private static boolean isWindowedAggregate(SqlNode node) {
         && ((SqlCall) node).getOperator() instanceof SqlOverOperator;
   }
 
+  /** Returns whether one of an aggregate's group keys contains an OVER 
expression. */
+  private static boolean groupKeysContainOver(Aggregate aggregate) {
+    final RelNode input = aggregate.getInput();
+    if (!(input instanceof Project)) {
+      return false;
+    }
+    final Project project = (Project) input;
+    for (int group : aggregate.getGroupSet()) {
+      if (RexOver.containsOver(project.getProjects().get(group))) {
+        return true;
+      }
+    }
+    return false;
+  }
+
   /** Context for translating a {@link RexNode} expression (within a
    * {@link RelNode}) into a {@link SqlNode} expression (within a SQL parse
    * tree). */
@@ -2172,6 +2187,10 @@ && hasSortByOrdinal(node)) {
           // Avoid losing the distinct attribute of inner aggregate.
           return !hasNestedAgg || Aggregate.isNotGrandTotal(agg);
         }
+
+        if (groupKeysContainOver(agg)) {
+          return true;
+        }
       }
 
       return false;
diff --git 
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java 
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
index 44dc09078d..801612976b 100644
--- 
a/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
+++ 
b/core/src/test/java/org/apache/calcite/rel/rel2sql/RelToSqlConverterTest.java
@@ -2016,6 +2016,40 @@ private static String toSql(RelNode root, SqlDialect 
dialect,
     relFn(relFn).withOracle().ok(expectedOracle);
   }
 
+  /** Test case for
+   * <a 
href="https://issues.apache.org/jira/browse/CALCITE-7655";>[CALCITE-7655]
+   * RelToSqlConverter incorrectly removes a subquery when grouping by a 
window function
+   * result</a>. */
+  @Test void testGroupByWindowFunction() {
+    final String query = "SELECT \"EMPNO\", \"row_number\", COUNT(*) AS 
\"c\"\n"
+        + "FROM (\n"
+        + "  SELECT \"EMPNO\",\n"
+        + "    ROW_NUMBER() OVER (ORDER BY \"EMPNO\" NULLS FIRST) AS 
\"row_number\"\n"
+        + "  FROM \"EMP\") AS \"t\"\n"
+        + "GROUP BY \"EMPNO\", \"row_number\"";
+
+    final String expectedMysql = "SELECT `EMPNO`, `row_number`, COUNT(*) AS 
`c`\n"
+        + "FROM (SELECT `EMPNO`, ROW_NUMBER() OVER (ORDER BY `EMPNO`) AS 
`row_number`\n"
+        + "FROM `SCOTT`.`EMP`) AS `t`\n"
+        + "GROUP BY `EMPNO`, `row_number`";
+    final String expectedOracle = "SELECT \"EMPNO\", \"row_number\", COUNT(*) 
\"c\"\n"
+        + "FROM (SELECT \"EMPNO\", ROW_NUMBER() OVER (ORDER BY \"EMPNO\" NULLS 
FIRST)"
+        + " \"row_number\"\n"
+        + "FROM \"SCOTT\".\"EMP\") \"t\"\n"
+        + "GROUP BY \"EMPNO\", \"row_number\"";
+    final String expectedPostgresql =
+        "SELECT \"EMPNO\", \"row_number\", COUNT(*) AS \"c\"\n"
+        + "FROM (SELECT \"EMPNO\", ROW_NUMBER() OVER (ORDER BY \"EMPNO\" NULLS 
FIRST)"
+        + " AS \"row_number\"\n"
+        + "FROM \"SCOTT\".\"EMP\") AS \"t\"\n"
+        + "GROUP BY \"EMPNO\", \"row_number\"";
+    sql(query)
+        .schema(CalciteAssert.SchemaSpec.JDBC_SCOTT)
+        .withMysql().ok(expectedMysql)
+        .withOracle().ok(expectedOracle)
+        .withPostgresql().ok(expectedPostgresql);
+  }
+
   @Test void testSemiJoin() {
     final RelBuilder builder = relBuilder();
     final RelNode root = builder