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 be78e311dc [CALCITE-7835] Optimize away LEAD/LAG with a value of 0
be78e311dc is described below
commit be78e311dc7194099f26f723fc5347d2e247d3b9
Author: Mihai Budiu <[email protected]>
AuthorDate: Sun Oct 4 20:36:20 2026 -0700
[CALCITE-7835] Optimize away LEAD/LAG with a value of 0
Signed-off-by: Mihai Budiu <[email protected]>
---
.../java/org/apache/calcite/rex/RexSimplify.java | 2 +-
.../java/org/apache/calcite/tools/RelBuilder.java | 24 ++++++++++++
core/src/test/resources/sql/winagg.iq | 43 ++++++++++++++++++++++
3 files changed, 68 insertions(+), 1 deletion(-)
diff --git a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
index 07ee355597..65461ddab6 100644
--- a/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
+++ b/core/src/main/java/org/apache/calcite/rex/RexSimplify.java
@@ -439,7 +439,7 @@ private static int findLiteralIndex(List<RexNode> operands,
BigDecimal value) {
}
/** Check whether the operand is a BigDecimal literal of the specified
value. */
- private static boolean checkLiteralValue(RexNode operand, BigDecimal value) {
+ public static boolean checkLiteralValue(RexNode operand, BigDecimal value) {
if (!operand.isA(SqlKind.LITERAL)) {
return false;
}
diff --git a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
index 97d2e3080d..c035c807da 100644
--- a/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
+++ b/core/src/main/java/org/apache/calcite/tools/RelBuilder.java
@@ -5142,6 +5142,10 @@ private OverCall
orderBy_(ImmutableList<RexFieldCollation> sortKeys) {
}
};
final RelDataType type = op.inferReturnType(bind);
+ final RexNode currentRowValue = simplifyZeroOffset(type);
+ if (currentRowValue != null) {
+ return aliasMaybe(currentRowValue, alias);
+ }
final ImmutableList<RexNode> newPartitionKeys =
simplifyPartitionKeys(partitionKeys);
final ImmutableList<RexFieldCollation> newSortKeys =
@@ -5209,6 +5213,26 @@ private ImmutableList<RexFieldCollation>
simplifySortKeys(
}
return newSortKeys.build();
}
+
+ /** Replaces {@code LAG(x, 0)} and {@code LEAD(x, 0)}, with or without a
+ * default, by {@code x}. Returns null if the call cannot be replaced. */
+ private @Nullable RexNode simplifyZeroOffset(RelDataType type) {
+ if (op.getKind() != SqlKind.LAG && op.getKind() != SqlKind.LEAD) {
+ return null;
+ }
+ // Check if the offset is 0; (the default offset is 1)
+ if (operands.size() < 2
+ || !RexSimplify.checkLiteralValue(operands.get(1), BigDecimal.ZERO))
{
+ return null;
+ }
+ final RexNode value = operands.get(0);
+ // The result must be nullable
+ if (!type.isNullable() && value.getType().isNullable()) {
+ return null;
+ }
+ // Preserve the type of the call
+ return getRexBuilder().ensureType(pos, type, value, false);
+ }
}
/** Collects the extra expressions needed for {@link #aggregate}.
diff --git a/core/src/test/resources/sql/winagg.iq
b/core/src/test/resources/sql/winagg.iq
index f8195f91aa..768d73fa16 100644
--- a/core/src/test/resources/sql/winagg.iq
+++ b/core/src/test/resources/sql/winagg.iq
@@ -1480,4 +1480,47 @@ order by o, v;
!ok
+# [CALCITE-7835] Optimize away LEAD/LAG with a value of 0
+# LAG and LEAD with offset 0 return the value of the current row.
+# Verified on Postgres.
+select o, v,
+ lag(v, 0) over (order by o) as l,
+ lag(o, 0) over (order by o) as lo,
+ lead(o, 0, 100) over (order by o) as ld
+from (values (1, 10), (2, cast(null as integer)), (3, 30)) as t(o, v);
++---+----+----+----+----+
+| O | V | L | LO | LD |
++---+----+----+----+----+
+| 1 | 10 | 10 | 1 | 1 |
+| 2 | | | 2 | 2 |
+| 3 | 30 | 30 | 3 | 3 |
++---+----+----+----+----+
+(3 rows)
+
+!ok
+O INTEGER(10) NOT NULL
+V INTEGER(10)
+L INTEGER(10)
+LO INTEGER(10)
+LD INTEGER(10) NOT NULL
+!type
+EnumerableCalc(expr#0..1=[{inputs}], expr#2=[CAST($t0):INTEGER],
proj#0..1=[{exprs}], L=[$t1], LO=[$t2], LD=[$t0])
+ EnumerableValues(tuples=[[{ 1, 10 }, { 2, null }, { 3, 30 }]])
+!plan
+
+# With offset 0, IGNORE NULLS has no effect: SQL:2016 returns the value of
+# the current row, even if it is NULL.
+select o, v, lag(v, 0) ignore nulls over (order by o) as l
+from (values (1, 10), (2, cast(null as integer)), (3, 30)) as t(o, v);
++---+----+----+
+| O | V | L |
++---+----+----+
+| 1 | 10 | 10 |
+| 2 | | |
+| 3 | 30 | 30 |
++---+----+----+
+(3 rows)
+
+!ok
+
# End winagg.iq
