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

ср, 30 сент. 2026 г. в 13:26, PG Bug reporting form <noreply@postgresql.org

:

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.

Hi, Shallow!

Thanks for the report.

Agreed on the WinGetFuncArgInFrame remap. Replacing abs_pos with
currentpos is only valid when the current row is in the frame.
When it is not, EXCLUDE TIES should skip the overlap the same way EXCLUDE
GROUP does.
The attached patch does that for the frame head and the frame tail.

--
Regards,
Rachitskiy Andrey

ср, 30 сент. 2026 г. в 18:25, Andrey Rachitskiy <pl0h0yp1@gmail.com>:

ср, 30 сент. 2026 г. в 13:26, PG Bug reporting form <
noreply@postgresql.org>:

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.

Hi, Shallow!

Thanks for the report.

Agreed on the WinGetFuncArgInFrame remap. Replacing abs_pos with
currentpos is only valid when the current row is in the frame.
When it is not, EXCLUDE TIES should skip the overlap the same way EXCLUDE
GROUP does.
The attached patch does that for the frame head and the frame tail.

v2 attached. The C change is the same. The regress is now two small
queries, first_value on a following frame and last_value on a
preceding frame.

Hi Andrey,

v2 looks good, I checked first_value, last_value and nth_value
against array_agg for every frame shape, and they all match now.

Maybe one more test

SELECT k, first_value(k) OVER w
FROM (VALUES (0), (1), (1), (1), (2)) t(k)
WINDOW w AS (ORDER BY k ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING
EXCLUDE TIES);

On Master it report ERROR: cannot fetch row before WindowObject's mark
position

This patch also covers 19731

Thanks,
Shihao

пн, 5 окт. 2026 г. в 07:39, shihao zhong <zhong950419@gmail.com>:

v2 looks good, I checked first_value, last_value and nth_value
against array_agg for every frame shape, and they all match now.

Maybe one more test

SELECT k, first_value(k) OVER w
FROM (VALUES (0), (1), (1), (1), (2)) t(k)
WINDOW w AS (ORDER BY k ROWS BETWEEN 2 FOLLOWING AND UNBOUNDED FOLLOWING
EXCLUDE TIES);

On Master it report ERROR: cannot fetch row before WindowObject's mark
position

Hi, Shihao!
Thanks for the review.
Added in v3.