Skip site navigation (1) Skip section navigation (2)

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

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Chris Cogdon <chris(at)felidae(dot)apana(dot)org(dot)au>
Cc: pgsql-bugs(at)postgreSQL(dot)org
Subject: Re: [BUGS] Bug in 6.4.2. Aggregate/View/Where-condition
Date: 2000-02-28 15:06:28
Message-ID: 25358.951750388@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
Chris Cogdon <chris(at)felidae(dot)apana(dot)org(dot)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

In response to

Responses

pgsql-bugs by date

Next:From: Bruce MomjianDate: 2000-02-28 17:22:02
Subject: Re: [BUGS] 7.0beta1: bugs appearing on cygwin
Previous:From: Chris CogdonDate: 2000-02-28 13:22:28
Subject: Re: [BUGS] Bug in 6.4.2. Aggregate/View/Where-condition

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group