public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: Richard Guo <[email protected]>
Cc: Chengpeng Yan <[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: Fri, 3 Jul 2026 15:38:34 +1200
Message-ID: <CAApHDvoJ07GM6V2QWyJAgsJcM-dcFS+nwPq4Um=-NsXv5PmoCw@mail.gmail.com> (raw)
In-Reply-To: <CAApHDvqHXZpF9pPStLqPp5O=G2gVpPD959bMTRRkLjdy4Tvx7g@mail.gmail.com>
References: <[email protected]>
<[email protected]>
<CAMbWs4-6umOecRifmC88RW0SHWDAvb4c3-VD8XQ3NJufsUyUOQ@mail.gmail.com>
<CAApHDvqHXZpF9pPStLqPp5O=G2gVpPD959bMTRRkLjdy4Tvx7g@mail.gmail.com>
On Tue, 30 Jun 2026 at 01:44, David Rowley <[email protected]> wrote:
> I think there are also a few equivalences, such as:
>
> "order by k rows between unbounded preceding and current row exclude
> current row"
>
> I believe is logically the same as:
>
> "order by k rows between unbounded preceding and 1 preceding"
>
> Then, if frame_end was 1 PRECEDING with "GROUPS BETWEEN ...", EXCLUDE
> would never see the current row or peer group as the frame ends before
> we get to the current group or row.
>
> Anyway, I'm still trying to decide how to balance this. On one hand,
> there's an incentive not to disable this optimisation for cases that
> are perfectly safe, and on the other hand, there are lots of variables
> here, and "perfectly safe" is a little harder to fathom than I'd like
> for a bug fix. I'm also not keen on adding very complex and
> hard-to-follow logic to the support function. If we can allow a few
> more cases but do so as a result of boiling down the logic into
> something simpler, then I'd likely be more in favour of covering some
> extra cases.
>
> I'll continue to look at this.
I've been playing around with the attached. It doesn't get all the
cases that are ok. For example, in theory, I believe 0 FOLLOWING is
the same as CURRENT ROW for the frame_end. I've not made that work
exactly the same as I don't have the same wfunc->winfnoid ==
F_COUNT_ANY check that I added to CURRENT ROW for 0 FOLLOWING. I was
trying to strike some sort of balance between not regressing things
unnecessarily and having to add too much code for cases that are
unlikely ever to be used. As far as I'm aware, for GROUPS and ROWS
mode, 0 FOLLOWING is the same as CURRENT ROW, so I suspect most people
would just use CURRENT ROW.
It's a little hard for me to know what to do here, as I really don't
know how commonly people use the EXCLUDE clause, and more so, how
often the Run Condition optimisation will apply when those are used.
It would be good to have an idea if the bug discovery was from a
real-world case, or if it was discovered from tooling, or from looking
at the code.
Does anyone think we should cover more or fewer cases? Or see any
problems with this patch?
David
From d1874e3f184240ff9498c35ae7bfd6072d504d29 Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Fri, 3 Jul 2026 14:17:42 +1200
Subject: [PATCH v1] Fix COUNT's logic for window run condition support
9d9c02ccd added code to allow the executor to stop early when processing
WindowAgg nodes where a monotonic window function starts producing
values that result in a pushed-down qual no longer matching, and will
never match again due to the window function's monotonic properties.
That commit requires a SupportRequestWFuncMonotonic to exist on the
window function and for it to detect when the function is monotonic. For
COUNT(ANY) and COUNT(*), the support function failed to consider some
cases where the WindowClause used EXCLUDE to exclude certain rows from
being aggregated. Some WindowClause definitions mean we aggregate rows
that come after the current row, and when processing those rows later,
if we EXCLUDE certain rows, the monotonic property can be broken.
Here, we disable the "Run Condition" pushdown optimization for COUNT(ANY)
and COUNT(*) when the window clause has an EXCLUDE clause and the
properties of that clause, plus the frame bounds result in the monotonic
properties not being guaranteed.
Bug: #19533
Reported-by: Qifan Liu <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/utils/adt/int8.c | 101 ++++++-
src/test/regress/expected/window.out | 436 +++++++++++++++++++++++++++
src/test/regress/sql/window.sql | 215 +++++++++++++
3 files changed, 751 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 9b429da86d9..e14aa751478 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -795,7 +795,106 @@ int8inc_support(PG_FUNCTION_ARGS)
MonotonicFunction monotonic = MONOTONICFUNC_NONE;
int frameOptions = req->window_clause->frameOptions;
- /* No ORDER BY clause then all rows are peers */
+ /*
+ * When the frame options has an EXCLUDE clause, this can affect the
+ * monotonic guarantees. Various cases are handled or rejected below.
+ */
+ if (frameOptions & FRAMEOPTION_EXCLUSION)
+ {
+ WindowFunc *wfunc = req->window_func;
+
+ if (frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING)
+ {
+ Node *endOffset = req->window_clause->endOffset;
+
+ /*
+ * ROWS mode with EXCLUDE CURRENT ROW is fine, as the count
+ * cannot include rows beyond the current row. With GROUPS or
+ * RANGE, peer rows would be included and those peers could be
+ * after the current row.
+ */
+ if (frameOptions & FRAMEOPTION_ROWS &&
+ frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW)
+ {
+ /* Allowed */
+ }
+
+ /*
+ * Otherwise, EXCLUDE is fine when in GROUPS or ROWS mode when
+ * we have an above zero Const in the N PRECEDING. This means
+ * all the would-be-excluded rows are never in the frame's
+ * scope. We allow any value of PRECEDING, including
+ * non-consts when EXCLUDE GROUP is used, as effectively
+ * that's 1 PRECEDING. The executor ensures the value is
+ * never negative. Range mode is disallowed as the endOffset
+ * has a different purpose.
+ */
+ else if ((frameOptions & FRAMEOPTION_EXCLUDE_GROUP) == 0)
+ {
+ endOffset = eval_const_expressions(NULL, endOffset);
+
+ if (frameOptions & FRAMEOPTION_RANGE ||
+ !IsA(endOffset, Const) || castNode(Const, endOffset)->constisnull ||
+ DatumGetInt64(castNode(Const, endOffset)->constvalue) <= 0)
+ {
+ req->monotonic = MONOTONICFUNC_NONE;
+ PG_RETURN_POINTER(req);
+ }
+ }
+ }
+
+ else if (frameOptions & FRAMEOPTION_END_CURRENT_ROW)
+ {
+ /*
+ * When the frame ends at CURRENT ROW, we must disallow
+ * COUNT(ANY) when in RANGE or GROUPS mode unless we're
+ * excluding the entire peer group. Some values being counted
+ * may be NULL, which could result in the count going up with
+ * EXCLUDE CURRENT ROW, or down with EXCLUDE TIES. ROWS mode
+ * is fine as the count doesn't include peer rows ahead of the
+ * current row.
+ */
+ if (wfunc->winfnoid == F_COUNT_ANY &&
+ frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS) &&
+ frameOptions & (FRAMEOPTION_EXCLUDE_CURRENT_ROW | FRAMEOPTION_EXCLUDE_TIES))
+ {
+ req->monotonic = MONOTONICFUNC_NONE;
+ PG_RETURN_POINTER(req);
+ }
+
+ /*
+ * COUNT(*) doesn't have the same problem as COUNT(ANY) since
+ * rows in the peer group are always counted with GROUPS and
+ * RANGE modes, and all the EXCLUDE options always exclude the
+ * same number of rows, just not always the same rows.
+ */
+ }
+
+ else if (frameOptions & FRAMEOPTION_END_OFFSET_FOLLOWING)
+ {
+ Node *endOffset = req->window_clause->endOffset;
+
+ endOffset = eval_const_expressions(NULL, endOffset);
+
+ /*
+ * If the frame ends beyond the current row or peer group, we
+ * can't prove any monotonic properties, as counting rows that
+ * may later be excluded by EXCLUDE could result in the count
+ * going down. Technically, someone could write 0 FOLLOWING,
+ * which is the same as CURRENT ROW in GROUPS or ROWS mode, so
+ * don't disallow that.
+ */
+ if (frameOptions & FRAMEOPTION_RANGE ||
+ !IsA(endOffset, Const) || castNode(Const, endOffset)->constisnull ||
+ DatumGetInt64(castNode(Const, endOffset)->constvalue) > 0)
+ {
+ req->monotonic = MONOTONICFUNC_NONE;
+ PG_RETURN_POINTER(req);
+ }
+ }
+ }
+
+ /* Otherwise, no ORDER BY clause means all rows are peers */
if (req->window_clause->orderClause == NIL)
monotonic = MONOTONICFUNC_BOTH;
else
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 90d9f953b81..ce3d6291d29 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4436,6 +4436,442 @@ 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 0 PRECEDING when we exclude the entire peer 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
+------------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE GROUP)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure we don't get pushdown with EXCLUDE CURRENT ROW
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS 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 GROUPS 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 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)
+
+-- Ensure pushdown occurs with 0 PRECEDING when in ROWS mode and only
+-- excluding the current row
+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)
+
+-- EXCLUDE GROUP is also fine to push down the run condition with 0 PRECEDING
+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
+----------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE GROUP)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure we don't do run condition pushdown for 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 the following 6 tests do pushdown the run condition.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN UNBOUNDED PRECEDING AND '1'::bigint PRECEDING EXCLUDE GROUP)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 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 '1'::bigint PRECEDING EXCLUDE CURRENT ROW)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN UNBOUNDED PRECEDING AND '1'::bigint PRECEDING EXCLUDE TIES)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 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 '1'::bigint PRECEDING EXCLUDE CURRENT ROW)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '1'::bigint PRECEDING EXCLUDE GROUP)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '1'::bigint PRECEDING EXCLUDE TIES)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- For the following 4 tests, ensure there's no pushdown with COUNT(ANY) when
+-- excluding rows from the current peer group.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 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 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 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 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 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 GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 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 CURRENT ROW EXCLUDE TIES)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure the following 2 tests push down the run condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) 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 GROUP)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) 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 GROUP)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure the following 3 ROWS mode tests also push down
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS 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 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure RANGE mode with 1 FOLLOWING does not push down the run condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING 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 RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure there's no run condition pushdown with a non-const N FOLLOWING value
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND random(1,10) FOLLOWING 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 random(1, 10) FOLLOWING EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure we push down the run condition with 0 following, which is the same as CURRENT ROW.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING 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 FOLLOWING EXCLUDE CURRENT ROW)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 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..6465436a153 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1460,6 +1460,221 @@ 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 0 PRECEDING when we exclude the entire peer 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 EXCLUDE CURRENT ROW
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW) 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 GROUPS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+
+-- Ensure pushdown occurs with 0 PRECEDING when in ROWS mode and only
+-- excluding the current row
+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;
+
+-- EXCLUDE GROUP is also fine to push down the run condition with 0 PRECEDING
+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 do run condition pushdown for 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 the following 6 tests do pushdown the run condition.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+
+-- For the following 4 tests, ensure there's no pushdown with COUNT(ANY) when
+-- excluding rows from the current peer group.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure the following 2 tests push down the run condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure the following 3 ROWS mode tests also push down
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure RANGE mode with 1 FOLLOWING does not push down the run condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure there's no run condition pushdown with a non-const N FOLLOWING value
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND random(1,10) FOLLOWING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure we push down the run condition with 0 following, which is the same as CURRENT ROW.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
-- Test Sort node collapsing
EXPLAIN (COSTS OFF)
SELECT * FROM
--
2.54.0.windows.1
Attachments:
[text/plain] v1-0001-Fix-COUNT-s-logic-for-window-run-condition-suppor.patch (31.5K, ../CAApHDvoJ07GM6V2QWyJAgsJcM-dcFS+nwPq4Um=-NsXv5PmoCw@mail.gmail.com/2-v1-0001-Fix-COUNT-s-logic-for-window-run-condition-suppor.patch)
download | inline diff:
From d1874e3f184240ff9498c35ae7bfd6072d504d29 Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Fri, 3 Jul 2026 14:17:42 +1200
Subject: [PATCH v1] Fix COUNT's logic for window run condition support
9d9c02ccd added code to allow the executor to stop early when processing
WindowAgg nodes where a monotonic window function starts producing
values that result in a pushed-down qual no longer matching, and will
never match again due to the window function's monotonic properties.
That commit requires a SupportRequestWFuncMonotonic to exist on the
window function and for it to detect when the function is monotonic. For
COUNT(ANY) and COUNT(*), the support function failed to consider some
cases where the WindowClause used EXCLUDE to exclude certain rows from
being aggregated. Some WindowClause definitions mean we aggregate rows
that come after the current row, and when processing those rows later,
if we EXCLUDE certain rows, the monotonic property can be broken.
Here, we disable the "Run Condition" pushdown optimization for COUNT(ANY)
and COUNT(*) when the window clause has an EXCLUDE clause and the
properties of that clause, plus the frame bounds result in the monotonic
properties not being guaranteed.
Bug: #19533
Reported-by: Qifan Liu <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/utils/adt/int8.c | 101 ++++++-
src/test/regress/expected/window.out | 436 +++++++++++++++++++++++++++
src/test/regress/sql/window.sql | 215 +++++++++++++
3 files changed, 751 insertions(+), 1 deletion(-)
diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 9b429da86d9..e14aa751478 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -795,7 +795,106 @@ int8inc_support(PG_FUNCTION_ARGS)
MonotonicFunction monotonic = MONOTONICFUNC_NONE;
int frameOptions = req->window_clause->frameOptions;
- /* No ORDER BY clause then all rows are peers */
+ /*
+ * When the frame options has an EXCLUDE clause, this can affect the
+ * monotonic guarantees. Various cases are handled or rejected below.
+ */
+ if (frameOptions & FRAMEOPTION_EXCLUSION)
+ {
+ WindowFunc *wfunc = req->window_func;
+
+ if (frameOptions & FRAMEOPTION_END_OFFSET_PRECEDING)
+ {
+ Node *endOffset = req->window_clause->endOffset;
+
+ /*
+ * ROWS mode with EXCLUDE CURRENT ROW is fine, as the count
+ * cannot include rows beyond the current row. With GROUPS or
+ * RANGE, peer rows would be included and those peers could be
+ * after the current row.
+ */
+ if (frameOptions & FRAMEOPTION_ROWS &&
+ frameOptions & FRAMEOPTION_EXCLUDE_CURRENT_ROW)
+ {
+ /* Allowed */
+ }
+
+ /*
+ * Otherwise, EXCLUDE is fine when in GROUPS or ROWS mode when
+ * we have an above zero Const in the N PRECEDING. This means
+ * all the would-be-excluded rows are never in the frame's
+ * scope. We allow any value of PRECEDING, including
+ * non-consts when EXCLUDE GROUP is used, as effectively
+ * that's 1 PRECEDING. The executor ensures the value is
+ * never negative. Range mode is disallowed as the endOffset
+ * has a different purpose.
+ */
+ else if ((frameOptions & FRAMEOPTION_EXCLUDE_GROUP) == 0)
+ {
+ endOffset = eval_const_expressions(NULL, endOffset);
+
+ if (frameOptions & FRAMEOPTION_RANGE ||
+ !IsA(endOffset, Const) || castNode(Const, endOffset)->constisnull ||
+ DatumGetInt64(castNode(Const, endOffset)->constvalue) <= 0)
+ {
+ req->monotonic = MONOTONICFUNC_NONE;
+ PG_RETURN_POINTER(req);
+ }
+ }
+ }
+
+ else if (frameOptions & FRAMEOPTION_END_CURRENT_ROW)
+ {
+ /*
+ * When the frame ends at CURRENT ROW, we must disallow
+ * COUNT(ANY) when in RANGE or GROUPS mode unless we're
+ * excluding the entire peer group. Some values being counted
+ * may be NULL, which could result in the count going up with
+ * EXCLUDE CURRENT ROW, or down with EXCLUDE TIES. ROWS mode
+ * is fine as the count doesn't include peer rows ahead of the
+ * current row.
+ */
+ if (wfunc->winfnoid == F_COUNT_ANY &&
+ frameOptions & (FRAMEOPTION_RANGE | FRAMEOPTION_GROUPS) &&
+ frameOptions & (FRAMEOPTION_EXCLUDE_CURRENT_ROW | FRAMEOPTION_EXCLUDE_TIES))
+ {
+ req->monotonic = MONOTONICFUNC_NONE;
+ PG_RETURN_POINTER(req);
+ }
+
+ /*
+ * COUNT(*) doesn't have the same problem as COUNT(ANY) since
+ * rows in the peer group are always counted with GROUPS and
+ * RANGE modes, and all the EXCLUDE options always exclude the
+ * same number of rows, just not always the same rows.
+ */
+ }
+
+ else if (frameOptions & FRAMEOPTION_END_OFFSET_FOLLOWING)
+ {
+ Node *endOffset = req->window_clause->endOffset;
+
+ endOffset = eval_const_expressions(NULL, endOffset);
+
+ /*
+ * If the frame ends beyond the current row or peer group, we
+ * can't prove any monotonic properties, as counting rows that
+ * may later be excluded by EXCLUDE could result in the count
+ * going down. Technically, someone could write 0 FOLLOWING,
+ * which is the same as CURRENT ROW in GROUPS or ROWS mode, so
+ * don't disallow that.
+ */
+ if (frameOptions & FRAMEOPTION_RANGE ||
+ !IsA(endOffset, Const) || castNode(Const, endOffset)->constisnull ||
+ DatumGetInt64(castNode(Const, endOffset)->constvalue) > 0)
+ {
+ req->monotonic = MONOTONICFUNC_NONE;
+ PG_RETURN_POINTER(req);
+ }
+ }
+ }
+
+ /* Otherwise, no ORDER BY clause means all rows are peers */
if (req->window_clause->orderClause == NIL)
monotonic = MONOTONICFUNC_BOTH;
else
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 90d9f953b81..ce3d6291d29 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4436,6 +4436,442 @@ 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 0 PRECEDING when we exclude the entire peer 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
+------------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE GROUP)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure we don't get pushdown with EXCLUDE CURRENT ROW
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS 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 GROUPS 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 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)
+
+-- Ensure pushdown occurs with 0 PRECEDING when in ROWS mode and only
+-- excluding the current row
+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)
+
+-- EXCLUDE GROUP is also fine to push down the run condition with 0 PRECEDING
+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
+----------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE GROUP)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure we don't do run condition pushdown for 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 the following 6 tests do pushdown the run condition.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN UNBOUNDED PRECEDING AND '1'::bigint PRECEDING EXCLUDE GROUP)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING 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 '1'::bigint PRECEDING EXCLUDE CURRENT ROW)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN UNBOUNDED PRECEDING AND '1'::bigint PRECEDING EXCLUDE TIES)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 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 '1'::bigint PRECEDING EXCLUDE CURRENT ROW)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+----------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '1'::bigint PRECEDING EXCLUDE GROUP)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '1'::bigint PRECEDING EXCLUDE TIES)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- For the following 4 tests, ensure there's no pushdown with COUNT(ANY) when
+-- excluding rows from the current peer group.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 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 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 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 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 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 GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 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 CURRENT ROW EXCLUDE TIES)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure the following 2 tests push down the run condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) 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 GROUP)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) 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 GROUP)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure the following 3 ROWS mode tests also push down
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS 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 ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+------------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+ QUERY PLAN
+-----------------------------------------------------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 rows)
+
+-- Ensure RANGE mode with 1 FOLLOWING does not push down the run condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING 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 RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure there's no run condition pushdown with a non-const N FOLLOWING value
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND random(1,10) FOLLOWING 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 random(1, 10) FOLLOWING EXCLUDE CURRENT ROW)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(7 rows)
+
+-- Ensure we push down the run condition with 0 following, which is the same as CURRENT ROW.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING 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 FOLLOWING EXCLUDE CURRENT ROW)
+ Run Condition: (count(*) OVER w1 <= 3)
+ -> Sort
+ Sort Key: empsalary.salary
+ -> Seq Scan on empsalary
+(6 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..6465436a153 100644
--- a/src/test/regress/sql/window.sql
+++ b/src/test/regress/sql/window.sql
@@ -1460,6 +1460,221 @@ 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 0 PRECEDING when we exclude the entire peer 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 EXCLUDE CURRENT ROW
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE CURRENT ROW) 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 GROUPS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+
+-- Ensure pushdown occurs with 0 PRECEDING when in ROWS mode and only
+-- excluding the current row
+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;
+
+-- EXCLUDE GROUP is also fine to push down the run condition with 0 PRECEDING
+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 do run condition pushdown for 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 the following 6 tests do pushdown the run condition.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+
+-- For the following 4 tests, ensure there's no pushdown with COUNT(ANY) when
+-- excluding rows from the current peer group.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure the following 2 tests push down the run condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure the following 3 ROWS mode tests also push down
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE GROUP) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW EXCLUDE TIES) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure RANGE mode with 1 FOLLOWING does not push down the run condition
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary RANGE BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure there's no run condition pushdown with a non-const N FOLLOWING value
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND random(1,10) FOLLOWING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
+-- Ensure we push down the run condition with 0 following, which is the same as CURRENT ROW.
+EXPLAIN (COSTS OFF)
+SELECT * FROM
+ (SELECT empno,
+ salary,
+ count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND 0 FOLLOWING EXCLUDE CURRENT ROW) c
+ FROM empsalary) emp
+WHERE c <= 3;
+
-- Test Sort node collapsing
EXPLAIN (COSTS OFF)
SELECT * FROM
--
2.54.0.windows.1
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]
Subject: Re: BUG #19533: Wrong results from WindowAgg run-condition pushdown on count() with EXCLUDE CURRENT ROW
In-Reply-To: <CAApHDvoJ07GM6V2QWyJAgsJcM-dcFS+nwPq4Um=-NsXv5PmoCw@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