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