diff options
| -rw-r--r-- | mysql-test/r/view.result | 28 | ||||
| -rw-r--r-- | mysql-test/t/view.test | 22 | ||||
| -rw-r--r-- | sql/sql_select.cc | 52 | ||||
| -rw-r--r-- | sql/table.h | 2 |
4 files changed, 104 insertions, 0 deletions
diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index 03ed22115bc..3fe42a0ff80 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -3570,6 +3570,34 @@ SELECT * FROM v1 IGNORE KEY(non_existant); ERROR HY000: Incorrect usage of index hints and VIEW DROP VIEW v1; DROP TABLE t1; +CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, +PRIMARY KEY(a), KEY (b)); +INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a; +SHOW CREATE VIEW v1; +View Create View +v1 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` FORCE INDEX (PRIMARY,`b`) order by `t1`.`a` +EXPLAIN SELECT * FROM v1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL PRIMARY 4 NULL 15 +CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a; +SHOW CREATE VIEW v2; +View Create View +v2 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v2` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` USE INDEX () order by `t1`.`a` +EXPLAIN SELECT * FROM v2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort +CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a; +SHOW CREATE VIEW v3; +View Create View +v3 CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v3` AS select `t1`.`a` AS `a`,`t1`.`b` AS `b` from `t1` IGNORE INDEX (`b`) order by `t1`.`a` +EXPLAIN SELECT * FROM v3; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 15 Using filesort +DROP VIEW v1; +DROP VIEW v2; +DROP VIEW v3; +DROP TABLE t1; End of 5.0 tests. DROP DATABASE IF EXISTS `d-1`; CREATE DATABASE `d-1`; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 341a17eb291..2d68dc3fec7 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -3433,6 +3433,28 @@ SELECT * FROM v1 IGNORE KEY(non_existant); DROP VIEW v1; DROP TABLE t1; +# +# Bug #28702: VIEWs defined with USE/FORCE KEY ignore that request +# +CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT, b INT NOT NULL DEFAULT 0, + PRIMARY KEY(a), KEY (b)); +INSERT INTO t1 VALUES (),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +CREATE VIEW v1 AS SELECT * FROM t1 FORCE KEY (PRIMARY,b) ORDER BY a; +SHOW CREATE VIEW v1; +EXPLAIN SELECT * FROM v1; +CREATE VIEW v2 AS SELECT * FROM t1 USE KEY () ORDER BY a; +SHOW CREATE VIEW v2; +EXPLAIN SELECT * FROM v2; +CREATE VIEW v3 AS SELECT * FROM t1 IGNORE KEY (b) ORDER BY a; +SHOW CREATE VIEW v3; +EXPLAIN SELECT * FROM v3; + +DROP VIEW v1; +DROP VIEW v2; +DROP VIEW v3; +DROP TABLE t1; + + --echo End of 5.0 tests. # diff --git a/sql/sql_select.cc b/sql/sql_select.cc index b77bb719e1e..83eb597041a 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -16043,6 +16043,47 @@ static void print_join(THD *thd, String *str, List<TABLE_LIST> *tables) } +/** + @brief Print an index hint for a table + + @details Prints out the USE|FORCE|IGNORE index hints for a table. + + @param thd the current thread + @param[out] str appends the index hint here + @param hint what the hint is (as string : "USE INDEX"| + "FORCE INDEX"|"IGNORE INDEX") + @param hint_length the length of the string in 'hint' + @param indexes a list of index names for the hint +*/ + +void +TABLE_LIST::print_index_hint(THD *thd, String *str, + const char *hint, uint32 hint_length, + List<String> indexes) +{ + List_iterator_fast<String> li(indexes); + String *idx; + bool first= 1; + + str->append (' '); + str->append (hint, hint_length); + str->append (STRING_WITH_LEN(" (")); + while ((idx = li++)) + { + if (first) + first= 0; + else + str->append(','); + if (!my_strcasecmp (system_charset_info, idx->c_ptr_safe(), + primary_key_name)) + str->append(primary_key_name); + else + append_identifier (thd, str, idx->ptr(), idx->length()); + } + str->append(')'); +} + + /* Print table as it should be in join list @@ -16110,6 +16151,17 @@ void TABLE_LIST::print(THD *thd, String *str) str->append(' '); append_identifier(thd, str, alias, strlen(alias)); } + + if (use_index) + { + if (force_index) + print_index_hint(thd, str, STRING_WITH_LEN("FORCE INDEX"), *use_index); + else + print_index_hint(thd, str, STRING_WITH_LEN("USE INDEX"), *use_index); + } + if (ignore_index) + print_index_hint (thd, str, STRING_WITH_LEN("IGNORE INDEX"), *ignore_index); + } } diff --git a/sql/table.h b/sql/table.h index 6554b6ed578..89d92b1be13 100644 --- a/sql/table.h +++ b/sql/table.h @@ -1161,6 +1161,8 @@ struct TABLE_LIST private: bool prep_check_option(THD *thd, uint8 check_opt_type); bool prep_where(THD *thd, Item **conds, bool no_where_clause); + void print_index_hint(THD *thd, String *str, const char *hint, + uint32 hint_length, List<String>); /* Cleanup for re-execution in a prepared statement or a stored procedure. |