Re: group by and count(*) behaviour in 8.3

Hello

it works to me:

postgres=# create table c1(n varchar, e integer);
CREATE TABLE
postgres=# create table c2(n2 varchar, e integer);
CREATE TABLE
postgres=# insert into c1 values('aa',1),('bb',2),('aa',3);
INSERT 0 3
postgres=# insert into c2 values('aa',1),('bb',2),('aa',3);
INSERT 0 3
postgres=# select * from c1 natural join c2;
 e | n  | n2
---+----+----
 1 | aa | aa
 2 | bb | bb
 3 | aa | aa
(3 rows)

postgres=# select * from c1 natural join c2 where c1.e = 3;
 e | n  | n2
---+----+----
 3 | aa | aa
(1 row)

postgres=# select n, count(*) from c1 natural join c2 where c1.e = 3 group by n;
 n  | count
----+-------
 aa |     1
(1 row)

postgres=# select n, count(*) from c1 natural join c2  group by n;
 n  | count
----+-------
 bb |     1
 aa |     2
(2 rows)

postgres=# select n2, count(*) from c1 natural join c2  group by n2;
 n2 | count
----+-------
 bb |     1
 aa |     2
(2 rows)


can you send structure and execution plan?

Regards
Pavel Stehule

On 02/01/2008, Edoardo Panfili <edoardo@aspix.it> wrote:
> I am using this query in 8.3beta4 (compiled from source) in MacOS X 10.5.1
>
> SELECT webName,count(*) FROM contenitore NATURAL JOIN cartellino WHERE
> contenitore.tipo='e' GROUP BY webName;
>
> this is the result
>                        webName                      | count
> --------------------------------------------------+-------
>    test palermo                                     | 36679
>    Herbarium Camerinensis - CAME                    | 36679
>    Herbarium Universitatis Aeserniae - IS           | 36679
>    Herbarium Universitatis Civitatis Perusii - PERU | 36679
>    Herbarium Anconitanum - ANC                      | 36679
>    Test database - São Paulo                        | 36679
>    Herbarium Universitatis Genuensis - GE           | 36679
>    Herbarium Universitatis Senensis - SIENA         | 36679
>    Segnalazioni Siena                               | 36679
>    Herbarium Aquilanum - AQUI                       | 36679
> (10 rows)
>
> but 36679 is the total number of row of the table.
> The same query in 8.1.4 retrieves the aspected result (the number of
> elements for each webName).
>
> Is this a bug or a change in the semantic of SQL?
>
> thank you
> Edoardo
>
> --
> Jabber: edoardopa@talk.google.com
> tel: 075 9142766
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>