What are the benefits of only_full_group_by mode?
I upgraded mysql and I migrated from MySQL version 5.6.17 to version 5.7.14
Since I have errors in my sql queries
Indeed, many of my requests look like this:
SELECT count (id) as nbr, lic from prep WHERE key = '18'
And I have this error:
1140 - In an aggregated query without GROUP BY, SELECT expression # 2 contains the non-aggregated column "operator.preparation.orig_lic"; this is incompatible with sql_mode = only_full_group_by
After some research I find out that Mysql 5.7.14 activates ONLY_FULL_GROUP_BY by default
Why is it enabled by default?
What's the best solution (for performance)? Disable ONLY_FULL_GROUP_BY or add a group on my request?
thank
source to share
Correct solution is adding column group i on
SELECT count (id) as nbr, lic
from prep
WHERE key = '18'
group by lic
for yoy index dependent performance,
ONLY_FULL_GROUP_BY is normal behavior for an aggregation function in SQL, and acceptance is 5.7, avoiding ambiguity in the random result for a result with a leaky column.
source to share
The "best" solution is to do the right thing and fix your request by adding group by
rather than overriding the error that occurs. If you reverse the error with an ONLY_FULL_GROUP_BY
error, then your experience is gone, but you will probably experience two new errors as a result of this:
-
Unexpected results of including an aggregated value with non-aggregated values ββis an issue your error is trying to prevent.
-
Failure to complete the request in other environments. If you ever need to switch settings or give the code to someone else without using this database, the request will throw an error again. If you get in the habit of overriding a bug or other bugs, your code can become unusable and seriously spoil its usefulness.
In general, if you get an error, fix it, not just tell the compiler / optimizer to ignore it.
source to share