incorrect result with order by & limit
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.1 |
Invalid
|
Undecided
|
Unassigned | |||
5.5 |
Invalid
|
Undecided
|
Unassigned | |||
5.6 |
Incomplete
|
High
|
Unassigned | |||
5.7 |
Incomplete
|
High
|
Unassigned |
Bug Description
server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519
my table looks like:
CREATE TABLE `mentor_
`id` int(11) NOT NULL AUTO_INCREMENT,
`rewardable_type` varchar(30) DEFAULT NULL,
`rewardable_id` int(11) DEFAULT NULL,
`apprentice_id` int(11) DEFAULT NULL,
`reward_content` varchar(30) DEFAULT NULL,
`reward_id` int(11) DEFAULT NULL,
`state` varchar(30) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_
KEY `index_
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8
1. query without limit:
SELECT id, state, reward_content FROM mentor_
explain result:
id: 1
select_type: SIMPLE
table: mentor_
type: ref
possible_keys: index_mentor_
key: index_mentor_
key_len: 98
ref: const,const
rows: 18
Extra: Using index condition; Using where; Using filesort
query result:
+-----+
| id | state | reward_content |
+-----+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 89 | pending | 2_elo_0800 |
| 102 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 94 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 113 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+
18 rows in set (0.00 sec)
2. now with limit(explain results are identical):
SELECT id, state, reward_content FROM mentor_
+-----+
| id | state | reward_content |
+-----+
| 96 | pending | 1_level_08 |
| 85 | pending | 1_level_10 |
| 98 | pending | 1_level_10 |
| 102 | pending | 2_elo_0800 |
| 89 | pending | 2_elo_0800 |
| 117 | pending | 2_elo_1000 |
| 104 | pending | 2_elo_1000 |
| 91 | pending | 2_elo_1000 |
+-----+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_
+-----+
| id | state | reward_content |
+-----+
| 94 | rewarded | 1_level_05 |
| 107 | rewarded | 1_level_05 |
| 81 | rewarded | 1_level_05 |
| 83 | rewarded | 1_level_08 |
| 109 | rewarded | 1_level_08 |
| 111 | rewarded | 1_level_10 |
| 87 | rewarded | 2_elo_0500 |
| 100 | rewarded | 2_elo_0500 |
+-----+
8 rows in set (0.00 sec)
SELECT id, state, reward_content FROM mentor_
+-----+
| id | state | reward_content |
+-----+
| 100 | rewarded | 2_elo_0500 |
| 115 | rewarded | 2_elo_0800 |
+-----+
2 rows in set (0.00 sec)
Isn't with MySQL, `limit 16, 8` means fetching the first 24 rows, then dumping the first 16 rows? If so, why should those rows with same `state` and `reward` are ordered seemingly randomly with different `limit`? What's more worse is that in this case, ID 100 appears in both `limit 8,8` and `limit 16, 8`, while ID 113 disappears. This is really bad for application level things like pagination.
PS: In this case, I can simply use `order by state, reward_content, id` to ensure consistent order
description: | updated |
description: | updated |
description: | updated |
description: | updated |
description: | updated |
tags: | added: upstream |
Able to reproduce with PS 5.6.15 and 5.6.17
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> 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> EXPLAIN SELECT id, name, city FROM test WHERE dept_id = 3 AND role = 'manager' ORDER BY name, city; ------- -----+- ------+ ------+ ------- ------- -+----- ------- --+---- -----+- ------- -----+- -----+- ------- ------- ------- ------- ------- ------- ------- --+ ------- -----+- ------+ ------+ ------- ------- -+----- ------- --+---- -----+- ------- -----+- -----+- ------- ------- ------- ------- ------- ------- ------- --+ ------- -----+- ------+ ------+ ------- ------- -+----- ------- --+---- -----+- ------- -----+- -----+- ------- ------- ------- ------- ------- ------- ------- --+
+----+-
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-
| 1 | SIMPLE | test | ref | role_dept_id | role_dept_id | 18 | const,const | 20 | Using index condition; Using where; Using filesort |
+----+-
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 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 |
+-----+