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
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-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

Browse pgsql-hackers by date

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

Browse pgsql-sql by date

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