Re: Aggregates containing outer references don't work per

Wow, a subselect in HAVING.  Where do they think up these things.  :-)

Yes, it would be nice to support it, though I am not surprised no one
has asked for this feature yet.  :-)

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

Tom Lane wrote:
> Some of the Red Hat guys have been trying to work through the NIST SQL
> compliance tests.  So far they've found several things we already knew
> about, and one we didn't:
> 
> -- TEST:0434 GROUP BY with HAVING EXISTS-correlated set function!
>    SELECT PNUM, SUM(HOURS) FROM WORKS
>           GROUP BY PNUM
>           HAVING EXISTS (SELECT PNAME FROM PROJ
>                          WHERE PROJ.PNUM = WORKS.PNUM AND
>                                SUM(WORKS.HOURS) > PROJ.BUDGET / 200);
> 
> This query is legal according to the test, but Postgres fails with
> ERROR:  Aggregates not allowed in WHERE clause
> 
> The SUM() should be allowed in the sub-SELECT because, according to the
> spec, it is actually an aggregate of the outer query --- and so the
> whole expression "SUM(WORKS.HOURS)" is effectively an outer reference
> for the sub-SELECT.
> 
> 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.)
> 
> Fixing this looks a tad nasty.  The planner can convert simple column
> outer references into Params for a subquery, but there is no
> infrastructure to handle making larger expressions into Params.  Also,
> I don't want the planner repeating the work that the parser is going to
> have to do to validate correctness of the query --- the parser will need
> to understand that the aggregate is an outer reference as a whole, and
> the planner shouldn't have to rediscover that for itself.  In any case,
> it seems that an outer-reference aggregate is a rather different animal
> from an aggregate of the current query, and ought to be so labeled in
> the parse tree.
> 
> I'm thinking of adding an "agglevelsup" field in Aggref nodes that has
> semantics similar to "varlevelsup" in Var nodes --- if it's zero then
> the aggregate is a regular aggregate of the current level, if it's more
> than zero then the aggregate belongs to an outer query that many levels
> up.  The parser would need to set this field based on what the
> aggregate's argument contains.  It'd also have to check that the
> argument does not contain variables of more than one query level.
> 
> Comments?
> 
>             regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 

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