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


Wed, 25 Oct 2000 14:10:07 +0200 (CEST)
Kovacs Zoltan Sandor <tip@pc10.radnoti-szeged.sulinet.hu>
bug in views/aggregates
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

Wed, 25 Oct 2000 23:01:08 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: bug in views/aggregates 