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
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S2 (Serious)
• Version: 8.0.35, 8.0.41, 8.4.4
• OS: Any
• CPU Architecture: Any
• Tags: regression

[28 Feb 3:28] Daniel Morton

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)

[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)
```