Re: Aggregates containing outer references don't work per

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.
> [...]
> 
> Comments?

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


Jan

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #