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

Hi Andrey,

With AI assistance, I tested v3 against 9e7d152488f197e96f171f0714459c227c3f7693 on Linux x86_64 with GCC 14.2.0 and assertions enabled. All 239 core regression tests passed via make installcheck.

One regression suggestion: could we also include nth_value with n greater than 1? This small case returns the wrong non-NULL value before the patch:

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

For the first row, the unpatched code returns 1 instead of 2. With v3, both columns return 2.

I also checked first_value, last_value and nth_value against array_agg across a bounded matrix of ROWS/RANGE/GROUPS frames and exclusion options, including NULL payloads, multiple partitions, descending ordering, and varying or NULL nth_value offsets. All 61,920 row comparisons matched with v3. The unpatched build showed mismatches and mark-position errors only in the ROWS/EXCLUDE TIES cases.

I did not find a correctness issue in these checks.