Create 'main' test directory and move 't' and 'r' there

1 files changed, 1905 insertions, 0 deletions

diff --git a/mysql-test/main/information_schema.test b/mysql-test/main/information_schema.test
new file mode 100644
index 00000000000..1f72d0b6129
--- /dev/null
+++ b/mysql-test/main/information_schema.test
@@ -0,0 +1,1905 @@
+# This test  uses grants, which can't get tested for embedded server
+-- source include/not_embedded.inc
+
+#Don't run this test when thread_pool active
+--source include/not_threadpool.inc
+
+# check that CSV engine was compiled in, as the result of the test depends
+# on the presence of the log tables (which are CSV-based).
+--source include/have_csv.inc
+
+-- source include/have_innodb.inc
+
+# Save the initial number of concurrent sessions
+--source include/count_sessions.inc
+
+set global sql_mode="";
+set local sql_mode="";
+
+# Test for information_schema.schemata &
+# show databases
+
+--disable_warnings
+DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
+DROP VIEW IF EXISTS v1;
+--enable_warnings
+
+
+show variables where variable_name like "skip_show_database";
+grant select, update, execute on test.* to mysqltest_2@localhost;
+grant select, update on test.* to mysqltest_1@localhost;
+create user mysqltest_3@localhost;
+create user mysqltest_3;
+
+
+--sorted_result
+select * from information_schema.SCHEMATA where schema_name > 'm';
+--sorted_result
+select schema_name from information_schema.schemata;
+show databases like 't%';
+show databases;
+show databases where `database` = 't%';
+
+# Test for information_schema.tables &
+# show tables
+
+create database mysqltest;
+create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
+create table test.t2(a int);
+create table t3(a int, KEY a_data (a));
+create table mysqltest.t4(a int);
+create table t5 (id int auto_increment primary key);
+insert into t5 values (10);
+create view v1 (c) as
+ SELECT table_name FROM information_schema.TABLES
+  WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND
+        table_name not like 'innodb_%' AND
+        table_name not like 'xtradb_%';
+--sorted_result
+select * from v1;
+
+--sorted_result
+select c,table_name from v1 
+inner join information_schema.TABLES v2 on (v1.c=v2.table_name)
+where v1.c like "t%";
+
+--sorted_result
+select c,table_name from v1 
+left join information_schema.TABLES v2 on (v1.c=v2.table_name)
+where v1.c like "t%";
+
+--sorted_result
+select c, v2.table_name from v1
+right join information_schema.TABLES v2 on (v1.c=v2.table_name)
+where v1.c like "t%";
+
+--sorted_result
+select table_name from information_schema.TABLES
+where table_schema = "mysqltest" and table_name like "t%";
+
+select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
+show keys from t3 where Key_name = "a_data";
+
+show tables like 't%';
+--replace_column 8 # 12 # 13 # 19 #
+show table status;
+show full columns from t3 like "a%";
+show full columns from mysql.db like "Insert%";
+show full columns from v1;
+select * from information_schema.COLUMNS where table_name="t1"
+and column_name= "a";
+show columns from mysqltest.t1 where field like "%a%";
+
+create view mysqltest.v1 (c) as select a from mysqltest.t1;
+grant select (a) on mysqltest.t1 to mysqltest_2@localhost;
+grant select on mysqltest.v1 to mysqltest_3;
+connect (user3,localhost,mysqltest_2,,);
+connection user3;
+select table_name, column_name, privileges from information_schema.columns
+where table_schema = 'mysqltest' and table_name = 't1';
+show columns from mysqltest.t1;
+connect (user4,localhost,mysqltest_3,,mysqltest);
+connection user4;
+select table_name, column_name, privileges from information_schema.columns
+where table_schema = 'mysqltest' and table_name = 'v1';
+--error ER_VIEW_NO_EXPLAIN
+explain select * from v1;
+connection default;
+disconnect user4;
+
+drop view v1, mysqltest.v1;
+drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
+drop database mysqltest;
+
+# Test for information_schema.CHARACTER_SETS &
+# SHOW CHARACTER SET
+
+select * from information_schema.CHARACTER_SETS
+where CHARACTER_SET_NAME like 'latin1%';
+SHOW CHARACTER SET LIKE 'latin1%';
+SHOW CHARACTER SET WHERE charset like 'latin1%';
+
+# Test for information_schema.COLLATIONS &
+# SHOW COLLATION
+
+--replace_column 5 #
+select * from information_schema.COLLATIONS
+where COLLATION_NAME like 'latin1%';
+--replace_column 5 #
+SHOW COLLATION LIKE 'latin1%';
+--replace_column 5 #
+SHOW COLLATION WHERE collation like 'latin1%';
+
+select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
+where COLLATION_NAME like 'latin1%';
+
+# Test for information_schema.ROUTINES &
+#
+
+--disable_warnings
+drop procedure if exists sel2;
+drop function if exists sub1;
+drop function if exists sub2;
+--enable_warnings
+
+create function sub1(i int) returns int
+  return i+1;
+delimiter |;
+create procedure sel2()
+begin
+  select * from t1;
+  select * from t2;
+end|
+delimiter ;|
+
+#
+# Bug#7222 information_schema: errors in "routines"
+#
+select parameter_style, sql_data_access, dtd_identifier
+from information_schema.routines where routine_schema='test';
+
+--replace_column 5 # 6 #
+show procedure status where db='test';
+--replace_column 5 # 6 #
+show function status where db='test';
+select a.ROUTINE_NAME from information_schema.ROUTINES a,
+information_schema.SCHEMATA b where
+a.ROUTINE_SCHEMA = b.SCHEMA_NAME AND b.SCHEMA_NAME='test';
+--replace_column 3 #
+explain select a.ROUTINE_NAME from information_schema.ROUTINES a,
+information_schema.SCHEMATA b where
+a.ROUTINE_SCHEMA = b.SCHEMA_NAME;
+
+select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a,
+mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) AND a.ROUTINE_SCHEMA='test' order by 1;
+select count(*) from information_schema.ROUTINES where routine_schema='test';
+
+create view v1 as select routine_schema, routine_name from information_schema.routines where routine_schema='test'
+order by routine_schema, routine_name;
+select * from v1;
+drop view v1;
+
+connect (user1,localhost,mysqltest_1,,);
+connection user1;
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+--error ER_SP_DOES_NOT_EXIST
+show create function sub1;
+connection user3;
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+connection default;
+grant all privileges on test.* to mysqltest_1@localhost;
+connect (user2,localhost,mysqltest_1,,);
+connection user2;
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+create function sub2(i int) returns int
+  return i+1;
+select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES;
+show create procedure sel2;
+show create function sub1;
+show create function sub2;
+--replace_column 5 # 6 #
+show function status like "sub2";
+connection default;
+disconnect user1;
+disconnect user3;
+drop function sub2;
+show create procedure sel2;
+
+#
+# Test for views
+#
+create view v0 (c) as select schema_name from information_schema.schemata;
+--sorted_result
+select * from v0;
+--replace_column 3 #
+explain select * from v0;
+create view v1 (c) as select table_name from information_schema.tables
+where table_name="v1";
+select * from v1;
+create view v2 (c) as select column_name from information_schema.columns
+where table_name="v2";
+select * from v2;
+create view v3 (c) as select CHARACTER_SET_NAME from information_schema.character_sets
+where CHARACTER_SET_NAME like "latin1%";
+select * from v3;
+create view v4 (c) as select COLLATION_NAME from information_schema.collations
+where COLLATION_NAME like "latin1%";
+select * from v4;
+show keys from v4;
+--sorted_result
+select * from information_schema.views where TABLE_NAME like "v%";
+drop view v0, v1, v2, v3, v4;
+
+#
+# Test for privileges tables
+#
+create table t1 (a int);
+grant select,update,insert on t1 to mysqltest_1@localhost;
+grant select (a), update (a),insert(a), references(a) on t1 to mysqltest_1@localhost;
+grant all on test.* to mysqltest_1@localhost with grant option;
+select * from information_schema.USER_PRIVILEGES where grantee like '%mysqltest_1%';
+select * from information_schema.SCHEMA_PRIVILEGES where grantee like '%mysqltest_1%';
+select * from information_schema.TABLE_PRIVILEGES where grantee like '%mysqltest_1%';
+select * from information_schema.COLUMN_PRIVILEGES where grantee like '%mysqltest_1%';
+delete from mysql.user where user like 'mysqltest%';
+delete from mysql.db where user like 'mysqltest%';
+delete from mysql.tables_priv where user like 'mysqltest%';
+delete from mysql.columns_priv where user like 'mysqltest%';
+flush privileges;
+drop table t1;
+
+
+#
+# Test for KEY_COLUMN_USAGE & TABLE_CONSTRAINTS tables
+#
+
+create table t1 (a int null, primary key(a));
+alter table t1 add constraint constraint_1 unique (a);
+alter table t1 add constraint unique key_1(a);
+alter table t1 add constraint constraint_2 unique key_2(a);
+show create table t1;
+select * from information_schema.TABLE_CONSTRAINTS where
+TABLE_SCHEMA= "test";
+select * from information_schema.KEY_COLUMN_USAGE where
+TABLE_SCHEMA= "test";
+
+connection user2;
+select table_name from information_schema.TABLES where table_schema like "test%";
+select table_name,column_name from information_schema.COLUMNS where table_schema like "test%";
+select ROUTINE_NAME from information_schema.ROUTINES;
+disconnect user2;
+connection default;
+delete from mysql.user where user='mysqltest_1';
+drop table t1;
+drop procedure sel2;
+drop function sub1;
+
+create table t1(a int);
+create view v1 (c) as select a from t1 with check option;
+create view v2 (c) as select a from t1 WITH LOCAL CHECK OPTION;
+create view v3 (c) as select a from t1 WITH CASCADED CHECK OPTION;
+--sorted_result
+select * from information_schema.views;
+grant select (a) on test.t1 to joe@localhost with grant option;
+select * from INFORMATION_SCHEMA.COLUMN_PRIVILEGES;
+select * from INFORMATION_SCHEMA.TABLE_PRIVILEGES;
+drop view v1, v2, v3;
+drop table t1;
+delete from mysql.user where user='joe';
+delete from mysql.db where user='joe';
+delete from mysql.tables_priv where user='joe';
+delete from mysql.columns_priv where user='joe';
+flush privileges;
+
+# QQ This results in NULLs instead of the version numbers when
+# QQ a LOCK TABLES is in effect when selecting from
+# QQ information_schema.tables.
+
+--disable_parsing # until bug is fixed
+delimiter //;
+create procedure px5 ()
+begin
+declare v int;
+declare c cursor for select version from
+information_schema.tables where table_schema <> 'information_schema';
+open c;
+fetch c into v;
+select v;
+close c;
+end;//
+
+call px5()//
+call px5()//
+delimiter ;//
+select sql_mode from information_schema.ROUTINES;
+drop procedure px5;
+--enable_parsing
+
+create table t1 (a int not null auto_increment,b int, primary key (a));
+insert into t1 values (1,1),(NULL,3),(NULL,4);
+select AUTO_INCREMENT from information_schema.tables where table_name = 't1';
+drop table t1;
+
+create table t1 (s1 int);
+insert into t1 values (0),(9),(0);
+--sorted_result
+select s1 from t1 where s1 in (select version from
+information_schema.tables) union select version from
+information_schema.tables;
+drop table t1;
+
+SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
+set names latin2;
+SHOW CREATE TABLE INFORMATION_SCHEMA.character_sets;
+set names latin1;
+
+create table t1 select * from information_schema.CHARACTER_SETS
+where CHARACTER_SET_NAME like "latin1";
+select * from t1;
+alter table t1 default character set utf8;
+show create table t1;
+drop table t1;
+
+create view v1 as select * from information_schema.TABLES;
+drop view v1;
+create table t1(a NUMERIC(5,3), b NUMERIC(5,1), c float(5,2),
+ d NUMERIC(6,4), e float, f DECIMAL(6,3), g int(11), h DOUBLE(10,3),
+ i DOUBLE);
+select COLUMN_NAME,COLUMN_TYPE, CHARACTER_MAXIMUM_LENGTH,
+ CHARACTER_OCTET_LENGTH, NUMERIC_PRECISION, NUMERIC_SCALE
+from information_schema.columns where table_name= 't1';
+drop table t1;
+
+create table t115 as select table_name, column_name, column_type
+from information_schema.columns where table_name = 'proc';
+select * from t115;
+drop table t115;
+
+delimiter //;
+create procedure p108 () begin declare c cursor for select data_type
+from information_schema.columns;  open c; open c; end;//
+--error ER_SP_CURSOR_ALREADY_OPEN
+call p108()//
+delimiter ;//
+drop procedure p108;
+
+create view v1 as select A1.table_name from information_schema.TABLES A1
+where table_name= "user";
+select * from v1;
+drop view v1;
+
+create view vo as select 'a' union select 'a';
+show index from vo;
+select * from information_schema.TABLE_CONSTRAINTS where
+TABLE_NAME= "vo";
+select * from information_schema.KEY_COLUMN_USAGE where
+TABLE_NAME= "vo";
+drop view vo;
+
+select TABLE_NAME,TABLE_TYPE,ENGINE
+from information_schema.tables
+where table_schema='information_schema' limit 2;
+--sorted_result
+show tables from information_schema like "T%";
+
+--error ER_DBACCESS_DENIED_ERROR
+create database information_schema;
+use information_schema;
+--sorted_result
+show full tables like "T%";
+--error ER_DBACCESS_DENIED_ERROR
+create table t1(a int);
+use test;
+show tables;
+use information_schema;
+--sorted_result
+show tables like "T%";
+
+#
+# Bug#7210 information_schema: can't access when table-name = reserved word
+#
+select table_name from tables where table_name='user';
+select column_name, privileges from columns
+where table_name='user' and column_name like '%o%';
+
+#
+# Bug#7212 information_schema: "Can't find file" errors if storage engine gone
+# Bug#7211 information_schema: crash if bad view
+#
+use test;
+create function sub1(i int) returns int
+  return i+1;
+create table t1(f1 int);
+create view v2 (c) as select f1 from t1;
+create view v3 (c) as select sub1(1);
+create table t4(f1 int, KEY f1_key (f1));
+drop table t1;
+drop function sub1;
+--sorted_result
+select table_name from information_schema.views
+where table_schema='test';
+--sorted_result
+select table_name from information_schema.views
+where table_schema='test';
+select column_name from information_schema.columns
+where table_schema='test' and table_name='t4';
+select column_name from information_schema.columns
+where table_schema='test' and table_name='v2';
+select column_name from information_schema.columns
+where table_schema='test' and table_name='v3';
+select index_name from information_schema.statistics where table_schema='test';
+select constraint_name from information_schema.table_constraints
+where table_schema='test';
+show create view v2;
+show create table v3;
+drop view v2;
+drop view v3;
+drop table t4;
+
+#
+# Bug#7213 information_schema: redundant non-standard TABLE_NAMES table
+#
+--error ER_UNKNOWN_TABLE
+select * from information_schema.table_names;
+
+#
+# Bug#2719 information_schema: errors in "columns"
+#
+select column_type from information_schema.columns
+where table_schema="information_schema" and table_name="COLUMNS" and
+(column_name="character_set_name" or column_name="collation_name");
+
+#
+# Bug#2718 information_schema: errors in "tables"
+#
+select TABLE_ROWS from information_schema.tables where
+table_schema="information_schema" and table_name="COLUMNS";
+select table_type from information_schema.tables
+where table_schema="mysql" and table_name="user";
+
+# test for 'show open tables ... where'
+show open tables where `table` like "user";
+# test for 'show status ... where'
+show status where variable_name like "%database%";
+# test for 'show variables ... where'
+show variables where variable_name like "skip_show_databas";
+
+#
+# Bug#7981 SHOW GLOBAL STATUS crashes server
+#
+# We don't actually care about the value, just that it doesn't crash.
+--replace_column 2 #
+show global status like "Threads_running";
+
+#
+# Bug#7915 crash,JOIN VIEW, subquery,
+# SELECT .. FROM INFORMATION_SCHEMA.COLUMNS
+#
+create table t1(f1 int);
+create table t2(f2 int);
+create view v1 as select * from t1, t2;
+set @got_val= (select count(*) from information_schema.columns);
+drop view v1;
+drop table t1, t2;
+
+#
+# Bug#7476 crash on SELECT * FROM INFORMATION_SCHEMA.TABLES
+#
+use test;
+CREATE TABLE t_crashme ( f1 BIGINT);
+CREATE VIEW a1 (t_CRASHME) AS SELECT f1 FROM t_crashme GROUP BY f1;
+CREATE VIEW a2 AS SELECT t_CRASHME FROM a1;
+let $tab_count= 65;
+--disable_query_log
+while ($tab_count)
+{
+     EVAL CREATE TABLE t_$tab_count (f1 BIGINT);
+     dec $tab_count ;
+}
+--disable_result_log
+SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES;
+--enable_result_log
+SELECT count(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA='test';
+let $tab_count= 65;
+while ($tab_count)
+{
+     EVAL DROP TABLE t_$tab_count;
+     dec $tab_count ;
+}
+--enable_query_log
+drop view a2, a1;
+drop table t_crashme;
+
+#
+# Bug#7215 information_schema: columns are longtext instead of varchar
+# Bug#7217 information_schema: columns are varbinary() instead of timestamp
+#
+select table_schema,table_name, column_name from
+information_schema.columns
+where data_type = 'longtext' and table_schema != 'performance_schema'
+order by binary table_name, ordinal_position;
+select table_name, column_name, data_type from information_schema.columns
+where data_type = 'datetime' and table_name not like 'innodb_%'
+order by binary table_name, ordinal_position;
+
+#
+# Bug#8164 subquery with INFORMATION_SCHEMA.COLUMNS, 100 % CPU
+#
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
+WHERE NOT EXISTS
+(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
+  WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
+  AND A.TABLE_NAME = B.TABLE_NAME);
+
+#
+# Bug#9344 INFORMATION_SCHEMA, wrong content, numeric columns
+#
+
+create table t1
+( x_bigint BIGINT,
+  x_integer INTEGER,
+  x_smallint SMALLINT,
+  x_decimal DECIMAL(5,3),
+  x_numeric NUMERIC(5,3),
+  x_real REAL,
+  x_float FLOAT,
+  x_double_precision DOUBLE PRECISION );
+SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
+FROM INFORMATION_SCHEMA.COLUMNS
+WHERE TABLE_NAME= 't1';
+drop table t1;
+
+#
+# Bug#10261 INFORMATION_SCHEMA.COLUMNS, incomplete result for non root user
+#
+
+grant select on test.* to mysqltest_4@localhost;
+connect (user10261,localhost,mysqltest_4,,);
+connection user10261;
+--sorted_result
+SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS 
+where COLUMN_NAME='TABLE_NAME' and table_name not like 'innodb%';
+connection default;
+disconnect user10261;
+delete from mysql.user where user='mysqltest_4';
+delete from mysql.db where user='mysqltest_4';
+flush privileges;
+
+#
+# TRIGGERS table test
+#
+create table t1 (i int, j int);
+
+delimiter |;
+create trigger trg1 before insert on t1 for each row
+begin
+  if new.j > 10 then
+    set new.j := 10;
+  end if;
+end|
+create trigger trg2 before update on t1 for each row
+begin
+  if old.i % 2 = 0 then
+    set new.j := -1;
+  end if;
+end|
+create trigger trg3 after update on t1 for each row
+begin
+  if new.j = -1 then
+    set @fired:= "Yes";
+  end if;
+end|
+delimiter ;|
+--replace_column 6 #
+show triggers;
+--replace_column 17 #
+select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest');
+
+drop trigger trg1;
+drop trigger trg2;
+drop trigger trg3;
+drop table t1;
+
+
+#
+# Bug#10964 Information Schema:Authorization check on privilege tables is improper
+#
+
+create database mysqltest;
+create table mysqltest.t1 (f1 int, f2 int);
+create table mysqltest.t2 (f1 int);
+grant select (f1) on mysqltest.t1 to user1@localhost;
+grant select on mysqltest.t2 to user2@localhost;
+grant select on mysqltest.* to user3@localhost;
+grant select on *.* to user4@localhost;
+
+connect (con1,localhost,user1,,mysqltest);
+connect (con2,localhost,user2,,mysqltest);
+connect (con3,localhost,user3,,mysqltest);
+connect (con4,localhost,user4,,);
+connection con1;
+select * from information_schema.column_privileges order by grantee;
+select * from information_schema.table_privileges order by grantee;
+select * from information_schema.schema_privileges order by grantee;
+select * from information_schema.user_privileges order by grantee;
+show grants;
+connection con2;
+select * from information_schema.column_privileges order by grantee;
+select * from information_schema.table_privileges order by grantee;
+select * from information_schema.schema_privileges order by grantee;
+select * from information_schema.user_privileges order by grantee;
+show grants;
+connection con3;
+select * from information_schema.column_privileges order by grantee;
+select * from information_schema.table_privileges order by grantee;
+select * from information_schema.schema_privileges order by grantee;
+select * from information_schema.user_privileges order by grantee;
+show grants;
+connection con4;
+select * from information_schema.column_privileges where grantee like '\'user%'
+order by grantee;
+select * from information_schema.table_privileges where grantee like '\'user%'
+order by grantee;
+select * from information_schema.schema_privileges where grantee like '\'user%'
+order by grantee;
+select * from information_schema.user_privileges where grantee like '\'user%'
+order by grantee;
+show grants;
+connection default;
+disconnect con1;
+disconnect con2;
+disconnect con3;
+disconnect con4;
+drop user user1@localhost, user2@localhost, user3@localhost, user4@localhost;
+use test;
+drop database mysqltest;
+
+#
+# Bug#11055 information_schema: routines.sql_data_access has wrong value
+#
+--disable_warnings
+drop procedure if exists p1;
+drop procedure if exists p2;
+--enable_warnings
+
+create procedure p1 () modifies sql data set @a = 5;
+create procedure p2 () set @a = 5;
+select sql_data_access from information_schema.routines
+where specific_name like 'p%';
+drop procedure p1;
+drop procedure p2;
+
+#
+# Bug#9434 SHOW CREATE DATABASE information_schema;
+#
+show create database information_schema;
+
+#
+# Bug#11057 information_schema: columns table has some questionable contents
+# Bug#12301 information_schema: NUMERIC_SCALE must be 0 for integer columns
+#
+create table t1(f1 LONGBLOB, f2 LONGTEXT);
+select column_name,data_type,CHARACTER_OCTET_LENGTH,
+       CHARACTER_MAXIMUM_LENGTH
+from information_schema.columns
+where table_name='t1';
+drop table t1;
+create table t1(f1 tinyint, f2 SMALLINT, f3 mediumint, f4 int,
+                f5 BIGINT, f6 BIT, f7 bit(64));
+select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
+from information_schema.columns
+where table_name='t1';
+drop table t1;
+
+#
+# Bug#12127 triggers do not show in info_schema before they are used if set to the database
+#
+create table t1 (f1 integer);
+create trigger tr1 after insert on t1 for each row set @test_var=42;
+use information_schema;
+select trigger_schema, trigger_name from triggers where
+trigger_name='tr1';
+use test;
+drop table t1;
+
+#
+# Bug#12518 COLUMN_DEFAULT has wrong value if NOT NULL is set
+#
+create table t1 (a int not null, b int);
+use information_schema;
+select column_name, column_default from columns
+  where table_schema='test' and table_name='t1';
+use test;
+show columns from t1;
+drop table t1;
+
+#
+# Bug#12636 SHOW TABLE STATUS with where condition containing a subquery
+#           over information schema
+#
+
+CREATE TABLE t1 (a int);
+CREATE TABLE t2 (b int);
+
+--replace_column 8 # 12 # 13 # 19 #
+SHOW TABLE STATUS FROM test
+  WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
+                    WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE');
+
+DROP TABLE t1,t2;
+
+#
+# Bug#12905 show fields from view behaving erratically with current database
+#
+create table t1(f1 int);
+create view v1 (c) as select f1 from t1;
+connect (con5,localhost,root,,*NO-ONE*);
+select database();
+show fields from test.v1;
+connection default;
+disconnect con5;
+drop view v1;
+drop table t1;
+
+#
+# Bug#9846 Inappropriate error displayed while dropping table from 'INFORMATION_SCHEMA'
+#
+--error ER_PARSE_ERROR
+alter database information_schema;
+--error ER_DBACCESS_DENIED_ERROR
+drop database information_schema;
+--error ER_DBACCESS_DENIED_ERROR
+drop table information_schema.tables;
+--error ER_DBACCESS_DENIED_ERROR
+alter table information_schema.tables;
+#
+# Bug#9683 INFORMATION_SCH: Creation of temporary table allowed in Information_schema DB
+#
+use information_schema;
+--error ER_DBACCESS_DENIED_ERROR
+create temporary table schemata(f1 char(10));
+#
+# Bug#10708 SP's can use INFORMATION_SCHEMA as ROUTINE_SCHEMA
+#
+delimiter |;
+--error ER_DBACCESS_DENIED_ERROR
+CREATE PROCEDURE p1 ()
+BEGIN
+  SELECT 'foo' FROM DUAL;
+END |
+delimiter ;|
+select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema';
+#
+# Bug#10734 Grant of privileges other than 'select' and 'create view' should fail on schema
+#
+--error ER_DBACCESS_DENIED_ERROR
+grant all on information_schema.* to 'user1'@'localhost';
+--error ER_DBACCESS_DENIED_ERROR
+grant select on information_schema.* to 'user1'@'localhost';
+
+#
+# Bug#14089 FROM list subquery always fails when information_schema is current database
+#
+use test;
+create table t1(id int);
+insert into t1(id) values (1);
+select 1 from (select 1 from test.t1) a;
+use information_schema;
+select 1 from (select 1 from test.t1) a;
+use test;
+drop table t1;
+
+#
+# Bug#14476 `information_schema`.`TABLES`.`TABLE_TYPE` with empty value
+#
+create table t1 (f1 int(11));
+create view v1 as select * from t1;
+drop table t1;
+select table_type from information_schema.tables
+where table_name="v1";
+drop view v1;
+
+#
+# Bug#14387 SHOW COLUMNS doesn't work on temporary tables
+# Bug#15224 SHOW INDEX from temporary table doesn't work
+# Bug#12770 DESC cannot display the info. about temporary table
+#
+create temporary table t1(f1 int, index(f1));
+show columns from t1;
+describe t1;
+show indexes from t1;
+drop table t1;
+
+#
+# Bug#14271 I_S: columns has no size for (var)binary columns
+#
+create table t1(f1 binary(32), f2 varbinary(64));
+select character_maximum_length, character_octet_length
+from information_schema.columns where table_name='t1';
+drop table t1;
+
+#
+# Bug#15533 crash, information_schema, function, view
+#
+CREATE TABLE t1 (f1 BIGINT, f2 VARCHAR(20), f3 BIGINT);
+INSERT INTO t1 SET f1 = 1, f2 = 'Schoenenbourg', f3 = 1;
+
+CREATE FUNCTION func2() RETURNS BIGINT RETURN 1;
+
+delimiter //;
+CREATE FUNCTION func1() RETURNS BIGINT
+BEGIN
+  RETURN ( SELECT COUNT(*) FROM INFORMATION_SCHEMA.VIEWS);
+END//
+delimiter ;//
+
+CREATE VIEW v1 AS SELECT 1 FROM t1
+                    WHERE f3 = (SELECT func2 ());
+SELECT func1();
+DROP TABLE t1;
+DROP VIEW v1;
+DROP FUNCTION func1;
+DROP FUNCTION func2;
+
+
+#
+# Bug#15307 GROUP_CONCAT() with ORDER BY returns empty set on information_schema
+#
+select column_type, group_concat(table_schema, '.', table_name), count(*) as num
+from information_schema.columns where
+table_schema='information_schema' and
+(column_type = 'varchar(7)' or column_type = 'varchar(20)'
+ or column_type = 'varchar(27)')
+group by column_type order by num;
+
+#
+# Bug#19236 bad COLUMNS.CHARACTER_MAXIMUM_LENGHT and CHARACTER_OCTET_LENGTH
+#
+create table t1(f1 char(1) not null, f2 char(9) not null)
+default character set utf8;
+select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
+information_schema.columns where table_schema='test' and table_name = 't1';
+drop table t1;
+
+#
+# Bug#18177 any access to INFORMATION_SCHEMA.ROUTINES crashes
+#
+use mysql;
+INSERT INTO `proc` VALUES ('test','','PROCEDURE','','SQL','CONTAINS_SQL',
+'NO','DEFINER','','','BEGIN\r\n  \r\nEND','root@%','2006-03-02 18:40:03',
+'2006-03-02 18:40:03','','','utf8','utf8_general_ci','utf8_general_ci','n/a', 'NONE');
+select routine_name from information_schema.routines where ROUTINE_SCHEMA='test';
+delete from proc where name='';
+use test;
+
+#
+# Bug#16681 information_schema shows forbidden VIEW details
+#
+grant select on test.* to mysqltest_1@localhost;
+create table t1 (id int);
+create view v1 as select * from t1;
+create definer = mysqltest_1@localhost
+sql security definer view v2 as select 1;
+
+connect (con16681,localhost,mysqltest_1,,test);
+connection con16681;
+
+select * from information_schema.views
+where table_name='v1' or table_name='v2' order by table_name;
+connection default;
+disconnect con16681;
+drop view v1, v2;
+drop table t1;
+drop user mysqltest_1@localhost;
+
+#
+# Bug#19599 duplication of information_schema column value in a CONCAT expr with user var
+#
+set @a:= '.';
+create table t1(f1 char(5));
+create table t2(f1 char(5));
+select concat(@a, table_name), @a, table_name
+from information_schema.tables where table_schema = 'test' order by table_name;
+drop table t1,t2;
+
+
+#
+# Bug#20230 routine_definition is not null
+#
+--disable_warnings
+DROP PROCEDURE IF EXISTS p1;
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+CREATE PROCEDURE p1() SET @a= 1;
+CREATE FUNCTION f1() RETURNS INT RETURN @a + 1;
+CREATE USER mysql_bug20230@localhost;
+GRANT EXECUTE ON PROCEDURE p1 TO mysql_bug20230@localhost;
+GRANT EXECUTE ON FUNCTION f1 TO mysql_bug20230@localhost;
+
+SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
+SHOW CREATE PROCEDURE p1;
+SHOW CREATE FUNCTION f1;
+
+connect (conn1, localhost, mysql_bug20230,,);
+
+SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test';
+SHOW CREATE PROCEDURE p1;
+SHOW CREATE FUNCTION f1;
+CALL p1();
+SELECT f1();
+
+disconnect conn1;
+connection default;
+
+DROP FUNCTION f1;
+DROP PROCEDURE p1;
+DROP USER mysql_bug20230@localhost;
+
+#
+# Bug#21231 query with a simple non-correlated subquery over
+#           INFORMARTION_SCHEMA.TABLES
+#
+
+SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test') and table_name not like 'xtradb%';
+SELECT table_name from information_schema.tables
+  WHERE table_name=(SELECT MAX(table_name)
+                      FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test') and table_name not like 'xtradb%');
+#
+# Bug #23037 Bug in field "Default" of query "SHOW COLUMNS FROM table"
+#
+# Note, MyISAM/InnoDB can't take more that 65532 chars, because the row
+# size is limited to 65535 bytes (BLOBs not counted)
+#
+--disable_warnings
+DROP TABLE IF EXISTS bug23037;
+DROP FUNCTION IF EXISTS get_value;
+--enable_warnings
+--disable_query_log
+DELIMITER |;
+CREATE FUNCTION get_value()
+  RETURNS TEXT
+  DETERMINISTIC
+BEGIN
+  DECLARE col1, col2, col3, col4, col6 CHAR(255);
+  DECLARE default_val VARCHAR(65532);
+  DECLARE done INT DEFAULT 0;
+  DECLARE cur1 CURSOR FOR SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_DEFAULT, EXTRA FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='bug23037';
+  DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
+  OPEN cur1;
+  FETCH cur1 INTO col1, col2, col3, col4, default_val, col6;
+  CLOSE cur1;
+  RETURN default_val;
+end|
+DELIMITER ;|
+
+let $body=`SELECT REPEAT('A', 65532)`;
+eval CREATE TABLE bug23037(fld1 VARCHAR(65532) CHARACTER SET latin1 DEFAULT "$body");
+--enable_query_log
+
+SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
+
+SELECT MD5(get_value());
+
+SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT=get_value() FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='bug23037';
+
+DROP TABLE bug23037;
+DROP FUNCTION get_value;
+
+#
+# Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash
+#
+set @tmp_optimizer_switch=@@optimizer_switch;
+set optimizer_switch='derived_merge=off,derived_with_keys=off';
+create view v1 as
+select table_schema as object_schema,
+       table_name   as object_name,
+       table_type   as object_type
+from information_schema.tables
+order by object_schema;
+explain select * from v1;
+explain select * from (select table_name from information_schema.tables) as a;
+set optimizer_switch=@tmp_optimizer_switch;
+drop view v1;
+
+#
+# Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail
+#
+create table t1 (f1 int(11));
+create table t2 (f1 int(11), f2 int(11));
+
+select table_name from information_schema.tables
+where table_schema = 'test' and table_name not in
+(select table_name from information_schema.columns
+ where table_schema = 'test' and column_name = 'f3')
+order by table_name;
+drop table t1,t2;
+
+
+#
+# Bug#28266 IS_UPDATABLE field on VIEWS table in I_S database is wrong
+#
+create table t1(f1 int);
+create view v1 as select f1+1 as a from t1;
+create table t2 (f1 int, f2 int);
+create view v2 as select f1+1 as a, f2 as b from t2;
+select table_name, is_updatable from information_schema.views order by table_name;
+#
+# Note: we can perform 'delete' for non updatable view.
+#
+delete from v1;
+drop view v1,v2;
+drop table t1,t2;
+
+#
+# Bug#25859 ALTER DATABASE works w/o parameters
+#
+--error ER_PARSE_ERROR
+alter database;
+--error ER_PARSE_ERROR
+alter database test;
+
+#
+# Bug#27629 Possible security flaw in INFORMATION_SCHEMA and SHOW statements
+#
+
+create database mysqltest;
+create table mysqltest.t1(a int, b int, c int);
+create trigger mysqltest.t1_ai after insert on mysqltest.t1
+  for each row set @a = new.a + new.b + new.c;
+grant select(b) on mysqltest.t1 to mysqltest_1@localhost;
+
+select trigger_name from information_schema.triggers
+where event_object_table='t1';
+--replace_column 6 #
+show triggers from mysqltest;
+
+connect (con27629,localhost,mysqltest_1,,mysqltest);
+show columns from t1;
+select column_name from information_schema.columns where table_name='t1';
+
+--replace_column 6 #
+show triggers;
+select trigger_name from information_schema.triggers
+where event_object_table='t1';
+connection default;
+disconnect con27629;
+drop user mysqltest_1@localhost;
+drop database mysqltest;
+
+#
+# Bug#27747 database metadata doesn't return sufficient column default info
+#
+create table t1 (
+  f1 varchar(50),
+  f2 varchar(50) not null,
+  f3 varchar(50) default '',
+  f4 varchar(50) default NULL,
+  f5 bigint not null,
+  f6 bigint not null default 10,
+  f7 datetime not null,
+  f8 datetime default '2006-01-01'
+);
+select column_default from information_schema.columns where table_name= 't1';
+show columns from t1;
+drop table t1;
+
+#
+# Bug#30079 A check for "hidden" I_S tables is flawed
+#
+--error ER_UNKNOWN_TABLE
+show fields from information_schema.table_names;
+--error ER_UNKNOWN_TABLE
+show keys from information_schema.table_names;
+
+#
+# Bug#34529 Crash on complex Falcon I_S select after ALTER .. PARTITION BY
+#
+USE information_schema;
+SET max_heap_table_size = 16384;
+
+CREATE TABLE test.t1( a INT );
+
+# What we need to create here is a bit of a corner case:
+# We need a star query with information_schema tables, where the first
+# branch of the star join produces zero rows, so that reading of the
+# second branch never happens. At the same time we have to make sure
+# that data for at least the last table is swapped from MEMORY/HEAP to
+# MyISAM. This and only this triggers the bug.
+SELECT *
+FROM tables ta
+JOIN collations co ON ( co.collation_name = ta.table_catalog )
+JOIN character_sets cs ON ( cs.character_set_name = ta.table_catalog );
+
+DROP TABLE test.t1;
+SET max_heap_table_size = DEFAULT;
+USE test;
+
+--echo End of 5.0 tests.
+
+#
+# Show engines
+#
+
+select * from information_schema.engines WHERE ENGINE="MyISAM";
+
+#
+# INFORMATION_SCHEMA.PROCESSLIST
+#
+
+grant select on *.* to user3148@localhost;
+connect (con3148,localhost,user3148,,test);
+connection con3148;
+select user,db from information_schema.processlist;
+connection default;
+disconnect con3148;
+drop user user3148@localhost;
+
+#
+# `time` and `time_ms` columns of INFORMATION_SCHEMA.PROCESSLIST.
+#
+connect (pslistcon,localhost,root,,test);
+let $ID= `select connection_id()`;
+SELECT 'other connection here' AS who;
+connection default;
+sleep 2;
+--disable_query_log
+eval SET @tid=$ID;
+--enable_query_log
+SELECT IF(`time` > 0, 'OK', `time`) AS time_low,
+       IF(`time` < 1000, 'OK', `time`) AS time_high,
+       IF(time_ms >= 1000, 'OK', time_ms) AS time_ms_low,
+       IF(time_ms < 1000000, 'OK', time_ms) AS time_ms_high
+  FROM INFORMATION_SCHEMA.PROCESSLIST
+ WHERE ID=@tid;
+disconnect pslistcon;
+
+#
+# Bug#26174 Server Crash: INSERT ... SELECT ... FROM I_S.GLOBAL_STATUS
+# in Event (see also openssl_1.test)
+#
+--disable_warnings
+DROP TABLE IF EXISTS server_status;
+DROP EVENT IF EXISTS event_status;
+--enable_warnings
+
+SET GLOBAL event_scheduler=1;
+
+DELIMITER $$;
+
+CREATE EVENT event_status
+ ON SCHEDULE AT NOW()
+ ON COMPLETION NOT PRESERVE
+ DO
+BEGIN
+  CREATE TABLE server_status
+  SELECT variable_name
+  FROM information_schema.global_status
+  WHERE variable_name LIKE 'ABORTED_CONNECTS' OR
+  variable_name LIKE 'BINLOG_CACHE_DISK_USE';
+END$$
+
+DELIMITER ;$$
+
+let $wait_timeout= 300;
+let $wait_condition=select count(*) = 0 from information_schema.events where event_name='event_status';
+--source include/wait_condition.inc
+
+SELECT variable_name FROM server_status;
+
+DROP TABLE server_status;
+SET GLOBAL event_scheduler=0;
+
+
+#
+# WL#3732 Information schema optimization
+#
+
+explain select table_name from information_schema.views where
+table_schema='test' and table_name='v1';
+
+explain select * from information_schema.tables;
+explain select * from information_schema.collations;
+
+explain select * from information_schema.tables where
+table_schema='test' and table_name= 't1';
+explain select table_name, table_type from information_schema.tables
+where table_schema='test';
+
+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;
+
+#
+# Bug#30310 wrong result on SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE ..
+#
+SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
+WHERE SCHEMA_NAME = 'mysqltest';
+
+SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
+WHERE SCHEMA_NAME = '';
+
+SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
+WHERE SCHEMA_NAME = 'test';
+
+select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='nonexisting';
+select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='mysql' AND TABLE_NAME='';
+select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='';
+select count(*) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA='' AND TABLE_NAME='nonexisting';
+
+#
+# Bug#30689 Wrong content in I_S.VIEWS.VIEW_DEFINITION if VIEW is based on I_S
+#
+CREATE VIEW v1
+AS SELECT *
+FROM information_schema.tables;
+SELECT VIEW_DEFINITION FROM INFORMATION_SCHEMA.VIEWS where TABLE_NAME = 'v1';
+DROP VIEW v1;
+
+#
+# Bug#30795 Query on INFORMATION_SCHEMA.SCHEMATA, wrong result
+#
+SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
+WHERE SCHEMA_NAME ='information_schema';
+
+#
+# Bug#31381 Error in retrieving Data from INFORMATION_SCHEMA
+#
+SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
+WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
+
+#
+# Bug#31633 Information schema = NULL queries crash the server
+#
+select * from information_schema.columns where table_schema = NULL;
+select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;
+select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
+select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
+select * from `information_schema`.`PARTITIONS` where `TABLE_SCHEMA` = NULL;
+select * from `information_schema`.`PARTITIONS` where `TABLE_NAME` = NULL;
+select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
+select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
+select * from information_schema.schemata where schema_name = NULL;
+select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
+select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;
+select * from information_schema.tables where table_schema = NULL;
+select * from information_schema.tables where table_catalog = NULL;
+select * from information_schema.tables where table_name = NULL;
+select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
+select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
+select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_SCHEMA` = NULL;
+select * from `information_schema`.`TRIGGERS` where `EVENT_OBJECT_TABLE` = NULL;
+select * from `information_schema`.`VIEWS` where `TABLE_SCHEMA` = NULL;
+select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL;
+
+#
+# Bug#31630 debug assert with explain extended select ... from i_s
+#
+explain extended select 1 from information_schema.tables;
+
+#
+# Bug#32775 problems with SHOW EVENTS and Information_Schema
+#
+use information_schema;
+show events;
+show events from information_schema;
+show events where Db= 'information_schema';
+use test;
+
+--echo #
+--echo # Bug#34166 Server crash in SHOW OPEN TABLES and prelocking
+--echo #
+--disable_warnings
+drop table if exists t1;
+drop function if exists f1;
+--enable_warnings
+create table t1 (a int);
+delimiter |;
+create function f1() returns int
+begin
+  insert into t1 (a) values (1);
+  return 0;
+end|
+delimiter ;|
+--disable_result_log
+show open tables where f1()=0;
+show open tables where f1()=0;
+--enable_result_log
+drop table t1;
+drop function f1;
+
+#
+# Bug#34656 KILL a query = Assertion failed: m_status == DA_ERROR ||
+#           m_status == DA_OK
+#
+connect (conn1, localhost, root,,);
+connection conn1;
+let $ID= `select connection_id()`;
+send select * from information_schema.tables where 1=sleep(100000);
+connection default;
+let $wait_timeout= 10;
+let $wait_condition=select count(*)=1 from information_schema.processlist
+where state='User sleep' and
+info='select * from information_schema.tables where 1=sleep(100000)';
+--source include/wait_condition.inc
+disable_query_log;
+eval kill $ID;
+enable_query_log;
+let $wait_timeout= 10;
+let $wait_condition=select count(*)=0 from information_schema.processlist
+where state='User sleep' and
+info='select * from information_schema.tables where 1=sleep(100000)';
+--source include/wait_condition.inc
+connection conn1;
+--error 2013,ER_CONNECTION_KILLED
+reap;
+connection default;
+disconnect conn1;
+
+connect (conn1, localhost, root,,);
+connection conn1;
+let $ID= `select connection_id()`;
+send select * from information_schema.columns where 1=sleep(100000);
+connection default;
+let $wait_timeout= 10;
+let $wait_condition=select count(*)=1 from information_schema.processlist
+where state='User sleep' and
+info='select * from information_schema.columns where 1=sleep(100000)';
+--source include/wait_condition.inc
+disable_query_log;
+eval kill $ID;
+enable_query_log;
+let $wait_timeout= 10;
+let $wait_condition=select count(*)=0 from information_schema.processlist
+where state='User sleep' and
+info='select * from information_schema.columns where 1=sleep(100000)';
+--source include/wait_condition.inc
+connection conn1;
+--error 2013,ER_CONNECTION_KILLED
+reap;
+connection default;
+disconnect conn1;
+
+
+#
+# Bug#38918 selecting from information_schema.columns is disproportionately slow
+#
+explain select count(*) from information_schema.tables;
+explain select count(*) from information_schema.columns;
+explain select count(*) from information_schema.views;
+
+#
+# Bug#39955 SELECT on INFORMATION_SCHEMA.GLOBAL_VARIABLES takes too long
+#
+set global init_connect="drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;\
+drop table if exists t1;drop table if exists t1;";
+select * from information_schema.global_variables where variable_name='init_connect';
+--replace_regex /at row [123]/at row #/
+select * from information_schema.global_variables where variable_name like 'init%' order by variable_name;
+set global init_connect="";
+
+#
+# Bug#34517 SHOW GLOBAL STATUS does not work properly in embedded server.
+#
+
+create table t0 select * from information_schema.global_status where VARIABLE_NAME='COM_SELECT';
+SELECT 1;
+select a.VARIABLE_VALUE - b.VARIABLE_VALUE from t0 b, information_schema.global_status a
+   where a.VARIABLE_NAME = b.VARIABLE_NAME;
+drop table t0;
+
+#
+# Bug#35275 INFORMATION_SCHEMA.TABLES.CREATE_OPTIONS omits KEY_BLOCK_SIZE
+#
+CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1;
+SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
+DROP TABLE t1;
+
+#
+# Bug #22047: Time in SHOW PROCESSLIST for SQL thread in replication seems
+# to become negative
+#
+# Note that at the time of writing, MariaDB differs in behaviour from MySQL on
+# the `time` column. In MySQL this changes depending on the setting of
+# @TIMESTAMP, which is contrary to the documented (and sensible) behaviour.
+# In MariaDB, the `time` column is independent of @TIMESTAMP.
+# (The rationale for this is to keep `time` and `time_ms` consistent;
+# @TIMESTAMP has no microsecond precision).
+#
+
+SET TIMESTAMP=@@TIMESTAMP + 10000000;
+SELECT 'NOT_OK' AS TEST_RESULT FROM INFORMATION_SCHEMA.PROCESSLIST WHERE time < 0;
+SET TIMESTAMP=DEFAULT;
+
+
+--echo #
+--echo # Bug #50276: Security flaw in INFORMATION_SCHEMA.TABLES
+--echo #
+CREATE DATABASE db1;
+USE db1;
+CREATE TABLE t1 (id INT);
+CREATE USER nonpriv;
+USE test;
+
+connect (nonpriv_con, localhost, nonpriv,,);
+connection nonpriv_con;
+--echo # connected as nonpriv
+--echo # Should return 0
+SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1';
+USE INFORMATION_SCHEMA;
+--echo # Should return 0
+SELECT COUNT(*) FROM TABLES WHERE TABLE_NAME='t1';
+
+connection default;
+--echo # connected as root
+disconnect nonpriv_con;
+DROP USER nonpriv;
+DROP TABLE db1.t1;
+DROP DATABASE db1;
+
+--echo
+--echo Bug#54422 query with = 'variables'
+--echo
+
+CREATE TABLE variables(f1 INT);
+SELECT COLUMN_DEFAULT, TABLE_NAME
+FROM INFORMATION_SCHEMA.COLUMNS
+WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'variables';
+DROP TABLE variables;
+
+--echo #
+--echo # Bug #53814: NUMERIC_PRECISION for unsigned bigint field is 19, 
+--echo # should be 20
+--echo #
+
+CREATE TABLE ubig (a BIGINT, b BIGINT UNSIGNED);
+
+SELECT TABLE_NAME, COLUMN_NAME, NUMERIC_PRECISION 
+  FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='ubig';
+
+INSERT INTO ubig VALUES (0xFFFFFFFFFFFFFFFF,0xFFFFFFFFFFFFFFFF);
+SELECT length(CAST(b AS CHAR)) FROM ubig;
+
+DROP TABLE ubig;
+
+#
+# Bug #13889741: HANDLE_FATAL_SIGNAL IN _DB_ENTER_ | HANDLE_FATAL_SIGNAL IN STRNLEN
+#
+select 1 from information_schema.tables where table_schema=repeat('a', 2000);
+grant usage on *.* to mysqltest_1@localhost;
+connect (con1, localhost, mysqltest_1,,);
+connection con1;
+select 1 from information_schema.tables where table_schema=repeat('a', 2000);
+connection default;
+disconnect con1;
+drop user mysqltest_1@localhost;
+
+--echo End of 5.1 tests.
+
+--echo #
+--echo # Additional test for WL#3726 "DDL locking for all metadata objects"
+--echo # To avoid possible deadlocks process of filling of I_S tables should
+--echo # use high-priority metadata lock requests when opening tables.
+--echo # Below we just test that we really use high-priority lock request
+--echo # since reproducing a deadlock will require much more complex test.
+--echo #
+--disable_warnings
+drop tables if exists t1, t2, t3;
+--enable_warnings
+create table t1 (i int);
+create table t2 (j int primary key auto_increment);
+connect (con3726_1,localhost,root,,test);
+connection con3726_1;
+lock table t2 read;
+connect (con3726_2,localhost,root,,test);
+connection con3726_2;
+--echo # RENAME below will be blocked by 'lock table t2 read' above but
+--echo # will add two pending requests for exclusive metadata locks.
+--send rename table t2 to t3
+connection default;
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table metadata lock" and
+        info like "rename table t2 to t3";
+--source include/wait_condition.inc
+--echo # These statements should not be blocked by pending lock requests
+select table_name, column_name, data_type from information_schema.columns
+  where table_schema = 'test' and table_name in ('t1', 't2') order by table_name, column_name;
+select table_name, auto_increment from information_schema.tables
+  where table_schema = 'test' and table_name in ('t1', 't2') order by table_name;
+connection con3726_1;
+unlock tables;
+connection con3726_2;
+--reap
+connection default;
+disconnect con3726_1;
+disconnect con3726_2;
+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';
+
+#
+# Bug#24062 Incorrect error msg after execute DROP TABLE IF EXISTS on information_schema
+#
+--error ER_DBACCESS_DENIED_ERROR
+create table information_schema.t1 (f1 INT);
+--error ER_DBACCESS_DENIED_ERROR
+drop table information_schema.t1;
+--error ER_DBACCESS_DENIED_ERROR
+drop temporary table if exists information_schema.t1;
+--error ER_DBACCESS_DENIED_ERROR
+create temporary table information_schema.t1 (f1 INT);
+--error ER_DBACCESS_DENIED_ERROR
+drop view information_schema.v1;
+--error ER_DBACCESS_DENIED_ERROR
+create view information_schema.v1;
+--error ER_DBACCESS_DENIED_ERROR
+create trigger mysql.trg1 after insert on information_schema.t1 for each row set @a=1;
+--error 1109
+create table t1 select * from information_schema.t1;
+
+CREATE TABLE t1(f1 char(100));
+--error ER_DBACCESS_DENIED_ERROR
+REPAIR TABLE t1, information_schema.tables;
+CHECKSUM TABLE t1, information_schema.tables;
+--error ER_DBACCESS_DENIED_ERROR
+ANALYZE TABLE t1, information_schema.tables;
+CHECK TABLE t1, information_schema.tables;
+--error ER_DBACCESS_DENIED_ERROR
+OPTIMIZE TABLE t1, information_schema.tables;
+--error ER_DBACCESS_DENIED_ERROR
+RENAME TABLE v1 to v2, information_schema.tables to t2;
+--error ER_DBACCESS_DENIED_ERROR
+DROP TABLE t1, information_schema.tables;
+
+--error ER_DBACCESS_DENIED_ERROR
+LOCK TABLES t1 READ, information_schema.tables READ;
+DROP TABLE t1;
+
+
+#
+# Bug #43834    Assertion in Natural_join_column::db_name() on an I_S query
+#
+
+SELECT *
+FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
+LEFT JOIN INFORMATION_SCHEMA.COLUMNS
+USING (TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME)
+WHERE COLUMNS.TABLE_SCHEMA = 'test'
+AND COLUMNS.TABLE_NAME = 't1';
+
+
+--echo #
+--echo # A test case for Bug#56540 "Exception (crash) in sql_show.cc
+--echo # during rqg_info_schema test on Windows"
+--echo # Ensure that we never access memory of a closed table,
+--echo # in particular, never access table->field[] array.
+--echo # Before the fix, the below test case, produced
+--echo # valgrind errors.
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+drop view if exists v1;
+--enable_warnings
+
+create table t1 (a int, b int);
+create view v1 as select t1.a, t1.b from t1;
+alter table t1 change b c int;
+lock table t1 read;
+connect(con1, localhost, root,,);
+connection con1;
+send flush tables;
+connection default;
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+    where state = "Waiting for table flush" and
+          info = "flush tables";
+--source include/wait_condition.inc
+--vertical_results
+select * from information_schema.views;
+--horizontal_results
+unlock tables;
+
+--echo #
+--echo # Cleanup.
+--echo #
+
+connection con1;
+--echo # Reaping 'flush tables'
+reap;
+disconnect con1;
+--source include/wait_until_disconnected.inc
+connection default;
+drop table t1;
+drop view v1;
+
+
+--echo #
+--echo # Test for bug #12828477 - "MDL SUBSYSTEM CREATES BIG OVERHEAD FOR
+--echo #                           CERTAIN QUERIES TO INFORMATION_SCHEMA".
+--echo #
+--echo # Check that metadata locks which are acquired during the process
+--echo # of opening tables/.FRMs/.TRG files while filling I_S table are
+--echo # not kept to the end of statement. Keeping the locks has caused
+--echo # performance problems in cases when big number of tables (.FRMs
+--echo # or .TRG files) were scanned as cost of new lock acquisition has
+--echo # increased linearly.
+--disable_warnings
+drop database if exists mysqltest;
+--enable_warnings
+create database mysqltest;
+use mysqltest;
+create table t0 (i int);
+create table t1 (j int);
+create table t2 (k int);
+
+--echo #
+--echo # Test that we don't keep locks in case when we to fill
+--echo # I_S table we perform full-blown table open.
+--echo #
+
+--echo # Acquire lock on 't2' so upcoming RENAME is
+--echo # blocked.
+lock tables t2 read;
+
+connect (con12828477_1, localhost, root,,mysqltest);
+--echo # The below RENAME should wait on 't2' while
+--echo # keeping X lock on 't1'.
+--send rename table t1 to t3, t2 to t1, t3 to t2
+
+connect (con12828477_2, localhost, root,,mysqltest);
+--echo # Wait while the above RENAME is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table metadata lock" and
+        info = "rename table t1 to t3, t2 to t1, t3 to t2";
+--source include/wait_condition.inc
+
+--echo # Issue query to I_S which will open 't0' and get
+--echo # blocked on 't1' because of RENAME.
+--send select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name='t0' union select table_name, auto_increment from information_schema.tables where table_schema='mysqltest' and table_name<>'t0' order by table_name
+
+connect (con12828477_3, localhost, root,,mysqltest);
+--echo # Wait while the above SELECT is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table metadata lock" and
+        info like '%t0%union%t0%';
+--source include/wait_condition.inc
+
+--echo #
+--echo # Check that it holds no lock on 't0' so it can be renamed.
+rename table t0 to t4;
+
+connection default;
+--echo #
+--echo # Unblock the first RENAME.
+unlock tables;
+
+connection con12828477_1;
+--echo # Reap the first RENAME
+--reap
+
+connection con12828477_2;
+--echo # Reap SELECT to I_S.
+--reap
+
+connection default;
+
+--echo #
+--echo # Now test that we don't keep locks in case when we to fill
+--echo # I_S table we read .FRM or .TRG file only (this was the case
+--echo # for which problem existed).
+--echo #
+
+rename table t4 to t0;
+--echo # Acquire lock on 't2' so upcoming RENAME is
+--echo # blocked.
+lock tables t2 read;
+
+connection con12828477_1;
+--echo # The below RENAME should wait on 't2' while
+--echo # keeping X lock on 't1'.
+--send rename table t1 to t3, t2 to t1, t3 to t2
+
+connection con12828477_2;
+--echo # Wait while the above RENAME is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table metadata lock" and
+        info = "rename table t1 to t3, t2 to t1, t3 to t2";
+--source include/wait_condition.inc
+
+--echo # Issue query to I_S which will open 't0' and get
+--echo # blocked on 't1' because of RENAME.
+--send select event_object_table, trigger_name from information_schema.triggers where event_object_schema='mysqltest'
+
+connection con12828477_3;
+--echo # Wait while the above SELECT is blocked.
+let $wait_condition=
+  select count(*) = 1 from information_schema.processlist
+  where state = "Waiting for table metadata lock" and
+        info = "select event_object_table, trigger_name from information_schema.triggers where event_object_schema='mysqltest'";
+--source include/wait_condition.inc
+
+--echo #
+--echo # Check that it holds no lock on 't0' so it can be renamed.
+rename table t0 to t4;
+
+connection default;
+--echo #
+--echo # Unblock the first RENAME.
+unlock tables;
+
+connection con12828477_1;
+--echo # Reap the first RENAME
+--reap
+
+connection con12828477_2;
+--echo # Reap SELECT to I_S.
+--reap
+
+connection default;
+disconnect con12828477_1;
+disconnect con12828477_2;
+disconnect con12828477_3;
+
+
+--echo #
+--echo # MDEV-3818: Query against view over IS tables worse than equivalent query without view
+--echo #
+
+create view v1 as select table_schema, table_name, column_name from information_schema.columns;
+
+explain extended
+select column_name from v1
+where (table_schema = "osm") and (table_name = "test");
+
+explain extended
+select information_schema.columns.column_name as column_name
+from information_schema.columns
+where (information_schema.columns.table_schema = 'osm') and (information_schema.columns.table_name = 'test');
+
+drop view v1;
+
+--echo #
+--echo # Clean-up.
+drop database mysqltest;
+
+--echo #
+--echo # Test for bug #16869534 - "QUERYING SUBSET OF COLUMNS DOESN'T USE TABLE
+--echo #                           CACHE; OPENED_TABLES INCREASES"
+--echo #
+--disable_result_log
+SELECT * FROM INFORMATION_SCHEMA.TABLES;
+--enable_result_log
+SELECT VARIABLE_VALUE INTO @val1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE
+  VARIABLE_NAME LIKE 'Opened_tables';
+--disable_result_log
+SELECT ENGINE FROM INFORMATION_SCHEMA.TABLES;
+--enable_result_log
+--echo # The below SELECT query should give same output as above SELECT query.
+SELECT VARIABLE_VALUE INTO @val2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE
+  VARIABLE_NAME LIKE 'Opened_tables';
+--echo # The below select should return '1'
+SELECT @val1 = @val2;
+
+--echo #
+--echo # End of 5.5 tests
+--echo #
+
+--echo # 
+--echo # MDEV-5723: mysqldump -uroot unusable for multi-database operations, checks all databases
+--echo # 
+
+--disable_warnings
+drop database if exists db1;
+--enable_warnings
+
+connect (con1,localhost,root,,);
+connection con1;
+
+create database db1;
+use db1;
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+
+create database mysqltest;
+use mysqltest;
+create table t1 (a int);
+create table t2 (a int);
+create table t3 (a int);
+
+flush tables;
+flush status;
+
+SELECT 
+  LOGFILE_GROUP_NAME, FILE_NAME, TOTAL_EXTENTS, INITIAL_SIZE, ENGINE, EXTRA 
+FROM 
+  INFORMATION_SCHEMA.FILES 
+WHERE 
+  FILE_TYPE = 'UNDO LOG' AND FILE_NAME IS NOT NULL AND 
+  LOGFILE_GROUP_NAME IN (SELECT DISTINCT LOGFILE_GROUP_NAME 
+                         FROM INFORMATION_SCHEMA.FILES 
+                         WHERE 
+                           FILE_TYPE = 'DATAFILE' AND 
+                           TABLESPACE_NAME IN (SELECT DISTINCT TABLESPACE_NAME 
+                                               FROM INFORMATION_SCHEMA.PARTITIONS 
+                                               WHERE TABLE_SCHEMA IN ('db1')
+                                              )
+                        ) 
+GROUP BY 
+  LOGFILE_GROUP_NAME, FILE_NAME, ENGINE 
+ORDER BY 
+  LOGFILE_GROUP_NAME;
+
+--echo # This must have Opened_tables=3, not 6.
+show status like 'Opened_tables';
+
+drop database mysqltest;
+drop database db1;
+
+connection default;
+disconnect con1;
+
+# Wait till all disconnects are completed
+--source include/wait_until_count_sessions.inc
+
+set global sql_mode=default;
+
+USE test;
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
+
+
+--echo #
+--echo # Start of 10.1 tests
+--echo #
+
+
+--echo #
+--echo # MDEV-13242 Wrong results for queries with row constructors and information_schema
+--echo #
+
+CREATE TABLE tt1(c1 INT);
+CREATE TABLE tt2(c2 INT);
+SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1', 'c1'));
+SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt2', 'c2'));
+SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2'));
+SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name, column_name) IN (SELECT 'tt1','c1' FROM dual UNION SELECT 'tt2', 'c2' FROM dual);
+SELECT count(*) FROM information_schema.columns WHERE table_schema='test' AND (table_name='tt1' AND column_name='c1') OR (table_name='tt2' AND column_name='c2');
+SELECT column_name FROM information_schema.columns WHERE (table_name, column_name) IN (('tt1','c1'),('tt2', 'c2')) ORDER BY column_name;
+DROP TABLE tt1, tt2;