Re: Aggregates containing outer references don't work per spec

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> Would

> SELECT PNUM, SUM(HOURS) FROM WORKS
> GROUP BY PNUM
> HAVING EXISTS (SELECT PNAME FROM PROJ
> WHERE PROJ.PNUM = WORKS.PNUM AND
> AVG(WORKS.HOURS) > PROJ.MAGIC / 200);
> ^^^

> be legal according to that spec too?

Yes. The fact that the same aggregate appears in the topmost target
list may be confusing the issue here --- that is *not* relevant to the
semantics of the aggregate in the subquery.

> Then the parser would not only have
> to identify the uplevel of the aggregate, it'd also have to add a junk
> aggregate TLE to the outer TL.

Nah, we don't use TLEs for aggregates. AFAICT the executor will work
perfectly correctly with this example, if we can arrange to migrate the
whole SUM(WORKS.HOURS) expression out of the subquery and put it as one
of the Params passed to the subquery. The failure is just in the parser
(too stupid to check the query correctly) and the planner (too stupid to
migrate the whole aggregate expression rather than just the WORKS.HOURS
variable reference).

regards, tom lane