mysql - Why should not disable ONLY_FULL_GROUP_BY

Question

During internet searching I see lot of questions now to disable ONLY_FULL_GROUP_BY feature, so it mean lot of developers have a trouble with writing SQL queries in this strict mode.

I know it pretty simple to disable this limitation, but now I ask asking: Why shouldn't I do this?

What problems or side effects will be involved by removing ONLY_FULL_GROUP_BY limitation?

Answer 1

Without the strictness that ONLY_FULL_GROUP_BY enforces, you may:

• not realize you have the wrong query;
• be getting some columns that don't have a specific meaning;
• the results that you are getting, may not be the results that appear on a different server, or on the the next upgrade, or when the query plan changes (added/deleted/updated data).

So listen to ONLY_FULL_GROUP_BY. Its a strong warning that your query isn't right. The error is simply saying your GROUP BY clause is incompatible with the set of results being returned. Other databases enforce it by default, and its users write better SQL as a result.

Ignoring warnings is like web developers that chmod a+rwx because they can't work out file permissions. Take the time to understand the environment in which you are working and you'll be better off for the experience. And so will the next person, potentially your future self, that looks at the SQL.