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:
None 
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
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".
[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 =))))