MySQL
Errore 1055: ONLY_FULL_GROUP_BY: qualcosa non è nella clausola GROUP BY ...

introduzione

Osservazioni

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'

Utilizzo e abuso di 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

mostrerà le righe in una tabella chiamata item e mostrerà il conteggio delle righe correlate in una tabella denominata uses . Funziona bene, ma sfortunatamente non è SQL-92 standard.

Perchè no? poiché la clausola SELECT (e la clausola ORDER BY ) nelle query GROUP BY deve contenere colonne

1. menzionato nella clausola GROUP BY , o
2. funzioni di aggregazione come COUNT() , MIN() e simili.

La clausola SELECT questo esempio menziona item.name , una colonna che non soddisfa uno di questi criteri. MySQL 5.6 e precedenti rifiuteranno questa query se la modalità SQL contiene ONLY_FULL_GROUP_BY .

Questa query di esempio può essere eseguita per conformarsi allo standard SQL-92 modificando la clausola GROUP BY , in questo modo.

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

Lo standard SQL-99 successivo consente a un'istruzione SELECT di omettere le colonne non aggregate dalla chiave di gruppo se il DBMS può dimostrare una dipendenza funzionale tra di esse e le colonne chiave di gruppo. Poiché item.name dipende dal punto di item.item_id funzionale su item.item_id , l'esempio iniziale è valido SQL-99. MySQL ha ottenuto un test di dipendenza funzionale nella versione 5.7. L'esempio originale funziona con ONLY_FULL_GROUP_BY .

Utilizzo improprio di GROUP BY per restituire risultati imprevedibili: la legge di 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

mostrerà le righe in una tabella chiamata item e mostrerà il conteggio delle righe correlate in una tabella denominata uses. uses.category anche il valore di una colonna chiamata uses.category .

Questa query funziona in MySQL (prima che ONLY_FULL_GROUP_BY flag ONLY_FULL_GROUP_BY ). Usa l'estensione non standard di MySQL a GROUP BY .

Ma la query ha un problema: se più righe nella tabella uses corrispondono alla condizione ON nella clausola JOIN , MySQL restituisce la colonna della category da una sola di quelle righe. Quale fila? Lo scrittore della query e l'utente dell'applicazione non lo sanno in anticipo. Formalmente parlando, è imprevedibile : MySQL può restituire qualsiasi valore desiderato.

Imprevedibile è come casuale, con una differenza significativa. Ci si potrebbe aspettare che una scelta casuale cambi di volta in volta. Pertanto, se una scelta fosse casuale, potreste rilevarla durante il debug o il test. Il risultato imprevedibile è peggiore: MySQL restituisce lo stesso risultato ogni volta che si utilizza la query, finché non lo fa. A volte è una nuova versione del server MySQL che causa un risultato diverso. A volte è una tabella in crescita a causare il problema. Cosa può andare storto, andrà male, e quando non te lo aspetti. Si chiama Murphy's Law .

Il team di MySQL ha lavorato per rendere più difficile per gli sviluppatori commettere questo errore. Le versioni più recenti di MySQL nella sequenza 5.7 hanno un flag sql_mode chiamato ONLY_FULL_GROUP_BY . Quando viene impostato questo flag, il server MySQL restituisce l'errore 1055 e si rifiuta di eseguire questo tipo di query.

Utilizzo errato di GROUP BY con SELECT * e come risolverlo.

A volte una query appare come questa, con un * nella clausola SELECT .

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

Tale query deve essere sottoposta a refactoring per essere conforme allo standard ONLY_FULL_GROUP_BY .

Per fare ciò, abbiamo bisogno di una subquery che usi correttamente GROUP BY per restituire il valore number_of_uses per ogni item_id . Questa sottoquery è breve e dolce, perché ha solo bisogno di guardare la tabella degli uses .

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

Quindi, possiamo unirmi a quella sottoquery con la tabella degli 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

Ciò consente alla clausola GROUP BY di essere semplice e corretta e ci consente anche di usare l'identificatore * .

Nota: tuttavia, gli sviluppatori saggi evitano di utilizzare l'identificatore * in ogni caso. Di solito è meglio elencare le colonne che desideri in una query.

ANY_VALUE ()

 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

mostra le righe in una tabella chiamata item , il conteggio delle righe correlate e uno dei valori nella tabella correlata chiamata uses .

Puoi pensare a questa funzione ANY_VALUE() come a uno strano tipo di funzione aggregata. Invece di restituire un conteggio, somma o massimo, istruisce il server MySQL a scegliere, arbitrariamente, un valore dal gruppo in questione. È un modo per aggirare l'errore 1055.

ANY_VALUE() attenzione quando usi ANY_VALUE() nelle query nelle applicazioni di produzione.

Dovrebbe davvero essere chiamato SURPRISE_ME() . Restituisce il valore di alcune righe nel gruppo GROUP BY. Quale riga restituisce è indeterminata. Ciò significa che dipende interamente dal server MySQL. Formalmente, restituisce un valore imprevedibile.

Il server non sceglie un valore casuale, è peggio di quello. Restituisce lo stesso valore ogni volta che si esegue la query, finché non lo fa. Può cambiare o meno quando una tabella cresce o si riduce, o quando il server ha più o meno RAM, o quando la versione del server cambia, o quando Marte è in retrogrado (qualunque cosa ciò significhi), o per nessuna ragione.

Sei stato avvertito.