SHOW TABLES FROM information_schema LIKE 'COLUMNS';
Tables_in_information_schema (COLUMNS)
COLUMNS
#######################################################################
# Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
#######################################################################
DROP VIEW      IF EXISTS test.v1;
DROP PROCEDURE IF EXISTS test.p1;
DROP FUNCTION  IF EXISTS test.f1;
CREATE VIEW test.v1 AS     SELECT * FROM information_schema.COLUMNS;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.COLUMNS;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.COLUMNS;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.COLUMNS;
SELECT * FROM test.v1;
CALL test.p1;
SELECT test.f1();
DROP VIEW test.v1;
DROP PROCEDURE test.p1;
DROP FUNCTION test.f1;
#########################################################################
# Testcase 3.2.6.1: INFORMATION_SCHEMA.COLUMNS layout
#########################################################################
DESCRIBE          information_schema.COLUMNS;
Field	Type	Null	Key	Default	Extra
TABLE_CATALOG	varchar(64)	NO		NULL	
TABLE_SCHEMA	varchar(64)	NO		NULL	
TABLE_NAME	varchar(64)	NO		NULL	
COLUMN_NAME	varchar(64)	YES		NULL	
ORDINAL_POSITION	int(10) unsigned	NO		NULL	
COLUMN_DEFAULT	text	YES		NULL	
IS_NULLABLE	varchar(3)	NO			
DATA_TYPE	longtext	YES		NULL	
CHARACTER_MAXIMUM_LENGTH	bigint(21)	YES		NULL	
CHARACTER_OCTET_LENGTH	bigint(21)	YES		NULL	
NUMERIC_PRECISION	bigint(10) unsigned	YES		NULL	
NUMERIC_SCALE	bigint(10) unsigned	YES		NULL	
DATETIME_PRECISION	int(10) unsigned	YES		NULL	
CHARACTER_SET_NAME	varchar(64)	YES		NULL	
COLLATION_NAME	varchar(64)	YES		NULL	
COLUMN_TYPE	mediumtext	NO		NULL	
COLUMN_KEY	enum('','PRI','UNI','MUL')	NO		NULL	
EXTRA	varchar(256)	YES		NULL	
PRIVILEGES	varchar(154)	YES		NULL	
COLUMN_COMMENT	text	NO		NULL	
GENERATION_EXPRESSION	longtext	NO		NULL	
SRS_ID	int(10) unsigned	YES		NULL	
SHOW CREATE TABLE information_schema.COLUMNS;
View	Create View	character_set_client	collation_connection
COLUMNS	CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`COLUMNS` AS select `cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,(`col`.`name` collate utf8_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`) 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` <> 'Visible'))))	utf8	utf8_general_ci
SHOW COLUMNS FROM information_schema.COLUMNS;
Field	Type	Null	Key	Default	Extra
TABLE_CATALOG	varchar(64)	NO		NULL	
TABLE_SCHEMA	varchar(64)	NO		NULL	
TABLE_NAME	varchar(64)	NO		NULL	
COLUMN_NAME	varchar(64)	YES		NULL	
ORDINAL_POSITION	int(10) unsigned	NO		NULL	
COLUMN_DEFAULT	text	YES		NULL	
IS_NULLABLE	varchar(3)	NO			
DATA_TYPE	longtext	YES		NULL	
CHARACTER_MAXIMUM_LENGTH	bigint(21)	YES		NULL	
CHARACTER_OCTET_LENGTH	bigint(21)	YES		NULL	
NUMERIC_PRECISION	bigint(10) unsigned	YES		NULL	
NUMERIC_SCALE	bigint(10) unsigned	YES		NULL	
DATETIME_PRECISION	int(10) unsigned	YES		NULL	
CHARACTER_SET_NAME	varchar(64)	YES		NULL	
COLLATION_NAME	varchar(64)	YES		NULL	
COLUMN_TYPE	mediumtext	NO		NULL	
COLUMN_KEY	enum('','PRI','UNI','MUL')	NO		NULL	
EXTRA	varchar(256)	YES		NULL	
PRIVILEGES	varchar(154)	YES		NULL	
COLUMN_COMMENT	text	NO		NULL	
GENERATION_EXPRESSION	longtext	NO		NULL	
SRS_ID	int(10) unsigned	YES		NULL	
SELECT table_catalog, table_schema, table_name, column_name
FROM information_schema.columns WHERE table_catalog IS NULL OR table_catalog <> 'def';
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME
###############################################################################
# Testcase 3.2.6.2 + 3.2.6.3: INFORMATION_SCHEMA.COLUMNS accessible information
###############################################################################
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
DROP   USER 'testuser1'@'localhost';
CREATE USER 'testuser1'@'localhost';
DROP   USER 'testuser2'@'localhost';
CREATE USER 'testuser2'@'localhost';
CREATE TABLE db_datadict.t1
(f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT AUTO_INCREMENT,
UNIQUE INDEX MUL_IDX(f1,f3), PRIMARY KEY (f4))
ENGINE = <other_engine_type>;
ANALYZE TABLE db_datadict.t1;
Table	Op	Msg_type	Msg_text
db_datadict.t1	analyze	status	OK
CREATE VIEW db_datadict.v1 AS SELECT 1 AS f1, 1 AS f2;
GRANT SELECT(f1, f2) ON db_datadict.t1 TO 'testuser1'@'localhost';
GRANT SELECT(f2)     ON db_datadict.v1 TO 'testuser1'@'localhost';
CREATE TABLE db_datadict.t2
(f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT, PRIMARY KEY (f1,f4))
ENGINE = <other_engine_type>;
ANALYZE TABLE db_datadict.t2;
Table	Op	Msg_type	Msg_text
db_datadict.t2	analyze	status	OK
GRANT INSERT(f1, f2) ON db_datadict.t2 TO 'testuser2'@'localhost';
SELECT * FROM information_schema.columns
WHERE table_schema = 'db_datadict'
ORDER BY table_schema, table_name, ordinal_position;
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
def	db_datadict	t1	f1	1	NULL	YES	char	10	40	NULL	NULL	NULL	utf8mb4	utf8mb4_general_ci	char(10)	MUL		select,insert,update,references			NULL
def	db_datadict	t1	f2	2	NULL	YES	text	65535	65535	NULL	NULL	NULL	utf8mb4	utf8mb4_general_ci	text			select,insert,update,references			NULL
def	db_datadict	t1	f3	3	NULL	YES	date	NULL	NULL	NULL	NULL	NULL	NULL	NULL	date			select,insert,update,references			NULL
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			NULL
def	db_datadict	t2	f1	1	NULL	NO	char	10	40	NULL	NULL	NULL	utf8mb4	utf8mb4_general_ci	char(10)	PRI		select,insert,update,references			NULL
def	db_datadict	t2	f2	2	NULL	YES	text	65535	65535	NULL	NULL	NULL	utf8mb4	utf8mb4_general_ci	text			select,insert,update,references			NULL
def	db_datadict	t2	f3	3	NULL	YES	date	NULL	NULL	NULL	NULL	NULL	NULL	NULL	date			select,insert,update,references			NULL
def	db_datadict	t2	f4	4	NULL	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	PRI		select,insert,update,references			NULL
def	db_datadict	v1	f1	1	0	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(1)			select,insert,update,references			NULL
def	db_datadict	v1	f2	2	0	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(1)			select,insert,update,references			NULL
SHOW COLUMNS FROM db_datadict.t1;
Field	Type	Null	Key	Default	Extra
f1	char(10)	YES	MUL	NULL	
f2	text	YES		NULL	
f3	date	YES		NULL	
f4	int(11)	NO	PRI	NULL	auto_increment
SHOW COLUMNS FROM db_datadict.t2;
Field	Type	Null	Key	Default	Extra
f1	char(10)	NO	PRI	NULL	
f2	text	YES		NULL	
f3	date	YES		NULL	
f4	int(11)	NO	PRI	NULL	
SHOW COLUMNS FROM db_datadict.v1;
Field	Type	Null	Key	Default	Extra
f1	int(1)	NO		0	
f2	int(1)	NO		0	
# Establish connection testuser1 (user=testuser1)
SELECT * FROM information_schema.columns
WHERE table_schema = 'db_datadict'
ORDER BY table_schema, table_name, ordinal_position;
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
def	db_datadict	t1	f1	1	NULL	YES	char	10	40	NULL	NULL	NULL	utf8mb4	utf8mb4_general_ci	char(10)	MUL		select			NULL
def	db_datadict	t1	f2	2	NULL	YES	text	65535	65535	NULL	NULL	NULL	utf8mb4	utf8mb4_general_ci	text			select			NULL
def	db_datadict	v1	f2	2	0	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(1)			select			NULL
SHOW COLUMNS FROM db_datadict.t1;
Field	Type	Null	Key	Default	Extra
f1	char(10)	YES	MUL	NULL	
f2	text	YES		NULL	
SHOW COLUMNS FROM db_datadict.t2;
ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table 't2'
SHOW COLUMNS FROM db_datadict.v1;
Field	Type	Null	Key	Default	Extra
f2	int(1)	NO		0	
# Establish connection testuser2 (user=testuser2)
SELECT * FROM information_schema.columns
WHERE table_schema = 'db_datadict'
ORDER BY table_schema, table_name, ordinal_position;
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
def	db_datadict	t2	f1	1	NULL	NO	char	10	40	NULL	NULL	NULL	utf8mb4	utf8mb4_general_ci	char(10)	PRI		insert			NULL
def	db_datadict	t2	f2	2	NULL	YES	text	65535	65535	NULL	NULL	NULL	utf8mb4	utf8mb4_general_ci	text			insert			NULL
SHOW COLUMNS FROM db_datadict.t1;
ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 't1'
SHOW COLUMNS FROM db_datadict.t2;
Field	Type	Null	Key	Default	Extra
f1	char(10)	NO	PRI	NULL	
f2	text	YES		NULL	
SHOW COLUMNS FROM db_datadict.v1;
ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table 'v1'
# Switch to connection default and close connections testuser1, testuser2
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP DATABASE IF EXISTS db_datadict;
###############################################################################
# Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMNS modifications
###############################################################################
DROP TABLE IF EXISTS test.t1_my_table;
DROP DATABASE IF EXISTS db_datadict;
CREATE DATABASE db_datadict;
SELECT table_name FROM information_schema.columns
WHERE table_name LIKE 't1_my_table%';
TABLE_NAME
CREATE TABLE test.t1_my_table (f1 CHAR(12))
DEFAULT CHARACTER SET latin1 COLLATE latin1_bin
ENGINE = <engine_type>;
SELECT * FROM information_schema.columns
WHERE table_name = 't1_my_table';
TABLE_CATALOG	def
TABLE_SCHEMA	test
TABLE_NAME	t1_my_table
COLUMN_NAME	f1
ORDINAL_POSITION	1
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
DATA_TYPE	char
CHARACTER_MAXIMUM_LENGTH	12
CHARACTER_OCTET_LENGTH	12
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	latin1
COLLATION_NAME	latin1_bin
COLUMN_TYPE	char(12)
COLUMN_KEY	
EXTRA	
PRIVILEGES	select,insert,update,references
COLUMN_COMMENT	
GENERATION_EXPRESSION	
SRS_ID	NULL
SELECT table_name FROM information_schema.columns
WHERE table_name LIKE 't1_my_table%';
TABLE_NAME
t1_my_table
RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
SELECT table_name FROM information_schema.columns
WHERE table_name LIKE 't1_my_table%';
TABLE_NAME
t1_my_tablex
SELECT table_schema,table_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_SCHEMA	TABLE_NAME
test	t1_my_tablex
RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
SELECT table_schema,table_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_SCHEMA	TABLE_NAME
db_datadict	t1_my_tablex
SELECT table_name, column_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME
t1_my_tablex	f1
ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
SELECT table_name, column_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME
t1_my_tablex	first_col
SELECT table_name, column_name, character_maximum_length,
character_octet_length, column_type
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	COLUMN_TYPE
t1_my_tablex	first_col	12	12	char(12)
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(20);
SELECT table_name, column_name, character_maximum_length,
character_octet_length, column_type
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	COLUMN_TYPE
t1_my_tablex	first_col	20	20	char(20)
SELECT table_name, column_name, character_maximum_length,
character_octet_length, column_type
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	COLUMN_TYPE
t1_my_tablex	first_col	20	20	char(20)
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col VARCHAR(20);
SELECT table_name, column_name, character_maximum_length,
character_octet_length, column_type
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	COLUMN_TYPE
t1_my_tablex	first_col	20	20	varchar(20)
SELECT table_name, column_name, column_default
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	COLUMN_DEFAULT
t1_my_tablex	first_col	NULL
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(10) DEFAULT 'hello';
SELECT table_name, column_name, column_default
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	COLUMN_DEFAULT
t1_my_tablex	first_col	hello
SELECT table_name, column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	IS_NULLABLE
t1_my_tablex	first_col	YES
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(10) NOT NULL;
SELECT table_name, column_name, is_nullable
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	IS_NULLABLE
t1_my_tablex	first_col	NO
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	COLLATION_NAME
t1_my_tablex	first_col	latin1_bin
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(10) COLLATE 'latin1_general_cs';
SELECT table_name, column_name, collation_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	COLLATION_NAME
t1_my_tablex	first_col	latin1_general_cs
SELECT table_name, column_name, character_maximum_length,
character_octet_length, character_set_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	CHARACTER_SET_NAME
t1_my_tablex	first_col	10	10	latin1
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(10) CHARACTER SET utf8;
Warnings:
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.
SELECT table_name, column_name, character_maximum_length,
character_octet_length, character_set_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	CHARACTER_SET_NAME
t1_my_tablex	first_col	10	30	utf8
SELECT table_name, column_name, column_comment
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	COLUMN_COMMENT
t1_my_tablex	first_col	
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN first_col CHAR(10) COMMENT 'Hello';
SELECT table_name, column_name, column_comment
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	COLUMN_COMMENT
t1_my_tablex	first_col	Hello
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME
t1_my_tablex	first_col
ALTER TABLE db_datadict.t1_my_tablex
ADD COLUMN second_col CHAR(10);
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
TABLE_NAME	COLUMN_NAME
t1_my_tablex	first_col
t1_my_tablex	second_col
SELECT table_name, column_name, ordinal_position
FROM information_schema.columns
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION
t1_my_tablex	first_col	1
t1_my_tablex	second_col	2
ALTER TABLE db_datadict.t1_my_tablex
MODIFY COLUMN second_col CHAR(10) FIRST;
SELECT table_name, column_name, ordinal_position
FROM information_schema.columns
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION
t1_my_tablex	first_col	2
t1_my_tablex	second_col	1
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
TABLE_NAME	COLUMN_NAME
t1_my_tablex	first_col
t1_my_tablex	second_col
ALTER TABLE db_datadict.t1_my_tablex
DROP COLUMN first_col;
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME
t1_my_tablex	second_col
SELECT table_name, column_name, column_key
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	COLUMN_KEY
t1_my_tablex	second_col	
ALTER TABLE db_datadict.t1_my_tablex
ADD UNIQUE INDEX IDX(second_col);
SELECT table_name, column_name, column_key
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME	COLUMN_KEY
t1_my_tablex	second_col	UNI
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME
t1_my_tablex	second_col
DROP TABLE db_datadict.t1_my_tablex;
SELECT table_name, column_name
FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME	COLUMN_NAME
set names latin1;
SET sql_mode = 'NO_ENGINE_SUBSTITUTION';
CREATE VIEW test.t1_my_tablex
AS SELECT 1 AS "col1", 'A' collate latin1_german1_ci AS "col2";
SELECT * FROM information_schema.columns
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
TABLE_CATALOG	def
TABLE_SCHEMA	test
TABLE_NAME	t1_my_tablex
COLUMN_NAME	col1
ORDINAL_POSITION	1
COLUMN_DEFAULT	0
IS_NULLABLE	NO
DATA_TYPE	int
CHARACTER_MAXIMUM_LENGTH	NULL
CHARACTER_OCTET_LENGTH	NULL
NUMERIC_PRECISION	10
NUMERIC_SCALE	0
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	NULL
COLLATION_NAME	NULL
COLUMN_TYPE	int(1)
COLUMN_KEY	
EXTRA	
PRIVILEGES	select,insert,update,references
COLUMN_COMMENT	
GENERATION_EXPRESSION	
SRS_ID	NULL
TABLE_CATALOG	def
TABLE_SCHEMA	test
TABLE_NAME	t1_my_tablex
COLUMN_NAME	col2
ORDINAL_POSITION	2
COLUMN_DEFAULT	
IS_NULLABLE	NO
DATA_TYPE	varchar
CHARACTER_MAXIMUM_LENGTH	1
CHARACTER_OCTET_LENGTH	1
NUMERIC_PRECISION	NULL
NUMERIC_SCALE	NULL
DATETIME_PRECISION	NULL
CHARACTER_SET_NAME	latin1
COLLATION_NAME	latin1_german1_ci
COLUMN_TYPE	varchar(1)
COLUMN_KEY	
EXTRA	
PRIVILEGES	select,insert,update,references
COLUMN_COMMENT	
GENERATION_EXPRESSION	
SRS_ID	NULL
DROP VIEW test.t1_my_tablex;
SELECT table_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME
SET sql_mode = default;
set names utf8mb4;
CREATE TABLE db_datadict.t1_my_tablex
ENGINE = <engine_type> AS
SELECT 1;
SELECT table_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME
t1_my_tablex
DROP DATABASE db_datadict;
SELECT table_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
TABLE_NAME
########################################################################
# Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
#           DDL on INFORMATION_SCHEMA table are not supported
########################################################################
DROP DATABASE IF EXISTS db_datadict;
DROP TABLE IF EXISTS test.t1;
CREATE DATABASE db_datadict;
CREATE TABLE test.t1 (f1 BIGINT);
INSERT INTO information_schema.columns (table_schema,table_name,column_name)
VALUES('test','t1', 'f2');
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
INSERT INTO information_schema.columns (table_schema,table_name,column_name)
VALUES('test','t2', 'f1');
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.columns SET table_name = 't4' WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.columns WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.columns;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX i3 ON information_schema.columns(table_name);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.columns ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.columns;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.columns RENAME db_datadict.columns;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.columns RENAME information_schema.xcolumns;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE test.t1;
DROP DATABASE db_datadict;