Atomicity of UPDATEs

mjp wrote:

Under 2.1.3, the following statement:

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

appears non-atomic to me, seeing two different values for "Q" while updating a single row. I'd have expected that "P = (Q - 1)", but Firebird does not concur

ISQL Version: LI-V2.1.3.18185 Firebird 2.1
Server version:
LI-V2.1.3.18185 Firebird 2.1
LI-V2.1.3.18185 Firebird 2.1/tcp (worclip)/P11
LI-V2.1.3.18185 Firebird 2.1/tcp (worclip)/P11

SQL> CREATE TABLE tbl (p INT NOT NULL, q INT NOT NULL);
SQL> INSERT INTO tbl VALUES (1, 5);
SQL> SELECT * FROM tbl;

           P            Q
============ ============
           1            5

SQL> UPDATE tbl SET q = q + 1, p = q;
SQL> SELECT * FROM tbl;

           P            Q
============ ============
           6            6

In my testing, one SQL engine shares FB's behavior here after the same series of SQL commands

DATABASE           P  Q
================= == ==
Firebird 2.1.3     6  6
InterBase 2009     5  6
MySQL 5.0.77       6  6
Oracle XE (10g)    5  6
PostgreSQL 8.4.2   5  6
SQLite 3.3.6       5  6

Am I misunderstanding things? What behavior, if any, is mandated by the SQL standards? Is future FB behavior different?

Vlad Horsun answers:

It is fixed at v2.5. Search in its ReleaseNotes for "Logic Change in SET Clause".

Djordje Radovanovic adds:

did you try:

UPDATE tbl set p = q, q = q + 1;
SELECT * from tbl

P  Q
=====================
5  6

I am satisfied with this result.