Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
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(EOL), 10.4(EOL), 10.5
-
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.
Attachments
Activity
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.
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.
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/
Bug still available in 10.3.20