Обсуждение: group by and count(*) behaviour in 8.3
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
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
>
Pavel Stehule ha scritto:
> 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?
Thank you for your request, the execution plan is the one from
"explain" (I think) but what is the "structure plan"?
The problema was a bug on my import in new database!
To avoid future error of this type, how can I ask to postgres wath
column is it using in "natural join"?
tanks again
and sorry for my error
Edoardo
>
> 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
>>
--
Jabber: edoardopa@talk.google.com
tel: 075 9142766
> > > can you send structure and execution plan? > Thank you for your request, the execution plan is the one from > "explain" (I think) but what is the "structure plan"? no, only structure :) table and fields. > The problema was a bug on my import in new database! > > To avoid future error of this type, how can I ask to postgres wath > column is it using in "natural join"? > Don't use natural join. It has some others disadvantagedness. Use classic JOIN. It is safe. SELECT FROM tab1 JOIN tab2 ON .... Regards Pavel Stehule > tanks again > and sorry for my error > Edoardo > > > > 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 > >> > > > -- > Jabber: edoardopa@talk.google.com > tel: 075 9142766 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >