| Bug #116636 | Optimizer incorrectly ignores all indexes with NO_INDEX() in some cases | ||
|---|---|---|---|
| Submitted: | 13 Nov 5:25 | Modified: | 15 Nov 15:26 |
| Reporter: | Joshua Varner | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: Optimizer | Severity: | S2 (Serious) |
| Version: | 8.4.3, 8.0.40, 9.1.0 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[13 Nov 8:37]
MySQL Verification Team
Hello Joshua Varner, Thank you for the report and test case. Verified as described. regards, Umesh
[13 Nov 15:43]
Joshua Varner
I also noticed that setting an index as invisible does not reproduce this behavior. An invisible index is still considered valid, so it does not trigger this issue. That's also somewhat troubling, actually, because making an index invisible prior to dropping is a safety mechanism that's often used to ensure the safety of dropping an index. It seems to me that invisible indexes should not be considered in the list of indexes to compare against at this stage.
[13 Nov 15:46]
Joshua Varner
To say more explicitly: I think an invisible index should also yield this unresolved name warning and behave exactly as though it's an invalid index name altogether; it seems like the whole point of setting an index as invisible, to hide it from the optimizer in as many phases as possible so that you're confident that behavior will be equivalent to dropping the index.
I would expect the following SELECT statements to behave the same after setting idx_y to invisible:
mysql [localhost:8034] {msandbox} (test) > ALTER TABLE foo ALTER INDEX idx_y INVISIBLE;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [localhost:8034] {msandbox} (test) > EXPLAIN SELECT /*+ NO_INDEX(foo idx_y) */ x FROM foo WHERE x = '2';
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | foo | NULL | ref | idx_x | idx_x | 1023 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql [localhost:8034] {msandbox} (test) > EXPLAIN SELECT /*+ NO_INDEX(foo idx_doesnt_exist) */ x FROM foo WHERE x = '2';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | foo | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql [localhost:8034] {msandbox} (test) > show warnings;
+---------+------+------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------+
| Warning | 3128 | Unresolved name `foo`@`select#1` `idx_doesnt_exist` for NO_INDEX hint |
| Note | 1003 | /* select#1 */ select `test`.`foo`.`x` AS `x` from `test`.`foo` where (`test`.`foo`.`x` = '2') |
+---------+------+------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
[15 Nov 15:26]
Joshua Varner
Similar issues seem to exist with NO_GROUP_INDEX, NO_JOIN_INDEX, and NO_ORDER_INDEX as well. I believe the code is filtering out invalid indexes, and in the end, that leaves the set empty. That then causes the logic to use all indexes instead, essentially making all of them ignored.
Description: If you pass an invalid index name (for instance, an index that has since been dropped), or a list of invalid index names, to the NO_INDEX() optimizer hint, MySQL will incorrectly ignore all indexes on the table. The hint will work properly if at least one index is valid, but if none are valid, it fails. I marked the severity as serious, because this can suddenly cause failures after a schema change where an index is dropped, suddenly causing full table scans despite valid indexes still existing for the query. I tested this on 8.0.34, 8.0.40, and 8.4.3, and the same behavior exists on all versions tested. How to repeat: mysql [localhost:8403] {msandbox} (test) > SHOW CREATE TABLE foo \G *************************** 1. row *************************** Table: foo Create Table: CREATE TABLE `foo` ( `id` int NOT NULL AUTO_INCREMENT, `x` varchar(255) DEFAULT NULL, `y` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_x` (`x`), KEY `idx_y` (`y`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci 1 row in set (0.00 sec) mysql [localhost:8403] {msandbox} (test) > EXPLAIN SELECT x FROM foo WHERE x = '2'; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | foo | NULL | ref | idx_x | idx_x | 1023 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql [localhost:8403] {msandbox} (test) > EXPLAIN SELECT /*+ NO_INDEX(foo idx_y) */ x FROM foo WHERE x = '2'; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | foo | NULL | ref | idx_x | idx_x | 1023 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql [localhost:8403] {msandbox} (test) > EXPLAIN SELECT /*+ NO_INDEX(foo idx_y idx_doesnt_exist) */ x FROM foo WHERE x = '2'; +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | foo | NULL | ref | idx_x | idx_x | 1023 | const | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) mysql [localhost:8403] {msandbox} (test) > EXPLAIN SELECT /*+ NO_INDEX(foo idx_doesnt_exist) */ x FROM foo WHERE x = '2'; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | foo | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec)