bug: having clause evaluated before group by
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.
Jacob Brazeal <jacob.brazeal@gmail.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