Re: [BUGS] Bug in 6.4.2. Aggregate/View/Where-condition

Chris Cogdon <chris@felidae.apana.org.au> writes:
> create view span as select ref, min(starttime), max(endtime) from vals
> group by ref;
> select * from span where 0<min;

Current sources (7.0beta1) don't give the "node 108" failure, but they
don't give right answers either.  With a few more data rows than you
showed, viz

regression=# select * from vals;
 ref | starttime | endtime
-----+-----------+---------
   1 |         1 |       3
   1 |         2 |       4
   3 |         2 |       4
   3 |         1 |       7
   4 |         5 |       7
(5 rows)

regression=# select * from span;
 ref | min | max
-----+-----+-----
   1 |   1 |   4
   3 |   1 |   7
   4 |   5 |   7
(3 rows)

So far so good, but:

regression=# select * from span where min > 3;
 ref | min | max
-----+-----+-----
(0 rows)

Ooops.  (I think the problem here is that the WHERE clause really needs
to be a HAVING clause after the rule is expanded, since that "min" is
really an aggregate invocation --- but the rewriter isn't smart enough
to make that change.)

The bottom line is that grouped views don't work right in any but the
very simplest cases, and they can't work right given the current
implementation of rules.  We need to redesign the internal querytree
data structure to support explicit subqueries.  I hope to see that
happen for 7.1, but it's not done or even started as of today.

Sorry the news isn't better :-(

            regards, tom lane