List:Commits« Previous MessageNext Message »
From:mhansson Date:April 24 2008 10:07am
Subject:bk commit into 5.1 tree (mhansson:1.2562) BUG#34921
View as plain text  
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#34921mhansson24 Apr