Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Not a Bug
-
10.0.9, 10.0.15, 10.1.2
-
ubuntu 12.04 x64, ubuntu 10.04 x64
Description
When SELECT using ORDER BY DESC and LIMIT if the sorting column has the same value would produce unexpected results on MariaDB 10.0 and 10.1 series;
Reproducible on MariaDB version: 10.0.15, 10.1.2.
Was NOT able to reproduce on MariaDB
versions: 5.5.33a.
I just tested on the above versions.
How to reproduce:
1. on MariaDB version: 10.1.2, 10.0.15, 10.0.9(unexpected results)
-- create table
|
CREATE TABLE `zzz` (
|
`a` int(11) DEFAULT NULL,
|
`b` int(11) DEFAULT NULL
|
) ENGINE=InnoDB
|
|
|
-- insert some data
|
insert into zzz(a,b) values
|
(101679,1395219593),
|
(103040,1395219593),
|
(12375542,1395219593),
|
(29263588,1395219593),
|
(30408843,1395219593),
|
(50000010,1395219593),
|
(55555544,1395219593),
|
(10000,1395219593);
|
(75188381,1395219593),
|
(85651228,1395219593);
|
|
|
-- query and results set
|
> select * from zzz order by b desc ;
|
+----------+------------+
|
| a | b |
|
+----------+------------+
|
| 85651228 | 1395219593 |
|
| 75188381 | 1395219593 |
|
| 55555544 | 1395219593 |
|
| 50000010 | 1395219593 |
|
| 30408843 | 1395219593 |
|
| 29263588 | 1395219593 |
|
| 12375542 | 1395219593 |
|
| 103040 | 1395219593 |
|
| 101679 | 1395219593 |
|
| 10000 | 1395219593 |
|
+----------+------------+
|
|
|
> select * from zzz order by b desc limit 0,6;
|
+----------+------------+
|
| a | b |
|
+----------+------------+
|
| 10000 | 1395219593 |
|
| 101679 | 1395219593 |
|
| 103040 | 1395219593 |
|
| 12375542 | 1395219593 |
|
| 29263588 | 1395219593 |
|
| 30408843 | 1395219593 |
|
+----------+------------+
|
|
|
> select * from zzz order by b desc limit 6,6;
|
+----------+------------+
|
| a | b |
|
+----------+------------+
|
| 12375542 | 1395219593 |
|
| 103040 | 1395219593 |
|
| 101679 | 1395219593 |
|
| 10000 | 1395219593 |
|
+----------+------------+
|
|
|
Here the last two query produce some duplicate data.
|
2. do the same on MariaDB 5.5.33a( expected results)
-- query and results set
|
> select * from zzz order by b desc ;
|
+----------+------------+
|
| a | b |
|
+----------+------------+
|
| 85651228 | 1395219593 |
|
| 75188381 | 1395219593 |
|
| 55555544 | 1395219593 |
|
| 50000010 | 1395219593 |
|
| 30408843 | 1395219593 |
|
| 29263588 | 1395219593 |
|
| 12375542 | 1395219593 |
|
| 103040 | 1395219593 |
|
| 101679 | 1395219593 |
|
| 10000 | 1395219593 |
|
+----------+------------+
|
|
|
> select * from zzz order by b desc limit 0,6;
|
+----------+------------+
|
| a | b |
|
+----------+------------+
|
| 85651228 | 1395219593 |
|
| 75188381 | 1395219593 |
|
| 55555544 | 1395219593 |
|
| 50000010 | 1395219593 |
|
| 30408843 | 1395219593 |
|
| 29263588 | 1395219593 |
|
+----------+------------+
|
|
|
> select * from zzz order by b desc limit 6,6;
|
+----------+------------+
|
| a | b |
|
+----------+------------+
|
| 12375542 | 1395219593 |
|
| 103040 | 1395219593 |
|
| 101679 | 1395219593 |
|
| 10000 | 1395219593 |
|
+----------+------------+
|
result is as expected.
|