public inbox for [email protected]  

help / color / mirror / Atom feed

From: PG Bug reporting form <[email protected]>
To: [email protected]
Cc: [email protected]
Subject: BUG #19533: Wrong results from WindowAgg run-condition pushdown on count() with EXCLUDE CURRENT ROW
Date: Wed, 24 Jun 2026 14:41:28 +0000
Message-ID: <[email protected]> (raw)

The following bug has been logged on the website:

Bug reference:      19533
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}`.

view thread (15+ messages)  latest in thread

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 #19533: Wrong results from WindowAgg run-condition pushdown on count() with EXCLUDE CURRENT ROW
  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