X-Original-To: pgsql-hackers@postgresql.org Received: from fzkmail2.fzk.de (fzkmail2.fzk.de [141.52.27.52]) by postgresql.org (Postfix) with SMTP id 9B04C475E4D for ; Thu, 13 Mar 2003 11:15:22 -0500 (EST) Received: FROM resy5.fzk.de BY fzkmail2.fzk.de ; Thu Mar 13 17:15:16 2003 +0100 Received: œby rodos.fzk.de with ESMTP (8.8.6 (PHNE_17135)/8.7.1) id RAA01563; Thu, 13 Mar 2003 17:15:16 +0100 (MET) Message-ID: <3E70AE93.F370199A@rodos.fzk.de> Date: Thu, 13 Mar 2003 17:15:16 +0100 From: Christoph Haller X-Mailer: Mozilla 4.77 [en] (X11; U; HP-UX B.10.20 9000/780) X-Accept-Language: en MIME-Version: 1.0 To: mkoi-pg@aon.at Cc: pgsql-sql@postgresql.org, pgsql-hackers@postgresql.org Subject: Re: [SQL] What's wrong with this group by clause? Content-Type: text/plain; charset=iso-8859-2 Content-Transfer-Encoding: 7bit X-Archive-Number: 200303/546 X-Sequence-Number: 36792 > > On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi > 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