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, 10.4, 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.