Bug #100024 optimizer switch use_invisible_indexes=on does not allow FORCE INDEX

• Submitted: 28 Jun 2020 16:57
• Modified: 11 Aug 2020 20:22
• Reporter: Morgan Tocker
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 8.0.20
• OS: Any
• CPU Architecture: Any

[28 Jun 2020 16:57] Morgan Tocker

Description:
The optimizer switch of use_invisible_indexes=on does not permit the index to be used in all contexts. It is not a major issue, just unexpected behavior.

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (
 id INT NOT NULL PRIMARY KEY auto_increment,
 b INT NOT NULL,
 INDEX (b) INVISIBLE
);

INSERT INTO t1 VALUES (1, 1), (2,2),(3,3),(4,4),(5,5);
SELECT SLEEP(1);
SET optimizer_switch="use_invisible_indexes=on";
EXPLAIN SELECT * FROM t1 WHERE b = 3;
EXPLAIN SELECT * FROM t1 FORCE INDEX (b) WHERE b = 3;

Last two statements:

mysql [localhost:8020] {msandbox} (test) > EXPLAIN SELECT * FROM t1 WHERE b = 3;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | b             | b    | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql [localhost:8020] {msandbox} (test) > EXPLAIN SELECT * FROM t1 FORCE INDEX (b) WHERE b = 3;
ERROR 1176 (42000): Key 'b' doesn't exist in table 't1'

[29 Jun 2020 5:27] MySQL Verification Team

Hello Morgan,

Thank you for the report and feedback.

regards,
Umesh

[11 Aug 2020 20:22] Jon Stephens

Documented fix as follows in the MySQL 8.0.22 changelog:

    In some cases, the server issued an error when an invisible
    index was used in an index hint even when the
    use_invisible_indexes optimizer switch was not set to OFF.

Closed.