bzr commit into mysql-5.5.0-next-mr-bugfixing branch (Sergey.Glukhov:2916) Bug#39270

[Sergey Glukhov]

#At file:///home/gluh/MySQL/mysql-next-mr-bugfixing/ based on revid:sergey.glukhov@stripped

 2916 Sergey Glukhov	2009-10-23
      Bug#39270 I_S optimization algorithm does not work properly in some cases
      backport to Betony
     @ mysql-test/r/information_schema.result
        Bug#39270 I_S optimization algorithm does not work properly in some cases
        backport to Betony
     @ mysql-test/t/information_schema.test
        Bug#39270 I_S optimization algorithm does not work properly in some cases
        backport to Betony
     @ sql/sql_show.cc
        Bug#39270 I_S optimization algorithm does not work properly in some cases
        backport to Betony

    modified:
      mysql-test/r/information_schema.result
      mysql-test/t/information_schema.test
      sql/sql_show.cc
=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result	2009-10-23 06:56:30 +0000
+++ b/mysql-test/r/information_schema.result	2009-10-23 07:02:55 +0000
@@ -1681,3 +1681,21 @@ ERROR 42000: Access denied for user 'roo
 LOCK TABLES t1 READ, information_schema.tables READ;
 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
 DROP TABLE t1;
+EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	KEY_COLUMN_USAGE	ALL	NULL	NULL	NULL	NULL	NULL	Open_full_table; Scanned all databases
+EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	PARTITIONS	ALL	NULL	TABLE_NAME	NULL	NULL	NULL	Using where; Open_full_table; Scanned 1 database
+EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
+WHERE CONSTRAINT_SCHEMA='test';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	REFERENTIAL_CONSTRAINTS	ALL	NULL	CONSTRAINT_SCHEMA	NULL	NULL	NULL	Using where; Open_full_table; Scanned 1 database
+EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	TABLE_CONSTRAINTS	ALL	NULL	TABLE_SCHEMA,TABLE_NAME	NULL	NULL	NULL	Using where; Open_full_table; Scanned 0 databases
+EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
+WHERE EVENT_OBJECT_SCHEMA='test';
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	TRIGGERS	ALL	NULL	EVENT_OBJECT_SCHEMA	NULL	NULL	NULL	Using where; Open_full_table; Scanned 1 database

=== modified file 'mysql-test/t/information_schema.test'
--- a/mysql-test/t/information_schema.test	2009-10-23 06:56:30 +0000
+++ b/mysql-test/t/information_schema.test	2009-10-23 07:02:55 +0000
@@ -1391,9 +1391,6 @@ SET TIMESTAMP=DEFAULT;
 
 --echo End of 5.1 tests.
 
-# Wait till all disconnects are completed
---source include/wait_until_count_sessions.inc
-
 #
 # Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
 # 
@@ -1431,3 +1428,18 @@ DROP TABLE t1, information_schema.tables
 --error ER_DBACCESS_DENIED_ERROR
 LOCK TABLES t1 READ, information_schema.tables READ;
 DROP TABLE t1;
+
+#
+# Bug#39270 I_S optimization algorithm does not work properly in some cases
+#
+EXPLAIN SELECT * FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE;
+EXPLAIN SELECT * FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME='t1';
+EXPLAIN SELECT * FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
+  WHERE CONSTRAINT_SCHEMA='test';
+EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
+  WHERE TABLE_NAME='t1' and TABLE_SCHEMA='test';
+EXPLAIN SELECT * FROM INFORMATION_SCHEMA.TRIGGERS
+  WHERE EVENT_OBJECT_SCHEMA='test';
+
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc

=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc	2009-10-23 06:20:44 +0000
+++ b/sql/sql_show.cc	2009-10-23 07:02:55 +0000
@@ -6717,11 +6717,12 @@ ST_SCHEMA_TABLE schema_tables[]=
    fill_variables, make_old_format, 0, 0, -1, 0, 0},
   {"KEY_COLUMN_USAGE", key_column_usage_fields_info, create_schema_table,
    get_all_tables, 0, get_schema_key_column_usage_record, 4, 5, 0,
-   OPEN_TABLE_ONLY},
+   OPTIMIZE_I_S_TABLE|OPEN_TABLE_ONLY},
   {"OPEN_TABLES", open_tables_fields_info, create_schema_table,
    fill_open_tables, make_old_format, 0, -1, -1, 1, 0},
   {"PARTITIONS", partitions_fields_info, create_schema_table,
-   get_all_tables, 0, get_schema_partitions_record, 1, 2, 0, OPEN_TABLE_ONLY},
+   get_all_tables, 0, get_schema_partitions_record, 1, 2, 0,
+   OPTIMIZE_I_S_TABLE|OPEN_TABLE_ONLY},
   {"PLUGINS", plugin_fields_info, create_schema_table,
    fill_plugins, make_old_format, 0, -1, -1, 0, 0},
   {"PROCESSLIST", processlist_fields_info, create_schema_table,
@@ -6731,7 +6732,7 @@ ST_SCHEMA_TABLE schema_tables[]=
     NULL, -1, -1, false, 0},
   {"REFERENTIAL_CONSTRAINTS", referential_constraints_fields_info,
    create_schema_table, get_all_tables, 0, get_referential_constraints_record,
-   1, 9, 0, OPEN_TABLE_ONLY},
+   1, 9, 0, OPTIMIZE_I_S_TABLE|OPEN_TABLE_ONLY},
   {"ROUTINES", proc_fields_info, create_schema_table, 
    fill_schema_proc, make_proc_old_format, 0, -1, -1, 0, 0},
   {"SCHEMATA", schema_fields_info, create_schema_table,
@@ -6751,14 +6752,15 @@ ST_SCHEMA_TABLE schema_tables[]=
    get_all_tables, make_old_format, get_schema_tables_record, 1, 2, 0,
    OPTIMIZE_I_S_TABLE},
   {"TABLE_CONSTRAINTS", table_constraints_fields_info, create_schema_table,
-   get_all_tables, 0, get_schema_constraints_record, 3, 4, 0, OPEN_TABLE_ONLY},
+   get_all_tables, 0, get_schema_constraints_record, 3, 4, 0,
+   OPTIMIZE_I_S_TABLE|OPEN_TABLE_ONLY},
   {"TABLE_NAMES", table_names_fields_info, create_schema_table,
    get_all_tables, make_table_names_old_format, 0, 1, 2, 1, 0},
   {"TABLE_PRIVILEGES", table_privileges_fields_info, create_schema_table,
    fill_schema_table_privileges, 0, 0, -1, -1, 0, 0},
   {"TRIGGERS", triggers_fields_info, create_schema_table,
    get_all_tables, make_old_format, get_schema_triggers_record, 5, 6, 0,
-   OPEN_TABLE_ONLY},
+   OPTIMIZE_I_S_TABLE|OPEN_TABLE_ONLY},
   {"USER_PRIVILEGES", user_privileges_fields_info, create_schema_table, 
    fill_schema_user_privileges, 0, 0, -1, -1, 0, 0},
   {"VARIABLES", variables_fields_info, create_schema_table, fill_variables,


Attachment: [text/bzr-bundle] bzr/[e-mail]