[MDEV-13417] UPDATE produces wrong values if an updated column is later used as an update source - Jira

XML

Word

Printable

This problem was originally reported by Jerome B. on the maria-developers list on 2017-07-21:
This script:

drop table t1;

create table t1 (c1 integer, c2 integer, c3 integer);

insert into t1(c1,c2,c3) values (1,1,1);

update t1

   set c1 = c1+1,

       c2 = c1+1,

       c3 = c2+1;

select * from t1;

produces this result:

+------+------+------+

| c1   | c2   | c3   |

+------+------+------+

|    2 |    3 |    4 |

+------+------+------+

Other databases return 2 for all columns:

PostgreSQL:

 c1 | c2 | c3

----+----+----

  2 |  2 |  2

Oracle:

	C1	   C2	      C3

---------- ---------- ----------

	 2	    2	       2

SQLite3:

2|2|2

MariaDB's behavior contradicts the SQL standard, which says in the section <update statement: searched>:

10) The <update source> of each <set clause> contained in SCL is effectively evaluated for each row of T before any row of T is updated.

relates to

MDEV-13418 Compatibility: The order of evaluation of SELECT..INTO assignments

MDEV-15122 Simultaneous assignment for LOAD..SET

MDEV-15123 Make multiple user variable assignment work similar in standalone query and in SP

MDEV-15124 Simultaneous assignment for INSERT ... SET

MDEV-19304 Segfault in ALTER TABLE after UPDATE for SIMULTANEOUS_ASSIGNMENT

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.