MySQL Bugs: #89805: Optionally avoid hard errors on IGNORE INDEX or FORCE INDEX usage

Bug #89805	Optionally avoid hard errors on IGNORE INDEX or FORCE INDEX usage
Submitted:	26 Feb 2018 13:45	Modified:	26 Feb 2018 16:44
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S4 (Feature request)
Version:	5.7+	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	force index, ignore index, Optimizer

Description:
MySQL allows you to tell the optimiser to override the behaviour it wants to use.
This can be done using the syntax FORCE INDEX ... , and IGNORE INDEX ...

Problem:
* if the index mentioned no longer exists the query will fail.
* If you replace a table by a view the query will fail as the syntax when using a view doesn't make sense and currently FORCE/IGNORE INDEX on a view makes no sense.

The DBAs may change the underlying "database structure" in a way which the devs may not be aware of. This may be required for various reasons and the behaviour described above while "correct" can be painful.

How to repeat:
See above.

Suggested fix:
Make it possible to convert these errors into warnings.
* That would allow things to work even under the situations described which is better than getting errors.
* The warnings should be enough to alert the developers that something is wrong and so they can poke the DBAs and then probably adjust their code.
* Default behaviour should probably be unchanged from current behaviour. (The new option just provides more flexibility if needed).

Verified ERROR 1176 (42000) gets thrown while querying with force/ignore indexes on tables without such indexes and also while querying views on an underlying table with or without those indexes.

--
Force index testing:

CREATE table `test_table` (
  `a` int(5) NOT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `removed_idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into test_table values (1);

insert into test_table values (2);

select * from test_table force index (`removed_idx`);
+---+
| a |
+---+
| 1 |
| 2 |
+---+

explain select * from test_table force index (`removed_idx`);
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_table | NULL       | index | NULL          | removed_idx   | 4       | NULL |    2 |   100.00 | Using index |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-------------+

alter table test_table drop index removed_idx;

select * from test_table force index (`removed_idx`);
ERROR 1176 (42000): Key 'removed_idx' doesn't exist in table 'test_table'

mysql> explain select * from test_table force index (`removed_idx`);
ERROR 1176 (42000): Key 'removed_idx' doesn't exist in table 'test_table'

---
Ignore index testing:

mysql> select * from test_table ignore index (primary);
+---+
| a |
+---+
| 1 |
| 2 |
+---+

alter table test_table drop primary key;
 
mysql> select * from test_table ignore index (primary);
ERROR 1176 (42000): Key 'PRIMARY' doesn't exist in table 'test_table'

---
View testing:

CREATE table `view_test_table` (
  `a` int(5) NOT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `removed_idx` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

insert into view_test_table values (1),(2);

create view test_view  as (select * from view_test_table);

mysql> select * from test_view force index (`removed_idx`);
ERROR 1176 (42000): Key 'removed_idx' doesn't exist in table 'test_view'

mysql> select * from test_view force index (primary);
ERROR 1176 (42000): Key 'PRIMARY' doesn't exist in table 'test_view'

alter table view_test_table drop index removed_idx;
 
mysql> select * from test_view force index (`removed_idx`);
ERROR 1176 (42000): Key 'removed_idx' doesn't exist in table 'test_view'

create view test_view_primary as (select * from view_test_table force index (primary));
 
mysql> select * from test_view_primary force index (primary);
ERROR 1176 (42000): Key 'PRIMARY' doesn't exist in table 'test_view_primary'

Perhaps in this scenario, the view itself should contain the force/ignore clauses, however, this will still require modifications of the app queries to remove the force/ignore clauses.

Just a brief comment from where I stand, as I was the one encountering the issue while working on something: 

For the case in which a query with a hint is issued against a view, what I would like to see is that the hint is propagated to the underlying table(s) and applied if it can be resolved to an index on one of them. Otherwise, it raises the warning.