Thread

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

Thom Brown <thom@linux.com> — 2026-05-26T14:05:48Z

On Tue, 26 May 2026 at 09:48, Richard Guo <guofenglinux@gmail.com> wrote:
>
> Continued with the rabbit-hole around equality-relation issues, here
> is another one in the HAVING-to-WHERE pushdown path.
>
> f76686ce7 stopped the planner from pushing a HAVING clause to WHERE
> when the clause's collation disagreed with the GROUP BY's
> nondeterministic collation.  The same shape of bug exists with
> operator families: when a HAVING clause uses a comparison operator
> from a different opfamily than the GROUP BY's eqop, pushing it to
> WHERE can produce wrong results.
>
> create type t_rec as (a numeric, b int);
> create table t_having (id int, r t_rec);
>
> insert into t_having values
>   (1, row(100, 1)::t_rec),
>   (2, row(100.0, 1)::t_rec),
>   (3, row(2, 2)::t_rec);
>
> -- wrong result: count should be 2
> select r, count(*) from t_having group by r having r *= row(100, 1)::t_rec;
>     r    | count
> ---------+-------
>  (100,1) |     1
> (1 row)
>
> The fix in the attached patch mirrors f76686ce7's structure.  We
> detect the conflict before flatten_group_exprs while the HAVING clause
> still contains GROUP Vars, and record the indices of unsafe clauses in
> a Bitmapset that's consulted by the existing pushdown loop.
>
> This issue can be reproduced as far back as v14.  However, the fix
> relies on RTE_GROUP to identify grouping expressions via GROUP Vars on
> pre-flatten havingQual.  As with f76686ce7, I'm inclined to back-patch
> to v18 only.
>
> Thoughts?

Makes sense to me, but out of curiosity, while digging into these
opfamily mismatches, have you noticed if this same record_ops vs
record_image_ops inequality poses any risks to other optimisation
paths like window function pushdowns or partition pruning? And
apologies if that has already been discussed, but I couldn't find
mention of it.

Thom