David Wheeler wird das folgende herrliche Zitat zugeschrieben (wobei gerne nur der erste Teil zitiert wird):
Any problem in computer science can be solved with another layer of indirection. But that usually will create another problem.
Was hat nun MySQL damit zu tun? Nun, die nehmen den ersten Teil wörtlich. Gegeben sei folgende Tabelle
mysql> create table foo (a int, b int);
mysql> insert into foo values (1,2);
mysql> insert into foo values (3,4);
Nun möchte ich folgendes versuchen:
mysql> update foo set a = (select a from foo where b = 2) where b = 4;
ERROR 1093 (HY000): You can't specify target table 'foo' for update in FROM clause
Damit kommen wir zu David Wheeler:
mysql> update foo set a = (select a from (select * from foo where b = 2) as x) where b = 4;
Query OK, 1 row affected (0.00 sec)
Kein weiterer Kommentar…
Naja. Du trickst MySQL einfach aus.
Das, was du da vorhast, wird absichtlich unterbunden, aber du hast einen Weg gefunden diese Sperre zu umgehen.
Die Entwickler von MySQL halten es für eine schlechte Idee ein UPDATE und einen SELECT im selben Statement auf derselben Tabelle zu machen. Über die Gründe dafür werden wir leider im Dunkeln gelassen. Ich konnte jedenfalls auf die Schnelle nichts konkretes dazu finden.
Auf jeden Fall wird die Fehlermeldung nicht aus Versehen geworfen. Sie fangen die simpelste Variante für solche verbotenen Queries ab. Du hast eine kompliziertere gefunden.
So gesehen arbeitet nicht MySQL nach David Wheeler, sondern du.
Und das neue Problem lauert wahrscheinlich irgendwo im Hintergrund und hat damit zu tun wie MySQL diese Queries auswertet.
Infos zu deinem Fehler: http://dev.mysql.com/doc/refman/5.0/en/subquery-errors.html
Auf einer anderen Seite heisst es explizit:
In MySQL, you cannot modify a table and select from the same table in a subquery. This applies to statements such as DELETE, INSERT, REPLACE, UPDATE, and (because subqueries can be used in the SET clause) LOAD DATA INFILE.
Ich gebe zu, dass ich mich gerade frage, ob du doch das Problem löst.
Vielleicht werden die Queries durch den zusätzlichen Subquery ja soweit separiert, dass das Problem nicht mehr auftritt. Für wahrscheinlicher halte ich es aber, dass MySQL durch den weiteren Subquery das potentielle Problem einfach nicht mehr abfängt. Sowas muss ja auch nicht immer knallen.
Nach Lektüre eines weiteren Blogeintrags muss ich meine Interpretation des Phänomens zurückziehen.
Offensichtlich sehen Leute, die sich mit den Internas von MySQL ausführlich auseinander gesetzt haben, das also gute Lösung an.
Bleibt die brennende Frage, weshalb dieser Workaround nötig ist, was uns zu deinem Blogeintrag führt.
von wegen Kein weiterer Kommentar… 😉
Aus dem Bauch heraus halte ich das für ein Optimierungsproblem. Beziehungsweise eine bewusste Entscheidung der MySQLer, deinen Ursprungsfall zugunsten anderer Fälle zu verbieten. Wenn die zu verändernde Tabelle auch das Datenmaterial für ebendiese Änderung liefert, besteht die Gefahr, dass sich die Katze in den Schwanz beißt. Dann kann man einfach nicht mehr nach Belieben anfangen, erste Einträge zu updaten, weil man sich damit ja die Datengrundlage beschädigt. Folglich müssen für alle Einträge erst die neuen Werte berechnet werden, bevor der erste geschrieben werden kann. Das schränkt einerseits stark ein, auf welche Weise solch Query abgearbeitet/optimiert werden kann (und vielleicht gibt das die Engine sogar absichtlich gar nicht her). Vor allem aber dürfte das im Allgemeinen grottig ineffizient sein. Man kommt wenn ich das richtig überblicke im Allgemeinen nicht drumrum, auf einer kompletten Kopie zu arbeiten. Viel Spaß bei einer großen Tabelle. Klar, wenn man wöllte, könnte man sowas zulassen. Aber ich tippe mal an dieser Stelle, dass der in der Praxis häufigere Fall ist, dass man sich eigentlich gar nicht in den Schwanz beißen möchte. Wenn man genau das also verhindert, lassen sich die gutartigen Fälle sicherlich flexibler/optimierter abarbeiten.
Und nochmal Bauchgefühl: Ich denke nicht, dass die kompliziertere Anfrage “knallt”. Ich tippe auf “ausreichend separiert”. Ob nun aufgrund der Verschachtelungstiefe oder wegen des “as x”, da muss in den SQL-Innereien vermutlich ohnehin eine komplette temporäre Tabelle gebastelt werden. Das ist dann auch völlig in Ordnung, ist ja explizit(er) so hingeschrieben und nicht nur implizite Folge von Optimierung/Inschwanzbeißvermeidung. Und wenn die temporäre dann “Datenquelle” ist an Stelle der originalen, dann gibts auch kein Problem mehr…
Man könnte bei Langeweile versuchen, einen Fall basteln, wo mit “Knall” zu rechnen ist, falls widererwartend wirklich nur die Prüfung nicht streng genug ist. Sowas wo die geupdatete Tabelle als Quelle andere Ergebnisse liefert als die originale. Ich rechne allerdings mit “Wohlverhalten” 😉