Bug #33461 SELECT ... FROM <view> USE INDEX (...) throws an error

Description:
The use of clauses USE INDEX () or FORCE INDEX() when selecting from views leads to error messages

ERROR 1221 (HY000): Incorrect usage of USE INDEX and VIEW
ERROR 1221 (HY000): Incorrect usage of FORCE INDEX and VIEW

There were no such error messages with 5.0.45. There is no changelog entry for this new behaviour.

The same regression can be seen in 5.1.22 vs. 5.1.23. The changelog for 5.1.23 lists this change as fix for bug #28701.

How to repeat:
drop table if exists t1;
create table t1 (c1 int primary key, c2 int, index (c2));
insert into t1 values (1,1), (2,2), (3,3);
select * from t1 use index (PRIMARY) where c1=2;
select * from t1 use index (c2) where c2=2;

drop view if exists v1;
create view v1 as select c1, c2 from t1;
select * from v1 use index (PRIMARY) where c1=2;
select * from v1 force index (PRIMARY) where c1=2;
select * from v1 use index (c2) where c2=2;
select * from v1 force index (c2) where c2=2;

Suggested fix:
This change makes views not looking like base tables, even for SELECT statements. If there is a valid USE|FORCE INDEX() clause that does not make sense for selecting from a view, it should be silently discarded.

It seems that this error message come from the fix of this "bug" :
http://bugs.mysql.com/bug.php?id=28701

[quote=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.
[/quote]

Maybe you can put FORCE INDEX when you create the view. But in my case, it is not very effective...

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/58614

2712 Gleb Shchepa	2008-11-13
      Bug #33461: SELECT ... FROM <view> USE INDEX (...) throws 
                  an error
      
      Even after the fix for bug 28701 visible behaviors of 
      SELECT FROM a view and SELECT FROM a regular table are 
      little bit different:
      
      1. "SELECT FROM regular table USE/FORCE/IGNORE(non 
         existent index)" fails with a "ERROR 1176 (HY000): 
         Key '...' doesn't exist in table '...'"
      
      2. "SELECT FROM view USING/FORCE/IGNORE(any index)" fails 
         with a "ERROR 1221 (HY000): Incorrect usage of 
         USE/IGNORE INDEX and VIEW".  OTOH "SHOW INDEX FROM
         view" always returns empty result set, so from the point
         of same behaviour view we trying to use/ignore non 
         existent index.
      
      To harmonize the behaviour of USE/FORCE/IGNORE(index) 
      clauses in SELECT from a view and from a regular table the 
      "ERROR 1221 (HY000): Incorrect usage of USE/IGNORE INDEX 
      and VIEW" message has been replaced with the "ERROR 1176 
      (HY000): Key '...' doesn't exist in table '...'" message 
      like for tables and non existent keys.

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/60202

2732 Gleb Shchepa	2008-11-28
      Bug #33461: SELECT ... FROM <view> USE INDEX (...) throws
                  an error
      
      Even after the fix for bug 28701 visible behaviors of
      SELECT FROM a view and SELECT FROM a regular table are
      little bit different:
      
      1. "SELECT FROM regular table USE/FORCE/IGNORE(non
         existent index)" fails with a "ERROR 1176 (HY000):
         Key '...' doesn't exist in table '...'"
      
      2. "SELECT FROM view USING/FORCE/IGNORE(any index)" fails
         with a "ERROR 1221 (HY000): Incorrect usage of
         USE/IGNORE INDEX and VIEW".  OTOH "SHOW INDEX FROM
         view" always returns empty result set, so from the point
         of same behaviour view we trying to use/ignore non
         existent index.
      
      To harmonize the behaviour of USE/FORCE/IGNORE(index)
      clauses in SELECT from a view and from a regular table the
      "ERROR 1221 (HY000): Incorrect usage of USE/IGNORE INDEX
      and VIEW" message has been replaced with the "ERROR 1176
      (HY000): Key '...' doesn't exist in table '...'" message
      like for tables and non existent keys.

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/60255

2727 Gleb Shchepa	2008-12-01
      After-push commit for bug #33461 to make valgrind happy:
      TABLE_LIST doesn't free Strings in its string lists
      (TABLE_LIST::use_index and TABLE_liST::ignore_index), so
      calling c_ptr_safe() on that Strings leads to memleaks.
      OTOH "safe" c_ptr_safe() is not necessary there and we can
      replace it with c_ptr().

Pushed into 5.0.74  (revid:gshchepa@mysql.com-20081201081402-sa6ctaqjeb52lvao) (version source revid:gshchepa@mysql.com-20081201081402-sa6ctaqjeb52lvao) (pib:5)

Noted in 5.0.74 changelog.

Previously, use of index hints with views (which do not have indexes)
produced the error "ERROR 1221 (HY000): Incorrect usage of USE/IGNORE
INDEX and VIEW". Now this produces "ERROR 1176 (HY000): Key '...'
doesn't exist in table '...'", the same error as for base tables 
without an appropriate index.

Resetting report to NDI pending push into 5.1.x, 6.0.x.

Pushed into 5.1.31  (revid:gshchepa@mysql.com-20081201081402-sa6ctaqjeb52lvao) (version source revid:gshchepa@mysql.com-20081201081611-tiyawcsxmb3bwzbh) (pib:5)

Pushed into 6.0.9-alpha  (revid:gshchepa@mysql.com-20081201081402-sa6ctaqjeb52lvao) (version source revid:gshchepa@mysql.com-20081201081806-mbtgvvaz0iupv0h4) (pib:5)

Noted in 5.1.31, 6.0.9 changelogs.

Pushed into 5.1.31-ndb-6.2.17 (revid:tomas.ulin@sun.com-20090119095303-uwwvxiibtr38djii) (version source revid:tomas.ulin@sun.com-20090108105244-8opp3i85jw0uj5ib) (merge vers: 5.1.31-ndb-6.2.17) (pib:6)

Pushed into 5.1.31-ndb-6.3.21 (revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (version source revid:tomas.ulin@sun.com-20090119104956-guxz190n2kh31fxl) (merge vers: 5.1.31-ndb-6.3.21) (pib:6)

Pushed into 5.1.31-ndb-6.4.1 (revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (version source revid:tomas.ulin@sun.com-20090119144033-4aylstx5czzz88i5) (merge vers: 5.1.31-ndb-6.4.1) (pib:6)