On Sun, Jun 21, 2026 at 6:46 AM Zsolt Parragi <[email protected]> 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
