Below is the list of changes that have just been committed into a local
5.1 repository of mhansson. When mhansson 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, 2008-04-24 12:06:13+02:00, mhansson@riffraff.(none) +3 -0
Bug#34921: comparisons with Information schema tables
don't honor collation
When searching for tables using information_schema tables,
the optimization to answer the query by only opening FRM
files failed because a lookup key was created and the file
system will generally disregard collations when using this
key.
Fixed by not creating the lookup key if the bespoke
optimization is used.
mysql-test/r/information_schema.result@stripped, 2008-04-24 12:06:10+02:00, mhansson@riffraff.(none) +81 -3
Bug#34921: Changed test result, test result.
mysql-test/t/information_schema.test@stripped, 2008-04-24 12:06:10+02:00, mhansson@riffraff.(none) +77 -0
Bug#34921: Test case.
sql/sql_show.cc@stripped, 2008-04-24 12:06:10+02:00, mhansson@riffraff.(none) +14 -4
Bug#34921: Removed unused parameter from
get_table_open_method and call it from get_lookup_value
diff -Nrup a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result
--- a/mysql-test/r/information_schema.result 2008-03-27 12:54:42 +01:00
+++ b/mysql-test/r/information_schema.result 2008-04-24 12:06:10 +02:00
@@ -1518,7 +1518,7 @@ SET GLOBAL event_scheduler=0;
explain select table_name from information_schema.views where
table_schema='test' and table_name='v1';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE views ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Open_frm_only; Scanned 0 databases
+1 SIMPLE views ALL NULL NULL NULL NULL NULL Using where; Open_frm_only; Scanned all databases
explain select * from information_schema.tables;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE tables ALL NULL NULL NULL NULL NULL Open_full_table; Scanned all databases
@@ -1528,11 +1528,11 @@ id select_type table type possible_keys
explain select * from information_schema.tables where
table_schema='test' and table_name= 't1';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tables ALL NULL TABLE_SCHEMA,TABLE_NAME NULL NULL NULL Using where; Open_full_table; Scanned 0 databases
+1 SIMPLE tables ALL NULL NULL NULL NULL NULL Using where; Open_full_table; Scanned all databases
explain select table_name, table_type from information_schema.tables
where table_schema='test';
id select_type table type possible_keys key key_len ref rows Extra
-1 SIMPLE tables ALL NULL TABLE_SCHEMA NULL NULL NULL Using where; Open_frm_only; Scanned 1 database
+1 SIMPLE tables ALL NULL NULL NULL NULL NULL Using where; Open_frm_only; Scanned all databases
explain select b.table_name
from information_schema.tables a, information_schema.columns b
where a.table_name='t1' and a.table_schema='test' and b.table_name=a.table_name;
@@ -1646,4 +1646,82 @@ drop table t1;
drop function f1;
select * from information_schema.tables where 1=sleep(100000);
select * from information_schema.columns where 1=sleep(100000);
+SET collation_connection = 'latin1_general_cs';
+CREATE TABLE t1( a INT );
+CREATE TABLE T2( a INT );
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 't1';
+table_name
+t1
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 'T1';
+table_name
+t1
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name COLLATE utf8_general_cs = 'T1';
+table_name
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name COLLATE utf8_general_cs = 't1';
+table_name
+t1
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 'T2';
+table_name
+T2
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 't2';
+table_name
+T2
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name COLLATE utf8_general_cs = 't2';
+table_name
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name COLLATE utf8_general_cs = 'T2';
+table_name
+T2
+SET collation_connection = 'utf8_general_ci';
+CREATE TABLE t3( a INT );
+CREATE TABLE T4( a INT );
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 't3';
+table_name
+t3
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 'T3';
+table_name
+t3
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 'T4';
+table_name
+T4
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 'T4';
+table_name
+T4
+DROP TABLE t1, T2, t3, T4;
+SET collation_connection = DEFAULT;
End of 5.1 tests.
diff -Nrup a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test
--- a/mysql-test/t/information_schema.test 2008-03-27 12:54:42 +01:00
+++ b/mysql-test/t/information_schema.test 2008-04-24 12:06:10 +02:00
@@ -1338,4 +1338,81 @@ where state='User sleep' and
info='select * from information_schema.columns where 1=sleep(100000)';
--source include/wait_condition.inc
+#
+# Bug#34921: comparisons with Information schema tables don't honor collation
+#
+SET collation_connection = 'latin1_general_cs';
+
+CREATE TABLE t1( a INT );
+CREATE TABLE T2( a INT );
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 't1';
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 'T1';
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name COLLATE utf8_general_cs = 'T1';
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name COLLATE utf8_general_cs = 't1';
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 'T2';
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 't2';
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name COLLATE utf8_general_cs = 't2';
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name COLLATE utf8_general_cs = 'T2';
+
+SET collation_connection = 'utf8_general_ci';
+
+CREATE TABLE t3( a INT );
+CREATE TABLE T4( a INT );
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 't3';
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 'T3';
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 'T4';
+
+SELECT table_name
+FROM information_schema.columns
+WHERE table_schema = schema()
+AND table_name = 'T4';
+
+DROP TABLE t1, T2, t3, T4;
+
+SET collation_connection = DEFAULT;
+
--echo End of 5.1 tests.
diff -Nrup a/sql/sql_show.cc b/sql/sql_show.cc
--- a/sql/sql_show.cc 2008-03-28 11:03:04 +01:00
+++ b/sql/sql_show.cc 2008-04-24 12:06:10 +02:00
@@ -81,6 +81,8 @@ static void store_key_options(THD *thd,
static void
append_algorithm(TABLE_LIST *table, String *buff);
+static uint get_table_open_method(TABLE_LIST *tables,
+ ST_SCHEMA_TABLE *schema_table);
/***************************************************************************
** List all table types supported
@@ -2276,6 +2278,13 @@ int make_table_list(THD *thd, SELECT_LEX
the part of 'WHERE' condition if it's possible and
fill appropriate lookup_field_vals struct field
with this value.
+
+ The function is used for information_schema lookup and is heavily
+ tailor-made for this purpose. For instance, the function will
+ recognize if the optimization 'open FRM only' is used and will in
+ this case refrain from creating a lookup value if the used collation
+ is case-insensitive since the file system may be case-sensitive and
+ hence will not find certain values.
@param[in] thd thread handler
@param[in] item_func part of WHERE condition
@@ -2307,6 +2316,9 @@ bool get_lookup_value(THD *thd, Item_fun
Item_field *item_field;
CHARSET_INFO *cs= system_charset_info;
+ if (get_table_open_method(table, schema_table) & OPEN_FRM_ONLY)
+ return 0;
+
if (item_func->arguments()[0]->type() == Item::FIELD_ITEM &&
item_func->arguments()[1]->const_item())
{
@@ -2952,15 +2964,13 @@ static int fill_schema_table_names(THD *
OPEN_FULL_TABLE - open FRM, data, index files
@param[in] tables I_S table table_list
@param[in] schema_table I_S table struct
- @param[in] schema_table_idx I_S table index
@return return a set of flags
@retval SKIP_OPEN_TABLE | OPEN_FRM_ONLY | OPEN_FULL_TABLE
*/
static uint get_table_open_method(TABLE_LIST *tables,
- ST_SCHEMA_TABLE *schema_table,
- enum enum_schema_tables schema_table_idx)
+ ST_SCHEMA_TABLE *schema_table)
{
/*
determine which method will be used for table opening
@@ -3180,7 +3190,7 @@ int get_all_tables(THD *thd, TABLE_LIST
partial_cond= make_cond_for_info_schema(cond, tables);
tables->table_open_method= table_open_method=
- get_table_open_method(tables, schema_table, schema_table_idx);
+ get_table_open_method(tables, schema_table);
if (lex->describe)
{
| Thread |
|---|
| • bk commit into 5.1 tree (mhansson:1.2562) BUG#34921 | mhansson | 24 Apr |
