BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: theshallow27(at)gmail(dot)com
Subject: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
Date: 2026-09-30 03:34:33
Message-ID: 19732-d4eed889e3021ef8@postgresql.org
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 19732
Logged by: Shallow
Email address: theshallow27(at)gmail(dot)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.

Browse pgsql-bugs by date

  From Date Subject
Next Message PG Bug reporting form 2026-09-30 04:30:43 BUG #19733: Row not visible to a new snapshot after its transactional logical decoding message has been streamed
Previous Message Masahiko Sawada 2026-09-30 02:19:33 Re: autovacuum: automatically propagate updated parameters