Re: bug in views/aggregates

Kovacs Zoltan Sandor <tip(at)pc10(dot)radnoti-szeged(dot)sulinet(dot)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