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

[28 Sep 2016 15:33] Vasilij Rzhavy
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.
[28 Sep 2016 22:14] MySQL Verification Team
Results from 5.5/5.6/5.7

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

[28 Sep 2016 22:18] MySQL Verification Team
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).
[29 Sep 2016 6:51] Vasilij Rzhavy
Your last test for MySQL 5.0 and don't for 5.5.
[29 Sep 2016 8:49] Øystein Grøvlen
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;
[29 Sep 2016 8:51] Vasilij Rzhavy
Why in another versions of MySQL it is working correct?

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.