MySQL – Sélection d’une colonne qui n’est pas dans le groupe par

J’essaie d’append des fonctionnalités à une application préexistante et je suis tombé sur une vue MySQL du genre:

SELECT AVG(table_name.col1), AVG(table_name.col2), AVG(table_name.col3), table_name.personID, table_name.col4 FROM table_name GROUP BY table_name.personID;

OK, donc il y a quelques fonctions d’agrégat. Vous pouvez sélectionner identifiant personnel car vous le regroupez. Mais il s’agit également de sélectionner une colonne qui ne se trouve pas dans une fonction d’agrégation et qui ne fait pas partie de la clause GROUP BY. Comment est-ce possible??? Est-ce qu’il choisit simplement une valeur aléatoire parce que les valeurs ne sont pas uniques par groupe?

D’où je viens (MSSQL Server), c’est une erreur. Quelqu’un peut-il m’expliquer ce comportement et pourquoi il est autorisé dans MySQL?

Il est vrai que cette fonctionnalité autorise certaines requêtes ambiguës et renvoie en silence un jeu de résultats avec une valeur arbitraire tirée de cette colonne. En pratique, cela a tendance à être la valeur de la ligne du groupe qui est stockée en premier.

Ces requêtes ne sont pas ambiguës si vous choisissez uniquement des colonnes qui dépendent fonctionnellement de la ou des colonnes du critère GROUP BY. En d’autres termes, s’il ne peut y avoir qu’une seule valeur distincte de la colonne “ambiguë” par valeur qui définit le groupe, il n’y a pas de problème. Cette requête serait illégale dans Microsoft SQL Server (et ANSI SQL), même si elle ne peut pas entraîner une ambiguïté logique:

 SELECT AVG(table1.col1), table1.personID, persons.col4 FROM table1 JOIN persons ON (table1.personID = persons.id) GROUP BY table1.personID;

De plus, MySQL a un mode SQL pour le faire se conformer à la norme: ONLY_FULL_GROUP_BY

FWIW, SQLite autorise également ces clauses GROUP BY ambiguës, mais choisit la valeur de la dernière ligne du groupe. ^†

^† Au moins dans la version testée. Ce que cela signifie d’être arbitraire, c’est que MySQL ou SQLite puissent changer leur implémentation à l’avenir et avoir des comportements différents. Vous ne devriez donc pas compter sur le comportement qui rest tel qu’il est actuellement dans des cas ambigus comme celui-ci. Il est préférable de réécrire vos requêtes pour qu’elles soient déterministes et non ambiguës. C’est pourquoi MySQL 5.7 active désormais ONLY_FULL_GROUP_BY par défaut.

J’aurais dû googler un peu plus longtemps … Il semble que j’ai trouvé ma réponse .

MySQL étend l’utilisation de GROUP BY afin que vous puissiez utiliser des colonnes ou des calculs non agrégés dans la liste SELECT qui n’apparaissent pas dans la clause GROUP BY. Vous pouvez utiliser cette fonctionnalité pour obtenir de meilleures performances en évitant le sorting et le regroupement de colonnes inutiles. Par exemple, vous n’avez pas besoin de grouper sur customer.name dans la requête suivante

En SQL standard, vous devez append customer.name à la clause GROUP BY. Dans MySQL, le nom est redondant.

Pourtant, cela semble juste … faux.

 select * from personel where p_id IN(select min(dbo.personel.p_id) FROM personel GROUP BY dbo.personel.p_adi)