Verstößt MySQL gegen den Standard, wenn es die Auswahl von Spalten erlaubt, die nicht Teil der Gruppenklausel sind?

Question

Ich bin mit Microsoft-Technologien einschließlich SQL Server vertraut. Heute stieß ich auf a Q&A wo die folgende Passage aus der MySQL-Dokumentation zitiert wurde:

Standard-SQL würde Ihre Anfrage zurückweisen, da Sie nicht SELECT Nicht-Aggregatfelder, die nicht Teil der GROUP BY-Klausel sind, in einer Aggregat-Abfrage. MySQL erweitert die Verwendung von GROUP BY so, dass die Select Liste auf nicht-aggregierte Spalten verweisen kann, die nicht in der GROUP BY Klausel genannt werden. Das bedeutet, dass die vorangehende Abfrage in MySQL legal ist. Sie können diese Funktion nutzen, um eine bessere Leistung zu erzielen, indem Sie unnötige Spaltensortierung und -gruppierung vermeiden. Dies ist jedoch hauptsächlich dann nützlich, wenn alle Werte in jeder nicht gruppierten Spalte, die nicht in der GROUP BY genannt werden für jede Gruppe gleich sind. Dem Server steht es frei, jeden Wert aus jeder Gruppe zu wählen, d.h. wenn sie nicht gleich sind, sind die gewählten Werte unbestimmt .

Verstößt MySQL gegen den Standard, wenn es dies zulässt? Inwiefern? Was ist das Ergebnis, wenn dies zugelassen wird?

Answer 1

Standard-SQL würde Ihre Anfrage ablehnen, weil Sie kann nicht SELECT nicht-aggregierte Felder die sind nicht Teil der GROUP BY-Klausel in einer aggregierten Abfrage

Dies ist korrekt, bis 1992 .

Aber es ist ab 2003 und darüber hinaus schlichtweg falsch.

Aus dem SQL-2003-Standard, 6IWD6-02-Foundation-2011-01.pdf, von http://www.wiscorp.com/ Abschnitt 7.12 (Abfragespezifikation), Seite 398 :

17. Wenn T eine gruppierte Tabelle ist, sei G die Menge der Gruppierungsspalten von T. In jedem ((Wertausdruck)), der ((Auswahlliste)) muss jeder Spaltenverweis, der auf eine Spalte von T verweist, auf eine Spalte C verweisen, die ist. funktionell abhängig zu G ou werden in einem aggregierten Argument enthalten einer ((Mengenfunktionsangabe)) deren Aggregationsabfrage QS ist

---

Jetzt hat MYSQL diese Funktion implementiert, indem es erlaubt nicht nur Spalten, die funktionell abhängig zu den Gruppierungsspalten mais unter alle Spalten . Dies führt zu einigen Problemen mit Benutzern, die nicht verstehen, wie die Gruppierung funktioniert und unbestimmte Ergebnisse erhalten, wo sie es nicht erwarten.

Aber Sie haben Recht, wenn Sie sagen, dass MySQL eine Funktion hinzugefügt hat, die mit den SQL-Standards kollidiert (obwohl Sie das aus dem falschen Grund zu glauben scheinen). Das ist nicht ganz richtig, da sie eine SQL-Standardfunktion hinzugefügt haben, aber nicht auf die beste Art und Weise (eher auf die einfache Art und Weise), aber sie steht im Konflikt mit den neuesten Standards.

Um Ihre Frage zu beantworten: Der Grund für diese MySQL-Funktion (Erweiterung) ist vermutlich, dass sie den neuesten SQL-Standards (2003+) entspricht. Warum sie sich entschieden haben, es auf diese Weise zu implementieren (nicht vollständig konform), können wir nur spekulieren.

Wie @Quassnoi und @Johan mit Beispielen beantworteten, ist es hauptsächlich ein Problem der Leistung und der Wartbarkeit. Aber man kann das RDBMS nicht einfach so ändern, dass es klug genug ist (Skynet ausgenommen), um funktional abhängige Spalten zu erkennen, also haben die MySQL-Entwickler eine Entscheidung getroffen:

Wir (MySQL) geben Ihnen (MySQL-Benutzern) diese Funktion, die in den SQL-2003-Standards enthalten ist. Es verbessert die Geschwindigkeit in bestimmten GROUP BY Fragen, aber es gibt einen Haken. Sie müssen vorsichtig sein (und nicht die SQL-Engine), damit Spalten in der SELECT y HAVING Listen sind funktional abhängig von der GROUP BY Spalten. Andernfalls können Sie unbestimmte Ergebnisse erhalten.

Wenn Sie diese Funktion deaktivieren möchten, können Sie sql_mode a ONLY_FULL_GROUP_BY .

Es ist alles in der MySQL-Dokumente: Erweiterungen für GROUP BY (5.5) - allerdings nicht im obigen Wortlaut, sondern wie in Ihrem Zitat (sie haben sogar vergessen zu erwähnen, dass es sich um eine Abweichung vom Standard SQL-2003 und nicht vom Standard SQL-92 handelt). Diese Art von Entscheidungen ist meiner Meinung nach bei jeder Software üblich, auch bei anderen RDBMS. Sie werden aus Gründen der Leistung, der Abwärtskompatibilität und aus vielen anderen Gründen getroffen. Oracle hat das berühmte '' is the same as NULL zum Beispiel, und SQL-Server hat wahrscheinlich auch welche.

Es gibt auch diesen Blogbeitrag von Peter Bouman, in dem die Entscheidung der MySQL-Entwickler verteidigt wird: Entlarvung von GROUP BY-Mythen .

Im Jahr 2011, als @Mark Byers informierte uns in einem Kommentar (in einer verwandten Frage bei DBA.SE), PostgreSQL 9.1 hat eine neue Funktion (Erscheinungsdatum: September 2011) zu diesem Zweck entwickelt. Sie ist restriktiver als die Implementierung von MySQL und näher am Standard.

Später, im Jahr 2015, gab MySQL bekannt, dass in der Version 5.7 das Verhalten verbessert wurde, um dem Standard zu entsprechen und funktionale Abhängigkeiten tatsächlich zu erkennen (sogar besser als die Postgres-Implementierung). Die Dokumentation: MySQL-Behandlung von GROUP BY (5.7) und ein weiterer Blogbeitrag von Peter Bouman: MySQL 5.7.5: GROUP BY respektiert funktionale Abhängigkeiten!

Answer 2

Verstößt MySQL gegen den Standard, wenn es dies zulässt? Wie?

Damit können Sie eine Abfrage wie diese schreiben:

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

Bei anderen Systemen müssten Sie alle Spalten aus a in die GROUP BY Liste, wodurch die Abfrage größer, weniger pflegbar und weniger effizient wird.

In dieser Form (mit Gruppierung nach dem PK ), widerspricht dies nicht dem Standard, da jede Spalte in a funktional von seinem Primärschlüssel abhängig ist.

Allerdings, MySQL prüft die funktionale Abhängigkeit nicht wirklich und ermöglicht es Ihnen, Spalten auszuwählen, die funktional nicht von der Gruppierungsmenge abhängig sind. Dies kann zu unbestimmten Ergebnissen führen und sollte nicht als verlässlich angesehen werden. Das einzige, was garantiert ist, ist, dass die Spaltenwerte zu einigen der Datensätze gehören, die den Gruppierungsausdruck teilen (nicht einmal zu einem Datensatz!).

Dieses Verhalten kann deaktiviert werden durch die Einstellung sql_mode a ONLY_FULL_GROUP_BY .

Answer 3

Kurze Antwort
Es ist ein Speed-Hack

Diese Funktion ist standardmäßig aktiviert, kann aber mit dieser Einstellung deaktiviert werden: https://dev.mysql.com/doc/refman/8.0/en/group-by-handling.html

Lange Antwort Der Grund für die nicht standardmäßige Gruppierung nach der Klausel ist, dass es sich um einen Speed-Hack handelt.
MySQL lässt den Programmierer bestimmen, ob die ausgewählten Felder funktional von der Gruppenklausel abhängig sind.
Die DB führt keine Tests durch, sondern wählt einfach das erste Ergebnis, das sie findet, als Wert für das Feld aus.
Dies führt zu erheblichen Geschwindigkeitssteigerungen.

Betrachten Sie diesen Code:

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

MySQL wählt einfach den ersten Wert aus, den es findet, und verbraucht so wenig Zeit wie möglich.
f1,f3, f4 stammen aus derselben Zeile, aber diese Beziehung fällt auseinander, wenn mehrere Tabellen mit Joins beteiligt sind.

Um das zu tun derselbe etwas Ähnliches in SQL-server müssten Sie tun

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

Die DB wird nun prüfen müssen alle Ergebnisse, um den Mindestwert zu finden, und zwar mit Mühe und Not.
f1, f3, f4 haben höchstwahrscheinlich keine Beziehung zueinander und stammen nicht aus derselben Reihe.

Wenn Sie es dennoch tun:

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

Alle übrigen Felder sind funktional abhängig von id .
Rowcount ist immer 1, und f2count ist entweder 0 (wenn f2 null ist) oder 1.

Bei Joins, an denen viele Tabellen beteiligt sind, in einer 1-n-Konfiguration wie hier:

Beispiel:

Website 1 -> n Themen 1 -> n Threads 1 -> n Beiträge 1 -> 1 Person.

Und Sie machen einen komplizierten Select, der alle Tabellen einbezieht, und machen einfach einen GROUP BY posts.id
Natürlich sind alle anderen Felder funktional von posts.id (und NUR von posts.id) abhängig.
Es macht also keinen Sinn, weitere Felder in der Gruppenklausel aufzuführen oder Sie zu zwingen, Aggregatfunktionen zu verwenden.
Um die Dinge zu beschleunigen. MySQL zwingt Sie nicht, dies zu tun.

Aber Sie do müssen das Konzept der funktionalen Abhängigkeit und die Beziehungen in den Tabellen und der von Ihnen geschriebenen Verknüpfung verstehen, so dass der Programmierer eine große Last zu tragen hat.
Allerdings mit:

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

Für den Programmierer bedeutet das genau die gleiche mentale Belastung.

Answer 4

Alle großen DBMS haben ihre eigenen Ausprägungen und Erweiterungen; warum sollte es sonst mehr als eines von ihnen geben?

Die strikte Einhaltung der SQL-Standards ist schön und gut, aber die Bereitstellung von Erweiterungen mit mehr Funktionalität es noch besser . Das Zitat aus der Dokumentation erläutert den Nutzen dieser Funktion.

Es gibt keine viel Ich sehe in diesem Fall keinen Konflikt, also sehe ich das Problem nicht wirklich.