agora inbox for pgsql-bugs@postgresql.org
help / color / mirror / Atom feedBUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
4+ messages / 3 participants
[nested] [flat]
* BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
@ 2026-09-30 03:34 PG Bug reporting form <noreply@postgresql.org>
2026-09-30 13:25 ` Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: PG Bug reporting form @ 2026-09-30 03:34 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: theshallow27@gmail.com
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
2026-09-30 03:34 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>
@ 2026-09-30 13:25 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-30 14:33 ` Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f Andrey Rachitskiy <pl0h0yp1@gmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Andrey Rachitskiy @ 2026-09-30 13:25 UTC (permalink / raw)
To: theshallow27@gmail.com; pgsql-bugs@lists.postgresql.org
ср, 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.
--
Regards,
Rachitskiy Andrey
Attachments:
[text/x-patch] 0001-Fix-first_value-nth_value-and-last_value-with-EXCLUD.patch (6.0K, ../../CAB8bMiv=E9u0h+GmH1xEoQvvZhvKifNvrCY71=mmP9FMznYKdw@mail.gmail.com/3-0001-Fix-first_value-nth_value-and-last_value-with-EXCLUD.patch)
download | inline diff:
From 3155dae57df13d88b8cc398b35b03755b5b3330b Mon Sep 17 00:00:00 2001
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Wed, 30 Sep 2026 18:04:07 +0500
Subject: [PATCH] Fix first_value, nth_value, and last_value with EXCLUDE TIES.
WinGetFuncArgInFrame remaps the first overlapping peer to currentpos
for EXCLUDE TIES, and the last overlapping peer when seeking from the
frame tail. That is only valid when the current row is in the frame.
With a ROWS frame that starts on a peer, such as 1 FOLLOWING AND
UNBOUNDED FOLLOWING, the remap lands on the current row, which is then
rejected as out of frame. first_value and nth_value return NULL even
though later in-frame rows remain. last_value has the same hole when
the frame ends on a peer.
Aggregates over the same window already saw those rows, because they
walk the frame with row_is_in_frame.
If the current row is before the frame head, skip the overlapping
peer group as EXCLUDE GROUP does. Do the same from the tail when the
current row is at or after the frame tail.
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reported-by: Shallow <theshallow27@gmail.com>
Discussion: https://www.postgresql.org/message-id/19732-d4eed889e3021ef8@postgresql.org
---
src/backend/executor/nodeWindowAgg.c | 29 +++++++++++++++++-----------
src/test/regress/expected/window.out | 25 ++++++++++++++++++++++++
src/test/regress/sql/window.sql | 13 +++++++++++++
3 files changed, 56 insertions(+), 11 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index b86dcbba055..0b41eb4739e 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -4062,11 +4062,8 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
* row's peer group from resulting in trying to fetch a row before
* some previous mark position.
*
- * Note that in some corner cases such as current row being
- * outside frame, these calculations are theoretically too simple,
- * but it doesn't matter because we'll end up deciding the row is
- * out of frame. We do not attempt to avoid fetching rows past
- * end of frame; that would happen in some cases anyway.
+ * We do not attempt to avoid fetching rows past end of frame.
+ * That would happen in some cases anyway.
*/
switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION)
{
@@ -4097,10 +4094,15 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
int64 overlapstart = Max(winstate->groupheadpos,
winstate->frameheadpos);
- if (abs_pos == overlapstart)
- abs_pos = winstate->currentpos;
+ if (winstate->currentpos >= winstate->frameheadpos)
+ {
+ if (abs_pos == overlapstart)
+ abs_pos = winstate->currentpos;
+ else
+ abs_pos += winstate->grouptailpos - overlapstart - 1;
+ }
else
- abs_pos += winstate->grouptailpos - overlapstart - 1;
+ abs_pos += winstate->grouptailpos - overlapstart;
}
break;
default:
@@ -4163,10 +4165,15 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
int64 overlapend = Min(winstate->grouptailpos,
winstate->frametailpos);
- if (abs_pos == overlapend - 1)
- abs_pos = winstate->currentpos;
+ if (winstate->currentpos < winstate->frametailpos)
+ {
+ if (abs_pos == overlapend - 1)
+ abs_pos = winstate->currentpos;
+ else
+ abs_pos -= overlapend - 1 - winstate->groupheadpos;
+ }
else
- abs_pos -= overlapend - 1 - winstate->groupheadpos;
+ abs_pos -= overlapend - winstate->groupheadpos;
}
update_frameheadpos(winstate);
if (abs_pos < winstate->frameheadpos)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index c0bde1c5eec..c5435903140 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -1037,6 +1037,31 @@ FROM tenk1 WHERE unique1 < 10;
7 | 7 | 3
(10 rows)
+-- ROWS frame that does not contain the current row, but whose edge falls on
+-- a peer. EXCLUDE TIES must still return remaining in-frame rows.
+SELECT k, first_value(k) OVER w, nth_value(k, 1) OVER w AS nth_1,
+ nth_value(k, 2) OVER w AS nth_2, 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_1 | nth_2 | array_agg
+---+-------------+-------+-------+-----------
+ 0 | 1 | 1 | | {1}
+ 0 | 1 | 1 | | {1}
+ 1 | | | |
+(3 rows)
+
+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 | {0}
+(3 rows)
+
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 8e6f92d94c7..7142b89c1ea 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -235,6 +235,19 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
unique1, four
FROM tenk1 WHERE unique1 < 10;
+-- ROWS frame that does not contain the current row, but whose edge falls on
+-- a peer. EXCLUDE TIES must still return remaining in-frame rows.
+SELECT k, first_value(k) OVER w, nth_value(k, 1) OVER w AS nth_1,
+ nth_value(k, 2) OVER w AS nth_2, 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);
+
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
--
2.53.0
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
2026-09-30 03:34 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>
2026-09-30 13:25 ` Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-09-30 14:33 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-10-05 02:39 ` 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>
0 siblings, 1 reply; 4+ messages in thread
From: Andrey Rachitskiy @ 2026-09-30 14:33 UTC (permalink / raw)
To: theshallow27@gmail.com; pgsql-bugs@lists.postgresql.org
ср, 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 a
preceding frame.
Attachments:
[text/x-patch] v2-0001-Fix-first_value-nth_value-and-last_value-with-EXC.patch (5.5K, ../../CAB8bMit1Y-YyDowBZ87Fxn6qKz_010EauUrj6K5J4_r_zQaWHw@mail.gmail.com/3-v2-0001-Fix-first_value-nth_value-and-last_value-with-EXC.patch)
download | inline diff:
From 3fdd05e3c584efec56a049c501f1973735c15e66 Mon Sep 17 00:00:00 2001
From: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Date: Wed, 30 Sep 2026 18:04:07 +0500
Subject: [PATCH v2] Fix first_value, nth_value, and last_value with EXCLUDE
TIES.
WinGetFuncArgInFrame remaps the first overlapping peer to currentpos
for EXCLUDE TIES, and the last overlapping peer when seeking from the
frame tail. That is only valid when the current row is in the frame.
With a ROWS frame that starts on a peer, such as 1 FOLLOWING AND
UNBOUNDED FOLLOWING, the remap lands on the current row, which is then
rejected as out of frame. first_value and nth_value return NULL even
though later in-frame rows remain. last_value has the same hole when
the frame ends on a peer.
Aggregates over the same window already saw those rows, because they
walk the frame with row_is_in_frame.
If the current row is before the frame head, skip the overlapping
peer group as EXCLUDE GROUP does. Do the same from the tail when the
current row is at or after the frame tail.
Author: Andrey Rachitskiy <pl0h0yp1@gmail.com>
Reported-by: Shallow <theshallow27@gmail.com>
Discussion: https://www.postgresql.org/message-id/19732-d4eed889e3021ef8@postgresql.org
---
src/backend/executor/nodeWindowAgg.c | 29 +++++++++++++++++-----------
src/test/regress/expected/window.out | 23 ++++++++++++++++++++++
src/test/regress/sql/window.sql | 11 +++++++++++
3 files changed, 52 insertions(+), 11 deletions(-)
diff --git a/src/backend/executor/nodeWindowAgg.c b/src/backend/executor/nodeWindowAgg.c
index b86dcbba055..0b41eb4739e 100644
--- a/src/backend/executor/nodeWindowAgg.c
+++ b/src/backend/executor/nodeWindowAgg.c
@@ -4062,11 +4062,8 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
* row's peer group from resulting in trying to fetch a row before
* some previous mark position.
*
- * Note that in some corner cases such as current row being
- * outside frame, these calculations are theoretically too simple,
- * but it doesn't matter because we'll end up deciding the row is
- * out of frame. We do not attempt to avoid fetching rows past
- * end of frame; that would happen in some cases anyway.
+ * We do not attempt to avoid fetching rows past end of frame.
+ * That would happen in some cases anyway.
*/
switch (winstate->frameOptions & FRAMEOPTION_EXCLUSION)
{
@@ -4097,10 +4094,15 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
int64 overlapstart = Max(winstate->groupheadpos,
winstate->frameheadpos);
- if (abs_pos == overlapstart)
- abs_pos = winstate->currentpos;
+ if (winstate->currentpos >= winstate->frameheadpos)
+ {
+ if (abs_pos == overlapstart)
+ abs_pos = winstate->currentpos;
+ else
+ abs_pos += winstate->grouptailpos - overlapstart - 1;
+ }
else
- abs_pos += winstate->grouptailpos - overlapstart - 1;
+ abs_pos += winstate->grouptailpos - overlapstart;
}
break;
default:
@@ -4163,10 +4165,15 @@ WinGetFuncArgInFrame(WindowObject winobj, int argno,
int64 overlapend = Min(winstate->grouptailpos,
winstate->frametailpos);
- if (abs_pos == overlapend - 1)
- abs_pos = winstate->currentpos;
+ if (winstate->currentpos < winstate->frametailpos)
+ {
+ if (abs_pos == overlapend - 1)
+ abs_pos = winstate->currentpos;
+ else
+ abs_pos -= overlapend - 1 - winstate->groupheadpos;
+ }
else
- abs_pos -= overlapend - 1 - winstate->groupheadpos;
+ abs_pos -= overlapend - winstate->groupheadpos;
}
update_frameheadpos(winstate);
if (abs_pos < winstate->frameheadpos)
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index c0bde1c5eec..d64060a91b1 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -1037,6 +1037,29 @@ FROM tenk1 WHERE unique1 < 10;
7 | 7 | 3
(10 rows)
+-- first_value/last_value with EXCLUDE TIES when the frame edge is a peer
+SELECT k, first_value(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
+---+-------------
+ 0 | 1
+ 0 | 1
+ 1 |
+(3 rows)
+
+SELECT k, last_value(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
+---+------------
+ 0 |
+ 1 | 0
+ 1 | 0
+(3 rows)
+
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 8e6f92d94c7..e74c00d668f 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -235,6 +235,17 @@ SELECT last_value(unique1) over (ORDER BY four rows between current row and 2 fo
unique1, four
FROM tenk1 WHERE unique1 < 10;
+-- first_value/last_value with EXCLUDE TIES when the frame edge is a peer
+SELECT k, first_value(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
+FROM (VALUES (0), (1), (1)) t(k)
+WINDOW w AS (ORDER BY k ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
+ EXCLUDE TIES);
+
SELECT sum(unique1) over (rows between 2 preceding and 1 preceding),
unique1, four
FROM tenk1 WHERE unique1 < 10;
--
2.53.0
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f
2026-09-30 03:34 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>
2026-09-30 13:25 ` Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-30 14:33 ` Re: BUG #19732: first_value/last_value/nth_value return NULL with EXCLUDE TIES when the current row is outside its f Andrey Rachitskiy <pl0h0yp1@gmail.com>
@ 2026-10-05 02:39 ` shihao zhong <zhong950419@gmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: shihao zhong @ 2026-10-05 02:39 UTC (permalink / raw)
To: Andrey Rachitskiy <pl0h0yp1@gmail.com>; +Cc: theshallow27@gmail.com; pgsql-bugs@lists.postgresql.org
Hi Andrey,
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);
On Master it report ERROR: cannot fetch row before WindowObject's mark
position
This patch also covers 19731
Thanks,
Shihao
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2026-10-05 02:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-30 03:34 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>
2026-09-30 13:25 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-09-30 14:33 ` Andrey Rachitskiy <pl0h0yp1@gmail.com>
2026-10-05 02:39 ` shihao zhong <zhong950419@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox