BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
От:
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.
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@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.
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.
GROUP does.
The attached patch does that for the frame head and the frame tail.
Regards,
Rachitskiy Andrey
Rachitskiy Andrey
Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
ср, 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 apreceding frame.
Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
От:
shihao zhong <zhong950419@gmail.com>
Дата:
Hi Andrey,
On Master it report ERROR: cannot fetch row before WindowObject's mark position
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);
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
Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
пн, 5 окт. 2026 г. в 07:39, shihao zhong <zhong950419@gmail.com>:
v2 looks good, I checked first_value, last_value and nth_valueagainst array_agg for every frame shape, and they all match now.Maybe one more testSELECT 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.
Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
От:
shihao zhong <zhong950419@gmail.com>
Дата:
Hi Andrey,
v3 looks good to me.
v3 looks good to me.
Thanks,
Shihao
Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
От:
QQ Mail <781408517@qq.com>
Дата:
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.Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
пн, 12 окт. 2026 г. в 13:14, QQ Mail <781408517@qq.com>:
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.
Hi,
Thanks for the test.
Added in v4.
Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
От:
Henson Choi <assam258@gmail.com>
Дата:
Hi Andrey,
> Added in v4.
I looked at v4.
This is how I read the old code. It assumes the current row is in
the frame. Then EXCLUDE TIES leaves only the current row of the
part of its peer group that overlaps the frame, so the arithmetic
keeps room for it: on the head side the first overlapping position
is remapped to currentpos, and the positions after it are shifted
by (grouptailpos - overlapstart - 1). The tail side is the mirror
image, with overlapend - 1 and (overlapend - 1 - groupheadpos).
The "- 1" is there because the current row is taken to be inside
this overlap.
But what if the current row is before the frame head (or, seeking
from the tail, at or after the frame tail)? Then the current row is
not part of the overlap, so there is no reason to subtract 1. The
old code uses the same arithmetic anyway: the remap points outside
the frame, and the shift falls one row short.
So in that case, without the remap and the "- 1", what is left is
exactly the EXCLUDE GROUP formula, on both the head and the tail
side. Working it out from the "- 1", I end up where your first
mail did: when the current row is not in the frame, EXCLUDE TIES
removes the same rows as EXCLUDE GROUP, so it should skip the
overlap the same way.
On backpatching: v4 applies cleanly to REL_14_STABLE through
REL_19_STABLE, and "make check" passes on all of them.
The fix looks correct to me. I'll mark it Ready for Committer.
Regards,
Henson
> Added in v4.
I looked at v4.
This is how I read the old code. It assumes the current row is in
the frame. Then EXCLUDE TIES leaves only the current row of the
part of its peer group that overlaps the frame, so the arithmetic
keeps room for it: on the head side the first overlapping position
is remapped to currentpos, and the positions after it are shifted
by (grouptailpos - overlapstart - 1). The tail side is the mirror
image, with overlapend - 1 and (overlapend - 1 - groupheadpos).
The "- 1" is there because the current row is taken to be inside
this overlap.
But what if the current row is before the frame head (or, seeking
from the tail, at or after the frame tail)? Then the current row is
not part of the overlap, so there is no reason to subtract 1. The
old code uses the same arithmetic anyway: the remap points outside
the frame, and the shift falls one row short.
So in that case, without the remap and the "- 1", what is left is
exactly the EXCLUDE GROUP formula, on both the head and the tail
side. Working it out from the "- 1", I end up where your first
mail did: when the current row is not in the frame, EXCLUDE TIES
removes the same rows as EXCLUDE GROUP, so it should skip the
overlap the same way.
On backpatching: v4 applies cleanly to REL_14_STABLE through
REL_19_STABLE, and "make check" passes on all of them.
The fix looks correct to me. I'll mark it Ready for Committer.
Regards,
Henson