Hi Tom, hi Chris, hi Jielin,
attached you find a partial fix for glassfish issue 932 "Wrong SQL for
HAVING clause with COUNT on relationship".
The fix adds caching for aggregate expressions. This allows reusing the
aggregate expression instance in the HAVING clause that was created when
processing the SELECT clause. The change fixes queries having the same
aggregate function in the SELECT- and in the HAVING-clause, e.g.:
SELECT p, COUNT(p) FROM Employee e JOIN e.projects p GROUP BY p HAVING
COUNT(p)>1
There is one remaining problem with issue 932: a query using a COUNT in
the HAVING clause and the SELECT clause does *not* include the same
COUNT will still result in invalid SQL. Chris filed a separate issue for
this:
https://glassfish.dev.java.net/issues/show_bug.cgi?id=1403. So how
should we continue with issue 932? Is it ok to close 932, because we
have another report for the remaining problem?
Regards Michael