1
SHOW TABLES FROM information_schema LIKE 'COLUMNS';
2
Tables_in_information_schema (COLUMNS)
4
#######################################################################
5
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
6
#######################################################################
7
DROP VIEW IF EXISTS test.v1;
8
DROP PROCEDURE IF EXISTS test.p1;
9
DROP FUNCTION IF EXISTS test.f1;
10
CREATE VIEW test.v1 AS SELECT * FROM information_schema.COLUMNS;
11
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.COLUMNS;
12
CREATE FUNCTION test.f1() returns BIGINT
14
DECLARE counter BIGINT DEFAULT NULL;
15
SELECT COUNT(*) INTO counter FROM information_schema.COLUMNS;
18
# Attention: The printing of the next result sets is disabled.
19
SELECT * FROM information_schema.COLUMNS;
20
SELECT * FROM test.v1;
24
DROP PROCEDURE test.p1;
25
DROP FUNCTION test.f1;
26
#########################################################################
27
# Testcase 3.2.6.1: INFORMATION_SCHEMA.COLUMNS layout
28
#########################################################################
29
DESCRIBE information_schema.COLUMNS;
30
Field Type Null Key Default Extra
31
TABLE_CATALOG varchar(512) YES NULL
32
TABLE_SCHEMA varchar(64) NO
33
TABLE_NAME varchar(64) NO
34
COLUMN_NAME varchar(64) NO
35
ORDINAL_POSITION bigint(21) NO 0
36
COLUMN_DEFAULT longtext YES NULL
37
IS_NULLABLE varchar(3) NO
38
DATA_TYPE varchar(64) NO
39
CHARACTER_MAXIMUM_LENGTH bigint(21) YES NULL
40
CHARACTER_OCTET_LENGTH bigint(21) YES NULL
41
NUMERIC_PRECISION bigint(21) YES NULL
42
NUMERIC_SCALE bigint(21) YES NULL
43
CHARACTER_SET_NAME varchar(64) YES NULL
44
COLLATION_NAME varchar(64) YES NULL
45
COLUMN_TYPE longtext NO NULL
46
COLUMN_KEY varchar(3) NO
48
PRIVILEGES varchar(80) NO
49
COLUMN_COMMENT varchar(255) NO
50
SHOW CREATE TABLE information_schema.COLUMNS;
52
COLUMNS CREATE TEMPORARY TABLE `COLUMNS` (
53
`TABLE_CATALOG` varchar(512) default NULL,
54
`TABLE_SCHEMA` varchar(64) NOT NULL default '',
55
`TABLE_NAME` varchar(64) NOT NULL default '',
56
`COLUMN_NAME` varchar(64) NOT NULL default '',
57
`ORDINAL_POSITION` bigint(21) NOT NULL default '0',
58
`COLUMN_DEFAULT` longtext,
59
`IS_NULLABLE` varchar(3) NOT NULL default '',
60
`DATA_TYPE` varchar(64) NOT NULL default '',
61
`CHARACTER_MAXIMUM_LENGTH` bigint(21) default NULL,
62
`CHARACTER_OCTET_LENGTH` bigint(21) default NULL,
63
`NUMERIC_PRECISION` bigint(21) default NULL,
64
`NUMERIC_SCALE` bigint(21) default NULL,
65
`CHARACTER_SET_NAME` varchar(64) default NULL,
66
`COLLATION_NAME` varchar(64) default NULL,
67
`COLUMN_TYPE` longtext NOT NULL,
68
`COLUMN_KEY` varchar(3) NOT NULL default '',
69
`EXTRA` varchar(20) NOT NULL default '',
70
`PRIVILEGES` varchar(80) NOT NULL default '',
71
`COLUMN_COMMENT` varchar(255) NOT NULL default ''
72
) ENGINE=MyISAM DEFAULT CHARSET=utf8
73
SHOW COLUMNS FROM information_schema.COLUMNS;
74
Field Type Null Key Default Extra
75
TABLE_CATALOG varchar(512) YES NULL
76
TABLE_SCHEMA varchar(64) NO
77
TABLE_NAME varchar(64) NO
78
COLUMN_NAME varchar(64) NO
79
ORDINAL_POSITION bigint(21) NO 0
80
COLUMN_DEFAULT longtext YES NULL
81
IS_NULLABLE varchar(3) NO
82
DATA_TYPE varchar(64) NO
83
CHARACTER_MAXIMUM_LENGTH bigint(21) YES NULL
84
CHARACTER_OCTET_LENGTH bigint(21) YES NULL
85
NUMERIC_PRECISION bigint(21) YES NULL
86
NUMERIC_SCALE bigint(21) YES NULL
87
CHARACTER_SET_NAME varchar(64) YES NULL
88
COLLATION_NAME varchar(64) YES NULL
89
COLUMN_TYPE longtext NO NULL
90
COLUMN_KEY varchar(3) NO
92
PRIVILEGES varchar(80) NO
93
COLUMN_COMMENT varchar(255) NO
94
SELECT table_catalog, table_schema, table_name, column_name
95
FROM information_schema.columns WHERE table_catalog IS NOT NULL;
96
table_catalog table_schema table_name column_name
97
###############################################################################
98
# Testcase 3.2.6.2 + 3.2.6.3: INFORMATION_SCHEMA.COLUMNS accessible information
99
###############################################################################
100
DROP DATABASE IF EXISTS db_datadict;
101
CREATE DATABASE db_datadict;
102
DROP USER 'testuser1'@'localhost';
103
CREATE USER 'testuser1'@'localhost';
104
DROP USER 'testuser2'@'localhost';
105
CREATE USER 'testuser2'@'localhost';
106
CREATE TABLE db_datadict.t1
107
(f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT AUTO_INCREMENT,
108
UNIQUE INDEX MUL_IDX(f1,f3), PRIMARY KEY (f4))
109
ENGINE = <other_engine_type>;
110
CREATE VIEW db_datadict.v1 AS SELECT 1 AS f1, 1 AS f2;
111
GRANT SELECT(f1, f2) ON db_datadict.t1 TO 'testuser1'@'localhost';
112
GRANT SELECT(f2) ON db_datadict.v1 TO 'testuser1'@'localhost';
113
CREATE TABLE db_datadict.t2
114
(f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT, PRIMARY KEY (f1,f4))
115
ENGINE = <other_engine_type>;
116
GRANT INSERT(f1, f2) ON db_datadict.t2 TO 'testuser2'@'localhost';
117
SELECT * FROM information_schema.columns
118
WHERE table_schema = 'db_datadict'
119
ORDER BY table_schema, table_name, ordinal_position;
120
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
121
NULL db_datadict t1 f1 1 NULL YES char 10 10 NULL NULL latin1 latin1_swedish_ci char(10) MUL select,insert,update,references
122
NULL db_datadict t1 f2 2 NULL YES text 65535 65535 NULL NULL latin1 latin1_swedish_ci text select,insert,update,references
123
NULL db_datadict t1 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL date select,insert,update,references
124
NULL db_datadict t1 f4 4 NULL NO int NULL NULL 10 0 NULL NULL int(11) PRI auto_increment select,insert,update,references
125
NULL db_datadict t2 f1 1 NO char 10 10 NULL NULL latin1 latin1_swedish_ci char(10) PRI select,insert,update,references
126
NULL db_datadict t2 f2 2 NULL YES text 65535 65535 NULL NULL latin1 latin1_swedish_ci text select,insert,update,references
127
NULL db_datadict t2 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL date select,insert,update,references
128
NULL db_datadict t2 f4 4 0 NO int NULL NULL 10 0 NULL NULL int(11) PRI select,insert,update,references
129
NULL db_datadict v1 f1 1 0 NO int NULL NULL 10 0 NULL NULL int(1) select,insert,update,references
130
NULL db_datadict v1 f2 2 0 NO int NULL NULL 10 0 NULL NULL int(1) select,insert,update,references
131
SHOW COLUMNS FROM db_datadict.t1;
132
Field Type Null Key Default Extra
133
f1 char(10) YES MUL NULL
136
f4 int(11) NO PRI NULL auto_increment
137
SHOW COLUMNS FROM db_datadict.t2;
138
Field Type Null Key Default Extra
143
SHOW COLUMNS FROM db_datadict.v1;
144
Field Type Null Key Default Extra
147
# Establish connection testuser1 (user=testuser1)
148
SELECT * FROM information_schema.columns
149
WHERE table_schema = 'db_datadict'
150
ORDER BY table_schema, table_name, ordinal_position;
151
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
152
NULL db_datadict t1 f1 1 NULL YES char 10 10 NULL NULL latin1 latin1_swedish_ci char(10) MUL select
153
NULL db_datadict t1 f2 2 NULL YES text 65535 65535 NULL NULL latin1 latin1_swedish_ci text select
154
NULL db_datadict v1 f2 2 0 NO int NULL NULL 10 0 NULL NULL int(1) select
155
SHOW COLUMNS FROM db_datadict.t1;
156
Field Type Null Key Default Extra
157
f1 char(10) YES MUL NULL
159
SHOW COLUMNS FROM db_datadict.t2;
160
ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 't2'
161
SHOW COLUMNS FROM db_datadict.v1;
162
Field Type Null Key Default Extra
164
# Establish connection testuser2 (user=testuser2)
165
SELECT * FROM information_schema.columns
166
WHERE table_schema = 'db_datadict'
167
ORDER BY table_schema, table_name, ordinal_position;
168
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
169
NULL db_datadict t2 f1 1 NO char 10 10 NULL NULL latin1 latin1_swedish_ci char(10) PRI insert
170
NULL db_datadict t2 f2 2 NULL YES text 65535 65535 NULL NULL latin1 latin1_swedish_ci text insert
171
SHOW COLUMNS FROM db_datadict.t1;
172
ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 't1'
173
SHOW COLUMNS FROM db_datadict.t2;
174
Field Type Null Key Default Extra
177
SHOW COLUMNS FROM db_datadict.v1;
178
ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'v1'
179
# Switch to connection default and close connections testuser1, testuser2
180
DROP USER 'testuser1'@'localhost';
181
DROP USER 'testuser2'@'localhost';
182
DROP DATABASE IF EXISTS db_datadict;
183
###############################################################################
184
# Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMNS modifications
185
###############################################################################
186
DROP TABLE IF EXISTS test.t1_my_table;
187
DROP DATABASE IF EXISTS db_datadict;
188
CREATE DATABASE db_datadict;
189
SELECT table_name FROM information_schema.columns
190
WHERE table_name LIKE 't1_my_table%';
192
CREATE TABLE test.t1_my_table (f1 CHAR(12))
193
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
194
ENGINE = <engine_type>;
195
SELECT * FROM information_schema.columns
196
WHERE table_name = 't1_my_table';
199
TABLE_NAME t1_my_table
205
CHARACTER_MAXIMUM_LENGTH 12
206
CHARACTER_OCTET_LENGTH 12
207
NUMERIC_PRECISION NULL
209
CHARACTER_SET_NAME latin1
210
COLLATION_NAME latin1_swedish_ci
214
PRIVILEGES select,insert,update,references
216
SELECT table_name FROM information_schema.columns
217
WHERE table_name LIKE 't1_my_table%';
220
RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
221
SELECT table_name FROM information_schema.columns
222
WHERE table_name LIKE 't1_my_table%';
225
SELECT table_schema,table_name FROM information_schema.columns
226
WHERE table_name = 't1_my_tablex';
227
table_schema table_name
229
RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
230
SELECT table_schema,table_name FROM information_schema.columns
231
WHERE table_name = 't1_my_tablex';
232
table_schema table_name
233
db_datadict t1_my_tablex
234
SELECT table_name, column_name FROM information_schema.columns
235
WHERE table_name = 't1_my_tablex';
236
table_name column_name
238
ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
239
SELECT table_name, column_name FROM information_schema.columns
240
WHERE table_name = 't1_my_tablex';
241
table_name column_name
242
t1_my_tablex first_col
243
SELECT table_name, column_name, character_maximum_length,
244
character_octet_length, column_type
245
FROM information_schema.columns
246
WHERE table_name = 't1_my_tablex';
247
table_name column_name character_maximum_length character_octet_length column_type
248
t1_my_tablex first_col 12 12 char(12)
249
ALTER TABLE db_datadict.t1_my_tablex
250
MODIFY COLUMN first_col CHAR(20);
251
SELECT table_name, column_name, character_maximum_length,
252
character_octet_length, column_type
253
FROM information_schema.columns
254
WHERE table_name = 't1_my_tablex';
255
table_name column_name character_maximum_length character_octet_length column_type
256
t1_my_tablex first_col 20 20 char(20)
257
SELECT table_name, column_name, character_maximum_length,
258
character_octet_length, column_type
259
FROM information_schema.columns
260
WHERE table_name = 't1_my_tablex';
261
table_name column_name character_maximum_length character_octet_length column_type
262
t1_my_tablex first_col 20 20 char(20)
263
ALTER TABLE db_datadict.t1_my_tablex
264
MODIFY COLUMN first_col VARCHAR(20);
265
SELECT table_name, column_name, character_maximum_length,
266
character_octet_length, column_type
267
FROM information_schema.columns
268
WHERE table_name = 't1_my_tablex';
269
table_name column_name character_maximum_length character_octet_length column_type
270
t1_my_tablex first_col 20 20 varchar(20)
271
SELECT table_name, column_name, column_default
272
FROM information_schema.columns
273
WHERE table_name = 't1_my_tablex';
274
table_name column_name column_default
275
t1_my_tablex first_col NULL
276
ALTER TABLE db_datadict.t1_my_tablex
277
MODIFY COLUMN first_col CHAR(10) DEFAULT 'hello';
278
SELECT table_name, column_name, column_default
279
FROM information_schema.columns
280
WHERE table_name = 't1_my_tablex';
281
table_name column_name column_default
282
t1_my_tablex first_col hello
283
SELECT table_name, column_name, is_nullable
284
FROM information_schema.columns
285
WHERE table_name = 't1_my_tablex';
286
table_name column_name is_nullable
287
t1_my_tablex first_col YES
288
ALTER TABLE db_datadict.t1_my_tablex
289
MODIFY COLUMN first_col CHAR(10) NOT NULL;
290
SELECT table_name, column_name, is_nullable
291
FROM information_schema.columns
292
WHERE table_name = 't1_my_tablex';
293
table_name column_name is_nullable
294
t1_my_tablex first_col NO
295
SELECT table_name, column_name, collation_name
296
FROM information_schema.columns
297
WHERE table_name = 't1_my_tablex';
298
table_name column_name collation_name
299
t1_my_tablex first_col latin1_swedish_ci
300
ALTER TABLE db_datadict.t1_my_tablex
301
MODIFY COLUMN first_col CHAR(10) COLLATE 'latin1_general_cs';
302
SELECT table_name, column_name, collation_name
303
FROM information_schema.columns
304
WHERE table_name = 't1_my_tablex';
305
table_name column_name collation_name
306
t1_my_tablex first_col latin1_general_cs
307
SELECT table_name, column_name, character_maximum_length,
308
character_octet_length, character_set_name
309
FROM information_schema.columns
310
WHERE table_name = 't1_my_tablex';
311
table_name column_name character_maximum_length character_octet_length character_set_name
312
t1_my_tablex first_col 10 10 latin1
313
ALTER TABLE db_datadict.t1_my_tablex
314
MODIFY COLUMN first_col CHAR(10) CHARACTER SET utf8;
315
SELECT table_name, column_name, character_maximum_length,
316
character_octet_length, character_set_name
317
FROM information_schema.columns
318
WHERE table_name = 't1_my_tablex';
319
table_name column_name character_maximum_length character_octet_length character_set_name
320
t1_my_tablex first_col 10 30 utf8
321
SELECT table_name, column_name, column_comment
322
FROM information_schema.columns
323
WHERE table_name = 't1_my_tablex';
324
table_name column_name column_comment
325
t1_my_tablex first_col
326
ALTER TABLE db_datadict.t1_my_tablex
327
MODIFY COLUMN first_col CHAR(10) COMMENT 'Hello';
328
SELECT table_name, column_name, column_comment
329
FROM information_schema.columns
330
WHERE table_name = 't1_my_tablex';
331
table_name column_name column_comment
332
t1_my_tablex first_col Hello
333
SELECT table_name, column_name
334
FROM information_schema.columns
335
WHERE table_name = 't1_my_tablex';
336
table_name column_name
337
t1_my_tablex first_col
338
ALTER TABLE db_datadict.t1_my_tablex
339
ADD COLUMN second_col CHAR(10);
340
SELECT table_name, column_name
341
FROM information_schema.columns
342
WHERE table_name = 't1_my_tablex';
343
table_name column_name
344
t1_my_tablex first_col
345
t1_my_tablex second_col
346
SELECT table_name, column_name, ordinal_position
347
FROM information_schema.columns
348
WHERE table_name = 't1_my_tablex'
349
ORDER BY table_name, column_name;
350
table_name column_name ordinal_position
351
t1_my_tablex first_col 1
352
t1_my_tablex second_col 2
353
ALTER TABLE db_datadict.t1_my_tablex
354
MODIFY COLUMN second_col CHAR(10) FIRST;
355
SELECT table_name, column_name, ordinal_position
356
FROM information_schema.columns
357
WHERE table_name = 't1_my_tablex'
358
ORDER BY table_name, column_name;
359
table_name column_name ordinal_position
360
t1_my_tablex first_col 2
361
t1_my_tablex second_col 1
362
SELECT table_name, column_name
363
FROM information_schema.columns
364
WHERE table_name = 't1_my_tablex';
365
table_name column_name
366
t1_my_tablex second_col
367
t1_my_tablex first_col
368
ALTER TABLE db_datadict.t1_my_tablex
369
DROP COLUMN first_col;
370
SELECT table_name, column_name
371
FROM information_schema.columns
372
WHERE table_name = 't1_my_tablex';
373
table_name column_name
374
t1_my_tablex second_col
375
SELECT table_name, column_name, column_key
376
FROM information_schema.columns
377
WHERE table_name = 't1_my_tablex';
378
table_name column_name column_key
379
t1_my_tablex second_col
380
ALTER TABLE db_datadict.t1_my_tablex
381
ADD UNIQUE INDEX IDX(second_col);
382
SELECT table_name, column_name, column_key
383
FROM information_schema.columns
384
WHERE table_name = 't1_my_tablex';
385
table_name column_name column_key
386
t1_my_tablex second_col UNI
387
SELECT table_name, column_name
388
FROM information_schema.columns
389
WHERE table_name = 't1_my_tablex';
390
table_name column_name
391
t1_my_tablex second_col
392
DROP TABLE db_datadict.t1_my_tablex;
393
SELECT table_name, column_name
394
FROM information_schema.columns
395
WHERE table_name = 't1_my_tablex';
396
table_name column_name
397
CREATE VIEW test.t1_my_tablex
398
AS SELECT 1 AS "col1", 'A' collate latin1_german1_ci AS "col2";
399
SELECT * FROM information_schema.columns
400
WHERE table_name = 't1_my_tablex'
401
ORDER BY table_name, column_name;
404
TABLE_NAME t1_my_tablex
410
CHARACTER_MAXIMUM_LENGTH NULL
411
CHARACTER_OCTET_LENGTH NULL
414
CHARACTER_SET_NAME NULL
419
PRIVILEGES select,insert,update,references
423
TABLE_NAME t1_my_tablex
429
CHARACTER_MAXIMUM_LENGTH 1
430
CHARACTER_OCTET_LENGTH 1
431
NUMERIC_PRECISION NULL
433
CHARACTER_SET_NAME latin1
434
COLLATION_NAME latin1_german1_ci
435
COLUMN_TYPE varchar(1)
438
PRIVILEGES select,insert,update,references
440
DROP VIEW test.t1_my_tablex;
441
SELECT table_name FROM information_schema.columns
442
WHERE table_name = 't1_my_tablex';
444
CREATE TABLE db_datadict.t1_my_tablex
445
ENGINE = <engine_type> AS
447
SELECT table_name FROM information_schema.columns
448
WHERE table_name = 't1_my_tablex';
451
DROP DATABASE db_datadict;
452
SELECT table_name FROM information_schema.columns
453
WHERE table_name = 't1_my_tablex';
455
########################################################################
456
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
457
# DDL on INFORMATION_SCHEMA table are not supported
458
########################################################################
459
DROP DATABASE IF EXISTS db_datadict;
460
DROP TABLE IF EXISTS test.t1;
461
CREATE DATABASE db_datadict;
462
CREATE TABLE test.t1 (f1 BIGINT);
463
INSERT INTO information_schema.columns (table_schema,table_name,column_name)
464
VALUES('test','t1', 'f2');
465
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
466
INSERT INTO information_schema.columns (table_schema,table_name,column_name)
467
VALUES('test','t2', 'f1');
468
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
469
UPDATE information_schema.columns SET table_name = 't4' WHERE table_name = 't1';
470
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
471
DELETE FROM information_schema.columns WHERE table_name = 't1';
472
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
473
TRUNCATE information_schema.columns;
474
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
475
CREATE INDEX i3 ON information_schema.columns(table_name);
476
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
477
ALTER TABLE information_schema.columns ADD f1 INT;
478
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
479
DROP TABLE information_schema.columns;
480
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
481
ALTER TABLE information_schema.columns RENAME db_datadict.columns;
482
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
483
ALTER TABLE information_schema.columns RENAME information_schema.xcolumns;
484
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
486
DROP DATABASE db_datadict;
added
removed
mysql-test/suite/funcs_1/r/is_columns.result
