From: Tatsuo Ishii <ishii@postgresql.org>
To: zhong950419@gmail.com
Cc: c.bhuvan@iitg.ac.in
Cc: 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)
Date: Thu, 05 Nov 2026 14:29:57 +0900 (JST)
Message-ID: <20261105.142957.1216400569926986716.ishii@postgresql.org> (raw)
In-Reply-To: <CAGRkXqRw1xNgDsCf215VSueLUcvHSyxjhYE=ox5eMpMOv4APtw@mail.gmail.com>
References: <CAGRkXqQOgo8qCsi42rJ-qJOpMVTZFM5YZdKxo9U2em51Vzyhvg@mail.gmail.com>
<20261105.091947.1557138019099337522.ishii@postgresql.org>
<CAGRkXqRw1xNgDsCf215VSueLUcvHSyxjhYE=ox5eMpMOv4APtw@mail.gmail.com>
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.jpAttachments: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.cindex 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.outindex 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.sqlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: ishii@postgresql.org, zhong950419@gmail.com, c.bhuvan@iitg.ac.in
Subject: Re: first_value()/nth_value() with IGNORE NULLS loop forever (EXCLUDE GROUP/TIES, frame past partition end, no ORDER BY)
In-Reply-To: <20261105.142957.1216400569926986716.ishii@postgresql.org>
References: <CAGRkXqQOgo8qCsi42rJ-qJOpMVTZFM5YZdKxo9U2em51Vzyhvg@mail.gmail.com> <20261105.091947.1557138019099337522.ishii@postgresql.org> <CAGRkXqRw1xNgDsCf215VSueLUcvHSyxjhYE=ox5eMpMOv4APtw@mail.gmail.com> <20261105.142957.1216400569926986716.ishii@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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