Formation Apprendre MySQL : only_full_group_by

Vous essayez de faire un GROUP BY et vous rencontrez l'erreur suivante :

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'XXXXX' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

La cause

Pour comprendre la source de cette erreur le mieux est un petit exemple.
Prenons une liste de personnes :

id | firtname  | lastname
12 |      John |      Doe
14 |      Jane |      Doe
20 |      Marc |    Boher
50 |     Marie |   Lafore

Et essayons la requête suivante :

SELECT firstname, lastname FROM users GROUP BY lastname

Le système va grouper les données par lastname mais il y a plusieurs utilisateurs nommés Doe. L'outil va devoir choisir une ligne à renvoyer pour le prénommais ne sait pas quelle donnée renvoyer. Sans le mode SQL only_full_group_by il se contente de renvoyer une ligne (sans une logique déterminée pour le choix de la ligne) ce qui peut engendrer des inconsistences. Avec le mode only_full_group_by il renvoie tout simplement une erreur pour signifier le problème.

Mais je n'avais pas cette erreur avant ?

Si vous vous demandez pourquoi vous n'aviez pas cette erreur avant, cela est dù à un changement des modes SQL par défaut et l'activation de ONLY_FULL_GROUP_BY (à partir de la version 5.7 de MySQL).

La solution

Pour remédier au problème vous pouvez utiliser des fonctions d'agrégation pour exprimer le résultat à récupérer.

SELECT MIN(firstname), lastname FROM users GROUP BY lastname

Si vous voulez garder le comportement précédent dans le cadre de MySQL vous pouvez utiliser la fonction d'agrégation ANY_VALUE().

Une seconde option est de modifier les modes sql pour supprimer cette restriction (je conseille cependant de corriger les requêtes SQL plutôt que de se reposer sur la configuration de votre SGBD).

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));