pg.ddx.io  pgsql-bugs@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tatsuo Ishii <ishii@postgresql.org>
To: masao.fujii@gmail.com
Cc: 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: Sat, 07 Nov 2026 09:18:33 +0900 (JST)
Message-ID: <20261107.091833.1799286331446051531.ishii@postgresql.org> (raw)
In-Reply-To: <CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com>
References: <20261105.142957.1216400569926986716.ishii@postgresql.org>
	<20261106.092531.2094637405208239588.ishii@postgresql.org>
	<CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com>

> 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






view thread (17+ messages)  latest in thread

Message-ID: <20261107.091833.1799286331446051531.ishii@postgresql.org>
Permalink:  ../20261107.091833.1799286331446051531.ishii@postgresql.org/
Also on:    postgresql.org/message-id/20261107.091833.1799286331446051531.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, masao.fujii@gmail.com, 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: <20261107.091833.1799286331446051531.ishii@postgresql.org>
  References: <20261105.142957.1216400569926986716.ishii@postgresql.org> <20261106.092531.2094637405208239588.ishii@postgresql.org> <CAHGQGwFTmdD5T+7z8kZekrjVkA=Z2yNhq43ff3AuoxPVxDXP1A@mail.gmail.com> <20261107.091833.1799286331446051531.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