Bug #28702 VIEWs defined with USE/FORCE KEY ignore that request
Submitted: 27 May 2007 2:38 Modified: 5 Nov 2007 2:48
Reporter: Björn Steinbrink Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Views Severity:S3 (Non-critical)
Version:5.0.44-BK, 5.0.41 OS:Linux (Debian Unstable)
Assigned to: Georgi Kodinov CPU Architecture:Any

[27 May 2007 2:38] Björn Steinbrink
Description:
When a VIEW is defined using USE/FORCE KEY, that part of the definition is ignored when a SELECT is issued on the VIEW.

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.03 sec)

mysql> CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY) ORDER BY id;
Query OK, 0 rows affected (0.00 sec)

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

mysql> EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY) ORDER BY id;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | t1    | index | NULL          | PRIMARY | 4       | NULL |   15 |       | 
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM v1;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   15 | Using filesort | 
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)
[29 May 2007 5:45] Valeriy Kravchuk
Thank you for a bug report. Verified just as described with latest 5.0.44-BK.
[7 Jun 2007 11:47] 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/28286

ChangeSet@1.2517, 2007-06-07 14:47:02+03:00, gkodinov@magare.gmz +4 -0
  Bug #28702: VIEWs defined with USE/FORCE KEY ignore that request
   When storing the VIEW the CREATE VIEW command is reconstructed 
   from the parse tree. While constructing the command string
   the index hints specified should also be printed.
   Fixed by adding code to print the index hints when printing a 
   table in the FROM clause.
[22 Sep 2007 9:20] 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/34480

ChangeSet@1.2517, 2007-09-22 12:20:23+03:00, gkodinov@magare.gmz +4 -0
  Bug #28702: VIEWs defined with USE/FORCE KEY ignore that request
   When storing the VIEW the CREATE VIEW command is reconstructed 
   from the parse tree. While constructing the command string
   the index hints specified should also be printed.
   Fixed by adding code to print the index hints when printing a 
   table in the FROM clause.
[29 Oct 2007 8:43] Bugs System
Pushed into 5.0.52
[29 Oct 2007 8:47] Bugs System
Pushed into 5.1.23-beta
[29 Oct 2007 8:51] Bugs System
Pushed into 6.0.4-alpha
[5 Nov 2007 2:48] Paul Dubois
Noted in 5.0.52, 5.1.23, 6.0.4 changelogs.

Index hints specified in view definitions were ignored when using the
view to select from the base table.