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

Richard Guo <guofenglinux(at)gmail(dot)com> 于2026年6月30日周二 17:22写道：
> This would make us fail to push some clauses that are in fact safe but
> that we cannot prove safe. But I think those cases are very narrow:
> they require a nondeterministic grouping key with a function-wrapped
> qual on it, and a plain direct comparison such as x = 'foo' COLLATE ci
> still pushes, so the optimization loss is small and limited to
> nondeterministic collations.
>
> Thoughts?

It works for me.
I ran the issued query on the v3 patch, and it returned the correct result.
Before looking at the v3 patch in detail, I found another query as below:
postgres=# explain SELECT x, y, part_sum
FROM (
SELECT x, y, sum(y) OVER (PARTITION BY x COLLATE case_sensitive) AS part_sum
FROM t_window_ci
) s
WHERE x = 'abc' COLLATE case_sensitive
ORDER BY x, y;
QUERY PLAN
----------------------------------------------------------------------------------------
Sort (cost=126.35..126.36 rows=6 width=44)
Sort Key: s.x COLLATE case_insensitive, s.y
-> Subquery Scan on s (cost=88.26..126.27 rows=6 width=44)
Filter: (s.x = 'abc'::text COLLATE case_sensitive)
-> WindowAgg (cost=88.26..110.40 rows=1270 width=76)
Window: w1 AS (PARTITION BY ((t_window_ci.x)::text))
-> Sort (cost=88.17..91.35 rows=1270 width=68)
Sort Key: ((t_window_ci.x)::text) COLLATE case_sensitive
-> Seq Scan on t_window_ci (cost=0.00..22.70
rows=1270 width=68)
(9 rows)

In the above query, the partition by clause and the where clause use
the same collation.
It's safe to push qual down into the subquery. But now on HEAD, it fails.
Is it worth fixing?

--
Thanks,
Tender Wang