Postgres Pro
Downloads
Home   >   mailing lists

Thread: integer attribute 1672 != 1672

integer attribute 1672 != 1672

From
Markus Bertheau
Date:
Good day,

cenes=> select version();                           version                            
---------------------------------------------------------------PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc
2.95.2
(1 row)

cenes=> \d partner_views      Table "partner_views"Attribute  |   Type    | Modifier 
------------+-----------+----------partner_id | integer   | not nulltimestamp  | timestamp | not null
Indices: partner_id_partner_views_key,        timestamp_partner_views_key

cenes=> select count(partner_id), partner_id from partner_views group by
partner_id order by partner_id;count  | partner_id 
--------+------------ 21473 |          0     1 |       1672116636 |          0   266 |       1670 17991 |       1672
1 |          0 79614 |       1672  3723 |       1677    39 |       1692 30150 |       1814  3853 |       1874  4951 |
   1905     2 |       1948    28 |       2101   241 |       2129
 
(15 rows)

how can this be? is it a known bug which is fixed in 7.1.3?

Re: integer attribute 1672 != 1672

From
Haller Christoph
Date:
Hi, 
This is not a bug. 
I think it's a mistake in understanding 
aggregate functions and grouping. 
A typical example for a count/group operation 
would be 
Tell me about the number of employees in each department.
in SQL 
select count(employee), department from employee_table group by depatment ; 
AFAIK if you use an aggregate function on a column and 
group by this column the result is undefined. 
Re-think what you want to query. 
Regards, Christoph 
> 
> cenes=> \d partner_views
>        Table "partner_views"
>  Attribute  |   Type    | Modifier 
> ------------+-----------+----------
>  partner_id | integer   | not null
>  timestamp  | timestamp | not null
> Indices: partner_id_partner_views_key,
>          timestamp_partner_views_key
> 
> cenes=> select count(partner_id), partner_id from partner_views group by
> partner_id order by partner_id;
>  count  | partner_id 
> --------+------------
>   21473 |          0
>       1 |       1672
>  116636 |          0
>     266 |       1670
>   17991 |       1672
>       1 |          0
>   79614 |       1672
>    3723 |       1677
>      39 |       1692
>   30150 |       1814
>    3853 |       1874
>    4951 |       1905
>       2 |       1948
>      28 |       2101
>     241 |       2129
> (15 rows)
> 
> how can this be? is it a known bug which is fixed in 7.1.3?
>

Re: integer attribute 1672 != 1672

From
Tom Lane
Date:
Markus Bertheau <twanger@bluetwanger.de> writes:
>  PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2

Grouped views don't really work in any version before 7.1.
Time to update ...
        regards, tom lane

Re: integer attribute 1672 != 1672

From
Markus Bertheau
Date:
Good day,

> Grouped views don't really work in any version before 7.1.
cenes=> select version();                           version                            
---------------------------------------------------------------PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc
2.95.2
(1 row)

cenes=> select distinct partner_id from partner_views;partner_id 
------------         0      1672         0      1670      1672         0      1672      1677      1692      1814
1874     1905      1948      2101      2129
 
(15 rows)

same thing?

Markus Bertheau

Re: integer attribute 1672 != 1672

From
Markus Bertheau
Date:
> AFAIK if you use an aggregate function on a column and 
> group by this column the result is undefined. 
> Re-think what you want to query. 
For each view there is a row in partner_views (the partner_id and a
timestamp). I want to know how many views each partner has. I think the
query is semantically correct.

Markus Bertheau

Re: integer attribute 1672 != 1672

From
Tom Lane
Date:
Markus Bertheau <twanger@bluetwanger.de> writes:
> same thing?

If it's a view that contains internal grouping/aggregating, yes.
        regards, tom lane