Bug #82960 Removing an invisible index breaks explain

• Submitted: 12 Sep 2016 19:29
• Modified: 4 Oct 2016 15:48
• Reporter: Daniël van Eeden (OCA)
• Status: Closed
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.0.0
• OS: Any
• CPU Architecture: Any
• Tags: INDEX, invisible

[12 Sep 2016 19:29] Daniël van Eeden

Description:
mysql> explain select * from t1 force index(r1) where r1='"$[0]"';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table t1 drop index r1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 force index(r1) where r1='"$[0]"';
ERROR 1176 (42000): Key 'r1' doesn't exist in table 't1'

So the index is not really invisible, it's just not used. It should give an error (or at least a warning) when a invisible index is used in a FORCE INDEX option.

How to repeat:
See description

[13 Sep 2016 13:58] MySQL Verification Team

Hello Daniël, 

Thank you for the report. 

Thanks, 
Umesh

[13 Sep 2016 13:59] MySQL Verification Team

--

 mysql> use test
Database changed
mysql> CREATE TABLE t1 (
    ->   i INT,
    ->   j INT,
    ->   k INT,
    ->   INDEX i_idx (i) INVISIBLE
    -> ) ENGINE = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE INDEX j_idx ON t1 (j) INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>
mysql> explain select * from t1 force index(i_idx) where j='"$[0]"';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table t1 drop index i_idx;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t1 force index(i_idx) where j='"$[0]"';
ERROR 1176 (42000): Key 'i_idx' doesn't exist in table 't1'
mysql>  SELECT INDEX_NAME, IS_VISIBLE
    ->  FROM INFORMATION_SCHEMA.STATISTICS
    ->  WHERE TABLE_SCHEMA = 'test' AND TABLE_NAME = 't1';
+------------+------------+
| INDEX_NAME | IS_VISIBLE |
+------------+------------+
| j_idx      | NO         |
| k_idx      | NO         |
+------------+------------+
2 rows in set (0.00 sec)

[4 Oct 2016 15:48] Paul DuBois

Posted by developer:
 
Noted in 8.0.1.

Index hints applied to invisible indexes produced no error.