Thread

1. bug in views/aggregates

   Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu> — 2000-10-25T12:10:07Z

   I'm not sure if this is a reported bug or not. SELECT statements with some
   aggregates on certain complex views can give terrible results. An example:
   
   CREATE TABLE master (
       id int4 not null,
       no int4 check (no >= 0) default 0,
       primary key (id, no),
       started date check ((not started is null) or (not closed)),
       received date,
       starter int4 not null,
       description text,
       closed bool default 'f',
       date_of_closing timestamp,
       closed_by int4);
   
   CREATE TABLE detail (
       id int4 not null,
       no_ int4 not null,
       primary key (id, no_, modification, archive),
       ordering int4 not null,
       object int4 not null,
       ordered_by int4,
       quantity numeric(14,4) not null,
       quality int4 not null default 1,
       archive bool default 'f',
       starting int4,
       modification int4 not null check (modification >= 0),
       foreign key (id,modification) references
   	master(id,no)); 
   
   CREATE VIEW buggy_view AS
   SELECT de.id, de.no_, de.ordering, de.object, 
   de.ordered_by, de.quantity, de.quality, ma.no FROM 
   detail de, master ma WHERE 
   ((((ma.no >= de.starting) AND (ma.no < de.modification)) AND de.archive) 
   OR ((ma.no >= de.modification) AND (NOT de.archive))) GROUP BY 
   de.id, de.no_, de.ordering, de.object,
   de.ordered_by, de.quantity, de.quality, ma.no;
   
   INSERT INTO master VALUES (1,0,now(),now(),1,'','f',now(),1);
   INSERT INTO detail VALUES (1,1,1,100,1,1000,1,'f',1,0);
   INSERT INTO detail VALUES (1,2,2,101,1,2000,1,'f',1,0);
   
   SELECT count(*) FROM buggy_view; -- I can see two rows of result! :-o
   
   I'm using PostgreSQL 7.0.2.
   I am interested in workarounds as well.
   TIA, Zoltan
   
   
   

2. Re: bug in views/aggregates

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-10-26T03:01:08Z

   Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu> writes:
   > I'm not sure if this is a reported bug or not. SELECT statements with some
   > aggregates on certain complex views can give terrible results. An example:
   
   Aggregates on grouped views do not and cannot work in 7.0 or earlier
   releases, because the existing rewriter implementation cannot cause the
   system to do multiple rounds of grouping/aggregation.  Unfortunately
   the rewriter is usually not bright enough to realize it can't do the
   right thing, either :-(
   
   This is fixed for 7.1.  In current sources your example produces
   
   regression=# SELECT count(*) FROM buggy_view;
    count
   -------
        2
   (1 row)
   
   > I am interested in workarounds as well.
   
   For now, you could select the view's results into a temp table,
   and then aggregate over the table.
   
   			regards, tom lane