| From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
|---|---|
| To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
| Cc: | imchifan(at)163(dot)com |
| Subject: | BUG #19534: Qual pushdown across a window subquery is unsafe with nondeterministic partition collations |
| Date: | 2026-06-24 14:45:18 |
| Message-ID: | 19534-9cdf4693c42033da@postgresql.org |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 19534
Logged by: Qifan Liu
Email address: imchifan(at)163(dot)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_window_ci;
DROP COLLATION IF EXISTS case_sensitive;
DROP COLLATION IF EXISTS case_insensitive;
CREATE COLLATION case_sensitive
(provider = icu, locale = 'und', deterministic = true);
CREATE COLLATION case_insensitive
(provider = icu, locale = 'und-u-ks-level2', deterministic = 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 = '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 = t1.x
) AS part_sum
FROM t_window_ci t1
WHERE t1.x = '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 = '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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | PG Bug reporting form | 2026-06-24 14:46:21 | BUG #19535: Splitting window input targets can break same-level SRF lockstep semantics |
| Previous Message | PG Bug reporting form | 2026-06-24 14:41:28 | BUG #19533: Wrong results from WindowAgg run-condition pushdown on count() with EXCLUDE CURRENT ROW |