MySQL 5.7: SQL_MODE, and FULL_GROUP_BY
by on November 27, 2016
When MySQL 5.7 was released, they changed default SQL_MODE settings. This affected a lot of existing database setups, effectively breaking a myriad of SQL queries.
The default SQL_MODE in 5.7 includes: ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION.
In this article, we'll focus on the breaking changes caused by ONLY_FULL_GROUP_BY, one of the most visible changes in MySQL 5.7's default behaviour.
ONLY_FULL_GROUP_BY
This mode is one of the more common trip ups after upgrading. It can cause existing queries that use GROUP BY to error out.
What is this mode doing?
When a SQL statement is grouped using GROUP BY, the results are grouped by whichever columns are passed to GROUP BY. So if a statement is grouped by, let's say name, it may return, for example, two different groups of rows because there are only two different values for name for all the records in the table.
i.e. There are 20 rows in a table, and say 11 have the value of 'Bob' for name and the other 9 have the value of 'Barbara' for name.
Any leftover columns you wish to return in your SELECT statement are now used to determine which record in each group qualifies to be returned. You may have 11 rows in a group where name = 'Bob', but which one of these rows is the one returned in the result set?
This is where it errors out, because it doesn't know what you want it to do, and it refuses to come up with an arbitrary solution.
These leftover columns are considered:
- Nonaggregated: Because they aren't used in the
GROUP BYsection. - Nondeterministic: Because MySQL is unable to determine how to use these columns to select the one row from each group to return.
Let's take a sample query and see what happens. Here's the query: SELECT name, address, MAX(age) FROM t GROUP BY name;
With ONLY_FULL_GROUP_BY enabled, it errors out with this:
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'mydb.t.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
The possible solutions are as follows.
- Ensure all nonaggregated columns are functionally dependent on
GROUP BYcolumns. - Refer to nonaggregated columns using
ANY_VALUE(). - Disable
ONLY_FULL_GROUP_BYeither for the session, or globally.
Solution 1: Functional dependence on GROUP BY
MAX(age) is functionally dependent on the GROUP_BY statement because for each group of rows — in this case grouped by name — it selects the row with the highest (maximum) age value. The address column, however, is not functionally dependent on any column named in the GROUP_BY statement. This will cause such an error.
So if you can figure out how to apply address to one of these aggregate functions, as described below, it would solve the problem.
Other examples of functions that cause a nonaggregated column to become functionally dependent on GROUP BY columns: MAX(), MIN(), SUM(), AVG() etc. These functions take all the rows in each group and apply their given intent to determine which row, or which calculated value to return in the row of the result set representing each group.
Visit this page for a list of all aggregate functions that would apply to this solution.
Solution 2: Use ANY_VALUE()
Use ANY_VALUE() to refer to the address column. This column is nondeterministic which causes problems when it is grouped because MySQL can't determine which of these grouped rows takes precedence if it doesn't know which row to choose based on the address column that is in your SELECT statement.
By using ANY_VALUE(), you're basically telling MySQL that you don't care which value of the passed nonaggregated column (in this case address) is chosen for each group.
Solution 3: Disable ONLY_FULL_GROUP_BY
You can disable ONLY_FULL_GROUP_BY by resetting SQL_MODE without it, either for the session or globally.
The following sets it only for the current session.
SET SESSION SQL_MODE='STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION';
The following sets it globally, across all future sessions, until MySQL restarts. If you want this change to persist across any MySQL restart, modify your my.cnf file.
SET GLOBAL SQL_MODE='STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION';
What About The Other Modes?
As it turns out, in certain minor versions of 5.7 updates, some of these modes were deprecated. Then some were built into strict mode.
Well, it's a bit unclear, but in 5.7.4, they deprecated some modes, then in 5.7.8 they reintroduced them with a warning. Suffice it to say that ONLY_FULL_GROUP_BY is the most commonly visible breaking change when going from < 5.7 to 5.7.x.