Re: [SQL] What's wrong with this group by clause? - Mailing list pgsql-hackers

From Christoph Haller
Subject Re: [SQL] What's wrong with this group by clause?
Date
Msg-id 3E70AE93.F370199A@rodos.fzk.de
Whole thread Raw
List pgsql-hackers
>
> On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi
> <franco@akyasociados.com.ar> wrote:
> >Below you can find a simplified example of a real case.
> >I don't understand why I'm getting the "john" record twice.
>
> ISTM you have found a Postgres 7.3 bug.
>
> I get one john with
>  PostgreSQL 7.1.3 on i686-pc-cygwin, compiled by GCC 2.95.3-5
> and
>  PostgreSQL 7.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
>
> but two johns with
>  PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC 2.7.2.1
>
> >/*EXAMPLE*/
> >CREATE TABLE people
> >(
> >   name TEXT
> >);
> >INSERT INTO people VALUES ('john');
> >INSERT INTO people VALUES ('john');
> >INSERT INTO people VALUES ('pete');
> >INSERT INTO people VALUES ('pete');
> >INSERT INTO people VALUES ('ernest');
> >INSERT INTO people VALUES ('john');
> >
> >SELECT
> >   0 AS field1,
> >   0 AS field2,
> >   name
> >FROM
> >   people
> >GROUP BY
> >   field1,
> >   field2,
> >   name;
> >
> > field1 | field2 |  name
> >--------+--------+--------
> >      0 |      0 | john
> >      0 |      0 | pete
> >      0 |      0 | ernest
> >      0 |      0 | john
> >(4 rows)
>PostgreSQL 7.2.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.2

SELECT   0 AS field1,   0 AS field2,    name FROM   people GROUP BY
field1,   field2,   name;field1 | field2 |  name
--------+--------+--------     0 |      0 | ernest     0 |      0 | john     0 |      0 | pete
(3 rows)
PostgreSQL 7.3.2 on hppa-hp-hpux10.20, compiled by GCC 2.95.2

SELECT   0 AS field1,   0 AS field2,    name FROM   people GROUP BY
field1,   field2,   name;field1 | field2 |  name
--------+--------+--------     0 |      0 | john     0 |      0 | pete     0 |      0 | john     0 |      0 | pete
0|      0 | john     0 |      0 | ernest
 
(6 rows)

I doubt this is a bug in 7.3.2 but in prior versions.
I've cross-checked how another DBMS (HP's ALLBASE) handles GROUP BY
without an aggregate, and it acts like 7.3.2.

Regards, Christoph





pgsql-hackers by date:

Previous
From: "Marc G. Fournier"
Date:
Subject: Re: Roadmap for FE/BE protocol redesign
Next
From: Peter Eisentraut
Date:
Subject: Re: SQL99 ARRAY support proposal