#Don't run this test when thread_pool active --source include/not_threadpool.inc -- source include/testdb_only.inc set @orig_sql_mode= @@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 use INFORMATION_SCHEMA; sorted_result; --replace_result Tables_in_INFORMATION_SCHEMA Tables_in_information_schema show tables where Tables_in_INFORMATION_SCHEMA NOT LIKE 'INNODB%' and Tables_in_INFORMATION_SCHEMA NOT LIKE 'ndb%'; sorted_result; --replace_result 'Tables_in_INFORMATION_SCHEMA (T%)' 'Tables_in_information_schema (T%)' 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). # create user 'mysqltest_1'@'localhost'; 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'; drop table t1; select table_name, table_type, table_comment from information_schema.tables where table_schema='test'; 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; create user mysqltest_1@localhost; 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; set sql_mode= @orig_sql_mode; connection user1; disconnect user1; --source include/wait_until_disconnected.inc connection default; --echo # --echo # Bug#30158484 FULLY QUALIFIED COLUMN NAMES IN WHERE CLAUSE ARE --echo # FAILING WITH INFORMATION_SCHEMA TABLES IN 8.0 --echo # --echo # Case 1: INFORMATION_SCHEMA name in SELECT clause with mixed case. --echo # --echo # These commands fail with ER_BAD_FIELD_ERROR without the fix. SELECT INFORMATION_schema.TABLES.TABLE_NAME, information_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, information_schema.TABLES.TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='non_existing_table'; SELECT INFORMATION_schema.TABLES.TABLE_NAME, information_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, information_schema.TABLES.TABLE_NAME FROM information_schema.TABLES WHERE TABLE_NAME='non_existing_table'; SELECT INFORMATION_schema.TABLES.TABLE_NAME, information_SCHEMA.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME, information_schema.TABLES.TABLE_NAME FROM information_SCHEMA.TABLES WHERE TABLE_NAME='non_existing_table'; --echo # --echo # Case 2: INFORMATION_SCHEMA name used to specify '*' in SELECT --echo # clause with mixed case. --echo # SELECT INFORMATION_SCHEMA.TABLES.* FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='non_existing_table'; SELECT information_schema.TABLES.* FROM information_schema.TABLES WHERE TABLE_NAME='non_existing_table'; SELECT INFORMATION_SCHEMA.TABLES.* FROM information_schema.TABLES WHERE TABLE_NAME='non_existing_table'; SELECT INFORMATION_schema.TABLES.* FROM information_SCHEMA.TABLES WHERE TABLE_NAME='non_existing_table'; --echo # --echo # Case 3: INFORMATION_SCHEMA name in WHERE clause with mixed case. --echo # SELECT COUNT(*) > 1 FROM INFORMATION_SCHEMA.TABLES WHERE INFORMATION_schema.TABLES.TABLE_NAME LIKE 'a%' AND information_SCHEMA.TABLES.TABLE_NAME LIKE 'b%' AND information_schema.TABLES.TABLE_NAME LIKE 'c%' AND INFORMATION_SCHEMA.TABLES.TABLE_NAME LIKE 'd%'; SELECT COUNT(*) > 1 FROM information_schema.TABLES WHERE INFORMATION_schema.TABLES.TABLE_NAME LIKE 'a%' AND information_SCHEMA.TABLES.TABLE_NAME LIKE 'b%' AND information_schema.TABLES.TABLE_NAME LIKE 'c%' AND INFORMATION_SCHEMA.TABLES.TABLE_NAME LIKE 'd%'; SELECT COUNT(*) > 1 FROM INFORMATION_schema.TABLES WHERE INFORMATION_schema.TABLES.TABLE_NAME LIKE 'a%' AND information_SCHEMA.TABLES.TABLE_NAME LIKE 'b%' AND information_schema.TABLES.TABLE_NAME LIKE 'c%' AND INFORMATION_SCHEMA.TABLES.TABLE_NAME LIKE 'd%'; --echo # --echo # Case 4: INFORMATION_SCHEMA name in ORDER BY clause with mixed case. --echo # SELECT COUNT(*) > 1 FROM INFORMATION_SCHEMA.TABLES ORDER BY INFORMATION_schema.TABLES.TABLE_NAME, information_SCHEMA.TABLES.TABLE_NAME, information_schema.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME; SELECT COUNT(*) > 1 FROM information_schema.TABLES ORDER BY INFORMATION_schema.TABLES.TABLE_NAME, information_SCHEMA.TABLES.TABLE_NAME, information_schema.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME; SELECT COUNT(*) > 1 FROM INFORMATION_schema.TABLES ORDER BY INFORMATION_schema.TABLES.TABLE_NAME, information_SCHEMA.TABLES.TABLE_NAME, information_schema.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME; --echo # --echo # Case 5: INFORMATION_SCHEMA name in GROUP BY clause with mixed case. --echo # SELECT COUNT(*) > 1 FROM INFORMATION_SCHEMA.TABLES GROUP BY INFORMATION_schema.TABLES.TABLE_NAME, information_SCHEMA.TABLES.TABLE_NAME, information_schema.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME LIMIT 1; SELECT COUNT(*) > 1 FROM information_schema.TABLES GROUP BY INFORMATION_schema.TABLES.TABLE_NAME, information_SCHEMA.TABLES.TABLE_NAME, information_schema.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME LIMIT 1; SELECT COUNT(*) > 1 FROM INFORMATION_schema.TABLES GROUP BY INFORMATION_schema.TABLES.TABLE_NAME, information_SCHEMA.TABLES.TABLE_NAME, information_schema.TABLES.TABLE_NAME, INFORMATION_SCHEMA.TABLES.TABLE_NAME LIMIT 1; --echo # --echo # Case 6: Test behavior using INFORMATION_SCHEMA name, I_S table name --echo # and I_S column name with mixed cases. --echo # # EXPECTATION: # - INFORMATION_SCHEMA name is treated as case-insensitive, in any of SELECT # clause. # # - I_S COLUMN name is treated as case-insensitive, in any of SELECT clause. # # - I_S table name is allowed with mixed case in FROM clause. However, the # name used in the FROM clause should match the I_S table name used in # other SELECT clause or else we get ER_BAD_FIELD_ERROR in setting # lower_case_table_names=0. The I_S table name are treated as case # insensitive in setting lower_case_table_names > 0. --let $error_code= `SELECT IF(@@lower_case_table_names>0,'0','ER_BAD_FIELD_ERROR')` --disable_result_log SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = 'unknowdb'; SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE INFORMATION_SCHEMA.SCHEMATA.schema_name = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE INFORMATION_SCHEMA.schemata.SCHEMA_NAME = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE INFORMATION_SCHEMA.schemata.schema_name = 'unknowdb'; SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE information_schema.SCHEMATA.SCHEMA_NAME = 'unknowdb'; SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE information_schema.SCHEMATA.schema_name = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE information_schema.schemata.SCHEMA_NAME = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE information_schema.schemata.schema_name = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata WHERE INFORMATION_SCHEMA.SCHEMATA.schema_name = 'unknowdb'; SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata WHERE INFORMATION_SCHEMA.schemata.SCHEMA_NAME = 'unknowdb'; SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata WHERE INFORMATION_SCHEMA.schemata.schema_name = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata WHERE information_schema.SCHEMATA.SCHEMA_NAME = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata WHERE information_schema.SCHEMATA.schema_name = 'unknowdb'; SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata WHERE information_schema.schemata.SCHEMA_NAME = 'unknowdb'; SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata WHERE information_schema.schemata.schema_name = 'unknowdb'; SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = 'unknowdb'; SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE INFORMATION_SCHEMA.SCHEMATA.schema_name = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE INFORMATION_SCHEMA.schemata.SCHEMA_NAME = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE INFORMATION_SCHEMA.schemata.schema_name = 'unknowdb'; SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE information_schema.SCHEMATA.SCHEMA_NAME = 'unknowdb'; SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE information_schema.SCHEMATA.schema_name = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE information_schema.schemata.SCHEMA_NAME = 'unknowdb'; --error $error_code SELECT SCHEMA_NAME FROM information_schema.SCHEMATA WHERE information_schema.schemata.schema_name = 'unknowdb'; --enable_result_log