Skip site navigation (1) Skip section navigation (2)

What's wrong with this group by clause?

From: Franco Bruno Borghesi <franco(at)akyasociados(dot)com(dot)ar>
To: pgsql-sql(at)postgresql(dot)org
Subject: What's wrong with this group by clause?
Date: 2003-11-04 21:28:12
Message-ID: 200311041828.13144.franco@akyasociados.com.ar (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-sql
Hi everyone. 

Below you can find a simplified example of a real case. 
I don't understand why I'm getting the "john" record twice. 
I know there's no point in using constants in the group by, but makes sense in 
the real much bigger query. 
What I see is that when I issue an EXPLAIN with this query, it tells me that 
the SORT KEY is "0". 
When I remove one of the constant fields and try again the EXPLAIN, it says 
that the SORT KEY is "0, name" (and the group by works, it shows only one 
record for each person in the table).

Thanks anyone.

/*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)

Responses

pgsql-hackers by date

Next:From: Dave CramerDate: 2003-11-04 21:49:19
Subject: Re: Open Sourcing pgManage
Previous:From: Andrew DunstanDate: 2003-11-04 21:17:39
Subject: Re: Experimental patch for inter-page delay in VACUUM

pgsql-sql by date

Next:From: Jamie LawrenceDate: 2003-11-04 22:33:19
Subject: Problems with NEW.* in triggers
Previous:From: Richard HuxtonDate: 2003-11-04 15:43:35
Subject: Re: query assistance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group