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.
modified:
mysql-test/r/view.result
mysql-test/t/view.test
sql/sql_view.cc
2731 Georgi Kodinov 2008-11-28 [merge]
merged bug 37339 to 5.0-bugteam
added:
mysql-test/r/ctype_filesystem.result
mysql-test/t/ctype_filesystem-master.opt
mysql-test/t/ctype_filesystem.test
modified:
sql/mysqld.cc
sql/set_var.cc
sql/set_var.h
sql/sql_show.cc
=== modified file 'mysql-test/r/view.result'
--- a/mysql-test/r/view.result 2008-10-27 10:22:38 +0000
+++ b/mysql-test/r/view.result 2008-11-28 16:13:12 +0000
@@ -625,7 +625,7 @@ drop table t1;
create table t1 (a int, b int);
create view v1 as select a, sum(b) from t1 group by a;
select b from v1 use index (some_index) where b=1;
-ERROR HY000: Incorrect usage of USE INDEX and VIEW
+ERROR HY000: Key 'some_index' doesn't exist in table 'v1'
drop view v1;
drop table t1;
create table t1 (col1 char(5),col2 char(5));
@@ -3567,11 +3567,11 @@ CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
CREATE VIEW v1 AS SELECT * FROM t1;
SELECT * FROM v1 USE KEY(non_existant);
-ERROR HY000: Incorrect usage of USE INDEX and VIEW
+ERROR HY000: Key 'non_existant' doesn't exist in table 'v1'
SELECT * FROM v1 FORCE KEY(non_existant);
-ERROR HY000: Incorrect usage of FORCE INDEX and VIEW
+ERROR HY000: Key 'non_existant' doesn't exist in table 'v1'
SELECT * FROM v1 IGNORE KEY(non_existant);
-ERROR HY000: Incorrect usage of IGNORE INDEX and VIEW
+ERROR HY000: Key 'non_existant' doesn't exist in table 'v1'
DROP VIEW v1;
DROP TABLE t1;
CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0,
@@ -3679,6 +3679,31 @@ DROP VIEW v1;
CREATE VIEW v1 AS SELECT 1;
DROP VIEW v1;
+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;
+c1 c2
+2 2
+SELECT * FROM t1 USE INDEX (c2) WHERE c2=2;
+c1 c2
+2 2
+CREATE VIEW v1 AS SELECT c1, c2 FROM t1;
+SHOW INDEX FROM v1;
+Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
+SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2;
+ERROR HY000: Key 'PRIMARY' doesn't exist in table 'v1'
+SELECT * FROM v1 USE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2;
+ERROR HY000: Key 'c2' doesn't exist in table 'v1'
+DROP VIEW v1;
+DROP TABLE t1;
# -----------------------------------------------------------------
# -- End of 5.0 tests.
# -----------------------------------------------------------------
=== modified file 'mysql-test/t/view.test'
--- a/mysql-test/t/view.test 2008-10-27 10:22:38 +0000
+++ b/mysql-test/t/view.test 2008-11-28 16:13:12 +0000
@@ -510,7 +510,7 @@ drop table t1;
#
create table t1 (a int, b int);
create view v1 as select a, sum(b) from t1 group by a;
---error ER_WRONG_USAGE
+--error ER_KEY_DOES_NOT_EXITS
select b from v1 use index (some_index) where b=1;
drop view v1;
drop table t1;
@@ -3424,11 +3424,11 @@ drop table t1;
CREATE TABLE t1 (a INT);
INSERT INTO t1 VALUES (1),(2);
CREATE VIEW v1 AS SELECT * FROM t1;
---error ER_WRONG_USAGE
+--error ER_KEY_DOES_NOT_EXITS
SELECT * FROM v1 USE KEY(non_existant);
---error ER_WRONG_USAGE
+--error ER_KEY_DOES_NOT_EXITS
SELECT * FROM v1 FORCE KEY(non_existant);
---error ER_WRONG_USAGE
+--error ER_KEY_DOES_NOT_EXITS
SELECT * FROM v1 IGNORE KEY(non_existant);
DROP VIEW v1;
@@ -3568,6 +3568,32 @@ DROP VIEW v1;
CREATE VIEW v1 AS SELECT 1;
DROP VIEW v1;
+#
+# Bug #33461: SELECT ... FROM <view> USE INDEX (...) throws an error
+#
+
+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;
+
+CREATE VIEW v1 AS SELECT c1, c2 FROM t1;
+SHOW INDEX FROM v1;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 USE INDEX (PRIMARY) WHERE c1=2;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 FORCE INDEX (PRIMARY) WHERE c1=2;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 IGNORE INDEX (PRIMARY) WHERE c1=2;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 USE INDEX (c2) WHERE c2=2;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 FORCE INDEX (c2) WHERE c2=2;
+--error ER_KEY_DOES_NOT_EXITS
+SELECT * FROM v1 IGNORE INDEX (c2) WHERE c2=2;
+
+DROP VIEW v1;
+DROP TABLE t1;
--echo # -----------------------------------------------------------------
--echo # -- End of 5.0 tests.
=== modified file 'sql/sql_view.cc'
--- a/sql/sql_view.cc 2008-11-14 17:25:57 +0000
+++ b/sql/sql_view.cc 2008-11-28 16:13:12 +0000
@@ -980,13 +980,14 @@ bool mysql_make_view(THD *thd, File_pars
DBUG_RETURN(0);
}
- if (table->use_index || table->ignore_index)
- {
- my_error(ER_WRONG_USAGE, MYF(0),
- table->ignore_index ? "IGNORE INDEX" :
- (table->force_index ? "FORCE INDEX" : "USE INDEX"),
- "VIEW");
- DBUG_RETURN(TRUE);
+ List<String> *index_list= table->use_index ? table->use_index
+ : table->ignore_index;
+ if (index_list)
+ {
+ DBUG_ASSERT(index_list->head()); // should never fail
+ my_error(ER_KEY_DOES_NOT_EXITS, MYF(0), index_list->head()->c_ptr_safe(),
+ table->table_name);
+ DBUG_RETURN(TRUE);
}
/* check loop via view definition */
| Thread |
|---|
| • bzr push into mysql-5.0-bugteam branch (gshchepa:2731 to 2732)Bug#33461 | Gleb Shchepa | 28 Nov |
