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 #19534: Qual pushdown across a window subquery is unsafe with nondeterministic partition collations
Date: Wed, 24 Jun 2026 14:45:18 +0000
Message-ID: <[email protected]> (raw)

The following bug has been logged on the website:

Bug reference:      19534
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_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.

view thread (6+ 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 #19534: Qual pushdown across a window subquery is unsafe with nondeterministic partition collations
  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