Perché MySQL aggiunge una funzionalità che è in conflitto con gli standard SQL?

Sono abituato alle tecnologie Microsoft incluso SQL Server. Oggi mi sono imbattuto in un Q & A in cui è stato citato il seguente passaggio dalla documentazione di MySQL:

SQL standard rifiuta la query perché non è ansible selezionare campi non aggregati che non fanno parte della clausola GROUP BY in una query aggregata. MySQL estende l’uso di GROUP BY in modo che l’elenco di selezione possa fare riferimento a colonne non aggregate non denominate nella clausola GROUP BY. Ciò significa che la query precedente è legale in MySQL. È ansible utilizzare questa funzione per ottenere prestazioni migliori evitando l’ordinamento e il raggruppamento non necessari delle colonne. Tuttavia, ciò è utile principalmente quando tutti i valori in ogni colonna non aggregata non denominata in GROUP BY sono uguali per ciascun gruppo. Il server è libero di scegliere qualsiasi valore da ciascun gruppo, quindi a meno che non siano gli stessi, i valori scelti sono indeterminati .

Qual è la ragione di questa estensione MySQL, se in conflitto con lo standard SQL?

SQL standard rifiuterà la tua query perché non puoi SELEZIONARE campi non aggregati che non fanno parte della clausola GROUP BY in una query aggregata

Questo è corretto, fino al 1992 .

Ma è chiaramente sbagliato, dal 2003 e oltre.

Dallo standard SQL 2003, 6IWD6-02-Foundation-2011-01.pdf, da http://www.wiscorp.com/ , paragrafo 7.12 (specifica della query), pagina 398 :

17) Se T è una tabella raggruppata, allora sia G l’insieme di colonne di raggruppamento di T. In ciascuna ((espressione di valore)) contenuta in ((lista di selezione)), ogni riferimento di colonna che fa riferimento a una colonna di T deve fare riferimento ad alcuni la colonna C dipende funzionalmente da G o deve essere contenuta in un argomento aggregato di a ((specifica funzione specifica)) la cui query di aggregazione è QS


Ora MySQL, ha implementato questa funzionalità consentendo non solo le colonne che sono funzionalmente dipendenti dalle colonne di raggruppamento ma che consentono tutte le colonne . Ciò sta causando alcuni problemi con gli utenti che non capiscono come funziona il raggruppamento e ottengono risultati indeterminati dove non si aspettano.

Ma hai ragione nel dire che MySQL ha aggiunto una funzionalità che è in conflitto con gli standard SQL (anche se sembra che tu la pensi per il motivo sbagliato). Non è del tutto preciso in quanto hanno aggiunto una funzionalità standard SQL ma non nel modo migliore (più simile alla modalità semplice) ma è in conflitto con gli standard più recenti.

Per rispondere alla tua domanda, suppongo che il motivo di questa caratteristica MySQL (estensione) sia conforms agli ultimi standard SQL (2003+). Perché hanno scelto di implementarlo in questo modo (non pienamente conforms), possiamo solo ipotizzare.

Come @Quassnoi e @Johan hanno risposto con esempi, è principalmente un problema di prestazioni e manutenibilità. Ma non si può facilmente cambiare l’RDBMS in modo abbastanza intelligente (escluso Skynet) per riconoscere le colonne dipendenti dal punto di vista funzionale, quindi gli sviluppatori MySQL hanno fatto una scelta:

Noi (MySQL) diamo a voi (utenti MySQL) questa funzionalità che è negli standard SQL-2003. Migliora la velocità in alcune query GROUP BY ma c’è un problema. È necessario prestare attenzione (e non al motore SQL) in modo che le colonne negli elenchi SELECT e HAVING dipendano funzionalmente dalle colonne GROUP BY . In caso contrario, potresti ottenere risultati indeterminati.

Se vuoi disabilitarlo, puoi impostare sql_mode su ONLY_FULL_GROUP_BY .

È tutto nei documenti MySQL: Estensioni a GROUP BY (5.5) – sebbene non nella suddetta dicitura ma come nella tua citazione (hanno anche dimenticato di menzionare che si tratta di una deviazione dallo standard SQL-2003 mentre non è standard SQL-92). Questo tipo di scelte è comune penso in tutto il software, inclusi altri RDBMS. Sono fatti per prestazioni, compatibilità con le versioni precedenti e molti altri motivi. Oracle ha il famoso '' is the same as NULL per esempio e SQL-Server ne ha probabilmente anche un po ‘.

C’è anche questo post sul blog di Peter Bouman, in cui viene difesa la scelta degli sviluppatori MySQL: Debunking GROUP BY myths .


Aggiornamento (2011)

Come @Mark Byers ci ha informato in un commento (in una domanda correlata a DBA.SE), PostgreSQL 9.1 ha aggiunto una nuova funzionalità (data di rilascio: settembre 2011) progettata per questo scopo. È più restrittivo dell’implementazione di MySQL e più vicino allo standard.


Aggiornamento 2 (2015)

MySQL ha annunciato che nella versione 5.7, il comportamento è migliorato per conformarsi allo standard e in realtà riconosce le dipendenze funzionali (anche meglio dell’implementazione di Postgres). La documentazione: MySQL Handling di GROUP BY (5.7) e un altro post di Peter Bouman: MySQL 5.7.5: GROUP BY rispetta le dipendenze funzionali!

Qual è la ragione di questa estensione MySQL, se in conflitto con lo standard SQL?

Ti permette di scrivere una query del genere:

 SELECT a.*, COUNT(*) FROM a JOIN b ON ba = a.id GROUP BY a.id 

Altri sistemi richiederebbero l’aggiunta di tutte le colonne da a nell’elenco GROUP BY che rende la query più ampia, meno gestibile e meno efficiente.

In questo modulo (con raggruppamento da parte del PK ), questo non contraddice lo standard poiché ogni colonna in a dipende funzionalmente dalla sua chiave primaria.

Tuttavia, MySQL non controlla realmente la dipendenza funzionale e consente di selezionare colonne che non dipendono dal gruppo. Questo può produrre risultati indeterminati e non dovrebbe essere invocato. L’unica cosa garantita è che i valori delle colonne appartengono ad alcuni dei record che condividono l’espressione di raggruppamento (nemmeno per un record!).

Questo comportamento può essere disabilitato impostando sql_mode su ONLY_FULL_GROUP_BY .

Risposta breve
È un hack della velocità

Questo è abilitato di default, ma può essere disabilitato con questa impostazione: http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html#sqlmode_only_full_group_by

Risposta lunga La ragione per la clausola di gruppo abbreviata non standard è che si tratta di un hack di velocità.
MySQL consente al programmatore di determinare se i campi selezionati sono funzionalmente dipendenti dalla clausola group by.
Il DB non esegue alcun test, ma seleziona solo il primo risultato che trova come valore del campo.
Ciò comporta considerevoli aumenti di velocità.

Considera questo codice:

 SELECT f1, f2, f3, f4 FROM t1 GROUP BY f2 -- invalid in most SQL flavors, valid in MySQL 

MySQL selezionerà solo il primo valore che trova, trascorrendo un periodo di tempo minimo.
f1, f3, f4 saranno dalla stessa riga, ma questa relazione si separerà se sono coinvolte più tabelle con join.

Per fare lo stesso qualcosa di simile in SQL-server dovresti fare

 SELECT MIN(f1), f2, MIN(f3), MIN(f4) FROM t1 GROUP BY f2 -- valid SQL, but really a hack 

Il DB dovrà ora esaminare tutti i risultati per trovare il valore minimo, sbuffando e soffiando.
f1, f3, f4 molto probabilmente non avranno alcuna relazione tra loro e non saranno dalla stessa riga.

Se comunque lo fai:

 SELECT id as `primary_key`, count(*) as rowcount, count(f2) as f2count, f2, f3, f4 FROM t1 GROUP BY id 

Tutto il resto dei campi sarà funzionalmente dipendente id .
Rowcount sarà sempre 1 e f2count sarà 0 (se f2 è nullo) o 1.

Nei join, dove sono coinvolte molte tabelle, in una configurazione 1-n in questo modo:

Esempio:

Sito web 1 -> n Argomenti 1 -> n Threads 1 -> n Posts 1 -> 1 Person.

E fai una selezione complicata che coinvolge tutte le tabelle e fai un GROUP BY posts.id
Ovviamente tutti gli altri campi sono funzionalmente dipendenti da posts.id (e SOLO su posts.id).
Quindi non ha senso elencare più campi nel gruppo per clausola, o per forzare l’uso di funzioni aggregate.
Al fine di accelerare le cose. MySQL non ti obbliga a farlo.

Ma hai bisogno di capire il concetto di dipendenza funzionale e le relazioni nelle tabelle e il join che hai scritto, quindi mette un po ‘di peso sul programmatore.
Tuttavia usando:

 SELECT posts.id, MIN(posts.f2) ,MIN(threads.id), min(threads.other) ,MIN(topics.id), .... ,MIN(website.id), ..... ,MIN(Person.id), ... FROM posts p INNER JOIN threads t on (p.thread_id = t.id) INNER JOIN topic to on (t.topic_id = to.id) INNER JOIN website w ON (w.id = to.website_id) INNER JOIN person pe ON (pe.id = p.person_id) GROUP BY posts.id //NEVER MIND THE SYNTAX ERROR WITH THE ALIASES 

Mette esattamente lo stesso carico mentale sul programmatore.

Tutti i grandi DBMS hanno i loro sapori ed estensioni; altrimenti perché mai ci sarebbe più di uno di loro?

Seguire rigorosamente gli standard SQL è bello e tutto, ma fornire estensioni con più funzionalità è ancora meglio . La citazione dalla documentazione spiega come questa funzionalità è utile.

Non c’è molto conflitto in questo caso, quindi non vedo davvero il problema.