bzr commit into mysql-6.0-bugteam branch (Sergey.Glukhov:2817) Bug#39270

[Sergey Glukhov]

#At file:///home/gluh/MySQL/mysql-6.0-bugteam/ based on revid:sergey.glukhov@stripped

 2817 Sergey Glukhov	2008-12-17
      Bug#39270 I_S optimization algorithm does not work properly in some cases
      I_S  KEY_COLUMN_USAGE, PARTITIONS,
      REFERENTIAL_CONSTRAINTS, TABLE_CONSTRAINTS, TRIGGERS tables:
      set OPTIMIZE_I_S_TABLE flag to make them EXPLAIN visible.
modified:
  mysql-test/r/information_schema.result
  mysql-test/t/information_schema.test
  sql/sql_show.cc

per-file messages:
  mysql-test/r/information_schema.result
    test result
  mysql-test/t/information_schema.test
    test case
  sql/sql_show.cc
    I_S  KEY_COLUMN_USAGE, PARTITIONS,
    REFERENTIAL_CONSTRAINTS, TABLE_CONSTRAINTS, TRIGGERS tables:
    set OPTIMIZE_I_S_TABLE flag to make them EXPLAIN visible.
=== modified file 'mysql-test/r/information_schema.result'
--- a/mysql-test/r/information_schema.result	2008-12-10 15:59:12 +0000
+++ b/mysql-test/r/information_schema.result	2008-12-17 16:16:15 +0000
@@ -1732,3 +1732,21 @@ unlock tables;
 # Switching to connection 'con3726_2'
 # Switching to connection 'default'
 drop tables t1, t3;
+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_frm_only; Scanned 1 database

=== modified file 'mysql-test/t/information_schema.test'
--- a/mysql-test/t/information_schema.test	2008-12-10 15:59:12 +0000
+++ b/mysql-test/t/information_schema.test	2008-12-17 16:16:15 +0000
@@ -1450,3 +1450,16 @@ connection con3726_2;
 --echo # Switching to connection 'default'
 connection default;
 drop tables t1, t3;
+
+#
+# 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';
+

=== modified file 'sql/sql_show.cc'
--- a/sql/sql_show.cc	2008-12-17 16:03:50 +0000
+++ b/sql/sql_show.cc	2008-12-17 16:16:15 +0000
@@ -7143,13 +7143,14 @@ 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},
   {"PARAMETERS", parameters_fields_info, create_schema_table,
    fill_schema_proc, 0, 0, -1, -1, 0, 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,
@@ -7159,7 +7160,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,
@@ -7181,7 +7182,8 @@ ST_SCHEMA_TABLE schema_tables[]=
   {"TABLESPACES", tablespaces_fields_info, create_schema_table,
    hton_fill_schema_table, 0, 0, -1, -1, 0, 0},
   {"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,