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

Started by PG Bug reporting form3 months ago2 messagesbugs
Jump to latest
#1PG Bug reporting form
noreply@postgresql.org

The following bug has been logged on the website:

Bug reference: 19532
Logged by: Qifan Liu
Email address: imchifan@163.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.

#2Chengpeng Yan
chengpeng_yan@outlook.com
In reply to: PG Bug reporting form (#1)
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@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 19532
Logged by: Qifan Liu
Email address: imchifan@163.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:
/messages/by-id/19533-413a1014e5d0e766@postgresql.org

--
Best regards,
Chengpeng Yan