agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedFrom: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: theshallow27@gmail.com
Subject: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
Date: Wed, 30 Sep 2026 03:34:33 +0000
Message-ID: <19732-d4eed889e3021ef8@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 19732
Logged by: Shallow
Email address: theshallow27@gmail.com
PostgreSQL version: 18.6
Operating system: Linux
Description:
With a ROWS frame that does not contain the current row, such as `1
FOLLOWING AND UNBOUNDED FOLLOWING`,
and `EXCLUDE TIES`, `first_value`, `nth_value` and `last_value` can return
NULL although the frame has rows.
This happens when the frame edge falls on a peer of the current row.
`array_agg` over the same window shows
the rows.
```sql
SELECT k, first_value(k) OVER w, nth_value(k, 1) OVER w, array_agg(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 | nth_value | array_agg
---+-------------+-----------+-----------
0 | | | {1} <- expected 1, 1
0 | 1 | 1 | {1}
1 | | |
SELECT k, last_value(k) OVER w, array_agg(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 | array_agg
---+------------+-----------
0 | |
1 | 0 | {0}
1 | | {0} <- expected 0
```
For the first row of the first query, the frame is rows 2 and 3. Row 2 is a
peer of the current row, so
`EXCLUDE TIES` removes it, and row 3 (k = 1) remains. Without `EXCLUDE
TIES`, the same frame gives the
right values.
In `WinGetFuncArgInFrame` (nodeWindowAgg.c), the `FRAMEOPTION_EXCLUDE_TIES`
case replaces `abs_pos` by
`winstate->currentpos` when the frame edge is the first row of the overlap
between the frame and the
current row's peer group. This is right only when the current row is inside
the frame. The comment before
the switch expects the out-of-frame case to end with "deciding the row is
out of frame", but that returns
NULL here although later frame rows remain. The frame-tail branch has the
same substitution.
view thread (5+ messages) latest in thread
Message-ID: <19732-d4eed889e3021ef8@postgresql.org>
Permalink: ../19732-d4eed889e3021ef8@postgresql.org/
Also on: postgresql.org/message-id/19732-d4eed889e3021ef8@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 using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, theshallow27@gmail.com
Subject: Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
In-Reply-To: <19732-d4eed889e3021ef8@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 agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox