incorrect result with order by & limit

Bug #1312001 reported by winchester on 2014-04-24
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL Server
Unknown
Unknown
Percona Server
Status tracked in 5.7
5.1
Undecided
Unassigned
5.5
Undecided
Unassigned
5.6
High
Unassigned
5.7
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_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

winchester (wjp712) on 2014-04-24
description: updated
description: updated
winchester (wjp712) on 2014-04-24
description: updated
description: updated
description: updated
Download full text (3.5 KiB)

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

Read more...

Download full text (3.4 KiB)

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

Read more...

Download full text (3.4 KiB)

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

Read more...

tags: added: upstream

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

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.