MySQL Bugs: #119198: Optimizer with reverse index range scan gives incorrect query result

Bug #119198	Optimizer with reverse index range scan gives incorrect query result
Submitted:	20 Oct 19:44	Modified:	22 Oct 6:59
Reporter:	Somrak Monpengpinij (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.4.6	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When executing a query that performs a reverse index range scan eg. the query which includes ORDER BY ... DESC clause with range, it returns incorrect results for example.

1. Given the following table:

CREATE TABLE temp_table (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  col1 TINYINT UNSIGNED NOT NULL,
  col2 TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (id),
  KEY temp_table_idx01 (col1)
) ENGINE=InnoDB;

2. Insert some data into the table:

INSERT INTO temp_table (id, col1, col2)
VALUES (10, 1, 4);

INSERT INTO temp_table (id, col1, col2)
VALUES (20, 1, 4);

3. Verify there should be 2 records with id of 10, 20 respectively:

SELECT * FROM temp_table;

id	col1	col2
10	1	4
20	1	4

4. Analyze the query:

EXPLAIN
SELECT * FROM temp_table
WHERE col1 >= 1 AND col1 <= 1
ORDER BY col1 DESC, id DESC
LIMIT 1;

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	temp_table	NULL	range	temp_table_idx01	temp_table_idx01	1	NULL	2	100.00	Using index condition; Backward index scan

5. Then Running this query:

SELECT * FROM temp_table
WHERE col1 >= 1 AND col1 <= 1
ORDER BY col1 DESC, id DESC
LIMIT 1;

id	col1	col2
10	1	4

The correct result should be id = 20, since the query orders by both col1 DESC, id DESC, and col1 is an EQ range (i.e., constant within the range). However the actual query result id = 10 which is incorrect.

How to repeat:
1. Create table which has an index on a column and a primary key eg. above `temp_table`.

2. Run query which force using reverse index range scan and limit 1 result eg above query.

3. The result will be incorrect consider descending order.

Suggested fix:
suggested fix will be on the pull request.

Hi Somrak Monpengpinij,

Thank you for the test case. Verified as described.

I’ve identified the root cause and submitted a pull request on GitHub as well, which is currently pending OCA approval.

https://github.com/mysql/mysql-server/pull/633

Contribution submitted via Github - Bug#119198 fix bug optimizer reverse index scan give incorrect result (*) Contribution by Somrak Monpengpinij (Github srmppn, mysql-server/pull/633#issuecomment-3445475772): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_2931065335.txt (text/plain), 6.13 KiB.