From: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Organization: AK y Asociados S.R.L.
To: pgsql-sql@postgresql.org
Subject: What's wrong with this group by clause?
Date: Tue, 4 Nov 2003 18:28:12 -0300
User-Agent: KMail/1.5
MIME-Version: 1.0
Content-Type: multipart/signed; protocol="application/pgp-signature";
	micalg=pgp-sha1; boundary="Boundary-02=_tnBq/ontMoBg6eN";
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <200311041828.13144.franco@akyasociados.com.ar>

--Boundary-02=_tnBq/ontMoBg6eN
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Content-Description: signed data
Content-Disposition: inline

Hi everyone.=20

Below you can find a simplified example of a real case.=20
I don't understand why I'm getting the "john" record twice.=20
I know there's no point in using constants in the group by, but makes sense=
 in=20
the real much bigger query.=20
What I see is that when I issue an EXPLAIN with this query, it tells me tha=
t=20
the SORT KEY is "0".=20
When I remove one of the constant fields and try again the EXPLAIN, it says=
=20
that the SORT KEY is "0, name" (and the group by works, it shows only one=
=20
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');
=20=20=20
SELECT
   0 AS field1,
   0 AS field2,=20
   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)

--Boundary-02=_tnBq/ontMoBg6eN
Content-Type: application/pgp-signature
Content-Description: signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.1 (FreeBSD)

iD8DBQA/qBntz/PwDAvtu7oRAnrfAJ9XqcVz+k9tQFR/5f4KCxVWClCvjQCghmce
J0x1MYw1mHVJ4EhyZaob0aI=
=lEJN
-----END PGP SIGNATURE-----

--Boundary-02=_tnBq/ontMoBg6eN--