Debian Codegraph @

mysql-test/suite/funcs_1/r/is_columns.result

raw
1 SHOW TABLES FROM information_schema LIKE 'COLUMNS';
2 Tables_in_information_schema (COLUMNS)
3 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
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.COLUMNS;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.COLUMNS;
20 SELECT * FROM test.v1;
21 CALL test.p1;
22 SELECT test.f1();
23 DROP VIEW 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) NO NULL
32 TABLE_SCHEMA varchar(64) NO NULL
33 TABLE_NAME varchar(64) NO NULL
34 COLUMN_NAME varchar(64) NO NULL
35 ORDINAL_POSITION bigint(21) unsigned NO NULL
36 COLUMN_DEFAULT longtext YES NULL
37 IS_NULLABLE varchar(3) NO NULL
38 DATA_TYPE varchar(64) NO NULL
39 CHARACTER_MAXIMUM_LENGTH bigint(21) unsigned YES NULL
40 CHARACTER_OCTET_LENGTH bigint(21) unsigned YES NULL
41 NUMERIC_PRECISION bigint(21) unsigned YES NULL
42 NUMERIC_SCALE bigint(21) unsigned YES NULL
43 DATETIME_PRECISION bigint(21) unsigned YES NULL
44 CHARACTER_SET_NAME varchar(32) YES NULL
45 COLLATION_NAME varchar(64) YES NULL
46 COLUMN_TYPE longtext NO NULL
47 COLUMN_KEY varchar(3) NO NULL
48 EXTRA varchar(80) NO NULL
49 PRIVILEGES varchar(80) NO NULL
50 COLUMN_COMMENT varchar(1024) NO NULL
51 IS_GENERATED varchar(6) NO NULL
52 GENERATION_EXPRESSION longtext YES NULL
53 IS_SYSTEM_TIME_PERIOD_START varchar(3) NO NULL
54 IS_SYSTEM_TIME_PERIOD_END varchar(3) NO NULL
55 SHOW CREATE TABLE information_schema.COLUMNS;
56 Table Create Table
57 COLUMNS CREATE TEMPORARY TABLE `COLUMNS` (
58 `TABLE_CATALOG` varchar(512) NOT NULL,
59 `TABLE_SCHEMA` varchar(64) NOT NULL,
60 `TABLE_NAME` varchar(64) NOT NULL,
61 `COLUMN_NAME` varchar(64) NOT NULL,
62 `ORDINAL_POSITION` bigint(21) unsigned NOT NULL,
63 `COLUMN_DEFAULT` longtext,
64 `IS_NULLABLE` varchar(3) NOT NULL,
65 `DATA_TYPE` varchar(64) NOT NULL,
66 `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned,
67 `CHARACTER_OCTET_LENGTH` bigint(21) unsigned,
68 `NUMERIC_PRECISION` bigint(21) unsigned,
69 `NUMERIC_SCALE` bigint(21) unsigned,
70 `DATETIME_PRECISION` bigint(21) unsigned,
71 `CHARACTER_SET_NAME` varchar(32),
72 `COLLATION_NAME` varchar(64),
73 `COLUMN_TYPE` longtext NOT NULL,
74 `COLUMN_KEY` varchar(3) NOT NULL,
75 `EXTRA` varchar(80) NOT NULL,
76 `PRIVILEGES` varchar(80) NOT NULL,
77 `COLUMN_COMMENT` varchar(1024) NOT NULL,
78 `IS_GENERATED` varchar(6) NOT NULL,
79 `GENERATION_EXPRESSION` longtext,
80 `IS_SYSTEM_TIME_PERIOD_START` varchar(3) NOT NULL,
81 `IS_SYSTEM_TIME_PERIOD_END` varchar(3) NOT NULL
82 ) DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
83 SHOW COLUMNS FROM information_schema.COLUMNS;
84 Field Type Null Key Default Extra
85 TABLE_CATALOG varchar(512) NO NULL
86 TABLE_SCHEMA varchar(64) NO NULL
87 TABLE_NAME varchar(64) NO NULL
88 COLUMN_NAME varchar(64) NO NULL
89 ORDINAL_POSITION bigint(21) unsigned NO NULL
90 COLUMN_DEFAULT longtext YES NULL
91 IS_NULLABLE varchar(3) NO NULL
92 DATA_TYPE varchar(64) NO NULL
93 CHARACTER_MAXIMUM_LENGTH bigint(21) unsigned YES NULL
94 CHARACTER_OCTET_LENGTH bigint(21) unsigned YES NULL
95 NUMERIC_PRECISION bigint(21) unsigned YES NULL
96 NUMERIC_SCALE bigint(21) unsigned YES NULL
97 DATETIME_PRECISION bigint(21) unsigned YES NULL
98 CHARACTER_SET_NAME varchar(32) YES NULL
99 COLLATION_NAME varchar(64) YES NULL
100 COLUMN_TYPE longtext NO NULL
101 COLUMN_KEY varchar(3) NO NULL
102 EXTRA varchar(80) NO NULL
103 PRIVILEGES varchar(80) NO NULL
104 COLUMN_COMMENT varchar(1024) NO NULL
105 IS_GENERATED varchar(6) NO NULL
106 GENERATION_EXPRESSION longtext YES NULL
107 IS_SYSTEM_TIME_PERIOD_START varchar(3) NO NULL
108 IS_SYSTEM_TIME_PERIOD_END varchar(3) NO NULL
109 SELECT table_catalog, table_schema, table_name, column_name
110 FROM information_schema.columns WHERE table_catalog IS NULL OR table_catalog <> 'def';
111 table_catalog table_schema table_name column_name
112 Warnings:
113 Warning 1286 Unknown storage engine 'InnoDB'
114 Warning 1286 Unknown storage engine 'InnoDB'
115 Warning 1286 Unknown storage engine 'InnoDB'
116 ###############################################################################
117 # Testcase 3.2.6.2 + 3.2.6.3: INFORMATION_SCHEMA.COLUMNS accessible information
118 ###############################################################################
119 DROP DATABASE IF EXISTS db_datadict;
120 CREATE DATABASE db_datadict;
121 DROP USER 'testuser1'@'localhost';
122 CREATE USER 'testuser1'@'localhost';
123 DROP USER 'testuser2'@'localhost';
124 CREATE USER 'testuser2'@'localhost';
125 CREATE TABLE db_datadict.t1
126 (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT AUTO_INCREMENT,
127 UNIQUE INDEX MUL_IDX(f1,f3), PRIMARY KEY (f4))
128 ENGINE = <other_engine_type>;
129 CREATE VIEW db_datadict.v1 AS SELECT 1 AS f1, 1 AS f2;
130 GRANT SELECT(f1, f2) ON db_datadict.t1 TO 'testuser1'@'localhost';
131 GRANT SELECT(f2) ON db_datadict.v1 TO 'testuser1'@'localhost';
132 CREATE TABLE db_datadict.t2
133 (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT, PRIMARY KEY (f1,f4))
134 ENGINE = <other_engine_type>;
135 GRANT INSERT(f1, f2) ON db_datadict.t2 TO 'testuser2'@'localhost';
136 SELECT * FROM information_schema.columns
137 WHERE table_schema = 'db_datadict'
138 ORDER BY table_schema, table_name, ordinal_position;
139 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 DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION IS_SYSTEM_TIME_PERIOD_START IS_SYSTEM_TIME_PERIOD_END
140 def db_datadict t1 f1 1 NULL YES char 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci char(10) MUL select,insert,update,references NEVER NULL NO NO
141 def db_datadict t1 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci text select,insert,update,references NEVER NULL NO NO
142 def db_datadict t1 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL NULL date select,insert,update,references NEVER NULL NO NO
143 def db_datadict t1 f4 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) PRI auto_increment select,insert,update,references NEVER NULL NO NO
144 def db_datadict t2 f1 1 NULL NO char 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci char(10) PRI select,insert,update,references NEVER NULL NO NO
145 def db_datadict t2 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci text select,insert,update,references NEVER NULL NO NO
146 def db_datadict t2 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL NULL date select,insert,update,references NEVER NULL NO NO
147 def db_datadict t2 f4 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int(11) PRI select,insert,update,references NEVER NULL NO NO
148 def db_datadict v1 f1 1 0 NO int NULL NULL 10 0 NULL NULL NULL int(1) select,insert,update,references NEVER NULL NO NO
149 def db_datadict v1 f2 2 0 NO int NULL NULL 10 0 NULL NULL NULL int(1) select,insert,update,references NEVER NULL NO NO
150 SHOW COLUMNS FROM db_datadict.t1;
151 Field Type Null Key Default Extra
152 f1 char(10) YES MUL NULL
153 f2 text YES NULL
154 f3 date YES NULL
155 f4 int(11) NO PRI NULL auto_increment
156 SHOW COLUMNS FROM db_datadict.t2;
157 Field Type Null Key Default Extra
158 f1 char(10) NO PRI NULL
159 f2 text YES NULL
160 f3 date YES NULL
161 f4 int(11) NO PRI NULL
162 SHOW COLUMNS FROM db_datadict.v1;
163 Field Type Null Key Default Extra
164 f1 int(1) NO 0
165 f2 int(1) NO 0
166 connect testuser1, localhost, testuser1, , db_datadict;
167 SELECT * FROM information_schema.columns
168 WHERE table_schema = 'db_datadict'
169 ORDER BY table_schema, table_name, ordinal_position;
170 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 DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION IS_SYSTEM_TIME_PERIOD_START IS_SYSTEM_TIME_PERIOD_END
171 def db_datadict t1 f1 1 NULL YES char 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci char(10) MUL select NEVER NULL NO NO
172 def db_datadict t1 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci text select NEVER NULL NO NO
173 def db_datadict v1 f2 2 0 NO int NULL NULL 10 0 NULL NULL NULL int(1) select NEVER NULL NO NO
174 SHOW COLUMNS FROM db_datadict.t1;
175 Field Type Null Key Default Extra
176 f1 char(10) YES MUL NULL
177 f2 text YES NULL
178 SHOW COLUMNS FROM db_datadict.t2;
179 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table `db_datadict`.`t2`
180 SHOW COLUMNS FROM db_datadict.v1;
181 Field Type Null Key Default Extra
182 f2 int(1) NO 0
183 connect testuser2, localhost, testuser2, , db_datadict;
184 SELECT * FROM information_schema.columns
185 WHERE table_schema = 'db_datadict'
186 ORDER BY table_schema, table_name, ordinal_position;
187 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 DATETIME_PRECISION CHARACTER_SET_NAME COLLATION_NAME COLUMN_TYPE COLUMN_KEY EXTRA PRIVILEGES COLUMN_COMMENT IS_GENERATED GENERATION_EXPRESSION IS_SYSTEM_TIME_PERIOD_START IS_SYSTEM_TIME_PERIOD_END
188 def db_datadict t2 f1 1 NULL NO char 10 40 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci char(10) PRI insert NEVER NULL NO NO
189 def db_datadict t2 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_uca1400_ai_ci text insert NEVER NULL NO NO
190 SHOW COLUMNS FROM db_datadict.t1;
191 ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table `db_datadict`.`t1`
192 SHOW COLUMNS FROM db_datadict.t2;
193 Field Type Null Key Default Extra
194 f1 char(10) NO PRI NULL
195 f2 text YES NULL
196 SHOW COLUMNS FROM db_datadict.v1;
197 ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table `db_datadict`.`v1`
198 connection default;
199 disconnect testuser1;
200 disconnect testuser2;
201 DROP USER 'testuser1'@'localhost';
202 DROP USER 'testuser2'@'localhost';
203 DROP DATABASE IF EXISTS db_datadict;
204 ###############################################################################
205 # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMNS modifications
206 ###############################################################################
207 DROP TABLE IF EXISTS test.t1_my_table;
208 DROP DATABASE IF EXISTS db_datadict;
209 CREATE DATABASE db_datadict;
210 SELECT table_name FROM information_schema.columns
211 WHERE table_name LIKE 't1_my_table%';
212 table_name
213 CREATE TABLE test.t1_my_table (f1 CHAR(12))
214 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
215 ENGINE = <engine_type>;
216 SELECT * FROM information_schema.columns
217 WHERE table_name = 't1_my_table';
218 TABLE_CATALOG def
219 TABLE_SCHEMA test
220 TABLE_NAME t1_my_table
221 COLUMN_NAME f1
222 ORDINAL_POSITION 1
223 COLUMN_DEFAULT NULL
224 IS_NULLABLE YES
225 DATA_TYPE char
226 CHARACTER_MAXIMUM_LENGTH 12
227 CHARACTER_OCTET_LENGTH 12
228 NUMERIC_PRECISION NULL
229 NUMERIC_SCALE NULL
230 DATETIME_PRECISION NULL
231 CHARACTER_SET_NAME latin1
232 COLLATION_NAME latin1_swedish_ci
233 COLUMN_TYPE char(12)
234 COLUMN_KEY
235 EXTRA
236 PRIVILEGES select,insert,update,references
237 COLUMN_COMMENT
238 IS_GENERATED NEVER
239 GENERATION_EXPRESSION NULL
240 IS_SYSTEM_TIME_PERIOD_START NO
241 IS_SYSTEM_TIME_PERIOD_END NO
242 SELECT table_name FROM information_schema.columns
243 WHERE table_name LIKE 't1_my_table%';
244 table_name
245 t1_my_table
246 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
247 SELECT table_name FROM information_schema.columns
248 WHERE table_name LIKE 't1_my_table%';
249 table_name
250 t1_my_tablex
251 SELECT table_schema,table_name FROM information_schema.columns
252 WHERE table_name = 't1_my_tablex';
253 table_schema table_name
254 test t1_my_tablex
255 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
256 SELECT table_schema,table_name FROM information_schema.columns
257 WHERE table_name = 't1_my_tablex';
258 table_schema table_name
259 db_datadict t1_my_tablex
260 SELECT table_name, column_name FROM information_schema.columns
261 WHERE table_name = 't1_my_tablex';
262 table_name column_name
263 t1_my_tablex f1
264 ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
265 SELECT table_name, column_name FROM information_schema.columns
266 WHERE table_name = 't1_my_tablex';
267 table_name column_name
268 t1_my_tablex first_col
269 SELECT table_name, column_name, character_maximum_length,
270 character_octet_length, column_type
271 FROM information_schema.columns
272 WHERE table_name = 't1_my_tablex';
273 table_name column_name character_maximum_length character_octet_length column_type
274 t1_my_tablex first_col 12 12 char(12)
275 ALTER TABLE db_datadict.t1_my_tablex
276 MODIFY COLUMN first_col CHAR(20);
277 SELECT table_name, column_name, character_maximum_length,
278 character_octet_length, column_type
279 FROM information_schema.columns
280 WHERE table_name = 't1_my_tablex';
281 table_name column_name character_maximum_length character_octet_length column_type
282 t1_my_tablex first_col 20 20 char(20)
283 SELECT table_name, column_name, character_maximum_length,
284 character_octet_length, column_type
285 FROM information_schema.columns
286 WHERE table_name = 't1_my_tablex';
287 table_name column_name character_maximum_length character_octet_length column_type
288 t1_my_tablex first_col 20 20 char(20)
289 ALTER TABLE db_datadict.t1_my_tablex
290 MODIFY COLUMN first_col VARCHAR(20);
291 SELECT table_name, column_name, character_maximum_length,
292 character_octet_length, column_type
293 FROM information_schema.columns
294 WHERE table_name = 't1_my_tablex';
295 table_name column_name character_maximum_length character_octet_length column_type
296 t1_my_tablex first_col 20 20 varchar(20)
297 SELECT table_name, column_name, column_default
298 FROM information_schema.columns
299 WHERE table_name = 't1_my_tablex';
300 table_name column_name column_default
301 t1_my_tablex first_col NULL
302 ALTER TABLE db_datadict.t1_my_tablex
303 MODIFY COLUMN first_col CHAR(10) DEFAULT 'hello';
304 SELECT table_name, column_name, column_default
305 FROM information_schema.columns
306 WHERE table_name = 't1_my_tablex';
307 table_name column_name column_default
308 t1_my_tablex first_col 'hello'
309 SELECT table_name, column_name, is_nullable
310 FROM information_schema.columns
311 WHERE table_name = 't1_my_tablex';
312 table_name column_name is_nullable
313 t1_my_tablex first_col YES
314 ALTER TABLE db_datadict.t1_my_tablex
315 MODIFY COLUMN first_col CHAR(10) NOT NULL;
316 SELECT table_name, column_name, is_nullable
317 FROM information_schema.columns
318 WHERE table_name = 't1_my_tablex';
319 table_name column_name is_nullable
320 t1_my_tablex first_col NO
321 SELECT table_name, column_name, collation_name
322 FROM information_schema.columns
323 WHERE table_name = 't1_my_tablex';
324 table_name column_name collation_name
325 t1_my_tablex first_col latin1_swedish_ci
326 ALTER TABLE db_datadict.t1_my_tablex
327 MODIFY COLUMN first_col CHAR(10) COLLATE 'latin1_general_cs';
328 SELECT table_name, column_name, collation_name
329 FROM information_schema.columns
330 WHERE table_name = 't1_my_tablex';
331 table_name column_name collation_name
332 t1_my_tablex first_col latin1_general_cs
333 SELECT table_name, column_name, character_maximum_length,
334 character_octet_length, character_set_name
335 FROM information_schema.columns
336 WHERE table_name = 't1_my_tablex';
337 table_name column_name character_maximum_length character_octet_length character_set_name
338 t1_my_tablex first_col 10 10 latin1
339 ALTER TABLE db_datadict.t1_my_tablex
340 MODIFY COLUMN first_col CHAR(10) CHARACTER SET utf8;
341 SELECT table_name, column_name, character_maximum_length,
342 character_octet_length, character_set_name
343 FROM information_schema.columns
344 WHERE table_name = 't1_my_tablex';
345 table_name column_name character_maximum_length character_octet_length character_set_name
346 t1_my_tablex first_col 10 30 utf8mb3
347 SELECT table_name, column_name, column_comment
348 FROM information_schema.columns
349 WHERE table_name = 't1_my_tablex';
350 table_name column_name column_comment
351 t1_my_tablex first_col
352 ALTER TABLE db_datadict.t1_my_tablex
353 MODIFY COLUMN first_col CHAR(10) COMMENT 'Hello';
354 SELECT table_name, column_name, column_comment
355 FROM information_schema.columns
356 WHERE table_name = 't1_my_tablex';
357 table_name column_name column_comment
358 t1_my_tablex first_col Hello
359 SELECT table_name, column_name
360 FROM information_schema.columns
361 WHERE table_name = 't1_my_tablex';
362 table_name column_name
363 t1_my_tablex first_col
364 ALTER TABLE db_datadict.t1_my_tablex
365 ADD COLUMN second_col CHAR(10);
366 SELECT table_name, column_name
367 FROM information_schema.columns
368 WHERE table_name = 't1_my_tablex';
369 table_name column_name
370 t1_my_tablex first_col
371 t1_my_tablex second_col
372 SELECT table_name, column_name, ordinal_position
373 FROM information_schema.columns
374 WHERE table_name = 't1_my_tablex'
375 ORDER BY table_name, column_name;
376 table_name column_name ordinal_position
377 t1_my_tablex first_col 1
378 t1_my_tablex second_col 2
379 ALTER TABLE db_datadict.t1_my_tablex
380 MODIFY COLUMN second_col CHAR(10) FIRST;
381 SELECT table_name, column_name, ordinal_position
382 FROM information_schema.columns
383 WHERE table_name = 't1_my_tablex'
384 ORDER BY table_name, column_name;
385 table_name column_name ordinal_position
386 t1_my_tablex first_col 2
387 t1_my_tablex second_col 1
388 SELECT table_name, column_name
389 FROM information_schema.columns
390 WHERE table_name = 't1_my_tablex';
391 table_name column_name
392 t1_my_tablex second_col
393 t1_my_tablex first_col
394 ALTER TABLE db_datadict.t1_my_tablex
395 DROP COLUMN first_col;
396 SELECT table_name, column_name
397 FROM information_schema.columns
398 WHERE table_name = 't1_my_tablex';
399 table_name column_name
400 t1_my_tablex second_col
401 SELECT table_name, column_name, column_key
402 FROM information_schema.columns
403 WHERE table_name = 't1_my_tablex';
404 table_name column_name column_key
405 t1_my_tablex second_col
406 ALTER TABLE db_datadict.t1_my_tablex
407 ADD UNIQUE INDEX IDX(second_col);
408 SELECT table_name, column_name, column_key
409 FROM information_schema.columns
410 WHERE table_name = 't1_my_tablex';
411 table_name column_name column_key
412 t1_my_tablex second_col UNI
413 SELECT table_name, column_name
414 FROM information_schema.columns
415 WHERE table_name = 't1_my_tablex';
416 table_name column_name
417 t1_my_tablex second_col
418 DROP TABLE db_datadict.t1_my_tablex;
419 SELECT table_name, column_name
420 FROM information_schema.columns
421 WHERE table_name = 't1_my_tablex';
422 table_name column_name
423 CREATE VIEW test.t1_my_tablex
424 AS SELECT 1 AS "col1", 'A' collate latin1_german1_ci AS "col2";
425 SELECT * FROM information_schema.columns
426 WHERE table_name = 't1_my_tablex'
427 ORDER BY table_name, column_name;
428 TABLE_CATALOG def
429 TABLE_SCHEMA test
430 TABLE_NAME t1_my_tablex
431 COLUMN_NAME col1
432 ORDINAL_POSITION 1
433 COLUMN_DEFAULT 0
434 IS_NULLABLE NO
435 DATA_TYPE int
436 CHARACTER_MAXIMUM_LENGTH NULL
437 CHARACTER_OCTET_LENGTH NULL
438 NUMERIC_PRECISION 10
439 NUMERIC_SCALE 0
440 DATETIME_PRECISION NULL
441 CHARACTER_SET_NAME NULL
442 COLLATION_NAME NULL
443 COLUMN_TYPE int(1)
444 COLUMN_KEY
445 EXTRA
446 PRIVILEGES select,insert,update,references
447 COLUMN_COMMENT
448 IS_GENERATED NEVER
449 GENERATION_EXPRESSION NULL
450 IS_SYSTEM_TIME_PERIOD_START NO
451 IS_SYSTEM_TIME_PERIOD_END NO
452 TABLE_CATALOG def
453 TABLE_SCHEMA test
454 TABLE_NAME t1_my_tablex
455 COLUMN_NAME col2
456 ORDINAL_POSITION 2
457 COLUMN_DEFAULT NULL
458 IS_NULLABLE YES
459 DATA_TYPE varchar
460 CHARACTER_MAXIMUM_LENGTH 1
461 CHARACTER_OCTET_LENGTH 1
462 NUMERIC_PRECISION NULL
463 NUMERIC_SCALE NULL
464 DATETIME_PRECISION NULL
465 CHARACTER_SET_NAME latin1
466 COLLATION_NAME latin1_german1_ci
467 COLUMN_TYPE varchar(1)
468 COLUMN_KEY
469 EXTRA
470 PRIVILEGES select,insert,update,references
471 COLUMN_COMMENT
472 IS_GENERATED NEVER
473 GENERATION_EXPRESSION NULL
474 IS_SYSTEM_TIME_PERIOD_START NO
475 IS_SYSTEM_TIME_PERIOD_END NO
476 DROP VIEW test.t1_my_tablex;
477 SELECT table_name FROM information_schema.columns
478 WHERE table_name = 't1_my_tablex';
479 table_name
480 CREATE TABLE db_datadict.t1_my_tablex
481 ENGINE = <engine_type> AS
482 SELECT 1;
483 SELECT table_name FROM information_schema.columns
484 WHERE table_name = 't1_my_tablex';
485 table_name
486 t1_my_tablex
487 DROP DATABASE db_datadict;
488 SELECT table_name FROM information_schema.columns
489 WHERE table_name = 't1_my_tablex';
490 table_name
491 ########################################################################
492 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
493 # DDL on INFORMATION_SCHEMA table are not supported
494 ########################################################################
495 DROP DATABASE IF EXISTS db_datadict;
496 DROP TABLE IF EXISTS test.t1;
497 CREATE DATABASE db_datadict;
498 CREATE TABLE test.t1 (f1 BIGINT);
499 INSERT INTO information_schema.columns (table_schema,table_name,column_name)
500 VALUES('test','t1', 'f2');
501 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
502 INSERT INTO information_schema.columns (table_schema,table_name,column_name)
503 VALUES('test','t2', 'f1');
504 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
505 UPDATE information_schema.columns SET table_name = 't4' WHERE table_name = 't1';
506 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
507 DELETE FROM information_schema.columns WHERE table_name = 't1';
508 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
509 TRUNCATE information_schema.columns;
510 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
511 CREATE INDEX i3 ON information_schema.columns(table_name);
512 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
513 ALTER TABLE information_schema.columns ADD f1 INT;
514 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
515 DROP TABLE information_schema.columns;
516 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
517 ALTER TABLE information_schema.columns RENAME db_datadict.columns;
518 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
519 ALTER TABLE information_schema.columns RENAME information_schema.xcolumns;
520 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
521 DROP TABLE test.t1;
522 DROP DATABASE db_datadict;