public inbox for [email protected]
help / color / mirror / Atom feedBUG #19532: Window run-condition optimization can produce wrong results for count() window aggregates
2+ messages / 2 participants
[nested] [flat]
* BUG #19532: Window run-condition optimization can produce wrong results for count() window aggregates
@ 2026-06-24 14:34 PG Bug reporting form <[email protected]>
2026-06-27 17:00 ` Re: BUG #19532: Window run-condition optimization can produce wrong results for count() window aggregates Chengpeng Yan <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: PG Bug reporting form @ 2026-06-24 14:34 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: BUG #19532: Window run-condition optimization can produce wrong results for count() window aggregates
2026-06-24 14:34 BUG #19532: Window run-condition optimization can produce wrong results for count() window aggregates PG Bug reporting form <[email protected]>
@ 2026-06-27 17:00 ` Chengpeng Yan <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Chengpeng Yan @ 2026-06-27 17:00 UTC (permalink / raw)
To: [email protected] <[email protected]>; [email protected] <[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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2026-06-27 17:00 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-24 14:34 BUG #19532: Window run-condition optimization can produce wrong results for count() window aggregates PG Bug reporting form <[email protected]>
2026-06-27 17:00 ` Chengpeng Yan <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox