Subject: BUG #19534: Qual pushdown across a window subquery is unsafe with nondeterministic partition collations

From: PG Bug reporting form <[email-masked]>
Date: Wed, 24 Jun 2026 14:45:18 +0000

The following bug has been logged on the website:

Bug reference:      19534
Logged by:          Qifan Liu
Email address:      [email-masked]
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: Tender Wang <[email-masked]>
Date: Sun, 28 Jun 2026 19:33:47 +0800

> below `WindowAgg`, which changes the partition before the window sum is

Yes, I can reproduce this on HEAD.
The whereClause was pushed down to the scan level of table t_window_ci.
So the row with 'ABC' was ignored by the filter.

In current logic, we have no logic to process a nondeterministic
partition clause when considering whether the
qual can be pushed down to the subquery.

I try to fix this with the attached patch.
I add a new flag UNSAFE_HAS_NONDETERMINITIC for
pushdown_safety_info.unsafeFlags.
If the var is in the partition clause and it is nondeterministic, we
set this flag.
In qual_is_pushdown_safe(), we check the qual's inputcollid; if it is
deterministic, but the var in
the partition clause is nondeterministic, it is PUSHDOWN_UNSAFE.

Any thoughts?
-- 
Thanks,
Tender Wang

---

From: Chengpeng Yan <[email-masked]>
Date: Sun, 28 Jun 2026 13:43:22 +0000

Hi

> On Jun 28, 2026, at 19:33, Tender Wang <[email-masked]> wrote:
>
> <0001-Fix-qual-pushdown-with-nondeterministic-partition-co.patch>

Thanks for working on this. I was looking into this issue too, and I
agree that this is a wrong-result bug in the window-function subquery
qual pushdown.

Your approach looks reasonable for the most direct collation-conflict
case: a qual can use equality semantics that distinguish values that the
window partition treats as equal. I think the underlying safety
condition is a bit broader, though. The pushed-down qual must return
the same result for all values in a window partition equality class.

The same risk is not limited to explicit collation conflicts. Using the
same kind of nondeterministic case-insensitive partition key as the
report, a non-collation-aware qual can still split a partition:

```
CREATE COLLATION case_insensitive
(provider = icu, locale = '@colStrength=secondary',
deterministic = false);

CREATE TABLE t (x text COLLATE case_insensitive);
INSERT INTO t VALUES ('abc'), ('ABC');

SELECT x, c
FROM (
SELECT x, count(*) OVER (PARTITION BY x) AS c
FROM t
) s
WHERE ascii(x) = 97;
```

Without the patch, the planner can push the `ascii(x) = 97` filter below
`WindowAgg`, so the result is:

```
x | c
-----+---
abc | 1
```

The correct result is:

```
x | c
-----+---
abc | 2
```

Postgres only knows at this point that the column appears in every
window `PARTITION BY` list; it does not know that an arbitrary qual on
that column is constant over the partition equality class. The attached
v2 patch therefore takes a conservative approach: if the matching
partition key uses a nondeterministic collation, ordinary qual pushdown
is disabled for that column.

The tradeoff is that we may miss some pushdown opportunities for quals
that really are constant over the partition. But for a wrong-result
case, that is safer than pushing a qual whose behavior over the
partition is not proven. Deterministic partition keys keep the existing
behavior.

I ran `make check`; all tests passed.

Any thoughts?

--
Best regards,
Chengpeng Yan

---

From: Tender Wang <[email-masked]>
Date: Mon, 29 Jun 2026 09:22:49 +0800

> `WindowAgg`, so the result is:

Good catch.
I didn't take this query into account.

> I ran `make check`; all tests passed.

The correct result is the most important thing. So I agree with you.
The fix seems obvious: we should refuse quals pushed down to the
subquery if the quals contain a nondeterministic partition clause var.

-- 
Thanks,
Tender Wang

---

From: Richard Guo <[email-masked]>
Date: Mon, 29 Jun 2026 10:41:07 +0900

> subquery if the quals contain a nondeterministic partition clause var.

This class of bug is currently being discussed and addressed on thread
[1].  I suggest we consolidate the discussion there to avoid
duplicating effort.

[1] https://postgr.es/m/[email-masked]

- Richard

---

From: Tender Wang <[email-masked]>
Date: Mon, 29 Jun 2026 09:53:11 +0800

> [1] https://postgr.es/m/[email-masked]

I had noticed the thread [1] before writing a patch to fix this issue.
But I thought it was a different problem at that time.
We can shift our gaze to the thread [1]. It seems it handles all kinds
of collation problems.

-- 
Thanks,
Tender Wang