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

[27 May 2007 2:30] Björn Steinbrink
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)
[27 May 2007 2:40] Björn Steinbrink
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).
[28 May 2007 9:00] Sveta Smirnova
Thank you for the report.

Verified as described. All versions are affected.
[4 Jun 2007 7:19] Bugs System
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.
[6 Jun 2007 14:59] Bugs System
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.
[29 Oct 2007 8:43] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:46] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:50] Bugs System
Pushed into 6.0.4-alpha
[5 Nov 2007 2:43] Paul Dubois
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.