Re: Fix HAVING-to-WHERE pushdown with mismatched operator families

Hi Richard,

Richard Guo <guofenglinux(at)gmail(dot)com> 于2026年5月28日周四 17:11写道：
>
> On Wed, May 27, 2026 at 8:04 AM Richard Guo <guofenglinux(at)gmail(dot)com> 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