Details

      Description

      When using ORDER in a INSERT INTO SELECT the ORDER is ignored since 10.3.15, it works in 10.3.14.

      Example:

      DROP TABLE IF EXISTS counttest;
      CREATE TEMPORARY TABLE counttest (
       id INT AUTO_INCREMENT,
       counter INT,
       rank INT,
       PRIMARY KEY (id)
      );
      INSERT INTO counttest (id,counter) VALUES (5,10),(7,20),(6,5);
      SELECT * FROM counttest;
      

      Initial data:

      +----+---------+------+
      | id | counter | rank |
      +----+---------+------+
      |  5 |      10 | NULL |
      |  6 |       5 | NULL |
      |  7 |      20 | NULL |
      +----+---------+------+
      

      When calculating the rank with updating the rank column at the same time with:

      SET @rank_count := 0;
      INSERT INTO counttest (id, rank)
       ( SELECT id, @rank_count := @rank_count + 1 AS rank
          FROM counttest
          ORDER BY counter DESC
       )
      ON DUPLICATE KEY UPDATE rank = VALUE(rank);
      SELECT * FROM counttest ORDER by rank ASC;
      

      Result:

      +----+---------+------+
      | id | counter | rank |
      +----+---------+------+
      |  5 |      10 |    1 |
      |  6 |       5 |    2 |
      |  7 |      20 |    3 |
      +----+---------+------+
      

      But when only using the inner select the result is correct:

      SET @rank_count := 0;
      SELECT id, counter, @rank_count := @rank_count + 1 AS rank
       FROM counttest
       ORDER BY counter DESC;
      

      Result:

      +----+---------+------+
      | id | counter | rank |
      +----+---------+------+
      |  7 |      20 |    1 |
      |  5 |      10 |    2 |
      |  6 |       5 |    3 |
      +----+---------+------+
      

      It looks like the ORDER is completely ignored in the INSERT INTO SELECT.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              alex2 Alex
            • Votes:
              1 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

              • Created:
                Updated: