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.
