[HACKERS] bug in views/aggregates

[Kovacs Zoltan Sandor]

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