MySQL Bugs: #52861: Non-atomic UPDATE on single row of single table (order of evaluation)

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

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)

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.

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

Thanks, Valeriy.  May I suggest that the standards compliance section note this behavior and perhaps include an example?

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.

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.

WL#927

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.

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.

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.