postgres.git / summary / log / commit / refs
commit b8d82d178758b6e956ee02af512bbd949c335cc2
Author: Tatsuo Ishii <ishii@postgresql.org>
Date: Fri Nov 06 05:22:19 2026 +0000
Fix wrong results of some window functions with IGNORE NULLS.
Window functions such as lead() and lag() could return wrong results
if IGNORE NULLS is specified and the offset argument is equal to
INT_MIN.
WinGetFuncArgInPartition() used abs() to get the absolute value of the
offset argument. If it is INT_MIN (-2147483648), the return value of
abs() is undefined. Typically it returns INT_MIN. However,
WinGetFuncArgInPartition() was not prepared for the case and returns
wrong result. To fix this, use pg_abs_s32() instead of abs() to return
2147483648, instead of -2147483648.
ignorenulls_getfuncarginframe() has the same problem. So this commit
fixes them as well. Note that currently no built in window function
hits the bug because only nth_value() has the offset argument and it
does not accept negative offset. However, WinGetFuncArgInFrame(), the
caller of ignorenulls_getfuncarginframe(), is a public API, we should
prepare the similar case when user defined window functions use it.
Moreover, this commit changes some internal variable data types from
int to int64 in order to prevent an overflow while calculating a row
position in the partition.
Also add regression test cases to window.sql.
Author: jian he <jian.universality@gmail.com>
Reviewed-by: Tatsuo Ishii <ishii@postgresql.org>
Reviewed-by: Kirill Reshke <reshkekirill@gmail.com>
Discussion: https://postgr.es/m/CACJufxH2_4ucJ58_YQPin8E8hf2RRD6S7rA-UK9pSWxdg7YBQw%40mail.gmail.com
Backpatch-through: 19
src/backend/executor/nodeWindowAgg.c | 12 ++++++------
src/test/regress/expected/window.out | 22 ++++++++++++++++++++++
src/test/regress/sql/window.sql | 12 ++++++++++++
3 files changed, 40 insertions(+), 6 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 41443f607ce..8d80f2cc023 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3415,8 +3415,8 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
Datum datum;
int64 abs_pos;
int64 mark_pos;
- int notnull_offset;
- int notnull_relpos;
+ int64 notnull_offset;
+ int64 notnull_relpos;
int forward;
Assert(WindowObjectIsValid(winobj));
@@ -3425,7 +3425,7 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
slot = winstate->temp_slot_1;
datum = (Datum) 0;
notnull_offset = 0;
- notnull_relpos = abs(relpos);
+ notnull_relpos = pg_abs_s32(relpos);
switch (seektype)
{
@@ -3874,8 +3874,8 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
int64 mark_pos;
Datum datum;
bool null_treatment;
- int notnull_offset;
- int notnull_relpos;
+ int64 notnull_offset;
+ int64 notnull_relpos;
int forward;
bool myisout;
bool got_datum;
@@ -3924,7 +3924,7 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
/* Prepare for loop */
notnull_offset = 0;
- notnull_relpos = abs(relpos);
+ notnull_relpos = pg_abs_s32(relpos);
forward = relpos > 0 ? 1 : -1;
myisout = false;
got_datum = false;
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 4b829c0d3dd..112551c350b 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -2143,6 +2143,28 @@ from generate_series(-9223372036854775806, -9223372036854775804) x;
-9223372036854775806 | -9223372036854775806
(3 rows)
+SELECT x,
+ lead(x, -2147483648) over w as respect_nulls,
+ lead(x, -2147483648) ignore nulls over w as ignore_nulls
+from (values (1), (2)) as t(x)
+window w as (order by x);
+ x | respect_nulls | ignore_nulls
+---+---------------+--------------
+ 1 | |
+ 2 | |
+(2 rows)
+
+SELECT x,
+ lag(x, -2147483648) over w as respect_nulls,
+ lag(x, -2147483648) ignore nulls over w as ignore_nulls
+from (values (1), (2)) as t(x)
+window w as (order by x);
+ x | respect_nulls | ignore_nulls
+---+---------------+--------------
+ 1 | |
+ 2 | |
+(2 rows)
+
-- Test in_range for other numeric datatypes
create temp table numerics(
id int,
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 3ea1fae688b..e848557174b 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -587,6 +587,18 @@ from generate_series(9223372036854775804, 9223372036854775806) x;
select x, last_value(x) over (order by x desc range between current row and 5 following)
from generate_series(-9223372036854775806, -9223372036854775804) x;
+SELECT x,
+ lead(x, -2147483648) over w as respect_nulls,
+ lead(x, -2147483648) ignore nulls over w as ignore_nulls
+from (values (1), (2)) as t(x)
+window w as (order by x);
+
+SELECT x,
+ lag(x, -2147483648) over w as respect_nulls,
+ lag(x, -2147483648) ignore nulls over w as ignore_nulls
+from (values (1), (2)) as t(x)
+window w as (order by x);
+
-- Test in_range for other numeric datatypes
create temp table numerics(
[parent: 3c5a4dcaaa8d]