Explanation query with GROUP_BY and ONLY_FULL_GROUP_BY

I want to understand how queries works with ONLY_FULL_GROUP_BY enabled.

If i list all the columns of the table with a MIN() on one column, it works fine:

$query = "SELECT id, member_id, name, code, MIN(price) AS price, FROM tbl_product GROUP BY code";

But if I select everything I have an error:

$query = "SELECT *, MIN(price) AS price FROM tbl_product GROUP BY code";

Can you explain me the differences between both ?

Answer

It’s about a bug that was fixed in MySQL 5.7.5. According to the manual 12.20.3 MySQL Handling of GROUP BY MySQL 5.7.5 and newer can detect functional dependence between the primary key and the rest of the columns of the table. Literally it says:

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them.

ONLY_FULL_GROUP_BY is now the default option and works as specified by the SQL Standard.