Der merkwürdige Fall des Aggregation Query

Neulich hat eine interessante Abfrage die Runde gemacht. Der begleitende Artikel von Justin Jaffray vergleicht vier Abfragen, wovon eine sehr viel Aufmerksamkeit erregt hat. Sieh dir das Szenario doch selbst an. Zuerst die benötigten Tabellen.

CREATE TABLE aa (a INT);
INSERT INTO aa VALUES (1), (2), (3);
CREATE TABLE xx (x INT);
INSERT INTO xx VALUES (10), (20), (30);

Darin versteckt sich noch keine Falle: zwei Tabellen, die jeweils drei Zeilen beinhalten. Beachte nur, dass die Spalte a zur Tabelle aa gehört und x zu xx. Und jetzt zur mysteriösen Abfrage:

SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa

Was liefert sie wohl?

Selbst wenn man genau schaut, besteht die Chance, dass man auf das falsche Ergebnis kommt – wie es mir passiert ist.

Weiter unten sehen wir, dass sich nicht einmal SQL-Systeme einig sind, was das richtige Ergebnis ist. Manche antworten mit den Werten 3, 6 und 9 in drei Zeilen. Obwohl das Sinn ergibt, ist es nicht das, was der SQL-Standard festlegt.

Das standard-konforme Ergebnis ist nämlich eine einzelne Zeile mit dem Wert 6. Das ist auch das Ergebnis, das die meisten getesteten Systeme liefern. Überrascht? Da bist du nicht allein. Um zu sehen, wie es dazu kommt, zitiere ich aus dem aktuellen Standard (SQL:2023) und um den Ursprung dieses Verhaltens zu finden, auch aus älteren.

Letztendlich läuft es darauf hinaus, dass Aggregatfunktionen (genauer set function specifications) nicht unbedingt zu jenem Select gehören, in dem sie stehen. Im Beispiel von oben gehört der Ausdruck sum(a) nämlich nicht zur inneren Abfrage, in der er steht, sondern zur äußeren. Die Abfrage ist letztendlich so zu verstehen:

SELECT (SELECT sum_a
          FROM xx
         LIMIT 1
       )
  FROM (SELECT sum(a) AS sum_a
          FROM aa
       ) nested

Zuerst führt die Abfrage in der From-Klausel die Aggregation durch. Die Unterabfrage in der Select-Klausel greift dann lediglich auf das Ergebnis zu. Damit wird auch der Nutzen der Limit-Klausel klar: Ohne diese würde die Unterabfrage im Select alle drei Zeilen der Tabelle xx liefern und dadurch zu einem Fehler führen. Standardkonforme Systeme verhalten sich tatsächlich so.

Die entscheidende Frage ist natürlich: Warum zum Teufel? Diese Frage ist zweideutig zu verstehen: (1) Was steht im Standard, das zu diesem ungewöhnlichen Ergebnis führt? (2) Warum wurde das Standard-SQL verhalten so definiert?

Zur Beantwortung der ersten Frage genügt ein Blick in den aktuellen Standard. Konkret in ISO/IEC 9075-2:2023 §6.9 SR 6:

The aggregation query of a <set function specification> SFS is determined as follows.

Case:

1. If SFS has no aggregated column reference […]

2. Otherwise, the innermost qualifying query of the aggregated column references of SFS is the aggregation query of SFS.

Der erste Satz führt den Begriff aggregation Query ein. Über diesen Begriff wird dann festgelegt, welche Zeilen die Aggregatfunktion verarbeitet – und wo nötigenfalls group by () ergänzt wird.0 Der Fall „a“ ist nicht anwendbar, da das Argument der Sum-Funktion einen Spaltenverweis hat – konkret a. Fall „b“ ist der Grund, warum wir hier sind: Er legt die innerste der qualifizierenden Abfragen als aggregation Query fest. Qualifizierende Abfragen sind wiederum jene, in deren From/Join-Klausel Tabellen einführen, auf die ein Argument der Aggregatfunktion verweist.1 Sehen wir uns dazu nochmals die ursprüngliche Abfrage an.

SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa

Die einzige Spaltenreferenz der Sum-Funktion verweist auf eine Spalte der Tabelle aa, die in der From-Klausel der äußeren Abfrage eingeführt wird. Daher gehört der Ausdruck sum(a) zum äußeren Select. Da diese nun ein aggregation Query ohne explizite Group By-Klausel ist, wird sie de facto um group by () ergänzt, sodass das endgültige Ergebnis nur eine Zeile hat.

Umgekehrt gibt es keine Aggregatfunktion, für die die innere Abfrage eine qualifizierende Abfrage ist. Daher ist die innere Abfrage kein aggregation Query sodass sie auch nicht um group by () ergänzt wird. Daraus folgt, dass die innere Abfrage, die durch ihre Position in der Select-Klausel auf eine Zeile beschränkt ist, grundsätzlich alle drei Zeilen der Tabelle xx liefert. Nur die Limit-Klausel verhindert den entsprechenden Fehler.2

Da wir jetzt wissen, wie sich Standard-SQL in diesem Fall verhält, sehen wir an, welche Systeme sich daran halten.

1. Das aggregation Query ist immer jenes, in der die Mengenfunktion steht
2. Ein äußerer Verweis muss der einzige Verweis sein (unterstützt F441 nicht)

Nur die Hälfte der getesteten Systeme verhält sich standardkonform. Das alleine gibt schon zu denken, ob der Standard hier nicht ein implementierungsabhängiges Verhalten einführen sollte. Wie auch immer, im Chart kann man auch sehen, dass sich das Verhalten Vergangenheit manchmal geändert hat. Daher habe ich mir auch die älteren Ausgaben des Standards angesehen. Das obere Zitat ist offenbar „erst“ in 2003 in den Standard gekommen. Das legt zumindest der MySQL Bug Report 27333 nahe. Dort wird das damalige Verhalten von MySQL mit dem Hinweis auf „the SQL2003 draft: (section 6.9)“ als falsch beanstandet. Sinngemäß wird in der Fehlereinmeldung auch die oben zitierte Definition des aggregation Query wiedergegeben.

In SQL:1999 war diese Angelegenheit noch völlig anders geregelt. Der Begriff aggregation Query kam in SQL:1999 nicht vor. Andere Formulierungen, die das aktuelle Verhalten beschreiben, habe ich nicht gefunden. Geht man noch weiter zurück, in den weithin bekannten SQL-92-Entwurf, der leicht online zu finden ist, findet man wiederum eine andere Formulierung. Das Fehlen der Group By-Klausel wurde nicht durch die Ergänzung um Group By () gelöst. Stattdessen gab es eine allgemeine Regel – keine Syntax Regel –, die sich nötigenfalls um die implizite Gruppierung gekümmert hat. In ISO/IEC 9075:1992 §7.9 GR 1ai heißt es:

If the <select list> contains a <set function specification> that contains a reference to a column of T [or …], then [… and] the result of the <query specification> is a table consisting of 1 row.

Die Hervorhebungen und Auslassungen kommen von mir. Beachte, dass bei der mysteriösen Abfrage das äußere Select die Funktion Sum beinhaltet (contains), weil der Begriff contains rekursiv definiert ist.3 Der Einfachheit halber noch mal die Abfrage.

SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa

Da die äußere Select-Liste eine <set function specification>, konkret sum(a), beinhaltet, ist das Ergebnis der äußeren Abfrage eine Tabelle mit einer Zeile. Das merkwürdige Verhalten ist also schon seit mindestens 1992 im Standard. Da ich keinen Zugriff auf noch ältere Ausgaben des Standards habe, endet mein archäologischer Exkurs hier.

Ach, eine Frage hätte ich da noch … Wie kommt es, dass SQL Server bei äußeren Verweisen in Aggregatfunktionen so pingelig ist? Wenn man die Abfrage zum Beispiel auf sum(a + x) ändert, erhält man Fehlermeldung 8124: In einem Aggregatausdruck, der einen äußeren Verweis enthält, sind mehrere Spalten angegeben. Ein Aggregatausdruck, der einen äußeren Verweis enthält, darf auf keine weitere Spalte verweisen.

Nach nochmaliger Durchsicht des aktuellen Standards stellt sich heraus, dass es eine dementsprechende Einschränkung gibt, die allerdings durch die optionale Funktion F441 aufgehoben wird.

Without Feature F441, “Extended set function support”, conforming SQL language shall not contain a <value expression> simply contained in a <general set function> that contains a column reference that is an outer reference where the <value expression> is not a column reference.

Damit ist diese Formulierung sogar strenger als jene der SQL Server Fehlermeldung: Ein äußerer Verweis schließt in Standard-SQL alles andere aus – nicht nur weitere Spaltenverweise. Aus Neugier habe ich dann auch SQL-92 nochmals durchforstet und in ISO/IEC 9075:1992 §6.5 SR 4 etwas Interessantes gefunden:

If the <value expression> contains a <column reference> that is an outer reference, then that outer reference shall be the only <column reference> contained in the <value expression>.

In SQL:1992 gab es also exakt jene Einschränkung, die die Fehlermeldung von SQL Server noch heute ausdrückt.

Nachdem auch der Zusammenhang zwischen SQL Server’s Einschränkung und dem Standard geklärt ist, bleibt nur noch die eine ultimative Frage: Warum zum Teufel … Warum zum Teufel wurde das Verhalten jemals eingeführt?