1 files changed, 257 insertions, 0 deletions
diff --git a/mysql-test/main/information_schema_db.test b/mysql-test/main/information_schema_db.test
new file mode 100644
index 00000000000..94908570969
--- /dev/null
+++ b/mysql-test/main/information_schema_db.test
@@ -0,0 +1,257 @@
+# this test mostly test privilege control (what doesn't work
+# in the embedded server by default). So skip the test in embedded-server mode.
+-- source include/not_embedded.inc
+
+#Don't run this test when thread_pool active
+--source include/not_threadpool.inc
+
+-- source include/testdb_only.inc
+
+set local sql_mode="";
+set global sql_mode="";
+
+--disable_warnings
+drop table if exists t1,t2;
+drop view if exists v1,v2;
+drop function if exists f1;
+drop function if exists f2;
+--enable_warnings
+
+--replace_result 'Tables_in_INFORMATION_SCHEMA (T%)' 'Tables_in_information_schema (T%)'
+--sorted_result
+show tables from INFORMATION_SCHEMA like 'T%';
+create database `inf%`;
+create database mbase;
+use `inf%`;
+show tables;
+
+#
+# Bug#18113 SELECT * FROM information_schema.xxx crashes server
+# Bug#17204 second CALL to procedure crashes Server
+# Crash happened when one selected data from one of INFORMATION_SCHEMA
+# tables and in order to build its contents server had to open view which
+# used stored function and table or view on which one had not global or
+# database-level privileges (e.g. had only table-level or had no
+# privileges at all).
+#
+grant all privileges on `inf%`.* to 'mysqltest_1'@'localhost';
+grant all privileges on `mbase`.* to 'mysqltest_1'@'localhost';
+create table t1 (f1 int);
+delimiter |;
+create function func1(curr_int int) returns int
+begin
+  declare ret_val int;
+  select max(f1) from t1 into ret_val;
+  return ret_val;
+end|
+delimiter ;|
+create view v1 as select f1 from t1 where f1 = func1(f1);
+create function func2() returns int return 1;
+
+use mbase;
+delimiter |;
+create procedure p1 ()
+begin
+select table_name from information_schema.key_column_usage
+order by table_name;
+end|
+delimiter ;|
+
+create table t1
+(f1 int(10) unsigned not null,
+ f2 varchar(100) not null,
+ primary key (f1), unique key (f2));
+
+connect (user1,localhost,mysqltest_1,,);
+connection user1;
+--disable_result_log
+select * from information_schema.tables;
+call mbase.p1();
+call mbase.p1();
+call mbase.p1();
+--enable_result_log
+
+connection default;
+use `inf%`;
+drop user mysqltest_1@localhost;
+drop table t1;
+select table_name, table_type, table_comment from information_schema.tables
+where table_schema='inf%' and func2();
+select table_name, table_type, table_comment from information_schema.tables
+where table_schema='inf%' and func2();
+drop view v1;
+drop function func1;
+drop function func2;
+
+drop database `inf%`;
+drop procedure mbase.p1;
+drop database mbase;
+disconnect user1;
+
+#
+# Bug#18282 INFORMATION_SCHEMA.TABLES provides inconsistent info about invalid views
+#
+use test;
+create table t1 (i int);
+create function f1 () returns int return (select max(i) from t1);
+create view v1 as select f1();
+create table t2 (id int);
+create function f2 () returns int return (select max(i) from t2);
+create view v2 as select f2();
+drop table t2;
+select table_name, table_type, table_comment from information_schema.tables
+where table_schema='test' order by table_name;
+drop table t1;
+select table_name, table_type, table_comment from information_schema.tables
+where table_schema='test' order by table_name;
+drop function f1;
+drop function f2;
+drop view v1, v2;
+
+#
+# Bug#20543 select on information_schema strange warnings, view, different
+#           schemas/users
+#
+#
+create database testdb_1;
+create user testdb_1@localhost;
+grant all on testdb_1.* to testdb_1@localhost with grant option;
+
+create user testdb_2@localhost;
+grant all on test.* to testdb_2@localhost with grant option;
+
+connect (testdb_1,localhost,testdb_1,,test);
+use testdb_1;
+create table t1 (f1 char(4));
+create view v1 as select f1 from t1;
+grant insert on v1 to testdb_2@localhost;
+
+create view v5 as select f1 from t1;
+grant select, show view on v5 to testdb_2@localhost;
+
+--error ER_SPECIFIC_ACCESS_DENIED_ERROR
+create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1;
+
+connection default;
+use testdb_1;
+create view v6 as select f1 from t1;
+grant select, show view on v6 to testdb_2@localhost;
+
+create table t2 (f1 char(4));
+create definer=`no_such_user`@`no_such_host` view v7 as select * from t2;
+
+show fields from testdb_1.v6;
+show create view testdb_1.v6;
+
+show create view testdb_1.v7;
+show fields from testdb_1.v7;
+
+connection testdb_1;
+
+create table t3 (f1 char(4), f2 char(4));
+create view v3 as select f1,f2 from t3;
+grant insert(f1), insert(f2) on v3 to testdb_2@localhost;
+
+connect (testdb_2,localhost,testdb_2,,test);
+create view v2 as select f1 from testdb_1.v1;
+create view v4 as select f1,f2 from testdb_1.v3;
+
+show fields from testdb_1.v5;
+show create view testdb_1.v5;
+
+show fields from testdb_1.v6;
+show create view testdb_1.v6;
+
+connection testdb_1;
+show fields from testdb_1.v7;
+show create view testdb_1.v7;
+
+revoke insert(f1) on v3 from testdb_2@localhost;
+revoke select,show view on v5 from testdb_2@localhost;
+connection default;
+use testdb_1;
+revoke select,show view on v6 from testdb_2@localhost;
+connection testdb_2;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+show fields from testdb_1.v5;
+--error ER_TABLEACCESS_DENIED_ERROR
+show create view testdb_1.v5;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+show fields from testdb_1.v6;
+--error ER_TABLEACCESS_DENIED_ERROR
+show create view testdb_1.v6;
+
+--error ER_TABLEACCESS_DENIED_ERROR
+show fields from testdb_1.v7;
+--error ER_TABLEACCESS_DENIED_ERROR
+show create view testdb_1.v7;
+
+show create view v4;
+#--error ER_VIEW_NO_EXPLAIN
+show fields from v4;
+
+show fields from v2;
+show fields from testdb_1.v1;
+show create view v2;
+--error ER_TABLEACCESS_DENIED_ERROR
+show create view testdb_1.v1;
+
+select table_name from information_schema.columns a
+where a.table_name = 'v2';
+select view_definition from information_schema.views a
+where a.table_name = 'v2';
+select view_definition from information_schema.views a
+where a.table_name = 'testdb_1.v1';
+
+--error ER_VIEW_INVALID
+select * from v2;
+
+connection default;
+use test;
+drop view testdb_1.v1, v2, testdb_1.v3, v4;
+drop database testdb_1;
+connection testdb_1;
+disconnect testdb_1;
+--source include/wait_until_disconnected.inc
+connection testdb_2;
+disconnect testdb_2;
+--source include/wait_until_disconnected.inc
+connection default;
+drop user testdb_1@localhost;
+drop user testdb_2@localhost;
+
+#
+# Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
+#
+create database testdb_1;
+create table testdb_1.t1 (a int);
+create view testdb_1.v1 as select * from testdb_1.t1;
+
+grant show view on testdb_1.* to mysqltest_1@localhost;
+grant select on testdb_1.v1 to mysqltest_1@localhost;
+
+connect (user1,localhost,mysqltest_1,,test);
+connection user1;
+select table_schema, table_name, view_definition from information_schema.views
+where table_name='v1';
+show create view testdb_1.v1;
+
+connection default;
+revoke select on testdb_1.v1 from mysqltest_1@localhost;
+connection user1;
+select table_schema, table_name, view_definition from information_schema.views
+where table_name='v1';
+--error ER_TABLEACCESS_DENIED_ERROR
+show create view testdb_1.v1;
+
+connection default;
+drop user mysqltest_1@localhost;
+drop database testdb_1;
+connection user1;
+disconnect user1;
+--source include/wait_until_disconnected.inc
+connection default;
+
+set global sql_mode=default;