Bug #76393 Update query stores one value before evaluation equation for second
Submitted: 19 Mar 2015 17:41 Modified: 19 Mar 2015 19:03
Reporter: Ben Kaap Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6.23 OS:Any
Assigned to: CPU Architecture:Any
Tags: UPDATE SET IF CASE

[19 Mar 2015 17:41] Ben Kaap 
Description:
When updating multiple columns the first-listed column value seems to be stored before evaluating the equation to determine the new value for the second-listed column.  So if you use the first column in the equation for the second, you get incorrect results.
Reversing the order of the columns or removing the first column reference from the equation provides the correct results.
I used IF(), but results are the same if using CASE.
I first noticed the issue in version 5.5.11 (linux), but also replicated it on 5.6.19 (windows) and 5.6.23 (windows).

How to repeat:
CREATE TABLE foo (
  `bar` int(10) unsigned NOT NULL,
  `baz` int(10) unsigned NOT NULL
);

INSERT INTO foo VALUES (6, 7);

UPDATE foo SET bar = IF(bar < 6, bar + 2, bar - 4), baz = IF(bar < 6, baz + 2, baz - 4);

--results should be 2,3 but the row ends up as 2,9.  Swapping the order of the updated columns provides the correct values as baz is stored but not referenced in the equation for the new bar value:

--UPDATE foo SET baz = IF(bar < 6, baz + 2, baz - 4), bar = IF(bar < 6, bar + 2, bar - 4);
[19 Mar 2015 19:03] MySQL Verification Team 
I have tested this with latest 5.6 and 5.7 and behavior is the same. I have also tested it with MyISAM and InnoDB and it is still the same.

Fully verified.
[20 Mar 2015 11:29] Roy Lyseng 
This is actually the documented behavior. Quoting from the manual:

If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column. For example, the following statement sets col1 to one more than its current value:

  UPDATE t1 SET col1 = col1 + 1;

The second assignment in the following statement sets col2 to the current (updated) col1 value, not the original col1 value. The result is that col1 and col2 have the same value. This behavior differs from standard SQL.

  UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Single-table UPDATE assignments are generally evaluated from left to right. For multiple-table updates, there is no guarantee that assignments are carried out in any particular order.

It is quite unfortunate that we do not support standard SQL here, so this should be a valid feature request.