“Welcher Tag ist heute?”
select 'Mittwoch' as Wochentag
from dual;
“Das funktioniert aber nur in einem von sieben Fällen.”
“Quatsch! Ich habe das heute mehr als zwanzigmal getestet. War immer
richtig.”
Dieser alte Informatiker-Witz, den es in Variationen für beinahe jede Programmiersprache gibt, lässt mich immer wieder schmunzeln.
Anfang des Jahres bekam ich den Auftrag, einen neuen Report für die Geschäftsleitung eines Kunden zu erstellen.
Der Report sollte für Kennzahlen den bisherigen Jahresverlauf aufsummieren und fortan jedes Quartal laufen.
Die passenden Abfragen waren schnell erstellt. Da kritische Geschäftsentscheidungen auf den erzeugten Zahlen basieren würden, wurde er vor der Freigabe intensiv durch die QA getestet und vom Fachbereich auf Plausibilität geprüft. Alles schien korrekt und andere Aufgaben verdrängten den Report aus meiner Aufmerksamkeit.
Bis zu dem aufgeregten Anruf der Fachabteilung kurz vor Ostern: "Mit dem Report stimmt etwas nicht – die Zahlen sind zu niedrig."
Die Fehlersuche war aufwändig – alles wurde infrage gestellt, sämtliche Daten- und Prozessflüsse überprüft.
Andere Reports auf denselben Daten, die auf Tages- oder Monats-Granularität liefen, waren korrekt, deshalb fingen wir an, die Basics zu überprüfen:
Der Report basierte direkt auf CSV-Quelldaten, die unverändert in ein Staging-Schema geladen wurden.
Die Datumsfelder waren in einzelne Spalten aufgeteilt: Jahr;Monat;Tag;Stunde;Minute;Sekunde.
Der Report sollte Jahreswerte aggregieren, folgerichtig kam die SQL-Funktion
to_date(Jahr, 'YYYY')
zum Einsatz.
An dieser Stelle brach mein Weltbild zusammen: Das gute alte to_date das ich schon seit beinahe dreißig Jahren verwende ist nicht deterministisch, wenn es mit Datums-Teilstrings aufgerufen wird:
Zeile | FUNKTION | ERGEBNIS | ERWARTET |
1 | sysdate | 20-NOV-2022 17:54:39 | Ja: aktuelles Datum und Uhrzeit |
2 | to_date(‘2021-02’, ‘YYYY-MM’) | 01-FEB-2021 00:00:00 | Ja: 1. Tag von Monat und Jahr |
3 | to_date(‘03’, ‘MM’) | 01-MAR-2022 00:00:00 | Ja: 1. Tag des angegebenen Monats im aktuellen Jahr |
4 | to_date(‘2021’, ‘YYYY’) | 01-NOV-2021 00:00:00 | Nein: erwartet wurde der erste Tag des ersten Monats im angegebenen Jahr |
5 | to_date(‘22’, ‘DD’) | 22-NOV-2022 00:00:00 | Nein: erwartet wurde der angegebene Tag im ersten Monat des aktuellen Jahrs |
6 | to_date(‘13’, ‘HH24’) | 01-NOV-2022 13:00:00 | Nein: erwartet wurde die Stunde am heutigen Tag oder am 1.1. |
Die ersten drei Zeilen habe ich genauso erwartet und schon oft so verwandt. Dass für Datumsmasken ohne Jahresangabe an jedem ersten Januar das zurückgelieferte Datum ein Jahr weiter springt, ist logisch.
Das Verhalten der letzten drei Zeilen hatte ich so nicht erwartet.
Die Ursache für unser Problem ist in Zeile 4 zu finden: to_date('2021', 'YYYY') liefert über das Jahr zwölf verschiedene Ergebnisse zurück.
Im Januar – zum Zeitpunkt von Entwicklung und Test des Reports – den erwarteten "1.1.2021", und im April den "1.4.2021".
Der Fix war dann schnell gemacht: Das Eingabestring für to_date wird jetzt aus allen sechs Datum- und Uhrzeitfeldern zusammengesetzt und anschließend mit
trunc(to_date(...), 'YYYY')
zu jeder Zeit korrekt abgeschnitten.
Das Verhalten von to_date sieht wie ein Bug aus, ist aber tatsächlich genau so dokumentiert, wenn auch sehr gut versteckt und nicht an der Stelle, an der ich es erwarten würde...
Wir haben im Projekt unsere Lehren daraus gezogen und neue Regeln aufgestellt:
- Wir testen Funktionalitäten, die mit dem Tagesdatum verbunden sind zu verschiedenen Systemzeiten.
- Stage-Tabellen sind als Datenquellen für Reports jetzt untersagt.
- Ich selbst werde auch zu Funktionen, die ich gut kenne, öfter in der Doku nachlesen.
Es laufen natürlich schon Wetten, wer welche der neuen Regeln als erstes so bricht, dass es erneut zu unerwarteten und unerklärlichen Fehlern kommt.
Robert Marz
Themenverantwortlicher Cloud, Stellv. Themenverantwortlicher DevOps
Bild von JamesDeMers auf Pixabay
