public inbox for [email protected]
help / color / mirror / Atom feedFrom: Chengpeng Yan <[email protected]>
To: [email protected] <[email protected]>
To: [email protected] <[email protected]>
Subject: Re: BUG #19532: Window run-condition optimization can produce wrong results for count() window aggregates
Date: Sat, 27 Jun 2026 17:00:20 +0000
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
Hi,
> On Jun 24, 2026, at 22:34, PG Bug reporting form <[email protected]> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19532
> Logged by: Qifan Liu
> Email address: [email protected]
> 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
view thread (2+ messages)
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]
Subject: Re: BUG #19532: Window run-condition optimization can produce wrong results for count() window aggregates
In-Reply-To: <[email protected]>
* 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