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

On Sun, Jun 21, 2026 at 6:46 AM Zsolt Parragi <zsolt(dot)parragi(at)percona(dot)com> wrote:
> 1. It seems to only look for direct operands:
>
> 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);
> select r, count(*) from t_having group by r having r *=
> row(100,1)::t_rec; -- 2, correct
> select r, count(*) from t_having group by r having
> row((r).a,(r).b)::t_rec *= row(100,1)::t_rec; -- 1, incorrect

This is a known limitation, and has been documented in the comment of
comparison_has_grouping_eqop_conflict():

* Only direct Var operands (after stripping RelabelType wrappers) are checked.
* A grouping-column Var wrapped in a function or other expression can slip a
* different-opfamily comparison past this check. Such cases are rare enough
* that the recursive operand search that would catch them isn't justified.

The difficulty is that cathing such cases requires semantic analysis
of the function. Consider WHERE length(grouping_var) > 42 where
grouping_var is text. equality_ops_are_compatible(int4gt, texteq)
returns false and the walker would flag conflict, which is wrong as
that's a safe pushdown.

On the other hand, consider WHERE f(grouping_var) = some_const with
the same opfamily as the grouping. It passes the check. But if f
doesn't preserve the equivalence (e.g., f exposes bytewise differences
that record_ops = considers equal), pushing it inside can still flip
the result. The outer op's opfamily matches the grouping eqop, so
there's nothing for our walker to detect. The bug is "function fails
to preserve the equivalence", which would require semantic analysis
of f.

So given that such cases are rare^2 in practice, I don't think it's
worth all the effort to detect. I'd rather document the limitation as
the current patch does.

> 2. unknown operators (non btree/hash) seem to behave incorrectly, they
> default to non-conflicting but they should conflict?
>
> CREATE FUNCTION num_image_eq(numeric, numeric) RETURNS bool
> LANGUAGE sql IMMUTABLE AS $$ SELECT $1::text = $2::text $$;
> CREATE OPERATOR === (LEFTARG = numeric, RIGHTARG = numeric, FUNCTION =
> num_image_eq);
> CREATE TABLE g_hole (g numeric, v int);
> INSERT INTO g_hole VALUES (100, 1), (100.0, 2), (100.00, 3);
> SELECT g, count(*), sum(v) FROM g_hole GROUP BY g; -- 100 | 3 | 6
> SELECT g, count(*), sum(v) FROM g_hole GROUP BY g HAVING g === 100.0;
> -- 100 | 1 | 2

This is also documented in the comment of
comparison_has_grouping_eqop_conflict():

* Operators not in any btree/hash opfamily are skipped
* (see the header comment on op_is_safe_index_member).

For operators not in btree/hash opfamily, we have no way to know their
equivalence relation, so we can't prove a conflict.

- Richard