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 2e99d5118e [CALCITE-6655] Aggregation of deeply nested window not
detected when unparsing
2e99d5118e is described below
commit 2e99d5118efbba21c87e7da08b8359ce296e138d
Author: Lino Rosa <[email protected]>
AuthorDate: Wed Oct 30 20:14:49 2024 -0400
[CALCITE-6655] Aggregation of deeply nested window not detected when
unparsing
---
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 20 ++++++-----
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 42 ++++++++++++++++++++++
2 files changed, 54 insertions(+), 8 deletions(-)
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 a5f6afeb91..e9a7f88e74 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
@@ -93,6 +93,7 @@ import org.apache.calcite.sql.parser.SqlParserPos;
import org.apache.calcite.sql.type.SqlTypeFactoryImpl;
import org.apache.calcite.sql.type.SqlTypeFamily;
import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.sql.util.SqlShuttle;
import org.apache.calcite.sql.validate.SqlValidatorUtil;
import org.apache.calcite.util.DateString;
import org.apache.calcite.util.ImmutableBitSet;
@@ -2099,8 +2100,9 @@ public abstract class SqlImplementor {
@UnknownInitialization Result this,
Aggregate aggregate,
Predicate<SqlNode> operandPredicate) {
+ final boolean[] result = {false};
if (node instanceof SqlSelect) {
- final SqlNodeList selectList = ((SqlSelect) node).getSelectList();
+ SqlNodeList selectList = ((SqlSelect) node).getSelectList();
if (!selectList.equals(SqlNodeList.SINGLETON_STAR)) {
final Set<Integer> aggregatesArgs = new HashSet<>();
for (AggregateCall aggregateCall : aggregate.getAggCallList()) {
@@ -2108,18 +2110,20 @@ public abstract class SqlImplementor {
}
for (int aggregatesArg : aggregatesArgs) {
if (selectList.get(aggregatesArg) instanceof SqlBasicCall) {
- final SqlBasicCall call =
- (SqlBasicCall) selectList.get(aggregatesArg);
- for (SqlNode operand : call.getOperandList()) {
- if (operand != null && operandPredicate.test(operand)) {
- return true;
- }
+ final SqlBasicCall call = (SqlBasicCall)
selectList.get(aggregatesArg);
+ if (call != null) {
+ call.accept(new SqlShuttle() {
+ @Override public @Nullable SqlNode visit(SqlCall call) {
+ result[0] = result[0] || operandPredicate.test(call);
+ return super.visit(call);
+ }
+ });
}
}
}
}
}
- return false;
+ return result[0];
}
/** Returns the highest clause that is in use. */
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 78aa404b8e..f291bc4346 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
@@ -8741,6 +8741,48 @@ class RelToSqlConverterTest {
.dialect(MssqlSqlDialect.DEFAULT).ok(mssqlExpected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6655";>[CALCITE-6655]
+ * Aggregation of deeply nested window not detected when unparsing</a>.
+ */
+ @Test void testAggregatedDeeplyNested() {
+ // The CASE statement makes the inner sum deep enough to test we're
+ // recursively looking for it
+ final String query =
+ "with cte as\n"
+ + "(select\n"
+ + " case when count(\"salary\") over (partition by \"first_name\") >
0 then\n"
+ + " sum(\"salary\") over (partition by \"first_name\")"
+ + " else 0.0\n"
+ + " end\n"
+ + "as inner_sum from \"employee\"\n"
+ + ")\n"
+ + "select sum(inner_sum) from cte\n";
+
+ // Spark does not support nested aggregations
+ String spark =
+ "SELECT SUM(`INNER_SUM`)\n"
+ + "FROM ("
+ + "SELECT CASE WHEN (COUNT(`salary`) OVER "
+ + "(PARTITION BY `first_name` RANGE BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)) > 0 "
+ + "THEN SUM(`salary`) OVER "
+ + "(PARTITION BY `first_name` RANGE BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) "
+ + "ELSE 0.0000 END `INNER_SUM`\n"
+ + "FROM `foodmart`.`employee`"
+ + ") `t`";
+ sql(query).withSpark().ok(spark);
+
+ // Oracle does support nested aggregations
+ String oracle =
+ "SELECT SUM(CASE WHEN (COUNT(\"salary\") OVER "
+ + "(PARTITION BY \"first_name\" RANGE BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING)) > 0 "
+ + "THEN SUM(\"salary\") OVER "
+ + "(PARTITION BY \"first_name\" RANGE BETWEEN UNBOUNDED PRECEDING AND
UNBOUNDED FOLLOWING) "
+ + "ELSE 0.0000 END)\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query).withOracle().ok(oracle);
+ }
+
/** Fluid interface to run tests. */
static class Sql {
private final CalciteAssert.SchemaSpec schemaSpec;
