Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE
 TIES when the current row is outside its f
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: theshallow27@gmail.com
Reply-To: theshallow27@gmail.com, pgsql-bugs@lists.postgresql.org
Date: Wed, 30 Sep 2026 03:34:33 +0000
Message-ID: <19732-d4eed889e3021ef8@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19732-d4eed889e3021ef8%40postgresql.org>
Precedence: bulk

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:       =20

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 =3D 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.