Re: bug: having clause evaluated before group by

Jacob Brazeal <jacob(dot)brazeal(at)gmail(dot)com> writes:
> 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.

I think you are expecting too much here. Given your example:
> 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

That result is valid, except that it's unspecified whether the
group's representative will be "0" or "0.0". So when you do

> SELECT c, count(*) FROM t
> GROUP BY c
> HAVING scale(c) = 1;
> c | count
> -----+-------
> 0.0 | 2

the filtering happens at the row level, and you get 2 rows that are
certain to both be "0.0". If we were to apply HAVING after the
GROUP BY, you would get either no output rows at all, or "0.0 | 5",
depending on that unspecified detail. It's not clear to me that
that's better than what we do now. I think what you have here is a
fundamentally ill-posed query, because its results necessarily are
under-defined.

> 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.

I'm inclined to think that your proposed fix would make many more
people unhappy (because their query got slower) than happy (because
their ill-defined query got a result they think is more correct).

Another problem is that we don't really know whether a datatype's
equality condition is equivalent to image equality; it's too much
of a black box for that. (The collation problem is different
because we can be fairly sure that nondeterministic collations don't
obey image equality.)

regards, tom lane