Hi Richard, Richard Guo <[email protected]> 于2026年5月28日周四 17:11写道： > > On Wed, May 27, 2026 at 8:04 AM Richard Guo <[email protected]> wrote: > > Thanks for raising these points. For partition pruning, > > match_clause_to_partition_key() already checks both collation and > > opfamily compatibility, so I don't think it has similar issues. I'm > > not sure what is meant by "window function pushdowns", but your > > question prompted me to look around, and I did notice that pushing > > restriction clauses down into a subquery suffers from a similar > > problem, specifically, when the subquery has DISTINCT, DISTINCT ON, or > > a window PARTITION BY clause. > > I think all these issues belong to the same class of bug: the planner > moves a qual clause across a grouping layer, and the result is wrong > when the qual's equivalence relation disagrees with the grouping's, > either an opfamily mismatch or a nondeterministic-collation mismatch. > This includes HAVING-to-WHERE pushdown, as well as qual pushdown into > a subquery past its DISTINCT, DISTINCT ON, window PARTITION BY, or > set-operation grouping layer. > > v2 attached tries to fix the full bug class through a shared walker > expression_has_grouping_conflict that detects either kind of conflict > in an expression tree. The walker takes a callback that maps each > Var to the grouping equality operator for its column (or InvalidOid > for non-grouping Vars). See the commit message for details. > I applied the v2 patch; it fixed the reported issue in [1]. But it failed on Chengpeng Yan's reported case.
SELECT x, c FROM ( SELECT x, count(*) OVER (PARTITION BY x) AS c FROM t ) s WHERE ascii(x) = 97; NOTICE: using standard form "und-u-ks-level2" for ICU locale "@colStrength=secondary" CREATE COLLATION CREATE TABLE INSERT 0 2 x | c -----+--- abc | 1 (1 row) [1] https://www.postgresql.org/message-id/19534-9cdf4693c42033da%40postgresql.org -- Thanks, Tender Wang
