MySQL Bugs: #75750: incorrect sorting results when using float field and LIMIT clause

Bug #75750	incorrect sorting results when using float field and LIMIT clause
Submitted:	3 Feb 2015 14:10	Modified:	6 Feb 2015 14:48
Reporter:	Michał Grabowski	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.6.22, 5.6.23	OS:	Linux (gentoo)
Assigned to:		CPU Architecture:	Any
Tags:	FLOAT, limit, OFFSET, order by, regression

Description:
Hello,
After upgrading mysql to version 5.6.22, there is a problem with queries which use float field to sort and LIMIT clause for pagination.

My table definition is:

CREATE TABLE `test` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `position` float(8,2) unsigned DEFAULT '9999.00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

Query:

SELECT id,position FROM test order by position limit 0,40;
+-----+----------+
| id  | position |
+-----+----------+
| 489 |     0.10 |
| 486 |     9.00 |
| 487 |    10.10 |
| 488 |    10.10 |
| 476 |    10.20 |
| 464 |  9999.00 |
| 463 |  9999.00 |
| 462 |  9999.00 |
| 461 |  9999.00 |
| 460 |  9999.00 |
| 459 |  9999.00 |
| 458 |  9999.00 |
| 465 |  9999.00 |
| 466 |  9999.00 |
| 474 |  9999.00 |
| 473 |  9999.00 |
| 472 |  9999.00 |
| 471 |  9999.00 |
| 470 |  9999.00 |
| 469 |  9999.00 |
| 468 |  9999.00 |
| 467 |  9999.00 |
| 457 |  9999.00 |
| 456 |  9999.00 |
| 438 |  9999.00 |
| 439 |  9999.00 |
| 440 |  9999.00 |
| 442 |  9999.00 |
| 443 |  9999.00 |
| 444 |  9999.00 |
| 445 |  9999.00 |
| 446 |  9999.00 |
| 447 |  9999.00 |
| 448 |  9999.00 |
| 450 |  9999.00 |
| 451 |  9999.00 |
| 452 |  9999.00 |
| 453 |  9999.00 |
| 454 |  9999.00 |
| 455 |  9999.00 |
+-----+----------+
40 rows in set (0,00 sec)

Please note at the moment id 459,458

Second query 

 SELECT id,position FROM test order by position limit 40,40;
+-----+----------+
| id  | position |
+-----+----------+
| 435 |  9999.00 |
| 447 |  9999.00 |
| 448 |  9999.00 |
| 460 |  9999.00 |
| 459 |  9999.00 |
| 458 |  9999.00 |
| 457 |  9999.00 |
| 456 |  9999.00 |
| 455 |  9999.00 |
| 454 |  9999.00 |
| 453 |  9999.00 |
| 452 |  9999.00 |
| 451 |  9999.00 |
| 450 |  9999.00 |
| 449 |  9999.00 |
| 434 |  9999.00 |
+-----+----------+
16 rows in set (0,00 sec)

Id 459,458 is still there ,why ?

Problem exists in version 5.6.22 . Tested in 5.5.41 and problem did not occurs.
It seems like the problem is float field, because any other field using to sort, returns correct result. 
Try this query changing "order by position" to "order by id"

Her is data sample:

INSERT INTO `test` (`id`,`position`) VALUES (434,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (435,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (436,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (437,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (438,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (439,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (440,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (441,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (442,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (443,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (444,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (445,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (446,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (447,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (448,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (449,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (450,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (451,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (452,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (453,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (454,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (455,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (456,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (457,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (458,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (459,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (460,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (461,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (462,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (463,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (464,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (465,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (466,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (467,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (468,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (469,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (470,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (471,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (472,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (473,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (474,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (475,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (476,10.20);
INSERT INTO `test` (`id`,`position`) VALUES (477,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (478,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (479,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (480,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (481,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (482,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (483,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (484,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (485,9999.00);
INSERT INTO `test` (`id`,`position`) VALUES (486,9.00);
INSERT INTO `test` (`id`,`position`) VALUES (487,10.10);
INSERT INTO `test` (`id`,`position`) VALUES (488,10.10);
INSERT INTO `test` (`id`,`position`) VALUES (489,0.10);

How to repeat:
Any time

Thank you for the report.

Verified as described.

Bug is not repeatable with versions 5.5 and 5.7

Posted by developer:
 
This issue comes up once in a while.  Note that MySQL treat the two queries as two separate queries.  The optimizer will try to find the optimal query plan for each query, and may come up with different query plans that give different ordering of the result if ordering is not fully specified.  If one wants to have the same ordering for both queries, one will have to fully specify the ordering.  For the given example, the ORDER BY clause should be "ORDER BY position, id".

Thank You for you answer.

What do You mean saying " if ordering is not fully specified", does ORDER by position isn't enough?

Why this query work well in 5.5 and 5.7 ?

Don't You think that this behavior in 5.6.22 is a little bit surprising and confusing?
This leads to unpredictable results.

Additionally only float field are affected, so how Your tip  refer to other field types ?

"ORDER by position" does not fully specify the order because any order of the ids with equal position is allowed.  If the two queries use different  access methods, (e.g. one uses table scan with file sort, the other uses index range scan), order of rows may be different.

In 5.5 and 5.7, it may seem to work well because in those cases, same query plan is used for both queries.

If you want to avoid unpredictable results, you need to tell MySQL that the order of ids matters to you.  Otherwise, the MySQL optimizer will pick the query plan it thinks is fastest, regardless of what previous queries you may have run.

Thus the optimizer behavior in that case in 5.6.22 is considered temporary ?
5.7 will "fix" this ?

Any query optimizer behavior is temporary.  As your data evolve, what is the most optimal way to execute a query may change; even in the same version.  Also, in new major versions, new optimizations are added and the cost model adjusted so that query plans may change.  If your query depends on a specific ordering of the rows in the result set, you need to specify this ordering.

Thank you for the explanation.