Bug #72917 Incorrect result with order by & limit in MySQL 5.6.17

Submitted: 9 Jun 2014 9:13 Modified: 10 Jun 2014 5:53
Reporter: Nilnandan Joshi Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:5.6.15, 5.6.17, 5.6.20, 5.7.5 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Tags: 5.6.15, 5.6.17

[9 Jun 2014 9:13] Nilnandan Joshi 
Description:
While using order by with limit, select gives incorrect result. 

mysql> show create table test \G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `role` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `role_dept_id` (`role`,`dept_id`)
) ENGINE=InnoDB AUTO_INCREMENT=155 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> 
mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city;
+-----+-----------+-----------+
| id  | name      | city      |
+-----+-----------+-----------+
| 141 | neel      | pune      |
| 125 | neel      | pune      |
| 122 | neel      | pune      |
| 133 | neel      | pune      |
|  87 | neel      | pune      |
| 129 | neel      | pune      |
|  83 | neel      | pune      |
| 137 | neel      | pune      |
|  79 | neel      | pune      |
|  75 | neel      | pune      |
| 140 | nilnandan | ahmedabad |
| 132 | nilnandan | ahmedabad |
| 136 | nilnandan | ahmedabad |
| 128 | nilnandan | ahmedabad |
| 124 | nilnandan | ahmedabad |
| 121 | nilnandan | ahmedabad |
|  86 | nilnandan | ahmedabad |
|  82 | nilnandan | ahmedabad |
|  78 | nilnandan | ahmedabad |
|  74 | nilnandan | ahmedabad |
+-----+-----------+-----------+
20 rows in set (0.00 sec)

mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 0,8;
+-----+------+------+
| id  | name | city |
+-----+------+------+
| 122 | neel | pune |
|  75 | neel | pune |
| 133 | neel | pune |
|  79 | neel | pune |
| 125 | neel | pune |
|  83 | neel | pune |
| 141 | neel | pune |
|  87 | neel | pune |
+-----+------+------+
8 rows in set (0.00 sec)

mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 8,8;
+-----+-----------+-----------+
| id  | name      | city      |
+-----+-----------+-----------+
| 133 | neel      | pune      |
|  75 | neel      | pune      |
| 128 | nilnandan | ahmedabad |
| 132 | nilnandan | ahmedabad |
| 124 | nilnandan | ahmedabad |
| 121 | nilnandan | ahmedabad |
|  86 | nilnandan | ahmedabad |
|  78 | nilnandan | ahmedabad |
+-----+-----------+-----------+
8 rows in set (0.01 sec)

mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 16,8;
+----+-----------+-----------+
| id | name      | city      |
+----+-----------+-----------+
| 86 | nilnandan | ahmedabad |
| 82 | nilnandan | ahmedabad |
| 78 | nilnandan | ahmedabad |
| 74 | nilnandan | ahmedabad |
+----+-----------+-----------+
4 rows in set (0.00 sec)

You can see that 'name' and 'city' are ordered seemingly randomly with different `limit`. More worse in this case is, ID 78 and 86 are appears in both limit 8,8 and limit 16,8

How to repeat:
CREATE TABLE `test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  `dept_id` int(11) DEFAULT NULL,
  `role` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `role_dept_id` (`role`,`dept_id`)
) ENGINE=InnoDB

mysql> select * from test;
+-----+-----------+-----------+---------+---------+
| id  | name      | city      | dept_id | role    |
+-----+-----------+-----------+---------+---------+
|  74 | nilnandan | ahmedabad |       3 | manager |
|  75 | neel      | pune      |       3 | manager |
|  76 | ramesh    | kerala    |       0 | manager |
|  77 | muhammad  | karachi   |       0 | manager |
|  78 | nilnandan | ahmedabad |       3 | manager |
|  79 | neel      | pune      |       3 | manager |
|  80 | ramesh    | kerala    |       0 | manager |
|  81 | muhammad  | karachi   |       0 | manager |
|  82 | nilnandan | ahmedabad |       3 | manager |
|  83 | neel      | pune      |       3 | manager |
|  84 | ramesh    | kerala    |       0 | manager |
|  85 | muhammad  | karachi   |       0 | manager |
|  86 | nilnandan | ahmedabad |       3 | manager |
|  87 | neel      | pune      |       3 | manager |
|  88 | ramesh    | kerala    |       0 | manager |
|  89 | muhammad  | karachi   |       0 | manager |
| 121 | nilnandan | ahmedabad |       3 | manager |
| 122 | neel      | pune      |       3 | manager |
| 124 | nilnandan | ahmedabad |       3 | manager |
| 125 | neel      | pune      |       3 | manager |
| 126 | ramesh    | kerala    |       0 | manager |
| 127 | muhammad  | karachi   |       0 | manager |
| 128 | nilnandan | ahmedabad |       3 | manager |
| 129 | neel      | pune      |       3 | manager |
| 130 | ramesh    | kerala    |       0 | manager |
| 131 | muhammad  | karachi   |       0 | manager |
| 132 | nilnandan | ahmedabad |       3 | manager |
| 133 | neel      | pune      |       3 | manager |
| 134 | ramesh    | kerala    |       0 | manager |
| 135 | muhammad  | karachi   |       0 | manager |
| 136 | nilnandan | ahmedabad |       3 | manager |
| 137 | neel      | pune      |       3 | manager |
| 138 | ramesh    | kerala    |       0 | manager |
| 139 | muhammad  | karachi   |       0 | manager |
| 140 | nilnandan | ahmedabad |       3 | manager |
| 141 | neel      | pune      |       3 | manager |
+-----+-----------+-----------+---------+---------+
36 rows in set (0.00 sec)

mysql> 

mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city;

mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 0,8;

mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 8,8;

mysql> SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city limit 16,8;
[9 Jun 2014 9:14] Nilnandan Joshi 
Dump file for test table

Attachment: test_new.sql (application/sql, text), 3.27 KiB.
[9 Jun 2014 11:14] MySQL Verification Team 
Hello Nilnandan,

Thank for the bug report and test case.
Verified as described.

Thanks,
Umesh
[9 Jun 2014 11:18] MySQL Verification Team 
5.1/5.5 are not affected where as 5.6/5.7 seems to hit this issue.
[10 Jun 2014 5:53] Tor Didriksen 
This is not a bug.
See discussion here:
http://bugs.mysql.com/bug.php?id=72076

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.