MySQL
Błąd 1055: ONLY_FULL_GROUP_BY: coś nie znajduje się w klauzuli GROUP BY ...

Wprowadzenie

Uwagi

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'

Używanie i niewłaściwe korzystanie z 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

pokaże wiersze w tabeli o nazwie item i pokaże liczbę powiązanych wierszy w tabeli o nazwie uses . Działa to dobrze, ale niestety nie jest to standardowy SQL-92.

Dlaczego nie? ponieważ klauzula SELECT (i klauzula ORDER BY ) w zapytaniach GROUP BY muszą zawierać kolumny, które są

1. wymienione w klauzuli GROUP BY lub
2. funkcje agregujące, takie jak COUNT() , MIN() i tym podobne.

W tym przykładzie klauzula SELECT wspomina item.name , kolumnę, która nie spełnia żadnego z tych kryteriów. MySQL 5.6 i wcześniejsze odrzucą to zapytanie, jeśli tryb SQL zawiera ONLY_FULL_GROUP_BY .

To przykładowe zapytanie może być zgodne ze standardem SQL-92, zmieniając klauzulę GROUP BY , w ten sposób.

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

Późniejszy standard SQL-99 pozwala instrukcji SELECT na pominięcie niezagregowanych kolumn w kluczu grupy, jeśli DBMS może udowodnić funkcjonalną zależność między nimi a kolumnami klucza grupy. Ponieważ item.name jest funkcjonalnie zależny od item.item_id , początkowy przykład to poprawny SQL-99. MySQL zyskał funkcjonalnego kontrolera zależności w wersji 5.7. Oryginalny przykład działa pod ONLY_FULL_GROUP_BY .

Niewłaściwe użycie GROUP BY w celu zwrócenia nieprzewidzianych rezultatów: prawo Murphy'ego

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

pokaże wiersze w tabeli o nazwie item i pokaże liczbę powiązanych wierszy w tabeli o nazwie use. Wyświetli również wartość kolumny o nazwie uses.category .

To zapytanie działa w MySQL (zanim pojawiła się flaga ONLY_FULL_GROUP_BY ). Wykorzystuje niestandardowe rozszerzenie MySQL do GROUP BY .

Ale zapytanie ma problem: jeśli kilka wierszy w tabeli uses pasuje do warunku ON w klauzuli JOIN , MySQL zwraca kolumnę category tylko z jednego z tych wierszy. Który rząd? Autor zapytania i użytkownik aplikacji nie dowie się o tym wcześniej. Formalnie rzecz biorąc, jest to nieprzewidywalne : MySQL może zwrócić dowolną wartość.

Nieprzewidywalny jest losowy, z jedną znaczącą różnicą. Można się spodziewać od czasu do czasu losowego wyboru. Dlatego jeśli wybór był losowy, można go wykryć podczas debugowania lub testowania. Nieprzewidywalny wynik jest gorszy: MySQL zwraca ten sam wynik za każdym razem, gdy używasz zapytania, dopóki tak nie jest. Czasami jest to nowa wersja serwera MySQL, która powoduje inny wynik. Czasami problem stanowi rosnący stół. Co może pójść nie tak, pójdzie nie tak, a kiedy się tego nie spodziewasz. To się nazywa prawo Murphy'ego .

Zespół MySQL pracował nad tym, aby trudniej było programistom popełnić ten błąd. Nowsze wersje MySQL w sekwencji 5,7 mają sql_mode flagę zwaną ONLY_FULL_GROUP_BY . Po ustawieniu tej flagi serwer MySQL zwraca błąd 1055 i odmawia uruchomienia tego rodzaju zapytania.

Niewłaściwe użycie GROUP BY za pomocą SELECT * i jak to naprawić.

Czasami zapytanie wygląda tak, z * w klauzuli SELECT .

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

Takie zapytanie musi zostać refaktoryzowane, aby było zgodne ze standardem ONLY_FULL_GROUP_BY .

Aby to zrobić, musimy podkwerenda który używa GROUP BY poprawnie zwrócić number_of_uses wartość dla każdego item_id . To podzapytanie jest krótkie i słodkie, ponieważ wystarczy spojrzeć na tabelę uses .

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

Następnie możemy połączyć to podzapytanie z tabelą 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

Dzięki temu klauzula GROUP BY jest prosta i poprawna, a także pozwala nam użyć * specyfikatora.

Uwaga: jednak mądrzy programiści unikają w każdym przypadku użycia specyfikatora * . Zwykle lepiej jest wymienić kolumny, które chcesz w zapytaniu.

DOWOLNA WARTOŚĆ()

 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

pokazuje wiersze w tabeli o nazwie item , liczbę powiązanych wierszy i jedną z wartości w powiązanej tabeli o nazwie uses .

Możesz myśleć o tej funkcji ANY_VALUE() jako dziwnym rodzaju funkcji agregującej. Zamiast zwracać liczbę, sumę lub maksimum, instruuje serwer MySQL, aby arbitralnie wybrał jedną wartość z danej grupy. Jest to sposób obejścia błędu 1055.

Zachowaj ostrożność, używając ANY_VALUE() w zapytaniach w aplikacjach produkcyjnych.

To naprawdę powinno się nazywać SURPRISE_ME() . Zwraca wartość jakiegoś wiersza w grupie GROUP BY. Który wiersz zwraca, jest nieokreślony. Oznacza to, że wszystko zależy od serwera MySQL. Formalnie zwraca nieprzewidywalną wartość.

Serwer nie wybiera losowej wartości, jest gorzej. Zwraca tę samą wartość za każdym razem, gdy uruchamiasz zapytanie, dopóki tak nie jest. Może się zmieniać, lub nie, gdy stół powiększy się lub zmniejszy, albo gdy serwer będzie miał więcej lub mniej pamięci RAM, albo gdy zmieni się wersja serwera, lub gdy Mars będzie w trybie wstecznym (cokolwiek to znaczy), lub bez żadnego powodu.

Zostałeś ostrzeżony.