public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Chengpeng Yan <[email protected]>
Cc: John Naylor <[email protected]>
Cc: Richard Guo <[email protected]>
Cc: [email protected] <[email protected]>
Cc: [email protected] <[email protected]>
Subject: Re: BUG #19533: Wrong results from WindowAgg run-condition pushdown on count() with EXCLUDE CURRENT ROW
Date: Mon, 6 Jul 2026 19:24:54 +1200
Message-ID: <CAApHDvpKHEZXVDsaAyJ9pAkX3KH34jC83spXPjJPyrtrsKk_HA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<CAMbWs4-6umOecRifmC88RW0SHWDAvb4c3-VD8XQ3NJufsUyUOQ@mail.gmail.com>
<CAApHDvqHXZpF9pPStLqPp5O=G2gVpPD959bMTRRkLjdy4Tvx7g@mail.gmail.com>
<CAApHDvoJ07GM6V2QWyJAgsJcM-dcFS+nwPq4Um=-NsXv5PmoCw@mail.gmail.com>
<CANWCAZbiYQ+=CHAaftwp2x83Gy6tJCB8GUe15yO5DW-cpp0Mxg@mail.gmail.com>
<CAApHDvrN-HL=dSBez=pme5RQKCiH7SrsMEFV-eQRJN3Vs94ZEQ@mail.gmail.com>
<CANWCAZap+k7-E2f-XoC36Hk4izZjbN8w2c9OuomummGfZ5d20w@mail.gmail.com>
<[email protected]>
On Mon, 6 Jul 2026 at 00:54, Chengpeng Yan <[email protected]> wrote:
> count(*) FILTER (WHERE ...)
Yeah, looks like we'll need to disable if there's a FILTER clause.
> int exclusion = frameOptions & FRAMEOPTION_EXCLUSION;
> bool plain_count_star = req->window_func->winfnoid == F_COUNT_ &&
> req->window_func->aggfilter == NULL;
>
> if (exclusion &&
> (exclusion != FRAMEOPTION_EXCLUDE_CURRENT_ROW || !plain_count_star))
> {
> req->monotonic = MONOTONICFUNC_NONE;
> PG_RETURN_POINTER(req);
> }
> ```
Given how complex Claude's logic became to get all those cases
correct, I now think the above is the best option. I've attached a
patch for that.
David
Attachments:
[application/octet-stream] v4_fix_count_monotonic_detection.patch (15.9K, ../CAApHDvpKHEZXVDsaAyJ9pAkX3KH34jC83spXPjJPyrtrsKk_HA@mail.gmail.com/2-v4_fix_count_monotonic_detection.patch)
download | inline diff:
diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 9b429da86d9..5e22b0e7e9c 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -795,8 +795,32 @@ int8inc_support(PG_FUNCTION_ARGS)
MonotonicFunction monotonic = MONOTONICFUNC_NONE;
int frameOptions = req->window_clause->frameOptions;
- /* No ORDER BY clause then all rows are peers */
- if (req->window_clause->orderClause == NIL)
+ /*
+ * EXCLUDE clauses in the window clause only support monotonic
+ * properties in a very narrow set of circumstances.
+ */
+ if (frameOptions & FRAMEOPTION_EXCLUSION)
+ {
+ WindowFunc *wfunc = req->window_func;
+
+ /*
+ * To add handling for all valid monotonic cases with an EXCLUDE
+ * clause is complex and likely not worth troubling over. For
+ * now, just bail unless we see EXCLUDE CURRENT ROW with COUNT(*)
+ * and no FILTER.
+ */
+ if ((frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW) == 0 ||
+ wfunc->winfnoid != F_COUNT_ ||
+ wfunc->aggfilter != NULL)
+ {
+ req->monotonic = MONOTONICFUNC_NONE;
+ PG_RETURN_POINTER(req);
+ }
+ }
+
+ /* No ORDER BY clause and RANGE mode means all rows are peers. */
+ if (req->window_clause->orderClause == NIL &&
+ (frameOptions & FRAMEOPTION_RANGE))
monotonic = MONOTONICFUNC_BOTH;
else
{
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 90d9f953b81..8841063de0e 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4232,23 +4232,60 @@ WHERE c <= 3;
(8 rows)
-- Ensure we get the correct run condition when the window function is both
--- monotonically increasing and decreasing.
+-- monotonically increasing and decreasing in RANGE mode without an ORDER BY
EXPLAIN (COSTS OFF)
SELECT * FROM
(SELECT empno,
depname,
salary,
- count(empno) OVER () c
+ count(empno) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) c
FROM empsalary) emp
WHERE c = 1;
- QUERY PLAN
--------------------------------------------------------
+ QUERY PLAN
+-------------------------------------------------------------
WindowAgg
- Window: w1 AS ()
+ Window: w1 AS (RANGE BETWEEN CURRENT ROW AND CURRENT ROW)
Run Condition: (count(empsalary.empno) OVER w1 = 1)
-> Seq Scan on empsalary
(4 rows)
+-- Ensure that ROWS mode without an ORDER BY doesn't think it's monotonically
+-- decreasing, i.e. don't push down the run condition.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ depname,
+ salary,
+ count(empno) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c > 1;
+ QUERY PLAN
+------------------------------------------------------------------
+ Subquery Scan on emp
+ Filter: (emp.c > 1)
+ -> WindowAgg
+ Window: w1 AS (ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
+ -> Seq Scan on empsalary
+(5 rows)
+
+-- As above, but check we detect it's monotonically increasing
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ depname,
+ salary,
+ count(empno) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+------------------------------------------------------------------
+ Subquery Scan on emp
+ Filter: (emp.c <= 3)
+ -> WindowAgg
+ Window: w1 AS (ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
+ -> Seq Scan on empsalary
+(5 rows)
+
-- Try another case with a WindowFunc with a byref return type
SELECT * FROM
(SELECT row_number() OVER (PARTITION BY salary) AS rn,
@@ -4436,6 +4473,181 @@ WHERE c = 1;
-> Seq Scan on empsalary
(9 rows)
+--
+-- Ensure we get the correct behavior for run condition pushdown when the
+-- frame option has an EXCLUDE clause
+--
+-- Ensure pushdown occurs for ROWS BETWEEN UNBOUNDED PRECEDING with EXCLUDE
+-- CURRENT ROW with COUNT(*)
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE CURRENT ROW)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure pushdown occurs for GROUPS BETWEEN UNBOUNDED PRECEDING with EXCLUDE
+-- CURRENT ROW with COUNT(*)
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+--------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure pushdown occurs for RANGE BETWEEN UNBOUNDED PRECEDING with EXCLUDE
+-- CURRENT ROW with COUNT(*)
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+-------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure we don't get pushdown when a FILTER clause is present
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) FILTER (WHERE salary > 4000) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------
+ Subquery Scan on emp
+ Filter: (emp.c <= 3)
+ -> WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure we don't get pushdown with COUNT(ANY)
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------------
+ Subquery Scan on emp
+ Filter: (emp.c <= 3)
+ -> WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure we don't get pushdown with EXCLUDE GROUP
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------------
+ Subquery Scan on emp
+ Filter: (emp.c <= 3)
+ -> WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE GROUP)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure we don't get pushdown with EXCLUDE TIES
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------------
+ Subquery Scan on emp
+ Filter: (emp.c <= 3)
+ -> WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE TIES)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure we don't get pushdown with GROUPS mode and EXCLUDE GROUP
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------------
+ Subquery Scan on emp
+ Filter: (emp.c <= 3)
+ -> WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE GROUP)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure we don't get pushdown with GROUPS mode and EXCLUDE TIES
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------------
+ Subquery Scan on emp
+ Filter: (emp.c <= 3)
+ -> WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE TIES)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
-- Test Sort node collapsing
EXPLAIN (COSTS OFF)
SELECT * FROM
diff --git a/src/test/regress/sql/window.sql b/src/test/regress/sql/window.sql
index 5ac3a486e16..1f8e8c87e71 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1361,16 +1361,37 @@ SELECT * FROM
WHERE c <= 3;
-- Ensure we get the correct run condition when the window function is both
--- monotonically increasing and decreasing.
+-- monotonically increasing and decreasing in RANGE mode without an ORDER BY
EXPLAIN (COSTS OFF)
SELECT * FROM
(SELECT empno,
depname,
salary,
- count(empno) OVER () c
+ count(empno) OVER (RANGE BETWEEN CURRENT ROW AND CURRENT ROW) c
FROM empsalary) emp
WHERE c = 1;
+-- Ensure that ROWS mode without an ORDER BY doesn't think it's monotonically
+-- decreasing, i.e. don't push down the run condition.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ depname,
+ salary,
+ count(empno) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c > 1;
+
+-- As above, but check we detect it's monotonically increasing
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ depname,
+ salary,
+ count(empno) OVER (ROWS BETWEEN CURRENT ROW AND CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
-- Try another case with a WindowFunc with a byref return type
SELECT * FROM
(SELECT row_number() OVER (PARTITION BY salary) AS rn,
@@ -1460,6 +1481,96 @@ SELECT * FROM
FROM empsalary) emp
WHERE c = 1;
+--
+-- Ensure we get the correct behavior for run condition pushdown when the
+-- frame option has an EXCLUDE clause
+--
+
+-- Ensure pushdown occurs for ROWS BETWEEN UNBOUNDED PRECEDING with EXCLUDE
+-- CURRENT ROW with COUNT(*)
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure pushdown occurs for GROUPS BETWEEN UNBOUNDED PRECEDING with EXCLUDE
+-- CURRENT ROW with COUNT(*)
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure pushdown occurs for RANGE BETWEEN UNBOUNDED PRECEDING with EXCLUDE
+-- CURRENT ROW with COUNT(*)
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure we don't get pushdown when a FILTER clause is present
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) FILTER (WHERE salary > 4000) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure we don't get pushdown with COUNT(ANY)
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(salary) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure we don't get pushdown with EXCLUDE GROUP
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure we don't get pushdown with EXCLUDE TIES
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure we don't get pushdown with GROUPS mode and EXCLUDE GROUP
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure we don't get pushdown with GROUPS mode and EXCLUDE TIES
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+
-- Test Sort node collapsing
EXPLAIN (COSTS OFF)
SELECT * FROM
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], [email protected], [email protected]
Subject: Re: BUG #19533: Wrong results from WindowAgg run-condition pushdown on count() with EXCLUDE CURRENT ROW
In-Reply-To: <CAApHDvpKHEZXVDsaAyJ9pAkX3KH34jC83spXPjJPyrtrsKk_HA@mail.gmail.com>
* 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