SQL Greatest(), least()

1. ⚡Nicht-konforme Null-Behandlung
2. Alternative: Skalare Min- und Max-Funktionen
3. Nicht-konforme Behandlung von Null-Elementen in Zeilen-Werten

Die SQL-Funktionen Greatest (größter) und Least (kleinster) akzeptieren ein oder mehrere✓✗ Argumente und liefern den größten oder kleinsten Wert, wenn keines der Argumente Null ist✓✗.

SELECT GREATEST(1, 2, 3)
  FROM …

Greatest und Least sind skalare Funktionen. Anders als die Aggregatfunktionen Min und Max betrachten sie jede Zeile einzeln.

Null-Behandlung

Der SQL-Standard legt fest, dass Greatest und Least den Null-Wert liefern, sobald eines der Argumente Null ist. Das ist also anders als bei den meisten Aggregatfunktionen, insbesondere Min und Max. Während Min und Max Null-Werte ignorieren und den kleinsten oder größten Wert der restlichen Menge liefern, respektieren Greatest und Least Null-Werte insofern, als dass die Präsenz eines einzelnen Null-Wertes bedeutet, dass der größte oder kleinste Werte nicht zweifelsfrei feststellbar ist.

Wie man in der Tabelle sehen kann, wird die vom Standard vorgesehene Null-Behandlung nicht immer umgesetzt. Das ist auch keine Überraschung, da Greatest und Least oft schon Jahrzehnte bevor sie 2023 in den Standard aufgenommen wurden, verfügbar waren.

Ich persönlich denke, dass die Standardisierung von Greatest und Least mit strikter Respektierung von Null-Werten nicht die beste Entscheidung war. Der SQL-Standard wird ohnehin nicht sehr genau befolgt. Neues Verhalten einzufordern, das Jahrzehnte alten Implementierungen0 widerspricht, macht es nicht besser, da sich die alten Implementierungen wahrscheinlich nicht an den neuen Standard anpassen werden. Da beide Varianten Null-Werte zu behandelt schon seit geraumer Zeit im Umlauf sind, hätte keine der Varianten zum Standard erhoben werden sollen. Es ist einfach ein weiteres Verhalten, dass durch die Umsetzung vorgegeben wird.1

In die Zukunft blickend hoffe ich, dass die Benutzer die Wahl haben werden. Zum Beispiel mit respect nulls und ignore nulls-Angaben, wie es sie bei manchen Window-Funktionen gibt.

LEAST(…) [ RESPECT NULLS | IGNORE NULLS ]

Die Filter-Klausel, wie man sie von Aggregatfunktionen kennt, wäre zwar generischer, löst aber das Problem der Umsetzungen nicht, die Null-Werte schon lange ignorieren.

Zeilenwerte

In Standard-SQL kann ein Wert, dessen Vergleich mit einem einzigen anderen Wert unknown ergibt, nicht das Ergebnis von Greatest oder Least sein.

Angewendet auf die folgenden Beispiele bedeutet das, dass ⓵ and ⓶ jeweils den ersten Wert liefern, weil es keinen anderen Wert gibt, der noch kleiner sein könnte.

⓵ LEAST( ROW(1   ,    1),  ROW(1,    2) )
⓶ LEAST( ROW(1   , null),  ROW(2, null) )
⓷ LEAST( ROW(1   , null),  ROW(1, null) )
⓸ LEAST( ROW(null,    1),  ROW(9,    2) )

Falls du dich bezüglich ⓶ wunderst bedenke, dass Bereichsvergleiche auf Zeilenwerte zweifelsfrei festgelegt sind, wenn ein Präfix eine zweifelsfreie Antwort gibt. Das ist im Beispiel ⓷ nicht der Fall: Obwohl beide Werte ununterscheidbar sind, sind sie entsprechend der normalen Vergleichsregeln für Null nicht gleich. Ein Kleiner-gleich-Vergleich (<=) liefert unknown. Bei keinem Wert kann ausgeschlossen werden, dass es einen noch kleineren gibt, sodass Least den Null-Wert liefert. Das Gleiche gilt für Beispiel ⓸. Da das erste Element eines Wertes Null ist, ergeben alle Vergleiche mit diesem Wert unknown. Es kann keinen Wert geben, der zweifelsfrei kleiner-gleich allen anderen ist. Least liefert wieder Null. Das sind lediglich die Folgen des normalen Verhaltens von Null in SQL: Null stellt Ungewissheit dar, Ungewissheit breitet sich aus.

Auch hier ist es keine Überraschung, dass sich alte Systeme nicht entsprechend dem neuen Standard verhalten. Die wenigen der getesteten Systeme, die Greatest und Least mit Zeilenwerten unterstützen, wenden eine Logik auf Basis einer Sortierung an, d.h. sie behandeln Null-Elemente als größt- oder kleinst-möglichen Wert. Ob es ein besonders großer oder kleiner Wert ist, hängt vom System ab – wie auch bei Default-Sortierung von Null-Werte in der Order By-Klausel (ID133). Zum Glück machen es die Systeme bei Greatest und Least genauso, wie sie es auch bei Order By machen.

Limits

Der SQL-Standard sieht für Greatest und Least ein oder mehrere Argumente vor. In der Praxis gibt es einige Systeme die den (wenig nützlichen) Ein-Argument-Fall nicht unterstützen. Die Obergrenze liegt meist jenseits meiner Tests.

Alternativen

Manche SQL-Dialekte haben skalare Varianten der Funktionen Min und Max, die mehrere Argument akzeptieren und den kleinsten oder größten davon liefern.

1. Respektiert Null-Werte: Ein Null-Wert mach das Ergebnis Null
2. Respektiert Null-Werte: Ein Null-Wert mach das Ergebnis Null • Nicht-konformer Umgang mit Collations

Weiterführende Links

• NULL: Platzhalter für fehlende Daten

• Filter: Selektive Aggregate

Normative Referenzen

Die Funktionen Greatest und Least bilden die optionale Funktion T054, „GREATEST and LEAST“.