Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.1.22, 10.1.26
-
None
-
Cent OS 6.7
Description
Hi,
recently, we encountered a order by question on product server.
example:
CREATE TABLE tmp (id INT AUTO_INCREMENT PRIMARY KEY , c1 VARCHAR(10) , c2 INT); |
|
|
INSERT INTO tmp VALUES (1, 'a', 90),(2,'a', 36), (3, 'b',103) ,(4, 'a',320),(5,'b', 691); |
|
|
SELECT t1.* , if(@t = c1 , @r := @r + 1, @r := 1) AS rownm , @t := c1 AS t |
FROM |
(SELECT * FROM tmp ORDER BY c1 ,c2 DESC ) t1 , (SELECT @r := 0 , @t := NULL) t2 |
result on produce server (MariaDB 10.1.22)
id c1 c2 rownm t
|
2 a 36 1 a
|
1 a 90 2 a
|
4 a 320 3 a
|
3 b 103 1 b
|
5 b 691 2 b
|
after upgrade to 10.1.26, it is same result.
on MySQL 5.7.17, this is we want to get result.
id c1 c2 rownm t
|
4 a 320 1 a
|
1 a 90 2 a
|
2 a 36 3 a
|
5 b 691 1 b
|
3 b 103 2 b
|
We could not confirm , it is MairaDB optimizer and MySQL's different or bug, or others cause ?
please help confirm, thanks.