Vorsicht bei MySQL 5.1+ UPDATE Statements mit JOIN

Kürzlich bin ich über ein Problem mit MySQL Statements gestolpert, das ziemlich unangenehm werden kann. Abfragen dieser Art werden nicht allzu häufig vorkommen, doch vielleicht stolpert der ein oder andere ja darüber.
Ich kann auch nicht sagen, ob dies bei allen MySQL Versionsupdates von 5.0 auf 5.1 passiert, bei mir war es unter Debian bei mehreren Tests so.

Nehmen wir folgende SQL-Abfrage:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

UPDATE table1 SET value = value + 0.05, value = IF(value > 1, 1, value) WHERE 1

[/pastacode]

Es werden also alle Werte in der Spalte um 0.05 erhöht, danach wird geprüft ob der Wert 1 überstiegen hat und in diesem Fall auf 1 gesetzt.

Wollen wir die gleiche Abfrage jetzt in Abhängigkeit einer zweiten Tabelle durchführen, könnten wir folgende Abfrage nutzen:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON (t1.id = t2.other_id) SET t1.value = t1.value + 0.05, t1.value = IF(t1.value > 1, 1, t1.value + 0.05) WHERE t2.column = 'search'

[/pastacode]

Das funktionierte bis zu einem MySQL Update von 5.0.x auf 5.1.x auch einwandfrei, doch danach tat sich ein Phänomen auf. Die Werte in der Spalte „value“ wurden bei Abfragen im Stil der 2. einfach nicht mehr höher. Abfrage 1 funktioniert weiterhin wunderbar. Ich kann nur vermuten, aber scheinbar wird in neueren Versionen bei UPDATE Anfragen, die ein JOIN verwenden der Inhalt der Spalten gecached, so dass bei der Ausführung des IF Teiles die vorhergegangene Erhöhung der Spalte nicht berücksichtigt wird, sondern der vorherige Inhalt der Spalte t1.value.

Die Abfrage muss also umgeschrieben werden:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON (t1.id = t2.other_id) SET t1.value = IF(t1.value + 0.05 > 1, 1, t1.value + 0.05) WHERE t2.column = 'search'

[/pastacode]

Bei dieser Abfrage geht das recht einfach und das Ergebnis ist wieder wie gewünscht.

Das gleiche Problem tritt auf, wenn man sich folgender Abfrage bedient:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON (t1.id = t2.other_id) SET t1.value = t1.value + 0.5, t1.value = t1.value + t2.addition WHERE t2.column = 'search'

[/pastacode]

Angenommen, dass der Wert von t1.value vor der Abfrage 0.2 war und in der Spalte t2.addition steht 0.3, so würde man erwarten, dass nach der Abfrage t1.value 1.0 enthält. Weit gefehlt, denn das Ergebnis ist 0.5. Nur die letzte Modifikation des Wertes wird berücksichtigt. Wir müssen also die Abfrage wieder umschreiben:

[pastacode lang=“sql“ message=“MySQL“ highlight=““ provider=“manual“]

UPDATE table1 as t1 LEFT JOIN table2 as t2 ON (t1.id = t2.other_id) SET t1.value = t1.value + 0.5 + t2.addition WHERE t2.column = 'search'

[/pastacode]

So funktioniert auch dies wieder.