diff options
Diffstat (limited to 'mysql-test/r/information_schema.result')
| -rw-r--r-- | mysql-test/r/information_schema.result | 65 |
1 files changed, 47 insertions, 18 deletions
diff --git a/mysql-test/r/information_schema.result b/mysql-test/r/information_schema.result index 5688d8c2145..6d8907fff00 100644 --- a/mysql-test/r/information_schema.result +++ b/mysql-test/r/information_schema.result @@ -1,4 +1,4 @@ -DROP TABLE IF EXISTS t0,t1,t2; +DROP TABLE IF EXISTS t0,t1,t2,t3,t5; show variables where variable_name like "skip_show_database"; Variable_name Value skip_show_database OFF @@ -30,6 +30,8 @@ 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; select * from v1; c @@ -70,6 +72,7 @@ t1 t4 t2 t3 +t5 v1 select c,table_name from v1 inner join information_schema.TABLES v2 on (v1.c=v2.table_name) @@ -89,6 +92,7 @@ t1 t1 t4 t4 t2 t2 t3 t3 +t5 t5 select c,table_name from v1 left join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; @@ -107,6 +111,7 @@ t1 t1 t4 t4 t2 t2 t3 t3 +t5 t5 select c, v2.table_name from v1 right join information_schema.TABLES v2 on (v1.c=v2.table_name) where v1.c like "t%"; @@ -125,6 +130,7 @@ t1 t1 t4 t4 t2 t2 t3 t3 +t5 t5 select table_name from information_schema.TABLES where table_schema = "mysqltest" and table_name like "t%"; table_name @@ -140,11 +146,13 @@ show tables like 't%'; Tables_in_test (t%) t2 t3 +t5 show table status; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL t3 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL -v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL view +t5 MyISAM 10 Fixed 1 7 7 # 2048 0 11 # # NULL latin1_swedish_ci NULL +v1 NULL NULL NULL NULL NULL NULL # NULL NULL NULL # # NULL NULL NULL NULL VIEW show full columns from t3 like "a%"; Field Type Collation Null Key Default Extra Privileges Comment a int(11) NULL YES MUL NULL select,insert,update,references @@ -157,7 +165,7 @@ c varchar(64) utf8_general_ci NO select,insert,update,references select * from information_schema.COLUMNS where table_name="t1" and column_name= "a"; TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION COLUMN_DEFAULT IS_NULLABLE DATA_TYPE CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH NUMERIC_PRECISION NUMERIC_SCALE CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT -NULL mysqltest t1 a 1 NULL YES int NULL NULL 10 NULL NULL NULL int(11) select,insert,update,references +NULL mysqltest t1 a 1 NULL YES int NULL NULL 10 0 NULL NULL int(11) select,insert,update,references show columns from mysqltest.t1 where field like "%a%"; Field Type Null Key Default Extra a int(11) YES NULL @@ -177,7 +185,7 @@ where table_schema = 'mysqltest' and table_name = 'v1'; table_name column_name privileges v1 c select drop view v1, mysqltest.v1; -drop tables mysqltest.t4, mysqltest.t1, t2, t3; +drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5; drop database mysqltest; select * from information_schema.CHARACTER_SETS where CHARACTER_SET_NAME like 'latin1%'; @@ -265,10 +273,10 @@ id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE # ALL NULL NULL NULL NULL 2 1 SIMPLE # ALL NULL NULL NULL NULL 2 Using where select a.ROUTINE_NAME, b.name from information_schema.ROUTINES a, -mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8); +mysql.proc b where a.ROUTINE_NAME = convert(b.name using utf8) order by 1; ROUTINE_NAME name -sub1 sub1 sel2 sel2 +sub1 sub1 select count(*) from information_schema.ROUTINES; count(*) 2 @@ -527,7 +535,7 @@ c float(5,2) NULL NULL 5 2 d decimal(6,4) NULL NULL 6 4 e float NULL NULL 12 NULL f decimal(6,3) NULL NULL 6 3 -g int(11) NULL NULL 10 NULL +g int(11) NULL NULL 10 0 h double(10,3) NULL NULL 10 3 i double NULL NULL 22 NULL drop table t1; @@ -577,8 +585,8 @@ select TABLE_NAME,TABLE_TYPE,ENGINE from information_schema.tables where table_schema='information_schema' limit 2; TABLE_NAME TABLE_TYPE ENGINE -CHARACTER_SETS TEMPORARY MEMORY -COLLATIONS TEMPORARY MEMORY +CHARACTER_SETS SYSTEM VIEW MEMORY +COLLATIONS SYSTEM VIEW MEMORY show tables from information_schema like "T%"; Tables_in_information_schema (T%) TABLES @@ -590,10 +598,10 @@ ERROR HY000: Can't create database 'information_schema'; database exists use information_schema; show full tables like "T%"; Tables_in_information_schema (T%) Table_type -TABLES TEMPORARY -TABLE_CONSTRAINTS TEMPORARY -TABLE_PRIVILEGES TEMPORARY -TRIGGERS TEMPORARY +TABLES SYSTEM VIEW +TABLE_CONSTRAINTS SYSTEM VIEW +TABLE_PRIVILEGES SYSTEM VIEW +TRIGGERS SYSTEM VIEW create table t1(a int); ERROR 42S02: Unknown table 't1' in information_schema use test; @@ -933,11 +941,11 @@ select column_name, NUMERIC_PRECISION, NUMERIC_SCALE from information_schema.columns where table_name='t1'; column_name NUMERIC_PRECISION NUMERIC_SCALE -f1 3 NULL -f2 5 NULL -f3 7 NULL -f4 10 NULL -f5 19 NULL +f1 3 0 +f2 5 0 +f3 7 0 +f4 10 0 +f5 19 0 f6 1 NULL f7 64 NULL drop table t1; @@ -950,3 +958,24 @@ trigger_schema trigger_name test tr1 use test; drop table t1; +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'; +column_name column_default +a NULL +b NULL +use test; +show columns from t1; +Field Type Null Key Default Extra +a int(11) NO +b int(11) YES NULL +drop table t1; +CREATE TABLE t1 (a int); +CREATE TABLE t2 (b int); +SHOW TABLE STATUS FROM test +WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES +WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE'); +Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment +t1 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL +t2 MyISAM 10 Fixed 0 0 0 # 1024 0 NULL # # NULL latin1_swedish_ci NULL |