|
Performing an ordered multi-table update uses values from unrelated rows for the assignment.
Setup:
MariaDB [test]> CREATE TABLE x (id INT PRIMARY KEY AUTO_INCREMENT, filter INT, INDEX filter(filter));
|
Query OK, 0 rows affected (0.022 sec)
|
|
MariaDB [test]> CREATE TABLE y (id INT PRIMARY KEY AUTO_INCREMENT, x_id INT REFERENCES x(id), val FLOAT);
|
Query OK, 0 rows affected (0.028 sec)
|
|
MariaDB [test]> INSERT INTO x VALUES (1,1), (2,2), (3,3);
|
Query OK, 3 rows affected (0.009 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> INSERT INTO y VALUES (1,1,123), (2,2,234), (3,3,345);
|
Query OK, 3 rows affected (0.001 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
Working version, without `ORDER BY`:
MariaDB [test]> BEGIN;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> SELECT * FROM y;
|
+----+------+------+
|
| id | x_id | val |
|
+----+------+------+
|
| 1 | 1 | 123 |
|
| 2 | 2 | 234 |
|
| 3 | 3 | 345 |
|
+----+------+------+
|
3 rows in set (0.001 sec)
|
|
MariaDB [test]> EXPLAIN UPDATE y INNER JOIN x ON x.id = y.x_id SET y.val = y.val / 100 WHERE x.filter = 2;
|
+------+-------------+-------+------+----------------+--------+---------+-------+------+-------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+----------------+--------+---------+-------+------+-------------+
|
| 1 | SIMPLE | x | ref | PRIMARY,filter | filter | 5 | const | 1 | Using index |
|
| 1 | SIMPLE | y | ALL | x_id | NULL | NULL | NULL | 3 | Using where |
|
+------+-------------+-------+------+----------------+--------+---------+-------+------+-------------+
|
2 rows in set (0.001 sec)
|
|
MariaDB [test]> UPDATE y INNER JOIN x ON x.id = y.x_id SET y.val = y.val / 100 WHERE x.filter = 2;
|
Query OK, 1 row affected (0.001 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0
|
|
MariaDB [test]> SELECT * FROM y;
|
+----+------+------+
|
| id | x_id | val |
|
+----+------+------+
|
| 1 | 1 | 123 |
|
| 2 | 2 | 2.34 |
|
| 3 | 3 | 345 |
|
+----+------+------+
|
3 rows in set (0.001 sec)
|
|
MariaDB [test]> ROLLBACK;
|
Query OK, 0 rows affected (0.001 sec)
|
Broken version, with `ORDER BY`:
MariaDB [test]> BEGIN;
|
Query OK, 0 rows affected (0.000 sec)
|
|
MariaDB [test]> SELECT * FROM y;
|
+----+------+------+
|
| id | x_id | val |
|
+----+------+------+
|
| 1 | 1 | 123 |
|
| 2 | 2 | 234 |
|
| 3 | 3 | 345 |
|
+----+------+------+
|
3 rows in set (0.001 sec)
|
|
MariaDB [test]> EXPLAIN UPDATE y INNER JOIN x ON x.id = y.x_id SET y.val = y.val / 100 WHERE x.filter = 2 ORDER BY y.val;
|
+------+-------------+-------+------+----------------+--------+---------+-------+------+----------------------------------------------+
|
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
|
+------+-------------+-------+------+----------------+--------+---------+-------+------+----------------------------------------------+
|
| 1 | SIMPLE | x | ref | PRIMARY,filter | filter | 5 | const | 1 | Using index; Using temporary; Using filesort |
|
| 1 | SIMPLE | y | ALL | x_id | NULL | NULL | NULL | 3 | Using where |
|
+------+-------------+-------+------+----------------+--------+---------+-------+------+----------------------------------------------+
|
2 rows in set (0.001 sec)
|
|
MariaDB [test]> UPDATE y INNER JOIN x ON x.id = y.x_id SET y.val = y.val / 100 WHERE x.filter = 2 ORDER BY y.val;
|
Query OK, 1 row affected (0.001 sec)
|
Rows matched: 1 Changed: 1 Warnings: 0
|
|
MariaDB [test]> SELECT * FROM y;
|
+----+------+------+
|
| id | x_id | val |
|
+----+------+------+
|
| 1 | 1 | 123 |
|
| 2 | 2 | 3.45 |
|
| 3 | 3 | 345 |
|
+----+------+------+
|
3 rows in set (0.001 sec)
|
|
MariaDB [test]> ROLLBACK;
|
Query OK, 0 rows affected (0.000 sec)
|
Notice how the update including the `ORDER BY` clause updates the `val` for the row with id 2 using the `val` from the row with id 3, which, to my understanding, should not be included in the query at all given the filter conditions in the `WHERE` and `ON` clauses.
|