Thread

1. Small bug in GROUP BY

   Andriy I Pilipenko <bamby@marka.net.ua> — 2000-06-29T13:42:34Z

   ============================================================================
                           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 :-(
   
   
   

2. Re: Small bug in GROUP BY

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-06-29T15:51:16Z

   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