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 10:39]
Sveta Smirnova
[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 =))))