pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
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.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

view thread (17+ messages)  latest in thread

Message-ID: <20261105.142957.1216400569926986716.ishii@postgresql.org>
Permalink:  ../20261105.142957.1216400569926986716.ishii@postgresql.org/
Also on:    postgresql.org/message-id/20261105.142957.1216400569926986716.ishii@postgresql.org

 ·  · 

reply

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