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

    XMLWordPrintable

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

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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