QoQ support HAVING clause in non-grouped aggregate select

Description

A HAVING clause should be allowed on any aggregate select, not just ones with a GROUP BY.

qry = queryNew('col','varchar') result = queryExecute(sql=" SELECT count(1) FROM qry having count(1) = 0", params={}, options={dbtype="query"} );

Note, a non-grouped aggregate select only returns one row so the HAVING would only ever be applied to that one grouped row. If the HAVING does not match no rows would be returned.

Note, while Adobe CF allows a HAVING clause in this context, its behavior differs due to this outstanding bug:

https://tracker.adobe.com/#/view/CF-4211230

Activity

Brad Wood 
1 December 2022 at 18:52

I assigned this to me since I plan on eventually sending a pull for it. It’s fairly straightforward, I just need to modify the SQL parser to not blow up on it.

Zac Spitzer 
1 December 2022 at 17:36

Pothys - MitrahSoft 
1 December 2022 at 14:46

I've checked this ticket and confirmed the issue on the lucee latest version 5.3.10.98-SNAPSHOT. When using HAVING clause in non-grouped aggregate select in queryexecute(), it doesn't support and it throws an error in lucee.

I added a test case to this ticket
Pull Request:https://github.com/lucee/Lucee/pull/1889

Details

Assignee

Reporter

Labels

New Issue warning screen

Before you create a new Issue, please post to the mailing list first https://dev.lucee.org

Once the issue has been verified, one of the Lucee team will ask you to file an issue

Priority

Created 1 December 2022 at 04:17
Updated 1 December 2022 at 18:52