This is an automated email from the ASF dual-hosted git repository.
mihaibudiu 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 6400e14a2d [CALCITE-7838] HsqldbSqlDialect incorrectly claims to
support nested aggregations
6400e14a2d is described below
commit 6400e14a2daa14259675c6446d3837472d353ce9
Author: Sean Broeder <[email protected]>
AuthorDate: Mon Oct 5 13:27:04 2026 -0700
[CALCITE-7838] HsqldbSqlDialect incorrectly claims to support nested
aggregations
HsqldbSqlDialect incorrectly used the default true behavior for
supportsNestedAggregations(), so an aggregate over an expression
of another aggregate was pushed down, which HSQLDB itself rejects.
Override supportsNestedAggregations() to return false so that the
converter generates a sub-SELECT, similar to how MySQL and
PostgreSQL are already handled.
---
.../calcite/sql/dialect/HsqldbSqlDialect.java | 4 +++
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 29 ++++++++++++++++++++++
.../org/apache/calcite/test/JdbcAdapterTest.java | 19 ++++++++++++++
3 files changed, 52 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
b/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
index e5b4791358..599d70f4a5 100644
--- a/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
+++ b/core/src/main/java/org/apache/calcite/sql/dialect/HsqldbSqlDialect.java
@@ -49,6 +49,10 @@ public HsqldbSqlDialect(Context context) {
super(context);
}
+ @Override public boolean supportsNestedAggregations() {
+ return false;
+ }
+
@Override public boolean supportsCharSet() {
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 3ca31df695..1e15dfb6ec 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
@@ -2745,6 +2745,35 @@ private static RelNode sqlToRel(String sql, SchemaPlus
defaultSchema,
.withDoris().ok(expectedStarRocks);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7838";>[CALCITE-7838]
+ * HsqldbSqlDialect incorrectly claims to support nested aggregations</a>.
+ *
+ * <p>The inner aggregate is wrapped in an expression, so the outer aggregate
+ * must read it from a sub-SELECT on dialects that do not support nested
+ * aggregate functions. Oracle can do it in a single SELECT. */
+ @Test void testNestedAggregatesOverExpression() {
+ final String query = "select\n"
+ + " SUM(\"net_weight1\") as \"net_weight_converted\"\n"
+ + " from ("
+ + " select\n"
+ + " SUM(\"net_weight\") + 1 as \"net_weight1\"\n"
+ + " from \"foodmart\".\"product\"\n"
+ + " group by \"product_id\")";
+ final String expectedOracle =
+ "SELECT SUM(SUM(\"net_weight\") + 1) \"net_weight_converted\"\n"
+ + "FROM \"foodmart\".\"product\"\n"
+ + "GROUP BY \"product_id\"";
+ final String expectedHsqldb =
+ "SELECT SUM(net_weight1) AS net_weight_converted\n"
+ + "FROM (SELECT SUM(net_weight) + 1 AS net_weight1\n"
+ + "FROM foodmart.product\n"
+ + "GROUP BY product_id) AS t1";
+ sql(query)
+ .withOracle().ok(expectedOracle)
+ .withHsqldb().ok(expectedHsqldb);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-2628";>[CALCITE-2628]
* JDBC adapter throws NullPointerException while generating GROUP BY query
diff --git a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
index fbf502169d..c8e540dc32 100644
--- a/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/JdbcAdapterTest.java
@@ -89,6 +89,25 @@ class JdbcAdapterTest {
.returnsCount(14);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-7838";>[CALCITE-7838]
+ * HsqldbSqlDialect incorrectly claims to support nested aggregations</a>.
+ *
+ * <p>Runs against a real HSQLDB, which rejects
+ * {@code SELECT SUM(CAST(SUM(x) AS DOUBLE)) ... GROUP BY y}. */
+ @Test void testNestedAggregateUnderCast() {
+ CalciteAssert.model(JdbcTest.FOODMART_SCOTT_MODEL)
+ .query("select sum(m) from (\n"
+ + " select cast(sum(sal) as double) as m\n"
+ + " from scott.emp group by deptno)")
+ .enable(CalciteAssert.DB == CalciteAssert.DatabaseInstance.HSQLDB)
+ .planHasSql("SELECT SUM(\"M\")\n"
+ + "FROM (SELECT CAST(SUM(\"SAL\") AS DOUBLE) AS \"M\"\n"
+ + "FROM \"SCOTT\".\"EMP\"\n"
+ + "GROUP BY \"DEPTNO\") AS \"t0\"")
+ .returns("EXPR$0=29025.0\n");
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-5161";>[CALCITE-5161]
* NPE when inserting a null value into a decimal column</a>. */
