Hi.
SELECT v,
lead(v, -2147483648) OVER w AS respect_nulls,
lead(v, -2147483648) IGNORE NULLS OVER w AS ignore_nulls
FROM (VALUES (1), (2)) AS t(v)
WINDOW w AS (ORDER BY v);
should return all NULL values for column ignore_nulls.
The fix is to replace the abs function in nodeWindowAgg.c with pg_abs_s32.
--
jian
https://www.enterprisedb.com/
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index b86dcbba055..d85dd4978dd 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)
{
@@ -3859,8 +3859,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;
@@ -3909,7 +3909,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 c0bde1c5eec..17faafb7188 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -2143,6 +2143,17 @@ from generate_series(-9223372036854775806, -9223372036854775804) x;
-9223372036854775806 | -9223372036854775806
(3 rows)
+SELECT v,
+ lead(v, -2147483648) OVER w AS respect_nulls,
+ lead(v, -2147483648) IGNORE NULLS OVER w AS ignore_nulls
+FROM (VALUES (1), (2)) AS t(v)
+WINDOW w AS (ORDER BY v);
+ v | 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 8e6f92d94c7..e93b72939cd 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -587,6 +587,12 @@ 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 v,
+ lead(v, -2147483648) OVER w AS respect_nulls,
+ lead(v, -2147483648) IGNORE NULLS OVER w AS ignore_nulls
+FROM (VALUES (1), (2)) AS t(v)
+WINDOW w AS (ORDER BY v);
+
-- Test in_range for other numeric datatypes
create temp table numerics(
