Re: Aggregates containing outer references don't work per

I think the issue is this part:

> What we now realize is that the spec says SUM(outervar) ought to be
> considered an aggregate evaluated at the outervar's query level, and
> then imported *as a whole* as an effective constant for the subquery.
> 
> I claim that there should be nothing wrong with interpreting
>     SUM(outervar1 + outervar2)
> as an aggregate of the closest outer variable's level, with further-up
When we considered outervar1 as a constant, we could do the aggregate in
the subquery using computations, but when SUM(outervar1) is computed in
an above query, combining that with anything that is part of different
query level makes no sense to me because those variables might not even
exist at the level that aggregate is being computed.

So, for example, this:
    SUM(outervar1) + SUM(outervar2)

does make sense to me because those are computable, but if outervar1 and
outervar2 are in different query levels:
SUM(outervar1 + outervar2)

doesn't make sense.  I think most of the aggregates support such
spliting, so maybe they figured people should split apart aggregates
that were unclear.

---------------------------------------------------------------------------

Tom Lane wrote:
> I wrote:
> > Now I finally understand why the spec has all that strange verbiage
> > about outer references in set-function arguments. This is the case
> > they're talking about.  (I don't much like their restriction to a single
> > outer reference ... seems like it would be appropriate to allow multiple
> > references as long as they're all from the same outer query level.)
> 
> I've been thinking about that some more.  Currently, if a subquery
> contains an aggregate expression like
>     SUM(localvar + outervar)
> (where localvar is a variable of the subquery and outervar is a variable
> of the parent query), we accept this, and execute it in what seems to me
> a perfectly reasonable way: the aggregate is evaluated over all the
> appropriate rows of the subquery, taking the outer variable as a
> constant for any one evaluation of the subquery.
> 
> The spec appears to forbid this case, but I really don't see why.
> 
> Obviously the aggregate argument could be more complex, with outer
> references from several different levels of outer query, but that
> doesn't change anything --- all the outer-reference variables are
> constants from the perspective of the subquery, whether they come
> from one level up or many levels up.
> 
> What we now realize is that the spec says SUM(outervar) ought to be
> considered an aggregate evaluated at the outervar's query level, and
> then imported *as a whole* as an effective constant for the subquery.
> 
> I claim that there should be nothing wrong with interpreting
>     SUM(outervar1 + outervar2)
> as an aggregate of the closest outer variable's level, with further-up
> variables taken as constants with respect to that level.  This isn't
> really a different case, it's the same as "SUM(localvar + outervar)"
> from the perspective of that closest outer level.  We are just allowing
> the expression to be referenced from within sub-subqueries.  The
> constraint that the aggregate must appear in the SELECT targetlist or
> HAVING clause applies to the query level that the aggregate belongs to,
> but not to lower levels.
> 
> In short, I see no reason why the spec should restrict the aggregate's
> argument to contain only a single outer reference, or even references
> from just a single outer query level.  The behavior is perfectly well
> defined without that constraint.  We can accommodate both our historical
> behavior and the spec-mandated cases if we interpret multilevel cases
> per this sketch.
> 
> Anyone see a flaw in this reasoning?
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
> 

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073