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

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

From: Chris Cogdon <chris(at)felidae(dot)apana(dot)org(dot)au>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-bugs(at)postgreSQL(dot)org
Subject: Re: [BUGS] Bug in 6.4.2. Aggregate/View/Where-condition
Date: 2000-02-28 21:28:52
Message-ID: Pine.LNX.4.10.10002290813180.15232-100000@uncia.felidae.apana.org.au (view raw or flat)
Thread:
Lists: pgsql-bugs
On Mon, 28 Feb 2000, Tom Lane wrote:


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

Note that under 6.4 and 6.5, the result of a aggregate (or a subquery,
even) has to be on the RHS Of an operator. Viz:

test1=> select * from span where min>0;
ERROR:  rewrite: aggregate column of view must be at rigth side in 

The fact that this error does not come up in 7 either means that they've
fixed a limitation, or there's a bug that's crept in. Can you try it with

select * from span where 3<min;

and see what you get.


> 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.)

Okay... here's a test under 6.5.2:

test1=> select * from span having 0<min;
ERROR:  SELECT/HAVING requires aggregates to be valid

Oops.

Does this work under 7.beta?


> 
> 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 :-(

Thanks tons for trying that out for me, tom. Hope that gives our kind,
wonderful developers food for thought :)


(PS... just in case having min and max as column names in the view was
giving 6.4 or 6.5 headaches, I've repeated /all/ the tests using different
names for the columns, with no apparent change in results)



   ("`-/")_.-'"``-._        Ch'marr, a.k.a.
    . . `; -._    )-;-,_`)  Chris Cogdon <chris(at)felidae(dot)apana(dot)org(dot)au>
   (v_,)'  _  )`-.\  ``-'
  _.- _..-_/ / ((.'       FC1.3: FFH3cmA+>++C++D++H++M++P++R++T+++WZ++Sm++
((,.-'   ((,/   fL               RLCT acl+++d++e+f+++h++i++++jp-sm++


In response to

Responses

pgsql-bugs by date

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

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