Search code examples
sqlsql-updateevaluationoperator-precedence

SQL UPDATE order of evaluation

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.

Solution

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