Обсуждение: PgSQL 6.5.1 and Group BY pb

Running PostgreSQL-6.5.1 (rpm version distributed for linux on redhat
6.0)

GROUP BY complitely fails

bash$ psql template1
template1=> CREATE TABLE "gruppi" ("id" int4 NOT NULL,"nome" character
varying(32));
CREATE
template1=> CREATE TABLE "usergroup" ("id" int4 NOT NULL,"uid" int4 NOT
NULL,"gid" int4 NOT NULL);
CREATE
template1=> insert into gruppi values (0,'ciao');
INSERT 31637 1
template1=> insert into gruppi values (1,'pippo');
INSERT 31638 1
template1=> insert into usergroup values (0,1,2);
INSERT 31639 1
template1=> insert into usergroup values (0,2,3);
INSERT 31640 1
template1=> insert into usergroup values (1,2,3);
INSERT 31641 1

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

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 "."


-- 
Best Regards
----------------------------
Giampiero Raschetti
Sistemi Innovativi
Banca Popolare di Sondrio
----------------------------

At 14:08 +0300 on 25/08/1999, Giampiero Raschetti wrote:


> 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
>
> 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 "."

I think you have a misunderstanding about the purpose of group by statements.

In fact, the above seems to indicate that you are not well aware of what
joins are, or at least you don't know that you have to restrict them to
make sense of your data. You really have to add WHERE g.id = u.id.

Anyway, what was it that you wanted to display in that second query of
yours? For each nome, display some id that matches it? Define "some". Which
id do you want. DON'T use "distinct on". It makes no sense. SQL is defined
in such a way that the returned set of results will be the same in all
implementations that respect the standard. If you use "DISTINCT ON", it
will pick up a representative at will, and it may not be the same
representative picked by other implementations. So... it is not a standard
part of SQL.

In order to be more standard, you have to tell it how to pick the
representative. For example, tell him to give you the smallest-number id
that matches a group. This is done with min():

SELECT g.nome, min(u.uid), min(u.id)
FROM gruppi g, usergroup u
WHERE g.id = u.id
GROUP BY g.nome;

This will work. The question is whether this is what you wanted.

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma

Giampiero Raschetti <Giampiero.Raschetti@popso.it> 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

Herouth Maoz wrote:
> 
> At 14:08 +0300 on 25/08/1999, Giampiero Raschetti wrote:
> 
> > ...............................
> 
> I think you have a misunderstanding about the purpose of group by statements.
> 
> In fact, the above seems to indicate that you are not well aware of what
> joins are, or at least you don't know that you have to restrict them to
> make sense of your data. You really have to add WHERE g.id = u.id.
>

OK. That's true. I'm not well aware of SQL in general but if you
try out that secuence into postgres 6.4.2 you will see that it works.
So I understand that probably it was not standard. Well.

Let's take a look at this same problem without any join involved
and if possible let me know how to obtain my target:

xaxa=> \d comuni
Table    = comuni
+----------------------------------+----------------------------------+-------+
|              Field               |              Type                |
Length|
+----------------------------------+----------------------------------+-------+
| produtt                          | varchar()                       
|    40 |
| unital                           | varchar()                       
|    40 |
| comune                           | varchar()                       
|    40 |
| login                            | varchar()                       
|     8 |
+----------------------------------+----------------------------------+-------+

xaxa=> select * from comuni;               
produtt|         unital|comune      |login
-------+---------------+------------+-----
xxx    |199900200000100|POGGIRIDENTI|user1
yyy    |199900200000100|POGGIRIDENTI|user2
yyy    |199900200000100|ARDENNO     |USER3
yyy    |199900200000100|BORMIO      |user2
(4 rows)

xaxa=> select * from comuni group by login;
ERROR:  Illegal use of aggregates or non-group column in target list
xaxa=> 

This works too on Postgres 6.4.2 so how can I obtain a list of
unique users from this table on 6.5.1 ?

Many Thanks

-- 
Best Regards
----------------------------
Giampiero Raschetti
Sistemi Innovativi
Banca Popolare di Sondrio
----------------------------

On Wed, Aug 25, 1999 at 03:58:29PM +0300, Herouth Maoz wrote:
> At 14:08 +0300 on 25/08/1999, Giampiero Raschetti wrote:
...
> In fact, the above seems to indicate that you are not well aware of what
> joins are, or at least you don't know that you have to restrict them to
> make sense of your data. You really have to add WHERE g.id = u.id.

Of course this is not at the core of Giampiero's problem, but there
are instances where you don't use a restriction, i.e. exactly when you
want to get the Cartesian product of the tables. Suppose, e.g., that
you want to generate a list of all the fields on a chess board. You
can do this by

create table rows (l char);
create table cols (n int2);

insert into rows ('a');
... (all the way to 'h')
insert into cols (1);
... (all the way to 8);

select l, n from rows, cols;


Albert.

-- 

--------------------------------------------------------------------------- Post an / Mail to / Skribu al: Albert
Reiner<areiner@tph.tuwien.ac.at>
 
---------------------------------------------------------------------------