pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedfirst_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
17+ messages / 5 participants
[nested] [flat]
Commits from this thread:
2026-11-05 69b20fdd20 50f4c42722 Fix infinite loop in first_value()/nth_value() with IGNORE NULLS.
* first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-10-24 04:34 BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
0 siblings, 1 reply; 17+ messages in thread
From: BHUVAN CHILUKUTI @ 2026-10-24 04:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hello,
A window function call with IGNORE NULLS never terminates, and the backend
does not respond to query cancellation, when all of the following hold:
*
the function scans the frame forward: first_value() or nth_value();
*
the window has no ORDER BY;
*
the frame end is UNBOUNDED FOLLOWING, or a ROWS offset past the end of
the partition (e.g. 1000000000000 FOLLOWING);
*
the exclusion is EXCLUDE GROUP, or EXCLUDE TIES when the current row
does not supply the requested non-null value.
The backend spins at 100% CPU and never reaches a CHECK_FOR_INTERRUPTS(), so
statement_timeout, pg_cancel_backend(), pg_terminate_backend() and
"pg_ctl stop -m fast" do not stop it; recovery needs SIGKILL, which restarts
all sessions. The wedged backend also does not absorb a ProcSignalBarrier, so
an unrelated "ALTER DATABASE <other_db> SET TABLESPACE" (normally ~0.2 s)
blocks for as long as the query runs. Any role that can run a SELECT can
trigger it; no table and no special privilege are required.
PostgreSQL version
------------------
Reproduced on REL_19_STABLE (tip 015f4c9fc3, and also fc189eb750) and on
master (20devel, 126976a8ff). No commit touches
src/backend/executor/nodeWindowAgg.c between these revisions. PostgreSQL 18
and earlier are not affected (IGNORE NULLS is new in 19).
Platform: x86_64 Linux (Ubuntu 24.04, gcc 13.3.0), build configured with
--enable-cassert --enable-debug. The defect is in executor control flow and
is not expected to depend on the platform.
Minimal reproduction
---------------------
No tables or settings are needed. Run on a DISPOSABLE cluster: the statement
does not return and cannot be cancelled; stop it with SIGKILL on the backend.
SELECT first_value(x) IGNORE NULLS OVER (
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP
) FROM (VALUES (1)) v(x);
Expected: one row containing NULL. With no ORDER BY every row is a peer of the
current row (ref/select.sgml: "all rows are peers if there is no ORDER BY"),
EXCLUDE GROUP removes the current row and its peers, so the frame is empty,
and first_value() over an empty frame is NULL. The same query with RESPECT
NULLS returns NULL immediately, as does the aggregate-path equivalent
(array_agg(x) FILTER (WHERE x IS NOT NULL) OVER w)[1].
Actual: the statement never completes. In a debugger on this one-row query,
the loop position advances by ~1.8e8 per second (backend in state R), where
any valid position is 0 -- i.e. an unbounded loop, not slow execution.
Other shapes that hang the same way (each in its own session):
-- EXCLUDE TIES, current row NULL (expected: NULL)
SELECT first_value(x) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING EXCLUDE TIES) FROM (VALUES (NULL::int)) v(x);
-- nth_value, second non-null never exists (expected: NULL)
SELECT nth_value(x,2) IGNORE NULLS OVER (ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING EXCLUDE TIES)
FROM (VALUES (1),(2)) v(x) WHERE x = 1;
-- huge finite ROWS offset past the partition (expected: NULL)
SELECT first_value(x) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING
AND 1000000000000 FOLLOWING EXCLUDE GROUP) FROM (VALUES (1)) v(x);
-- RANGE mode, same conditions (expected: NULL)
SELECT first_value(x) IGNORE NULLS OVER (RANGE BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING EXCLUDE GROUP) FROM (VALUES (1)) v(x);
These related forms terminate correctly: the same queries with an ORDER BY in
the window; a frame ending at CURRENT ROW or a small finite offset;
last_value() (which scans backward); and RESPECT NULLS.
Analysis
--------
ignorenulls_getfuncarginframe() (nodeWindowAgg.c) walks the frame forward,
calling row_is_in_frame(winobj, abs_pos, slot, true) for each position and
skipping to the next on a 0 result without fetching the row. But
row_is_in_frame()'s header states the caller must have already determined the
row is in the partition. For a frame ending at UNBOUNDED FOLLOWING it has no
frame-end test, and its exclusion branch returns 0 ("excluded") for every
position other than the current row when ordNumCols == 0 (EXCLUDE GROUP, and
EXCLUDE TIES for pos != currentpos). So once abs_pos passes the last row,
every call returns 0; window_gettupleslot() -- the only place that detects
pos >= spooled_rows -- is never called, and abs_pos (an int64) just keeps
growing. No CHECK_FOR_INTERRUPTS() runs on this path.
With an ORDER BY, positions past the partition are not peers, row_is_in_frame()
returns 1, window_gettupleslot() fails, and the loop ends -- which is why only
the no-ORDER-BY case hangs. (This is distinct from the separately reported
abs() overflow in the same function: that needs a large negative offset to
overflow an int, whereas here the offset is 0 and the counter is int64.)
Fix
________________________________
The attached patch makes ignorenulls_getfuncarginframe() establish partition
membership before calling row_is_in_frame(), using the same test
window_gettupleslot() uses (spool_tuples() + spooled_rows), and adds a
CHECK_FOR_INTERRUPTS() to the loop (which can skip many excluded or
known-NULL rows without fetching any tuple). It also adds regression queries
to window.sql. A lower-level alternative would be to make row_is_in_frame()
itself report positions past the partition end, but that would change its
documented precondition and affect every caller, so the caller-side fix seems
preferable; I'm happy to redo it that way if you disagree.
With the patch: the frame-scan queries above return at once with the expected
values; the core regression suite passes (239/239), isolation passes
(134/134), and the relevant TAP suites pass (psql including the query-cancel
test, plus pg_ctl and test_misc). The new window.sql expected output was
cross-checked against the aggregate-path results on an unpatched server, and a
24,000-query randomized comparison of IGNORE NULLS against that oracle found
no differences. The patch applies to both current master and REL_19_STABLE.
Thanks,
Bhuvan Chilukuti
Attachments:
AUDIT-F2_REGRESSION_TEST.patch (application/octet-stream, 6.9K) download
AUDIT-F2: fix endless loop in IGNORE NULLS frame scan, with regression test
===========================================================================
Base: PostgreSQL master 126976a8ffcfa47f40d3df09243ebf4606001912 (20devel,
2026-10-23). Also applies unchanged to REL_19_STABLE fc189eb750
("git apply --check" OK). Apply with: git apply AUDIT-F2_REGRESSION_TEST.patch
(git apply skips this header).
Status: proposed by this audit. NOT reviewed or accepted upstream.
Files changed
-------------
src/backend/executor/nodeWindowAgg.c (+15)
ignorenulls_getfuncarginframe() walks the frame row by row looking for the
n-th non-null value. It calls row_is_in_frame() on each position without
first checking that the position is inside the partition, although
row_is_in_frame() requires its caller to have done so ("The caller must
have already determined that the row is in the partition"). When the
frame ends at UNBOUNDED FOLLOWING, row_is_in_frame() has no end-of-frame
test, and with EXCLUDE GROUP (or EXCLUDE TIES, for rows other than the
current one) and no ORDER BY it returns 0 ("excluded") for every
position, including positions past the last row. The loop then never
calls window_gettupleslot(), which is what would have noticed the end of
the partition, and it advances abs_pos forever.
The fix checks the partition end before calling row_is_in_frame(), the
same way window_gettupleslot() does (spool_tuples(), then compare with
spooled_rows). It also adds CHECK_FOR_INTERRUPTS() at the top of the
loop: the loop can pass over many excluded or known-NULL rows without
fetching any tuple, and window_gettupleslot() is where the interrupt check
normally happens.
src/test/regress/sql/window.sql (+20)
src/test/regress/expected/window.out (+40)
Three queries, added next to the existing IGNORE NULLS tests:
1. first_value/nth_value IGNORE NULLS, ROWS ... UNBOUNDED FOLLOWING
EXCLUDE GROUP, no ORDER BY -> empty frame, all NULL
2. same functions, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
EXCLUDE TIES, no ORDER BY -> frame = current row only
3. first_value IGNORE NULLS with PARTITION BY and EXCLUDE TIES
Without the C change, the first query never finishes (pg_regress was
killed by a 180 s watchdog, still running). With it, the output is
identical to the same frames computed through the aggregate path,
(array_agg(x) FILTER (WHERE x IS NOT NULL) OVER w)[n], on the unpatched
server. The expected output was taken from the patched run only after
that independent check. The expected-file change is a pure insertion.
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index b86dcbba05..25cf3c15f7 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3466,6 +3466,9 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
int inframe;
int v;
+ /* we may skip many rows without fetching any of them */
+ CHECK_FOR_INTERRUPTS();
+
/*
* Check apparent out of frame case. We need to do this because we
* may not call window_gettupleslot before row_is_in_frame, which
@@ -3474,6 +3477,18 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
if (abs_pos < 0)
goto out_of_frame;
+ /*
+ * Likewise, stop at the end of the partition, which row_is_in_frame
+ * expects its caller to have checked. It cannot always detect that
+ * itself: for a frame ending at UNBOUNDED FOLLOWING with EXCLUDE
+ * GROUP or EXCLUDE TIES and no ORDER BY, it reports every row other
+ * than the current one as excluded, wherever it is, so we would
+ * never reach the end of the loop.
+ */
+ spool_tuples(winstate, abs_pos);
+ if (abs_pos >= winstate->spooled_rows)
+ goto out_of_frame;
+
/* check whether row is in frame */
inframe = row_is_in_frame(winobj, abs_pos, slot, true);
if (inframe == -1)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index c0bde1c5ee..4b829c0d3d 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -6188,6 +6188,46 @@ WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
5 | 4
(5 rows)
+-- EXCLUDE GROUP/TIES without ORDER BY: every row is a peer of the current
+-- row, so the IGNORE NULLS scan must stop at the end of the partition
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP)
+ORDER BY x;
+ x | first_value | nth_value
+---+-------------+-----------
+ 1 | |
+ 3 | |
+ | |
+(3 rows)
+
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY x;
+ x | first_value | nth_value
+---+-------------+-----------
+ 1 | 1 |
+ 3 | 3 |
+ | |
+(3 rows)
+
+SELECT p, x,
+ first_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1, 1), (1, NULL), (2, NULL)) v(p, x)
+WINDOW w AS (PARTITION BY p ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY p, x;
+ p | x | first_value
+---+---+-------------
+ 1 | 1 | 1
+ 1 | |
+ 2 | |
+(3 rows)
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 8e6f92d94c..3ea1fae688 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -2271,6 +2271,26 @@ SELECT x,
FROM generate_series(1,5) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
+-- EXCLUDE GROUP/TIES without ORDER BY: every row is a peer of the current
+-- row, so the IGNORE NULLS scan must stop at the end of the partition
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP)
+ORDER BY x;
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY x;
+SELECT p, x,
+ first_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1, 1), (1, NULL), (2, NULL)) v(p, x)
+WINDOW w AS (PARTITION BY p ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY p, x;
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-10-24 07:51 Tatsuo Ishii <ishii@postgresql.org>
parent: BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
0 siblings, 1 reply; 17+ messages in thread
From: Tatsuo Ishii @ 2026-10-24 07:51 UTC (permalink / raw)
To: c.bhuvan@iitg.ac.in; +Cc: pgsql-bugs@lists.postgresql.org
> Hello,
> A window function call with IGNORE NULLS never terminates, and the backend
> does not respond to query cancellation, when all of the following hold:
>
> *
> the function scans the frame forward: first_value() or nth_value();
> *
> the window has no ORDER BY;
> *
> the frame end is UNBOUNDED FOLLOWING, or a ROWS offset past the end of
> the partition (e.g. 1000000000000 FOLLOWING);
> *
> the exclusion is EXCLUDE GROUP, or EXCLUDE TIES when the current row
> does not supply the requested non-null value.
>
> The backend spins at 100% CPU and never reaches a CHECK_FOR_INTERRUPTS(), so
> statement_timeout, pg_cancel_backend(), pg_terminate_backend() and
> "pg_ctl stop -m fast" do not stop it; recovery needs SIGKILL, which restarts
> all sessions. The wedged backend also does not absorb a ProcSignalBarrier, so
> an unrelated "ALTER DATABASE <other_db> SET TABLESPACE" (normally ~0.2 s)
> blocks for as long as the query runs. Any role that can run a SELECT can
> trigger it; no table and no special privilege are required.
>
> PostgreSQL version
> ------------------
> Reproduced on REL_19_STABLE (tip 015f4c9fc3, and also fc189eb750) and on
> master (20devel, 126976a8ff). No commit touches
> src/backend/executor/nodeWindowAgg.c between these revisions. PostgreSQL 18
> and earlier are not affected (IGNORE NULLS is new in 19).
> Platform: x86_64 Linux (Ubuntu 24.04, gcc 13.3.0), build configured with
> --enable-cassert --enable-debug. The defect is in executor control flow and
> is not expected to depend on the platform.
>
> Minimal reproduction
> ---------------------
> No tables or settings are needed. Run on a DISPOSABLE cluster: the statement
> does not return and cannot be cancelled; stop it with SIGKILL on the backend.
> SELECT first_value(x) IGNORE NULLS OVER (
> ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP
> ) FROM (VALUES (1)) v(x);
> Expected: one row containing NULL. With no ORDER BY every row is a peer of the
> current row (ref/select.sgml: "all rows are peers if there is no ORDER BY"),
> EXCLUDE GROUP removes the current row and its peers, so the frame is empty,
> and first_value() over an empty frame is NULL. The same query with RESPECT
> NULLS returns NULL immediately, as does the aggregate-path equivalent
> (array_agg(x) FILTER (WHERE x IS NOT NULL) OVER w)[1].
> Actual: the statement never completes. In a debugger on this one-row query,
> the loop position advances by ~1.8e8 per second (backend in state R), where
> any valid position is 0 -- i.e. an unbounded loop, not slow execution.
> Other shapes that hang the same way (each in its own session):
> -- EXCLUDE TIES, current row NULL (expected: NULL)
> SELECT first_value(x) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING
> AND UNBOUNDED FOLLOWING EXCLUDE TIES) FROM (VALUES (NULL::int)) v(x);
> -- nth_value, second non-null never exists (expected: NULL)
> SELECT nth_value(x,2) IGNORE NULLS OVER (ROWS BETWEEN CURRENT ROW
> AND UNBOUNDED FOLLOWING EXCLUDE TIES)
> FROM (VALUES (1),(2)) v(x) WHERE x = 1;
> -- huge finite ROWS offset past the partition (expected: NULL)
> SELECT first_value(x) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING
> AND 1000000000000 FOLLOWING EXCLUDE GROUP) FROM (VALUES (1)) v(x);
> -- RANGE mode, same conditions (expected: NULL)
> SELECT first_value(x) IGNORE NULLS OVER (RANGE BETWEEN UNBOUNDED PRECEDING
> AND UNBOUNDED FOLLOWING EXCLUDE GROUP) FROM (VALUES (1)) v(x);
> These related forms terminate correctly: the same queries with an ORDER BY in
> the window; a frame ending at CURRENT ROW or a small finite offset;
> last_value() (which scans backward); and RESPECT NULLS.
>
> Analysis
> --------
> ignorenulls_getfuncarginframe() (nodeWindowAgg.c) walks the frame forward,
> calling row_is_in_frame(winobj, abs_pos, slot, true) for each position and
> skipping to the next on a 0 result without fetching the row. But
> row_is_in_frame()'s header states the caller must have already determined the
> row is in the partition. For a frame ending at UNBOUNDED FOLLOWING it has no
> frame-end test, and its exclusion branch returns 0 ("excluded") for every
> position other than the current row when ordNumCols == 0 (EXCLUDE GROUP, and
> EXCLUDE TIES for pos != currentpos). So once abs_pos passes the last row,
> every call returns 0; window_gettupleslot() -- the only place that detects
> pos >= spooled_rows -- is never called, and abs_pos (an int64) just keeps
> growing. No CHECK_FOR_INTERRUPTS() runs on this path.
> With an ORDER BY, positions past the partition are not peers, row_is_in_frame()
> returns 1, window_gettupleslot() fails, and the loop ends -- which is why only
> the no-ORDER-BY case hangs. (This is distinct from the separately reported
> abs() overflow in the same function: that needs a large negative offset to
> overflow an int, whereas here the offset is 0 and the counter is int64.)
>
> Fix
> ________________________________
> The attached patch makes ignorenulls_getfuncarginframe() establish partition
> membership before calling row_is_in_frame(), using the same test
> window_gettupleslot() uses (spool_tuples() + spooled_rows), and adds a
> CHECK_FOR_INTERRUPTS() to the loop (which can skip many excluded or
> known-NULL rows without fetching any tuple). It also adds regression queries
> to window.sql. A lower-level alternative would be to make row_is_in_frame()
> itself report positions past the partition end, but that would change its
> documented precondition and affect every caller, so the caller-side fix seems
> preferable; I'm happy to redo it that way if you disagree.
> With the patch: the frame-scan queries above return at once with the expected
> values; the core regression suite passes (239/239), isolation passes
> (134/134), and the relevant TAP suites pass (psql including the query-cancel
> test, plus pg_ctl and test_misc). The new window.sql expected output was
> cross-checked against the aggregate-path results on an unpatched server, and a
> 24,000-query randomized comparison of IGNORE NULLS against that oracle found
> no differences. The patch applies to both current master and REL_19_STABLE.
>
> Thanks,
> Bhuvan Chilukuti
Thank you for the report and the fix. Unfortunately I have no time to
take care this for now (till November 3rd). I will work on this after
November 4th unless someone beats me.
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-10-25 16:04 shihao zhong <zhong950419@gmail.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 2 replies; 17+ messages in thread
From: shihao zhong @ 2026-10-25 16:04 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
Hi Bhuvan,
Thanks for the report and the patch. I can reproduce all five queries
on master and on 19rc1.
This came in with 25a30bbd423. I think it belongs on the 19 open items.
https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
With your patch all five return at once and the regression tests
pass. I also compared the three functions against array_agg(x) FILTER
(WHERE x IS NOT NULL) over about 11k frame combinations. That included
spill to disk and volatile arguments. No differences.
One suggestion for the test. Could you add last_value IGNORE NULLS
with EXCLUDE TIES and no ORDER BY? It scans backward, so it would
catch a later fix in row_is_in_frame that returns -1 for every
position after the current row.
Thanks,
Shihao
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-10-25 19:55 BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
parent: shihao zhong <zhong950419@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: BHUVAN CHILUKUTI @ 2026-10-25 19:55 UTC (permalink / raw)
To: shihao zhong <zhong950419@gmail.com>; Tatsuo Ishii <ishii@postgresql.org>; +Cc: pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi Shihao,
Thanks for reproducing the issue and for the helpful suggestion.
I've added the last_value(x) IGNORE NULLS regression case with EXCLUDE TIES and no ORDER BY. This covers the backward-scan path you mentioned: with no window ORDER BY, all rows are peers, so EXCLUDE TIES leaves only the current row in the frame, and the expected results are 1, 2, and 3.
The updated patch passes make check on both master and REL_19_STABLE (239/239 on each), and it applies cleanly to both branches. The production C fix is unchanged.
I've attached the updated patch for your review.
Best regards,
Bhuvan Chilukuti
________________________________
From: shihao zhong <zhong950419@gmail.com>
Sent: Sunday, October 25, 2026 9:34 PM
To: Tatsuo Ishii <ishii@postgresql.org>
Cc: BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Subject: Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
You don't often get email from zhong950419@gmail.com. Learn why this is important<https://aka.ms/LearnAboutSenderIdentification;
Hi Bhuvan,
Thanks for the report and the patch. I can reproduce all five queries
on master and on 19rc1.
This came in with 25a30bbd423. I think it belongs on the 19 open items.
https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
With your patch all five return at once and the regression tests
pass. I also compared the three functions against array_agg(x) FILTER
(WHERE x IS NOT NULL) over about 11k frame combinations. That included
spill to disk and volatile arguments. No differences.
One suggestion for the test. Could you add last_value IGNORE NULLS
with EXCLUDE TIES and no ORDER BY? It scans backward, so it would
catch a later fix in row_is_in_frame that returns -1 for every
position after the current row.
Thanks,
Shihao
Attachments:
AUDIT-F2_REGRESSION_TEST.patch (application/octet-stream, 8.4K) download
AUDIT-F2: fix endless loop in IGNORE NULLS frame scan, with regression test
===========================================================================
Base: PostgreSQL master 126976a8ffcfa47f40d3df09243ebf4606001912 (20devel,
2026-10-23). Also applies unchanged to REL_19_STABLE fc189eb750
("git apply --check" OK). Apply with: git apply AUDIT-F2_REGRESSION_TEST.patch
(git apply skips this header).
Status: proposed by this audit. NOT reviewed or accepted upstream.
Files changed
-------------
src/backend/executor/nodeWindowAgg.c (+15)
ignorenulls_getfuncarginframe() walks the frame row by row looking for the
n-th non-null value. It calls row_is_in_frame() on each position without
first checking that the position is inside the partition, although
row_is_in_frame() requires its caller to have done so ("The caller must
have already determined that the row is in the partition"). When the
frame ends at UNBOUNDED FOLLOWING, row_is_in_frame() has no end-of-frame
test, and with EXCLUDE GROUP (or EXCLUDE TIES, for rows other than the
current one) and no ORDER BY it returns 0 ("excluded") for every
position, including positions past the last row. The loop then never
calls window_gettupleslot(), which is what would have noticed the end of
the partition, and it advances abs_pos forever.
The fix checks the partition end before calling row_is_in_frame(), the
same way window_gettupleslot() does (spool_tuples(), then compare with
spooled_rows). It also adds CHECK_FOR_INTERRUPTS() at the top of the
loop: the loop can pass over many excluded or known-NULL rows without
fetching any tuple, and window_gettupleslot() is where the interrupt check
normally happens.
src/test/regress/sql/window.sql (+30)
src/test/regress/expected/window.out (+56)
Four queries, added next to the existing IGNORE NULLS tests:
1. first_value/nth_value IGNORE NULLS, ROWS ... UNBOUNDED FOLLOWING
EXCLUDE GROUP, no ORDER BY -> empty frame, all NULL
2. same functions, RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
EXCLUDE TIES, no ORDER BY -> frame = current row only
3. first_value IGNORE NULLS with PARTITION BY and EXCLUDE TIES
4. last_value IGNORE NULLS, ROWS ... UNBOUNDED FOLLOWING EXCLUDE TIES,
no ORDER BY -> frame = current row only, so each row returns its own
value. last_value() scans backward from the frame tail; this guards
the backward-scan path (which starts at a row after the current row)
against a later change to row_is_in_frame() that reports every
position after the current row as out of frame with -1.
Without the C change, the first query never finishes (pg_regress was
killed by a 180 s watchdog, still running). With it, the output is
identical to the same frames computed through the aggregate path,
(array_agg(x) FILTER (WHERE x IS NOT NULL) OVER w)[n], on the unpatched
server. The expected output was taken from the patched run only after
that independent check. The expected-file change is a pure insertion.
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index b86dcbba05..25cf3c15f7 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3466,6 +3466,9 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
int inframe;
int v;
+ /* we may skip many rows without fetching any of them */
+ CHECK_FOR_INTERRUPTS();
+
/*
* Check apparent out of frame case. We need to do this because we
* may not call window_gettupleslot before row_is_in_frame, which
@@ -3474,6 +3477,18 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
if (abs_pos < 0)
goto out_of_frame;
+ /*
+ * Likewise, stop at the end of the partition, which row_is_in_frame
+ * expects its caller to have checked. It cannot always detect that
+ * itself: for a frame ending at UNBOUNDED FOLLOWING with EXCLUDE
+ * GROUP or EXCLUDE TIES and no ORDER BY, it reports every row other
+ * than the current one as excluded, wherever it is, so we would
+ * never reach the end of the loop.
+ */
+ spool_tuples(winstate, abs_pos);
+ if (abs_pos >= winstate->spooled_rows)
+ goto out_of_frame;
+
/* check whether row is in frame */
inframe = row_is_in_frame(winobj, abs_pos, slot, true);
if (inframe == -1)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index c0bde1c5ee..9f007a2396 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -6188,6 +6188,62 @@ WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
5 | 4
(5 rows)
+-- EXCLUDE GROUP/TIES without ORDER BY: every row is a peer of the current
+-- row, so the IGNORE NULLS scan must stop at the end of the partition
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP)
+ORDER BY x;
+ x | first_value | nth_value
+---+-------------+-----------
+ 1 | |
+ 3 | |
+ | |
+(3 rows)
+
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY x;
+ x | first_value | nth_value
+---+-------------+-----------
+ 1 | 1 |
+ 3 | 3 |
+ | |
+(3 rows)
+
+SELECT p, x,
+ first_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1, 1), (1, NULL), (2, NULL)) v(p, x)
+WINDOW w AS (PARTITION BY p ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY p, x;
+ p | x | first_value
+---+---+-------------
+ 1 | 1 | 1
+ 1 | |
+ 2 | |
+(3 rows)
+
+-- last_value IGNORE NULLS scans backward; with EXCLUDE TIES and no ORDER BY
+-- the only in-frame row is the current row, and the scan must skip past the
+-- rows after the current row (which a later fix to row_is_in_frame might mark
+-- as out of frame with -1) rather than stopping there.
+SELECT x,
+ last_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1), (2), (3)) v(x)
+WINDOW w AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY x;
+ x | last_value
+---+------------
+ 1 | 1
+ 2 | 2
+ 3 | 3
+(3 rows)
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 8e6f92d94c..689fb0201f 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -2271,6 +2271,36 @@ SELECT x,
FROM generate_series(1,5) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
+-- EXCLUDE GROUP/TIES without ORDER BY: every row is a peer of the current
+-- row, so the IGNORE NULLS scan must stop at the end of the partition
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP)
+ORDER BY x;
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY x;
+SELECT p, x,
+ first_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1, 1), (1, NULL), (2, NULL)) v(p, x)
+WINDOW w AS (PARTITION BY p ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY p, x;
+
+-- last_value IGNORE NULLS scans backward; with EXCLUDE TIES and no ORDER BY
+-- the only in-frame row is the current row, and the scan must skip past the
+-- rows after the current row (which a later fix to row_is_in_frame might mark
+-- as out of frame with -1) rather than stopping there.
+SELECT x,
+ last_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1), (2), (3)) v(x)
+WINDOW w AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY x;
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-10-25 21:37 shihao zhong <zhong950419@gmail.com>
parent: BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
0 siblings, 0 replies; 17+ messages in thread
From: shihao zhong @ 2026-10-25 21:37 UTC (permalink / raw)
To: BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>; +Cc: Tatsuo Ishii <ishii@postgresql.org>; pgsql-bugs@lists.postgresql.org <pgsql-bugs@lists.postgresql.org>
Hi Bhuvan,
Thanks, v2 looks good tp me.
Tatsuo said he can look after Nov 3, so I think we just wait.
Thanks,
Shihao
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-04 07:44 Tatsuo Ishii <ishii@postgresql.org>
parent: shihao zhong <zhong950419@gmail.com>
1 sibling, 1 reply; 17+ messages in thread
From: Tatsuo Ishii @ 2026-11-04 07:44 UTC (permalink / raw)
To: zhong950419@gmail.com; +Cc: c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
Hi Shihao,
> Hi Bhuvan,
>
> Thanks for the report and the patch. I can reproduce all five queries
> on master and on 19rc1.
>
> This came in with 25a30bbd423. I think it belongs on the 19 open items.
>
> https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
Yeah, I have just added this to the 19 open items.
> With your patch all five return at once and the regression tests
> pass. I also compared the three functions against array_agg(x) FILTER
> (WHERE x IS NOT NULL) over about 11k frame combinations. That included
> spill to disk and volatile arguments. No differences.
>
> One suggestion for the test. Could you add last_value IGNORE NULLS
> with EXCLUDE TIES and no ORDER BY? It scans backward, so it would
> catch a later fix in row_is_in_frame that returns -1 for every
> position after the current row.
I have just started to read the discussion and patches. Just to
clarify, is the test for feature improvement of row_is_in_frame?
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-04 09:49 Tatsuo Ishii <ishii@postgresql.org>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 17+ messages in thread
From: Tatsuo Ishii @ 2026-11-04 09:49 UTC (permalink / raw)
To: zhong950419@gmail.com; +Cc: c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
Hi Shihao,
Fixing typo in the previous message...
>> Hi Bhuvan,
>>
>> Thanks for the report and the patch. I can reproduce all five queries
>> on master and on 19rc1.
>>
>> This came in with 25a30bbd423. I think it belongs on the 19 open items.
>>
>> https://wiki.postgresql.org/wiki/PostgreSQL_19_Open_Items
>
> Yeah, I have just added this to the 19 open items.
>
>> With your patch all five return at once and the regression tests
>> pass. I also compared the three functions against array_agg(x) FILTER
>> (WHERE x IS NOT NULL) over about 11k frame combinations. That included
>> spill to disk and volatile arguments. No differences.
>>
>> One suggestion for the test. Could you add last_value IGNORE NULLS
>> with EXCLUDE TIES and no ORDER BY? It scans backward, so it would
>> catch a later fix in row_is_in_frame that returns -1 for every
>> position after the current row.
>
> I have just started to read the discussion and patches. Just to
> clarify, is the test for feature improvement of row_is_in_frame?
~~~~~~~future
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-04 14:23 shihao zhong <zhong950419@gmail.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 17+ messages in thread
From: shihao zhong @ 2026-11-04 14:23 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
Yes, the last_value test passes with or without the fix and only
guards against a later change to row_is_in_frame that returns -1 for
rows after the current row.
Thanks,
Shihao
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-05 00:19 Tatsuo Ishii <ishii@postgresql.org>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tatsuo Ishii @ 2026-11-05 00:19 UTC (permalink / raw)
To: zhong950419@gmail.com; +Cc: c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
> Yes, the last_value test passes with or without the fix and only
> guards against a later change to row_is_in_frame that returns -1 for
> rows after the current row.
The change to row_is_in_frame has not been decided in the community
yet. Why we need to install a test for it now?
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-05 00:48 shihao zhong <zhong950419@gmail.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 17+ messages in thread
From: shihao zhong @ 2026-11-05 00:48 UTC (permalink / raw)
To: ishii@postgresql.org; +Cc: c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
Hi Tatsuo,
I think you are right here, sorry for my previous email confused you.
I misread Bhuvan's first mail. I thought row_is_in_frame also needed a fix,
so that it would return -1 for rows after the current row when there is no
ORDER BY.
I rechecked the code and the fix already covered all the cases, I dont
think we need to adjust the test anymore.
Thanks,
Shihao
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-05 05:29 Tatsuo Ishii <ishii@postgresql.org>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tatsuo Ishii @ 2026-11-05 05:29 UTC (permalink / raw)
To: zhong950419@gmail.com; +Cc: c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
Hi Shihao,
> Hi Tatsuo,
>
> I think you are right here, sorry for my previous email confused you.
No problem at all.
> I misread Bhuvan's first mail. I thought row_is_in_frame also needed a fix,
> so that it would return -1 for rows after the current row when there is no
> ORDER BY.
>
> I rechecked the code and the fix already covered all the cases, I dont
> think we need to adjust the test anymore.
Ok.
Attached is the v1 patch, based on Bhuvan's second patch, minus the
last_value test case. The commit message is almost copied from his
patch, except comments about the test cases, which I feel too verbose
for a commit message. IMO attached patch is ready for commit, but
comments are welcome. If there's no objection, I will push it to
master and 19 branches soon.
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
Attachments:
v1-0001-Fix-infinite-loop-in-first_value-nth_value-with-I.patch (application/octet-stream, 6.2K) download
From 547300fdf525c1e72e2cd3230cb8956761c72bf7 Mon Sep 17 00:00:00 2001
From: Tatsuo Ishii <ishii@postgresql.org>
Date: Thu, 5 Nov 2026 13:39:22 +0900
Subject: [PATCH v1] Fix infinite loop in first_value()/nth_value() with IGNORE
NULLS.
ignorenulls_getfuncarginframe() walks the frame row by row looking for
the n-th non-null value. It calls row_is_in_frame() on each position
without first checking that the position is inside the partition,
although row_is_in_frame() requires its caller to have done so ("The
caller must have already determined that the row is in the
partition"). When the frame ends at UNBOUNDED FOLLOWING,
row_is_in_frame() has no end-of-frame test, and with EXCLUDE GROUP (or
EXCLUDE TIES, for rows other than the current one) and no ORDER BY it
returns 0 ("excluded") for every position, including positions past
the last row. The loop then never calls window_gettupleslot(), which
is what would have noticed the end of the partition, and it advances
abs_pos forever. The fix checks the partition end before calling
row_is_in_frame(), the same way window_gettupleslot() does
(spool_tuples(), then compare with spooled_rows). It also adds
CHECK_FOR_INTERRUPTS() at the top of the loop: the loop can pass over
many excluded or known-NULL rows without fetching any tuple, and
window_gettupleslot() is where the interrupt check normally happens.
Author: BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
Reviewed-by: shihao zhong <zhong950419@gmail.com>
Reviewed-by: Tatsuo Ishii <ishii@postgresql.org>
Discussion: https://postgr.es/m/PN0P287MB09038F3BD9921EBACF5646F6A7632%40PN0P287MB0903.INDP287.PROD.OUTLOOK.COM
Backpatch-through: 19
---
src/backend/executor/nodeWindowAgg.c | 15 +++++++++++
src/test/regress/expected/window.out | 40 ++++++++++++++++++++++++++++
src/test/regress/sql/window.sql | 20 ++++++++++++++
3 files changed, 75 insertions(+)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index b86dcbba055..25cf3c15f7f 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3466,6 +3466,9 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
int inframe;
int v;
+ /* we may skip many rows without fetching any of them */
+ CHECK_FOR_INTERRUPTS();
+
/*
* Check apparent out of frame case. We need to do this because we
* may not call window_gettupleslot before row_is_in_frame, which
@@ -3474,6 +3477,18 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
if (abs_pos < 0)
goto out_of_frame;
+ /*
+ * Likewise, stop at the end of the partition, which row_is_in_frame
+ * expects its caller to have checked. It cannot always detect that
+ * itself: for a frame ending at UNBOUNDED FOLLOWING with EXCLUDE
+ * GROUP or EXCLUDE TIES and no ORDER BY, it reports every row other
+ * than the current one as excluded, wherever it is, so we would
+ * never reach the end of the loop.
+ */
+ spool_tuples(winstate, abs_pos);
+ if (abs_pos >= winstate->spooled_rows)
+ goto out_of_frame;
+
/* check whether row is in frame */
inframe = row_is_in_frame(winobj, abs_pos, slot, true);
if (inframe == -1)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index c0bde1c5eec..4b829c0d3dd 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -6188,6 +6188,46 @@ WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
5 | 4
(5 rows)
+-- EXCLUDE GROUP/TIES without ORDER BY: every row is a peer of the current
+-- row, so the IGNORE NULLS scan must stop at the end of the partition
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP)
+ORDER BY x;
+ x | first_value | nth_value
+---+-------------+-----------
+ 1 | |
+ 3 | |
+ | |
+(3 rows)
+
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY x;
+ x | first_value | nth_value
+---+-------------+-----------
+ 1 | 1 |
+ 3 | 3 |
+ | |
+(3 rows)
+
+SELECT p, x,
+ first_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1, 1), (1, NULL), (2, NULL)) v(p, x)
+WINDOW w AS (PARTITION BY p ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY p, x;
+ p | x | first_value
+---+---+-------------
+ 1 | 1 | 1
+ 1 | |
+ 2 | |
+(3 rows)
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 8e6f92d94c7..3ea1fae688b 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -2271,6 +2271,26 @@ SELECT x,
FROM generate_series(1,5) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING);
+-- EXCLUDE GROUP/TIES without ORDER BY: every row is a peer of the current
+-- row, so the IGNORE NULLS scan must stop at the end of the partition
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP)
+ORDER BY x;
+SELECT x,
+ first_value(x) IGNORE NULLS OVER w,
+ nth_value(x, 2) IGNORE NULLS OVER w
+FROM (VALUES (1), (NULL), (3)) v(x)
+WINDOW w AS (RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY x;
+SELECT p, x,
+ first_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1, 1), (1, NULL), (2, NULL)) v(p, x)
+WINDOW w AS (PARTITION BY p ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES)
+ORDER BY p, x;
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
--
2.43.0
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-06 00:25 Tatsuo Ishii <ishii@postgresql.org>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 17+ messages in thread
From: Tatsuo Ishii @ 2026-11-06 00:25 UTC (permalink / raw)
To: zhong950419@gmail.com; c.bhuvan@iitg.ac.in; +Cc: pgsql-bugs@lists.postgresql.org
> Attached is the v1 patch, based on Bhuvan's second patch, minus the
> last_value test case. The commit message is almost copied from his
> patch, except comments about the test cases, which I feel too verbose
> for a commit message. IMO attached patch is ready for commit, but
> comments are welcome. If there's no objection, I will push it to
> master and 19 branches soon.
Pushed. Thank you!
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=69b20fdd20de68b0f0eaa855669a6657c65c8...
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-06 14:41 Fujii Masao <masao.fujii@gmail.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 17+ messages in thread
From: Fujii Masao @ 2026-11-06 14:41 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: zhong950419@gmail.com; c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
On Fri, Nov 6, 2026 at 9:26 AM Tatsuo Ishii <ishii@postgresql.org> wrote:
> Pushed. Thank you!
>
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=69b20fdd20de68b0f0eaa855669a6657c65c8...
Thanks for working on this!
The following query succeeded before this fix but fails afterward.
Is this intentional?
WITH t AS MATERIALIZED (
SELECT NULLIF(i, 1) / (2 - i) AS v
FROM generate_series(1, 2) AS g(i)
)
SELECT first_value(v) IGNORE NULLS OVER (ROWS CURRENT ROW)
FROM t
LIMIT 1;
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-07 00:18 Tatsuo Ishii <ishii@postgresql.org>
parent: Fujii Masao <masao.fujii@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tatsuo Ishii @ 2026-11-07 00:18 UTC (permalink / raw)
To: masao.fujii@gmail.com; +Cc: zhong950419@gmail.com; c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
> The following query succeeded before this fix but fails afterward.
> Is this intentional?
>
> WITH t AS MATERIALIZED (
> SELECT NULLIF(i, 1) / (2 - i) AS v
> FROM generate_series(1, 2) AS g(i)
> )
> SELECT first_value(v) IGNORE NULLS OVER (ROWS CURRENT ROW)
> FROM t
> LIMIT 1;
Not intentional. I think it just happened to work by chance. The
outer node (WITH t AS MATERIALIZED ... FROM generate_series(1, 2)...)
raises an error (division by zero) as soon as the second row is
fetched.
When the window aggregate node (SELECT first_value(v) IGNORE NULLS
OVER (ROWS CURRENT ROW) FROM t) is executed, the outer node provides
the first row with v = NULL. So the window agg node tries to fetch the
second row to find out a non NULL row.
Previously we called row_is_in_frame() to determine if the second row
is in the frame without fetching the row in this
case. row_is_in_frame() determined that the second row is out of frame
because the framing option is "ROWS CURRENT ROW", meaning only the
current row (in this case the first row) is within a frame. Thus it
gave up searching further rows and return NULL without an error.
After the fix, before calling row_is_in_frame(), we call
spool_tuples() which tries to read rows from outer node to check
whether we hit the partition end. In the case above, we want to read
up to the second row. Unfortunately it raises the error in the outer
node.
I think this kind of behavior (reading beyond frame end) is not
specific to window functions with IGNORE NULLLS. Consider following
query:
WITH t AS MATERIALIZED (
SELECT NULLIF(i, 1) / (2 - i) AS v
FROM generate_series(1, 2) AS g(i)
)
SELECT nth_value(v,2) RESPECT NULLS OVER (ROWS CURRENT ROW)
FROM t
LIMIT 1;
This throws an error:
ERROR: division by zero
The window agg node tries to fetch the second row to evaluate
nth_value(v,2) and fails. One may ask why it fetches the second row
even if it is out of frame. In my understanding to determine whether a
row is in frame, we need to fetch the row first. We need to check the
tuple content in some frame options.
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-07 23:55 shihao zhong <zhong950419@gmail.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 1 reply; 17+ messages in thread
From: shihao zhong @ 2026-11-07 23:55 UTC (permalink / raw)
To: ishii@postgresql.org; masao.fujii@gmail.com; +Cc: c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
> In my understanding to determine whether a
> row is in frame, we need to fetch the row first. We need to check the
> tuple content in some frame options.
I agree about nth_value() with RESPECT NULLS. It has always worked
this way.
But for ROWS frames row_is_in_frame() can tell from the position
alone that the row is out of frame. The attached patch moves that call
back in front of the partition end check. Fujii-san's query returns
NULL again, and the loop from the original report still ends.
Thanks,
Shihao
Attachments:
v1-0001-Don-t-read-past-a-ROWS-frame-end-in-IGNORE-NULLS-.patch (application/octet-stream, 3.5K) download
From 3e2988ec7ad99c20a3015652b97d1ac5727acb33 Mon Sep 17 00:00:00 2001
From: shihao zhong <zhong950419@gmail.com>
Date: Sat, 7 Nov 2026 18:34:12 -0500
Subject: [PATCH v1] Don't read past a ROWS frame end in IGNORE NULLS scan.
Commit 69b20fdd20d checks the partition end before calling
row_is_in_frame(), which spools the row being tested. For ROWS frames
row_is_in_frame() can tell from the position alone that the row is out
of frame, so we now read and evaluate one extra row. Call
row_is_in_frame() first and check the partition end only when it does
not return -1.
Reported-by: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com
Backpatch-through: 19
---
src/backend/executor/nodeWindowAgg.c | 11 ++++++-----
src/test/regress/expected/window.out | 13 +++++++++++++
src/test/regress/sql/window.sql | 8 ++++++++
3 files changed, 27 insertions(+), 5 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 8d80f2cc023..ceec084aaf1 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3477,6 +3477,11 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
if (abs_pos < 0)
goto out_of_frame;
+ /* check whether row is in frame */
+ inframe = row_is_in_frame(winobj, abs_pos, slot, true);
+ if (inframe == -1)
+ goto out_of_frame;
+
/*
* Likewise, stop at the end of the partition, which row_is_in_frame
* expects its caller to have checked. It cannot always detect that
@@ -3489,11 +3494,7 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
if (abs_pos >= winstate->spooled_rows)
goto out_of_frame;
- /* check whether row is in frame */
- inframe = row_is_in_frame(winobj, abs_pos, slot, true);
- if (inframe == -1)
- goto out_of_frame;
- else if (inframe == 0)
+ if (inframe == 0)
goto advance;
if (isout)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 112551c350b..49d8489198e 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -6250,6 +6250,19 @@ ORDER BY p, x;
2 | |
(3 rows)
+-- the IGNORE NULLS scan must not read the row after a ROWS frame end
+WITH t AS MATERIALIZED (
+ SELECT NULLIF(i, 1) / (2 - i) AS v
+ FROM generate_series(1, 2) AS g(i)
+)
+SELECT first_value(v) IGNORE NULLS OVER (ROWS CURRENT ROW)
+FROM t
+LIMIT 1;
+ first_value
+-------------
+
+(1 row)
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index e848557174b..3a4e01b9119 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -2302,6 +2302,14 @@ SELECT p, x,
FROM (VALUES (1, 1), (1, NULL), (2, NULL)) v(p, x)
WINDOW w AS (PARTITION BY p ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES)
ORDER BY p, x;
+-- the IGNORE NULLS scan must not read the row after a ROWS frame end
+WITH t AS MATERIALIZED (
+ SELECT NULLIF(i, 1) / (2 - i) AS v
+ FROM generate_series(1, 2) AS g(i)
+)
+SELECT first_value(v) IGNORE NULLS OVER (ROWS CURRENT ROW)
+FROM t
+LIMIT 1;
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
--
2.37.1 (Apple Git-137.1)
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-09 00:19 Tatsuo Ishii <ishii@postgresql.org>
parent: shihao zhong <zhong950419@gmail.com>
0 siblings, 1 reply; 17+ messages in thread
From: Tatsuo Ishii @ 2026-11-09 00:19 UTC (permalink / raw)
To: zhong950419@gmail.com; +Cc: masao.fujii@gmail.com; c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
> I agree about nth_value() with RESPECT NULLS. It has always worked
> this way.
>
> But for ROWS frames row_is_in_frame() can tell from the position
> alone that the row is out of frame. The attached patch moves that call
> back in front of the partition end check. Fujii-san's query returns
> NULL again, and the loop from the original report still ends.
Thanks for the patch. I had thought about a similar fix but still am
not convinced it's worth the trouble.
Let's continue the discussion on it after 19 GA is out.
Regards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
@ 2026-11-09 05:54 Henson Choi <assam258@gmail.com>
parent: Tatsuo Ishii <ishii@postgresql.org>
0 siblings, 0 replies; 17+ messages in thread
From: Henson Choi @ 2026-11-09 05:54 UTC (permalink / raw)
To: Tatsuo Ishii <ishii@postgresql.org>; +Cc: zhong950419@gmail.com; masao.fujii@gmail.com; c.bhuvan@iitg.ac.in; pgsql-bugs@lists.postgresql.org
Hi Shihao, Tatsuo, Fujii-san, Andrey,
> Thanks for the patch. I had thought about a similar fix but still am
> not convinced it's worth the trouble.
>
> Let's continue the discussion on it after 19 GA is out.
That is fine with me, and the same goes for what follows: nothing here
needs to go in before 19 GA, and review and commit can wait until
after it. I am posting it now only so that it is on the list when the
discussion resumes. It is a series of eight patches on top of master,
starting with Shihao's v1 [2]. For the record, one case in it is a
query that worked before 69b20fdd20d [1] and fails on master and
REL_19_STABLE now (0003 and 0004 below).
The series has two parts. The first keeps the IGNORE NULLS scan
inside the frame. 0001 checks a ROWS frame end before reading the row
there. 0004 and 0005 handle what row_is_in_frame() cannot tell the
loop: it returns 0 both for a row that EXCLUDE removes and for a row
before the frame start, so the loop keeps stepping past such rows,
forward (0004) or backward (0005). The second part, 0006 and 0008,
makes sure that no frame access fetches a row before the frame start,
so that the mark can be moved there when no row is found. 0005 ties
the two together: it makes the frame start the explicit lower bound of
the backward walk, which 0008 then uses as the mark. 0002, 0003 and
0007 are comments and tests for these.
0001 is Shihao's v1, unchanged. I built current master and master
with v1: Fujii-san's query [3] returns NULL again with v1, and all of
Bhuvan's reproducers [4] return at once. Queries over a range of
frame modes, bounds, exclusions, and with and without ORDER BY and
PARTITION BY all finished, and their results matched array_agg(x)
FILTER (WHERE x IS NOT NULL).
0002 fixes the comment that started with "Likewise", which v1's
reordering moved away from the check it referred to, and says why
row_is_in_frame() is called first. Feel free to fold it into v1.
0003 adds tests for a case v1 does not cover: no ORDER BY in the
window, with EXCLUDE GROUP or EXCLUDE TIES.
WITH t AS MATERIALIZED (
SELECT NULLIF(i, 1) / (2 - i) AS v
FROM generate_series(1, 2) AS g(i)
)
SELECT first_value(v) IGNORE NULLS OVER (ROWS BETWEEN CURRENT ROW
AND 1 FOLLOWING EXCLUDE GROUP),
first_value(v) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED
PRECEDING AND 1 FOLLOWING EXCLUDE TIES)
FROM t
LIMIT 1;
Before 69b20fdd20d this returned two NULLs; on master and with v1 it
fails with "ERROR: division by zero". Without ORDER BY every row is
a peer of the current row, so EXCLUDE GROUP leaves the frame empty and
EXCLUDE TIES leaves only the current row, and the second row is not
needed. But row_is_in_frame() returns 0 (excluded) for it, not -1,
and v1 leaves before spool_tuples() only on -1, so the row is still
read. 0003 records the current output (the error) as the expected
result. It also checks that EXCLUDE TIES does not take the value of
the current row when the current row is outside the frame.
0004 handles this case before the loop: without ORDER BY, EXCLUDE
GROUP returns "out of frame" at once, and EXCLUDE TIES looks at the
current row only. The query above returns NULL again. In this shape
the loop also stepped over the excluded rows one at a time for every
row, up to the frame end or the partition end with EXCLUDE GROUP,
which is O(N^2); with EXCLUDE GROUP it is the shape that looped
forever in the original report. Timings for first_value(x) IGNORE
NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
...) over a 20000-row partition (cassert, -O0 build):
EXCLUDE GROUP EXCLUDE TIES
before the loop fix never ends 1.5 s
master 3.8 s 2.8 s
master + 0001 3.8 s 2.8 s
master + 0001-0004 8.6 ms 11.6 ms
RESPECT NULLS with the same frames takes about 8 ms and 11 ms on every
build. The appendix has the queries and the build used for all the
numbers in this mail. The partition-end check stays as a safety
net. 0004 also updates the expected output from 0003, so the diff
shows what changes.
0005 stops the backward walk of last_value() IGNORE NULLS at the frame
start. When the frame holds no non-null value, the walk does not stop
there today: row_is_in_frame() returns 0 (excluded), not -1, for a row
before the frame start, so the walk steps over every row back to the
partition start. It reads none of those rows, so the result is the
same, but it costs O(N^2). Timings for last_value(nullif(i, i))
IGNORE NULLS OVER (ORDER BY i ROWS BETWEEN CURRENT ROW AND 2
FOLLOWING) (cassert, -O0 build):
20000 rows 40000 rows
master + 0001-0004 2.4 s 9.3 s
master + 0001-0005 19 ms 38 ms
RESPECT NULLS with the same frame takes 19 ms and 37 ms.
Since no output changes, the regression tests cannot show this fix
itself. The test added in 0005 has the same output before and after;
it only checks that the walk neither returns the value just before the
frame start nor misses the one at it.
0006 is Andrey's v4 from CF 7412 [5] (bug #19732 [6]), unchanged. It
fixes first_value(), nth_value() and last_value() with EXCLUDE TIES
when the frame starts or ends on a peer of the current row. 0008
depends on it: without it, the WINDOW_SEEK_HEAD branch fetches the
current row even when the current row is before the frame start. That
fetch already fails with "cannot fetch row before WindowObject's mark
position" on master when the frame starts two or more rows after the
current row, and once 0008 also moves the mark when no row is found,
it fails in more cases. If 7412 goes in first, 0006 can be dropped.
0007 adds tests for frame accessors that find no row. With work_mem
set to 64kB, EXPLAIN ANALYZE shows the window storage for five
queries: first_value() over a one-row frame emptied by EXCLUDE CURRENT
ROW, nth_value() beyond the frame length, last_value() IGNORE NULLS,
which marks the partition start, and with IGNORE NULLS an empty frame
and a frame with no non-null value. All five show Disk today. It
also adds result tests that pass before and after 0008: a scan that
finds nothing in one frame and something in the next, and frames that
start after their end.
0008, with 0005, is the core-side patch I mentioned in the RPR thread
[7]. When the frame is empty or the requested row is outside it,
WinGetFuncArgInFrame() and ignorenulls_getfuncarginframe() return
without moving the mark, even if the caller asked them to set it, so
the tuplestore keeps every row since the last successful call. 0008
moves the mark to the frame start in that case, and makes last_value()
IGNORE NULLS mark the frame start instead of the partition start. The
window storage shown by EXPLAIN ANALYZE over a 20000-row table with
the default work_mem:
before 0008 with 0008
first_value, empty frame Disk, 4336kB Memory, 17kB
nth_value(pad, 5) Disk, 4336kB Memory, 18kB
last_value IGNORE NULLS Memory, 1142kB Memory, 17kB
count(*) over the same empty frame, nth_value(pad, 2), and
last_value() without IGNORE NULLS use Memory, 17kB on
both. 2d7b247cb41 [8] adjusted the IGNORE NULLS mark in
WinGetFuncArgInPartition() to avoid a fetch error; 0008 deals with the
mark of the frame accessors. With 0008 all five tests in 0007 show
Memory. This changes what set_mark means for a call that finds no
row. A window function that passed set_mark and then read rows before
the frame start after such a call would now fail; the built-in ones do
not, and a call that finds a row already moves the mark at least that
far.
0001-0004 deal with what changed in 69b20fdd20d, so I think they
belong in REL_19_STABLE as well as master. For 0005-0008 it is a
policy call. 0005 and the IGNORE NULLS part of 0008 fix
inefficiencies in code that is new in 19, while the
WinGetFuncArgInFrame() part of 0008 predates IGNORE NULLS. 0005, 0007
and 0008 change no result, and 0006 is here only because 0008 depends
on it. I left 0005, 0007 and 0008 without a Backpatch-through line,
and I can split 0008 if only its IGNORE NULLS part should go to 19. I
put them in this thread because they work on the same functions and
the same out_of_frame paths, and 0008 applies on top of 0005, but if
you would rather keep this thread to the regression, I will move
0005-0008 to a separate thread on pgsql-hackers.
Appendix: how the numbers were measured
Each build: --enable-cassert --enable-debug, CFLAGS=-O0, default
settings, macOS on Apple Silicon. Times are the best of three runs in
psql with \timing.
CREATE TABLE t20k AS
SELECT i AS x FROM generate_series(1, 20000) i;
CREATE TABLE u016_t AS
SELECT g AS id, repeat('x', 200) AS pad,
CASE WHEN g % 3 = 0 THEN NULL ELSE g END AS v
FROM generate_series(1, 20000) g;
ANALYZE t20k, u016_t;
0004 (EXCLUDE TIES for the second column; without IGNORE NULLS for
RESPECT NULLS):
SELECT count(f) FROM (
SELECT first_value(x) IGNORE NULLS
OVER (ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING EXCLUDE GROUP) AS f
FROM t20k) s;
0005 (40000 for the second column; without IGNORE NULLS for RESPECT
NULLS):
SELECT count(v) FROM (
SELECT last_value(nullif(i, i)) IGNORE NULLS
OVER (ORDER BY i ROWS BETWEEN CURRENT ROW
AND 2 FOLLOWING) AS v
FROM generate_series(1, 20000) i) s;
0008, each under EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF,
BUFFERS OFF), reading the Storage line of the WindowAgg node:
SELECT first_value(pad) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW
AND CURRENT ROW EXCLUDE CURRENT ROW) FROM u016_t;
SELECT count(*) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW
AND CURRENT ROW EXCLUDE CURRENT ROW) FROM u016_t;
SELECT nth_value(pad, 5) OVER (ORDER BY id ROWS BETWEEN
CURRENT ROW AND 2 FOLLOWING) FROM u016_t;
SELECT nth_value(pad, 2) OVER (ORDER BY id ROWS BETWEEN
CURRENT ROW AND 2 FOLLOWING) FROM u016_t;
SELECT last_value(v) IGNORE NULLS OVER (ORDER BY id ROWS BETWEEN
CURRENT ROW AND 2 FOLLOWING) FROM u016_t;
SELECT last_value(v) OVER (ORDER BY id ROWS BETWEEN CURRENT ROW
AND 2 FOLLOWING) FROM u016_t;
[1]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=69b20fdd20de68b0f0eaa855669a6657c...
[2]
https://postgr.es/m/CAGRkXqQEwe4aP-1eo3b8qJ_A2cxPQGd=n1U4-36z5CZx_niFxA@mail.gmail.com
[3]
https://postgr.es/m/CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com
[4]
https://postgr.es/m/PN0P287MB09038F3BD9921EBACF5646F6A7632@PN0P287MB0903.INDP287.PROD.OUTLOOK.COM
[5] https://commitfest.postgresql.org/patch/7412/
[6] https://postgr.es/m/19732-d4eed889e3021ef8@postgresql.org
[7]
https://postgr.es/m/CAAAe_zDM87k_d_UnkYVeG-XP+z_aBHWQFoe8osTN7DYcb_32cA@mail.gmail.com
[8]
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=2d7b247cb414ccbc052c485fd82a84147...
Best regards,
Henson
Attachments: download all (8 files, .tar.gz)
v2-0001-Don-t-read-past-a-ROWS-frame-end-in-IGNORE-NULLS-.patch (application/octet-stream, 3.5K) download
From ff36572adf1107ddec3a21070ed4598a9afd3e58 Mon Sep 17 00:00:00 2001
From: shihao zhong <zhong950419@gmail.com>
Date: Sat, 7 Nov 2026 18:34:12 -0500
Subject: [PATCH v2 1/8] Don't read past a ROWS frame end in IGNORE NULLS scan.
Commit 69b20fdd20d checks the partition end before calling
row_is_in_frame(), which spools the row being tested. For ROWS frames
row_is_in_frame() can tell from the position alone that the row is out
of frame, so we now read and evaluate one extra row. Call
row_is_in_frame() first and check the partition end only when it does
not return -1.
Author: shihao zhong <zhong950419@gmail.com>
Reported-by: Fujii Masao <masao.fujii@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com
Backpatch-through: 19
---
src/backend/executor/nodeWindowAgg.c | 11 ++++++-----
src/test/regress/expected/window.out | 13 +++++++++++++
src/test/regress/sql/window.sql | 8 ++++++++
3 files changed, 27 insertions(+), 5 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 8d80f2cc023..ceec084aaf1 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3477,6 +3477,11 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
if (abs_pos < 0)
goto out_of_frame;
+ /* check whether row is in frame */
+ inframe = row_is_in_frame(winobj, abs_pos, slot, true);
+ if (inframe == -1)
+ goto out_of_frame;
+
/*
* Likewise, stop at the end of the partition, which row_is_in_frame
* expects its caller to have checked. It cannot always detect that
@@ -3489,11 +3494,7 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
if (abs_pos >= winstate->spooled_rows)
goto out_of_frame;
- /* check whether row is in frame */
- inframe = row_is_in_frame(winobj, abs_pos, slot, true);
- if (inframe == -1)
- goto out_of_frame;
- else if (inframe == 0)
+ if (inframe == 0)
goto advance;
if (isout)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 112551c350b..49d8489198e 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -6250,6 +6250,19 @@ ORDER BY p, x;
2 | |
(3 rows)
+-- the IGNORE NULLS scan must not read the row after a ROWS frame end
+WITH t AS MATERIALIZED (
+ SELECT NULLIF(i, 1) / (2 - i) AS v
+ FROM generate_series(1, 2) AS g(i)
+)
+SELECT first_value(v) IGNORE NULLS OVER (ROWS CURRENT ROW)
+FROM t
+LIMIT 1;
+ first_value
+-------------
+
+(1 row)
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index e848557174b..3a4e01b9119 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -2302,6 +2302,14 @@ SELECT p, x,
FROM (VALUES (1, 1), (1, NULL), (2, NULL)) v(p, x)
WINDOW w AS (PARTITION BY p ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE TIES)
ORDER BY p, x;
+-- the IGNORE NULLS scan must not read the row after a ROWS frame end
+WITH t AS MATERIALIZED (
+ SELECT NULLIF(i, 1) / (2 - i) AS v
+ FROM generate_series(1, 2) AS g(i)
+)
+SELECT first_value(v) IGNORE NULLS OVER (ROWS CURRENT ROW)
+FROM t
+LIMIT 1;
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
--
2.54.0 (Apple Git-157)
v2-0002-Fix-the-comments-on-the-checks-in-the-IGNORE-NULL.patch (application/octet-stream, 2.6K) download
From b7aeac98b002245bb3107307b3cc3e38a501d027 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Sun, 8 Nov 2026 23:20:05 +0900
Subject: [PATCH v2 2/8] Fix the comments on the checks in the IGNORE NULLS
frame scan loop.
The previous commit moved the row_is_in_frame() call ahead of the
partition-end check. Adjust the comments to the new order:
- The comment that started with "Likewise" no longer followed the
check it referred to.
- Nothing said why the two checks are in this order. Explain that for
ROWS frames row_is_in_frame() tells from the position alone that a
row is past the frame end, so we need not read that row.
- Keep the explanation of why the partition-end check is still needed.
Author: Henson Choi <assam258@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com
Backpatch-through: 19
---
src/backend/executor/nodeWindowAgg.c | 19 ++++++++++++-------
1 file changed, 12 insertions(+), 7 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index ceec084aaf1..a64e80e4fcf 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3477,18 +3477,23 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
if (abs_pos < 0)
goto out_of_frame;
- /* check whether row is in frame */
+ /*
+ * Check whether row is in frame. row_is_in_frame expects its caller
+ * to have checked that the row is in the partition, but we call it
+ * first anyway: for ROWS frames it can tell from the position alone
+ * that the row is past the frame end, and we must not read such a row
+ * just to find out whether it exists.
+ */
inframe = row_is_in_frame(winobj, abs_pos, slot, true);
if (inframe == -1)
goto out_of_frame;
/*
- * Likewise, stop at the end of the partition, which row_is_in_frame
- * expects its caller to have checked. It cannot always detect that
- * itself: for a frame ending at UNBOUNDED FOLLOWING with EXCLUDE
- * GROUP or EXCLUDE TIES and no ORDER BY, it reports every row other
- * than the current one as excluded, wherever it is, so we would never
- * reach the end of the loop.
+ * Now stop at the end of the partition. row_is_in_frame cannot
+ * always detect that itself: for a frame ending at UNBOUNDED
+ * FOLLOWING with EXCLUDE GROUP or EXCLUDE TIES and no ORDER BY, it
+ * reports every row other than the current one as excluded, wherever
+ * it is, so we would never reach the end of the loop.
*/
spool_tuples(winstate, abs_pos);
if (abs_pos >= winstate->spooled_rows)
--
2.54.0 (Apple Git-157)
v2-0003-Add-tests-for-IGNORE-NULLS-frame-scans-with-EXCLU.patch (application/octet-stream, 3.9K) download
From 0c0ed6de4b28e7c42dc6204f6d3d9ffeae01d009 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Sun, 8 Nov 2026 23:20:05 +0900
Subject: [PATCH v2 3/8] Add tests for IGNORE NULLS frame scans with EXCLUDE
GROUP/TIES and no ORDER BY.
Without ORDER BY every row is a peer of the current row, so EXCLUDE
GROUP leaves no row in the frame and EXCLUDE TIES leaves at most the
current row; first_value() IGNORE NULLS over such a frame needs no row
other than the current one. The scan nevertheless reads the excluded
rows that follow, so an error raised while producing the second row of
the input makes these queries fail. The expected output records the
current behavior, which the next commit fixes. Also check that
EXCLUDE TIES does not take the value of the current row when the
current row is outside the frame.
Author: Henson Choi <assam258@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com
Backpatch-through: 19
---
src/test/regress/expected/window.out | 24 ++++++++++++++++++++++++
src/test/regress/sql/window.sql | 17 +++++++++++++++++
2 files changed, 41 insertions(+)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 49d8489198e..b646cca97c4 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -6263,6 +6263,30 @@ LIMIT 1;
(1 row)
+-- likewise for the rows excluded by EXCLUDE GROUP/TIES without ORDER BY
+WITH t AS MATERIALIZED (
+ SELECT NULLIF(i, 1) / (2 - i) AS v
+ FROM generate_series(1, 2) AS g(i)
+)
+SELECT first_value(v) IGNORE NULLS OVER (ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING EXCLUDE GROUP),
+ first_value(v) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE TIES)
+FROM t
+LIMIT 1;
+ERROR: division by zero
+-- EXCLUDE TIES without ORDER BY: the current row is not used when it is
+-- outside the frame
+SELECT x,
+ first_value(x) IGNORE NULLS OVER (ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING EXCLUDE TIES) AS f1,
+ last_value(x) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) AS l1,
+ last_value(x) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS l2
+FROM (VALUES (1), (NULL), (3)) v(x);
+ x | f1 | l1 | l2
+---+----+----+----
+ 1 | | | 1
+ | | |
+ 3 | | | 3
+(3 rows)
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 3a4e01b9119..9cf98f4385a 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -2310,6 +2310,23 @@ WITH t AS MATERIALIZED (
SELECT first_value(v) IGNORE NULLS OVER (ROWS CURRENT ROW)
FROM t
LIMIT 1;
+-- likewise for the rows excluded by EXCLUDE GROUP/TIES without ORDER BY
+WITH t AS MATERIALIZED (
+ SELECT NULLIF(i, 1) / (2 - i) AS v
+ FROM generate_series(1, 2) AS g(i)
+)
+SELECT first_value(v) IGNORE NULLS OVER (ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING EXCLUDE GROUP),
+ first_value(v) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE TIES)
+FROM t
+LIMIT 1;
+
+-- EXCLUDE TIES without ORDER BY: the current row is not used when it is
+-- outside the frame
+SELECT x,
+ first_value(x) IGNORE NULLS OVER (ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING EXCLUDE TIES) AS f1,
+ last_value(x) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) AS l1,
+ last_value(x) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS l2
+FROM (VALUES (1), (NULL), (3)) v(x);
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
--
2.54.0 (Apple Git-157)
v2-0004-Skip-the-IGNORE-NULLS-frame-scan-for-EXCLUDE-GROU.patch (application/octet-stream, 4.3K) download
From f31b85e373d4b6f6f078a332725204a978f0e707 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Sun, 8 Nov 2026 23:20:05 +0900
Subject: [PATCH v2 4/8] Skip the IGNORE NULLS frame scan for EXCLUDE
GROUP/TIES without ORDER BY.
Without ORDER BY every row is a peer of the current row, so EXCLUDE
GROUP leaves no row in the frame and EXCLUDE TIES leaves at most the
current row. row_is_in_frame() reports each of the other rows in the
frame as excluded, so ignorenulls_getfuncarginframe() stepped over all
of them until the frame end or the end of the partition. Since
69b20fdd20d that loop spools every position it visits, so it now reads,
and evaluates, rows that are not in the frame, including the first row
of the next partition, and an error in such a row makes the query fail.
It also costs O(N^2) for a partition of N rows.
Handle these cases before the loop: EXCLUDE GROUP returns "out of
frame" at once, and EXCLUDE TIES looks at the current row only. The
example in the comment on the partition-end check no longer reaches
the loop, so drop it.
Author: Henson Choi <assam258@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com
Backpatch-through: 19
---
src/backend/executor/nodeWindowAgg.c | 43 +++++++++++++++++++++++-----
src/test/regress/expected/window.out | 6 +++-
2 files changed, 41 insertions(+), 8 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index a64e80e4fcf..9fb3f4b29c5 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3457,6 +3457,41 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
break;
}
+ /*
+ * Without ORDER BY, all rows are peers of the current row, so EXCLUDE
+ * GROUP leaves no row in the frame and EXCLUDE TIES leaves at most the
+ * current row. row_is_in_frame() reports every other row in the frame as
+ * excluded, so the loop below would step over each of them, possibly
+ * reading rows up to the frame end or the end of the partition, to find
+ * at most the current row. Handle these cases directly.
+ */
+ if (((WindowAgg *) winstate->ss.ps.plan)->ordNumCols == 0 &&
+ (winstate->frameOptions & (FRAMEOPTION_EXCLUDE_GROUP |
+ FRAMEOPTION_EXCLUDE_TIES)))
+ {
+ if ((winstate->frameOptions & FRAMEOPTION_EXCLUDE_GROUP) ||
+ notnull_relpos > 0)
+ goto out_of_frame;
+
+ /* only the current row can supply the value */
+ abs_pos = winstate->currentpos;
+ if (row_is_in_frame(winobj, abs_pos, slot, true) <= 0)
+ goto out_of_frame;
+ if (!window_gettupleslot(winobj, abs_pos, slot))
+ goto out_of_frame;
+ econtext->ecxt_outertuple = slot;
+ datum = ExecEvalExpr((ExprState *) list_nth(winobj->argstates, argno),
+ econtext, isnull);
+ if (*isnull)
+ goto out_of_frame;
+
+ if (isout)
+ *isout = false;
+ if (set_mark)
+ WinSetMarkPosition(winobj, mark_pos);
+ return datum;
+ }
+
/*
* Get the next nonnull value in the frame, moving forward or backward
* until we find a value or reach the frame's end.
@@ -3488,13 +3523,7 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
if (inframe == -1)
goto out_of_frame;
- /*
- * Now stop at the end of the partition. row_is_in_frame cannot
- * always detect that itself: for a frame ending at UNBOUNDED
- * FOLLOWING with EXCLUDE GROUP or EXCLUDE TIES and no ORDER BY, it
- * reports every row other than the current one as excluded, wherever
- * it is, so we would never reach the end of the loop.
- */
+ /* now stop at the end of the partition */
spool_tuples(winstate, abs_pos);
if (abs_pos >= winstate->spooled_rows)
goto out_of_frame;
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index b646cca97c4..fdb0513f747 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -6272,7 +6272,11 @@ SELECT first_value(v) IGNORE NULLS OVER (ROWS BETWEEN CURRENT ROW AND 1 FOLLOWIN
first_value(v) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE TIES)
FROM t
LIMIT 1;
-ERROR: division by zero
+ first_value | first_value
+-------------+-------------
+ |
+(1 row)
+
-- EXCLUDE TIES without ORDER BY: the current row is not used when it is
-- outside the frame
SELECT x,
--
2.54.0 (Apple Git-157)
v2-0005-Stop-the-IGNORE-NULLS-backward-frame-scan-at-the-.patch (application/octet-stream, 4.3K) download
From d6242f49a29ed61b41b41390b3b74fefe9b4ec47 Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Mon, 9 Nov 2026 13:23:41 +0900
Subject: [PATCH v2 5/8] Stop the IGNORE NULLS backward frame scan at the frame
start.
When last_value() IGNORE NULLS finds no non-null value in the frame,
ignorenulls_getfuncarginframe() keeps walking backward past the frame
start, because row_is_in_frame() returns 0 for a row before it, as for
an excluded row, rather than -1. The walk only stops at the partition
start, so for a frame such as ROWS BETWEEN CURRENT ROW AND 2 FOLLOWING
it costs O(N^2) for a partition of N rows.
Stop at the frame start: exclusion only removes rows inside the frame,
so no row before it can be in the frame. This replaces the abs_pos < 0
test at the top of the loop, since the frame start is never negative.
Add a test that the walk does not return the value just before the
frame start and does not miss the one at it. Its output is the same
before and after this change.
Author: Henson Choi <assam258@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com
---
src/backend/executor/nodeWindowAgg.c | 12 ++++++++----
src/test/regress/expected/window.out | 14 ++++++++++++++
src/test/regress/sql/window.sql | 7 +++++++
3 files changed, 29 insertions(+), 4 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 9fb3f4b29c5..8d2cf3f96b2 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -3448,6 +3448,7 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
goto out_of_frame;
update_frametailpos(winstate);
abs_pos = winstate->frametailpos - 1;
+ update_frameheadpos(winstate);
mark_pos = 0; /* keep compiler quiet */
forward = -1;
break;
@@ -3505,11 +3506,14 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
CHECK_FOR_INTERRUPTS();
/*
- * Check apparent out of frame case. We need to do this because we
- * may not call window_gettupleslot before row_is_in_frame, which
- * supposes abs_pos is never negative.
+ * Check apparent out of frame case. Only the backward walk can get
+ * before the frame start, and that ends it: exclusion only removes
+ * rows inside the frame, so every row from here on is out of it. We
+ * cannot leave this to row_is_in_frame, which returns 0 for such a
+ * row, as for an excluded one, since it assumes a forward scan, and
+ * which supposes abs_pos is never negative.
*/
- if (abs_pos < 0)
+ if (abs_pos < winstate->frameheadpos)
goto out_of_frame;
/*
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index fdb0513f747..038a3f07ff1 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -6291,6 +6291,20 @@ FROM (VALUES (1), (NULL), (3)) v(x);
3 | | | 3
(3 rows)
+-- a value before the frame start is not returned
+SELECT i, x,
+ last_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1, 1), (2, NULL), (3, NULL), (4, 4)) v(i, x)
+WINDOW w AS (ORDER BY i ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
+ORDER BY i;
+ i | x | last_value
+---+---+------------
+ 1 | 1 |
+ 2 | | 4
+ 3 | | 4
+ 4 | 4 |
+(4 rows)
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 9cf98f4385a..7c591307999 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -2328,6 +2328,13 @@ SELECT x,
last_value(x) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS l2
FROM (VALUES (1), (NULL), (3)) v(x);
+-- a value before the frame start is not returned
+SELECT i, x,
+ last_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1, 1), (2, NULL), (3, NULL), (4, 4)) v(i, x)
+WINDOW w AS (ORDER BY i ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
+ORDER BY i;
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
--
2.54.0 (Apple Git-157)
v2-0006-Fix-first_value-nth_value-and-last_value-with-EXC.patch (application/octet-stream, 6.9K) download
From 611ad54d9aca218d70809e178843073b094dd43f Mon Sep 17 00:00:00 2001
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Mon, 5 Oct 2026 16:07:43 +0500
Subject: [PATCH v2 6/8] Fix first_value, nth_value, and last_value with
EXCLUDE TIES.
WinGetFuncArgInFrame remaps the first overlapping peer to currentpos
for EXCLUDE TIES, and the last overlapping peer when seeking from the
frame tail. That is only valid when the current row is in the frame.
With a ROWS frame that starts on a peer, such as 1 FOLLOWING AND
UNBOUNDED FOLLOWING, the remap lands on the current row, which is then
rejected as out of frame. first_value and nth_value return NULL even
though later in-frame rows remain. last_value has the same hole when
the frame ends on a peer.
Aggregates over the same window already saw those rows, because they
walk the frame with row_is_in_frame.
If the current row is before the frame head, skip the overlapping
peer group as EXCLUDE GROUP does. Do the same from the tail when the
current row is at or after the frame tail.
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reported-by: Shallow <theshallow27@gmail.com>
Reviewed-by: shihao zhong <zhong950419@gmail.com>
Discussion: https://www.postgresql.org/message-id/19732-d4eed889e3021ef8@postgresql.org
---
src/backend/executor/nodeWindowAgg.c | 29 ++++++++++------
src/test/regress/expected/window.out | 51 ++++++++++++++++++++++++++++
src/test/regress/sql/window.sql | 24 +++++++++++++
3 files changed, 93 insertions(+), 11 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 8d2cf3f96b2..49f2d268dcb 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -4116,11 +4116,8 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
* row's peer group from resulting in trying to fetch a row before
* some previous mark position.
*
- * Note that in some corner cases such as current row being
- * outside frame, these calculations are theoretically too simple,
- * but it doesn't matter because we'll end up deciding the row is
- * out of frame. We do not attempt to avoid fetching rows past
- * end of frame; that would happen in some cases anyway.
+ * We do not attempt to avoid fetching rows past end of frame.
+ * That would happen in some cases anyway.
*/
switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION)
{
@@ -4151,10 +4148,15 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
int64 overlapstart = Max(winstate->groupheadpos,
winstate->frameheadpos);
- if (abs_pos == overlapstart)
- abs_pos = winstate->currentpos;
+ if (winstate->currentpos >= winstate->frameheadpos)
+ {
+ if (abs_pos == overlapstart)
+ abs_pos = winstate->currentpos;
+ else
+ abs_pos += winstate->grouptailpos - overlapstart - 1;
+ }
else
- abs_pos += winstate->grouptailpos - overlapstart - 1;
+ abs_pos += winstate->grouptailpos - overlapstart;
}
break;
default:
@@ -4217,10 +4219,15 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
int64 overlapend = Min(winstate->grouptailpos,
winstate->frametailpos);
- if (abs_pos == overlapend - 1)
- abs_pos = winstate->currentpos;
+ if (winstate->currentpos < winstate->frametailpos)
+ {
+ if (abs_pos == overlapend - 1)
+ abs_pos = winstate->currentpos;
+ else
+ abs_pos -= overlapend - 1 - winstate->groupheadpos;
+ }
else
- abs_pos -= overlapend - 1 - winstate->groupheadpos;
+ abs_pos -= overlapend - winstate->groupheadpos;
}
update_frameheadpos(winstate);
if (abs_pos < winstate->frameheadpos)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 038a3f07ff1..271f50bd464 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -1037,6 +1037,57 @@ FROM tenk1 WHERE unique1 < 10;
7 | 7 | 3
(10 rows)
+-- first_value/last_value with EXCLUDE TIES when the frame edge is a peer
+SELECT k, first_value(k) OVER w
+FROM (VALUES (0), (0), (1)) t(k)
+WINDOW w AS (ORDER BY k ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
+ EXCLUDE TIES);
+ k | first_value
+---+-------------
+ 0 | 1
+ 0 | 1
+ 1 |
+(3 rows)
+
+SELECT k, last_value(k) OVER w
+FROM (VALUES (0), (1), (1)) t(k)
+WINDOW w AS (ORDER BY k ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
+ EXCLUDE TIES);
+ k | last_value
+---+------------
+ 0 |
+ 1 | 0
+ 1 | 0
+(3 rows)
+
+-- first_value with EXCLUDE TIES when peers lie before the frame head
+SELECT k, first_value(k) OVER w
+FROM (VALUES (0), (1), (1), (1), (2)) t(k)
+WINDOW w AS (ORDER BY k ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING
+ EXCLUDE TIES);
+ k | first_value
+---+-------------
+ 0 | 1
+ 1 | 2
+ 1 | 2
+ 1 |
+ 2 |
+(5 rows)
+
+-- nth_value(n > 1) with EXCLUDE TIES when the frame starts on a peer
+SELECT k, nth_value(k, 2) OVER w,
+ (array_agg(k) OVER w)[2] AS expected
+FROM (VALUES (0), (0), (1), (2)) t(k)
+WINDOW w AS (ORDER BY k ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
+ EXCLUDE TIES);
+ k | nth_value | expected
+---+-----------+----------
+ 0 | 2 | 2
+ 0 | 2 | 2
+ 1 | |
+ 2 | |
+(4 rows)
+
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 7c591307999..35bd881b632 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -235,6 +235,30 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
unique1, four
FROM tenk1 WHERE unique1 < 10;
+-- first_value/last_value with EXCLUDE TIES when the frame edge is a peer
+SELECT k, first_value(k) OVER w
+FROM (VALUES (0), (0), (1)) t(k)
+WINDOW w AS (ORDER BY k ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
+ EXCLUDE TIES);
+
+SELECT k, last_value(k) OVER w
+FROM (VALUES (0), (1), (1)) t(k)
+WINDOW w AS (ORDER BY k ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
+ EXCLUDE TIES);
+
+-- first_value with EXCLUDE TIES when peers lie before the frame head
+SELECT k, first_value(k) OVER w
+FROM (VALUES (0), (1), (1), (1), (2)) t(k)
+WINDOW w AS (ORDER BY k ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING
+ EXCLUDE TIES);
+
+-- nth_value(n > 1) with EXCLUDE TIES when the frame starts on a peer
+SELECT k, nth_value(k, 2) OVER w,
+ (array_agg(k) OVER w)[2] AS expected
+FROM (VALUES (0), (0), (1), (2)) t(k)
+WINDOW w AS (ORDER BY k ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING
+ EXCLUDE TIES);
+
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
--
2.54.0 (Apple Git-157)
v2-0007-Add-tests-for-frame-accessors-that-find-no-row.patch (application/octet-stream, 10.4K) download
From 603ba391a91f23d79edb02c5a579ba0d6f047d9c Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Mon, 9 Nov 2026 09:50:08 +0900
Subject: [PATCH v2 7/8] Add tests for frame accessors that find no row.
When the frame is empty or the requested row is outside it,
WinGetFuncArgInFrame() and ignorenulls_getfuncarginframe() do not move
the mark, so the window tuplestore cannot be trimmed and spills to disk
for a long partition even though the frame is small. The same holds
for last_value() IGNORE NULLS, whose backward scan sets the mark to the
partition start. Show the storage used in these cases; the expected
output records the current behavior, which the next commit fixes.
Also check the results of IGNORE NULLS scans that find nothing in one
frame and something in the next, and of frames that start after their
end.
Author: Henson Choi <assam258@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com
---
src/test/regress/expected/explain.out | 62 +++++++++++++++++++++++++++
src/test/regress/expected/window.out | 34 +++++++++++++++
src/test/regress/sql/explain.sql | 12 ++++++
src/test/regress/sql/window.sql | 15 +++++++
4 files changed, 123 insertions(+)
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 74a4d87801e..45e1096593a 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -831,4 +831,66 @@ select explain_filter('explain (analyze,buffers off,costs off) select sum(n) ove
Execution Time: N.N ms
(9 rows)
+-- Test tuplestore storage usage in Window aggregate when the requested row
+-- is not in the frame: a one-row frame emptied by EXCLUDE CURRENT ROW, and an
+-- nth_value() past the end of a three-row frame
+select explain_filter('explain (analyze,buffers off,costs off) select first_value(n) over(rows between current row and current row exclude current row) from generate_series(1,2500) a(n)');
+ explain_filter
+----------------------------------------------------------------------------------
+ WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: w1 AS (ROWS BETWEEN CURRENT ROW AND CURRENT ROW EXCLUDE CURRENT ROW)
+ Storage: Disk Maximum Storage: NkB
+ -> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(6 rows)
+
+select explain_filter('explain (analyze,buffers off,costs off) select nth_value(n, 5) over(rows between current row and 2 following) from generate_series(1,2500) a(n)');
+ explain_filter
+----------------------------------------------------------------------------------
+ WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: w1 AS (ROWS BETWEEN CURRENT ROW AND 'N'::bigint FOLLOWING)
+ Storage: Disk Maximum Storage: NkB
+ -> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(6 rows)
+
+-- likewise for last_value() IGNORE NULLS, which finds its row by scanning
+-- the frame backward from its end
+select explain_filter('explain (analyze,buffers off,costs off) select last_value(n) ignore nulls over(rows between current row and 2 following) from generate_series(1,2500) a(n)');
+ explain_filter
+----------------------------------------------------------------------------------
+ WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: w1 AS (ROWS BETWEEN CURRENT ROW AND 'N'::bigint FOLLOWING)
+ Storage: Disk Maximum Storage: NkB
+ -> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(6 rows)
+
+-- likewise with IGNORE NULLS, for an empty frame and for a frame with no
+-- non-null value
+select explain_filter('explain (analyze,buffers off,costs off) select first_value(n) ignore nulls over(rows between current row and current row exclude current row) from generate_series(1,2500) a(n)');
+ explain_filter
+----------------------------------------------------------------------------------
+ WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: w1 AS (ROWS BETWEEN CURRENT ROW AND CURRENT ROW EXCLUDE CURRENT ROW)
+ Storage: Disk Maximum Storage: NkB
+ -> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(6 rows)
+
+select explain_filter('explain (analyze,buffers off,costs off) select last_value(nullif(n, n)) ignore nulls over(rows between current row and 2 following) from generate_series(1,2500) a(n)');
+ explain_filter
+----------------------------------------------------------------------------------
+ WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
+ Window: w1 AS (ROWS BETWEEN CURRENT ROW AND 'N'::bigint FOLLOWING)
+ Storage: Disk Maximum Storage: NkB
+ -> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
+ Planning Time: N.N ms
+ Execution Time: N.N ms
+(6 rows)
+
reset work_mem;
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 271f50bd464..c4715e8a0aa 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -6342,6 +6342,22 @@ FROM (VALUES (1), (NULL), (3)) v(x);
3 | | | 3
(3 rows)
+-- an IGNORE NULLS scan that finds no non-null value does not prevent the
+-- next frame from returning one
+SELECT i, x,
+ last_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1, NULL), (2, NULL), (3, NULL), (4, 4), (5, NULL)) v(i, x)
+WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
+ORDER BY i;
+ i | x | last_value
+---+---+------------
+ 1 | |
+ 2 | |
+ 3 | | 4
+ 4 | 4 | 4
+ 5 | |
+(5 rows)
+
-- a value before the frame start is not returned
SELECT i, x,
last_value(x) IGNORE NULLS OVER w
@@ -6356,6 +6372,24 @@ ORDER BY i;
4 | 4 |
(4 rows)
+-- a frame that starts after its end is empty
+SELECT i,
+ first_value(i) OVER w,
+ last_value(i) OVER w,
+ nth_value(i, 1) OVER w,
+ last_value(i) IGNORE NULLS OVER w
+FROM generate_series(1, 6) i
+WINDOW w AS (ORDER BY i ROWS BETWEEN 3 FOLLOWING AND 1 FOLLOWING);
+ i | first_value | last_value | nth_value | last_value
+---+-------------+------------+-----------+------------
+ 1 | | | |
+ 2 | | | |
+ 3 | | | |
+ 4 | | | |
+ 5 | | | |
+ 6 | | | |
+(6 rows)
+
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
CREATE FUNCTION pg_temp.volatile_null(i int) RETURNS int
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index 2f163c64bf6..9285154d701 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -190,4 +190,16 @@ set work_mem to 64;
select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over() from generate_series(1,2500) a(n)');
-- Test tuplestore storage usage in Window aggregate (memory and disk case, final result is disk)
select explain_filter('explain (analyze,buffers off,costs off) select sum(n) over(partition by m) from (SELECT n < 3 as m, n from generate_series(1,2500) a(n))');
+-- Test tuplestore storage usage in Window aggregate when the requested row
+-- is not in the frame: a one-row frame emptied by EXCLUDE CURRENT ROW, and an
+-- nth_value() past the end of a three-row frame
+select explain_filter('explain (analyze,buffers off,costs off) select first_value(n) over(rows between current row and current row exclude current row) from generate_series(1,2500) a(n)');
+select explain_filter('explain (analyze,buffers off,costs off) select nth_value(n, 5) over(rows between current row and 2 following) from generate_series(1,2500) a(n)');
+-- likewise for last_value() IGNORE NULLS, which finds its row by scanning
+-- the frame backward from its end
+select explain_filter('explain (analyze,buffers off,costs off) select last_value(n) ignore nulls over(rows between current row and 2 following) from generate_series(1,2500) a(n)');
+-- likewise with IGNORE NULLS, for an empty frame and for a frame with no
+-- non-null value
+select explain_filter('explain (analyze,buffers off,costs off) select first_value(n) ignore nulls over(rows between current row and current row exclude current row) from generate_series(1,2500) a(n)');
+select explain_filter('explain (analyze,buffers off,costs off) select last_value(nullif(n, n)) ignore nulls over(rows between current row and 2 following) from generate_series(1,2500) a(n)');
reset work_mem;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 35bd881b632..ce49438e551 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -2352,12 +2352,27 @@ SELECT x,
last_value(x) IGNORE NULLS OVER (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) AS l2
FROM (VALUES (1), (NULL), (3)) v(x);
+-- an IGNORE NULLS scan that finds no non-null value does not prevent the
+-- next frame from returning one
+SELECT i, x,
+ last_value(x) IGNORE NULLS OVER w
+FROM (VALUES (1, NULL), (2, NULL), (3, NULL), (4, 4), (5, NULL)) v(i, x)
+WINDOW w AS (ORDER BY i ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING)
+ORDER BY i;
-- a value before the frame start is not returned
SELECT i, x,
last_value(x) IGNORE NULLS OVER w
FROM (VALUES (1, 1), (2, NULL), (3, NULL), (4, 4)) v(i, x)
WINDOW w AS (ORDER BY i ROWS BETWEEN 1 FOLLOWING AND 2 FOLLOWING)
ORDER BY i;
+-- a frame that starts after its end is empty
+SELECT i,
+ first_value(i) OVER w,
+ last_value(i) OVER w,
+ nth_value(i, 1) OVER w,
+ last_value(i) IGNORE NULLS OVER w
+FROM generate_series(1, 6) i
+WINDOW w AS (ORDER BY i ROWS BETWEEN 3 FOLLOWING AND 1 FOLLOWING);
-- volatile arguments cannot use the IGNORE NULLS nullness cache
CREATE TEMPORARY SEQUENCE null_treatment_seq;
--
2.54.0 (Apple Git-157)
v2-0008-Advance-the-window-mark-when-a-frame-accessor-fin.patch (application/octet-stream, 9.5K) download
From b5c6f60d4c64eea8b90b7e3b2506fb7b2b1f117e Mon Sep 17 00:00:00 2001
From: Henson Choi <assam258@gmail.com>
Date: Mon, 9 Nov 2026 09:45:27 +0900
Subject: [PATCH v2 8/8] Advance the window mark when a frame accessor finds no
row.
When the frame is empty or the requested row is outside it,
WinGetFuncArgInFrame() and ignorenulls_getfuncarginframe() return
without moving the mark, even if the caller asked them to set it.
The tuplestore then keeps every row since the last successful call,
so a long partition spills to disk although the frame is small, e.g.
first_value() over a one-row frame emptied by EXCLUDE CURRENT ROW, or
nth_value() beyond the frame length.
Move the mark to the frame start in that case, but not past the last
row read. This relies on no frame access reading a row before the
frame start. The EXCLUDE TIES fix earlier in this series removed the
one that did, and the WINDOW_SEEK_TAIL branch now checks the frame
start also when there is no exclusion.
The WINDOW_SEEK_TAIL branch of ignorenulls_getfuncarginframe() set the
mark to 0, which would now fail with "cannot move WindowObject's mark
position backward". Use the frame start instead; the backward walk
stops there.
The storage shown by the tests of the previous commit changes from
disk to memory.
Author: Henson Choi <assam258@gmail.com>
Discussion: https://postgr.es/m/CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com
---
src/backend/executor/nodeWindowAgg.c | 49 +++++++++++++++++++++++----
src/test/regress/expected/explain.out | 10 +++---
2 files changed, 47 insertions(+), 12 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index 49f2d268dcb..708ce5d336a 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -212,6 +212,7 @@ static bool are_peers(WindowAggState *winstate, TupleTableSlot *slot1,
static bool window_gettupleslot(WindowObject winobj, int64 pos,
TupleTableSlot *slot);
+static void advance_mark_to_framehead(WindowObject winobj);
static Datum ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
int relpos, int seektype,
bool set_mark, bool *isnull,
@@ -3399,6 +3400,32 @@ gettuple_eval_partition(WindowObject winobj, int argno,
econtext, isnull);
}
+/*
+ * advance_mark_to_framehead
+ * Advance the mark of a WindowObject to the current frame start, if it
+ * is behind it.
+ *
+ * Used when a frame accessor asked to set the mark finds no row to return.
+ * The frame start is a position before which no later frame access fetches,
+ * so the rows before it can be released by tuplestore_trim() even though no
+ * row was fetched. Don't move the mark past the last row read so far,
+ * though: the read pointers would reach the end of the tuplestore and lose
+ * their place once more rows are appended.
+ */
+static void
+advance_mark_to_framehead(WindowObject winobj)
+{
+ WindowAggState *winstate = winobj->winstate;
+ int64 markpos;
+
+ update_frameheadpos(winstate);
+
+ /* the current row has been read, so spooled_rows is at least 1 */
+ markpos = Min(winstate->frameheadpos, winstate->spooled_rows - 1);
+ if (markpos > winobj->markpos)
+ WinSetMarkPosition(winobj, markpos);
+}
+
/*
* ignorenulls_getfuncarginframe
* For IGNORE NULLS, get the next nonnull value in the frame, moving forward
@@ -3448,8 +3475,9 @@ ignorenulls_getfuncarginframe(WindowObject winobj, int argno,
goto out_of_frame;
update_frametailpos(winstate);
abs_pos = winstate->frametailpos - 1;
+ /* the backward walk never fetches a row before the frame start */
update_frameheadpos(winstate);
- mark_pos = 0; /* keep compiler quiet */
+ mark_pos = winstate->frameheadpos;
forward = -1;
break;
default:
@@ -3584,6 +3612,8 @@ advance:
return datum;
out_of_frame:
+ if (set_mark)
+ advance_mark_to_framehead(winobj);
if (isout)
*isout = true;
*isnull = true;
@@ -4054,8 +4084,11 @@ WinGetFuncArgInPartition(WindowObject winobj, int argno,
* argno: argument number to evaluate (counted from 0)
* relpos: signed rowcount offset from the seek position
* seektype: WINDOW_SEEK_HEAD or WINDOW_SEEK_TAIL
- * set_mark: If the row is found/in frame and set_mark is true, the mark is
- * moved to the row as a side-effect.
+ * set_mark: If true, the mark is moved forward as a side-effect, to a
+ * position before which no later frame access will fetch. That is
+ * the row returned if there is no exclusion clause and nulls are
+ * respected, and otherwise a position no later than the frame start.
+ * The mark is moved even if no row is found.
* isnull: output argument, receives isnull status of result
* isout: output argument, set to indicate whether target row position
* is out of frame (can pass NULL if caller doesn't care about this)
@@ -4177,21 +4210,23 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
* exclusion, we can safely set the mark at the accessed row. But
* if there is, we can only mark the frame start, because we can't
* be sure how far back in the frame the exclusion might cause us
- * to fetch in future. Furthermore, we have to actually check
+ * to fetch in future. Either way, we have to actually check
* against frameheadpos here, since it's unsafe to try to fetch a
* row before frame start if the mark might be there already.
*/
+ update_frameheadpos(winstate);
switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION)
{
case 0:
/* no adjustment needed */
+ if (abs_pos < winstate->frameheadpos)
+ goto out_of_frame;
mark_pos = abs_pos;
break;
case FRAMEOPTION_EXCLUDE_CURRENT_ROW:
if (abs_pos <= winstate->currentpos &&
winstate->currentpos < winstate->frametailpos)
abs_pos--;
- update_frameheadpos(winstate);
if (abs_pos < winstate->frameheadpos)
goto out_of_frame;
mark_pos = winstate->frameheadpos;
@@ -4206,7 +4241,6 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
abs_pos -= overlapend - winstate->groupheadpos;
}
- update_frameheadpos(winstate);
if (abs_pos < winstate->frameheadpos)
goto out_of_frame;
mark_pos = winstate->frameheadpos;
@@ -4229,7 +4263,6 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
else
abs_pos -= overlapend - winstate->groupheadpos;
}
- update_frameheadpos(winstate);
if (abs_pos < winstate->frameheadpos)
goto out_of_frame;
mark_pos = winstate->frameheadpos;
@@ -4263,6 +4296,8 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
econtext, isnull);
out_of_frame:
+ if (set_mark)
+ advance_mark_to_framehead(winobj);
if (isout)
*isout = true;
*isnull = true;
diff --git a/src/test/regress/expected/explain.out b/src/test/regress/expected/explain.out
index 45e1096593a..5054e6d75d3 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -839,7 +839,7 @@ select explain_filter('explain (analyze,buffers off,costs off) select first_valu
----------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
Window: w1 AS (ROWS BETWEEN CURRENT ROW AND CURRENT ROW EXCLUDE CURRENT ROW)
- Storage: Disk Maximum Storage: NkB
+ Storage: Memory Maximum Storage: NkB
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
@@ -850,7 +850,7 @@ select explain_filter('explain (analyze,buffers off,costs off) select nth_value(
----------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
Window: w1 AS (ROWS BETWEEN CURRENT ROW AND 'N'::bigint FOLLOWING)
- Storage: Disk Maximum Storage: NkB
+ Storage: Memory Maximum Storage: NkB
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
@@ -863,7 +863,7 @@ select explain_filter('explain (analyze,buffers off,costs off) select last_value
----------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
Window: w1 AS (ROWS BETWEEN CURRENT ROW AND 'N'::bigint FOLLOWING)
- Storage: Disk Maximum Storage: NkB
+ Storage: Memory Maximum Storage: NkB
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
@@ -876,7 +876,7 @@ select explain_filter('explain (analyze,buffers off,costs off) select first_valu
----------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
Window: w1 AS (ROWS BETWEEN CURRENT ROW AND CURRENT ROW EXCLUDE CURRENT ROW)
- Storage: Disk Maximum Storage: NkB
+ Storage: Memory Maximum Storage: NkB
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
@@ -887,7 +887,7 @@ select explain_filter('explain (analyze,buffers off,costs off) select last_value
----------------------------------------------------------------------------------
WindowAgg (actual time=N.N..N.N rows=N.N loops=N)
Window: w1 AS (ROWS BETWEEN CURRENT ROW AND 'N'::bigint FOLLOWING)
- Storage: Disk Maximum Storage: NkB
+ Storage: Memory Maximum Storage: NkB
-> Function Scan on generate_series a (actual time=N.N..N.N rows=N.N loops=N)
Planning Time: N.N ms
Execution Time: N.N ms
--
2.54.0 (Apple Git-157)
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2026-11-09 05:54 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-10-24 04:34 first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY) BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
2026-10-24 07:51 ` Tatsuo Ishii <ishii@postgresql.org>
2026-10-25 16:04 ` shihao zhong <zhong950419@gmail.com>
2026-10-25 19:55 ` BHUVAN CHILUKUTI <c.bhuvan@iitg.ac.in>
2026-10-25 21:37 ` shihao zhong <zhong950419@gmail.com>
2026-11-04 07:44 ` Tatsuo Ishii <ishii@postgresql.org>
2026-11-04 09:49 ` Tatsuo Ishii <ishii@postgresql.org>
2026-11-04 14:23 ` shihao zhong <zhong950419@gmail.com>
2026-11-05 00:19 ` Tatsuo Ishii <ishii@postgresql.org>
2026-11-05 00:48 ` shihao zhong <zhong950419@gmail.com>
2026-11-05 05:29 ` Tatsuo Ishii <ishii@postgresql.org>
2026-11-06 00:25 ` Tatsuo Ishii <ishii@postgresql.org>
2026-11-06 14:41 ` Fujii Masao <masao.fujii@gmail.com>
2026-11-07 00:18 ` Tatsuo Ishii <ishii@postgresql.org>
2026-11-07 23:55 ` shihao zhong <zhong950419@gmail.com>
2026-11-09 00:19 ` Tatsuo Ishii <ishii@postgresql.org>
2026-11-09 05:54 ` Henson Choi <assam258@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox