Bug #33461 | SELECT ... FROM <view> USE INDEX (...) throws an error | ||
---|---|---|---|
Submitted: | 21 Dec 2007 15:31 | Modified: | 8 Dec 2008 16:47 |
Reporter: | Axel Schwenke | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server: Views | Severity: | S3 (Non-critical) |
Version: | 5.0.52, 5.1.23 | OS: | Any |
Assigned to: | Gleb Shchepa | CPU Architecture: | Any |
[21 Dec 2007 15:31]
Axel Schwenke
[19 May 2008 11:58]
Bruno Boissard
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...
[13 Nov 2008 9:51]
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/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.
[28 Nov 2008 16:27]
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/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.
[1 Dec 2008 8:27]
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/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().
[2 Dec 2008 13:01]
Bugs System
Pushed into 5.0.74 (revid:gshchepa@mysql.com-20081201081402-sa6ctaqjeb52lvao) (version source revid:gshchepa@mysql.com-20081201081402-sa6ctaqjeb52lvao) (pib:5)
[3 Dec 2008 20:47]
Paul DuBois
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.
[8 Dec 2008 10:21]
Bugs System
Pushed into 5.1.31 (revid:gshchepa@mysql.com-20081201081402-sa6ctaqjeb52lvao) (version source revid:gshchepa@mysql.com-20081201081611-tiyawcsxmb3bwzbh) (pib:5)
[8 Dec 2008 11:32]
Bugs System
Pushed into 6.0.9-alpha (revid:gshchepa@mysql.com-20081201081402-sa6ctaqjeb52lvao) (version source revid:gshchepa@mysql.com-20081201081806-mbtgvvaz0iupv0h4) (pib:5)
[8 Dec 2008 16:47]
Paul DuBois
Noted in 5.1.31, 6.0.9 changelogs.
[19 Jan 2009 11:25]
Bugs System
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)
[19 Jan 2009 13:03]
Bugs System
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)
[19 Jan 2009 16:09]
Bugs System
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)