CODEWITHSUNDEEP
mysql
user6885115
user6885115

Reputation:

MySQL this is incompatible with sql_mode=only_full_group_by

SELECT u.*, p.name as plan, COUNT(m.user_id) as totalprojects
FROM users u 
LEFT JOIN plans p ON p.id = u.access
LEFT JOIN members m ON m.user_id = u.id
WHERE u.email = 'email address';

So I have the above and I'm running MySQL on Mac and tried to do 

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, ONLY_FULL_GROUP_BY';

Which says

Query OK, 0 rows affected (0.00 sec)

SELECT @@sql_mode

shows 

+------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                             |
+------------------------------------------------------------------------------------------------------------------------+
| STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Upvotes: 2

Views: 16686

Answers (1)

user1881277
user1881277

Reputation:

Yes. The mentioned query is incompatible with sql_mode=only_full_group_by. If the only_full_group_by is enabled you have to mentioned all the columns that you are selecting in the GROUP BY clause. Here you don't have any GROUP BY clause and you are using an aggregate function (COUNT) as well. Ideally this query should fail if only_full_group_by enabled. By default this is enabled from MySQL 5.7 onwards.

Please refer https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html for the details

Upvotes: 1

Related Questions