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