ср, 30 сент. 2026 г. в 18:25, Andrey Rachitskiy <pl0h0yp1(at)gmail(dot)com>:
>
>
> ср, 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.
>
>
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.