Thread: Small bug in GROUP BY

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               : Andriy I Pilipenko
Your email address      : bamby@marka.net.ua


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : Intel Pentium

  Operating System (example: Linux 2.0.26 ELF)  : FreeBSD-3.x

  PostgreSQL version (example: PostgreSQL-7.0):   PostgreSQL-7.0.2
                          PostgreSQL-7.0beta5

  Compiler used (example:  gcc 2.8.0)           : gcc 2.7.2.3


Please enter a FULL description of your problem:
------------------------------------------------

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

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

I know, that last 'GROUP BY' clause is not necessary to use, but it's not
necessary for backend to dump core in such a harmless situation ;-) This
query works pretty well without last 'GROUP BY' clause.

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

First create the table:

CREATE TABLE t (a int, b int)

Next insert some data into the table. It's necessary part - without data
you cannot catch the bug.

INSERT INTO t VALUES (0, 0)

And finally:

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

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------

Sorry, I don't :-(

Andriy I Pilipenko <bamby@marka.net.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