Hi all, A HAVING clause can be evaluated before GROUP BY when it contains an expression that distinguishes equal numeric values, producing a result that is not possible under the query's semantics.
For example, on current master: CREATE TABLE t (c numeric); INSERT INTO t VALUES (0), (0), (0), (0.0), (0.0); SELECT c, count(*) FROM t GROUP BY c; c | count ---+------- 0 | 5 SELECT c, count(*) FROM t GROUP BY c HAVING scale(c) = 1; c | count -----+------- 0.0 | 2 All five input values are equal according to numeric equality, so they form one group. Therefore the second query can return either no rows or one row with a count of 5, depending on the value used to represent the group. A count of 2 is impossible; it implies three scale-0 rows have been filtered before grouping. The planner has two optimizations that can move a qualification across a grouping boundary: - transferring a HAVING clause to WHERE before aggregation; and - pushing an outer restriction into a subquery past DISTINCT, DISTINCT ON, window partitioning, or set-operation grouping. Commit 44fb59fc605 added expression_has_grouping_conflict() to prevent such movement when the expression can distinguish values that the grouping operation considers equal. The check appears to be incomplete. For a grouping-column reference that is not a direct operand of the grouping equality, such as scale(c), grouping_conflict_walker() rejects the expression for a nondeterministic collation, but not for a type whose equality does not imply image equality. That matters for numeric, where 0 and 0.0 compare equal but have different scales. It also appears relevant to other non-equalimage cases such as record_image_ops and float positive/negative zero. I reproduced this on master at 1c9c3589042.
