MySQL
Erreur 1055: ONLY_FULL_GROUP_BY: quelque chose n'est pas dans la clause GROUP BY ...

Introduction

Remarques

SET  sql_mode = 'STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'

Utiliser et utiliser GROUP BY

 SELECT item.item_id, item.name,     /* not SQL-92 */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

affichera les lignes d'une table appelée item et affichera le nombre de lignes associées dans une table appelée uses . Cela fonctionne bien, mais malheureusement, ce n'est pas la norme SQL-92.

Pourquoi pas? car la clause SELECT (et la clause ORDER BY ) dans les requêtes GROUP BY doivent contenir des colonnes

1. mentionné dans la clause GROUP BY , ou
2. fonctions d'agrégation telles que COUNT() , MIN() , etc.

La clause SELECT cet exemple mentionne item.name , une colonne qui ne répond à aucun de ces critères. MySQL 5.6 et ONLY_FULL_GROUP_BY antérieures rejetteront cette requête si le mode SQL contient ONLY_FULL_GROUP_BY .

Cet exemple de requête peut être fait pour se conformer à la norme SQL-92 en modifiant la clause GROUP BY , comme ceci.

SELECT item.item_id, item.name, 
       COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id, item.name

La norme SQL-99 suivante permet à une SELECT d'omettre les colonnes non agrégées de la clé de groupe si le SGBD peut prouver une dépendance fonctionnelle entre elles et les colonnes de clé de groupe. Comme item.name dépend de manière fonctionnelle de item.item_id , l'exemple initial est valide SQL-99. MySQL a obtenu un prouveur de dépendance fonctionnelle dans la version 5.7. L'exemple d'origine fonctionne sous ONLY_FULL_GROUP_BY .

Abuser de GROUP BY pour obtenir des résultats imprévisibles: la loi de Murphy

SELECT item.item_id, uses.category,   /* nonstandard */ 
       COUNT(*) number_of_uses 
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

affichera les lignes d'une table appelée item et affichera le nombre de lignes associées dans une table appelée uses. Il affichera également la valeur d'une colonne appelée uses.category .

Cette requête fonctionne dans MySQL (avant l' ONLY_FULL_GROUP_BY indicateur ONLY_FULL_GROUP_BY ). Il utilise l'extension non standard de MySQL pour GROUP BY .

Mais la requête a un problème: si plusieurs lignes de la table uses correspondent à la condition ON dans la clause JOIN , MySQL renvoie la colonne category partir d'une seule de ces lignes. Quelle rangée? L'auteur de la requête et l'utilisateur de l'application ne le savent pas à l'avance. D'un point de vue formel, c'est imprévisible : MySQL peut retourner n'importe quelle valeur.

Imprévisible est comme aléatoire, avec une différence significative. On pourrait s'attendre à un choix aléatoire de changer de temps en temps. Par conséquent, si un choix était aléatoire, vous pourriez le détecter lors du débogage ou du test. Le résultat imprévisible est pire: MySQL renvoie le même résultat à chaque fois que vous utilisez la requête, jusqu'à ce que ce ne soit pas le cas. Parfois, c'est une nouvelle version du serveur MySQL qui provoque un résultat différent. Parfois, la table est en train de causer le problème. Ce qui peut mal tourner va mal se passer et quand vous ne vous y attendez pas. C'est ce qu'on appelle la loi de Murphy .

L’équipe de MySQL s’efforce de rendre l’erreur plus difficile pour les développeurs. Les versions plus récentes de MySQL dans la séquence 5.7 ont un indicateur sql_mode appelé ONLY_FULL_GROUP_BY . Lorsque cet indicateur est défini, le serveur MySQL renvoie l'erreur 1055 et refuse d'exécuter ce type de requête.

Mauvaise utilisation de GROUP BY avec SELECT * et comment y remédier.

Parfois, une requête ressemble à ceci, avec un * dans la clause SELECT .

 SELECT item.*,     /* nonstandard */ 
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

Une telle requête doit être refactorisée pour être conforme à la norme ONLY_FULL_GROUP_BY .

Pour ce faire, nous avons besoin d' un sous - requête qui utilise GROUP BY correctement pour retourner la number_of_uses valeur pour chaque item_id . Cette sous-requête est courte et simple, car il suffit de regarder la table des uses .

                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id

Ensuite, nous pouvons joindre cette sous-requête à la table des item .

 SELECT item.*, usecount.number_of_uses
   FROM item
   JOIN (
                              SELECT item_id, COUNT(*) number_of_uses
                                FROM  uses 
                               GROUP BY item_id
        ) usecount ON item.item_id = usecount.item_id

Cela permet à la clause GROUP BY d'être simple et correcte, et nous permet également d'utiliser le spécificateur * .

Note: néanmoins, les développeurs avisés évitent d’utiliser le spécificateur * dans tous les cas. Il est généralement préférable de répertorier les colonnes de votre choix dans une requête.

DE N'IMPORTE QUELLE VALEUR()

 SELECT item.item_id, ANY_VALUE(uses.tag) tag,   
        COUNT(*) number_of_uses
  FROM item 
  JOIN uses ON item.item_id, uses.item_id
 GROUP BY item.item_id

affiche les lignes d'une table appelée item , le nombre de lignes associées et l'une des valeurs de la table associée appelée uses .

Vous pouvez penser à cette fonction ANY_VALUE() comme une sorte de fonction d'agrégat étrange. Au lieu de renvoyer un nombre, une somme ou un maximum, le serveur MySQL doit choisir, arbitrairement, une valeur du groupe en question. C'est un moyen de contourner l'erreur 1055.

Soyez prudent lorsque vous utilisez ANY_VALUE() dans les requêtes des applications de production.

Cela devrait vraiment s'appeler SURPRISE_ME() . Il renvoie la valeur d'une ligne dans le groupe GROUP BY. La ligne qu'il retourne est indéterminée. Cela signifie que tout dépend du serveur MySQL. Formellement, il renvoie une valeur imprévisible.

Le serveur ne choisit pas une valeur aléatoire, c'est pire que cela. Il renvoie la même valeur chaque fois que vous exécutez la requête, jusqu'à ce que ce ne soit pas le cas. Cela peut changer ou non quand une table grandit ou rétrécit, ou quand le serveur a plus ou moins de RAM, ou quand la version du serveur change, ou quand Mars est rétrograde (peu importe ce que cela signifie), ou sans aucune raison.

Tu étais prévenu.