public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chengpeng Yan <[email protected]>
To: [email protected] <[email protected]>
To: [email protected] <[email protected]>
Cc: David Rowley <[email protected]>
Subject: Re: BUG #19533: Wrong results from WindowAgg run-condition pushdown on count() with EXCLUDE CURRENT ROW
Date: Sat, 27 Jun 2026 16:53:37 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
Hi,
> On Jun 24, 2026, at 22:41, PG Bug reporting form <[email protected]> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19533
> Logged by: Qifan Liu
> Email address: [email protected]
> PostgreSQL version: 18.4
> Operating system: Ubuntu 20.04 x86-64, docker image postgres:18.4
> Description:
>
> ## PoC
>
> ```sql
> DROP TABLE IF EXISTS t;
>
> CREATE TABLE t (
> id int PRIMARY KEY,
> k int NOT NULL,
> v int
> );
>
> INSERT INTO t(id, k, v) VALUES
> (1, 1, 1),
> (2, 1, NULL),
> (3, 1, 1),
> (4, 2, 1);
>
> WITH
> source_rows AS (
> SELECT id
> FROM (
> SELECT id,
> count(v) OVER (
> ORDER BY k
> RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
> EXCLUDE CURRENT ROW
> ) AS c
> FROM t
> ) s
> WHERE c <= 1
> ),
> reference_rows AS (
> SELECT t1.id
> FROM t AS t1
> WHERE (
> SELECT count(t2.v)
> FROM t AS t2
> WHERE t2.k <= t1.k
> AND t2.id <> t1.id
> ) <= 1
> ),
> metamorphic_rows AS (
> SELECT id
> FROM (
> SELECT id,
> count(v) OVER (
> ORDER BY k
> RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
> EXCLUDE CURRENT ROW
> ) AS c
> FROM t
> ) s
> WHERE c + 0 <= 1
> )
> SELECT 'source' AS label, coalesce(array_agg(id ORDER BY id), '{}'::int[])
> AS ids
> FROM source_rows
> UNION ALL
> SELECT 'reference', coalesce(array_agg(id ORDER BY id), '{}'::int[])
> FROM reference_rows
> UNION ALL
> SELECT 'metamorphic_no_pushdown', coalesce(array_agg(id ORDER BY id),
> '{}'::int[])
> FROM metamorphic_rows;
>
> EXPLAIN (COSTS OFF)
> SELECT id
> FROM (
> SELECT id,
> count(v) OVER (
> ORDER BY k
> RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
> EXCLUDE CURRENT ROW
> ) AS c
> FROM t
> ) s
> WHERE c <= 1;
> ```
>
> ## Expected Behavior
>
> The source query should return the same rows as the reference query and the
> semantically equivalent metamorphic_no_pushdown query.
>
> ## Actual Behavior
>
> The optimized query returns only `{1}`, while the reference and no-pushdown
> forms return `{1,3}`.
Thanks for the report. I reproduced the wrong result.
The planner can treat count() as a monotonic window function and turn a
qual such as c <= 1 into a WindowAgg run condition. That is not safe
when the frame has an EXCLUDE option, because exclusion is applied after
the frame bounds are found and can depend on the current row or peer
group. In the reported count(v) case, NULL handling can produce values
such as 1, 2, 1, so the run condition can drop a later valid row.
This is not only about NULLs. count(*) has no NULL issue, and some
excluded-frame cases remain monotone, but not all of them. For example,
GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE GROUP can
make count(*) decrease when the current peer group changes.
The attached patch takes the conservative route: int8inc_support() no
longer reports count() as monotonic when the window frame has
FRAMEOPTION_EXCLUSION. The added regression tests cover the reported
case, related EXCLUDE variants, and a count(*) case. They also check
that the excluded-frame query keeps a normal Filter instead of a
WindowAgg Run Condition.
I ran make check, which passed.
Thoughts welcome on whether this is the right boundary, or whether some
narrower excluded-frame cases should still prove monotonicity
separately.
--
Best regards,
Chengpeng Yan
Attachments:
[application/octet-stream] v1-0001-Avoid-count-run-conditions-with-frame-exclusion.patch (13.7K, ../[email protected]/3-v1-0001-Avoid-count-run-conditions-with-frame-exclusion.patch)
download | inline diff:
From fc275fd871c87375f66360135125d853a04384e4 Mon Sep 17 00:00:00 2001
From: Chengpeng Yan <[email protected]>
Date: Sat, 27 Jun 2026 23:59:21 +0800
Subject: [PATCH v1] Avoid count run conditions with frame exclusion
count() support derived window-function monotonicity from frame
bounds. That is not sufficient when the frame uses EXCLUDE, since
excluded rows are chosen after the bounds and may depend on the
current row or peer group.
count() can then decrease even for frames whose bounds alone would
grow monotonically. Do not report count() as monotonic for frames
with exclusion.
Add regression tests for excluded-frame cases, including count(*).
---
src/backend/utils/adt/int8.c | 12 ++
src/test/regress/expected/window.out | 192 +++++++++++++++++++++++++++
src/test/regress/sql/window.sql | 175 ++++++++++++++++++++++++
3 files changed, 379 insertions(+)
diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 9b429da86d9..653f8b028e9 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -795,6 +795,18 @@ int8inc_support(PG_FUNCTION_ARGS)
MonotonicFunction monotonic = MONOTONICFUNC_NONE;
int frameOptions = req->window_clause->frameOptions;
+ /*
+ * Frame exclusion is applied after the frame bounds have been
+ * determined. The excluded rows can depend on the current row or its
+ * peers, so the count is not guaranteed to follow the monotonic
+ * behavior implied by the bounds alone.
+ */
+ if (frameOptions & FRAMEOPTION_EXCLUSION)
+ {
+ req->monotonic = MONOTONICFUNC_NONE;
+ PG_RETURN_POINTER(req);
+ }
+
/* No ORDER BY clause then all rows are peers */
if (req->window_clause->orderClause == NIL)
monotonic = MONOTONICFUNC_BOTH;
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 90d9f953b81..1313e4c71d0 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4375,6 +4375,198 @@ WHERE c <= 3;
-> Seq Scan on empsalary
(7 rows)
+CREATE TEMPORARY TABLE window_exclusion (
+ id int,
+ k int,
+ v int
+);
+INSERT INTO window_exclusion VALUES
+ (1, 1, 1),
+ (2, 1, NULL),
+ (3, 1, 1),
+ (4, 2, 1);
+-- Ensure we don't push down for count() when the frame excludes rows.
+EXPLAIN (COSTS OFF)
+SELECT id
+FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE CURRENT ROW
+ ) AS c
+ FROM window_exclusion
+) s
+WHERE c <= 1;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Subquery Scan on s
+ Filter: (s.c <= 1)
+ -> WindowAgg
+ Window: w1 AS (ORDER BY window_exclusion.k RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: window_exclusion.k
+ -> Seq Scan on window_exclusion
+(7 rows)
+
+SELECT 'exclude_current_row' AS test_name,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE CURRENT ROW
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c <= 1
+ ORDER BY id) AS source,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE CURRENT ROW
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c + 0 <= 1
+ ORDER BY id) AS nopush
+UNION ALL
+SELECT 'exclude_ties',
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE TIES
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c <= 0
+ ORDER BY id),
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE TIES
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c + 0 <= 0
+ ORDER BY id)
+UNION ALL
+SELECT 'no_order_current_row',
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ EXCLUDE CURRENT ROW
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c <= 2
+ ORDER BY id),
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ EXCLUDE CURRENT ROW
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c + 0 <= 2
+ ORDER BY id)
+ORDER BY test_name;
+ test_name | source | nopush
+----------------------+---------+---------
+ exclude_current_row | {1,3} | {1,3}
+ exclude_ties | {2} | {2}
+ no_order_current_row | {1,3,4} | {1,3,4}
+(3 rows)
+
+TRUNCATE window_exclusion;
+INSERT INTO window_exclusion VALUES
+ (1, 1, NULL),
+ (2, 1, NULL),
+ (3, 2, 1),
+ (4, 2, NULL);
+SELECT 'exclude_group' AS test_name,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+ EXCLUDE GROUP
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c <= 0
+ ORDER BY id) AS source,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+ EXCLUDE GROUP
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c + 0 <= 0
+ ORDER BY id) AS nopush;
+ test_name | source | nopush
+---------------+--------+--------
+ exclude_group | {3,4} | {3,4}
+(1 row)
+
+TRUNCATE window_exclusion;
+INSERT INTO window_exclusion VALUES
+ (1, 1, NULL),
+ (2, 2, NULL),
+ (3, 2, NULL),
+ (4, 2, NULL),
+ (5, 2, NULL),
+ (6, 2, NULL);
+SELECT 'count_star_exclude_group' AS test_name,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(*) OVER (
+ ORDER BY k
+ GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+ EXCLUDE GROUP
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c <= 1
+ ORDER BY id) AS source,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(*) OVER (
+ ORDER BY k
+ GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+ EXCLUDE GROUP
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c + 0 <= 1
+ ORDER BY id) AS nopush;
+ test_name | source | nopush
+--------------------------+-------------+-------------
+ count_star_exclude_group | {2,3,4,5,6} | {2,3,4,5,6}
+(1 row)
+
+DROP TABLE window_exclusion;
-- Ensure we don't push down when the window function's monotonic properties
-- don't match that of the clauses.
EXPLAIN (COSTS OFF)
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 5ac3a486e16..1dae00d8def 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1431,6 +1431,181 @@ SELECT * FROM
FROM empsalary) emp
WHERE c <= 3;
+CREATE TEMPORARY TABLE window_exclusion (
+ id int,
+ k int,
+ v int
+);
+
+INSERT INTO window_exclusion VALUES
+ (1, 1, 1),
+ (2, 1, NULL),
+ (3, 1, 1),
+ (4, 2, 1);
+
+-- Ensure we don't push down for count() when the frame excludes rows.
+EXPLAIN (COSTS OFF)
+SELECT id
+FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE CURRENT ROW
+ ) AS c
+ FROM window_exclusion
+) s
+WHERE c <= 1;
+
+SELECT 'exclude_current_row' AS test_name,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE CURRENT ROW
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c <= 1
+ ORDER BY id) AS source,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE CURRENT ROW
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c + 0 <= 1
+ ORDER BY id) AS nopush
+UNION ALL
+SELECT 'exclude_ties',
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE TIES
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c <= 0
+ ORDER BY id),
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
+ EXCLUDE TIES
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c + 0 <= 0
+ ORDER BY id)
+UNION ALL
+SELECT 'no_order_current_row',
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ EXCLUDE CURRENT ROW
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c <= 2
+ ORDER BY id),
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
+ EXCLUDE CURRENT ROW
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c + 0 <= 2
+ ORDER BY id)
+ORDER BY test_name;
+
+TRUNCATE window_exclusion;
+
+INSERT INTO window_exclusion VALUES
+ (1, 1, NULL),
+ (2, 1, NULL),
+ (3, 2, 1),
+ (4, 2, NULL);
+
+SELECT 'exclude_group' AS test_name,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+ EXCLUDE GROUP
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c <= 0
+ ORDER BY id) AS source,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(v) OVER (
+ ORDER BY k
+ GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+ EXCLUDE GROUP
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c + 0 <= 0
+ ORDER BY id) AS nopush;
+
+TRUNCATE window_exclusion;
+
+INSERT INTO window_exclusion VALUES
+ (1, 1, NULL),
+ (2, 2, NULL),
+ (3, 2, NULL),
+ (4, 2, NULL),
+ (5, 2, NULL),
+ (6, 2, NULL);
+
+SELECT 'count_star_exclude_group' AS test_name,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(*) OVER (
+ ORDER BY k
+ GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+ EXCLUDE GROUP
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c <= 1
+ ORDER BY id) AS source,
+ ARRAY(SELECT id
+ FROM (
+ SELECT id,
+ count(*) OVER (
+ ORDER BY k
+ GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING
+ EXCLUDE GROUP
+ ) AS c
+ FROM window_exclusion
+ ) s
+ WHERE c + 0 <= 1
+ ORDER BY id) AS nopush;
+
+DROP TABLE window_exclusion;
+
-- Ensure we don't push down when the window function's monotonic properties
-- don't match that of the clauses.
EXPLAIN (COSTS OFF)
--
2.50.1 (Apple Git-155)
view thread (15+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: BUG #19533: Wrong results from WindowAgg run-condition pushdown on count() with EXCLUDE CURRENT ROW
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox