← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/information_schema.result

  • Committer: Brian Aker
  • Date: 2008-07-13 21:20:24 UTC
  • Revision ID: brian@tangent.org-20080713212024-o6263c1vha7yxdeu
More bool removal. More cow bell!

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/information_schema.result
1
1
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
 
2
show variables where variable_name like "skip_show_database";
 
3
Variable_name   Value
 
4
skip_show_database      OFF
2
5
select * from information_schema.SCHEMATA where schema_name > 'm';
3
6
CATALOG_NAME    SCHEMA_NAME     DEFAULT_CHARACTER_SET_NAME      DEFAULT_COLLATION_NAME  SQL_PATH
4
 
NULL    mysql   utf8    utf8_general_ci NULL
5
 
NULL    test    utf8    utf8_general_ci NULL
 
7
NULL    mysql   latin1  latin1_swedish_ci       NULL
 
8
NULL    test    latin1  latin1_swedish_ci       NULL
6
9
select schema_name from information_schema.schemata;
7
10
schema_name
8
11
information_schema
16
19
information_schema
17
20
mysql
18
21
test
 
22
show databases where `database` = 't%';
 
23
Database
19
24
create database mysqltest;
20
25
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
21
26
create table test.t2(a int);
30
35
t4
31
36
select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
32
37
TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      NON_UNIQUE      INDEX_SCHEMA    INDEX_NAME      SEQ_IN_INDEX    COLUMN_NAME     COLLATION       CARDINALITY     SUB_PART        PACKED  NULLABLE        INDEX_TYPE      COMMENT INDEX_COMMENT
33
 
NULL    mysqltest       t1      1       mysqltest       string_data     1       b       A       0       NULL    NULL    YES     BTREE           
 
38
NULL    mysqltest       t1      1       mysqltest       string_data     1       b       A       NULL    NULL    NULL    YES     BTREE           
 
39
show keys from t3 where Key_name = "a_data";
 
40
Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_Comment
 
41
t3      1       a_data  1       a       A       NULL    NULL    NULL    YES     BTREE           
34
42
show tables like 't%';
35
43
Tables_in_test (t%)
36
44
t2
38
46
t5
39
47
show table status;
40
48
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
41
 
t2      InnoDB  10      Compact 0       0       16384   #       0       0       NULL    #       #       NULL    utf8_general_ci NULL            
42
 
t3      InnoDB  10      Compact 0       0       16384   #       16384   0       NULL    #       #       NULL    utf8_general_ci NULL            
43
 
t5      InnoDB  10      Compact 1       16384   16384   #       0       0       11      #       #       NULL    utf8_general_ci NULL            
 
49
t2      MyISAM  10      Fixed   0       0       0       #       1024    0       NULL    #       #       NULL    latin1_swedish_ci       NULL            
 
50
t3      MyISAM  10      Fixed   0       0       0       #       1024    0       NULL    #       #       NULL    latin1_swedish_ci       NULL            
 
51
t5      MyISAM  10      Fixed   1       7       7       #       2048    0       11      #       #       NULL    latin1_swedish_ci       NULL            
44
52
show full columns from t3 like "a%";
45
53
Field   Type    Collation       Null    Key     Default Extra   Privileges      Comment
46
 
a       int     NULL    YES     MUL     NULL                    
 
54
a       int(11) NULL    YES     MUL     NULL                    
47
55
select * from information_schema.COLUMNS where table_name="t1"
48
56
and column_name= "a";
49
57
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  STORAGE FORMAT
50
 
NULL    mysqltest       t1      a       1       NULL    YES     int     NULL    NULL    10      0       NULL    NULL    int                                     Default Default
 
58
NULL    mysqltest       t1      a       1       NULL    YES     int     NULL    NULL    10      0       NULL    NULL    int(11)                                 Default Default
 
59
show columns from mysqltest.t1 where field like "%a%";
 
60
Field   Type    Null    Key     Default Extra
 
61
a       int(11) YES             NULL    
51
62
select table_name, column_name, privileges from information_schema.columns 
52
63
where table_schema = 'mysqltest' and table_name = 't1';
53
64
table_name      column_name     privileges
55
66
t1      b       
56
67
show columns from mysqltest.t1;
57
68
Field   Type    Null    Key     Default Extra
58
 
a       int     YES             NULL    
 
69
a       int(11) YES             NULL    
59
70
b       varchar(30)     YES     MUL     NULL    
60
71
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
61
72
drop database mysqltest;
62
73
select * from information_schema.CHARACTER_SETS
63
74
where CHARACTER_SET_NAME like 'latin1%';
64
75
CHARACTER_SET_NAME      DEFAULT_COLLATE_NAME    DESCRIPTION     MAXLEN
 
76
latin1  latin1_swedish_ci       cp1252 West European    1
 
77
SHOW CHARACTER SET LIKE 'latin1%';
 
78
Charset Description     Default collation       Maxlen
 
79
latin1  cp1252 West European    latin1_swedish_ci       1
 
80
SHOW CHARACTER SET WHERE charset like 'latin1%';
 
81
Charset Description     Default collation       Maxlen
 
82
latin1  cp1252 West European    latin1_swedish_ci       1
65
83
select * from information_schema.COLLATIONS
66
84
where COLLATION_NAME like 'latin1%';
67
85
COLLATION_NAME  CHARACTER_SET_NAME      ID      IS_DEFAULT      IS_COMPILED     SORTLEN
 
86
latin1_german1_ci       latin1  5               #       1
 
87
latin1_swedish_ci       latin1  8       Yes     #       1
 
88
latin1_danish_ci        latin1  15              #       1
 
89
latin1_german2_ci       latin1  31              #       2
 
90
latin1_bin      latin1  47              #       1
 
91
latin1_general_ci       latin1  48              #       1
 
92
latin1_general_cs       latin1  49              #       1
 
93
latin1_spanish_ci       latin1  94              #       1
 
94
SHOW COLLATION LIKE 'latin1%';
 
95
Collation       Charset Id      Default Compiled        Sortlen
 
96
latin1_german1_ci       latin1  5               #       1
 
97
latin1_swedish_ci       latin1  8       Yes     #       1
 
98
latin1_danish_ci        latin1  15              #       1
 
99
latin1_german2_ci       latin1  31              #       2
 
100
latin1_bin      latin1  47              #       1
 
101
latin1_general_ci       latin1  48              #       1
 
102
latin1_general_cs       latin1  49              #       1
 
103
latin1_spanish_ci       latin1  94              #       1
 
104
SHOW COLLATION WHERE collation like 'latin1%';
 
105
Collation       Charset Id      Default Compiled        Sortlen
 
106
latin1_german1_ci       latin1  5               #       1
 
107
latin1_swedish_ci       latin1  8       Yes     #       1
 
108
latin1_danish_ci        latin1  15              #       1
 
109
latin1_german2_ci       latin1  31              #       2
 
110
latin1_bin      latin1  47              #       1
 
111
latin1_general_ci       latin1  48              #       1
 
112
latin1_general_cs       latin1  49              #       1
 
113
latin1_spanish_ci       latin1  94              #       1
68
114
select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
69
115
where COLLATION_NAME like 'latin1%';
70
116
COLLATION_NAME  CHARACTER_SET_NAME
 
117
latin1_german1_ci       latin1
 
118
latin1_swedish_ci       latin1
 
119
latin1_danish_ci        latin1
 
120
latin1_german2_ci       latin1
 
121
latin1_bin      latin1
 
122
latin1_general_ci       latin1
 
123
latin1_general_cs       latin1
 
124
latin1_spanish_ci       latin1
71
125
select * from information_schema.table_names;
72
126
ERROR 42S02: Unknown table 'table_names' in information_schema
73
127
select column_type from information_schema.columns
83
137
select table_type from information_schema.tables
84
138
where table_schema="mysql" and table_name="user";
85
139
table_type
 
140
show open tables where `table` like "user";
 
141
Database        Table   In_use  Name_locked
 
142
show status where variable_name like "%database%";
 
143
Variable_name   Value
 
144
Com_show_databases      3
 
145
show variables where variable_name like "skip_show_databas";
 
146
Variable_name   Value
86
147
show global status like "Threads_running";
87
148
Variable_name   Value
88
149
Threads_running #
90
151
information_schema.columns 
91
152
where data_type = 'longtext';
92
153
table_schema    table_name      column_name
 
154
information_schema      COLUMNS COLUMN_DEFAULT
 
155
information_schema      COLUMNS COLUMN_TYPE
 
156
information_schema      PLUGINS PLUGIN_DESCRIPTION
 
157
information_schema      PROCESSLIST     INFO
93
158
select table_name, column_name, data_type from information_schema.columns
94
159
where data_type = 'datetime';
95
160
table_name      column_name     data_type
106
171
create table t1
107
172
( x_bigint BIGINT,
108
173
x_integer INTEGER,
109
 
x_int int,
 
174
x_smallint SMALLINT,
110
175
x_decimal DECIMAL(5,3),
111
176
x_numeric NUMERIC(5,3),
112
177
x_real REAL,
118
183
COLUMN_NAME     CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH
119
184
x_bigint        NULL    NULL
120
185
x_integer       NULL    NULL
121
 
x_int   NULL    NULL
 
186
x_smallint      NULL    NULL
122
187
x_decimal       NULL    NULL
123
188
x_numeric       NULL    NULL
124
189
x_real  NULL    NULL
131
196
information_schema      16
132
197
show create database information_schema;
133
198
Database        Create Database
134
 
information_schema      CREATE DATABASE `information_schema` /*!40100 DEFAULT CHARACTER SET utf8 */
 
199
information_schema      CREATE DATABASE "information_schema" /*!40100 DEFAULT CHARACTER SET utf8 */
135
200
create table t1(f1 LONGBLOB, f2 LONGTEXT);
136
201
select column_name,data_type,CHARACTER_OCTET_LENGTH,
137
202
CHARACTER_MAXIMUM_LENGTH
138
203
from information_schema.columns
139
204
where table_name='t1';
140
205
column_name     data_type       CHARACTER_OCTET_LENGTH  CHARACTER_MAXIMUM_LENGTH
141
 
f1      blob    4294967295      4294967295
142
 
f2      text    4294967295      4294967295
 
206
f1      longblob        4294967295      4294967295
 
207
f2      longtext        4294967295      4294967295
143
208
drop table t1;
144
 
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
145
 
f5 BIGINT, f6 int, f7 int);
 
209
create table t1(f1 tinyint, f2 SMALLINT, f3 BIGINT, f4 int,
 
210
f5 BIGINT, f6 TINYINT, f7 SMALLINT);
146
211
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
147
212
from information_schema.columns
148
213
where table_name='t1';
149
214
column_name     NUMERIC_PRECISION       NUMERIC_SCALE
150
 
f1      10      0
151
 
f2      10      0
 
215
f1      3       0
 
216
f2      5       0
152
217
f3      19      0
153
218
f4      10      0
154
219
f5      19      0
155
 
f6      10      0
156
 
f7      10      0
 
220
f6      3       0
 
221
f7      5       0
157
222
drop table t1;
158
223
create table t1 (a int not null, b int);
159
224
use information_schema;
165
230
use test;
166
231
show columns from t1;
167
232
Field   Type    Null    Key     Default Extra
168
 
a       int     NO              NULL    
169
 
b       int     YES             NULL    
 
233
a       int(11) NO              NULL    
 
234
b       int(11) YES             NULL    
170
235
drop table t1;
 
236
CREATE TABLE t1 (a int);
 
237
CREATE TABLE t2 (b int);
 
238
SHOW TABLE STATUS FROM test
 
239
WHERE name IN ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
 
240
WHERE TABLE_SCHEMA='test' AND TABLE_TYPE='BASE TABLE');
 
241
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
 
242
t1      MyISAM  10      Fixed   0       0       0       #       1024    0       NULL    #       #       NULL    latin1_swedish_ci       NULL            
 
243
t2      MyISAM  10      Fixed   0       0       0       #       1024    0       NULL    #       #       NULL    latin1_swedish_ci       NULL            
 
244
DROP TABLE t1,t2;
171
245
alter database information_schema;
172
246
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
173
247
drop database information_schema;
194
268
create temporary table t1(f1 int, index(f1));
195
269
show columns from t1;
196
270
Field   Type    Null    Key     Default Extra
197
 
f1      int     YES     MUL     NULL    
 
271
f1      int(11) YES     MUL     NULL    
198
272
describe t1;
199
273
Field   Type    Null    Key     Default Extra
200
 
f1      int     YES     MUL     NULL    
 
274
f1      int(11) YES     MUL     NULL    
201
275
show indexes from t1;
202
276
Table   Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_Comment
203
 
t1      1       f1      1       f1      A       0       NULL    NULL    YES     BTREE           
 
277
t1      1       f1      1       f1      A       NULL    NULL    NULL    YES     BTREE           
204
278
drop table t1;
205
 
create table t1(f1 varbinary(32), f2 varbinary(64));
 
279
create table t1(f1 binary(32), f2 varbinary(64));
206
280
select character_maximum_length, character_octet_length
207
281
from information_schema.columns where table_name='t1';
208
282
character_maximum_length        character_octet_length
216
290
 or column_type = 'varchar(27)')
217
291
group by column_type order by num;
218
292
column_type     group_concat(table_schema, '.', table_name)     num
219
 
varchar(20)     information_schema.PLUGINS      1
220
293
varchar(27)     information_schema.COLUMNS      1
221
 
create table t1(f1 char(1) not null, f2 char(9) not null);
 
294
varchar(20)     information_schema.PLUGINS,information_schema.PLUGINS,information_schema.PLUGINS        3
 
295
create table t1(f1 char(1) not null, f2 char(9) not null)
 
296
default character set utf8;
222
297
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
223
298
information_schema.columns where table_schema='test' and table_name = 't1';
224
299
CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH
306
381
FROM information_schema.tables);
307
382
table_name
308
383
TABLE_CONSTRAINTS
309
 
create table t1 (f1 int);
310
 
create table t2 (f1 int, f2 int);
 
384
create table t1 (f1 int(11));
 
385
create table t2 (f1 int(11), f2 int(11));
311
386
select table_name from information_schema.tables
312
387
where table_schema = 'test' and table_name not in
313
388
(select table_name from information_schema.columns
340
415
table_name      group_concat(t.table_schema, '.', t.table_name) num1
341
416
CHARACTER_SETS  information_schema.CHARACTER_SETS       1
342
417
COLLATIONS      information_schema.COLLATIONS   1
343
 
COLLATION_CHARACTER_SET_APPLICABILITY   information_schema.COLLATION_CHARACTER_SET_APPLICABILITY        1
344
418
COLUMNS information_schema.COLUMNS      1
345
 
GLOBAL_STATUS   information_schema.GLOBAL_STATUS        1
346
 
GLOBAL_VARIABLES        information_schema.GLOBAL_VARIABLES     1
347
419
KEY_COLUMN_USAGE        information_schema.KEY_COLUMN_USAGE     1
348
420
PLUGINS information_schema.PLUGINS      1
349
421
PROCESSLIST     information_schema.PROCESSLIST  1
350
422
REFERENTIAL_CONSTRAINTS information_schema.REFERENTIAL_CONSTRAINTS      1
351
423
SCHEMATA        information_schema.SCHEMATA     1
352
 
SESSION_STATUS  information_schema.SESSION_STATUS       1
353
 
SESSION_VARIABLES       information_schema.SESSION_VARIABLES    1
354
424
STATISTICS      information_schema.STATISTICS   1
355
425
TABLES  information_schema.TABLES       1
356
426
TABLE_CONSTRAINTS       information_schema.TABLE_CONSTRAINTS    1
384
454
f2      varchar(50)     NO              NULL    
385
455
f3      varchar(50)     YES             NULL    
386
456
f4      varchar(50)     YES             NULL    
387
 
f5      bigint  NO              NULL    
388
 
f6      bigint  NO              NULL    
 
457
f5      bigint(20)      NO              NULL    
 
458
f6      bigint(20)      NO              NULL    
389
459
f7      datetime        NO              NULL    
390
460
f8      datetime        YES             NULL    
391
461
drop table t1;