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
• Status: Duplicate
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 5.6.15, 5.6.17, 5.6.20, 5.7.5
• OS: Any
• Assigned to: Tor Didriksen
• 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