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(at)Yahoo(dot)com #