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 0fab5b3e6a [CALCITE-7701] Support IGNORE NULLS for
FIRST_VALUE/LAST_VALUE window functions in the enumerable convention
0fab5b3e6a is described below
commit 0fab5b3e6ac44dccb85c966ea4f194db963c4b66
Author: Yu Xu <[email protected]>
AuthorDate: Sun Aug 9 17:43:41 2026 +0800
[CALCITE-7701] Support IGNORE NULLS for FIRST_VALUE/LAST_VALUE window
functions in the enumerable convention
---
.../adapter/enumerable/EnumerableWindow.java | 13 +-
.../calcite/adapter/enumerable/RexImpTable.java | 100 +++++++++++++
.../calcite/adapter/enumerable/WinAggContext.java | 5 +
core/src/test/resources/sql/winagg.iq | 157 +++++++++++++++++++++
4 files changed, 274 insertions(+), 1 deletion(-)
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 1b60c56126..e57d3dd9e8 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
@@ -229,7 +229,14 @@ private static void
sampleOfTheGeneratedWindowedAggregate() {
for (int aggIdx = 0; aggIdx < aggregateCalls.size(); aggIdx++) {
AggregateCall call = aggregateCalls.get(aggIdx);
if (call.ignoreNulls()) {
- throw new UnsupportedOperationException("IGNORE NULLS not
supported");
+ switch (call.getAggregation().getKind()) {
+ case FIRST_VALUE:
+ case LAST_VALUE:
+ // IGNORE NULLS is implemented for these functions below.
+ break;
+ default:
+ throw new UnsupportedOperationException("IGNORE NULLS not
supported");
+ }
}
aggs.add(new AggImpState(aggIdx, call, true, implementorTable));
}
@@ -821,6 +828,10 @@ private void declareAndResetState(final JavaTypeFactory
typeFactory,
@Override public RexWindowExclusion getExclude() {
return exclusion;
}
+
+ @Override public boolean ignoreNulls() {
+ return agg.call.ignoreNulls();
+ }
};
String aggName = "a" + agg.aggIdx;
if (CalciteSystemProperty.DEBUG.value()) {
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
index ed7eac4cde..ae40e33c13 100644
--- a/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
+++ b/core/src/main/java/org/apache/calcite/adapter/enumerable/RexImpTable.java
@@ -35,6 +35,7 @@
import org.apache.calcite.linq4j.tree.OptimizeShuttle;
import org.apache.calcite.linq4j.tree.ParameterExpression;
import org.apache.calcite.linq4j.tree.Primitive;
+import org.apache.calcite.linq4j.tree.Types;
import org.apache.calcite.linq4j.tree.UnsignedType;
import org.apache.calcite.rel.type.RelDataType;
import org.apache.calcite.rel.type.RelDataTypeFactory;
@@ -2491,12 +2492,111 @@ protected FirstLastValueImplementor(SeekType seekType)
{
AggResultContext result) {
WinAggResultContext winResult = (WinAggResultContext) result;
+ final boolean ignoreNulls =
+ info instanceof WinAggContext && ((WinAggContext)
info).ignoreNulls();
+ if (ignoreNulls) {
+ return implementResultIgnoreNulls(info, winResult);
+ }
+
return Expressions.condition(winResult.hasRows(),
winResult.rowTranslator(
winResult.computeIndex(Expressions.constant(0), seekType))
.translate(winResult.rexArguments().get(0), info.returnType()),
getDefaultValue(info.returnType()));
}
+
+ /**
+ * Implements FIRST_VALUE / LAST_VALUE with IGNORE NULLS by scanning the
+ * frame (forward for FIRST_VALUE, backward for LAST_VALUE) and returning
+ * the first non-null argument value, or null if all rows in the frame are
+ * null (or the frame is empty).
+ *
+ * <p>Generated code (for FIRST_VALUE; LAST_VALUE scans backward):
+ * <pre>{@code
+ * BoxType res = null;
+ * if (hasRows) {
+ * for (int seekIdx = startIndex; seekIdx <= endIndex; seekIdx++) {
+ * BoxType seekValue = rowTranslator.translate(arg, boxType);
+ * if (seekValue != null) {
+ * res = seekValue;
+ * break;
+ * }
+ * }
+ * }
+ * return res;
+ * }</pre>
+ */
+ private Expression implementResultIgnoreNulls(AggContext info,
+ WinAggResultContext winResult) {
+ final Type returnType = info.returnType();
+ final RexNode arg = winResult.rexArguments().get(0);
+
+ // Use a boxed type internally so that a NULL comparison is always valid,
+ // even when the (frame-guaranteed non-empty) return type is a primitive.
+ // The surrounding window implementation converts the result back to the
+ // declared return type.
+ final Type boxType = Types.box(returnType);
+
+ final ParameterExpression res =
+ Expressions.parameter(0, boxType,
+ winResult.currentBlock().newName(
+ seekType == SeekType.START ? "first_value" : "last_value"));
+ // res = null
+ winResult.currentBlock().add(Expressions.declare(0, res, NULL_EXPR));
+
+ final ParameterExpression idx =
+ Expressions.parameter(int.class,
+ winResult.currentBlock().newName("seekIdx"));
+
+ // startIndex() and endIndex() are the concrete row indices of the frame
+ // bounds for the current row, already resolved by EnumerableWindow. For
+ // unbounded windows they span the whole partition; for RANGE windows
they
+ // span the peer group(s) included in the frame.
+ // Scan direction: FIRST_VALUE walks from start to end, LAST_VALUE walks
+ // from end back to start.
+ final boolean forward = seekType == SeekType.START;
+ final Expression from =
+ forward ? winResult.startIndex() : winResult.endIndex();
+ final Expression to =
+ forward ? winResult.endIndex() : winResult.startIndex();
+ final Expression condition =
+ forward
+ ? Expressions.lessThanOrEqual(idx, to)
+ : Expressions.greaterThanOrEqual(idx, to);
+ final Expression post =
+ forward
+ ? Expressions.postIncrementAssign(idx)
+ : Expressions.postDecrementAssign(idx);
+
+ // Build the loop body:
+ // BoxType seekValue = rowTranslator.translate(arg, boxType);
+ // if (seekValue != null) {
+ // res = seekValue;
+ // break;
+ // }
+ final BlockBuilder loopBody = winResult.nestBlock();
+ final Expression value =
+ winResult.rowTranslator(idx).translate(arg, boxType);
+ final ParameterExpression valueVar =
+ Expressions.parameter(0, boxType, loopBody.newName("seekValue"));
+ loopBody.add(Expressions.declare(0, valueVar, value));
+ loopBody.add(
+ Expressions.ifThen(
+ Expressions.notEqual(valueVar, NULL_EXPR),
+ Expressions.block(
+ Expressions.statement(Expressions.assign(res, valueVar)),
+ Expressions.break_(null))));
+ winResult.exitBlock();
+ final BlockStatement loopBodyBlock = loopBody.toBlock();
+
+ // Wrap the scan in: if (hasRows) { for (...) { ... } }
+ winResult.currentBlock().add(
+ Expressions.ifThen(winResult.hasRows(),
+ Expressions.for_(
+ Expressions.declare(0, idx, from),
+ condition, post, loopBodyBlock)));
+ return res;
+ }
}
/** Implementor for the {@code FIRST_VALUE} windowed aggregate function. */
diff --git
a/core/src/main/java/org/apache/calcite/adapter/enumerable/WinAggContext.java
b/core/src/main/java/org/apache/calcite/adapter/enumerable/WinAggContext.java
index 28fe9a96f5..a867a813fb 100644
---
a/core/src/main/java/org/apache/calcite/adapter/enumerable/WinAggContext.java
+++
b/core/src/main/java/org/apache/calcite/adapter/enumerable/WinAggContext.java
@@ -26,4 +26,9 @@
public interface WinAggContext extends AggContext {
/** The exclude clause of the group of the window function. */
RexWindowExclusion getExclude();
+
+ /** Whether the window function ignores NULL values (IGNORE NULLS). */
+ default boolean ignoreNulls() {
+ return false;
+ }
}
diff --git a/core/src/test/resources/sql/winagg.iq
b/core/src/test/resources/sql/winagg.iq
index 1f07d4351d..f8195f91aa 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -1323,4 +1323,161 @@ java.sql.SQLException: Error while executing SQL
"select first_value(sal) filter
from emp": FILTER clause is not supported for window function FIRST_VALUE
!error
+# [CALCITE-7701] Support IGNORE NULLS for FIRST_VALUE/LAST_VALUE window
functions in the enumerable convention
+# Verified against Oracle
+# FIRST_VALUE with IGNORE NULLS returns the first non-null value in the frame
+# (or NULL if the frame is empty or all values are null).
+select o, v,
+ first_value(v) ignore nulls over (order by o rows 2 preceding) as fv
+from (values (1, 1), (2, cast(null as integer)), (3, 3),
+ (4, cast(null as integer)), (5, cast(null as integer))) as t(o,
v);
++---+---+----+
+| O | V | FV |
++---+---+----+
+| 1 | 1 | 1 |
+| 2 | | 1 |
+| 3 | 3 | 1 |
+| 4 | | 3 |
+| 5 | | 3 |
++---+---+----+
+(5 rows)
+
+!ok
+
+# LAST_VALUE with IGNORE NULLS returns the last non-null value in the frame.
+select o, v,
+ last_value(v) ignore nulls over (order by o rows 2 preceding) as lv
+from (values (1, 1), (2, cast(null as integer)), (3, 3),
+ (4, cast(null as integer)), (5, cast(null as integer))) as t(o,
v);
++---+---+----+
+| O | V | LV |
++---+---+----+
+| 1 | 1 | 1 |
+| 2 | | 1 |
+| 3 | 3 | 3 |
+| 4 | | 3 |
+| 5 | | 3 |
++---+---+----+
+(5 rows)
+
+!ok
+
+# IGNORE NULLS returns NULL when every row in the frame is null.
+select o, v,
+ first_value(v) ignore nulls
+ over (order by o rows between 1 preceding and 1 preceding) as fv
+from (values (1, cast(null as integer)), (2, cast(null as integer)),
+ (3, 5)) as t(o, v);
++---+---+----+
+| O | V | FV |
++---+---+----+
+| 1 | | |
+| 2 | | |
+| 3 | 5 | |
++---+---+----+
+(3 rows)
+
+!ok
+
+# RESPECT NULLS (the default) still returns the boundary value, including NULL.
+select o, v,
+ first_value(v) respect nulls over (order by o rows 2 preceding) as fv,
+ last_value(v) over (order by o rows 2 preceding) as lv
+from (values (1, 1), (2, cast(null as integer)), (3, 3)) as t(o, v);
++---+---+----+----+
+| O | V | FV | LV |
++---+---+----+----+
+| 1 | 1 | 1 | 1 |
+| 2 | | 1 | |
+| 3 | 3 | 1 | 3 |
++---+---+----+----+
+(3 rows)
+
+!ok
+
+# IGNORE NULLS works with an unbounded ROWS window.
+select o, v,
+ first_value(v) ignore nulls
+ over (order by o rows between unbounded preceding and unbounded following)
as fv,
+ last_value(v) ignore nulls
+ over (order by o rows between unbounded preceding and unbounded following)
as lv
+from (values (1, 1), (2, cast(null as integer)), (3, 3),
+ (4, cast(null as integer)), (5, cast(null as integer))) as t(o,
v);
++---+---+----+----+
+| O | V | FV | LV |
++---+---+----+----+
+| 1 | 1 | 1 | 3 |
+| 2 | | 1 | 3 |
+| 3 | 3 | 1 | 3 |
+| 4 | | 1 | 3 |
+| 5 | | 1 | 3 |
++---+---+----+----+
+(5 rows)
+
+!ok
+
+# IGNORE NULLS works with the default RANGE frame (UNBOUNDED PRECEDING TO
CURRENT ROW).
+select o, v,
+ first_value(v) ignore nulls over (order by o) as fv,
+ last_value(v) ignore nulls over (order by o) as lv
+from (values (1, 1), (2, cast(null as integer)), (3, 3),
+ (4, cast(null as integer)), (5, cast(null as integer))) as t(o,
v);
++---+---+----+----+
+| O | V | FV | LV |
++---+---+----+----+
+| 1 | 1 | 1 | 1 |
+| 2 | | 1 | 1 |
+| 3 | 3 | 1 | 3 |
+| 4 | | 1 | 3 |
+| 5 | | 1 | 3 |
++---+---+----+----+
+(5 rows)
+
+!ok
+
+# IGNORE NULLS works with a symmetric RANGE window.
+select o, v,
+ first_value(v) ignore nulls
+ over (order by o range between 1 preceding and 1 following) as fv,
+ last_value(v) ignore nulls
+ over (order by o range between 1 preceding and 1 following) as lv
+from (values (1, 1), (2, cast(null as integer)), (3, 3),
+ (4, cast(null as integer)), (5, cast(null as integer))) as t(o,
v);
++---+---+----+----+
+| O | V | FV | LV |
++---+---+----+----+
+| 1 | 1 | 1 | 1 |
+| 2 | | 1 | 3 |
+| 3 | 3 | 3 | 3 |
+| 4 | | 3 | 3 |
+| 5 | | | |
++---+---+----+----+
+(5 rows)
+
+!ok
+
+# IGNORE NULLS works with RANGE peers: the current-row peer group is included.
+# The result rows are ordered to make the peer-group ordering deterministic
+# and to match Oracle.
+select o, v,
+ first_value(v) ignore nulls
+ over (order by o range between unbounded preceding and current row) as fv,
+ last_value(v) ignore nulls
+ over (order by o range between unbounded preceding and current row) as lv
+from (values (1, 1), (2, cast(null as integer)), (2, 3),
+ (4, cast(null as integer)), (5, cast(null as integer))) as t(o, v)
+order by o, v;
++---+---+----+----+
+| O | V | FV | LV |
++---+---+----+----+
+| 1 | 1 | 1 | 1 |
+| 2 | 3 | 1 | 3 |
+| 2 | | 1 | 3 |
+| 4 | | 1 | 3 |
+| 5 | | 1 | 3 |
++---+---+----+----+
+(5 rows)
+
+!ok
+
# End winagg.iq
