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 4681d4d16a [CALCITE-7864] RANGE window frames should handle NULL ORDER
BY keys
4681d4d16a is described below
commit 4681d4d16af74e9da5f2b5105e9e07b1836ff000
Author: Letian Jiang <[email protected]>
AuthorDate: Sat Oct 17 17:41:46 2026 +0800
[CALCITE-7864] RANGE window frames should handle NULL ORDER BY keys
Handle nullable ORDER BY keys in offset RANGE boundaries and add 28
regression cases. Verify expected results against PostgreSQL 14.24.
---
.../adapter/enumerable/EnumerableWindow.java | 11 ++++
.../adapter/enumerable/EnumerableWindowTest.java | 66 ++++++++++++++++++++++
2 files changed, 77 insertions(+)
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
index e57d3dd9e8..8602364bff 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/EnumerableWindow.java
@@ -1004,6 +1004,7 @@ private static Expression
translateBound(RexToLixTranslator translator,
Expression val =
translator.translate(new RexInputRef(orderKey, keyType),
desiredKeyType);
if (!bound.isCurrentRow()) {
+ final Expression key = val;
RexNode node = bound.getOffset();
Expression offs = translator.translate(node);
// TODO: support date + interval somehow
@@ -1012,6 +1013,16 @@ private static Expression
translateBound(RexToLixTranslator translator,
} else {
val = Expressions.subtract(val, offs);
}
+ if (keyType.isNullable()) {
+ // A null key's finite RANGE bounds are the edges of its peer group.
+ // Keep the offset arithmetic inside the non-null branch, and box its
+ // result so that the conditional expression does not unbox null.
+ final Type boundType = Primitive.box(val.getType());
+ val =
+ Expressions.condition(Expressions.equal(key,
Expressions.constant(null)),
+ Expressions.constant(null, boundType),
+ EnumUtils.convert(val, boundType));
+ }
}
return Expressions.call(
(lower
diff --git
a/core/src/test/java/org/apache/calcite/adapter/enumerable/EnumerableWindowTest.java
b/core/src/test/java/org/apache/calcite/adapter/enumerable/EnumerableWindowTest.java
index 2af3dd3730..592f8aa09f 100644
---
a/core/src/test/java/org/apache/calcite/adapter/enumerable/EnumerableWindowTest.java
+++
b/core/src/test/java/org/apache/calcite/adapter/enumerable/EnumerableWindowTest.java
@@ -31,10 +31,15 @@
import org.apache.calcite.sql.type.BasicSqlType;
import org.apache.calcite.sql.type.SqlTypeFactoryImpl;
import org.apache.calcite.sql.type.SqlTypeName;
+import org.apache.calcite.test.CalciteAssert;
import org.apache.calcite.test.MockRelOptPlanner;
import org.junit.jupiter.api.Test;
+import org.junit.jupiter.params.ParameterizedTest;
+import org.junit.jupiter.params.provider.Arguments;
+import org.junit.jupiter.params.provider.MethodSource;
+import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
@@ -47,6 +52,67 @@
* Test for {@link org.apache.calcite.adapter.enumerable.EnumerableWindow}.
*/
public class EnumerableWindowTest {
+ @ParameterizedTest
+ @MethodSource("rangeNullableOrderKeyCases")
+ void testRangeNullableOrderKey(String type, String nulls, String frame,
+ String sum1, String sum2, String sum4, String sumNull) {
+ CalciteAssert.that()
+ .query("select k, sum(v) over (order by k nulls " + nulls
+ + " range between " + frame + ") as s\n"
+ + "from (values (cast(1 as " + type + "), 10), (2, 20), (4, 40),\n"
+ + " (cast(null as " + type + "), 100), (null, 200)) as t(k, v)")
+ .explainContains("EnumerableWindow")
+ .returnsUnordered("K=1; S=" + sum1,
+ "K=2; S=" + sum2,
+ "K=4; S=" + sum4,
+ "K=null; S=" + sumNull,
+ "K=null; S=" + sumNull);
+ }
+
+ private static List<Arguments> rangeNullableOrderKeyCases() {
+ // Expected results for all 28 cases were verified on PostgreSQL 14.24
+ // by executing the same SQL and comparing all five result rows,
+ // including duplicate null-key rows, without regard to row order.
+ final List<Arguments> cases = new ArrayList<>();
+ for (String type : new String[] {"integer", "bigint"}) {
+ for (String nulls : new String[] {"first", "last"}) {
+ // Cover PRECEDING and FOLLOWING for both the lower and upper bounds.
+ cases.add(
+ Arguments.of(type, nulls, "1 preceding and current row",
+ "10", "30", "40", "300"));
+ cases.add(
+ Arguments.of(type, nulls, "current row and 1 following",
+ "30", "20", "40", "300"));
+ cases.add(
+ Arguments.of(type, nulls, "1 preceding and 1 following",
+ "30", "30", "40", "300"));
+ cases.add(
+ Arguments.of(type, nulls, "2 preceding and 1 preceding",
+ "null", "10", "20", "300"));
+ cases.add(
+ Arguments.of(type, nulls, "1 following and 2 following",
+ "20", "40", "null", "300"));
+ // An unbounded end can include non-null keys in a null key's frame.
+ if (nulls.equals("first")) {
+ cases.add(
+ Arguments.of(type, nulls, "unbounded preceding and 1 following",
+ "330", "330", "370", "300"));
+ cases.add(
+ Arguments.of(type, nulls, "1 preceding and unbounded following",
+ "70", "70", "40", "370"));
+ } else {
+ cases.add(
+ Arguments.of(type, nulls, "unbounded preceding and 1 following",
+ "30", "30", "70", "370"));
+ cases.add(
+ Arguments.of(type, nulls, "1 preceding and unbounded following",
+ "370", "370", "340", "300"));
+ }
+ }
+ }
+ return cases;
+ }
+
@Test void testCopyWithConstants() {
final MockRelOptPlanner planner = new MockRelOptPlanner(Contexts.empty());
final RelDataTypeFactory typeFactory =
