Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA50924 for ; Wed, 25 Aug 1999 10:08:18 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id KAA06047; Wed, 25 Aug 1999 10:07:43 -0400 (EDT) To: Giampiero Raschetti cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] PgSQL 6.5.1 and Group BY pb In-reply-to: Your message of Wed, 25 Aug 1999 13:08:41 +0200 <37C3CEB9.AB63E3DE@popso.it> Date: Wed, 25 Aug 1999 10:07:43 -0400 Message-ID: <6045.935590063@sss.pgh.pa.us> From: Tom Lane Giampiero Raschetti writes: > And now the output query with GROUP BY: > template1=> SELECT g.nome,u.uid,u.id FROM gruppi g, usergroup u GROUP BY > g.nome; > ERROR: Illegal use of aggregates or non-group column in target list > template1=> SELECT nome,id FROM gruppi GROUP BY nome; > ERROR: Illegal use of aggregates or non-group column in target list That is not a bug, it is the way things are supposed to work --- you have variables in your SELECT that are not being grouped over, and are not inside an aggregate function, so they have no well-defined value. Postgres before 6.5 was not very good about catching this error all the time, but 6.5 includes a more complete error check. > And now the output query with SELECT DISTINCT: > template1=> SELECT DISTINCT ON g.nome g.nome,u.uid,u.id from gruppi g, > usergroup u; > ERROR: parser: parse error at or near "." It looks like DISTINCT ON wants a plain unqualified column name. DISTINCT ON is not standard SQL, and IMHO we ought to take it out --- it's not well-defined. It's got the same definitional problem as non-group-by columns: what values of the other variables are you going to get? regards, tom lane