=== Applying patches on top of PostgreSQL commit ID a8c2547eaac73cd6d499a4ab151f0401bf647f56 === /etc/rc.d/jail: WARNING: Per-jail configuration via jail_* variables is obsolete. Please consider migrating to /etc/jail.conf. Sun Jul 5 11:03:28 UTC 2026 On branch cf/6945 nothing to commit, working tree clean === using 'git am' to apply patch ./v3-0001-Demo-full-coverage-for-COUNT-s-window-run-conditi.patch === Applying: Demo full coverage for COUNT's window run condition support === using 'git am' to apply patch ./v3-0001-Minimal-fix-for-COUNT-s-window-run-condition-supp.patch === Applying: Minimal fix for COUNT's window run condition support Using index info to reconstruct a base tree... M src/backend/utils/adt/int8.c M src/test/regress/expected/window.out M src/test/regress/sql/window.sql Falling back to patching base and 3-way merge... Auto-merging src/test/regress/sql/window.sql CONFLICT (content): Merge conflict in src/test/regress/sql/window.sql Auto-merging src/test/regress/expected/window.out CONFLICT (content): Merge conflict in src/test/regress/expected/window.out Auto-merging src/backend/utils/adt/int8.c CONFLICT (content): Merge conflict in src/backend/utils/adt/int8.c error: Failed to merge in the changes. hint: Use 'git am --show-current-patch=diff' to see the failed patch Patch failed at 0001 Minimal fix for COUNT's window run condition support When you have resolved this problem, run "git am --continue". If you prefer to skip this patch, run "git am --skip" instead. To restore the original branch and stop patching, run "git am --abort". === using patch(1) to apply patch ./v3-0001-Minimal-fix-for-COUNT-s-window-run-condition-supp.patch === patching file src/backend/utils/adt/int8.c Hunk #1 FAILED at 795. 1 out of 1 hunk FAILED -- saving rejects to file src/backend/utils/adt/int8.c.rej patching file src/test/regress/expected/window.out Hunk #1 succeeded at 4779 with fuzz 2 (offset 343 lines). patching file src/test/regress/sql/window.sql Hunk #1 succeeded at 1638 with fuzz 2 (offset 178 lines). Unstaged changes after reset: M src/test/regress/expected/window.out M src/test/regress/sql/window.sql Removing src/backend/utils/adt/int8.c.rej === using 'git apply' to apply patch ./v3-0001-Minimal-fix-for-COUNT-s-window-run-condition-supp.patch === Applied patch to 'src/backend/utils/adt/int8.c' with conflicts. Applied patch to 'src/test/regress/expected/window.out' with conflicts. Applied patch to 'src/test/regress/sql/window.sql' with conflicts. U src/backend/utils/adt/int8.c U src/test/regress/expected/window.out U src/test/regress/sql/window.sql diff --cc src/backend/utils/adt/int8.c index a97f2d41704,6f0da84b435..00000000000 --- a/src/backend/utils/adt/int8.c +++ b/src/backend/utils/adt/int8.c @@@ -964,56 -793,52 +964,84 @@@ int8inc_support(PG_FUNCTION_ARGS { SupportRequestWFuncMonotonic *req = (SupportRequestWFuncMonotonic *) rawreq; MonotonicFunction monotonic = MONOTONICFUNC_NONE; - int frameOptions = req->window_clause->frameOptions; + WindowClause *wc = req->window_clause; + int frameOptions = wc->frameOptions; + bool increasing; + bool decreasing; /* ++<<<<<<< ours + * Frame bounds only ever move forwards as the current row advances + * through the partition, so the count is monotonically increasing + * whenever the frame start is pinned to the partition start, and + * monotonically decreasing whenever the frame end is pinned to the + * partition end. + */ + increasing = (frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) != 0; + decreasing = (frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING) != 0; ++======= + * A frame EXCLUDE clause removes rows from within the frame as the + * frame moves over the partition, which can cause the count to both + * rise and fall for the same window definition. Proving which + * combinations of frame mode, frame bounds, exclusion type and + * COUNT(*) vs COUNT(any) remain monotonic requires analysis of both + * frame bounds relative to the current row's peer group, so we don't + * attempt it; just report the function as non-monotonic. + */ + if (frameOptions & FRAMEOPTION_EXCLUSION) + { + req->monotonic = MONOTONICFUNC_NONE; + PG_RETURN_POINTER(req); + } ++>>>>>>> theirs /* * When there's no ORDER BY clause, all rows are peers of one * another, so in RANGE mode every permitted frame bound degenerates ++<<<<<<< ours + * to a partition edge and the count is the same for every row. + * This does not apply in ROWS mode, where frame bounds count + * individual rows regardless of any peerage; those are handled by + * the frame bound checks above. (GROUPS mode requires an ORDER BY + * clause.) + */ + if (wc->orderClause == NIL && frameOptions & FRAMEOPTION_RANGE) + increasing = decreasing = true; + + /* + * A frame EXCLUDE clause can break the monotonicity established + * above, since rows excluded from the count for one current row may + * be counted again for a later one, and vice versa. Each direction + * must be re-checked separately. + */ + if (frameOptions & FRAMEOPTION_EXCLUSION) ++======= + * to the edge of the partition and the count is the same for every + * row. This does not apply in ROWS mode, where frame bounds count + * individual rows regardless of any peerage; those get the ordinary + * treatment below. (GROUPS mode requires an ORDER BY clause.) + */ + if (req->window_clause->orderClause == NIL && + frameOptions & FRAMEOPTION_RANGE) + monotonic = MONOTONICFUNC_BOTH; + else ++>>>>>>> theirs { - /* - * Otherwise take into account the frame options. When the frame - * bound is the start of the window then the resulting value can - * never decrease, therefore is monotonically increasing - */ - if (frameOptions & FRAMEOPTION_START_UNBOUNDED_PRECEDING) - monotonic |= MONOTONICFUNC_INCREASING; - - /* - * Likewise, if the frame bound is the end of the window then the - * resulting value can never decrease. - */ - if (frameOptions & FRAMEOPTION_END_UNBOUNDED_FOLLOWING) - monotonic |= MONOTONICFUNC_DECREASING; + bool count_any; + + count_any = req->window_func->winfnoid == F_COUNT_ANY; + + if (increasing) + increasing = count_wfunc_exclusion_ok(wc, count_any, false); + if (decreasing) + decreasing = count_wfunc_exclusion_ok(wc, count_any, true); } + if (increasing) + monotonic |= MONOTONICFUNC_INCREASING; + if (decreasing) + monotonic |= MONOTONICFUNC_DECREASING; + req->monotonic = monotonic; PG_RETURN_POINTER(req); } diff --cc src/test/regress/expected/window.out index a7f689362b4,b48316e2b53..00000000000 --- a/src/test/regress/expected/window.out +++ b/src/test/regress/expected/window.out @@@ -4437,138 -4437,32 +4437,163 @@@ WHERE c = 1 (9 rows) -- ++<<<<<<< ours +-- Tests for run condition pushdown with EXCLUDE clauses in the frame +-- +-- Excluding the current row keeps COUNT(*) monotonic with any frame bounds ++======= + -- Ensure we don't push down the run condition when the frame has an EXCLUDE + -- clause. The excluded rows can rejoin the count as the frame moves over + -- the partition, so the count is not guaranteed monotonic. + -- + -- The count is 10 minus the size of the current row's peer group, which can + -- both rise and fall, despite the UNBOUNDED PRECEDING start ++>>>>>>> theirs EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, salary, ++<<<<<<< ours + count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) c ++======= + count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP) c ++>>>>>>> theirs FROM empsalary) emp WHERE c <= 3; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- ++<<<<<<< ours + WindowAgg + Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 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 random(1,10) 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 random(1, 10) FOLLOWING EXCLUDE CURRENT ROW) + Run Condition: (count(*) OVER w1 <= 3) + -> Sort + Sort Key: empsalary.salary + -> Seq Scan on empsalary +(6 rows) + +-- but not COUNT(any), whose argument for the excluded row may be NULL +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED 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 UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) + -> Sort + Sort Key: empsalary.salary + -> Seq Scan on empsalary +(7 rows) + +-- COUNT(any) with EXCLUDE CURRENT ROW is fine in ROWS mode when the frame +-- cannot extend past the current row +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) + +-- but not in RANGE or GROUPS mode, where the frame covers the whole peer +-- group while the current row moves through it +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) ++======= + Subquery Scan on emp + Filter: (emp.c <= 3) + -> WindowAgg + Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP) ++>>>>>>> theirs -> Sort Sort Key: empsalary.salary -> Seq Scan on empsalary (7 rows) ++<<<<<<< ours +-- EXCLUDE GROUP is fine as long as the frame end cannot pass the peer group +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) + +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) + +-- with a frame end beyond the peer group, the count is 10 minus the size of +-- the current row's peer group, which can both rise and fall ++======= + -- As above, for the monotonically decreasing claim of the UNBOUNDED + -- FOLLOWING end ++>>>>>>> theirs EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, @@@ -4608,27 -4502,8 +4633,32 @@@ WHERE c >= 8 ORDER BY empno 11 | 5200 | 8 (10 rows) ++<<<<<<< ours +-- EXCLUDE TIES is like EXCLUDE GROUP for COUNT(*) ... +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 +--------------------------------------------------------------------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY empsalary.salary ROWS BETWEEN UNBOUNDED PRECEDING AND '0'::bigint PRECEDING EXCLUDE TIES) + Run Condition: (count(*) OVER w1 <= 3) + -> Sort + Sort Key: empsalary.salary + -> Seq Scan on empsalary +(6 rows) + +-- ... but for COUNT(any) the current row stays in the frame with its +-- possibly-NULL argument while its earlier peers are excluded, so the frame +-- may not contain the current row at all ++======= + -- EXCLUDE TIES keeps the current row, so with COUNT(any) the count can go + -- down when the current row's argument is NULL ++>>>>>>> theirs EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, @@@ -4647,6 -4522,7 +4677,10 @@@ WHERE c <= 3 -> Seq Scan on empsalary (7 rows) ++<<<<<<< ours ++======= + -- As above, spelled 0 FOLLOWING instead of CURRENT ROW ++>>>>>>> theirs EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, @@@ -4665,57 -4541,21 +4699,74 @@@ WHERE c <= 3 -> Seq Scan on empsalary (7 rows) ++<<<<<<< ours ++======= + -- No pushdown here either, even though this particular case happens to be + -- monotonic: the excluded peer group can never be within the frame ++>>>>>>> theirs EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, salary, ++<<<<<<< ours + count(empno) 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(empsalary.empno) OVER w1 <= 3) + -> Sort + Sort Key: empsalary.salary + -> Seq Scan on empsalary +(6 rows) + +-- The monotonically decreasing case must apply the same checks to the frame +-- start. The count of rows from the start of each peer group to the end of +-- the partition never increases ... +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + count(empno) OVER (ORDER BY salary GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP) c + FROM empsalary) emp +WHERE c >= 3; + QUERY PLAN +-------------------------------------------------------------------------------------------------------------- + WindowAgg + Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP) + Run Condition: (count(empsalary.empno) OVER w1 >= 3) + -> Sort + Sort Key: empsalary.salary + -> Seq Scan on empsalary +(6 rows) + +-- ... but from an earlier peer group onwards, it can both rise and fall +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + count(*) OVER (ORDER BY salary GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP) c + FROM empsalary) emp +WHERE c >= 3; + QUERY PLAN +------------------------------------------------------------------------------------------------------------------------------ + Subquery Scan on emp + Filter: (emp.c >= 3) + -> WindowAgg + Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN '1'::bigint PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP) ++======= + count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE GROUP) c + FROM empsalary) emp + WHERE c <= 3; + QUERY PLAN + ------------------------------------------------------------------------------------------------------------------------------ + Subquery Scan on emp + Filter: (emp.c <= 3) + -> WindowAgg + Window: w1 AS (ORDER BY empsalary.salary GROUPS BETWEEN UNBOUNDED PRECEDING AND '1'::bigint PRECEDING EXCLUDE GROUP) ++>>>>>>> theirs -> Sort Sort Key: empsalary.salary -> Seq Scan on empsalary diff --cc src/test/regress/sql/window.sql index d79ad51f9db,c2d7e36bb0c..00000000000 --- a/src/test/regress/sql/window.sql +++ b/src/test/regress/sql/window.sql @@@ -1461,74 -1461,23 +1461,93 @@@ SELECT * FRO WHERE c = 1; -- ++<<<<<<< ours +-- Tests for run condition pushdown with EXCLUDE clauses in the frame +-- + +-- Excluding the current row keeps COUNT(*) monotonic with any frame bounds ++======= + -- Ensure we don't push down the run condition when the frame has an EXCLUDE + -- clause. The excluded rows can rejoin the count as the frame moves over + -- the partition, so the count is not guaranteed monotonic. + -- + + -- The count is 10 minus the size of the current row's peer group, which can + -- both rise and fall, despite the UNBOUNDED PRECEDING start ++>>>>>>> theirs +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, ++<<<<<<< ours + count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING 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 random(1,10) FOLLOWING EXCLUDE CURRENT ROW) c + FROM empsalary) emp +WHERE c <= 3; + +-- but not COUNT(any), whose argument for the excluded row may be NULL +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + count(empno) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) c + FROM empsalary) emp +WHERE c <= 3; + +-- COUNT(any) with EXCLUDE CURRENT ROW is fine in ROWS mode when the frame +-- cannot extend past the current row +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; + +-- but not in RANGE or GROUPS mode, where the frame covers the whole peer +-- group while the current row moves through it +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; + +-- EXCLUDE GROUP is fine as long as the frame end cannot pass the peer group 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; + +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; + +-- with a frame end beyond the peer group, the count is 10 minus the size of +-- the current row's peer group, which can both rise and fall ++======= + count(*) OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP) c + FROM empsalary) emp + WHERE c <= 3; + + -- As above, for the monotonically decreasing claim of the UNBOUNDED + -- FOLLOWING end ++>>>>>>> theirs EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, @@@ -1545,18 -1494,8 +1564,23 @@@ SELECT * FRO FROM empsalary) emp WHERE c >= 8 ORDER BY empno; ++<<<<<<< ours +-- EXCLUDE TIES is like EXCLUDE GROUP for COUNT(*) ... +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; + +-- ... but for COUNT(any) the current row stays in the frame with its +-- possibly-NULL argument while its earlier peers are excluded, so the frame +-- may not contain the current row at all ++======= + -- EXCLUDE TIES keeps the current row, so with COUNT(any) the count can go + -- down when the current row's argument is NULL ++>>>>>>> theirs EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, @@@ -1565,6 -1504,7 +1589,10 @@@ FROM empsalary) emp WHERE c <= 3; ++<<<<<<< ours ++======= + -- As above, spelled 0 FOLLOWING instead of CURRENT ROW ++>>>>>>> theirs EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, @@@ -1573,34 -1513,16 +1601,46 @@@ FROM empsalary) emp WHERE c <= 3; ++<<<<<<< ours ++======= + -- No pushdown here either, even though this particular case happens to be + -- monotonic: the excluded peer group can never be within the frame ++>>>>>>> theirs +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, ++<<<<<<< ours + count(empno) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE TIES) c + FROM empsalary) emp +WHERE c <= 3; + +-- The monotonically decreasing case must apply the same checks to the frame +-- start. The count of rows from the start of each peer group to the end of +-- the partition never increases ... +EXPLAIN (COSTS OFF) +SELECT * FROM + (SELECT empno, + salary, + count(empno) OVER (ORDER BY salary GROUPS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING EXCLUDE GROUP) c + FROM empsalary) emp +WHERE c >= 3; + +-- ... but from an earlier peer group onwards, it can both rise and fall EXPLAIN (COSTS OFF) SELECT * FROM (SELECT empno, salary, + count(*) OVER (ORDER BY salary GROUPS BETWEEN 1 PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE GROUP) c + FROM empsalary) emp +WHERE c >= 3; + ++======= + count(*) OVER (ORDER BY salary GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING EXCLUDE GROUP) c + FROM empsalary) emp + WHERE c <= 3; + ++>>>>>>> theirs -- -- Frames without an ORDER BY clause. In RANGE mode all rows are peers, so -- the frame always covers the whole partition and the count is constant.