I recently upgraded the MySQL from 5.6 to 5.7.14, and now all the criteria queries which were using
groupProperty are failing with the error similar to the one mentioned below:
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test_dev.this_.user_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
This is due to a current change in MySQL's approach of handling
GROUP BY, which is mentioned here.
So, according to these changelogs, we can fix this issue by:
ONLY_FULL_GROUP_BY, which I believe is not a great approach.
ANY_VALUE()to refer to the nonaggregated column.
But there is no such option to use
any_value() in grails criteria query.
Updating the query to low-level SQL statement, or using HQL can help, but that'll require changes in multiple places.
So, is there any better approach using which we can handle the above-mentioned scenario?