Bug #52861 Non-atomic UPDATE on single row of single table (order of evaluation)
Submitted: 15 Apr 2010 15:31 Modified: 19 May 2010 15:11
Reporter: Mike Pomraning Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:5.1.42-community OS:Linux
Assigned to: Paul Dubois CPU Architecture:Any
Tags: UPDATE

[15 Apr 2010 15:31] Mike Pomraning
Description:
Given this simple statement:

    UPDATE t SET q = q + 1, p = q;

MySQL 5.1.42 sets "p" and "q" to the same value in a single row.  (It evaluates "q" twice for a single row, left to right, first incrementing "q" and second assigning its newly incremented value to "p".)

My expectation was that "q" would be one greater than "p", since I'd expect "q" as an rvalue to always evaluate to its value at the beginning of the UPDATE operation.

Oracle, PostgreSQL, InterBase, Firebird >= 2.5, and SQLite 3 behave as I expect.  I their behavior is in line with relevant SQL standards, given a Release Notes entry for the Firebird 2.5 series.

How to repeat:

mysql> CREATE TABLE t (p INT NOT NULL, q INT NOT NULL);
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO t (p, q) VALUES (1, 5);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+---+---+
| p | q |
+---+---+
| 1 | 5 |
+---+---+
1 row in set (0.00 sec)

mysql> UPDATE t SET q = q + 1, p = q;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT * FROM t;
+---+---+
| p | q |
+---+---+
| 6 | 6 |
+---+---+
1 row in set (0.01 sec)
[15 Apr 2010 15:46] Mike Pomraning
When I read this in the manual:

  "Single-table UPDATE assignments are generally evaluated from left to right."
  [MySQL manual, "UPDATE Syntax"]

I think of @user_variables, not of table attribute values.  If this is Working As Designed (tm), I'd respectfully ask that the above section and the "MySQL Differences from Standard" sections be updated to clarify explicitly this behavior.
[15 Apr 2010 15:50] Valeriy Kravchuk
The behavior you described is easily repeatable, but I think our manual (http://dev.mysql.com/doc/refman/5.1/en/update.html) explains it with the following two sentences:

"If you access a column from the table to be updated in an expression, UPDATE uses the current value of the column.
...
Single-table UPDATE assignments are generally evaluated from left to right."
[15 Apr 2010 16:00] Mike Pomraning
Thanks, Valeriy.  May I suggest that the standards compliance section note this behavior and perhaps include an example?
[15 Apr 2010 16:04] Valeriy Kravchuk
I will have to check the copy of standard and Oracle behavior first. I do not have any of them at hand. I'll add further comments tomorrow.
[15 Apr 2010 18:45] Susanne Ebrecht
Many thanks for pointing this out.

This is a well known MySQL behaviour.

But you are totally right it is different to SQL Standard and we should document it more clear.
[15 Apr 2010 19:28] Davi Arnaut
WL#927
[19 May 2010 15:11] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Added this example to the UPDATE page:

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;

Also created a subsection under the "differences from Standard SQL"
section with similar information.
[14 Apr 2011 21:27] Eduardo PĂ©rez Ureta
Do you have any plans to conform to the SQL standard? (at least adding an option)
I see it more useful and sensible as the SQL standard specifies.
[25 Mar 2017 3:54] Robert Montrose
Ok, so the documentation has been updated, but has anyone actually considered offering a method to do a fully-atomic update? It would make certain actions like column swaps much easier.

Requesting this bug be re-opened.