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:
## 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.
