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

Multi-table UPDATE does not consider ORDER BY clause

    XMLWordPrintable

Details

    • Bug
    • Status: Stalled (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3.27, 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11, 11.0, 11.1, 11.2
    • 10.4, 10.5, 10.6, 10.11
    • Server
    • None
    • Debian

    Description

      In this script, the ORDER BY AP.prio is not considered:

      CREATE TABLE AP (
       prid int not null,
       prio int not null
      );
       
      CREATE TABLE APD (
       prid int not null,
       info varchar(64)
      );
       
      INSERT INTO AP VALUES (1,3), (2,3), (3,2), (4,2), (5,1), (6,1);
      INSERT INTO APD (prid) VALUES (1), (2), (3), (4), (5), (6);
       
      SELECT * FROM AP;
      SELECT * FROM APD;
       
      # The SELECT returns the correct values for prid (prid 3,5,6) 
      SELECT APD.prid, AP.prio FROM AP INNER JOIN APD ON AP.prid = APD.prid
       ORDER BY AP.prio, AP.prid
       LIMIT 3;
       
      # The UPDATE with identical ORDER updates the columns with prid 1,2,3
      UPDATE AP INNER JOIN APD ON AP.prid = APD.prid
       SET info = 'UPDATED'
       ORDER BY AP.prio, AP.prid
       LIMIT 3;
       
      SELECT * FROM APD;
      

      The script returns this output:

      +------+------+
      | prid | prio |
      +------+------+
      |    1 |    3 |
      |    2 |    3 |
      |    3 |    2 |
      |    4 |    2 |
      |    5 |    1 |
      |    6 |    1 |
      +------+------+
      6 rows in set (0.001 sec)
       
      +------+------+
      | prid | info |
      +------+------+
      |    1 | NULL |
      |    2 | NULL |
      |    3 | NULL |
      |    4 | NULL |
      |    5 | NULL |
      |    6 | NULL |
      +------+------+
      6 rows in set (0.000 sec)
       
      +------+------+
      | prid | prio |
      +------+------+
      |    5 |    1 |
      |    6 |    1 |
      |    3 |    2 |
      +------+------+
      3 rows in set (0.000 sec)
       
      Query OK, 3 rows affected (0.001 sec)
      Rows matched: 3  Changed: 3  Warnings: 0
       
      +------+---------+
      | prid | info    |
      +------+---------+
      |    1 | UPDATED |
      |    2 | UPDATED |
      |    3 | UPDATED |
      |    4 | NULL    |
      |    5 | NULL    |
      |    6 | NULL    |
      +------+---------+
      
      

      Attachments

        Issue Links

          Activity

            People

              Johnston Rex Johnston
              schneoka Oskar Schneider
              Votes:
              2 Vote for this issue
              Watchers:
              6 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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