Re: BUG #19534: Qual pushdown across a window subquery is unsafe with nondeterministic partition collations - Mailing list pgsql-bugs

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