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
booldefault '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),
orderingint4 not null, object int4 not null, ordered_by int4, quantity numeric(14,4) not null, quality int4 not
nulldefault 1, archive bool default 'f', starting int4, modification int4 not null check (modification >= 0),
foreignkey (id,modification) referencesmaster(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