Re: 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(at)postgresql(dot)org
>:

> 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.
>
>
>
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