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(512)	NO		NULL	
TABLE_SCHEMA	varchar(64)	NO		NULL	
TABLE_NAME	varchar(64)	NO		NULL	
COLUMN_NAME	varchar(64)	NO		NULL	
ORDINAL_POSITION	bigint(21) unsigned	NO		NULL	
COLUMN_DEFAULT	longtext	YES		NULL	
IS_NULLABLE	varchar(3)	NO		NULL	
DATA_TYPE	varchar(64)	NO		NULL	
CHARACTER_MAXIMUM_LENGTH	bigint(21) unsigned	YES		NULL	
CHARACTER_OCTET_LENGTH	bigint(21) unsigned	YES		NULL	
NUMERIC_PRECISION	bigint(21) unsigned	YES		NULL	
NUMERIC_SCALE	bigint(21) unsigned	YES		NULL	
DATETIME_PRECISION	bigint(21) unsigned	YES		NULL	
CHARACTER_SET_NAME	varchar(32)	YES		NULL	
COLLATION_NAME	varchar(64)	YES		NULL	
COLUMN_TYPE	longtext	NO		NULL	
COLUMN_KEY	varchar(3)	NO		NULL	
EXTRA	varchar(80)	NO		NULL	
PRIVILEGES	varchar(80)	NO		NULL	
COLUMN_COMMENT	varchar(1024)	NO		NULL	
IS_GENERATED	varchar(6)	NO		NULL	
GENERATION_EXPRESSION	longtext	YES		NULL	
IS_SYSTEM_TIME_PERIOD_START	varchar(3)	NO		NULL	
IS_SYSTEM_TIME_PERIOD_END	varchar(3)	NO		NULL	
SHOW CREATE TABLE information_schema.COLUMNS;
Table	Create Table
COLUMNS	CREATE TEMPORARY TABLE `COLUMNS` (
  `TABLE_CATALOG` varchar(512) NOT NULL,
  `TABLE_SCHEMA` varchar(64) NOT NULL,
  `TABLE_NAME` varchar(64) NOT NULL,
  `COLUMN_NAME` varchar(64) NOT NULL,
  `ORDINAL_POSITION` bigint(21) unsigned NOT NULL,
  `COLUMN_DEFAULT` longtext,
  `IS_NULLABLE` varchar(3) NOT NULL,
  `DATA_TYPE` varchar(64) NOT NULL,
  `CHARACTER_MAXIMUM_LENGTH` bigint(21) unsigned,
  `CHARACTER_OCTET_LENGTH` bigint(21) unsigned,
  `NUMERIC_PRECISION` bigint(21) unsigned,
  `NUMERIC_SCALE` bigint(21) unsigned,
  `DATETIME_PRECISION` bigint(21) unsigned,
  `CHARACTER_SET_NAME` varchar(32),
  `COLLATION_NAME` varchar(64),
  `COLUMN_TYPE` longtext NOT NULL,
  `COLUMN_KEY` varchar(3) NOT NULL,
  `EXTRA` varchar(80) NOT NULL,
  `PRIVILEGES` varchar(80) NOT NULL,
  `COLUMN_COMMENT` varchar(1024) NOT NULL,
  `IS_GENERATED` varchar(6) NOT NULL,
  `GENERATION_EXPRESSION` longtext,
  `IS_SYSTEM_TIME_PERIOD_START` varchar(3) NOT NULL,
  `IS_SYSTEM_TIME_PERIOD_END` varchar(3) NOT NULL
)  DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
SHOW COLUMNS FROM information_schema.COLUMNS;
Field	Type	Null	Key	Default	Extra
TABLE_CATALOG	varchar(512)	NO		NULL	
TABLE_SCHEMA	varchar(64)	NO		NULL	
TABLE_NAME	varchar(64)	NO		NULL	
COLUMN_NAME	varchar(64)	NO		NULL	
ORDINAL_POSITION	bigint(21) unsigned	NO		NULL	
COLUMN_DEFAULT	longtext	YES		NULL	
IS_NULLABLE	varchar(3)	NO		NULL	
DATA_TYPE	varchar(64)	NO		NULL	
CHARACTER_MAXIMUM_LENGTH	bigint(21) unsigned	YES		NULL	
CHARACTER_OCTET_LENGTH	bigint(21) unsigned	YES		NULL	
NUMERIC_PRECISION	bigint(21) unsigned	YES		NULL	
NUMERIC_SCALE	bigint(21) unsigned	YES		NULL	
DATETIME_PRECISION	bigint(21) unsigned	YES		NULL	
CHARACTER_SET_NAME	varchar(32)	YES		NULL	
COLLATION_NAME	varchar(64)	YES		NULL	
COLUMN_TYPE	longtext	NO		NULL	
COLUMN_KEY	varchar(3)	NO		NULL	
EXTRA	varchar(80)	NO		NULL	
PRIVILEGES	varchar(80)	NO		NULL	
COLUMN_COMMENT	varchar(1024)	NO		NULL	
IS_GENERATED	varchar(6)	NO		NULL	
GENERATION_EXPRESSION	longtext	YES		NULL	
IS_SYSTEM_TIME_PERIOD_START	varchar(3)	NO		NULL	
IS_SYSTEM_TIME_PERIOD_END	varchar(3)	NO		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
Warnings:
Warning	1286	Unknown storage engine 'InnoDB'
Warning	1286	Unknown storage engine 'InnoDB'
Warning	1286	Unknown storage engine 'InnoDB'
###############################################################################
# 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>;
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>;
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	IS_GENERATED	GENERATION_EXPRESSION	IS_SYSTEM_TIME_PERIOD_START	IS_SYSTEM_TIME_PERIOD_END
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
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
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
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
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
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
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
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
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
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
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	
connect  testuser1, localhost, testuser1, , db_datadict;
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	IS_GENERATED	GENERATION_EXPRESSION	IS_SYSTEM_TIME_PERIOD_START	IS_SYSTEM_TIME_PERIOD_END
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
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
def	db_datadict	v1	f2	2	0	NO	int	NULL	NULL	10	0	NULL	NULL	NULL	int(1)			select		NEVER	NULL	NO	NO
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 `db_datadict`.`t2`
SHOW COLUMNS FROM db_datadict.v1;
Field	Type	Null	Key	Default	Extra
f2	int(1)	NO		0	
connect  testuser2, localhost, testuser2, , db_datadict;
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	IS_GENERATED	GENERATION_EXPRESSION	IS_SYSTEM_TIME_PERIOD_START	IS_SYSTEM_TIME_PERIOD_END
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
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
SHOW COLUMNS FROM db_datadict.t1;
ERROR 42000: SELECT command denied to user 'testuser2'@'localhost' for table `db_datadict`.`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 `db_datadict`.`v1`
connection default;
disconnect testuser1;
disconnect 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_swedish_ci
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_swedish_ci
COLUMN_TYPE	char(12)
COLUMN_KEY	
EXTRA	
PRIVILEGES	select,insert,update,references
COLUMN_COMMENT	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
IS_SYSTEM_TIME_PERIOD_START	NO
IS_SYSTEM_TIME_PERIOD_END	NO
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_swedish_ci
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;
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	utf8mb3
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';
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';
table_name	column_name
t1_my_tablex	second_col
t1_my_tablex	first_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
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	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
IS_SYSTEM_TIME_PERIOD_START	NO
IS_SYSTEM_TIME_PERIOD_END	NO
TABLE_CATALOG	def
TABLE_SCHEMA	test
TABLE_NAME	t1_my_tablex
COLUMN_NAME	col2
ORDINAL_POSITION	2
COLUMN_DEFAULT	NULL
IS_NULLABLE	YES
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	
IS_GENERATED	NEVER
GENERATION_EXPRESSION	NULL
IS_SYSTEM_TIME_PERIOD_START	NO
IS_SYSTEM_TIME_PERIOD_END	NO
DROP VIEW test.t1_my_tablex;
SELECT table_name FROM information_schema.columns
WHERE table_name = 't1_my_tablex';
table_name
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;