Bug #87367 SELECT returns same rows with different LIMIT offset

• Submitted: 10 Aug 2017 9:33
• Modified: 15 Aug 2017 11:01
• Reporter: Nikolai Ikhalainen
• Status: Not a Bug
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.6.37,5.7.17, 5.7.19
• OS: Any
• CPU Architecture: Any

[10 Aug 2017 9:33] Nikolai Ikhalainen

Description:
Original bug report: "Weird things caused by default sorting" https://bugs.launchpad.net/percona-server/+bug/1507164

SELECT id,k1 FROM table1 WHERE id > 3400 ORDER BY k1 DESC LIMIT 0,20
SELECT id,k1 FROM table1 WHERE id > 3400 ORDER BY k1 DESC LIMIT 20,20

Both queries having "| 3407 |  0 |" row in output.

For 5.7 different results returned, but value 3812 exists in both result sets.
Also LIMIT 0,20 result set is different from LIMIT 0,40

How to repeat:
docker run --rm --name m56 -e MYSQL_ALLOW_EMPTY_PASSWORD=1 -it mysql/mysql-server:5.6
docker exec -it m56
yum install wget
wget https://bugs.launchpad.net/percona-server/+bug/1507164/+attachment/4498046/+files/table1.s...
mysql -e 'create database test'
mysql test < table1.sql
mysql test -e 'SELECT id,k1 FROM table1 WHERE id > 3400 ORDER BY k1 DESC LIMIT 20,20'|grep 3407
3407    0
mysql test -e 'SELECT id,k1 FROM table1 WHERE id > 3400 ORDER BY k1 DESC LIMIT 0,20'|grep 3407
3407    0

[10 Aug 2017 12:12] MySQL Verification Team

Hello Nikolai,

Thank you for the report and test case.

Thanks,
Umesh

[15 Aug 2017 11:01] Erlend Dahl

Posted by developer:

[11 Aug 2017 1:03] Tor Didriksen

This is not a bug, see discussion for Bug#72076 and documentation at
https://dev.mysql.com/doc/refman/5.6/en/limit-optimization.html

If you want order by primary key, you have to say so explicitly.