Issues
- Sort bug in special casePS-6971Resolved issue: PS-6971
- DOC: statement about innodb_encrypt_tables = OFF is wrongPS-5491Resolved issue: PS-5491patrick.birch
- mysqld got signal 11PS-4902Resolved issue: PS-4902
- Percona Server Crashing when the stored procedure called the second time in the same sessionPS-4759Resolved issue: PS-4759Anton Matvienko
- Many Percona Server crashes (mysqld got signal 11).PS-4589Resolved issue: PS-4589
- PS compilation with GCC 7.3.0 + ASAN failurePS-4562Resolved issue: PS-4562Przemyslaw Skibinski
- protobuf compilation issue with Travis CIPS-4545Resolved issue: PS-4545Przemyslaw Skibinski
- False-positive detection "foreign key with 'CASCADE' clause" in group replication pluginPS-4544Resolved issue: PS-4544
- Fix for PS-3919 introduces test failures for rocksdb.rocksdb.part and rocksdb.partitionPS-4538Resolved issue: PS-4538Zsolt Parragi
- MTR: index_merge_rocksdb2 inadvertently tests InnoDB instead of MyRocksPS-4529Resolved issue: PS-4529George Lorch
- Last 3 Travis jobs after merge are always ignoredPS-4526Resolved issue: PS-4526Przemyslaw Skibinski
- innodb-optimize-keys fails when variable name is a prefixPS-4524Resolved issue: PS-4524Przemyslaw Skibinski
- TokuDB/Percona - Insert Performance worse as table grows, with cardinality quirkPS-4516Resolved issue: PS-4516George Lorch
- rpl.bug68490 is unstable in 5.7PS-4514Resolved issue: PS-4514Przemyslaw Skibinski
- Limit clang-format checkings to given extensionsPS-4511Resolved issue: PS-4511Przemyslaw Skibinski
- Some `memcached` tests failedPS-4509Resolved issue: PS-4509
- Backport fixes from 8.0 for InnoDB memcached PluginPS-4506Resolved issue: PS-4506Przemyslaw Skibinski
- Inconsistent escaping requirements for mysql CLI with string literal containing '*' (star)PS-4496Resolved issue: PS-4496
- `-Werror` is always disabled for `innodb_memcached`PS-4488Resolved issue: PS-4488Przemyslaw Skibinski
- Word with quote sign cannot be found even with ft_query_extra_word_chars=1PS-4484Resolved issue: PS-4484Satya Bodapati
- MyRocks assertion=assertion@entry=0x7f60e55e8c13 "whitelist != nullptr"PS-4474Resolved issue: PS-4474George Lorch
- Handlerton. trx->tokudb_lock_count > 0PS-4294Resolved issue: PS-4294George Lorch
- TDB-114 (Change use of MySQL HASH to unordered_map) introduces memory leakPS-4265Resolved issue: PS-4265George Lorch
- Test rocksdb.concurrent_alter output not updatedPS-3983Resolved issue: PS-3983George Lorch
- Add ccache support for Travis CI config filePS-3823Resolved issue: PS-3823
- refactoring of Travis config filePS-3816Resolved issue: PS-3816Przemyslaw Skibinski
- LP #1527463: Waiting for binlog lockPS-3345Resolved issue: PS-3345Przemyslaw Skibinski
27 of 27
Sort bug in special case
Won't Do
General
Escalation
General
Escalation
Description
Environment
Docker image and plain package installation on Ubuntu 16.
Attachments
1
Smart Checklist
Details
Details
Assignee
Unassigned
UnassignedReporter
SteffenS
SteffenS
Labels
Upstream Bug URL
Priority
MediumSmart Checklist
Smart Checklist
Created April 3, 2020 at 5:03 PM
Updated March 4, 2025 at 9:09 PM
Resolved March 4, 2025 at 9:09 PM
Activity
Show:
Julia Vural March 4, 2025 at 9:09 PM
It appears that this issue is no longer being worked on, so we are closing it for housekeeping purposes. If you believe the issue still exists, please open a new ticket after confirming it's present in the latest release.
Lalit Choudhary April 14, 2020 at 2:28 PM
Upstream report:
Lalit Choudhary April 14, 2020 at 1:50 PMEdited
Testing further I found that issue originally reported for 5.7 old vs New version, Can also visible between 5.7.29 and 8.0.19 version.
Percona server:
Server version: 5.7.29-32-log Percona Server
SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0;
+-----------+-------------------+
| order_id | external_order_id |
+-----------+-------------------+
| 555000000 | 671263031366 |
| 555000001 | 709922095177 |
| 555000002 | 709949980745 |
| 555000003 | 710472335433 |
| 555000004 | 650890018872 |
+-----------+-------------------+
5 rows in set (2.21 sec)
Server version: 8.0.19-10 Percona Server
SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0;
+-----------+-------------------+
| order_id | external_order_id |
+-----------+-------------------+
| 555292700 | 2004823834659 |
| 555292699 | 2087842381870 |
| 555292698 | 2168547836040 |
| 555292697 | NULL |
| 555292696 | NULL |
+-----------+-------------------+
5 rows in set (0.00 sec)Upstream MySQL:
mysql 5.7.29
SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0;
+-----------+-------------------+
| order_id | external_order_id |
+-----------+-------------------+
| 555000000 | 671263031366 |
| 555000001 | 709922095177 |
| 555000002 | 709949980745 |
| 555000003 | 710472335433 |
| 555000004 | 650890018872 |
+-----------+-------------------+
5 rows in set (1.22 sec)
MySQL 8.0.19
SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0;
+-----------+-------------------+
| order_id | external_order_id |
+-----------+-------------------+
| 555292700 | 2004823834659 |
| 555292699 | 2087842381870 |
| 555292698 | 2168547836040 |
| 555292697 | NULL |
| 555292696 | NULL |
+-----------+-------------------+
5 rows in set (0.00 sec)So Here we have 2 issues when using order by with limit,
1. Inconsistent output between versions
2. Order By sorting is wrong
SteffenS April 14, 2020 at 10:49 AM
Thank you!
Lalit Choudhary April 14, 2020 at 10:46 AM
@SteffenS
Thank you for your reply.
for consistent output for both 5.7.20 and 5.7.29 was referring to the upstream mysql versions test.
Regarding "ORDER BY t1.order_id DESC" yes, results for the order_id column is ASC and not DESC.
I will review it again and re-open it.
We found a bug in a usual query, which produces results in a wrong order.
The query is like this:
SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0;Where we get different results in different Server Version.
In version 5.7.20-19 we get:
mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.25-28, for debian-linux-gnu (x86_64) using 6.3 Connection id: 11 Current database: sort_bug Current user: root@192.168.150.1 SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.20-19 Percona Server (GPL), Release '19', Revision '3c5d3e5d53c' Protocol version: 10 Connection: 192.168.150.5 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 45 min 28 sec Threads: 1 Questions: 641 Slow queries: 0 Opens: 124 Flush tables: 1 Open tables: 106 Queries per second avg: 0.234 -------------- mysql> SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0; +-----------+-------------------+ | order_id | external_order_id | +-----------+-------------------+ | 555292700 | 2004823834659 | | 555292699 | 2087842381870 | | 555292698 | 2168547836040 | | 555292697 | NULL | | 555292696 | NULL | +-----------+-------------------+ 5 rows in set (1.67 sec)(the correct result)
Beginning from 5.7.21 up to the current latest (5.7.29), we get this result:
mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.25-28, for debian-linux-gnu (x86_64) using 6.3 Connection id: 13 Current database: sort_bug Current user: root@192.168.150.1 SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.21-21 Percona Server (GPL), Release '21', Revision '2a37e4e' Protocol version: 10 Connection: 192.168.150.2 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 8 hours 31 min 46 sec Threads: 1 Questions: 679 Slow queries: 0 Opens: 152 Flush tables: 1 Open tables: 122 Queries per second avg: 0.022 -------------- mysql> SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0; +-----------+-------------------+ | order_id | external_order_id | +-----------+-------------------+ | 555000000 | 671263031366 | | 555000001 | 709922095177 | | 555000002 | 709949980745 | | 555000003 | 710472335433 | | 555000004 | 650890018872 | +-----------+-------------------+ 5 rows in set (0.86 sec)mysql> \s -------------- mysql Ver 14.14 Distrib 5.7.25-28, for debian-linux-gnu (x86_64) using 6.3 Connection id: 15 Current database: sort_bug Current user: root@192.168.150.1 SSL: Cipher in use is ECDHE-RSA-AES128-GCM-SHA256 Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 5.7.29-32 Percona Server (GPL), Release 32, Revision 56bce88 Protocol version: 10 Connection: 192.168.150.6 via TCP/IP Server characterset: latin1 Db characterset: latin1 Client characterset: utf8 Conn. characterset: utf8 TCP port: 3306 Uptime: 43 min 51 sec Threads: 1 Questions: 772 Slow queries: 0 Opens: 139 Flush tables: 1 Open tables: 111 Queries per second avg: 0.293 -------------- mysql> SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0; +-----------+-------------------+ | order_id | external_order_id | +-----------+-------------------+ | 555000000 | 671263031366 | | 555000001 | 709922095177 | | 555000002 | 709949980745 | | 555000003 | 710472335433 | | 555000004 | 650890018872 | +-----------+-------------------+ 5 rows in set (0.94 sec)Take care of the wrong sort order while using the DESC keyword.
Even more we get the correct order, when leaving out the external_order_id column and confusing results with other select sets:
mysql> SELECT DISTINCT t1.order_id, t1.external_order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0; +-----------+-------------------+ | order_id | external_order_id | +-----------+-------------------+ | 555000000 | 671263031366 | | 555000001 | 709922095177 | | 555000002 | 709949980745 | | 555000003 | 710472335433 | | 555000004 | 650890018872 | +-----------+-------------------+ 5 rows in set (0.94 sec) mysql> SELECT DISTINCT t1.order_id FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0; +-----------+ | order_id | +-----------+ | 555292700 | | 555292699 | | 555292698 | | 555292697 | | 555292696 | +-----------+ 5 rows in set (0.88 sec) mysql> SELECT DISTINCT * FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0; +-------------------+-----------+-------------------+-------+ | order_revision_id | order_id | external_order_id | state | +-------------------+-----------+-------------------+-------+ | 405 | 555000404 | 701250011200 | NEW | | 720 | 555000719 | 1000474738801 | NEW | | 737 | 555000736 | 1001633513585 | NEW | | 3145 | 555001396 | 1000474738801 | NEW | | 3146 | 555001397 | 1001633513585 | NEW | +-------------------+-----------+-------------------+-------+ 5 rows in set (1.15 sec) mysql> SELECT DISTINCT t1.* FROM t1 INNER JOIN t2 USING (order_revision_id) ORDER BY t1.order_id DESC LIMIT 5 OFFSET 0; +-----------+-------------------+-------------------+ | order_id | order_revision_id | external_order_id | +-----------+-------------------+-------------------+ | 555000000 | 17720 | 671263031366 | | 555000001 | 6639 | 709922095177 | | 555000002 | 6640 | 709949980745 | | 555000003 | 6641 | 710472335433 | | 555000004 | 17721 | 650890018872 | +-----------+-------------------+-------------------+ 5 rows in set (0.95 sec)Please investigate, whats the reason ...
Thanks in advance!