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

Chengpeng Yan <chengpeng_yan(at)outlook(dot)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