diff options
Diffstat (limited to 'mysql-test/t/information_schema.test')
| -rw-r--r-- | mysql-test/t/information_schema.test | 151 |
1 files changed, 79 insertions, 72 deletions
diff --git a/mysql-test/t/information_schema.test b/mysql-test/t/information_schema.test index caf38945cbc..079f96777bf 100644 --- a/mysql-test/t/information_schema.test +++ b/mysql-test/t/information_schema.test @@ -36,11 +36,11 @@ insert into t5 values (10); create view v1 (c) as select table_name from information_schema.TABLES; select * from v1; -select c,table_name from v1 +select c,table_name from v1 inner join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; -select c,table_name from v1 +select c,table_name from v1 left join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; @@ -69,7 +69,7 @@ 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 +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); @@ -77,6 +77,7 @@ connection user4; select table_name, column_name, privileges from information_schema.columns where table_schema = 'mysqltest' and table_name = 'v1'; connection default; +disconnect user4; drop view v1, mysqltest.v1; drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5; @@ -126,7 +127,7 @@ delimiter ;| # # Bug#7222 information_schema: errors in "routines" # -select parameter_style, sql_data_access, dtd_identifier +select parameter_style, sql_data_access, dtd_identifier from information_schema.routines; --replace_column 5 # 6 # @@ -145,7 +146,7 @@ select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) order by 1; select count(*) from information_schema.ROUTINES; -create view v1 as select routine_schema, routine_name from information_schema.routines +create view v1 as select routine_schema, routine_name from information_schema.routines order by routine_schema, routine_name; select * from v1; drop view v1; @@ -153,7 +154,7 @@ drop view v1; connect (user1,localhost,mysqltest_1,,); connection user1; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; ---error 1305 +--error ER_SP_DOES_NOT_EXIST show create function sub1; connection user3; select ROUTINE_NAME, ROUTINE_DEFINITION from information_schema.ROUTINES; @@ -172,6 +173,7 @@ show create function sub2; show function status like "sub2"; connection default; disconnect user1; +disconnect user3; drop function sub2; show create procedure sel2; @@ -311,7 +313,7 @@ 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, +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; @@ -324,7 +326,7 @@ 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 1325 +--error ER_SP_CURSOR_ALREADY_OPEN call p108()// delimiter ;// drop procedure p108; @@ -334,24 +336,24 @@ where table_name= "user"; select * from v1; drop view v1; -create view vo as select 'a' union select 'a'; +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"; +TABLE_NAME= "vo"; drop view vo; select TABLE_NAME,TABLE_TYPE,ENGINE -from information_schema.tables +from information_schema.tables where table_schema='information_schema' limit 2; show tables from information_schema like "T%"; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create database information_schema; use information_schema; show full tables like "T%"; ---error 1109 +--error ER_UNKNOWN_TABLE create table t1(a int); use test; show tables; @@ -359,15 +361,15 @@ use information_schema; show tables like "T%"; # -# Bug#7210: information_schema: can't access when table-name = reserved word +# 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 +# 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 @@ -394,9 +396,9 @@ drop view v3; drop table t4; # -# Bug#7213: information_schema: redundant non-standard TABLE_NAMES table +# Bug#7213 information_schema: redundant non-standard TABLE_NAMES table # ---error 1109 +--error ER_UNKNOWN_TABLE select * from information_schema.table_names; # @@ -409,7 +411,7 @@ where table_schema="information_schema" and table_name="COLUMNS" and # # Bug#2718 information_schema: errors in "tables" # -select TABLE_ROWS from information_schema.tables where +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"; @@ -422,14 +424,14 @@ show status where variable_name like "%database%"; show variables where variable_name like "skip_show_databas"; # -# Bug #7981:SHOW GLOBAL STATUS crashes server +# 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, +# Bug#7915 crash,JOIN VIEW, subquery, # SELECT .. FROM INFORMATION_SCHEMA.COLUMNS # create table t1(f1 int); @@ -440,7 +442,7 @@ drop view v1; drop table t1, t2; # -# Bug #7476: crash on SELECT * FROM INFORMATION_SCHEMA.TABLES +# Bug#7476 crash on SELECT * FROM INFORMATION_SCHEMA.TABLES # CREATE TABLE t_crashme ( f1 BIGINT); @@ -468,26 +470,26 @@ 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 +# 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 +information_schema.columns where data_type = 'longtext'; select table_name, column_name, data_type from information_schema.columns where data_type = 'datetime'; # -# Bug #8164 subquery with INFORMATION_SCHEMA.COLUMNS, 100 % CPU +# Bug#8164 subquery with INFORMATION_SCHEMA.COLUMNS, 100 % CPU # SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A -WHERE NOT EXISTS +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 +# Bug#9344 INFORMATION_SCHEMA, wrong content, numeric columns # create table t1 @@ -505,21 +507,22 @@ WHERE TABLE_NAME= 't1'; drop table t1; # -# Bug#10261 INFORMATION_SCHEMA.COLUMNS, incomplete result for non root user +# 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; -SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS +SELECT TABLE_NAME, COLUMN_NAME, PRIVILEGES FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME='TABLE_NAME'; connection default; +disconnect user10261; delete from mysql.user where user='mysqltest_4'; delete from mysql.db where user='mysqltest_4'; flush privileges; # -# Bug #9404 information_schema: Weird error messages +# Bug#9404 information_schema: Weird error messages # with SELECT SUM() ... GROUP BY queries # SELECT table_schema, count(*) FROM information_schema.TABLES GROUP BY TABLE_SCHEMA; @@ -560,7 +563,7 @@ drop table t1; # -# Bug #10964 Information Schema:Authorization check on privilege tables is improper +# Bug#10964 Information Schema:Authorization check on privilege tables is improper # create database mysqltest; @@ -604,12 +607,16 @@ 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 +# Bug#11055 information_schema: routines.sql_data_access has wrong value # --disable_warnings drop procedure if exists p1; @@ -624,13 +631,13 @@ drop procedure p1; drop procedure p2; # -# Bug #9434 SHOW CREATE DATABASE information_schema; +# 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 +# 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, @@ -646,7 +653,7 @@ 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 +# 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; @@ -668,8 +675,8 @@ show columns from t1; drop table t1; # -# Bug #12636: SHOW TABLE STATUS with where condition containing a subquery -# over information schema +# Bug#12636 SHOW TABLE STATUS with where condition containing a subquery +# over information schema # CREATE TABLE t1 (a int); @@ -683,7 +690,7 @@ SHOW TABLE STATUS FROM test DROP TABLE t1,t2; # -# Bug #12905 show fields from view behaving erratically with current database +# 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; @@ -691,28 +698,29 @@ 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' +# Bug#9846 Inappropriate error displayed while dropping table from 'INFORMATION_SCHEMA' # --error ER_PARSE_ERROR alter database information_schema; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR drop database information_schema; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR drop table information_schema.tables; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR alter table information_schema.tables; # -# Bug #9683 INFORMATION_SCH: Creation of temporary table allowed in Information_schema DB +# Bug#9683 INFORMATION_SCH: Creation of temporary table allowed in Information_schema DB # use information_schema; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR create temporary table schemata(f1 char(10)); # -# Bug #10708 SP's can use INFORMATION_SCHEMA as ROUTINE_SCHEMA +# Bug#10708 SP's can use INFORMATION_SCHEMA as ROUTINE_SCHEMA # delimiter |; --error ER_BAD_DB_ERROR @@ -721,13 +729,13 @@ BEGIN SELECT 'foo' FROM DUAL; END | delimiter ;| -select ROUTINE_NAME from routines; +select ROUTINE_NAME from routines; # -# Bug #10734 Grant of privileges other than 'select' and 'create view' should fail on schema +# Bug#10734 Grant of privileges other than 'select' and 'create view' should fail on schema # ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant all on information_schema.* to 'user1'@'localhost'; ---error 1044 +--error ER_DBACCESS_DENIED_ERROR grant select on information_schema.* to 'user1'@'localhost'; # @@ -753,9 +761,9 @@ 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 +# 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; @@ -839,6 +847,7 @@ connection con16681; select * from information_schema.views where table_name='v1' or table_name='v2'; connection default; +disconnect con16681; drop view v1, v2; drop table t1; drop user mysqltest_1@localhost; @@ -855,7 +864,7 @@ drop table t1,t2; # -# Bug#20230: routine_definition is not null +# Bug#20230 routine_definition is not null # --disable_warnings DROP PROCEDURE IF EXISTS p1; @@ -888,7 +897,7 @@ DROP PROCEDURE p1; DROP USER mysql_bug20230@localhost; # -# Bug#18925: subqueries with MIN/MAX functions on INFORMARTION_SCHEMA +# Bug#18925 subqueries with MIN/MAX functions on INFORMARTION_SCHEMA # SELECT t.table_name, c1.column_name @@ -921,8 +930,8 @@ SELECT t.table_name, c1.column_name ); # -# Bug#21231: query with a simple non-correlated subquery over -# INFORMARTION_SCHEMA.TABLES +# Bug#2123 query with a simple non-correlated subquery over +# INFORMARTION_SCHEMA.TABLES # SELECT MAX(table_name) FROM information_schema.tables; @@ -931,7 +940,7 @@ SELECT table_name from information_schema.tables FROM information_schema.tables); # -# Bug #23037: Bug in field "Default" of query "SHOW COLUMNS FROM table" +# 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) @@ -971,11 +980,8 @@ SELECT COLUMN_NAME, MD5(COLUMN_DEFAULT), LENGTH(COLUMN_DEFAULT), COLUMN_DEFAULT= DROP TABLE bug23037; DROP FUNCTION get_value; - - - # -# Bug#22413: EXPLAIN SELECT FROM view with ORDER BY yield server crash +# Bug#22413 EXPLAIN SELECT FROM view with ORDER BY yield server crash # create view v1 as select table_schema as object_schema, @@ -1001,7 +1007,7 @@ drop table t1,t2; # -# Bug#24630 Subselect query crashes mysqld +# Bug#24630 Subselect query crashes mysqld # select 1 as f1 from information_schema.tables where "CHARACTER_SETS"= (select cast(table_name as char) from information_schema.tables @@ -1028,8 +1034,8 @@ group by t.table_name order by num1, t.table_name; # 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; +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; # # Note: we can perform 'delete' for non updatable view. @@ -1039,7 +1045,7 @@ drop view v1,v2; drop table t1,t2; # -# Bug#25859 ALTER DATABASE works w/o parameters +# Bug#25859 ALTER DATABASE works w/o parameters # --error ER_PARSE_ERROR alter database; @@ -1068,6 +1074,7 @@ 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; @@ -1111,13 +1118,13 @@ select * from `information_schema`.`VIEWS` where `TABLE_NAME` = NULL; # # Bug#30079 A check for "hidden" I_S tables is flawed # ---error 1109 +--error ER_UNKNOWN_TABLE show fields from information_schema.table_names; ---error 1109 +--error ER_UNKNOWN_TABLE show keys from information_schema.table_names; # -# Bug#34529: Crash on complex Falcon I_S select after ALTER .. PARTITION BY +# Bug#34529 Crash on complex Falcon I_S select after ALTER .. PARTITION BY # USE information_schema; SET max_heap_table_size = 16384; @@ -1126,9 +1133,9 @@ 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 +# 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 +# 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 |