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 f511791d1b [CALCITE-6648] IGNORE NULLS / RESPECT NULLS window function
option can result in a type validation error in SqlToRelConverter
f511791d1b is described below
commit f511791d1b49437635a960f07032f7c2f9a137f7
Author: Yu Xu <[email protected]>
AuthorDate: Sun Aug 9 16:38:26 2026 +0800
[CALCITE-6648] IGNORE NULLS / RESPECT NULLS window function option can
result in a type validation error in SqlToRelConverter
---
.../apache/calcite/test/SqlToRelConverterTest.java | 17 +++++++++++
.../org/apache/calcite/test/SqlValidatorTest.java | 33 ++++++++++++++++++++++
.../apache/calcite/test/SqlToRelConverterTest.xml | 15 ++++++++++
3 files changed, 65 insertions(+)
diff --git
a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
index fe86066be3..d67a982464 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlToRelConverterTest.java
@@ -3250,6 +3250,23 @@ void checkCorrelatedMapSubQuery(boolean expand) {
sql(sql).ok();
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6648";>[CALCITE-6648]
+ * IGNORE NULLS / RESPECT NULLS window function option can result in a type
+ * validation error in SqlToRelConverter</a>.
+ */
+ @Test void testOverNullTreatmentWithOffsetRowsFrame() {
+ final String sql = "select\n"
+ + "first_value(deptno) ignore nulls over "
+ + "(order by empno rows 1 preceding),\n"
+ + "last_value(deptno) respect nulls over "
+ + "(order by empno rows 1 preceding),\n"
+ + "nth_value(deptno, 2) ignore nulls over "
+ + "(order by empno rows 1 preceding)\n"
+ + "from emp";
+ sql(sql).ok();
+ }
+
/**
* Tests that a window with a FOLLOWING bound becomes BETWEEN CURRENT ROW
* AND FOLLOWING.
diff --git a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
index 854a5ce8ff..6390c8ca7e 100644
--- a/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
+++ b/core/src/test/java/org/apache/calcite/test/SqlValidatorTest.java
@@ -4081,6 +4081,39 @@ void testWinPartClause() {
.fails("Cannot specify IGNORE NULLS or RESPECT NULLS following 'ABS'");
}
+ /** Test case for
+ * <a
href="https://issues.apache.org/jira/browse/CALCITE-6648";>[CALCITE-6648]
+ * IGNORE NULLS / RESPECT NULLS window function option can result in a type
+ * validation error in SqlToRelConverter</a>.
+ *
+ * <p>When the input is a non-nullable type, the null treatment option is
+ * specified, and the window frame is ROWS with an offset-based bound (e.g.
+ * {@code n PRECEDING}), the window may be out of bounds, so the derived type
+ * must be nullable. Previously the null treatment wrapper caused the
+ * validation phase to derive a non-nullable type, which then diverged from
+ * the nullable type derived during conversion. */
+ @Test void testWindowNullTreatmentNullableWithOffsetRowsFrame() {
+ // EMPNO is non-nullable; with an offset-based ROWS frame the result may be
+ // null, so IGNORE NULLS / RESPECT NULLS must not force a non-nullable
type.
+ winSql("select first_value(empno) ignore nulls over "
+ + "(order by empno rows 1 preceding)\n"
+ + "from emp")
+ .type("RecordType(INTEGER EXPR$0) NOT NULL");
+
+ winSql("select last_value(empno) respect nulls over "
+ + "(order by empno rows 1 preceding)\n"
+ + "from emp")
+ .type("RecordType(INTEGER EXPR$0) NOT NULL");
+
+ // A full-partition frame is always non-empty, so the result stays
+ // non-nullable even with null treatment.
+ winSql("select first_value(empno) ignore nulls over "
+ + "(order by empno rows between unbounded preceding "
+ + "and unbounded following)\n"
+ + "from emp")
+ .type("RecordType(INTEGER NOT NULL EXPR$0) NOT NULL");
+ }
+
/** Test case for
* <a
href="https://issues.apache.org/jira/browse/CALCITE-1954";>[CALCITE-1954]
* Column from outer join should be null, whether or not it is aliased</a>.
*/
diff --git
a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
index 0d97150eb6..10171218a5 100644
--- a/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
+++ b/core/src/test/resources/org/apache/calcite/test/SqlToRelConverterTest.xml
@@ -7069,6 +7069,21 @@ window w as (order by empno)]]>
<![CDATA[
LogicalProject(EXPR$0=[LEAD($7, 1) OVER (ORDER BY $0)], EXPR$1=[LEAD($7, 2)
IGNORE NULLS OVER (ORDER BY $0)], EXPR$2=[LEAD($7, 3) OVER (ORDER BY $0)],
EXPR$3=[LEAD($7, 1) OVER (ORDER BY $0)], EXPR$4=[LAG($7, 2) IGNORE NULLS OVER
(ORDER BY $0)], EXPR$5=[LAG($7, 2) OVER (ORDER BY $0)], EXPR$6=[FIRST_VALUE($7)
OVER (ORDER BY $0)], EXPR$7=[FIRST_VALUE($7) IGNORE NULLS OVER (ORDER BY $0)],
EXPR$8=[FIRST_VALUE($7) OVER (ORDER BY $0)], EXPR$9=[LAST_VALUE($7) OVER (ORDER
BY $0)], EXPR$10=[LAST_V [...]
LogicalTableScan(table=[[CATALOG, SALES, EMP]])
+]]>
+ </Resource>
+ </TestCase>
+ <TestCase name="testOverNullTreatmentWithOffsetRowsFrame">
+ <Resource name="sql">
+ <![CDATA[select
+first_value(deptno) ignore nulls over (order by empno rows 1 preceding),
+last_value(deptno) respect nulls over (order by empno rows 1 preceding),
+nth_value(deptno, 2) ignore nulls over (order by empno rows 1 preceding)
+from emp]]>
+ </Resource>
+ <Resource name="plan">
+ <![CDATA[
+LogicalProject(EXPR$0=[FIRST_VALUE($7) IGNORE NULLS OVER (ORDER BY $0 ROWS 1
PRECEDING)], EXPR$1=[LAST_VALUE($7) OVER (ORDER BY $0 ROWS 1 PRECEDING)],
EXPR$2=[NTH_VALUE($7, 2) IGNORE NULLS OVER (ORDER BY $0 ROWS 1 PRECEDING)])
+ LogicalTableScan(table=[[CATALOG, SALES, EMP]])
]]>
</Resource>
</TestCase>
