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(at)aspix(dot)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(at)talk(dot)google(dot)com
> tel: 075 9142766
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>