Merke: Am besten keine NULL Werte in kritischen Spalten zulassen, die für Vergleichsoperationen genutzt werden
Wenn NULL Werte mit anderen Zeichenketten verglichen werden, so ist das Ergebnis stets falsch. Dies kann zu hoher Frustration und Verwirrung führen, da es nicht das zu erwartende Verhalten wiederspiegelt.
SELECT CASE WHEN NULL = 'test' THEN 1 ELSE 0 END -- Ergebnis: 0 SELECT CASE WHEN NULL != 'test' THEN 1 ELSE 0 END -- Ergebnis: immer noch 0
Ein Anwendungsfall
Zurzeit arbeite ich an der Entwicklung eines opens-source datawarehouse stacks. Speziell den ETL Prozess betrachtet, wollen wir meist nur neue, sich geänderte Daten laden. Um Änderungen zwischen zwei Tabellen oder Selektionen zu erkennen, könnte man jede einzelne Spalte einzeln abgleichen, oder aber alle Spalten zu einer einzigen Zeichenkette konkatinieren und diesen dann abgleichen (sehr vereinfacht).
alter Eintrag
| ColA | ColB | Concatination | md5() |
| hello | world | hello_world | 99b1ff8f11781541f7f89f9bd41c4a17 |
| bingo | bongo | bingo_bongo | d0946da002ae63f07dc9e1c4489b2c91 |
neuer Eintrag
| ColA | ColB | Concatination | md5() |
| hello | world | hello_world | 99b1ff8f11781541f7f89f9bd41c4a17 |
| bingo | bongi | bingo_bongi | a303447dc3952485141a4acb6f096f45 |
Anmerkung: Die Konkatination erfolgte durch postgres’ || Operator.
SELECT md5(ColA||'_'|| ColB) FROM table
Der hash Wert kann zur Detektion von Änderungen wie folgt genutzt werden:
INSERT INTO table SELECT * FROM table WHERE old.hash != new.hash -- wenn diese Aussage wahr ist, also sich der hash unterscheidet, gibt es Änderungen AND old.pk = new.pk
Das Problem ist jedoch, dass bei solch einer Konkatination der gesamte String ein NULL ausgibt, sobald nur eine Spalte einen NULL Wert beinhaltet. Die Verhashung eines NULL Wertes ergibt wiederum ein NULL Wert und wir treffen auf das anfangs eingeführte Problem. Das bedeutet also:
- Wenn der alte hash NULL ist und der neue hash ein legitimer String, wird die neue Zeile trotzdem nicht geupdated weil keine Änderung detektiert wurde ( old.hash != new.hash resultiert in false)
- Eine Konkatination wie oben beschrieben wird immer einen NULL Wert ausgeben, sobald eine der Spalten ein NULL Wert beinhaltet. Angenommen wir haben 10 Spalten mit NULL Werten. Zu einem späterem Zeitpunkt erhalten 8 Spalten einen Wert – das Ergebnis des hashes bleibt jedoch NULL, sodass keine Änderungen durch die Logik detektiert wird, selbst dann nicht, wenn COALESCE() genutzt werden würde.
Daher würde, einmal eingefügt, dieser Eintrag mit einem NULL Wert im hash über unsere Logik keine weiteren Änderungen erfahren.
Lösung
- COALESCE(): Unter Nutzung der Funktion COALESCE() oder ISNULL() (t-sql) ist es möglich bei einem NULL Wert diesen Wert mit einem Standardwert zu ersetzen und den Vergleich ordnungsgemäß auszuführen, kann jedoch in Fälle trotzdem keine Änderungen detektieren. Beispielsweise dann nicht, wenn beide hashes NULL sind.
- IS DISTINCT FROM: Dieser Operator ist genau dazu da, um einen NULL Wert ordnungsgemäß von einer Zeichenkette zu unterscheiden und auch ein true auszugeben falls dies der Fall ist. Dieser Operator ist nicht in jedem DBMS verfügbar und stößt auf ähnliche Probleme wie das COALESCE()
- CONCAT(): Das gesamte Problem wäre nicht entstanden, wenn keine NULL Werte im hash zugelassen werden würde. Dies kann bewerkstelligt werden, indem die Funktion CONCAT() zur Stringkonkatination genutzt wird. NULL Werte werden bei der Konkatination durch eine leere Zeichenkette ersetzt, sodass unser hash in keinem Fall NULL annehmen wird.
Anmerkung: Manchmal ist es denkbar, dass das Ergebnis einer Konkatination absichtlich einen NULL Wert annehmen kann z.B. bei einem zusammengesetzten Schlüssel. Zusätzlich noch ein NOT NULL constraint auf diese Spalte und wir haben eine Art Integritätsprüfung. Wenn eine der Komponenten des Schlüssels leer ist, wird der gesamte zusammengesetzte Schlüssel zu einem NULL Wert, welches dann beim einfügen in die Spalte einen Fehler wirft. Dies wäre ein wünschenswertes Verhalten, da ein zusammengesetzter Scshlüssel mit einer fehlenden Schlüsselkomponente eventuell nicht mehr eindeutig ist und in jedem Fall noch einmal nachgeprüft werden sollte.
