Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-13417

UPDATE produces wrong values if an updated column is later used as an update source

    Details

      Description

      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.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated: