pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
integer attribute 1672 != 1672
6+ messages / 3 participants
[nested] [flat]

* integer attribute 1672 != 1672
@ 2001-11-12 14:47  Markus Bertheau <twanger@bluetwanger.de>
  0 siblings, 2 replies; 6+ messages in thread

From: Markus Bertheau @ 2001-11-12 14:47 UTC (permalink / raw)
  To: pgsql-sql

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 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?





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: integer attribute 1672 != 1672
@ 2001-11-12 15:35  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Markus Bertheau <twanger@bluetwanger.de>
  1 sibling, 1 reply; 6+ messages in thread

From: Tom Lane @ 2001-11-12 15:35 UTC (permalink / raw)
  To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: integer attribute 1672 != 1672
@ 2001-11-12 15:52  Markus Bertheau <twanger@bluetwanger.de>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 6+ messages in thread

From: Markus Bertheau @ 2001-11-12 15:52 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-sql

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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: integer attribute 1672 != 1672
@ 2001-11-12 15:55  Markus Bertheau <twanger@bluetwanger.de>
  parent: Haller Christoph <ch@rodos.fzk.de>
  0 siblings, 0 replies; 6+ messages in thread

From: Markus Bertheau @ 2001-11-12 15:55 UTC (permalink / raw)
  To: Haller Christoph <ch@rodos.fzk.de>; pgsql-sql

> 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





^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: integer attribute 1672 != 1672
@ 2001-11-12 15:59  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Markus Bertheau <twanger@bluetwanger.de>
  0 siblings, 0 replies; 6+ messages in thread

From: Tom Lane @ 2001-11-12 15:59 UTC (permalink / raw)
  To: Markus Bertheau <twanger@bluetwanger.de>; +Cc: pgsql-sql

Markus Bertheau <twanger@bluetwanger.de> writes:
> same thing?

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

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: integer attribute 1672 != 1672
@ 2001-11-12 16:05  Haller Christoph <ch@rodos.fzk.de>
  parent: Markus Bertheau <twanger@bluetwanger.de>
  1 sibling, 1 reply; 6+ messages in thread

From: Haller Christoph @ 2001-11-12 16:05 UTC (permalink / raw)
  To: twanger@bluetwanger.de; +Cc: pgsql-sql

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?
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2001-11-12 16:05 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-11-12 14:47 integer attribute 1672 != 1672 Markus Bertheau <twanger@bluetwanger.de>
2001-11-12 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-12 15:52   ` Markus Bertheau <twanger@bluetwanger.de>
2001-11-12 15:59     ` Tom Lane <tgl@sss.pgh.pa.us>
2001-11-12 16:05 ` Haller Christoph <ch@rodos.fzk.de>
2001-11-12 15:55   ` Markus Bertheau <twanger@bluetwanger.de>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox