SHOW TABLES FROM information_schema LIKE 'KEY_COLUMN_USAGE';
Tables_in_information_schema (KEY_COLUMN_USAGE)
KEY_COLUMN_USAGE
#######################################################################
# 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.KEY_COLUMN_USAGE;
CREATE PROCEDURE test.p1() SELECT * FROM information_schema.KEY_COLUMN_USAGE;
CREATE FUNCTION test.f1() returns BIGINT
BEGIN
DECLARE counter BIGINT DEFAULT NULL;
SELECT COUNT(*) INTO counter FROM information_schema.KEY_COLUMN_USAGE;
RETURN counter;
END//
# Attention: The printing of the next result sets is disabled.
SELECT * FROM information_schema.KEY_COLUMN_USAGE;
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.7.1: INFORMATION_SCHEMA.KEY_COLUMN_USAGE layout
#########################################################################
DESCRIBE          information_schema.KEY_COLUMN_USAGE;
Field	Type	Null	Key	Default	Extra
CONSTRAINT_CATALOG	varchar(64)	NO			
CONSTRAINT_SCHEMA	varchar(64)	NO			
CONSTRAINT_NAME	varchar(64)	YES		NULL	
TABLE_CATALOG	varchar(64)	NO			
TABLE_SCHEMA	varchar(64)	NO			
TABLE_NAME	varchar(64)	NO			
COLUMN_NAME	varchar(64)	YES		NULL	
ORDINAL_POSITION	int(10) unsigned	NO		0	
POSITION_IN_UNIQUE_CONSTRAINT	int(10) unsigned	YES		NULL	
REFERENCED_TABLE_SCHEMA	varchar(64)	YES		NULL	
REFERENCED_TABLE_NAME	varchar(64)	YES		NULL	
REFERENCED_COLUMN_NAME	varchar(64)	YES		NULL	
SHOW CREATE TABLE information_schema.KEY_COLUMN_USAGE;
View	Create View	character_set_client	collation_connection
KEY_COLUMN_USAGE	CREATE ALGORITHM=UNDEFINED DEFINER=`mysql.infoschema`@`localhost` SQL SECURITY DEFINER VIEW `information_schema`.`KEY_COLUMN_USAGE` AS select `cat`.`name` AS `CONSTRAINT_CATALOG`,`sch`.`name` AS `CONSTRAINT_SCHEMA`,`idx`.`name` AS `CONSTRAINT_NAME`,`cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,(`col`.`name` collate utf8_tolower_ci) AS `COLUMN_NAME`,`icu`.`ordinal_position` AS `ORDINAL_POSITION`,NULL AS `POSITION_IN_UNIQUE_CONSTRAINT`,NULL AS `REFERENCED_TABLE_SCHEMA`,NULL AS `REFERENCED_TABLE_NAME`,NULL AS `REFERENCED_COLUMN_NAME` from (((((`mysql`.`indexes` `idx` join `mysql`.`tables` `tbl` on((`idx`.`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`.`index_column_usage` `icu` on((`icu`.`index_id` = `idx`.`id`))) join `mysql`.`columns` `col` on(((`icu`.`column_id` = `col`.`id`) and (`idx`.`type` in ('PRIMARY','UNIQUE'))))) where ((0 <> can_access_column(`sch`.`name`,`tbl`.`name`,`col`.`name`)) and (0 <> is_visible_dd_object(`tbl`.`hidden`,((`col`.`hidden` <> 'Visible') or (0 <> `idx`.`hidden`) or (0 <> `icu`.`hidden`))))) union select `cat`.`name` AS `CONSTRAINT_CATALOG`,`sch`.`name` AS `CONSTRAINT_SCHEMA`,(`fk`.`name` collate utf8_tolower_ci) AS `CONSTRAINT_NAME`,`cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,(`col`.`name` collate utf8_tolower_ci) AS `COLUMN_NAME`,`fkcu`.`ordinal_position` AS `ORDINAL_POSITION`,`fkcu`.`ordinal_position` AS `POSITION_IN_UNIQUE_CONSTRAINT`,`fk`.`referenced_table_schema` AS `REFERENCED_TABLE_SCHEMA`,`fk`.`referenced_table_name` AS `REFERENCED_TABLE_NAME`,`fkcu`.`referenced_column_name` AS `REFERENCED_COLUMN_NAME` from (((((`mysql`.`foreign_keys` `fk` join `mysql`.`tables` `tbl` on((`fk`.`table_id` = `tbl`.`id`))) join `mysql`.`foreign_key_column_usage` `fkcu` on((`fkcu`.`foreign_key_id` = `fk`.`id`))) join `mysql`.`schemata` `sch` on((`fk`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join `mysql`.`columns` `col` on((`fkcu`.`column_id` = `col`.`id`))) where ((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.KEY_COLUMN_USAGE;
Field	Type	Null	Key	Default	Extra
CONSTRAINT_CATALOG	varchar(64)	NO			
CONSTRAINT_SCHEMA	varchar(64)	NO			
CONSTRAINT_NAME	varchar(64)	YES		NULL	
TABLE_CATALOG	varchar(64)	NO			
TABLE_SCHEMA	varchar(64)	NO			
TABLE_NAME	varchar(64)	NO			
COLUMN_NAME	varchar(64)	YES		NULL	
ORDINAL_POSITION	int(10) unsigned	NO		0	
POSITION_IN_UNIQUE_CONSTRAINT	int(10) unsigned	YES		NULL	
REFERENCED_TABLE_SCHEMA	varchar(64)	YES		NULL	
REFERENCED_TABLE_NAME	varchar(64)	YES		NULL	
REFERENCED_COLUMN_NAME	varchar(64)	YES		NULL	
SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog,
table_schema, table_name, column_name
FROM information_schema.key_column_usage
WHERE (constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL) AND
table_name != 'ndb_binlog_index'
ORDER BY constraint_name, table_name, column_name;
constraint_catalog	constraint_schema	constraint_name	table_catalog	table_schema	table_name	column_name
def	performance_schema	ACCOUNT	def	performance_schema	accounts	HOST
def	performance_schema	ACCOUNT	def	performance_schema	accounts	USER
def	performance_schema	ACCOUNT	def	performance_schema	events_errors_summary_by_account_by_error	ERROR_NUMBER
def	performance_schema	ACCOUNT	def	performance_schema	events_errors_summary_by_account_by_error	HOST
def	performance_schema	ACCOUNT	def	performance_schema	events_errors_summary_by_account_by_error	USER
def	performance_schema	ACCOUNT	def	performance_schema	events_stages_summary_by_account_by_event_name	EVENT_NAME
def	performance_schema	ACCOUNT	def	performance_schema	events_stages_summary_by_account_by_event_name	HOST
def	performance_schema	ACCOUNT	def	performance_schema	events_stages_summary_by_account_by_event_name	USER
def	performance_schema	ACCOUNT	def	performance_schema	events_statements_summary_by_account_by_event_name	EVENT_NAME
def	performance_schema	ACCOUNT	def	performance_schema	events_statements_summary_by_account_by_event_name	HOST
def	performance_schema	ACCOUNT	def	performance_schema	events_statements_summary_by_account_by_event_name	USER
def	performance_schema	ACCOUNT	def	performance_schema	events_transactions_summary_by_account_by_event_name	EVENT_NAME
def	performance_schema	ACCOUNT	def	performance_schema	events_transactions_summary_by_account_by_event_name	HOST
def	performance_schema	ACCOUNT	def	performance_schema	events_transactions_summary_by_account_by_event_name	USER
def	performance_schema	ACCOUNT	def	performance_schema	events_waits_summary_by_account_by_event_name	EVENT_NAME
def	performance_schema	ACCOUNT	def	performance_schema	events_waits_summary_by_account_by_event_name	HOST
def	performance_schema	ACCOUNT	def	performance_schema	events_waits_summary_by_account_by_event_name	USER
def	performance_schema	ACCOUNT	def	performance_schema	memory_summary_by_account_by_event_name	EVENT_NAME
def	performance_schema	ACCOUNT	def	performance_schema	memory_summary_by_account_by_event_name	HOST
def	performance_schema	ACCOUNT	def	performance_schema	memory_summary_by_account_by_event_name	USER
def	performance_schema	ACCOUNT	def	performance_schema	status_by_account	HOST
def	performance_schema	ACCOUNT	def	performance_schema	status_by_account	USER
def	performance_schema	ACCOUNT	def	performance_schema	status_by_account	VARIABLE_NAME
def	performance_schema	ERROR_NUMBER	def	performance_schema	events_errors_summary_global_by_error	ERROR_NUMBER
def	performance_schema	HOST	def	performance_schema	events_errors_summary_by_host_by_error	ERROR_NUMBER
def	performance_schema	HOST	def	performance_schema	events_errors_summary_by_host_by_error	HOST
def	performance_schema	HOST	def	performance_schema	events_stages_summary_by_host_by_event_name	EVENT_NAME
def	performance_schema	HOST	def	performance_schema	events_stages_summary_by_host_by_event_name	HOST
def	performance_schema	HOST	def	performance_schema	events_statements_summary_by_host_by_event_name	EVENT_NAME
def	performance_schema	HOST	def	performance_schema	events_statements_summary_by_host_by_event_name	HOST
def	performance_schema	HOST	def	performance_schema	events_transactions_summary_by_host_by_event_name	EVENT_NAME
def	performance_schema	HOST	def	performance_schema	events_transactions_summary_by_host_by_event_name	HOST
def	performance_schema	HOST	def	performance_schema	events_waits_summary_by_host_by_event_name	EVENT_NAME
def	performance_schema	HOST	def	performance_schema	events_waits_summary_by_host_by_event_name	HOST
def	performance_schema	HOST	def	performance_schema	hosts	HOST
def	performance_schema	HOST	def	performance_schema	memory_summary_by_host_by_event_name	EVENT_NAME
def	performance_schema	HOST	def	performance_schema	memory_summary_by_host_by_event_name	HOST
def	performance_schema	HOST	def	performance_schema	status_by_host	HOST
def	performance_schema	HOST	def	performance_schema	status_by_host	VARIABLE_NAME
def	mysql	name	def	mysql	help_category	name
def	mysql	name	def	mysql	help_keyword	name
def	mysql	name	def	mysql	help_topic	name
def	performance_schema	OBJECT	def	performance_schema	objects_summary_global_by_type	OBJECT_NAME
def	performance_schema	OBJECT	def	performance_schema	objects_summary_global_by_type	OBJECT_SCHEMA
def	performance_schema	OBJECT	def	performance_schema	objects_summary_global_by_type	OBJECT_TYPE
def	performance_schema	OBJECT	def	performance_schema	setup_objects	OBJECT_NAME
def	performance_schema	OBJECT	def	performance_schema	setup_objects	OBJECT_SCHEMA
def	performance_schema	OBJECT	def	performance_schema	setup_objects	OBJECT_TYPE
def	performance_schema	OBJECT	def	performance_schema	table_io_waits_summary_by_index_usage	INDEX_NAME
def	performance_schema	OBJECT	def	performance_schema	table_io_waits_summary_by_index_usage	OBJECT_NAME
def	performance_schema	OBJECT	def	performance_schema	table_io_waits_summary_by_index_usage	OBJECT_SCHEMA
def	performance_schema	OBJECT	def	performance_schema	table_io_waits_summary_by_index_usage	OBJECT_TYPE
def	performance_schema	OBJECT	def	performance_schema	table_io_waits_summary_by_table	OBJECT_NAME
def	performance_schema	OBJECT	def	performance_schema	table_io_waits_summary_by_table	OBJECT_SCHEMA
def	performance_schema	OBJECT	def	performance_schema	table_io_waits_summary_by_table	OBJECT_TYPE
def	performance_schema	OBJECT	def	performance_schema	table_lock_waits_summary_by_table	OBJECT_NAME
def	performance_schema	OBJECT	def	performance_schema	table_lock_waits_summary_by_table	OBJECT_SCHEMA
def	performance_schema	OBJECT	def	performance_schema	table_lock_waits_summary_by_table	OBJECT_TYPE
def	performance_schema	OWNER_THREAD_ID	def	performance_schema	prepared_statements_instances	OWNER_EVENT_ID
def	performance_schema	OWNER_THREAD_ID	def	performance_schema	prepared_statements_instances	OWNER_THREAD_ID
def	mysql	PRIMARY	def	mysql	columns_priv	Column_name
def	mysql	PRIMARY	def	mysql	columns_priv	Db
def	mysql	PRIMARY	def	mysql	columns_priv	Host
def	mysql	PRIMARY	def	mysql	columns_priv	Table_name
def	mysql	PRIMARY	def	mysql	columns_priv	User
def	mysql	PRIMARY	def	mysql	component	component_id
def	mysql	PRIMARY	def	mysql	concurrency_control	Id
def	performance_schema	PRIMARY	def	performance_schema	cond_instances	OBJECT_INSTANCE_BEGIN
def	mysql	PRIMARY	def	mysql	consensus_info	number_of_lines
def	performance_schema	PRIMARY	def	performance_schema	data_locks	ENGINE
def	performance_schema	PRIMARY	def	performance_schema	data_locks	ENGINE_LOCK_ID
def	mysql	PRIMARY	def	mysql	db	Db
def	mysql	PRIMARY	def	mysql	db	Host
def	mysql	PRIMARY	def	mysql	db	User
def	mysql	PRIMARY	def	mysql	default_roles	DEFAULT_ROLE_HOST
def	mysql	PRIMARY	def	mysql	default_roles	DEFAULT_ROLE_USER
def	mysql	PRIMARY	def	mysql	default_roles	HOST
def	mysql	PRIMARY	def	mysql	default_roles	USER
def	mysql	PRIMARY	def	mysql	engine_cost	cost_name
def	mysql	PRIMARY	def	mysql	engine_cost	device_type
def	mysql	PRIMARY	def	mysql	engine_cost	engine_name
def	performance_schema	PRIMARY	def	performance_schema	events_stages_current	EVENT_ID
def	performance_schema	PRIMARY	def	performance_schema	events_stages_current	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_stages_history	EVENT_ID
def	performance_schema	PRIMARY	def	performance_schema	events_stages_history	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_stages_summary_by_thread_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	events_stages_summary_by_thread_by_event_name	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_stages_summary_global_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	events_statements_current	EVENT_ID
def	performance_schema	PRIMARY	def	performance_schema	events_statements_current	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_statements_histogram_global	BUCKET_NUMBER
def	performance_schema	PRIMARY	def	performance_schema	events_statements_history	EVENT_ID
def	performance_schema	PRIMARY	def	performance_schema	events_statements_history	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_statements_summary_by_program	OBJECT_NAME
def	performance_schema	PRIMARY	def	performance_schema	events_statements_summary_by_program	OBJECT_SCHEMA
def	performance_schema	PRIMARY	def	performance_schema	events_statements_summary_by_program	OBJECT_TYPE
def	performance_schema	PRIMARY	def	performance_schema	events_statements_summary_by_thread_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	events_statements_summary_by_thread_by_event_name	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_statements_summary_global_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	events_transactions_current	EVENT_ID
def	performance_schema	PRIMARY	def	performance_schema	events_transactions_current	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_transactions_history	EVENT_ID
def	performance_schema	PRIMARY	def	performance_schema	events_transactions_history	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_transactions_summary_by_thread_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	events_transactions_summary_by_thread_by_event_name	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_transactions_summary_global_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	events_waits_current	EVENT_ID
def	performance_schema	PRIMARY	def	performance_schema	events_waits_current	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_waits_history	EVENT_ID
def	performance_schema	PRIMARY	def	performance_schema	events_waits_history	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_waits_summary_by_instance	OBJECT_INSTANCE_BEGIN
def	performance_schema	PRIMARY	def	performance_schema	events_waits_summary_by_thread_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	events_waits_summary_by_thread_by_event_name	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	events_waits_summary_global_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	file_instances	FILE_NAME
def	performance_schema	PRIMARY	def	performance_schema	file_summary_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	file_summary_by_instance	OBJECT_INSTANCE_BEGIN
def	mysql	PRIMARY	def	mysql	func	name
def	mysql	PRIMARY	def	mysql	global_grants	HOST
def	mysql	PRIMARY	def	mysql	global_grants	PRIV
def	mysql	PRIMARY	def	mysql	global_grants	USER
def	performance_schema	PRIMARY	def	performance_schema	global_status	VARIABLE_NAME
def	performance_schema	PRIMARY	def	performance_schema	global_variables	VARIABLE_NAME
def	mysql	PRIMARY	def	mysql	gtid_executed	interval_start
def	mysql	PRIMARY	def	mysql	gtid_executed	source_uuid
def	mysql	PRIMARY	def	mysql	help_category	help_category_id
def	mysql	PRIMARY	def	mysql	help_keyword	help_keyword_id
def	mysql	PRIMARY	def	mysql	help_relation	help_keyword_id
def	mysql	PRIMARY	def	mysql	help_relation	help_topic_id
def	mysql	PRIMARY	def	mysql	help_topic	help_topic_id
def	performance_schema	PRIMARY	def	performance_schema	host_cache	IP
def	mysql	PRIMARY	def	mysql	innodb_flashback_snapshot	scn
def	mysql	PRIMARY	def	mysql	innodb_index_stats	database_name
def	mysql	PRIMARY	def	mysql	innodb_index_stats	index_name
def	mysql	PRIMARY	def	mysql	innodb_index_stats	stat_name
def	mysql	PRIMARY	def	mysql	innodb_index_stats	table_name
def	mysql	PRIMARY	def	mysql	innodb_table_stats	database_name
def	mysql	PRIMARY	def	mysql	innodb_table_stats	table_name
def	performance_schema	PRIMARY	def	performance_schema	memory_summary_by_thread_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	memory_summary_by_thread_by_event_name	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	memory_summary_global_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	metadata_locks	OBJECT_INSTANCE_BEGIN
def	performance_schema	PRIMARY	def	performance_schema	mutex_instances	OBJECT_INSTANCE_BEGIN
def	mysql	PRIMARY	def	mysql	outline	Id
def	mysql	PRIMARY	def	mysql	password_history	Host
def	mysql	PRIMARY	def	mysql	password_history	Password_timestamp
def	mysql	PRIMARY	def	mysql	password_history	User
def	performance_schema	PRIMARY	def	performance_schema	persisted_variables	VARIABLE_NAME
def	mysql	PRIMARY	def	mysql	plugin	name
def	performance_schema	PRIMARY	def	performance_schema	prepared_statements_instances	OBJECT_INSTANCE_BEGIN
def	mysql	PRIMARY	def	mysql	procs_priv	Db
def	mysql	PRIMARY	def	mysql	procs_priv	Host
def	mysql	PRIMARY	def	mysql	procs_priv	Routine_name
def	mysql	PRIMARY	def	mysql	procs_priv	Routine_type
def	mysql	PRIMARY	def	mysql	procs_priv	User
def	mysql	PRIMARY	def	mysql	proxies_priv	Host
def	mysql	PRIMARY	def	mysql	proxies_priv	Proxied_host
def	mysql	PRIMARY	def	mysql	proxies_priv	Proxied_user
def	mysql	PRIMARY	def	mysql	proxies_priv	User
def	performance_schema	PRIMARY	def	performance_schema	replication_applier_configuration	CHANNEL_NAME
def	performance_schema	PRIMARY	def	performance_schema	replication_applier_status	CHANNEL_NAME
def	performance_schema	PRIMARY	def	performance_schema	replication_applier_status_by_coordinator	CHANNEL_NAME
def	performance_schema	PRIMARY	def	performance_schema	replication_applier_status_by_worker	CHANNEL_NAME
def	performance_schema	PRIMARY	def	performance_schema	replication_applier_status_by_worker	WORKER_ID
def	performance_schema	PRIMARY	def	performance_schema	replication_connection_configuration	CHANNEL_NAME
def	performance_schema	PRIMARY	def	performance_schema	replication_connection_status	CHANNEL_NAME
def	mysql	PRIMARY	def	mysql	role_edges	FROM_HOST
def	mysql	PRIMARY	def	mysql	role_edges	FROM_USER
def	mysql	PRIMARY	def	mysql	role_edges	TO_HOST
def	mysql	PRIMARY	def	mysql	role_edges	TO_USER
def	performance_schema	PRIMARY	def	performance_schema	rwlock_instances	OBJECT_INSTANCE_BEGIN
def	mysql	PRIMARY	def	mysql	server_cost	cost_name
def	mysql	PRIMARY	def	mysql	servers	Server_name
def	performance_schema	PRIMARY	def	performance_schema	session_account_connect_attrs	ATTR_NAME
def	performance_schema	PRIMARY	def	performance_schema	session_account_connect_attrs	PROCESSLIST_ID
def	performance_schema	PRIMARY	def	performance_schema	session_connect_attrs	ATTR_NAME
def	performance_schema	PRIMARY	def	performance_schema	session_connect_attrs	PROCESSLIST_ID
def	performance_schema	PRIMARY	def	performance_schema	session_status	VARIABLE_NAME
def	performance_schema	PRIMARY	def	performance_schema	session_variables	VARIABLE_NAME
def	performance_schema	PRIMARY	def	performance_schema	setup_actors	HOST
def	performance_schema	PRIMARY	def	performance_schema	setup_actors	ROLE
def	performance_schema	PRIMARY	def	performance_schema	setup_actors	USER
def	performance_schema	PRIMARY	def	performance_schema	setup_consumers	NAME
def	performance_schema	PRIMARY	def	performance_schema	setup_instruments	NAME
def	performance_schema	PRIMARY	def	performance_schema	setup_threads	NAME
def	mysql	PRIMARY	def	mysql	slave_master_info	Channel_name
def	mysql	PRIMARY	def	mysql	slave_relay_log_info	Channel_name
def	mysql	PRIMARY	def	mysql	slave_worker_info	Channel_name
def	mysql	PRIMARY	def	mysql	slave_worker_info	Id
def	performance_schema	PRIMARY	def	performance_schema	socket_instances	OBJECT_INSTANCE_BEGIN
def	performance_schema	PRIMARY	def	performance_schema	socket_summary_by_event_name	EVENT_NAME
def	performance_schema	PRIMARY	def	performance_schema	socket_summary_by_instance	OBJECT_INSTANCE_BEGIN
def	performance_schema	PRIMARY	def	performance_schema	status_by_thread	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	status_by_thread	VARIABLE_NAME
def	sys	PRIMARY	def	sys	sys_config	variable
def	performance_schema	PRIMARY	def	performance_schema	table_handles	OBJECT_INSTANCE_BEGIN
def	mysql	PRIMARY	def	mysql	tables_priv	Db
def	mysql	PRIMARY	def	mysql	tables_priv	Host
def	mysql	PRIMARY	def	mysql	tables_priv	Table_name
def	mysql	PRIMARY	def	mysql	tables_priv	User
def	performance_schema	PRIMARY	def	performance_schema	threads	THREAD_ID
def	mysql	PRIMARY	def	mysql	time_zone	Time_zone_id
def	mysql	PRIMARY	def	mysql	time_zone_leap_second	Transition_time
def	mysql	PRIMARY	def	mysql	time_zone_name	Name
def	mysql	PRIMARY	def	mysql	time_zone_transition	Time_zone_id
def	mysql	PRIMARY	def	mysql	time_zone_transition	Transition_time
def	mysql	PRIMARY	def	mysql	time_zone_transition_type	Time_zone_id
def	mysql	PRIMARY	def	mysql	time_zone_transition_type	Transition_type_id
def	mysql	PRIMARY	def	mysql	user	Host
def	mysql	PRIMARY	def	mysql	user	User
def	performance_schema	PRIMARY	def	performance_schema	user_defined_functions	UDF_NAME
def	performance_schema	PRIMARY	def	performance_schema	user_variables_by_thread	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	user_variables_by_thread	VARIABLE_NAME
def	performance_schema	PRIMARY	def	performance_schema	variables_by_thread	THREAD_ID
def	performance_schema	PRIMARY	def	performance_schema	variables_by_thread	VARIABLE_NAME
def	performance_schema	SCHEMA_NAME	def	performance_schema	events_statements_histogram_by_digest	BUCKET_NUMBER
def	performance_schema	SCHEMA_NAME	def	performance_schema	events_statements_histogram_by_digest	DIGEST
def	performance_schema	SCHEMA_NAME	def	performance_schema	events_statements_histogram_by_digest	SCHEMA_NAME
def	performance_schema	SCHEMA_NAME	def	performance_schema	events_statements_summary_by_digest	DIGEST
def	performance_schema	SCHEMA_NAME	def	performance_schema	events_statements_summary_by_digest	SCHEMA_NAME
def	performance_schema	SCHEMA_NAME	def	performance_schema	events_statements_summary_by_digest_supplement	DIGEST
def	performance_schema	SCHEMA_NAME	def	performance_schema	events_statements_summary_by_digest_supplement	SCHEMA_NAME
def	performance_schema	THREAD_ID	def	performance_schema	events_errors_summary_by_thread_by_error	ERROR_NUMBER
def	performance_schema	THREAD_ID	def	performance_schema	events_errors_summary_by_thread_by_error	THREAD_ID
def	performance_schema	USER	def	performance_schema	events_errors_summary_by_user_by_error	ERROR_NUMBER
def	performance_schema	USER	def	performance_schema	events_errors_summary_by_user_by_error	USER
def	performance_schema	USER	def	performance_schema	events_stages_summary_by_user_by_event_name	EVENT_NAME
def	performance_schema	USER	def	performance_schema	events_stages_summary_by_user_by_event_name	USER
def	performance_schema	USER	def	performance_schema	events_statements_summary_by_user_by_event_name	EVENT_NAME
def	performance_schema	USER	def	performance_schema	events_statements_summary_by_user_by_event_name	USER
def	performance_schema	USER	def	performance_schema	events_transactions_summary_by_user_by_event_name	EVENT_NAME
def	performance_schema	USER	def	performance_schema	events_transactions_summary_by_user_by_event_name	USER
def	performance_schema	USER	def	performance_schema	events_waits_summary_by_user_by_event_name	EVENT_NAME
def	performance_schema	USER	def	performance_schema	events_waits_summary_by_user_by_event_name	USER
def	performance_schema	USER	def	performance_schema	memory_summary_by_user_by_event_name	EVENT_NAME
def	performance_schema	USER	def	performance_schema	memory_summary_by_user_by_event_name	USER
def	performance_schema	USER	def	performance_schema	status_by_user	USER
def	performance_schema	USER	def	performance_schema	status_by_user	VARIABLE_NAME
def	performance_schema	USER	def	performance_schema	users	USER
########################################################################################
# Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.KEY_COLUMN_USAGE 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';
USE db_datadict;
CREATE TABLE t1_1
(f1 INT NOT NULL, PRIMARY KEY(f1),
f2 INT,          INDEX f2_ind(f2))
ENGINE = <engine_type>;
GRANT SELECT ON t1_1 to 'testuser1'@'localhost';
CREATE TABLE t1_2
(f1 INT NOT NULL, PRIMARY KEY(f1),
f2 INT,          INDEX f2_ind(f2))
ENGINE = <engine_type>;
GRANT SELECT ON t1_2 to 'testuser2'@'localhost';
SELECT * FROM information_schema.key_column_usage
WHERE table_name LIKE 't1_%'
ORDER BY constraint_catalog, constraint_schema, constraint_name,
table_catalog, table_schema, table_name, ordinal_position;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
def	db_datadict	PRIMARY	def	db_datadict	t1_1	f1	1	NULL	NULL	NULL	NULL
def	db_datadict	PRIMARY	def	db_datadict	t1_2	f1	1	NULL	NULL	NULL	NULL
# Establish connection testuser1 (user=testuser1)
SELECT * FROM information_schema.key_column_usage
WHERE table_name LIKE 't1_%'
ORDER BY constraint_catalog, constraint_schema, constraint_name,
table_catalog, table_schema, table_name, ordinal_position;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
def	db_datadict	PRIMARY	def	db_datadict	t1_1	f1	1	NULL	NULL	NULL	NULL
# Establish connection testuser2 (user=testuser2)
SELECT * FROM information_schema.key_column_usage
WHERE table_name LIKE 't1_%'
ORDER BY constraint_catalog, constraint_schema, constraint_name,
table_catalog, table_schema, table_name, ordinal_position;
CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
def	db_datadict	PRIMARY	def	db_datadict	t1_2	f1	1	NULL	NULL	NULL	NULL
# Switch to connection default and close connections testuser1, testuser2
DROP USER 'testuser1'@'localhost';
DROP USER 'testuser2'@'localhost';
DROP TABLE t1_1;
DROP TABLE t1_2;
DROP DATABASE IF EXISTS db_datadict;
########################################################################################
# Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.KEY_COLUMN_USAGE modifications
########################################################################################
DROP DATABASE IF EXISTS db_datadict;
DROP TABLE IF EXISTS test.t1_my_table;
CREATE DATABASE db_datadict;
SELECT table_name FROM information_schema.key_column_usage
WHERE table_name LIKE 't1_my_table%';
table_name
CREATE TABLE test.t1_my_table
(f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
ENGINE = <engine_type>;
SELECT * FROM information_schema.key_column_usage
WHERE table_name = 't1_my_table' ORDER BY column_name;
CONSTRAINT_CATALOG	def
CONSTRAINT_SCHEMA	test
CONSTRAINT_NAME	PRIMARY
TABLE_CATALOG	def
TABLE_SCHEMA	test
TABLE_NAME	t1_my_table
COLUMN_NAME	f1
ORDINAL_POSITION	#
POSITION_IN_UNIQUE_CONSTRAINT	NULL
REFERENCED_TABLE_SCHEMA	NULL
REFERENCED_TABLE_NAME	NULL
REFERENCED_COLUMN_NAME	NULL
CONSTRAINT_CATALOG	def
CONSTRAINT_SCHEMA	test
CONSTRAINT_NAME	PRIMARY
TABLE_CATALOG	def
TABLE_SCHEMA	test
TABLE_NAME	t1_my_table
COLUMN_NAME	f2
ORDINAL_POSITION	#
POSITION_IN_UNIQUE_CONSTRAINT	NULL
REFERENCED_TABLE_SCHEMA	NULL
REFERENCED_TABLE_NAME	NULL
REFERENCED_COLUMN_NAME	NULL
SELECT DISTINCT table_name FROM information_schema.key_column_usage
WHERE table_name LIKE 't1_my_table%';
table_name
t1_my_table
RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
SELECT DISTINCT table_name FROM information_schema.key_column_usage
WHERE table_name LIKE 't1_my_table%';
table_name
t1_my_tablex
SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
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 DISTINCT table_schema,table_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_schema	table_name
db_datadict	t1_my_tablex
SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
table_name	column_name
t1_my_tablex	f1
t1_my_tablex	f2
ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
table_name	column_name
t1_my_tablex	f2
t1_my_tablex	first_col
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
DROP INDEX f2 ON db_datadict.t1_my_tablex;
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	f2	db_datadict	t1_my_tablex	f2	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
DROP INDEX f2 ON db_datadict.t1_my_tablex;
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	my_idx	db_datadict	t1_my_tablex	f2	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
DROP INDEX my_idx ON db_datadict.t1_my_tablex;
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,first_col);
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	my_idx	db_datadict	t1_my_tablex	f4	1
db_datadict	my_idx	db_datadict	t1_my_tablex	first_col	2
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	my_idx	db_datadict	t1_my_tablex	f4	1
db_datadict	my_idx	db_datadict	t1_my_tablex	first_col	2
db_datadict	PRIMARY	db_datadict	t1_my_tablex	first_col	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	2
ALTER TABLE db_datadict.t1_my_tablex
DROP COLUMN first_col;
SELECT constraint_schema, constraint_name, table_schema,
table_name, column_name, ordinal_position
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY constraint_schema, constraint_name, table_schema,
table_name, ordinal_position;
constraint_schema	constraint_name	table_schema	table_name	column_name	ordinal_position
db_datadict	my_idx	db_datadict	t1_my_tablex	f4	1
db_datadict	PRIMARY	db_datadict	t1_my_tablex	f2	1
SELECT table_name, column_name
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex'
ORDER BY table_name, column_name;
table_name	column_name
t1_my_tablex	f2
t1_my_tablex	f4
DROP TABLE db_datadict.t1_my_tablex;
SELECT table_name, column_name
FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name	column_name
SELECT table_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name
CREATE TABLE db_datadict.t1_my_tablex
ENGINE = <engine_type> AS
SELECT 1 AS f1;
SELECT table_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name
ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
SELECT table_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name
t1_my_tablex
SELECT table_name FROM information_schema.key_column_usage
WHERE table_name = 't1_my_tablex';
table_name
t1_my_tablex
DROP DATABASE db_datadict;
SELECT table_name FROM information_schema.key_column_usage
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 db_datadict.t1;
CREATE DATABASE db_datadict;
CREATE TABLE db_datadict.t1 (f1 BIGINT)
ENGINE = <engine_type>;
INSERT INTO information_schema.key_column_usage
(constraint_schema, constraint_name, table_name)
VALUES (          'mysql',       'primary',       'db');
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
INSERT INTO information_schema.key_column_usage
SELECT * FROM information_schema.key_column_usage;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
UPDATE information_schema.key_column_usage
SET  table_name = 'db1' WHERE constraint_name = 'primary';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DELETE FROM information_schema.key_column_usage WHERE table_name = 't1';
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
TRUNCATE information_schema.key_column_usage;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
CREATE INDEX i3 ON information_schema.key_column_usage(table_name);
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.key_column_usage ADD f1 INT;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE information_schema.key_column_usage;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.key_column_usage
RENAME db_datadict.key_column_usage;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
ALTER TABLE information_schema.key_column_usage
RENAME information_schema.xkey_column_usage;
ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
DROP TABLE db_datadict.t1;
DROP DATABASE db_datadict;