Debian Codegraph @

mysql-test/suite/funcs_1/r/is_columns_ci.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(64) YES NULL
32 TABLE_SCHEMA varchar(64) YES NULL
33 TABLE_NAME varchar(64) YES NULL
34 COLUMN_NAME varchar(64) YES NULL
35 ORDINAL_POSITION int unsigned NO NULL
36 COLUMN_DEFAULT text YES NULL
37 IS_NULLABLE varchar(3) NO
38 DATA_TYPE longtext YES NULL
39 CHARACTER_MAXIMUM_LENGTH bigint YES NULL
40 CHARACTER_OCTET_LENGTH bigint YES NULL
41 NUMERIC_PRECISION bigint unsigned YES NULL
42 NUMERIC_SCALE bigint unsigned YES NULL
43 DATETIME_PRECISION int unsigned YES NULL
44 CHARACTER_SET_NAME varchar(64) YES NULL
45 COLLATION_NAME varchar(64) YES NULL
46 COLUMN_TYPE mediumtext NO NULL
47 COLUMN_KEY enum('','PRI','UNI','MUL') NO NULL
48 EXTRA varchar(256) YES NULL
49 PRIVILEGES varchar(154) YES NULL
50 COLUMN_COMMENT text NO NULL
51 GENERATION_EXPRESSION longtext NO NULL
52 SRS_ID int unsigned YES NULL
53 SHOW CREATE TABLE information_schema.COLUMNS;
54 View Create View character_set_client collation_connection
55 COLUMNS CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`COLUMNS` AS select (`cat`.`name` collate utf8mb3_tolower_ci) AS `TABLE_CATALOG`,(`sch`.`name` collate utf8mb3_tolower_ci) AS `TABLE_SCHEMA`,(`tbl`.`name` collate utf8mb3_tolower_ci) AS `TABLE_NAME`,(`col`.`name` collate utf8mb3_tolower_ci) AS `COLUMN_NAME`,`col`.`ordinal_position` AS `ORDINAL_POSITION`,`col`.`default_value_utf8` AS `COLUMN_DEFAULT`,if((`col`.`is_nullable` = 1),'YES','NO') AS `IS_NULLABLE`,substring_index(substring_index(`col`.`column_type_utf8`,'(',1),' ',1) AS `DATA_TYPE`,internal_dd_char_length(`col`.`type`,`col`.`char_length`,`coll`.`name`,0) AS `CHARACTER_MAXIMUM_LENGTH`,internal_dd_char_length(`col`.`type`,`col`.`char_length`,`coll`.`name`,1) AS `CHARACTER_OCTET_LENGTH`,if((`col`.`numeric_precision` = 0),NULL,`col`.`numeric_precision`) AS `NUMERIC_PRECISION`,if(((`col`.`numeric_scale` = 0) and (`col`.`numeric_precision` = 0)),NULL,`col`.`numeric_scale`) AS `NUMERIC_SCALE`,`col`.`datetime_precision` AS `DATETIME_PRECISION`,(case `col`.`type` when 'MYSQL_TYPE_STRING' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_VAR_STRING' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_VARCHAR' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_TINY_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_MEDIUM_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_LONG_BLOB' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_ENUM' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) when 'MYSQL_TYPE_SET' then if((`cs`.`name` = 'binary'),NULL,`cs`.`name`) else NULL end) AS `CHARACTER_SET_NAME`,(case `col`.`type` when 'MYSQL_TYPE_STRING' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_VAR_STRING' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_VARCHAR' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_TINY_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_MEDIUM_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_LONG_BLOB' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_ENUM' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) when 'MYSQL_TYPE_SET' then if((`cs`.`name` = 'binary'),NULL,`coll`.`name`) else NULL end) AS `COLLATION_NAME`,`col`.`column_type_utf8` AS `COLUMN_TYPE`,`col`.`column_key` AS `COLUMN_KEY`,internal_get_dd_column_extra((`col`.`generation_expression_utf8` is null),`col`.`is_virtual`,`col`.`is_auto_increment`,`col`.`update_option`,if(length(`col`.`default_option`),true,false),`col`.`options`,`col`.`hidden`,`tbl`.`type`) AS `EXTRA`,get_dd_column_privileges(`sch`.`name`,`tbl`.`name`,`col`.`name`) AS `PRIVILEGES`,ifnull(`col`.`comment`,'') AS `COLUMN_COMMENT`,ifnull(`col`.`generation_expression_utf8`,'') AS `GENERATION_EXPRESSION`,`col`.`srs_id` AS `SRS_ID` from (((((`mysql`.`columns` `col` join `mysql`.`tables` `tbl` on((`col`.`table_id` = `tbl`.`id`))) join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`collations` `coll` on((`col`.`collation_id` = `coll`.`id`))) join `mysql`.`character_sets` `cs` on((`coll`.`character_set_id` = `cs`.`id`))) where ((0 <> internal_get_view_warning_or_error(`sch`.`name`,`tbl`.`name`,`tbl`.`type`,`tbl`.`options`)) and (0 <> can_access_column(`sch`.`name`,`tbl`.`name`,`col`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`,(`col`.`hidden` not in ('Visible','User')),`col`.`options`))) utf8mb3 utf8mb3_general_ci
56 SHOW COLUMNS FROM information_schema.COLUMNS;
57 Field Type Null Key Default Extra
58 TABLE_CATALOG varchar(64) YES NULL
59 TABLE_SCHEMA varchar(64) YES NULL
60 TABLE_NAME varchar(64) YES NULL
61 COLUMN_NAME varchar(64) YES NULL
62 ORDINAL_POSITION int unsigned NO NULL
63 COLUMN_DEFAULT text YES NULL
64 IS_NULLABLE varchar(3) NO
65 DATA_TYPE longtext YES NULL
66 CHARACTER_MAXIMUM_LENGTH bigint YES NULL
67 CHARACTER_OCTET_LENGTH bigint YES NULL
68 NUMERIC_PRECISION bigint unsigned YES NULL
69 NUMERIC_SCALE bigint unsigned YES NULL
70 DATETIME_PRECISION int unsigned YES NULL
71 CHARACTER_SET_NAME varchar(64) YES NULL
72 COLLATION_NAME varchar(64) YES NULL
73 COLUMN_TYPE mediumtext NO NULL
74 COLUMN_KEY enum('','PRI','UNI','MUL') NO NULL
75 EXTRA varchar(256) YES NULL
76 PRIVILEGES varchar(154) YES NULL
77 COLUMN_COMMENT text NO NULL
78 GENERATION_EXPRESSION longtext NO NULL
79 SRS_ID int unsigned YES NULL
80 SELECT table_catalog, table_schema, table_name, column_name
81 FROM information_schema.columns WHERE table_catalog IS NULL OR table_catalog <> 'def';
82 TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME
83 ###############################################################################
84 # Testcase 3.2.6.2 + 3.2.6.3: INFORMATION_SCHEMA.COLUMNS accessible information
85 ###############################################################################
86 DROP DATABASE IF EXISTS db_datadict;
87 CREATE DATABASE db_datadict;
88 DROP USER 'testuser1'@'localhost';
89 CREATE USER 'testuser1'@'localhost';
90 DROP USER 'testuser2'@'localhost';
91 CREATE USER 'testuser2'@'localhost';
92 CREATE TABLE db_datadict.t1
93 (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT AUTO_INCREMENT,
94 UNIQUE INDEX MUL_IDX(f1,f3), PRIMARY KEY (f4))
95 ENGINE = <other_engine_type>;
96 ANALYZE TABLE db_datadict.t1;
97 Table Op Msg_type Msg_text
98 db_datadict.t1 analyze status OK
99 CREATE VIEW db_datadict.v1 AS SELECT 1 AS f1, 1 AS f2;
100 GRANT SELECT(f1, f2) ON db_datadict.t1 TO 'testuser1'@'localhost';
101 GRANT SELECT(f2) ON db_datadict.v1 TO 'testuser1'@'localhost';
102 CREATE TABLE db_datadict.t2
103 (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT, PRIMARY KEY (f1,f4))
104 ENGINE = <other_engine_type>;
105 ANALYZE TABLE db_datadict.t2;
106 Table Op Msg_type Msg_text
107 db_datadict.t2 analyze status OK
108 GRANT INSERT(f1, f2) ON db_datadict.t2 TO 'testuser2'@'localhost';
109 SELECT * FROM information_schema.columns
110 WHERE table_schema = 'db_datadict'
111 ORDER BY table_schema, table_name, ordinal_position;
112 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 GENERATION_EXPRESSION SRS_ID
113 def db_datadict t1 f1 1 NULL YES char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) MUL select,insert,update,references NULL
114 def db_datadict t1 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text select,insert,update,references NULL
115 def db_datadict t1 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL NULL date select,insert,update,references NULL
116 def db_datadict t1 f4 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int PRI auto_increment select,insert,update,references NULL
117 def db_datadict t2 f1 1 NULL NO char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) PRI select,insert,update,references NULL
118 def db_datadict t2 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text select,insert,update,references NULL
119 def db_datadict t2 f3 3 NULL YES date NULL NULL NULL NULL NULL NULL NULL date select,insert,update,references NULL
120 def db_datadict t2 f4 4 NULL NO int NULL NULL 10 0 NULL NULL NULL int PRI select,insert,update,references NULL
121 def db_datadict v1 f1 1 0 NO int NULL NULL 10 0 NULL NULL NULL int select,insert,update,references NULL
122 def db_datadict v1 f2 2 0 NO int NULL NULL 10 0 NULL NULL NULL int select,insert,update,references NULL
123 SHOW COLUMNS FROM db_datadict.t1;
124 Field Type Null Key Default Extra
125 f1 char(10) YES MUL NULL
126 f2 text YES NULL
127 f3 date YES NULL
128 f4 int NO PRI NULL auto_increment
129 SHOW COLUMNS FROM db_datadict.t2;
130 Field Type Null Key Default Extra
131 f1 char(10) NO PRI NULL
132 f2 text YES NULL
133 f3 date YES NULL
134 f4 int NO PRI NULL
135 SHOW COLUMNS FROM db_datadict.v1;
136 Field Type Null Key Default Extra
137 f1 int NO 0
138 f2 int NO 0
139 # Establish connection testuser1 (user=testuser1)
140 SELECT * FROM information_schema.columns
141 WHERE table_schema = 'db_datadict'
142 ORDER BY table_schema, table_name, ordinal_position;
143 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 GENERATION_EXPRESSION SRS_ID
144 def db_datadict t1 f1 1 NULL YES char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) MUL select NULL
145 def db_datadict t1 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text select NULL
146 def db_datadict v1 f2 2 0 NO int NULL NULL 10 0 NULL NULL NULL int select NULL
147 SHOW COLUMNS FROM db_datadict.t1;
148 Field Type Null Key Default Extra
149 f1 char(10) YES MUL NULL
150 f2 text YES NULL
151 SHOW COLUMNS FROM db_datadict.t2;
152 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 't2'
153 SHOW COLUMNS FROM db_datadict.v1;
154 Field Type Null Key Default Extra
155 f2 int NO 0
156 # Establish connection testuser2 (user=testuser2)
157 SELECT * FROM information_schema.columns
158 WHERE table_schema = 'db_datadict'
159 ORDER BY table_schema, table_name, ordinal_position;
160 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 GENERATION_EXPRESSION SRS_ID
161 def db_datadict t2 f1 1 NULL NO char 10 40 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci char(10) PRI insert NULL
162 def db_datadict t2 f2 2 NULL YES text 65535 65535 NULL NULL NULL utf8mb4 utf8mb4_0900_ai_ci text insert NULL
163 SHOW COLUMNS FROM db_datadict.t1;
164 ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 't1'
165 SHOW COLUMNS FROM db_datadict.t2;
166 Field Type Null Key Default Extra
167 f1 char(10) NO PRI NULL
168 f2 text YES NULL
169 SHOW COLUMNS FROM db_datadict.v1;
170 ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'v1'
171 # Switch to connection default and close connections testuser1, testuser2
172 DROP USER 'testuser1'@'localhost';
173 DROP USER 'testuser2'@'localhost';
174 DROP DATABASE IF EXISTS db_datadict;
175 ###############################################################################
176 # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMNS modifications
177 ###############################################################################
178 DROP TABLE IF EXISTS test.t1_my_table;
179 DROP DATABASE IF EXISTS db_datadict;
180 CREATE DATABASE db_datadict;
181 SELECT table_name FROM information_schema.columns
182 WHERE table_name LIKE 't1_my_table%';
183 TABLE_NAME
184 CREATE TABLE test.t1_my_table (f1 CHAR(12))
185 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
186 ENGINE = <engine_type>;
187 SELECT * FROM information_schema.columns
188 WHERE table_name = 't1_my_table';
189 TABLE_CATALOG def
190 TABLE_SCHEMA test
191 TABLE_NAME t1_my_table
192 COLUMN_NAME f1
193 ORDINAL_POSITION 1
194 COLUMN_DEFAULT NULL
195 IS_NULLABLE YES
196 DATA_TYPE char
197 CHARACTER_MAXIMUM_LENGTH 12
198 CHARACTER_OCTET_LENGTH 12
199 NUMERIC_PRECISION NULL
200 NUMERIC_SCALE NULL
201 DATETIME_PRECISION NULL
202 CHARACTER_SET_NAME latin1
203 COLLATION_NAME latin1_swedish_ci
204 COLUMN_TYPE char(12)
205 COLUMN_KEY
206 EXTRA
207 PRIVILEGES select,insert,update,references
208 COLUMN_COMMENT
209 GENERATION_EXPRESSION
210 SRS_ID NULL
211 SELECT table_name FROM information_schema.columns
212 WHERE table_name LIKE 't1_my_table%';
213 TABLE_NAME
214 t1_my_table
215 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
216 SELECT table_name FROM information_schema.columns
217 WHERE table_name LIKE 't1_my_table%';
218 TABLE_NAME
219 t1_my_tablex
220 SELECT table_schema,table_name FROM information_schema.columns
221 WHERE table_name = 't1_my_tablex';
222 TABLE_SCHEMA TABLE_NAME
223 test t1_my_tablex
224 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
225 SELECT table_schema,table_name FROM information_schema.columns
226 WHERE table_name = 't1_my_tablex';
227 TABLE_SCHEMA TABLE_NAME
228 db_datadict t1_my_tablex
229 SELECT table_name, column_name FROM information_schema.columns
230 WHERE table_name = 't1_my_tablex';
231 TABLE_NAME COLUMN_NAME
232 t1_my_tablex f1
233 ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
234 SELECT table_name, column_name FROM information_schema.columns
235 WHERE table_name = 't1_my_tablex';
236 TABLE_NAME COLUMN_NAME
237 t1_my_tablex first_col
238 SELECT table_name, column_name, character_maximum_length,
239 character_octet_length, column_type
240 FROM information_schema.columns
241 WHERE table_name = 't1_my_tablex';
242 TABLE_NAME COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH COLUMN_TYPE
243 t1_my_tablex first_col 12 12 char(12)
244 ALTER TABLE db_datadict.t1_my_tablex
245 MODIFY COLUMN first_col CHAR(20);
246 SELECT table_name, column_name, character_maximum_length,
247 character_octet_length, column_type
248 FROM information_schema.columns
249 WHERE table_name = 't1_my_tablex';
250 TABLE_NAME COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH COLUMN_TYPE
251 t1_my_tablex first_col 20 20 char(20)
252 SELECT table_name, column_name, character_maximum_length,
253 character_octet_length, column_type
254 FROM information_schema.columns
255 WHERE table_name = 't1_my_tablex';
256 TABLE_NAME COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH COLUMN_TYPE
257 t1_my_tablex first_col 20 20 char(20)
258 ALTER TABLE db_datadict.t1_my_tablex
259 MODIFY COLUMN first_col VARCHAR(20);
260 SELECT table_name, column_name, character_maximum_length,
261 character_octet_length, column_type
262 FROM information_schema.columns
263 WHERE table_name = 't1_my_tablex';
264 TABLE_NAME COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH COLUMN_TYPE
265 t1_my_tablex first_col 20 20 varchar(20)
266 SELECT table_name, column_name, column_default
267 FROM information_schema.columns
268 WHERE table_name = 't1_my_tablex';
269 TABLE_NAME COLUMN_NAME COLUMN_DEFAULT
270 t1_my_tablex first_col NULL
271 ALTER TABLE db_datadict.t1_my_tablex
272 MODIFY COLUMN first_col CHAR(10) DEFAULT 'hello';
273 SELECT table_name, column_name, column_default
274 FROM information_schema.columns
275 WHERE table_name = 't1_my_tablex';
276 TABLE_NAME COLUMN_NAME COLUMN_DEFAULT
277 t1_my_tablex first_col hello
278 SELECT table_name, column_name, is_nullable
279 FROM information_schema.columns
280 WHERE table_name = 't1_my_tablex';
281 TABLE_NAME COLUMN_NAME IS_NULLABLE
282 t1_my_tablex first_col YES
283 ALTER TABLE db_datadict.t1_my_tablex
284 MODIFY COLUMN first_col CHAR(10) NOT NULL;
285 SELECT table_name, column_name, is_nullable
286 FROM information_schema.columns
287 WHERE table_name = 't1_my_tablex';
288 TABLE_NAME COLUMN_NAME IS_NULLABLE
289 t1_my_tablex first_col NO
290 SELECT table_name, column_name, collation_name
291 FROM information_schema.columns
292 WHERE table_name = 't1_my_tablex';
293 TABLE_NAME COLUMN_NAME COLLATION_NAME
294 t1_my_tablex first_col latin1_swedish_ci
295 ALTER TABLE db_datadict.t1_my_tablex
296 MODIFY COLUMN first_col CHAR(10) COLLATE 'latin1_general_cs';
297 SELECT table_name, column_name, collation_name
298 FROM information_schema.columns
299 WHERE table_name = 't1_my_tablex';
300 TABLE_NAME COLUMN_NAME COLLATION_NAME
301 t1_my_tablex first_col latin1_general_cs
302 SELECT table_name, column_name, character_maximum_length,
303 character_octet_length, character_set_name
304 FROM information_schema.columns
305 WHERE table_name = 't1_my_tablex';
306 TABLE_NAME COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH CHARACTER_SET_NAME
307 t1_my_tablex first_col 10 10 latin1
308 ALTER TABLE db_datadict.t1_my_tablex
309 MODIFY COLUMN first_col CHAR(10) CHARACTER SET utf8;
310 Warnings:
311 Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
312 SELECT table_name, column_name, character_maximum_length,
313 character_octet_length, character_set_name
314 FROM information_schema.columns
315 WHERE table_name = 't1_my_tablex';
316 TABLE_NAME COLUMN_NAME CHARACTER_MAXIMUM_LENGTH CHARACTER_OCTET_LENGTH CHARACTER_SET_NAME
317 t1_my_tablex first_col 10 30 utf8mb3
318 SELECT table_name, column_name, column_comment
319 FROM information_schema.columns
320 WHERE table_name = 't1_my_tablex';
321 TABLE_NAME COLUMN_NAME COLUMN_COMMENT
322 t1_my_tablex first_col
323 ALTER TABLE db_datadict.t1_my_tablex
324 MODIFY COLUMN first_col CHAR(10) COMMENT 'Hello';
325 SELECT table_name, column_name, column_comment
326 FROM information_schema.columns
327 WHERE table_name = 't1_my_tablex';
328 TABLE_NAME COLUMN_NAME COLUMN_COMMENT
329 t1_my_tablex first_col Hello
330 SELECT table_name, column_name
331 FROM information_schema.columns
332 WHERE table_name = 't1_my_tablex';
333 TABLE_NAME COLUMN_NAME
334 t1_my_tablex first_col
335 ALTER TABLE db_datadict.t1_my_tablex
336 ADD COLUMN second_col CHAR(10);
337 SELECT table_name, column_name
338 FROM information_schema.columns
339 WHERE table_name = 't1_my_tablex'
340 ORDER BY table_name, column_name;
341 TABLE_NAME COLUMN_NAME
342 t1_my_tablex first_col
343 t1_my_tablex second_col
344 SELECT table_name, column_name, ordinal_position
345 FROM information_schema.columns
346 WHERE table_name = 't1_my_tablex'
347 ORDER BY table_name, column_name;
348 TABLE_NAME COLUMN_NAME ORDINAL_POSITION
349 t1_my_tablex first_col 1
350 t1_my_tablex second_col 2
351 ALTER TABLE db_datadict.t1_my_tablex
352 MODIFY COLUMN second_col CHAR(10) FIRST;
353 SELECT table_name, column_name, ordinal_position
354 FROM information_schema.columns
355 WHERE table_name = 't1_my_tablex'
356 ORDER BY table_name, column_name;
357 TABLE_NAME COLUMN_NAME ORDINAL_POSITION
358 t1_my_tablex first_col 2
359 t1_my_tablex second_col 1
360 SELECT table_name, column_name
361 FROM information_schema.columns
362 WHERE table_name = 't1_my_tablex'
363 ORDER BY table_name, column_name;
364 TABLE_NAME COLUMN_NAME
365 t1_my_tablex first_col
366 t1_my_tablex second_col
367 ALTER TABLE db_datadict.t1_my_tablex
368 DROP COLUMN first_col;
369 SELECT table_name, column_name
370 FROM information_schema.columns
371 WHERE table_name = 't1_my_tablex';
372 TABLE_NAME COLUMN_NAME
373 t1_my_tablex second_col
374 SELECT table_name, column_name, column_key
375 FROM information_schema.columns
376 WHERE table_name = 't1_my_tablex';
377 TABLE_NAME COLUMN_NAME COLUMN_KEY
378 t1_my_tablex second_col
379 ALTER TABLE db_datadict.t1_my_tablex
380 ADD UNIQUE INDEX IDX(second_col);
381 SELECT table_name, column_name, column_key
382 FROM information_schema.columns
383 WHERE table_name = 't1_my_tablex';
384 TABLE_NAME COLUMN_NAME COLUMN_KEY
385 t1_my_tablex second_col UNI
386 SELECT table_name, column_name
387 FROM information_schema.columns
388 WHERE table_name = 't1_my_tablex';
389 TABLE_NAME COLUMN_NAME
390 t1_my_tablex second_col
391 DROP TABLE db_datadict.t1_my_tablex;
392 SELECT table_name, column_name
393 FROM information_schema.columns
394 WHERE table_name = 't1_my_tablex';
395 TABLE_NAME COLUMN_NAME
396 set names latin1;
397 SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
398 CREATE VIEW test.t1_my_tablex
399 AS SELECT 1 AS "col1", 'A' collate latin1_german1_ci AS "col2";
400 SELECT * FROM information_schema.columns
401 WHERE table_name = 't1_my_tablex'
402 ORDER BY table_name, column_name;
403 TABLE_CATALOG def
404 TABLE_SCHEMA test
405 TABLE_NAME t1_my_tablex
406 COLUMN_NAME col1
407 ORDINAL_POSITION 1
408 COLUMN_DEFAULT 0
409 IS_NULLABLE NO
410 DATA_TYPE int
411 CHARACTER_MAXIMUM_LENGTH NULL
412 CHARACTER_OCTET_LENGTH NULL
413 NUMERIC_PRECISION 10
414 NUMERIC_SCALE 0
415 DATETIME_PRECISION NULL
416 CHARACTER_SET_NAME NULL
417 COLLATION_NAME NULL
418 COLUMN_TYPE int
419 COLUMN_KEY
420 EXTRA
421 PRIVILEGES select,insert,update,references
422 COLUMN_COMMENT
423 GENERATION_EXPRESSION
424 SRS_ID NULL
425 TABLE_CATALOG def
426 TABLE_SCHEMA test
427 TABLE_NAME t1_my_tablex
428 COLUMN_NAME col2
429 ORDINAL_POSITION 2
430 COLUMN_DEFAULT
431 IS_NULLABLE NO
432 DATA_TYPE varchar
433 CHARACTER_MAXIMUM_LENGTH 1
434 CHARACTER_OCTET_LENGTH 1
435 NUMERIC_PRECISION NULL
436 NUMERIC_SCALE NULL
437 DATETIME_PRECISION NULL
438 CHARACTER_SET_NAME latin1
439 COLLATION_NAME latin1_german1_ci
440 COLUMN_TYPE varchar(1)
441 COLUMN_KEY
442 EXTRA
443 PRIVILEGES select,insert,update,references
444 COLUMN_COMMENT
445 GENERATION_EXPRESSION
446 SRS_ID NULL
447 DROP VIEW test.t1_my_tablex;
448 SELECT table_name FROM information_schema.columns
449 WHERE table_name = 't1_my_tablex';
450 TABLE_NAME
451 SET sql_mode = default;
452 set names utf8mb4;
453 CREATE TABLE db_datadict.t1_my_tablex
454 ENGINE = <engine_type> AS
455 SELECT 1;
456 SELECT table_name FROM information_schema.columns
457 WHERE table_name = 't1_my_tablex';
458 TABLE_NAME
459 t1_my_tablex
460 DROP DATABASE db_datadict;
461 SELECT table_name FROM information_schema.columns
462 WHERE table_name = 't1_my_tablex';
463 TABLE_NAME
464 ########################################################################
465 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
466 # DDL on INFORMATION_SCHEMA table are not supported
467 ########################################################################
468 DROP DATABASE IF EXISTS db_datadict;
469 DROP TABLE IF EXISTS test.t1;
470 CREATE DATABASE db_datadict;
471 CREATE TABLE test.t1 (f1 BIGINT);
472 INSERT INTO information_schema.columns (table_schema,table_name,column_name)
473 VALUES('test','t1', 'f2');
474 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
475 INSERT INTO information_schema.columns (table_schema,table_name,column_name)
476 VALUES('test','t2', 'f1');
477 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
478 UPDATE information_schema.columns SET table_name = 't4' WHERE table_name = 't1';
479 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
480 DELETE FROM information_schema.columns WHERE table_name = 't1';
481 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
482 TRUNCATE information_schema.columns;
483 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
484 CREATE INDEX i3 ON information_schema.columns(table_name);
485 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
486 ALTER TABLE information_schema.columns ADD f1 INT;
487 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
488 DROP TABLE information_schema.columns;
489 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
490 ALTER TABLE information_schema.columns RENAME db_datadict.columns;
491 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
492 ALTER TABLE information_schema.columns RENAME information_schema.xcolumns;
493 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
494 DROP TABLE test.t1;
495 DROP DATABASE db_datadict;