diff options
Diffstat (limited to 'mysql-test/r/information_schema.result')
| -rw-r--r-- | mysql-test/r/information_schema.result | 45 |
1 files changed, 29 insertions, 16 deletions
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 85af461eb14..6320b6d7ad3 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -9,11 +9,13 @@ create user mysqltest_3@localhost; create user mysqltest_3; select * from information_schema.SCHEMATA where schema_name > 'm'; CATALOG_NAME SCHEMA_NAME DEFAULT_CHARACTER_SET_NAME DEFAULT_COLLATION_NAME SQL_PATH +NULL mtr latin1 latin1_swedish_ci NULL NULL mysql latin1 latin1_swedish_ci NULL NULL test latin1 latin1_swedish_ci NULL select schema_name from information_schema.schemata; schema_name information_schema +mtr mysql test show databases like 't%'; @@ -22,6 +24,7 @@ test show databases; Database information_schema +mtr mysql test show databases where `database` = 't%'; @@ -33,7 +36,11 @@ 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_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status'; +create view v1 (c) as +SELECT table_name FROM information_schema.TABLES +WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND +table_name<>'ndb_binlog_index' AND +table_name<>'ndb_apply_status'; select * from v1; c CHARACTER_SETS @@ -269,19 +276,19 @@ select * from t1; select * from t2; end| select parameter_style, sql_data_access, dtd_identifier -from information_schema.routines; +from information_schema.routines where routine_schema='test'; parameter_style sql_data_access dtd_identifier SQL CONTAINS SQL NULL SQL CONTAINS SQL int(11) -show procedure status; +show procedure status where db='test'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation test sel2 PROCEDURE root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci -show function status; +show function status where db='test'; Db Name Type Definer Modified Created Security_type Comment character_set_client collation_connection Database Collation test sub1 FUNCTION root@localhost # # DEFINER latin1 latin1_swedish_ci latin1_swedish_ci select a.ROUTINE_NAME from information_schema.ROUTINES a, information_schema.SCHEMATA b where -a.ROUTINE_SCHEMA = b.SCHEMA_NAME; +a.ROUTINE_SCHEMA = b.SCHEMA_NAME AND b.SCHEMA_NAME='test'; ROUTINE_NAME sel2 sub1 @@ -292,14 +299,14 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE # ALL NULL NULL NULL NULL NULL 1 SIMPLE # ALL NULL NULL NULL NULL NULL Using where; Using join buffer 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; +mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) AND a.ROUTINE_SCHEMA='test' order by 1; ROUTINE_NAME name sel2 sel2 sub1 sub1 -select count(*) from information_schema.ROUTINES; +select count(*) from information_schema.ROUTINES where routine_schema='test'; count(*) 2 -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 where routine_schema='test' order by routine_schema, routine_name; select * from v1; routine_schema routine_name @@ -351,6 +358,7 @@ create view v0 (c) as select schema_name from information_schema.schemata; select * from v0; c information_schema +mtr mysql test explain select * from v0; @@ -842,7 +850,7 @@ VIEWS TABLE_NAME select delete from mysql.user where user='mysqltest_4'; delete from mysql.db where user='mysqltest_4'; flush privileges; -SELECT table_schema, count(*) FROM information_schema.TABLES where table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA; +SELECT table_schema, count(*) FROM information_schema.TABLES WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test', 'mysqltest') AND table_name<>'ndb_binlog_index' AND table_name<>'ndb_apply_status' GROUP BY TABLE_SCHEMA; table_schema count(*) information_schema 28 mysql 22 @@ -882,7 +890,7 @@ if new.j = -1 then set @fired:= "Yes"; end if; end AFTER NULL root@localhost latin1 latin1_swedish_ci latin1_swedish_ci -select * from information_schema.triggers; +select * from information_schema.triggers where trigger_schema in ('mysql', 'information_schema', 'test', 'mysqltest'); TRIGGER_CATALOG TRIGGER_SCHEMA TRIGGER_NAME EVENT_MANIPULATION EVENT_OBJECT_CATALOG EVENT_OBJECT_SCHEMA EVENT_OBJECT_TABLE ACTION_ORDER ACTION_CONDITION ACTION_STATEMENT ACTION_ORIENTATION ACTION_TIMING ACTION_REFERENCE_OLD_TABLE ACTION_REFERENCE_NEW_TABLE ACTION_REFERENCE_OLD_ROW ACTION_REFERENCE_NEW_ROW CREATED SQL_MODE DEFINER CHARACTER_SET_CLIENT COLLATION_CONNECTION DATABASE_COLLATION NULL test trg1 INSERT NULL test t1 0 NULL begin if new.j > 10 then @@ -1071,7 +1079,7 @@ BEGIN SELECT 'foo' FROM DUAL; END | ERROR 42000: Unknown database 'information_schema' -select ROUTINE_NAME from routines; +select ROUTINE_NAME from routines where ROUTINE_SCHEMA='information_schema'; ROUTINE_NAME grant all on information_schema.* to 'user1'@'localhost'; ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema' @@ -1153,7 +1161,7 @@ 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'); -select routine_name from information_schema.routines; +select routine_name from information_schema.routines where ROUTINE_SCHEMA='test'; routine_name delete from proc where name=''; @@ -1187,7 +1195,7 @@ 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; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test'; ROUTINE_NAME ROUTINE_DEFINITION f1 RETURN @a + 1 p1 SET @a= 1 @@ -1199,7 +1207,7 @@ SHOW CREATE FUNCTION f1; Function sql_mode Create Function character_set_client collation_connection Database Collation f1 CREATE DEFINER=`root`@`localhost` FUNCTION `f1`() RETURNS int(11) RETURN @a + 1 latin1 latin1_swedish_ci latin1_swedish_ci -SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES; +SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA='test'; ROUTINE_NAME ROUTINE_DEFINITION f1 NULL p1 NULL @@ -1302,12 +1310,12 @@ TABLE_PRIVILEGES TABLE_SCHEMA TRIGGERS TRIGGER_SCHEMA USER_PRIVILEGES GRANTEE VIEWS TABLE_SCHEMA -SELECT MAX(table_name) FROM information_schema.tables; +SELECT MAX(table_name) FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test'); MAX(table_name) VIEWS SELECT table_name from information_schema.tables WHERE table_name=(SELECT MAX(table_name) -FROM information_schema.tables); +FROM information_schema.tables WHERE table_schema IN ('mysql', 'INFORMATION_SCHEMA', 'test')); table_name VIEWS DROP TABLE IF EXISTS bug23037; @@ -1707,4 +1715,9 @@ where a.VARIABLE_NAME = b.VARIABLE_NAME; a.VARIABLE_VALUE - b.VARIABLE_VALUE 2 drop table t0; +CREATE TABLE t1(a INT) KEY_BLOCK_SIZE=1; +SELECT CREATE_OPTIONS FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='t1'; +CREATE_OPTIONS +key_block_size=1 +DROP TABLE t1; End of 5.1 tests. |