BUG #19534: Qual pushdown across a window subquery is unsafe with nondeterministic partition collations
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t248658psql -h localhost -U postgresBuilt from patchset v3 (message #3), July 28, 2026 at 11:55 PM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t248658_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t248658_3 && git checkout t248658_3Patchset v3 (message #3) is on t248658_3
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.
PG Bug reporting form <noreply@postgresql.org> 于2026年6月27日周六 20:55写道：
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:## 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.
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
Attachments:
0001-Fix-qual-pushdown-with-nondeterministic-partition-co.patchapplication/octet-stream; name=0001-Fix-qual-pushdown-with-nondeterministic-partition-co.patchDownload+37-1
Hi
On Jun 28, 2026, at 19:33, Tender Wang <tndrwang@gmail.com> wrote:
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
<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
Chengpeng Yan <chengpeng_yan@outlook.com> 于2026年6月28日周日 21:43写道：
Hi
```
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
```
Good catch.
I didn't take this query into account.
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?
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
On Mon, Jun 29, 2026 at 10:23 AM Tender Wang <tndrwang@gmail.com> wrote:
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.
This class of bug is currently being discussed and addressed on thread
[1]: /messages/by-id/CAMbWs49dHFVVwx9MjYmvUrZu0L4vZTbGVyhj3T2SsWgS1Bzhzg@mail.gmail.com
duplicating effort.
[1]: /messages/by-id/CAMbWs49dHFVVwx9MjYmvUrZu0L4vZTbGVyhj3T2SsWgS1Bzhzg@mail.gmail.com
- Richard
Richard Guo <guofenglinux@gmail.com> 于2026年6月29日周一 09:41写道：
On Mon, Jun 29, 2026 at 10:23 AM Tender Wang <tndrwang@gmail.com> wrote:
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.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] /messages/by-id/CAMbWs49dHFVVwx9MjYmvUrZu0L4vZTbGVyhj3T2SsWgS1Bzhzg@mail.gmail.com
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