Die ANY_VALUE(…) Aggregatfunktion

1. ⚡Liefert gelegentlich null, selbst wenn nicht-null Werte in der Gruppe sind
2. ⚡Keine Aggregatfunktion
3. Ohne Filter-Klausel
4. Nicht als Fensterfunktion (Over-Klausel)

Die Aggregatfunktion Any_value liefert einen willkürlichen nicht-null Wert aus einer Gruppe von Zeilen.

SELECT produkt_id
     , ANY_VALUE(order_id) AS beispiel_order_id
  FROM bestellposten
 GROUP BY produkt_id

Die Abfrage liefert alle jemals bestellten Produkt_id-Werte und eine zugehörige order_id als Beispiel für jedes Produkt.

Als Aggregatfunktion kann Any_value um die Filter- und Over-Klauseln ergänzt werden0, wenn diese vom System generell unterstützt werden.

1. ⚡Liefert gelegentlich null, selbst wenn nicht-null Werte in der Gruppe sind
2. ⚡Liefert gelegentlich null, selbst wenn nicht-null Werte in der Gruppe sind • ⚡Keine Aggregatfunktion

Beachte, dass Any_value nicht-deterministisch ist. Es könnte jedes Mal einen anderen Wert aus der Gruppe nehmen – d. h. das Ergebnis ist nicht reproduzierbar. Das kann bei Window-Funktionen besonders überraschend sein:

SELECT ANY_VALUE(x) OVER()
  FROM …

Die Any_value-Funktion wird hier „über alles“ angewendet. In diesem Fall liefert jede andere Aggregatfunktion auch das selber Ergebnis für jede Zeile – reproduzierbar. Der SQL-Standard fordert dieses Verhalten allerdings nicht für Any_value. Es könnte für jede Zeile einen anderen Wert auswählen, selbst wenn die Over-Klausel immer dieselben Zeilen umfasst. Wohlgemerkt: Bisher hat keiner meiner Tests jemals ein solches Verhalten beobachtet.

Min und Max erfüllen übrigens alle Anforderungen, die der Standard an Any_value stellt. Man kann also statt Any_value auch Min oder Max verwenden und damit ein deterministisches Ergebnis erhalten. Der Hauptunterschied ist, dass Any_value effizienter umgesetzt sein könnte, da es keinen bestimmten Wert suchen muss, sondern den erstbesten nicht-null Wert nehmen kann.

Zum Abschluss möchte ich noch ausführlich auf einen problematischen Anwendungsfall von Any_value eingehen: Wie man damit umgeht, wenn das Feature T301, „Functional dependencies“ nicht umgesetzt ist. Dazu folgendes Beispiel:

CREATE TABLE kunden (
 id   BIGINT PRIMARY KEY,
 name VARCHAR,
 […]
)

CREATE TABLE bestellungen (
 kunde BIGINT REFERENCES kunden(id),
 von   TIMESTAMP,
 […]
)

SELECT kunden.*, MAX(von)
  FROM kunden
  LEFT JOIN bestellungen
         ON kunden.id = kunden.customer
 GROUP BY kunden.id

Die Abfrage listet alle Kunden auf und liefert den Zeitpunkt der jeweils letzten Bestellung. Beachte, dass alle Spalten der Kunden-Tabelle ausgegeben werden – also auch die Spalte Name. Die Group by-Klausel nennt jedoch nur die Spalte Kunden.id. Ist diese Abfrage überhaupt erlaubt?

Ja, ist sie. Sowohl aus rein logischer Sicht alsauch aus der Sicht des SQL-Standards. Der Knackpunkt ist, dass der Kundenname eine funktionale Abhängigkeit vom Primärschlüssel der Kunden-Tabelle hat. Für jede Gruppe von Zeilen, für die Kunden.id den selben Wert hat, muss zwangsläufig auch Kunden.name denselben Wert haben. Der SQL-Standard achtet sehr darauf, die bekannten funktionalen Abhängigkeiten zu dokumentieren, stellt es aber den Umsetzungen frei, diese zu ignorieren. T301, „Functional dependencies“ ist eine optionale Funktion von SQL.

1. ⚡ Schlägt nicht fehl, wenn keine funktionale Abhängigkeit besteht (per Voreinstellung)

Dieser Aspekt wird von den Herstellern jedoch stark vernachlässigt. Einige Systeme liefern für die Abfrage einen Fehler, was lediglich bedeutet, dass sie die optionale Funktion nicht umsetzen. Andere Systeme (⚡) ignorieren das Problem und erlauben es, beliebige Spalten direkt auszugeben – auch jene, die keine funktionale Abhängigkeit haben. Diese Systeme implizieren also Any_value.

Das bedeutet für SQL-Nutzer vor allem folgendes: Obwohl die Abfrage logisch korrekt ist, könnte sie einen Fehler auslösen (kein T301) oder versehentlich das richtige Ergebnis liefern (impliziert Any_value). Nur zwei von neun getesteten Systemen liefern das gewünschte Verhalten aus dem richtigen Grund. Das ist nicht unbedingt eine Wette, die ich eingehen möchte.

SQL-92 hatte zwei Möglichkeiten, die Abfrage so zu verfassen, dass sie überall richtig funktioniert: (1) funktional abhängige Spalten, die nicht in der Group by-Klausel aufscheinen, über Min oder Max abfragen; (2) die Group by-Klausel um diese Spalten erweitern. Die SQL:2023 Funktion Any_value ist lediglich eine dritte Variante von Option (1). Keine dieser Möglichkeiten ist Benutzerfreundlich. Schlimmer noch: Sie schützen nicht vor Fehlern wie zum Beispiel wenn man eine Spalte irrtümlich in die Group by-Klausel aufnimmt.1

SQL:1999 hat aber eine weitere Möglichkeit eingeführt, die alle genannten Probleme löst: Das (optionale) Feature T301, „Functional dependencies“. Daher möchte ich diese Artikel mit einem Appell an die Hersteller abschließen: Bitte Mehraufwand auf euch und setzt lieber T301 als T626 (any_value) um. Das ist das, was man sich als Benutzer erwartet: kein Aufwand, maximale Sicherheit. An dieser Stelle gebührt dem MySQL-Team für die Umsetzung in Version 5.7 ein großer Dank!

Der einzige Wermutstropfen ist, dass man zum erkennen funktionaler Abhängigkeiten Constraints braucht. Systeme ohne Constraints, wie zum Beispiel ein Data Warehouse, können eben nicht davon profitieren. Für diesen Fall wäre vielleicht eine Funktion wünschenswert, die prüft ob es nur einen nicht-null Wert gibt. Vielleicht Unique_value(… [null|error] on error)? Oder Not enforced trusted Constraints wie bei Db2?