Re: BUG #19533: Wrong results from WindowAgg run-condition pushdown on count() with EXCLUDE CURRENT ROW

Hi,

> On Jun 24, 2026, at 22:41, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19533
> Logged by: Qifan Liu
> Email address: imchifan(at)163(dot)com
> PostgreSQL version: 18.4
> Operating system: Ubuntu 20.04 x86-64, docker image postgres:18.4
> Description:
>
> ## PoC
>
> ```sql
> DROP TABLE IF EXISTS t;
>
> CREATE TABLE t (
> id int PRIMARY KEY,
> k int NOT NULL,
> v int
> );
>
> INSERT INTO t(id, k, v) VALUES
> (1, 1, 1),
> (2, 1, NULL),
> (3, 1, 1),
> (4, 2, 1);
>
> WITH
> source_rows AS (
> SELECT id
> FROM (
> SELECT id,
> count(v) OVER (
> ORDER BY k
> RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
> EXCLUDE CURRENT ROW
> ) AS c
> FROM t
> ) s
> WHERE c <= 1
> ),
> reference_rows AS (
> SELECT t1.id
> FROM t AS t1
> WHERE (
> SELECT count(t2.v)
> FROM t AS t2
> WHERE t2.k <= t1.k
> AND t2.id <> t1.id
> ) <= 1
> ),
> metamorphic_rows AS (
> SELECT id
> FROM (
> SELECT id,
> count(v) OVER (
> ORDER BY k
> RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
> EXCLUDE CURRENT ROW
> ) AS c
> FROM t
> ) s
> WHERE c + 0 <= 1
> )
> SELECT 'source' AS label, coalesce(array_agg(id ORDER BY id), '{}'::int[])
> AS ids
> FROM source_rows
> UNION ALL
> SELECT 'reference', coalesce(array_agg(id ORDER BY id), '{}'::int[])
> FROM reference_rows
> UNION ALL
> SELECT 'metamorphic_no_pushdown', coalesce(array_agg(id ORDER BY id),
> '{}'::int[])
> FROM metamorphic_rows;
>
> EXPLAIN (COSTS OFF)
> SELECT id
> FROM (
> SELECT id,
> count(v) OVER (
> ORDER BY k
> RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
> EXCLUDE CURRENT ROW
> ) AS c
> FROM t
> ) s
> WHERE c <= 1;
> ```
>
> ## Expected Behavior
>
> The source query should return the same rows as the reference query and the
> semantically equivalent metamorphic_no_pushdown query.
>
> ## Actual Behavior
>
> The optimized query returns only `{1}`, while the reference and no-pushdown
> forms return `{1,3}`.

Thanks for the report. I reproduced the wrong result.

The planner can treat count() as a monotonic window function and turn a
qual such as c <= 1 into a WindowAgg run condition. That is not safe
when the frame has an EXCLUDE option, because exclusion is applied after
the frame bounds are found and can depend on the current row or peer
group. In the reported count(v) case, NULL handling can produce values
such as 1, 2, 1, so the run condition can drop a later valid row.

This is not only about NULLs. count(*) has no NULL issue, and some
excluded-frame cases remain monotone, but not all of them. For example,
GROUPS BETWEEN UNBOUNDED PRECEDING AND 1 FOLLOWING EXCLUDE GROUP can
make count(*) decrease when the current peer group changes.

The attached patch takes the conservative route: int8inc_support() no
longer reports count() as monotonic when the window frame has
FRAMEOPTION_EXCLUSION. The added regression tests cover the reported
case, related EXCLUDE variants, and a count(*) case. They also check
that the excluded-frame query keeps a normal Filter instead of a
WindowAgg Run Condition.

I ran make check, which passed.

Thoughts welcome on whether this is the right boundary, or whether some
narrower excluded-frame cases should still prove monotonicity
separately.

--
Best regards,
Chengpeng Yan