Re: Small bug in GROUP BY

Andriy I Pilipenko <bamby(at)marka(dot)net(dot)ua> writes:
> There is a bug where backend dumps core while executing such a request:

> SELECT sum(a), b
> FROM t
> GROUP BY b
> UNION SELECT sum(a), 1 as b
> FROM t
> GROUP BY b

Bug confirmed. Seems to be caused by the hidden column that's added to
the second select in order to implement GROUP BY b. (7.0 assumes that
"GROUP BY b" refers to t.b, not to the output column you labeled b.
This is a change from 6.x to bring us into conformance with SQL92.)
The result is that the two SELECTs are producing different targetlists
internally, and the tuple output routines get confused.

> This problem is new to version 7. In PostgreSQL 6.x this problem not
> present.

6.x surely had the same bug, though it would not have crashed on this
exact query because of its different interpretation of the ambiguous
GROUP BY spec. But if you make it unambiguous:

SELECT sum(a), b
FROM t
GROUP BY b
UNION SELECT sum(a), 1 as c
FROM t
GROUP BY b;

then 6.5 also crashes.

This appears to be another one in a long list of problems caused by the
poorly-designed internal representation used for UNION/INTERSECT/EXCEPT.
We are planning to clean that up as part of a major querytree redesign
project scheduled for the 7.2 release cycle. I will put this on the
list to make sure it's fixed at that time.

regards, tom lane