MySQL Bugs: #83191: Bug of sorting

Bug #83191	Bug of sorting
Submitted:	28 Sep 2016 15:33	Modified:	29 Sep 2016 8:51
Reporter:	Vasilij Rzhavy	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S1 (Critical)
Version:	5.6.24	OS:	MacOS (10.9.5)
Assigned to:		CPU Architecture:	Any

Description:
Sorting with some value of fields to work incorrect.

How to repeat:
Table dump:
-----------
CREATE TABLE `test` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `datetime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `dateint` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `test` (`id`, `datetime`, `dateint`)
VALUES
	(1,'2016-09-28 19:49:14',1475074414),
	(2,'2016-09-28 19:49:15',1475074415),
	(3,'2016-09-28 19:49:16',1475074416),
	(4,'2016-09-28 19:49:16',1475074416),
(5,'2016-09-28 19:49:17',1475074417);

Queries:
--------
# for `datetime`
#SELECT * FROM `test` ORDER BY `datetime` LIMIT 3;
#SELECT * FROM `test` ORDER BY `datetime` LIMIT 3 OFFSET 3;
# for `dateint`
#SELECT * FROM `test` ORDER BY `dateint` LIMIT 3;
#SELECT * FROM `test` ORDER BY `dateint` LIMIT 3 OFFSET 3;

Example:
--------
1)
Run: `SELECT * FROM `test` ORDER BY `datetime` LIMIT 3;`.
Out: 
1	2016-09-28 19:49:14	1475074414
2	2016-09-28 19:49:15	1475074415
4	2016-09-28 19:49:16	1475074416
Question: Where the record with id=3? 
2)
Run: `SELECT * FROM `test` ORDER BY `datetime` LIMIT 3 OFFSET 3;`
Out:
4	2016-09-28 19:49:16	1475074416
5	2016-09-28 19:49:17	1475074417
Question: Where the record with id=3? 

Result
------
The record missing with id=3.

Suggested fix:
I don't know.

Results from 5.5/5.6/5.7

Attachment: bug#83191.txt (text/plain), 6.99 KiB.

Thank you for the bug report. Only 5.6 server version affected for this bug in case 1 (case 2 is using offset 3 then expected result id: 4-5).

Your last test for MySQL 5.0 and don't for 5.5.

This is not a bug.  The queries do not specify a deterministic ordering. To
always get a correct result, you need to specify a deterministic order for
rows with same date:

SELECT * FROM test ORDER BY dateint, id LIMIT 3;

Why in another versions of MySQL it is working correct?