1

Can anyone explain why this happens in MySQL?

mysql> select * from test;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 1 | 1 |
+----+---+---+
1 row in set

mysql> update test set a = a + 1, b = a + 1;
Query OK, 1 row affected
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from test;
+----+---+---+
| id | a | b |
+----+---+---+
|  1 | 2 | 3 |
+----+---+---+
1 row in set

mysql>

Schema:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL,
  `a` int(10) unsigned NOT NULL,
  `b` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Ian
  • 24,116
  • 22
  • 58
  • 96
  • Did you mean `b = a + 1;` because as `a` has already been incremented this will equate to `b = 2 + 1` which `= 3` – RiggsFolly Aug 03 '17 at 17:30
  • I'm not 100% sure, but I'm guessing MySQL runs the command sequentially. So what @RiggsFolly said is the case. – TheDetective Aug 03 '17 at 17:30
  • My issue is the order of the updates affects the value. I was expecting the value of a to remain unchanged until after the query was completed. I don't think the mysql behavior is standard. – Ian Aug 03 '17 at 17:34
  • What is your current isolation level? SELECT @@GLOBAL.transaction_isolation, @@transaction_isolation; – Wayne Aug 03 '17 at 17:47

3 Answers3

2

To my understanding sql does left to right evaluation. so if you update a value in a and use a as the basis for b. the new value will be reflected

SQL UPDATE order of evaluation

juergen d
  • 201,996
  • 37
  • 293
  • 362
ttallierchio
  • 460
  • 7
  • 17
1

That's the expected behavior, according to the MySQL documentation:

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.

However, if you want to prevent it, and have the query use the original value, try running it inside a transaction.

Community
  • 1
  • 1
pilsetnieks
  • 10,330
  • 12
  • 48
  • 60
-1

try this:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
update test set a = a + 1, b = a + 1;

The isolation levels can be changed... https://dev.mysql.com/doc/refman/5.7/en/set-transaction.html

Wayne
  • 3,359
  • 3
  • 30
  • 50