Tag Archives: Bug

Fiese Falle in MySQL bei Verwendung von Views in (Left)-Joins

Posted on by

Da habe ich doch kurz vor Weihnachten noch eine echte Knobelaufgabe erlebt, deren Ergebnis für den ein oder anderen sicherlich interessant sein dürfte. Ausgangspunkt war folgendes eigentlich simple SQL-Statement:

Select * from tableA left join viewC 
     on tableA.id=viewC.lookup

Soweit so unspektakulär, auch wenn das Original noch mit einigen weiteren Filtern gespickt war und die View nicht gerade ein einfaches Query kappselt.

Was allerdings gar nicht lustig war, waren die Ergebnisse dieser Abfrage, denn sie entsprachen nicht den Erwartungen. Aus der View erhielt ich nämlich keinen Join-Partner obwohl ich diesen als Test extra geprüft hatte mit:

 Select * from viewC where lookup='TestValue'

Folgende Punkte habe ich als „übliche Verdächtige“ abgeprüft:

  • Vergleich der Datentypen – auch nicht die Ursache
  • Collations (also Zeichensätze) – auch mit Casting, das Ergebnis bleibt das Gleiche

Spannender Nebeneffekt, der bei der weiteren Untersuchung heraus kam – nutze ich die view nicht sondern packe 1:1 den Code aus der Definition mit in mein Statement

 
Select * from tableA left join 
   (select * from tableB 
     left join tableF 
         on tableB.id=tableF.foo 
      where tableF.bar='baz' and deletedDate is null)
   as testview on testview.id=tableA.id

Dann funktioniert wieder alles wie erwartet. Es staunt der Laie und der Fachmann wundert sich. Höchste Zeit das Problem genauer unter die Lupe zu nehmen und die bekannten Bugs von MySQL bzw. den Deviraten wie MariaDB und Percona zu durchforsten. Ergebnisse sind leider recht dürftig und beschreiben nicht das was ich erlebe. Immerhin stoße ich auf weitere Probleme die erst einmal völlig losgelöst von der Thematik erscheinen, es geht dabei um möglich Einstellungen des Servers und den Standard-Verhalten. Das hat sich in den letzten Jahren immer mal wieder etwas verändert und spiegelt des „Erwachsen werden“ von MySQL wieder. Wir erinnern uns: Es hat eine geraume Zeit gedauert bis MySQL endlich als Standard auf INNODB als Engine gewechselt hat und somit referenzielle Integrität in Form von Transaktionen und Foreign Keys unterstützt wurden. Ebenso aus der grauen Urzeit von MySQL stammen Probleme mit NULL-Werten bei Datumsfeldern (DATE,DATETIME). Und genau da liegt der Hase am Ende auch begraben. Die Server-Konfiguration gibt vor, dass der Server sich strikt verhalten soll, unter anderem bei den Datumsfeldern.
Somit ist es nicht mehr zulässig den „uralten NULL-Ersatz 0000-00-00 00:00:00“ zu verwenden. Die Routinen für den Ersatz kann man aber per Session einschalten, und genau das ist bei der manuellen Abfrage der Werte jedes mal passiert, sei es per manuellem Join oder bei der Abfrage der einzelnen Tabellen. Dabei verhält sich das isnull je nach eingestelltem Modus. Mal liefert es Zeilen mit „0000-00-00 00:00:00“ als Ergebnis, mal nicht, je nachdem ob strict-mode gesetzt ist oder nicht.

Warum läuft es dann beim Verwenden der View aus dem Ruder? Das liegt an der Art und Weise wie MySQL mit Views umgeht: Sind diese übermäßig komplex werden sie als separater Thread gestartet, der eine temporäre Tabelle bereitstellt auf die dann wiederum gejoined werden kann. Dieser Thread wird allerdings vom Serverprozess angestoßen, und dort steht der Modus per default auf strict und dieser wird dann auch angewandt. Somit ist das Ergebnis nicht das was ich bei der singulären Abfrage erhalten habe, sondern leider eben ein leeres da in der Tabelle anstelle NULL eben noch „0000-00-00 00:00:00“ verwendet wird.

Die temporäre Lösung ist, den Join manuell auszuführen, mittelfristig werden wir den Server umstellen. Das sind aber eigentlich nur kosmetische Lösungen. Die eigentlich korrekte Maßnahme für dieses Problem ist die Verwendung von sauberen NULL-Werten anstelle obskurer Workarounds. Leider verlassen sich sehr viele Applikationen bei uns noch auf die Tabelle und diese kruden Werte, einfach Umstellen ist also erstmal leider nicht drin. Ich kann jedem Entwickler nur wärmstens empfehlen, sich auf derartige Altlasten nicht zu verlassen und schnellstmöglich dafür zu sorgen diese abzubauen.