Content-Type: text/plain; charset="utf-8"
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Subject: BUG #19534: Qual pushdown across a window subquery is unsafe with
 nondeterministic partition collations
To: pgsql-bugs@lists.postgresql.org
From: PG Bug reporting form <noreply@postgresql.org>
Cc: imchifan@163.com
Reply-To: imchifan@163.com, pgsql-bugs@lists.postgresql.org
Date: Wed, 24 Jun 2026 14:45:18 +0000
Message-ID: <19534-9cdf4693c42033da@postgresql.org>
Auto-Submitted: auto-generated
Archived-At: 
 <https://www.postgresql.org/message-id/19534-9cdf4693c42033da%40postgresql.org>
Precedence: bulk

The following bug has been logged on the website:

Bug reference:      19534
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:       =20

## PoC

```sql
DROP TABLE IF EXISTS t_window_ci;
DROP COLLATION IF EXISTS case_sensitive;
DROP COLLATION IF EXISTS case_insensitive;

CREATE COLLATION case_sensitive
  (provider =3D icu, locale =3D 'und', deterministic =3D true);

CREATE COLLATION case_insensitive
  (provider =3D icu, locale =3D 'und-u-ks-level2', deterministic =3D false);

CREATE TABLE t_window_ci (
    x text COLLATE case_insensitive,
    y int
);

INSERT INTO t_window_ci VALUES
  ('abc', 1),
  ('ABC', 2),
  ('def', 10);

-- Window query
SELECT x, y, part_sum
FROM (
  SELECT x, y, sum(y) OVER (PARTITION BY x) AS part_sum
  FROM t_window_ci
) s
WHERE x =3D 'abc' COLLATE case_sensitive
ORDER BY x, y;

-- Reference query
SELECT t1.x, t1.y,
       (
         SELECT sum(t2.y)
         FROM t_window_ci t2
         WHERE t2.x =3D t1.x
       ) AS part_sum
FROM t_window_ci t1
WHERE t1.x =3D 'abc' COLLATE case_sensitive
ORDER BY t1.x, t1.y;

EXPLAIN (COSTS OFF)
SELECT x, y, part_sum
FROM (
  SELECT x, y, sum(y) OVER (PARTITION BY x) AS part_sum
  FROM t_window_ci
) s
WHERE x =3D 'abc' COLLATE case_sensitive
ORDER BY x, y;
```

## Expected Behavior

The window query should return the same result as the reference query. Since
`'abc'` and `'ABC'` are in the same nondeterministic partition, the
partition sum for row `'abc'` should be `3`.

## Actual Behavior

The window query returns `abc | 1 | 1`, while the reference query returns
`abc | 1 | 3`. `EXPLAIN` shows that the strict-collation filter is pushed
below `WindowAgg`, which changes the partition before the window sum is
computed.