Weird things caused by default sorting

[Andrey]

Hello. I've got a table like the attached one.

Then I launch following query:

SELECT id,k1 FROM table1 WHERE id > 3400 ORDER BY k1 DESC LIMIT 0,20 -- getting ids 3405,3406 etc

then I change the limit:

SELECT id,k1 FROM table1 WHERE id > 3400 ORDER BY k1 DESC LIMIT 20,20 -- and I get 3405, 3406 AGAIN in a row set.

reproduced on 5.6.21-70.1 .

[Nilnandan Joshi (nilnandan-joshi)]

Verified with 5.6.26.

nilnandan@desktop:~$ mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55
Server version: 5.6.26-74.0-log Percona Server (GPL), Release 74.0, Revision 32f8dfd

Copyright (c) 2009-2015 Percona LLC and/or its affiliates
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>
mysql> SELECT id,k1 FROM table1 WHERE id > 3400 ORDER BY k1 DESC LIMIT 0,20;
+------+----+
| id | k1 |
+------+----+
| 3669 | 2 |
| 3543 | 1 |
| 3705 | 1 |
| 3698 | 1 |
| 3421 | 1 |
| 3690 | 1 |
| 3671 | 1 |
| 3407 | 0 |
| 3420 | 0 |
| 3419 | 0 |
| 3418 | 0 |
| 3403 | 0 |
| 3415 | 0 |
| 3414 | 0 |
| 3413 | 0 |
| 3412 | 0 |
| 3405 | 0 |
| 3410 | 0 |
| 3812 | 0 |
| 3406 | 0 |
+------+----+
20 rows in set (0.00 sec)

mysql> SELECT id,k1 FROM table1 WHERE id > 3400 ORDER BY k1 DESC LIMIT 20,20;
+------+----+
| id | k1 |
+------+----+
| 3440 | 0 |
| 3441 | 0 |
| 3425 | 0 |
| 3424 | 0 |
| 3423 | 0 |
| 3405 | 0 |
| 3407 | 0 |
| 3409 | 0 |
| 3410 | 0 |
| 3411 | 0 |
| 3412 | 0 |
| 3413 | 0 |
| 3414 | 0 |
| 3415 | 0 |
| 3417 | 0 |
| 3418 | 0 |
| 3419 | 0 |
| 3420 | 0 |
| 3406 | 0 |
| 3422 | 0 |
+------+----+
20 rows in set (0.00 sec)

mysql> select * from table1 where id = 3405;
+------+----+
| id | k1 |
+------+----+
| 3405 | 0 |
+------+----+
1 row in set (0.00 sec)

mysql> select * from table1 where id = 3406;
+------+----+
| id | k1 |
+------+----+
| 3406 | 0 |
+------+----+
1 row in set (0.00 sec)

mysql>

[Laurynas Biveinis (laurynas-biveinis)]

Upstream?

[Laurynas Biveinis (laurynas-biveinis)]

Reverting to "New" in order to get upstream status checked

[Nickolay Ihalainen (ihanick)]

Not a Bug in upstream, explanation could be found in earlier bug:
https://bugs.mysql.com/bug.php?id=72076

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-931