SQL UPDATE order of evaluation

Question

What is the order of evaluation in the following query:

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

That is, will "tbl"."p" be set to q or q + 1? Is order of evaluation here governed by SQL standard?

Thanks.

UPDATE

After considering Migs' answer, I ran some tests on all DBs I could find. While I don't know what the standard says, implementations vary.

Given

CREATE TABLE tbl (p INT NOT NULL, q INT NOT NULL);
INSERT INTO tbl VALUES (1, 5);   -- p := 1, q := 5
UPDATE tbl SET q = q + 1, p = q;

I found the values of "p" and "q" were:

database           p   q
-----------------+---+---
Firebird 2.1.3   | 6 | 6  -- But see "Update 2" below
InterBase 2009   | 5 | 6
MySQL 5.0.77     | 6 | 6  -- See "Update 3" below
Oracle XE (10g)  | 5 | 6
PostgreSQL 8.4.2 | 5 | 6
SQLite 3.3.6     | 5 | 6
SQL Server 2016  | 5 | 6

UPDATE 2

Firebird 2.5 changes its behavior to match the majority of other SQL engines I tested, leaving MySQL alone. The relevant Release Notes entry, "Logic Change in SET Clause", strongly suggests that the majority behavior is correct per SQL specifications.

I've bugged MySQL to comment on this behavior (bug no. 52861), as they seem to be the outlier.

UPDATE 3

The aforementioned bug is today (2010-05-19) closed, and the documentation set to be updated to make this behavior explicit both in the UPDATE description and in the Differences from Standard SQL section.

Bravo, MySQL.

Bilyeu

confirming MySQL results for version 5.1.37 – Moderate

Answer 1

MySQL does "left to right" evaluation and does "see" the new values. (Tested on 5.0.45-community-nt-log MySQL Community Edition)

Furthermore, from the MySQL manual: "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."

Now, "generally" is quite vague and "no guarantee" is very bad given that the order of evaluation is important.

So, in order to answer the question: IS the behaviour specified by "the SQL standard" or is it just a convention?

---

UPDATE: Got hold of the SQL92 specs which state at "13.10 update statement: searched" item "6) The (value expression)s are effectively evaluated for each row of T before updating any row of T."

IMHO not absolutely unambiguous, but enough to consider that the STANDARD is NOT to "see" the results of your own update. Considering your example, the way Oracle, PostgreSQL and Interbase do it.

Percutaneous

+1 While my question wasn't MySQL-specific, this is fascinating (and undesirable, IMHO) single table behavior. – Bilyeu

I agree. The idea was to show that not all dbs act as the answer, which makes you wonder: is it or is it not part of the SQL standard? I searched the web over 30 minutes for the SQL specs with no results other than paid books, which is very strange, given the widespread use of SQL, – Percutaneous

Just checked the MySQL with multitable UPDATE tbl t1 JOIN tbl t2 ON t1.p = t2.p SET t1.q = t1.q + 1, t1.p = t1.q; and it the result was 5, 6 (inconsistent compared to single table). – Moderate

Answer 2

The UPDATE does not see the results of its work.

p will be set to q as of before update.

The following code will just swap the columns:

DECLARE @test TABLE (p INT, q INT)

INSERT
INTO    @test
VALUES  (2, 3)

SELECT  *
FROM    @test

p    q
---  ---
  2    3

UPDATE  @test
SET     p = q,
        q = p

SELECT  *
FROM    @test

p    q
---  ---
  3    2

Leifleifer

Your answer is incorrect for one of the most popular database engines in the world, and you didn't specify which engines it is correct for. – Marroquin

@Marroquin the question is about SQL standard and the answer is correct. MySQL does not conform with the standard in this part. – Superclass

I just got burned because MySQL does see the change and not the initial value. Lesson learned. – Discontinuous

Answer 3

The write to the table has to occur after transaction that was well under way when the read was completed.

Lecompte