MySQL Bugs: #50382: Value in multi-table UPDATE is taken from the first row found

Bug #50382	Value in multi-table UPDATE is taken from the first row found
Submitted:	15 Jan 2010 20:11	Modified:	18 Jan 2010 10:36
Reporter:	Stoyan Popov	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.1.42	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Multi-table UPDATE gives wrong results if there is more than one row satisfying join condition. Value is taken from the first row found and further search is just stopped.

How to repeat:
mysql> CREATE TABLE t1(id INT UNSIGNED, value INT UNSIGNED);

mysql> CREATE TABLE t2(id INT UNSIGNED, value INT UNSIGNED);

mysql> INSERT INTO t1 VALUES(1,0);

mysql> INSERT INTO t2 VALUES(1,1);

mysql> INSERT INTO t2 VALUES(1,2);

mysql> SELECT * FROM t1;
+------+-------+
| id   | value |
+------+-------+
|    1 |     0 |
+------+-------+

mysql> SELECT * FROM t2;
+------+-------+
| id   | value |
+------+-------+
|    1 |     1 |
|    1 |     2 |
+------+-------+

mysql> UPDATE t1 INNER JOIN t2 USING(id)
    -> SET t1.value = t1.value + t2.value;

Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t1;
+------+-------+
| id   | value |
+------+-------+
|    1 |     1 |
+------+-------+
1 row in set 

Two rows in t2 match join condition so I expect t1.value equals 3(1+2).

Suggested fix:
Make multi-table UPDATE using all rows matching join condition

Thank you for the problem report. Looks like a duplicate bug #27299. Please, check.

It looks like #27299 but it isn't exactly the same. The multi-table UPDATE in #27299 is

UPDATE test_1 t,
test_2 tmp
set t.col_test = tmp.col_test_new
where t.col_pk = tmp.col_pk_join;

It assigns new value to t.col_test and that value doesn't depend on the previous value of t.col_test. In this case it is pointless to execute several changes because there is no way to control their order(ORDER BY cannot be used in multi-table UPDATE) and in my view MySQL behaviour is logical.

On the other side my UPDATE depends on the previous value of the column and there is a sense in using all rows matching the join condition. My suggestion: check if the updated column is part of the expression on the right side. If yes then use all rows matching the join condition.

Thank you for the feedback.

This is not a bug. See comment "[5 May 2009 22:37] Omer BarNir" in bug #44494 for explanation why:

"The SQL standard allows each RDBMS to determine how to behave in this situation. The
consensus of the major RDBMS projects (MS SQL and Oracle) concurr with our implementation that the row is updated at most only once per UPDATE statement"

I saw the comment. I didn't know that and I spent more than an hour struggling with a complex transaction including similar UPDATE statements. MySQL manual says:

"For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions"

You should document how UPDATE behaves when a row is returned more than once.