| Bug #113892 | FORCE INDEX reports wrong number of rows | ||
|---|---|---|---|
| Submitted: | 5 Feb 2024 10:39 | Modified: | 5 Feb 2024 14:13 |
| Reporter: | Sveta Smirnova (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
| Version: | 8.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | regression | ||
[5 Feb 2024 11:56]
MySQL Verification Team
Hi Mrs. Sveta Smirnova, Thank you for your bug report. We managed to reproduce your report: +------+------+------+ | a | b | c | +------+------+------+ | 1 | 2 | 3 | | 4 | 5 | 6 | | 7 | 8 | 9 | | 0 | 0 | 0 | +------+------+------+ +------+ | a | +------+ | 0 | | 1 | | 4 | | 7 | +------+ +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t1 | NULL | range | a | a | 5 | NULL | 1 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` FORCE INDEX (`a`) where ((`test`.`t1`.`a` = 1) or (`test`.`t1`.`a` <> 1)) | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------+ +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ | 1 | SIMPLE | t1 | NULL | index | a | a | 5 | NULL | 4 | 100.00 | Using where; Using index | +----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+ +-------+------+---------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`t1`.`a` AS `a` from `test`.`t1` USE INDEX (`a`) where ((`test`.`t1`.`a` = 1) or (`test`.`t1`.`a` <> 1)) | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------+ Hence, this is now a verified bug report. Still this is a low priority bug ........ Also, we do not hear very much from you, lately ........
[5 Feb 2024 11:57]
MySQL Verification Team
This bug is verified with 8.0.36 and 8.3.0.
[5 Feb 2024 14:13]
Sveta Smirnova
Hi MySQL Verification Team! Thank you for verifying my bug report. Please check your Messenger Inbox =))))
Description: For the query that returns all rows from the table EXPLAIN with FORCE INDEX returns 1 row in column "rows" instead of the correct number of rows. EXPLAIN with USE INDEX works correctly. Version 8.0.34 and earlier also work correctly. How to repeat: mysql> SHOW CREATE TABLE dupe_key\G *************************** 1. row *************************** Table: dupe_key Create Table: CREATE TABLE `dupe_key` ( `a` int DEFAULT NULL, `b` int DEFAULT NULL, `c` int DEFAULT NULL, KEY `a` (`a`), KEY `a_2` (`a`,`b`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0,00 sec) mysql> SELECT * FROM dupe_key; +------+------+------+ | a | b | c | +------+------+------+ | 1 | 2 | 3 | | 4 | 5 | 6 | | 7 | 8 | 9 | | 0 | 0 | 0 | +------+------+------+ 4 rows in set (0,00 sec) mysql> SELECT a FROM `dupe_key` FORCE INDEX (`a`) WHERE a=1 OR a<>1; +------+ | a | +------+ | 0 | | 1 | | 4 | | 7 | +------+ 4 rows in set (0,00 sec) mysql> EXPLAIN SELECT a FROM `dupe_key` FORCE INDEX (`a`) WHERE a=1 OR a<>1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dupe_key partitions: NULL type: range possible_keys: a key: a key_len: 5 ref: NULL rows: 1 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0,00 sec) mysql> EXPLAIN SELECT a FROM `dupe_key` USE INDEX (`a`) WHERE a=1 OR a<>1\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: dupe_key partitions: NULL type: index possible_keys: a key: a key_len: 5 ref: NULL rows: 4 filtered: 100.00 Extra: Using where; Using index 1 row in set, 1 warning (0,00 sec) Suggested fix: Return previous (correct) behavior: for the example query, "rows" should be "4".