Below is the list of changes that have just been committed into a local
5.0 repository of kgeorge. When kgeorge does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2007-09-22 12:20:23+03:00, gkodinov@stripped +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.
mysql-test/r/view.result@stripped, 2007-09-22 12:20:22+03:00, gkodinov@stripped +28 -0
Bug #28702: test case
mysql-test/t/view.test@stripped, 2007-09-22 12:20:22+03:00, gkodinov@stripped +21 -0
Bug #28702: test case
sql/sql_select.cc@stripped, 2007-09-22 12:20:22+03:00, gkodinov@stripped +52 -0
Bug #28702: preserve index hints in a VIEW definition.
sql/table.h@stripped, 2007-09-22 12:20:22+03:00, gkodinov@stripped +2 -0
Bug #28702: preserve index hints in a VIEW definition.
# This is a BitKeeper patch. What follows are the unified diffs for the
# set of deltas contained in the patch. The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User: gkodinov
# Host: magare.gmz
# Root: /home/kgeorge/mysql/work/B28702-5.0-opt
--- 1.529/sql/sql_select.cc 2007-06-02 21:44:12 +03:00
+++ 1.530/sql/sql_select.cc 2007-09-22 12:20:22 +03:00
@@ -15366,6 +15366,47 @@ static void print_join(THD *thd, String
}
+/**
+ @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
+st_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
@@ -15433,6 +15474,17 @@ void st_table_list::print(THD *thd, Stri
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);
+
}
}
--- 1.144/sql/table.h 2007-06-01 00:14:03 +03:00
+++ 1.145/sql/table.h 2007-09-22 12:20:22 +03:00
@@ -755,6 +755,8 @@ typedef struct st_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.
--- 1.205/mysql-test/r/view.result 2007-06-01 00:40:46 +03:00
+++ 1.206/mysql-test/r/view.result 2007-09-22 12:20:22 +03:00
@@ -3455,4 +3455,32 @@ a1 c
2 0
DROP VIEW v1,v2;
DROP TABLE t1,t2,t3,t4;
+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.
--- 1.186/mysql-test/t/view.test 2007-06-01 00:40:47 +03:00
+++ 1.187/mysql-test/t/view.test 2007-09-22 12:20:22 +03:00
@@ -3320,4 +3320,25 @@ SELECT * FROM t1;
DROP VIEW v1,v2;
DROP TABLE t1,t2,t3,t4;
+#
+# 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.
| Thread |
|---|
| • bk commit into 5.0 tree (gkodinov:1.2517) BUG#28702 | kgeorge | 22 Sep |
