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

Bug or missing documentation in UPDATE-SET behaviour

    XMLWordPrintable

Details

    Description

      Documentation states:

      "Assignments are evaluated in left-to-right order, unless the SIMULTANEOUS_ASSIGNMENT sql_mode (available from MariaDB 10.3.5) is set, in which case the UPDATE statement evaluates all assignments simultaneously."

      But it seems like the "left-to-right" evaluation is not true for multiple-table-updates. So either this is a bug or the documentation lack this information.

      Example:

      drop table if exists tab1;
      drop table if exists tab2;
      create table tab1 (c1 int, c2 int);
      create table tab2 (c3 int);
      insert tab1 values (1,2);
      insert tab2 values (0);
       
      -- first we make a multiple-table update:
       
      update tab1, tab2 set c1 = c2, c2 = c1;
      select * from tab1;
      /* 
      output:
      +------+------+
      | c1   | c2   |
      +------+------+
      |    2 |    1 |
      +------+------+
       
      If evalutation was left-to-right, then the result should be "2" in both columns, but it seems like this statement "evaluates all assignments simultaneously".
      */
       
      -- and now a single-table update:
       
      update tab1       set c1 = c2, c2 = c1;
      select * from tab1;
      /* output:
      +------+------+
      | c1   | c2   |
      +------+------+
      |    1 |    1 |
      +------+------+
       
      Here evalutation is "left-to-right".
      */
      

      Attachments

        Activity

          People

            greenman Ian Gilfillan
            tgj1970 Thomas G. Jensen
            Votes:
            0 Vote for this issue
            Watchers:
            3 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.