Fix HAVING-to-WHERE Pushdown with Mismatched Operator Families

Core Problem

This thread exposes a family of optimizer correctness bugs where the planner incorrectly pushes filter clauses past grouping/deduplication boundaries when the clause's comparison semantics differ from those used by the grouping operation itself.

The Fundamental Invariant Being Violated

When the planner pushes a HAVING clause down to WHERE (i.e., evaluates it before aggregation rather than after), it relies on the assumption that if a row satisfies the pushed-down predicate, then every row that would be grouped together with it also satisfies the predicate. This assumption holds only when the HAVING clause's equality/comparison operator uses the same equivalence relation as the GROUP BY's equality operator.

When operator families differ, two values that are "equal" under the GROUP BY's opfamily (e.g., 100 and 100.0 under record_ops which uses numeric equality semantics) may NOT be equal under the HAVING clause's operator (e.g., *= which uses record_image_ops — byte-level comparison where 100 ≠ 100.0). Pushing the HAVING clause to WHERE means it filters individual rows before grouping, eliminating rows that should have been counted in the group.

Concrete Demonstration

-- record_ops groups (100,1) and (100.0,1) together (numeric equality)
-- record_image_ops (*= operator) treats them as different (byte-level)
select r, count(*) from t_having group by r having r *= row(100, 1)::t_rec;

The correct answer is count = 2 (both (100,1) and (100.0,1) fall into the same group). But if the HAVING clause is pushed to WHERE, the *= operator filters out (100.0,1) before grouping, yielding count = 1.

Historical Context

Commit f76686ce7 previously fixed a narrower version of this bug: it prevented pushdown when the HAVING clause's collation disagreed with a nondeterministic collation used by GROUP BY. The current patch generalizes this to also detect opfamily mismatches, which is the same shape of semantic conflict but through a different mechanism.

Scope of the Bug — Broader Than HAVING

The thread reveals that the problem extends beyond HAVING-to-WHERE pushdown. The same class of incorrect optimization occurs when restriction clauses are pushed into subqueries that contain:

1. DISTINCT / DISTINCT ON — The subquery deduplicates using one equality relation, but the pushed-down WHERE clause uses a different one. This can cause rows to appear that should have been eliminated by DISTINCT, or vice versa.

2. Window PARTITION BY — Pushing a filter past a window function boundary when the filter uses different equality semantics than the PARTITION BY can produce incorrect rank/row_number values, because rows are evaluated against the filter after window computation, but pushing them down changes the window partitions.

3. Collation mismatches in subquery pushdown — Not just opfamily differences, but collation disagreements also affect the DISTINCT ON and window function cases. The example with ci (case-insensitive) collation vs "C" collation demonstrates this clearly.

Partition pruning is noted as NOT vulnerable because match_clause_to_partition_key() already validates both collation and opfamily compatibility.

Proposed Solution

Architecture of the Fix

The patch mirrors the structure of f76686ce7:

1. Detection phase: Before flatten_group_exprs() transforms the HAVING clause (which replaces GROUP Vars with their underlying expressions), the code inspects each HAVING clause. It checks whether the clause's operator belongs to the same opfamily as the corresponding GROUP BY expression's equality operator.

2. Recording phase: Indices of "unsafe" clauses (those with opfamily mismatches) are recorded in a Bitmapset.

3. Consultation phase: The existing pushdown loop checks this Bitmapset and skips pushdown for flagged clauses.

The timing is critical — detection must happen before flatten_group_exprs() because after flattening, the GROUP Vars that identify which grouping expression each clause references are no longer present.

Back-patch Considerations

• The bug exists back to v14 (and likely earlier).
• The fix depends on RTE_GROUP infrastructure to identify grouping expressions via GROUP Vars in the pre-flattened havingQual.
• RTE_GROUP was introduced relatively recently, so the fix can only be back-patched to v18, same as f76686ce7.

Open Work

The subquery pushdown cases (DISTINCT, DISTINCT ON, window PARTITION BY) with both opfamily and collation mismatches represent additional bugs that likely need separate patches. These appear to be newly identified in this thread.

Technical Insights

Why record_ops vs record_image_ops Matters

• record_ops compares composite types field-by-field using each field's type-specific equality operator. For numeric, 100 = 100.0.
• record_image_ops compares the binary/datum representation directly. 100 and 100.0 have different internal representations and are thus unequal.

These two opfamilies define fundamentally different equivalence classes over the same data type, making them a perfect test case for opfamily-mismatch bugs.

The General Pattern

Any optimizer transformation that moves a predicate across a "grouping boundary" (GROUP BY, DISTINCT, PARTITION BY) must verify that the predicate's equality semantics are compatible with the boundary's equality semantics. This is a correctness invariant that PostgreSQL has historically enforced incompletely — the collation check was added in f76686ce7, and the opfamily check is being added now.

Why This Is Architecturally Important

The planner's pushdown optimizations are critical for performance (evaluating predicates early reduces the number of rows flowing through expensive operations like sorting and aggregation). But each pushdown transformation has semantic preconditions. As PostgreSQL supports more complex type systems (composite types, nondeterministic collations, custom opfamilies), the space of potential semantic mismatches grows, making these safety checks increasingly important.