| Bug #117598 | Wrong sort order for queries with ORDER BY DESC when using range comparison with LIMIT | ||
|---|---|---|---|
| Submitted: | 28 Feb 3:28 | Modified: | 28 Feb 7:21 |
| Reporter: | Daniel Morton | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.0.35, 8.0.41, 8.4.4 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[28 Feb 3:29]
Daniel Morton
Data to reproduce the issue
Attachment: inserts.sql (application/octet-stream, text), 40.87 KiB.
[28 Feb 7:21]
MySQL Verification Team
Hello Daniel Morton, Thank you for the report and test case. Verified as described. regards, Umesh
[1 Apr 5:09]
Andy Mok
I also ran into this same problem using the IN operator. Using the same table and data as OP and on version 8.0.35, here is a reproducible example: ``` > select pay_month, _id from employee_payment where employee_id = 'EMP010' and pay_month IN (829, 973) and status = 'PAID' order by pay_month desc, _id desc limit 10; +-----------+-----+ | pay_month | _id | +-----------+-----+ | 973 | 6 | | 973 | 13 | | 973 | 19 | | 973 | 20 | | 973 | 38 | | 973 | 87 | | 973 | 111 | | 973 | 124 | | 973 | 151 | | 973 | 168 | +-----------+-----+ 10 rows in set (0.001 sec) ```
[22 Sep 15:11]
Saumitr Pathak
This bug is in the LOW LIMIT recheck heuristic path. Here, the optimizer does not take into account the complete set of key parts required for an index to provide order (i.e. secondary __and__ primary key parts). Because of this, it ends up setting up the reverse scan without having accounting for the primary (suffix) key parts. This ends up triggering another optimization in the reverse range iterator, where it finds equality conditions on all key parts of the SK (other than the range), and not realizing there's are PK key parts suffixed to the SK key parts, it thinks the current range can be forward iterated.
The following should fix this:
diff --git a/fbcode/mysql/server/sql/sql_optimizer.cc b/fbcode/mysql/server/sql/sql_optimizer.cc
--- a/fbcode/mysql/server/sql/sql_optimizer.cc
+++ b/fbcode/mysql/server/sql/sql_optimizer.cc
@@ -10348,9 +10348,25 @@
used_index(tab->range_scan()) != MAX_KEY) {
const uint ref_key = used_index(tab->range_scan());
bool skip_quick;
+ uint used_key_parts = 0;
+ /*
+ bug#117598: when testing if the currently chosen key can
+ provide the requested ordering, always use the key parts
+ identified by test_if_order_by_key as essential in
+ providing the ordering (used_key_parts below). If the
+ requested ordering spans SK and PK key parts, then not
+ using `used_key_parts` means that only SK key parts are
+ actually considered essential for the reverse iterator.
+
+ This makes the reverse iterator think that this is a
+ range where a forward scan will give the right results
+ (since all SK key parts get utilized and the current
+ range is interpreted as comprised of equal key parts)
+ */
+
read_direction = test_if_order_by_key(
thd, &join->order, tab->table(), tab->type(), ref_key,
- nullptr, &skip_quick,
+ &used_key_parts, &skip_quick,
join->query_expression()->select_limit_cnt);
if (skip_quick) read_direction = 0;
/*
@@ -10365,8 +10381,7 @@
if (read_direction == 1 ||
(read_direction == -1 &&
reverse_sort_possible(tab->range_scan()) &&
- !make_reverse(get_used_key_parts(tab->range_scan()),
- tab->range_scan()))) {
+ !make_reverse(used_key_parts, tab->range_scan()))) {
recheck_reason = DONT_RECHECK;
}
}
Description: MySQL is ordering results in ASC order, when DESC order is specified (under very specific conditions) This appears to occur for queries matching the following conditions: - A table that has a PRIMARY KEY on a BIGINT column - An index exists on multiple columns, where the index does not specify an order for the columns - The query has an ORDER BY on more than one column, where both columns are present in the INDEX above, and both columns are ORDER BY DESC - And the last column of the ORDER BY is the primary key - And the query has a filter using both >= and <= for the same value (e.g. month >= 100 AND month <= 100) The expectation is that the data is sorted in descending order of primary key How to repeat: Schema: ``` CREATE TABLE employee_payment ( _id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY, employee_id CHAR(11) CHARACTER SET ascii COLLATE ascii_bin NOT NULL, status ENUM('PAID', 'NOT_PAID', 'IN_PROGRESS') NOT NULL, -- The month the employee was paid in pay_month INT NOT NULL, -- The amount paid to the employee amount DECIMAL(16, 8) NOT NULL, -- Support searching for all payments to an employee in a particular month. INDEX employee_id_status_pay_month (employee_id, status, pay_month) ); ``` Example query that reproduces the issue (test data will be uploaded as a separate attachment): ``` select pay_month, _id from employee_payment where employee_id = 'EMP010' and pay_month >= 973 and pay_month <= 973 and status = 'PAID' order by pay_month desc, _id desc limit 10; ``` Noting that the issue is only reproducible when a certain amount of data exists in the table (will upload in a separate file)