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

On Wed, Jul 1, 2026 at 10:49 AM Tender Wang <tndrwang@gmail.com> wrote:
> 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?

IIUC, this has nothing to do with what we are trying to fix here.  For
this query, the qual cannot be pushed down either even without this
patch.  So I don't feel motivated to touch it in this patch.

- Richard