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 644e29f867 [CALCITE-6569] RelToSqlConverter missing IGNORE NULLS for
window function
644e29f867 is described below
commit 644e29f867dd1d7a8117fecf5f30c0e1fd3a7091
Author: Jiajun Xie <[email protected]>
AuthorDate: Tue Sep 10 15:14:23 2024 +0800
[CALCITE-6569] RelToSqlConverter missing IGNORE NULLS for window function
---
.../apache/calcite/rel/rel2sql/SqlImplementor.java | 16 +++++++++----
.../calcite/rel/rel2sql/RelToSqlConverterTest.java | 26 ++++++++++++++++++++++
2 files changed, 38 insertions(+), 4 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 ca6e357469..6e52d841e4 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
@@ -1045,7 +1045,10 @@ public abstract class SqlImplementor {
};
RexCall aggCall = (RexCall) winAggCall.accept(replaceConstants);
List<SqlNode> operands = toSql(null, aggCall.operands);
- rexOvers.add(createOverCall(aggFunction, operands, sqlWindow,
winAggCall.distinct));
+ final SqlCall overCall =
+ createOverCall(aggFunction, operands, sqlWindow,
+ winAggCall.distinct, winAggCall.ignoreNulls);
+ rexOvers.add(overCall);
}
return rexOvers;
}
@@ -1092,15 +1095,16 @@ public abstract class SqlImplementor {
orderList, isRows, lowerBound, upperBound, allowPartial,
exclude, POS);
final List<SqlNode> nodeList = toSql(program, rexOver.getOperands());
- return createOverCall(sqlAggregateFunction, nodeList, sqlWindow,
rexOver.isDistinct());
+ return createOverCall(sqlAggregateFunction, nodeList, sqlWindow,
+ rexOver.isDistinct(), rexOver.ignoreNulls());
}
private static SqlCall createOverCall(SqlAggFunction op, List<SqlNode>
operands,
- SqlWindow window, boolean isDistinct) {
+ SqlWindow window, boolean isDistinct, boolean ignoreNulls) {
if (op instanceof SqlSumEmptyIsZeroAggFunction) {
// Rewrite "SUM0(x) OVER w" to "COALESCE(SUM(x) OVER w, 0)"
final SqlCall node =
- createOverCall(SqlStdOperatorTable.SUM, operands, window,
isDistinct);
+ createOverCall(SqlStdOperatorTable.SUM, operands, window,
isDistinct, ignoreNulls);
return SqlStdOperatorTable.COALESCE.createCall(POS, node, ZERO);
}
SqlCall aggFunctionCall;
@@ -1110,6 +1114,10 @@ public abstract class SqlImplementor {
} else {
aggFunctionCall = op.createCall(POS, operands);
}
+ if (ignoreNulls) {
+ aggFunctionCall =
+ SqlStdOperatorTable.IGNORE_NULLS.createCall(null, POS,
aggFunctionCall);
+ }
return SqlStdOperatorTable.OVER.createCall(POS, aggFunctionCall,
window);
}
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 d855bb96c3..6eba6a16cc 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
@@ -4584,6 +4584,32 @@ class RelToSqlConverterTest {
sql(query).ok(expected);
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6569";>[CALCITE-6569]
+ * RelToSqlConverter support IGNORE NULLS for window functions</a>. */
+ @Test void testIgnoreNullsWindow() {
+ final String query0 = "SELECT LEAD(\"employee_id\", 2) IGNORE NULLS "
+ + "OVER (ORDER BY \"hire_date\") FROM \"employee\"";
+ final String expected0 = "SELECT LEAD(\"employee_id\", 2) IGNORE NULLS
OVER (ORDER BY "
+ + "\"hire_date\")\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query0).ok(expected0);
+
+ final String query1 = "SELECT "
+ + "LAG(\"employee_id\", 1) IGNORE NULLS OVER (ORDER BY \"hire_date\"),"
+ + "FIRST_VALUE(\"employee_id\") IGNORE NULLS OVER (ORDER BY
\"hire_date\"),"
+ + "LAST_VALUE(\"employee_id\") IGNORE NULLS OVER (ORDER BY
\"hire_date\")"
+ + "FROM \"employee\"";
+ final String expected1 = "SELECT "
+ + "LAG(\"employee_id\", 1) IGNORE NULLS OVER (ORDER BY \"hire_date\"),
"
+ + "FIRST_VALUE(\"employee_id\") IGNORE NULLS OVER (ORDER BY
\"hire_date\""
+ + " RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), "
+ + "LAST_VALUE(\"employee_id\") IGNORE NULLS OVER (ORDER BY
\"hire_date\""
+ + " RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)\n"
+ + "FROM \"foodmart\".\"employee\"";
+ sql(query1).ok(expected1);
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-3112";>[CALCITE-3112]
* Support Window in RelToSqlConverter</a>. */
