MySQL Bugs: #28701: SELECTs from VIEWs completely ignore USE/FORCE KEY, allowing invalid statements

Bug #28701	SELECTs from VIEWs completely ignore USE/FORCE KEY, allowing invalid statements
Submitted:	27 May 2007 2:30	Modified:	5 Nov 2007 2:43
Reporter:	Björn Steinbrink	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Views	Severity:	S3 (Non-critical)
Version:	5.0.41, 5.1	OS:	Any
Assigned to:	Georgi Kodinov	CPU Architecture:	Any

Description:
When a SELECT query uses a view and there's a USE/FORCE KEY part in the query, this is completely ignored without any error or warning, even if the given key name is bogus.

How to repeat:
mysql> CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT, val INT NOT NULL, PRIMARY KEY(id)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),();
Query OK, 15 rows affected, 1 warning (0.01 sec)
Records: 15  Duplicates: 0  Warnings: 0

mysql> CREATE VIEW v1 AS SELECT * FROM t1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM v1 USE KEY(bfsd);
+----+-----+
| id | val |
+----+-----+
|  1 |   0 | 
|  2 |   0 | 
|  3 |   0 | 
|  4 |   0 | 
|  5 |   0 | 
|  6 |   0 | 
|  7 |   0 | 
|  8 |   0 | 
|  9 |   0 | 
| 10 |   0 | 
| 11 |   0 | 
| 12 |   0 | 
| 13 |   0 | 
| 14 |   0 | 
| 15 |   0 | 
+----+-----+
15 rows in set (0.00 sec)

I just noticed that this only happens with the MERGE algorithm, when using the TEMPTABLE algorithm, MySQL correctly complains that there is no such index (as the temporary table has no indexes).

Thank you for the report.

Verified as described. All versions are affected.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28018

ChangeSet@1.2506, 2007-06-04 10:18:52+03:00, gkodinov@magare.gmz +4 -0
  Bug #28701:
  Views don't have indexes. So they can't take index hints.
  Added a check and disabled the usage of hints for views.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/28216

ChangeSet@1.2506, 2007-06-06 17:54:14+03:00, gkodinov@macbook.gmz +3 -0
  Bug #28701:
  Views don't have indexes. So they can't take index hints.
  Added a check and disabled the usage of hints for views.

Pushed into 5.0.52

Pushed into 5.1.23-beta

Pushed into 6.0.4-alpha

Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

Views do not have indexes, so index hints do not apply. Use of index
hints when selecting from a view is now disallowed.