Description:
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by
reads:
"Do not permit queries for which the select list or HAVING list or ORDER BY list refers to nonaggregated columns that are not named in the GROUP BY clause."
This correctly describes the behavior prior to MySQL 5.7.5 m15. As of MySQL 5.7.5 m15, ONLY_FULL_GROUP_BY has the effect of requiring that any non-aggregate columns are functionally dependent upon the list of expressions appearing in the group by clause.
"
The following queries are invalid with ONLY_FULL_GROUP_BY enabled. The first is invalid because address in the select list is not named in the GROUP BY clause, and the second because max_age in the HAVING clause is not named in the GROUP BY clause:
mysql> SELECT name, address, MAX(age) FROM t GROUP BY name;
ERROR 1055 (42000): 't.address' isn't in GROUP BY
"
Without any information about the declaration of table t, it is not possible to say whether this query would fail or not in 5.7.5 m15. If name happens to be a primary key, or if a unique index exists on name, then this query would be perfectly valid, since name appears in the GROUP BY list, and since address would be (by definition) functionally dependent upon the key (name)
If name would not appear as primary key or have a unique index, then the query would fail but the error message would not be "ERROR 1055 (42000): 't.address' isn't in GROUP BY" but rather:
"
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.t.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
"
The text continues:
"
mysql> SELECT name, MAX(age) AS max_age FROM t GROUP BY name
-> HAVING max_age < 30;
Empty set (0.00 sec)
ERROR 1463 (42000): Non-grouping field 'max_age' is used in HAVING clause
In the second example, the query could be rewritten to use HAVING MAX(age) instead, so that the reference is to a column named in an aggregate function. (max_age fails because it is an aggregate function.)
"
In the example I tried, the HAVING clause now correctly supports the alias of the the aggregate expression. So I believe such and error can now longer occur.
How to repeat:
http://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_only_full_group_by
Suggested fix:
Modify text to reflect new behavior of ONLY_FULL_GROUP_BY since 5.7.5m15
