Re: BUG #19532: Window run-condition optimization can produce wrong results for count() window aggregates

Hi,

> On Jun 24, 2026, at 22:34, PG Bug reporting form <noreply(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19532
> 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}`. `EXPLAIN` shows a `Run Condition` on the window
> aggregate, indicating that the optimization changed query semantics.

This appears to be a duplicate of BUG #19533, so I have posted a
proposed fix in that thread:
https://www.postgresql.org/message-id/flat/19533-413a1014e5d0e766%40postgresql.org

--
Best regards,
Chengpeng Yan