[MDEV-20081] INSERT INTO SELECT ignores ORDER Created: 2019-07-17  Updated: 2020-11-03  Resolved: 2020-10-02

Status: Closed
Project: MariaDB Server
Component/s: Data Manipulation - Insert
Affects Version/s: 10.3.15, 10.3.16, 10.3.17, 10.3.18, 10.3.20, 10.4.10, 10.4.11, 10.5.0, 10.3, 10.4, 10.5
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Alex Assignee: Daniel Black
Resolution: Not a Bug Votes: 4
Labels: insert, insert_into, order, rank, value
Environment:

Cent OS 7


 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.

 Comments   
Comment by Alex [ 2019-11-11 ]

Bug still available in 10.3.20

Comment by Alex [ 2019-11-11 ]

Same error in 10.4.10.

Comment by Alex [ 2020-01-16 ]

Same Error on 10.5 and 10.4.11.

Comment by Daniel Black [ 2020-10-02 ]

Well the ORDER BY clause is not ignored

MariaDB [test]> EXPLAIN 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);
 
+------+-------------+-----------+------+---------------+------+---------+------+------+---------------------------------+
 
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
 
+------+-------------+-----------+------+---------------+------+---------+------+------+---------------------------------+
 
|    1 | SIMPLE      | counttest | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using temporary; Using filesort |
 
+------+-------------+-----------+------+---------------+------+---------+------+------+---------------------------------+
 
1 row in set (0.002 sec)

So it does the join first and puts that into a temp table and then sorts the temp table. So i think the results are as expected. With just the SELECT (no INSERT) , it used FILESORT on the table directly, no temp table, hence the results are different.

MariaDB [test]> EXPLAIN SELECT id, @rank_count := @rank_count + 1 AS rank FROM counttest ORDER BY counter DESC;
 
+------+-------------+-----------+------+---------------+------+---------+------+------+----------------+
 
| id   | select_type | table     | type | possible_keys | key  | key_len | ref  | rows | Extra          |
 
+------+-------------+-----------+------+---------------+------+---------+------+------+----------------+
 
|    1 | SIMPLE      | counttest | ALL  | NULL          | NULL | NULL    | NULL | 3    | Using filesort |
 
+------+-------------+-----------+------+---------------+------+---------+------+------+----------------+
 
1 row in set (0.002 sec)

User variables are numbered as the rows are outputed.

Comment by Alex [ 2020-10-05 ]

If sorting a temp table and filesort produce different results for the same ORDER-statement we are having an other problem.

Why has it worked up to 10.3.14?

Please reopen this bug.

Comment by Sergei Golubchik [ 2020-11-03 ]

See https://mariadb.com/kb/en/user-defined-variables/

It is unsafe to read a user-defined variable and set its value in the same statement (unless the command is SET), because the order of these actions is undefined.

The result of your statement is undefined. So neither outcome is wrong, anything can happen.

If you want to count rows use the ROW_NUMBER window function: https://mariadb.com/kb/en/row_number/

Generated at Thu Feb 08 08:56:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.