Bug #63747 UPDATE test SET a = a+b, b = a+b

• Submitted: 14 Dec 2011 14:43
• Modified: 14 Dec 2011 14:50
• Reporter: Alexander Urban
• Status: Not a Bug
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 5.1.41
• OS: Linux (mysql Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (x86_64) using readline 6.1)
• CPU Architecture: Any

[14 Dec 2011 14:43] Alexander Urban

Description:
I don't know whether this is bug in mySQL or in pgSQL, but one of them isn't doing the query according to standard.

How to repeat:
-> Given Table "test" with two integer columns "a" and "b", 
-> filled with single row: a=1, b=1
-> after applying UPDATE test SET a = a+b, b = a+b
--> result in MySQL is: a = 2, b = 3
--> result in pgSQL is: a = 2, b = 2

[14 Dec 2011 14:50] Valeriy Kravchuk

Yes, MySQL behavior is non-standard, but it is clearly documented in the manual, http://dev.mysql.com/doc/refman/5.1/en/update.html:

"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."

So formally this is not a bug.