Positionen tauschen mit einem UPDATE Befehl

Folgende Situation auf MySQL 4.1.9 (das Ganze muss minimal auf 4.1.15
laufen):

Ich habe eine Tabelle mit einer künstlichen Sortierungsspalte:

CREATE TABLE my_overview (
my_overview_id int(10) unsigned NOT NULL auto_increment,
user_id int(10) unsigned NOT NULL,
position int(10) unsigned NOT NULL,
payload varchar(255) NOT NULL,
PRIMARY KEY (my_overview_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Testdaten:

INSERT INTO my_overview (user_id, position, payload)
VALUES (1, 1, 'Datensatz 1');

INSERT INTO my_overview (user_id, position, payload)
VALUES (1, 2, 'Datensatz 2');

INSERT INTO my_overview (user_id, position, payload)
VALUES (1, 3, 'Datensatz 3');

INSERT INTO my_overview (user_id, position, payload)
VALUES (2, 1, 'Datensatz 4');

INSERT INTO my_overview (user_id, position, payload)
VALUES (2, 2, 'Datensatz 5');

INSERT INTO my_overview (user_id, position, payload)
VALUES (2, 3, 'Datensatz 6');

SELECT * FROM my_overview\G
*************************** 1. row ***************************
my_overview_id: 1
user_id: 1
position: 1
payload: Datensatz 1
*************************** 2. row ***************************
my_overview_id: 2
user_id: 1
position: 2
payload: Datensatz 2
*************************** 3. row ***************************
my_overview_id: 3
user_id: 1
position: 3
payload: Datensatz 3
*************************** 4. row ***************************
my_overview_id: 4
user_id: 2
position: 1
payload: Datensatz 4
*************************** 5. row ***************************
my_overview_id: 5
user_id: 2
position: 2
payload: Datensatz 5
*************************** 6. row ***************************
my_overview_id: 6
user_id: 2
position: 3
payload: Datensatz 6
6 rows in set (0,01 sec)

Folgende Query hab ich mir überlegt, um zwei Positionen zu tauschen:

UPDATE my_overview t1,
my_overview t2
SET t1.position = t1.position + 1,
t2.position = t2.position - 1
WHERE t1.my_overview_id = ? AND t1.user_id = t2.user_id
AND t1.position + 1 = t2.position;

Führen wir das aus, um Datensatz 1 mit Datensatz 2 zu tauschen:

UPDATE my_overview t1,
my_overview t2
SET t1.position = t1.position + 1,
t2.position = t2.position - 1
WHERE t1.my_overview_id = 1
AND t1.user_id = t2.user_id
AND t1.position + 1 = t2.position;
Query OK, 3 rows affected (0,00 sec)
^^^^^^
Rows matched: 3 Changed: 3 Warnings: 0
^^^^^^^^^^^^^^^ ^^^^^^^^^^
Schon einmal schlecht...

SELECT * FROM my_overview WHERE user_id = 1\G
*************************** 1. row ***************************
my_overview_id: 1
user_id: 1
position: 2
payload: Datensatz 1
*************************** 2. row ***************************
my_overview_id: 2
user_id: 1
position: 1
payload: Datensatz 2
*************************** 3. row ***************************
my_overview_id: 3
user_id: 1
position: 2
payload: Datensatz 3
3 rows in set (0,00 sec)

Daten korrigieren:

UPDATE my_overview SET position = 3 WHERE my_overview_id = 3;

Zurücktauschen:

UPDATE my_overview t1,
my_overview t2
SET t1.position = t1.position + 1,
t2.position = t2.position - 1
WHERE t1.my_overview_id = 2
AND t1.user_id = t2.user_id
AND t1.position + 1 = t2.position;
Query OK, 3 rows affected (0,01 sec)
Rows matched: 3 Changed: 3 Warnings: 0

Selbes Problem, wieder korrigieren:

UPDATE my_overview SET position = 3 WHERE my_overview_id = 3;

Tauschen von 'Datensatz 2' und 'Datensatz 3':

UPDATE my_overview t1,
my_overview t2
SET t1.position = t1.position + 1,
t2.position = t2.position - 1
WHERE t1.my_overview_id = 2
AND t1.user_id = t2.user_id
AND t1.position + 1 = t2.position;
Query OK, 2 rows affected (0,01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

SELECT * FROM my_overview WHERE user_id = 1\G
*************************** 1. row ***************************
my_overview_id: 1
user_id: 1
position: 1
payload: Datensatz 1
*************************** 2. row ***************************
my_overview_id: 2
user_id: 1
position: 3
payload: Datensatz 2
*************************** 3. row ***************************
my_overview_id: 3
user_id: 1
position: 2
payload: Datensatz 3
3 rows in set (0,00 sec)

Passt natürlich.

Jetzt tauschen von 'Datensatz 1' und 'Datensatz 3':

UPDATE my_overview t1,
my_overview t2
SET t1.position = t1.position + 1,
t2.position = t2.position - 1
WHERE t1.my_overview_id = 1
AND t1.user_id = t2.user_id
AND t1.position + 1 = t2.position;
Query OK, 2 rows affected (0,01 sec)
^^^^^^^^^^^^^^^
Rows matched: 2 Changed: 2 Warnings: 0
^^^^^^^^^^^^^^^ ^^^^^^^^^^

SELECT * FROM my_overview WHERE user_id = 1\G
*************************** 1. row ***************************
my_overview_id: 1
user_id: 1
position: 2
payload: Datensatz 1
*************************** 2. row ***************************
my_overview_id: 2
user_id: 1
position: 3
payload: Datensatz 2
*************************** 3. row ***************************
my_overview_id: 3
user_id: 1
position: 1
payload: Datensatz 3
3 rows in set (0,00 sec)

Jetzt passiert plötzlich das Gewünschte. Hat jemand eine Erklärung für
das unterschiedliche Verhalten? Ich kann dahinter keine Logik entdecken.

gruss, heli

Re: Positionen tauschen mit einem UPDATE Befehl

Helmut Chang schrieb:

[...]

Ein weiterer Test hat jetzt ergeben:

Lege ich einen Index über (user_id, position) /scheint/ das Ganze zu
funktionieren. Aber ich wüsste nicht, wo ich etwas finden könnte, dass
mir das Funktionieren garantiert.

gruss, heli

Re: Positionen tauschen mit einem UPDATE Befehl

Hallo Helmut,

Helmut Chang wrote:

[ snip ]

> UPDATE my_overview t1,
> my_overview t2
> SET t1.position = t1.position + 1,
> t2.position = t2.position - 1
> WHERE t1.my_overview_id = 1
> AND t1.user_id = t2.user_id
> AND t1.position + 1 = t2.position;
> Query OK, 3 rows affected (0,00 sec)
~~~~~~
....

> UPDATE my_overview t1,
> my_overview t2
> SET t1.position = t1.position + 1,
> t2.position = t2.position - 1
> WHERE t1.my_overview_id = 2
> AND t1.user_id = t2.user_id
> AND t1.position + 1 = t2.position;
> Query OK, 2 rows affected (0,01 sec)
~~~~~~

> Hat jemand eine Erklärung für
> das unterschiedliche Verhalten? Ich kann dahinter keine Logik entdecken.

Das Problem ist, daß du Spalten veränderst, die in der WHERE Klausel
stehen. Damit wird die Treffermenge davon abhängig, welchen Plan der
SQL-Executor verwendet. Im Fall eines Tablescans (ohne Index) hängt
das Ergebnis dann auch noch von der Reihenfolge der Datensätze ab:

CREATE TABLE my_overview (
my_overview_id int(10) unsigned NOT NULL auto_increment,
user_id int(10) unsigned NOT NULL,
position int(10) unsigned NOT NULL,
payload varchar(255) NOT NULL,
PRIMARY KEY (my_overview_id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO my_overview (user_id, position, payload)
VALUES (1, 3, 'Datensatz 3');

INSERT INTO my_overview (user_id, position, payload)
VALUES (1, 2, 'Datensatz 2');

INSERT INTO my_overview (user_id, position, payload)
VALUES (1, 1, 'Datensatz 1');

SELECT * FROM my_overview;
+----------------+---------+----------+-------------+
| my_overview_id | user_id | position | payload |
+----------------+---------+----------+-------------+
| 1 | 1 | 3 | Datensatz 3 |
| 2 | 1 | 2 | Datensatz 2 |
| 3 | 1 | 1 | Datensatz 1 |
+----------------+---------+----------+-------------+

UPDATE my_overview t1,
my_overview t2
SET t1.position = t1.position + 1,
t2.position = t2.position - 1
WHERE t1.my_overview_id = 3
AND t1.user_id = t2.user_id
AND t1.position + 1 = t2.position;
Query OK, 2 rows affected (0,01 sec)
Rows matched: 2 Changed: 2 Warnings: 0

SELECT * FROM my_overview;
+----------------+---------+----------+-------------+
| my_overview_id | user_id | position | payload |
+----------------+---------+----------+-------------+
| 1 | 1 | 3 | Datensatz 3 |
| 2 | 1 | 1 | Datensatz 2 |
| 3 | 1 | 2 | Datensatz 1 |
+----------------+---------+----------+-------------+


Schlußfolgerung: die UPDATE-Strategie mit Self-JOIN taugt nicht.

Den Query-Plan kannst du dir von EXPLAIN anzeigen lassen, indem du
das äquivalente SELECT Statement verwendest.


XL

Re: Positionen tauschen mit einem UPDATE Befehl

Axel Schwenke schrieb:

> Das Problem ist, daß du Spalten veränderst, die in der WHERE Klausel
> stehen. Damit wird die Treffermenge davon abhängig, welchen Plan der
> SQL-Executor verwendet.

Hmm... Das hatte ich schon vermutet. Scheint auch logisch. Unlogisch
erscheint mir jedoch: Warum in manchen Fällen drei Datensätze (mit mehr
hab ich es nicht getestet)?

> SELECT * FROM my_overview;
> +----------------+---------+----------+-------------+
> | my_overview_id | user_id | position | payload |
> +----------------+---------+----------+-------------+
> | 1 | 1 | 3 | Datensatz 3 |
> | 2 | 1 | 2 | Datensatz 2 |
> | 3 | 1 | 1 | Datensatz 1 |
> +----------------+---------+----------+-------------+
>
> UPDATE my_overview t1,
> my_overview t2
> SET t1.position = t1.position + 1,
> t2.position = t2.position - 1
> WHERE t1.my_overview_id = 3
> AND t1.user_id = t2.user_id
> AND t1.position + 1 = t2.position;
> Query OK, 2 rows affected (0,01 sec)
> Rows matched: 2 Changed: 2 Warnings: 0
>
> SELECT * FROM my_overview;
> +----------------+---------+----------+-------------+
> | my_overview_id | user_id | position | payload |
> +----------------+---------+----------+-------------+
> | 1 | 1 | 3 | Datensatz 3 |
> | 2 | 1 | 1 | Datensatz 2 |
> | 3 | 1 | 2 | Datensatz 1 |
> +----------------+---------+----------+-------------+

Hier ist es doch so, dass zuerst der Datensatz 2 geändert wird, danach
Datensatz 1 und somit das gewünschte Ergebnis eintrifft. Wäre es so,
dass zuerst Datensatz 1 geändert wird, müsste dann die JOIN-Bedingung
nicht nur mehr auf Datensatz 3 zutreffen (weil zu diesem Zeitpunkt
position von Datensatz 1 = 2 und 2 + 1 = 3)? Das wäre zwar nicht das
gewünschte Ergebnis, aber zumindest könnte ich dessen Entstehung
nachvollziehen. Wieso werden aber in manchen Fällen beide "anderen"
Datensätze geändert?

> Schlußfolgerung: die UPDATE-Strategie mit Self-JOIN taugt nicht.

Ist wohl so. Mein im zweiten Posting beschriebener Versuch mit Index
über user_id und position (der ohnehin hingehört hätte) ergab auf meinem
Testsystem in allen Versuchen korrekte Ergebnisse, auf dem Echtsystem
beim Kunden wieder nicht. Hab das Ganze jetzt in zwei Updates in einer
Transaktion verpackt.

> Den Query-Plan kannst du dir von EXPLAIN anzeigen lassen, indem du
> das äquivalente SELECT Statement verwendest.

Ich bezweifle allerdings, dass mich das zu einem Ergebnis mit drei Rows
führen würde.

gruss, heli

Re: Positionen tauschen mit einem UPDATE Befehl

Helmut Chang wrote:

>> Schlußfolgerung: die UPDATE-Strategie mit Self-JOIN taugt nicht.

Deshalb gibt es im Standard SQL sowas auch nicht.
Andererseits haben es viele implementiert...

> Ist wohl so. Mein im zweiten Posting beschriebener Versuch mit Index
> über user_id und position (der ohnehin hingehört hätte) ergab auf meinem
> Testsystem in allen Versuchen korrekte Ergebnisse, auf dem Echtsystem
> beim Kunden wieder nicht.

:-)

Wenn ein DBMS schon solche Updates erlaubt, dann sollte es sie auch
ordentlich abarbeiten. Über das gewünshcte Ergebnis liesse sich ja noch
streiten, aber zumindest sollte es immer das Gleiche sein.

> Hab das Ganze jetzt in zwei Updates in einer
> Transaktion verpackt.

Normalerweise schreibt man solche Updates mit CASE:

UPDATE my_overview
SET POSITION = CASE WHEN POSITION = 2 THEN 3 ELSE 2 END
WHERE POSITION IN (2,3);

Dieter

Re: Positionen tauschen mit einem UPDATE Befehl

Dieter Noeth wrote:
> Helmut Chang wrote:
>
>>> Schlußfolgerung: die UPDATE-Strategie mit Self-JOIN taugt nicht.
>
> Deshalb gibt es im Standard SQL sowas auch nicht.
> Andererseits haben es viele implementiert...
....

> Wenn ein DBMS schon solche Updates erlaubt, dann sollte es sie auch
> ordentlich abarbeiten. Über das gewünshcte Ergebnis liesse sich ja noch
> streiten, aber zumindest sollte es immer das Gleiche sein.

Das Problem ließe sich lösen, indem man entweder das JOIN Zwischen-
ergebnis oder die geänderten Datensätze materialisiert. Dann könnte
man garantieren, daß keine Seiteneffekte auftreten. Allerdings wird
das wohl die Performance negativ beeinflussen.

Ich bin mir sicher: wenn man das als Bug reported, gibts bestenfalls
eine Änderung zu "Feature Request", mit höherer Wahrscheinlichkeit
einen Hinweis im Manual, daß diese Art Queries kein definiertes
Ergebnis liefert :-/

> > Hab das Ganze jetzt in zwei Updates in einer
>> Transaktion verpackt.
>
> Normalerweise schreibt man solche Updates mit CASE:

Die Existenz anderer Lösungen reduziert die Dringlichkeit eines Feature
Requests gleich noch einmal.


XL

Re: Positionen tauschen mit einem UPDATE Befehl

Axel Schwenke schrieb:
> Dieter Noeth wrote:
>> Helmut Chang wrote:
>>
>>>> Schlußfolgerung: die UPDATE-Strategie mit Self-JOIN taugt nicht.
>> Deshalb gibt es im Standard SQL sowas auch nicht.
>> Andererseits haben es viele implementiert...
> ...
>
>> Wenn ein DBMS schon solche Updates erlaubt, dann sollte es sie auch
>> ordentlich abarbeiten. Über das gewünshcte Ergebnis liesse sich ja noch
>> streiten, aber zumindest sollte es immer das Gleiche sein.
>
> Das Problem ließe sich lösen, indem man entweder das JOIN Zwischen-
> ergebnis oder die geänderten Datensätze materialisiert. Dann könnte
> man garantieren, daß keine Seiteneffekte auftreten. Allerdings wird
> das wohl die Performance negativ beeinflussen.
>
> Ich bin mir sicher: wenn man das als Bug reported, gibts bestenfalls
> eine Änderung zu "Feature Request", mit höherer Wahrscheinlichkeit
> einen Hinweis im Manual, daß diese Art Queries kein definiertes
> Ergebnis liefert :-/

Etwas ähnliches hab' ich schonmal gemacht, ist aber schon länger her:

http://bugs.mysql.com/bug.php?id=7590

>>> Hab das Ganze jetzt in zwei Updates in einer
>>> Transaktion verpackt.

Die hatte ich damals noch nicht benutzt. Allerdings das ganze zum Anlass
genommen, anstelle der Multi-Table-Updates alles auf Transaktionen mit
passendem Locking umzustellen. (Eine Entscheidung, die ich alles andere
als bereue)

Grüße
Kai

Re: Positionen tauschen mit einem UPDATE Befehl

Kai Ruhnau wrote:
> Axel Schwenke schrieb:
>>
>> Ich bin mir sicher: wenn man das als Bug reported, gibts bestenfalls
>> eine Änderung zu "Feature Request", mit höherer Wahrscheinlichkeit
>> einen Hinweis im Manual, daß diese Art Queries kein definiertes
>> Ergebnis liefert :-/
>
> Etwas ähnliches hab' ich schonmal gemacht, ist aber schon länger her:
>
> http://bugs.mysql.com/bug.php?id=7590

Hey, cool - das ist nichtmal ein Feature Request, sondern immerhin ein
anerkannter Bug, wenn auch "S3, not critical". Und "to be fixed later".
Es gibt also noch Hoffnung :-)


XL

Re: Positionen tauschen mit einem UPDATE Befehl

In article ,
Axel Schwenke writes:

>> Wenn ein DBMS schon solche Updates erlaubt, dann sollte es sie auch
>> ordentlich abarbeiten. Über das gewünshcte Ergebnis liesse sich ja n=
och
>> streiten, aber zumindest sollte es immer das Gleiche sein.

> Ich bin mir sicher: wenn man das als Bug reported, gibts bestenfalls
> eine Änderung zu "Feature Request", mit höherer Wahrscheinlichkeit
> einen Hinweis im Manual, daß diese Art Queries kein definiertes
> Ergebnis liefert :-/

(Ich mach hier mal den Kretschmer)

Eine richtige Datenbank sollte jede Query ohne definiertes Ergebnis
verbieten - vor allem angesichts der Tatsache, daß hier sowieso keine
Sau ins Manual guckt.

Re: Positionen tauschen mit einem UPDATE Befehl

Harald Fuchs wrote:
> Axel Schwenke writes:
>
>>> Wenn ein DBMS schon solche Updates erlaubt, dann sollte es sie auch
>>> ordentlich abarbeiten. Über das gewünshcte Ergebnis liesse sich ja noch
>>> streiten, aber zumindest sollte es immer das Gleiche sein.
>
>> Ich bin mir sicher: wenn man das als Bug reported, gibts bestenfalls
>> eine Änderung zu "Feature Request", mit höherer Wahrscheinlichkeit
>> einen Hinweis im Manual, daß diese Art Queries kein definiertes
>> Ergebnis liefert :-/
>
> (Ich mach hier mal den Kretschmer)

Laß mal lieber, das steht dir nicht ;-)

> Eine richtige Datenbank sollte jede Query ohne definiertes Ergebnis
> verbieten - vor allem angesichts der Tatsache, daß hier sowieso keine
> Sau ins Manual guckt.

Jein. Bezogen auf diesen speziellen Fall hast du natürlich recht. Das
kann und sollte MySQL besser machen. Und wie die Kommentare zu Kais
Bugreport zeigen, ist das Problem ja nun bekannt und steht auf der
Liste der zu fixenden Dinge.

Andererseits sind weder Multi-Table-UPDATE noch -DELETE im SQL Standard
definiert. Für proprietäre Features kann der Datenbankhersteller aber
im Prinzip beliebige Einschränkungen vorgeben. Z.B. hatte INSERT ...
SELECT lange Zeit die Einschränkung, daß die Zieltabelle nicht im
SELECT vorkommen durfte. Mittlerweile geht das, aber für die Zwischen-
zeit mußte man sich mit einem Workaround behelfen.


XL

Re: Positionen tauschen mit einem UPDATE Befehl

Axel Schwenke wrote:

> Andererseits sind weder Multi-Table-UPDATE noch -DELETE im SQL Standard
> definiert. Für proprietäre Features kann der Datenbankhersteller aber
> im Prinzip beliebige Einschränkungen vorgeben. Z.B. hatte INSERT ...
> SELECT lange Zeit die Einschränkung, daß die Zieltabelle nicht im
> SELECT vorkommen durfte.

INSERT/SELECT ist aber kein proprietäres Feature :-)
Und dass Ziel- und Quelltabelle die gleiche sein dürfen ist mindestens
seit SQL:92 im Standard.

Dieter

Re: Positionen tauschen mit einem UPDATE Befehl

In article ,
Axel Schwenke writes:

>> Eine richtige Datenbank sollte jede Query ohne definiertes Ergebnis
>> verbieten - vor allem angesichts der Tatsache, daß hier sowieso keine
>> Sau ins Manual guckt.

> Jein. Bezogen auf diesen speziellen Fall hast du natürlich recht. Das
> kann und sollte MySQL besser machen. Und wie die Kommentare zu Kais
> Bugreport zeigen, ist das Problem ja nun bekannt und steht auf der
> Liste der zu fixenden Dinge.

> Andererseits sind weder Multi-Table-UPDATE noch -DELETE im SQL Standard
> definiert. Für proprietäre Features kann der Datenbankhersteller aber
> im Prinzip beliebige Einschränkungen vorgeben.

Ich meinte eher, der Datenbankhersteller _sollte_ in so einem Fall
sogar Einschränkungen vorgeben (vulgo: die Query zurückweisen) und
nicht stillschweigend irgendwas mehr oder weniger Zufälliges
zurückliefern. Wie viele Newbies sind z.B. schon durch die
Group-"Extension" verwirrt worden:

* MySQL extends the use of `GROUP BY' to allow selecting fields that
are not mentioned in the `GROUP BY' clause. If you are not getting
the results you expect from your query, please read the
description of `GROUP BY' found in *Note
group-by-functions-and-modifiers::.

Das Konstrukt ist dokumentiert und manchmal durchaus brauchbar, aber
warum ist es nicht implementiert durch eine spezielle Syntax, durch
deren Anwendung der User beweist, daß er weiß, was er tut?