Description:
http://dev.mysql.com/doc/refman/5.7/en/group-by-extensions.html reads:
"In standard SQL, a query that includes a GROUP BY clause cannot refer to nonaggregated columns in the select list that are not named in the GROUP BY clause. "
This behavior corresponds to SQL92 and prior. SQL99 and beyond require that non-aggregate columns are functionally dependent upon the SELECT list. As of version 5.7.5m15 MySQL also implements behavior similar to SQL99.
"
For example, this query is illegal in standard SQL because the name column in the select list does not appear in the GROUP BY:
SELECT o.custid, c.name, MAX(o.payment)
FROM orders AS o, customers AS c
WHERE o.custid = c.custid
GROUP BY o.custid;
For the query to be legal, the name column must be omitted from the select list or named in the GROUP BY clause.
"
Assuming orders is a child of customers then this query would be perfectly valid, since c.name is functionally dependent upon the GROUP BY list.
"
MySQL extends the use of GROUP BY so that the select list can refer to nonaggregated columns not named in the GROUP BY clause. This means that the preceding query is legal in MySQL. You can use this feature to get better performance by avoiding unnecessary column sorting and grouping. However, this is useful primarily when all values in each nonaggregated column not named in the GROUP BY are the same for each group. The server is free to choose any value from each group, so unless they are the same, the values chosen are indeterminate. Furthermore, the selection of values from each group cannot be influenced by adding an ORDER BY clause. Sorting of the result set occurs after values have been chosen, and ORDER BY does not affect which values within each group the server chooses.
"
Since MySQL 5.7.5m15 has ONLY_FULL_GROUP_BY enabled by default, the above is no longer true.
"
MySQL extends this behavior to permit the use of an alias in the HAVING clause for the aggregated column:
SELECT name, COUNT(name) AS c FROM orders
GROUP BY name
HAVING c = 1;
Enabling ONLY_FULL_GROUP_BY disables this MySQL extension and a non-grouping field 'c' is used in HAVING clause error occurs because the column c in the HAVING clause is not enclosed in an aggregate function (instead, it is an aggregate function).
"
The results I have is that in MySQL 5.7.5m13 one can refer to the aggregate by alias even if ONLY_FULL_GROUP_BY is enabled.
How to repeat:
see descriptoin
Suggested fix:
Modify doc to fit behavior since MySQL 5.7.5m15
