Re: [SQL] What's wrong with this group by clause?

[forwarding to -hackers]

On Tue, 4 Nov 2003 18:28:12 -0300, Franco Bruno Borghesi
<franco(at)akyasociados(dot)com(dot)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)

Same for
SELECT 0 AS field1, 0 AS field2, name
FROM people
GROUP BY 1, 2, name;

Servus
Manfred