sql - MySQL Group By functionality in different version

Question

Following is a simple SQL query:

SELECT * FROM *table_name*
GROUP BY *column_name*

In my system I have MySQL 5.5. It is working absolutely fine. Whereas in my friend's system he have MySQL 5.7, and he is getting the following error:

ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'testdb.assetentry.entryId' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

It is clearly visible this is happening because the versions are different.

But what I want to know is the reason behind this.

Can any one please explain.

Answer 1

First of all, please read Group by clause in mySQL and postgreSQL, why the error in postgreSQL?

It is not SQL Standard behaviour.

12.16.3 MySQL Handling of GROUP BY

To disable the MySQL GROUP BY extension and enable standard SQL behavior, enable the ONLY_FULL_GROUP_BY SQL mode. In this case, columns not named in the GROUP BY clause cannot be used in the select list or HAVING clause unless enclosed in an aggregate function.

It looks like on second server you have acitivated ONLY_FULL_GROUP_BY mode.

SELECT @@sql_mode;

You could simulate this behaviour on your MySQL 5.5:

SET SESSION sql_mode = 'ONLY_FULL_GROUP_BY';

SELECT *
FROM tab
GROUP BY col;
-- tab.col2' isn't in GROUP BY

SqlFiddleDemo

---

From MySQL 5.7:

Implementation of the ONLY_FULL_GROUP_BY SQL mode has been made more sophisticated, to no longer reject deterministic queries that previously were rejected. In consequence, ONLY_FULL_GROUP_BY is now enabled by default, to prohibit nondeterministic queries containing expressions not guaranteed to be uniquely determined within a group.