public inbox for [email protected]
help / color / mirror / Atom feedFrom: David Rowley <[email protected]>
To: John Naylor <[email protected]>
Cc: 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: Sat, 4 Jul 2026 11:57:23 +1200
Message-ID: <CAApHDvrN-HL=dSBez=pme5RQKCiH7SrsMEFV-eQRJN3Vs94ZEQ@mail.gmail.com> (raw)
In-Reply-To: <CANWCAZbiYQ+=CHAaftwp2x83Gy6tJCB8GUe15yO5DW-cpp0Mxg@mail.gmail.com>
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>
On Fri, 3 Jul 2026 at 21:10, John Naylor <[email protected]> wrote:
> If it was from tooling, it must have stopped with the first case it
> found, as is often the case. I just tried myself and found three more
> apparent bugs with EXCLUDE both with and without the v1 patch. Do you
> want to see the reproducers, or rethink the risk/reward ratio of
> what's handled? I'm also not sure how common that clause is.
Please share what you discovered. It would be good to understand if
it's just a simple missing check that can be added to handle all
three, or if there's just more complexity in this than making this
work correctly is worth.
> Separately, I also I believe I found a counterexample to the part
> starting with /* No ORDER BY clause then all rows are peers */ :
Thanks for finding that. Looks like I failed to consider ROWs mode for
that, where the function is only monotonically increasing. The
attached includes a fix for that.
David
Attachments:
[application/octet-stream] v2-0001-Fix-COUNT-s-logic-for-window-run-condition-suppor.patch (35.4K, ../CAApHDvrN-HL=dSBez=pme5RQKCiH7SrsMEFV-eQRJN3Vs94ZEQ@mail.gmail.com/2-v2-0001-Fix-COUNT-s-logic-for-window-run-condition-suppor.patch)
download | inline diff:
From dff4b6c9f143b3c2cb815d3e376b0992d3610115 Mon Sep 17 00:00:00 2001
From: David Rowley <[email protected]>
Date: Fri, 3 Jul 2026 14:17:42 +1200
Subject: [PATCH v2] 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.
Another issue was that the support function for the COUNT aggregate
mistakenly thought that a WindowClause without an ORDER BY meant that
the results would be both monotonically increasing and decreasing, but
that's only true when in RANGE mode, where all rows are peers. In ROWS
mode, no rows are peers, and the results are only montonically increasing.
Here, we fix these bugs by disabling the "Run Condition" pushdown for the
cases it cannot apply.
Bug: #19533
Reported-by: Qifan Liu <[email protected]>
Discussion: https://postgr.es/m/[email protected]
---
src/backend/utils/adt/int8.c | 107 +++++-
src/test/regress/expected/window.out | 482 ++++++++++++++++++++++++++-
src/test/regress/sql/window.sql | 240 ++++++++++++-
3 files changed, 820 insertions(+), 9 deletions(-)
diff --git a/src/backend/utils/adt/int8.c b/src/backend/utils/adt/int8.c
index 9b429da86d9..acfb0fe4f23 100644
--- a/src/backend/utils/adt/int8.c
+++ b/src/backend/utils/adt/int8.c
@@ -795,9 +795,112 @@ 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 either all rows are peers, in
+ * GROUPS mode, or the result is monotonically increasing.
+ */
if (req->window_clause->orderClause == NIL)
- monotonic = MONOTONICFUNC_BOTH;
+ monotonic = (frameOptions & FRAMEOPTION_ROWS) ?
+ MONOTONICFUNC_INCREASING : MONOTONICFUNC_BOTH;
else
{
/*
diff --git a/src/test/regress/expected/window.out b/src/test/regress/expected/window.out
index 90d9f953b81..9e414f0b418 100644
--- a/src/test/regress/expected/window.out
+++ b/src/test/regress/expected/window.out
@@ -4232,23 +4232,59 @@ 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
+------------------------------------------------------------
+ WindowAgg
+ Window: w1 AS (ROWS BETWEEN CURRENT ROW AND CURRENT ROW)
+ Run Condition: (count(empsalary.empno) OVER w1 <= 3)
+ -> Seq Scan on empsalary
+(4 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 +4472,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..733eb0cd8f7 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,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.53.0
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: <CAApHDvrN-HL=dSBez=pme5RQKCiH7SrsMEFV-eQRJN3Vs94ZEQ@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