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. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
