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

Multi-table UPDATE does not consider ORDER BY clause

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3, 10.4, 10.5, 10.3.27
    • Fix Version/s: 10.3, 10.4, 10.5
    • Component/s: Server
    • Labels:
      None
    • Environment:
      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

              Assignee:
              serg Sergei Golubchik
              Reporter:
              schneoka Oskar Schneider
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration