Hello all - Background I recently had to install MySQL 5.7 to run parallel with MySQL 8 due to differences in query results between the versions. I was successfully able to get everything set up in Lucee 5.3 to point to MySQL 5.7. Problem I am running a query of query and using a group by on only one field. It is actually grouping by month and year with the "monthly" field (ie values look like Mar'21 or Jun'22) without using an aggregate function on any fields in the SELECT clause. It works perfectly on my local machine. Example:

SELECT 		marketValue,
			marketPercent,
			d_as_at_date,
			monthly
FROM 		qMonthlyPortfolioSum
GROUP BY 	monthly
ORDER BY	d_as_at_date

My colleague is stating that he receives the following error:

Not in aggregate function or group by clause: org.hsqldb.Expression@6b6ba733 in statement [SELECT marketValue, marketPercent, d_as_at_date, monthly FROM qMonthlyPortfolioSum GROUP BY monthly ORDER BY d_as_at_date]

I cannot figure out why he is receiving an error when I am not on the query of query when we both have the same version of MySQL. I then looked in the Lucee Admin and noticed that the driver installed is version 8.0.28. I attempted to down grade MySQL Application to the highest version 5 available, which is version 5.1.40 and also version 6, but anything lower than 8.0.19 errors when validating the datasource in Lucee Admin. There is not a version 5.7 available to choose in the list of MySQL drivers for Lucee. I have even put the query of query together with the real query and executed the script against MySQL 5.7 the DBMS and it runs fine outside of Lucee as well. Would anyone have any sort of ideas as to the discrepancies between the errors on the local environments? Is it possible that I may not be running MySQL in some kind of restrictive mode and that maybe my colleague might have something restricted that would cause an error on his side?

There are a few things at play here, and im unsure where you error is happening. so lets break it down, can both of you dump out the results of the query directly from the database (before you run a query of query on it)?

Unfortunately, my colleague is out for the weekend

ok well that helps

For testing, I did pull out the source query and the QofQ, combined them and ran it on both server versions 5.7 and 8. Both work fine for me

the mention of

org.hsqldb.Expression

means that its getting passed to the in memory database for processing. (some queries are completely run internally with java instead of hsql)

I was just told in the Lucee channel that the Lucee minor versions make a difference on the QofQs. I do not believe that my colleague and I are on identical minor versions, but we are both on Lucee 5.3.

yes, there may have been an update to hsqldb that is to blame for this. Lucee 5.3.8 was a significant upgrade to QofQ by @bdw429s. it allowed more support and faster response of queries since they dont even get passed to

hsqldb

excellent, thank you so much for that

no problem, cheers

@ryan • Always include full stack traces with your error reports. • If the error is happening on a Query Of Query, all of the MySQL details you provided are not only irrelevant, but also just a red herring. A QoQ behaves the same regardless of where the query object came from, or if it even came from a JDBC call at all (as opposed to

queryNew()

Thanks for confirming. I'm in the middle of downgrading lucee. My colleague is on lucee 5.3.3.62

Holy cow, that's old

everything is a process Lol

Any QoQ using group by will 100% run on HSQLDB on a version of Lucee that old

I have asked them to upgrade MySQL as well, because I have hit issues I never imagined hitting because they are still on MySQL 5.7 rather than 8. MySQL 5.7 reaches EOL next year.

Sure, but don't confuse MySQL issues with QoQ issues. Those are two separate things.

yeah, I didn't know that til now

If you're getting an error on a Query of Query, adjusting your MySQL version, etc, etc will be pointless

I thought that QofQ used whatever driver is installed to the datasource, but nope.

If you guys use it for anything, you eed to update

Thanks for this, Brad. I'm going to push to have them upgrade Lucee, but as I have realized with trying to get them to upgrade MySQL, it will be placed as a task in the distant future. Lol

I didn’t realize you upgraded your blog! Looks nice

I'm going to have to reconfigure the QofQ for now

Gavin helped me get it to a current version of ContentBox, lol

for something that simple you could do it all in SQL or handle it with a queryReduce

Great article, @bdw429s. I'm going to refer to this article to my colleague to push for the lucee upgrade. Thanks, again.

Lucee 5.3.9 is also VERY stable as well so it's a good point to update to.

great to know