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

          relates to

          Task - A task that needs to be done. MDEV-13418 Compatibility: The order of evaluation of SELECT..INTO assignments

          • Major - Major loss of function.
          • Stalled

          Task - A task that needs to be done. MDEV-15122 Simultaneous assignment for LOAD..SET

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-15123 Make multiple user variable assignment work similar in standalone query and in SP

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-15124 Simultaneous assignment for INSERT ... SET

          • Major - Major loss of function.
          • Open

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19304 Segfault in ALTER TABLE after UPDATE for SIMULTANEOUS_ASSIGNMENT

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Descending order - Click to sort in ascending order
            bar Alexander Barkov added a comment - - edited

            I also do not like to do prior evaluation of <update source> only in ORACLE mode.

            Doing prior evaluation only in TRADITIONAL or ANSI mode also does not look good enough.
            ORACLE does not seem to include neither TRADITIONAL nor ANSI.
            So Oracle users would have to do something like this:

            SET sql_mode='ORACLE,TRADITIONAL';

            which looks not so convenient.

            I prefer to have a new sql_mode flag, and turn it on by default, to have all users get used with the standard behavior.

            However, STANDARD_UPDATE_BEHAVIOR sounds too optimistic, because there might be some other aspects of UPDATE (not related to the order of evaluation of value sources) that expose non-standard behavior

            Why not have something like this:

            SET sql_mode=NO_UPDATE_SOURCE_PRIOR_EVALUATION;

            to activate the legacy non-standard behavior?

            bar Alexander Barkov added a comment - - edited I also do not like to do prior evaluation of <update source> only in ORACLE mode. Doing prior evaluation only in TRADITIONAL or ANSI mode also does not look good enough. ORACLE does not seem to include neither TRADITIONAL nor ANSI . So Oracle users would have to do something like this: SET sql_mode= 'ORACLE,TRADITIONAL' ; which looks not so convenient. I prefer to have a new sql_mode flag, and turn it on by default, to have all users get used with the standard behavior. However, STANDARD_UPDATE_BEHAVIOR sounds too optimistic, because there might be some other aspects of UPDATE (not related to the order of evaluation of value sources) that expose non-standard behavior Why not have something like this: SET sql_mode=NO_UPDATE_SOURCE_PRIOR_EVALUATION; to activate the legacy non-standard behavior?
            serg Sergei Golubchik added a comment -

            Yes, I think we'll have to have the standard behavior eventually. "made somehow configurable" — that's exactly what I implied in "think of backward compatibility implications". Options:

            • do it only in ORACLE mode. I don't like it, because it's standard compatibility, not oracle compatibility issue
            • do it only in TRADITIONAL mode
            • do it only in ANSI mode
            • have a new STANDARD_UPDATE_BEHAVIOR mode or @@standard_update_behavior variable
            • do it always, by default, and disable with @@old=OLD_UPDATE_BEHAVIOR.
            serg Sergei Golubchik added a comment - Yes, I think we'll have to have the standard behavior eventually. "made somehow configurable" — that's exactly what I implied in "think of backward compatibility implications". Options: do it only in ORACLE mode. I don't like it, because it's standard compatibility, not oracle compatibility issue do it only in TRADITIONAL mode do it only in ANSI mode have a new STANDARD_UPDATE_BEHAVIOR mode or @@standard_update_behavior variable do it always, by default, and disable with @@old=OLD_UPDATE_BEHAVIOR .
            elenst Elena Stepanova added a comment -

            I'd rather it was closed as "Won't fix" (or, if it's required for the compatibility project, made somehow configurable, with the default behavior being the old one). I think fixing it will cause more troubles than gains.

            elenst Elena Stepanova added a comment - I'd rather it was closed as "Won't fix" (or, if it's required for the compatibility project, made somehow configurable, with the default behavior being the old one). I think fixing it will cause more troubles than gains.
            serg Sergei Golubchik added a comment -

            This is and always was the documented behavior. When fixing it, think of backward compatibility implications.

            serg Sergei Golubchik added a comment - This is and always was the documented behavior. When fixing it, think of backward compatibility implications.

            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.