incorrect result with order by & limit

[winchester]

server version is 5.6.15-56-log Percona Server (GPL), Release rel63.0, Revision 519

my table looks like:
CREATE TABLE `mentor_appren_rewards` (
  `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_mentor_appren_rewards_on_rewardable_type_and_rewardable_id` (`rewardable_type`,`rewardable_id`),
  KEY `index_mentor_appren_rewards_on_apprentice_id` (`apprentice_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6306 DEFAULT CHARSET=utf8

1. query without limit:
SELECT id, state, reward_content FROM mentor_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content;

explain result:
id: 1
select_type: SIMPLE
table: mentor_appren_rewards
type: ref
possible_keys: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
key: index_mentor_appren_rewards_on_rewardable_type_and_rewardable_id
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_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 0,8;
+-----+---------+----------------+
| 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_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 8,8;
+-----+----------+----------------+
| 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_appren_rewards WHERE rewardable_id = 11 AND rewardable_type = 'Mentor' ORDER BY state, reward_content limit 16,8;
+-----+----------+----------------+
| 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

[Nilnandan Joshi (nilnandan-joshi)]

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 |
+-----+-----------+----------...

[Nilnandan Joshi (nilnandan-joshi)]

Can't able to reproduce with PS 5.1.71

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.01 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 where; Using filesort |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------------+
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 limit 0,8;
+-----+------+------+
| 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 |
+-----+------+------+
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 |
+-----+-----------+-----------+
| 79 | neel | pune |
| 75 | neel | pune |
| 140 | nilnandan | ahmedabad |
| 132 | nilnandan | ahmedabad |
| 136 | nilnandan | ahmedabad |
| 128 | nilnandan | ahmedabad |
| 124 | nilnandan | ahmedabad |
| 121 | nilnandan | ahmedabad |
+-----+-----------+-----------+
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 lim...

[Nilnandan Joshi (nilnandan-joshi)]

Can't able to reproduce with PS 5.5

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 where; Using filesort |
+----+-------------+-------+------+---------------+--------------+---------+-------------+------+-----------------------------+
1 row in set (0.01 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 |
+-----+------+------+
| 141 | neel | pune |
| 125 | neel | pune |
| 122 | neel | pune |
| 133 | neel | pune |
| 87 | neel | pune |
| 129 | neel | pune |
| 83 | neel | pune |
| 137 | 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 |
+-----+-----------+-----------+
| 79 | neel | pune |
| 75 | neel | pune |
| 140 | nilnandan | ahmedabad |
| 132 | nilnandan | ahmedabad |
| 136 | nilnandan | ahmedabad |
| 128 | nilnandan | ahmedabad |
| 124 | nilnandan | ahmedabad |
| 121 | nilnandan | ahmedabad |
+-----+-----------+-----------+
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 limi...

[Laurynas Biveinis (laurynas-biveinis)]

The upstream bug was closed as not a bug. Please check it, do you agree with its resolution?

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-784