MySQL Bugs: #44494: Multiples tables UPDATE updates each eligible row at most once

Bug #44494	Multiples tables UPDATE updates each eligible row at most once
Submitted:	27 Apr 2009 13:42	Modified:	5 May 2009 20:37
Reporter:	Alex Bolenok	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	5.1.28, 4.1, 5.0, 5.1, 6.0 bzr	OS:	Any (MS Windows, Linux)
Assigned to:		CPU Architecture:	Any
Tags:	join, multiple, UPDATE

Description:
When updating a table using a multiple tables UPDATE syntax with a JOIN, each record in a table is updated at most once, despite being returned multiple times in a table_expression.

How to repeat:
SELECT  VERSION();

VERSION()
---
5.1.28-rc-community

CREATE TABLE updater (value INT NOT NULL);

INSERT
INTO    updater
VALUES  (1);

SELECT  *
FROM    updater

value
---
1

UPDATE  updater u
JOIN    (
        SELECT  1 AS newval
        UNION ALL
        SELECT  2
        ) q
SET     u.value = u.value + newval;

SELECT  *
FROM    updater;

value
---
2

I expected 4 to be returned, since value should first be updated from 1 to 1 + 1 = 2, then from 2 to 2 + 2 = 4 (or first from 1 to 1 + 2 = 3, then from 3 to 3 + 1 = 4)

Suggested fix:
Fix the UPDATE process so that each row is updated as many times as it's returned in the table_expression, or document how UPDATE behaves when a row is returned more than once.

Thank you for the report.

Verified as described.

At least our manual does not say this is expected behavior.

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