Debian Codegraph @

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

raw
1 SHOW TABLES FROM information_schema LIKE 'KEY_COLUMN_USAGE';
2 Tables_in_information_schema (KEY_COLUMN_USAGE)
3 KEY_COLUMN_USAGE
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.KEY_COLUMN_USAGE;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.KEY_COLUMN_USAGE;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.KEY_COLUMN_USAGE;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.KEY_COLUMN_USAGE;
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.7.1: INFORMATION_SCHEMA.KEY_COLUMN_USAGE layout
28 #########################################################################
29 DESCRIBE information_schema.KEY_COLUMN_USAGE;
30 Field Type Null Key Default Extra
31 CONSTRAINT_CATALOG varchar(64) NO NULL
32 CONSTRAINT_SCHEMA varchar(64) NO NULL
33 CONSTRAINT_NAME varchar(64) YES NULL
34 TABLE_CATALOG varchar(64) NO NULL
35 TABLE_SCHEMA varchar(64) NO NULL
36 TABLE_NAME varchar(64) NO NULL
37 COLUMN_NAME varchar(64) YES NULL
38 ORDINAL_POSITION int unsigned NO 0
39 POSITION_IN_UNIQUE_CONSTRAINT int unsigned YES NULL
40 REFERENCED_TABLE_SCHEMA varchar(64) YES NULL
41 REFERENCED_TABLE_NAME varchar(64) YES NULL
42 REFERENCED_COLUMN_NAME varchar(64) YES NULL
43 SHOW CREATE TABLE information_schema.KEY_COLUMN_USAGE;
44 View Create View character_set_client collation_connection
45 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`,`constraints`.`CONSTRAINT_NAME` AS `CONSTRAINT_NAME`,`cat`.`name` AS `TABLE_CATALOG`,`sch`.`name` AS `TABLE_SCHEMA`,`tbl`.`name` AS `TABLE_NAME`,(`col`.`name` collate utf8mb3_tolower_ci) AS `COLUMN_NAME`,`constraints`.`ORDINAL_POSITION` AS `ORDINAL_POSITION`,`constraints`.`POSITION_IN_UNIQUE_CONSTRAINT` AS `POSITION_IN_UNIQUE_CONSTRAINT`,`constraints`.`REFERENCED_TABLE_SCHEMA` AS `REFERENCED_TABLE_SCHEMA`,`constraints`.`REFERENCED_TABLE_NAME` AS `REFERENCED_TABLE_NAME`,`constraints`.`REFERENCED_COLUMN_NAME` AS `REFERENCED_COLUMN_NAME` from (((`mysql`.`tables` `tbl` join `mysql`.`schemata` `sch` on((`tbl`.`schema_id` = `sch`.`id`))) join `mysql`.`catalogs` `cat` on((`cat`.`id` = `sch`.`catalog_id`))) join (lateral (select `idx`.`name` AS `CONSTRAINT_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`,`icu`.`column_id` AS `column_id`,((0 <> `idx`.`hidden`) or (0 <> `icu`.`hidden`)) AS `HIDDEN` from (`mysql`.`indexes` `idx` join `mysql`.`index_column_usage` `icu` on((`icu`.`index_id` = `idx`.`id`))) where ((`idx`.`table_id` = `tbl`.`id`) and (`idx`.`type` in ('PRIMARY','UNIQUE'))) union all select (`fk`.`name` collate utf8mb3_tolower_ci) AS `CONSTRAINT_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`,`fkcu`.`column_id` AS `column_id`,false AS `HIDDEN` from (`mysql`.`foreign_keys` `fk` join `mysql`.`foreign_key_column_usage` `fkcu` on((`fkcu`.`foreign_key_id` = `fk`.`id`))) where (`fk`.`table_id` = `tbl`.`id`)) `constraints` join `mysql`.`columns` `col` on((`constraints`.`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` not in ('Visible','User')) or (0 <> `constraints`.`HIDDEN`)),`col`.`options`))) utf8mb3 utf8mb3_general_ci
46 SHOW COLUMNS FROM information_schema.KEY_COLUMN_USAGE;
47 Field Type Null Key Default Extra
48 CONSTRAINT_CATALOG varchar(64) NO NULL
49 CONSTRAINT_SCHEMA varchar(64) NO NULL
50 CONSTRAINT_NAME varchar(64) YES NULL
51 TABLE_CATALOG varchar(64) NO NULL
52 TABLE_SCHEMA varchar(64) NO NULL
53 TABLE_NAME varchar(64) NO NULL
54 COLUMN_NAME varchar(64) YES NULL
55 ORDINAL_POSITION int unsigned NO 0
56 POSITION_IN_UNIQUE_CONSTRAINT int unsigned YES NULL
57 REFERENCED_TABLE_SCHEMA varchar(64) YES NULL
58 REFERENCED_TABLE_NAME varchar(64) YES NULL
59 REFERENCED_COLUMN_NAME varchar(64) YES NULL
60 SELECT constraint_catalog, constraint_schema, constraint_name, table_catalog,
61 table_schema, table_name, column_name
62 FROM information_schema.key_column_usage
63 WHERE (constraint_catalog IS NOT NULL OR table_catalog IS NOT NULL) AND
64 table_name != 'ndb_binlog_index'
65 ORDER BY constraint_schema, constraint_name, table_schema, table_name, column_name;
66 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME
67 def mtr PRIMARY def mtr asserted_test_suppressions message
68 def mtr PRIMARY def mtr test_ignored_global_suppressions pattern
69 def mysql name def mysql help_category name
70 def mysql name def mysql help_keyword name
71 def mysql name def mysql help_topic name
72 def mysql PRIMARY def mysql columns_priv Column_name
73 def mysql PRIMARY def mysql columns_priv Db
74 def mysql PRIMARY def mysql columns_priv Host
75 def mysql PRIMARY def mysql columns_priv Table_name
76 def mysql PRIMARY def mysql columns_priv User
77 def mysql PRIMARY def mysql component component_id
78 def mysql PRIMARY def mysql db Db
79 def mysql PRIMARY def mysql db Host
80 def mysql PRIMARY def mysql db User
81 def mysql PRIMARY def mysql default_roles DEFAULT_ROLE_HOST
82 def mysql PRIMARY def mysql default_roles DEFAULT_ROLE_USER
83 def mysql PRIMARY def mysql default_roles HOST
84 def mysql PRIMARY def mysql default_roles USER
85 def mysql PRIMARY def mysql engine_cost cost_name
86 def mysql PRIMARY def mysql engine_cost device_type
87 def mysql PRIMARY def mysql engine_cost engine_name
88 def mysql PRIMARY def mysql func name
89 def mysql PRIMARY def mysql global_grants HOST
90 def mysql PRIMARY def mysql global_grants PRIV
91 def mysql PRIMARY def mysql global_grants USER
92 def mysql PRIMARY def mysql gtid_executed interval_start
93 def mysql PRIMARY def mysql gtid_executed source_uuid
94 def mysql PRIMARY def mysql help_category help_category_id
95 def mysql PRIMARY def mysql help_keyword help_keyword_id
96 def mysql PRIMARY def mysql help_relation help_keyword_id
97 def mysql PRIMARY def mysql help_relation help_topic_id
98 def mysql PRIMARY def mysql help_topic help_topic_id
99 def mysql PRIMARY def mysql innodb_index_stats database_name
100 def mysql PRIMARY def mysql innodb_index_stats index_name
101 def mysql PRIMARY def mysql innodb_index_stats stat_name
102 def mysql PRIMARY def mysql innodb_index_stats table_name
103 def mysql PRIMARY def mysql innodb_table_stats database_name
104 def mysql PRIMARY def mysql innodb_table_stats table_name
105 def mysql PRIMARY def mysql password_history Host
106 def mysql PRIMARY def mysql password_history Password_timestamp
107 def mysql PRIMARY def mysql password_history User
108 def mysql PRIMARY def mysql plugin name
109 def mysql PRIMARY def mysql procs_priv Db
110 def mysql PRIMARY def mysql procs_priv Host
111 def mysql PRIMARY def mysql procs_priv Routine_name
112 def mysql PRIMARY def mysql procs_priv Routine_type
113 def mysql PRIMARY def mysql procs_priv User
114 def mysql PRIMARY def mysql proxies_priv Host
115 def mysql PRIMARY def mysql proxies_priv Proxied_host
116 def mysql PRIMARY def mysql proxies_priv Proxied_user
117 def mysql PRIMARY def mysql proxies_priv User
118 def mysql PRIMARY def mysql replication_asynchronous_connection_failover Channel_name
119 def mysql PRIMARY def mysql replication_asynchronous_connection_failover Host
120 def mysql PRIMARY def mysql replication_asynchronous_connection_failover Managed_name
121 def mysql PRIMARY def mysql replication_asynchronous_connection_failover Network_namespace
122 def mysql PRIMARY def mysql replication_asynchronous_connection_failover Port
123 def mysql PRIMARY def mysql replication_asynchronous_connection_failover_managed Channel_name
124 def mysql PRIMARY def mysql replication_asynchronous_connection_failover_managed Managed_name
125 def mysql PRIMARY def mysql replication_group_configuration_version name
126 def mysql PRIMARY def mysql replication_group_member_actions event
127 def mysql PRIMARY def mysql replication_group_member_actions name
128 def mysql PRIMARY def mysql role_edges FROM_HOST
129 def mysql PRIMARY def mysql role_edges FROM_USER
130 def mysql PRIMARY def mysql role_edges TO_HOST
131 def mysql PRIMARY def mysql role_edges TO_USER
132 def mysql PRIMARY def mysql server_cost cost_name
133 def mysql PRIMARY def mysql servers Server_name
134 def mysql PRIMARY def mysql slave_master_info Channel_name
135 def mysql PRIMARY def mysql slave_relay_log_info Channel_name
136 def mysql PRIMARY def mysql slave_worker_info Channel_name
137 def mysql PRIMARY def mysql slave_worker_info Id
138 def mysql PRIMARY def mysql tables_priv Db
139 def mysql PRIMARY def mysql tables_priv Host
140 def mysql PRIMARY def mysql tables_priv Table_name
141 def mysql PRIMARY def mysql tables_priv User
142 def mysql PRIMARY def mysql time_zone Time_zone_id
143 def mysql PRIMARY def mysql time_zone_leap_second Transition_time
144 def mysql PRIMARY def mysql time_zone_name Name
145 def mysql PRIMARY def mysql time_zone_transition Time_zone_id
146 def mysql PRIMARY def mysql time_zone_transition Transition_time
147 def mysql PRIMARY def mysql time_zone_transition_type Time_zone_id
148 def mysql PRIMARY def mysql time_zone_transition_type Transition_type_id
149 def mysql PRIMARY def mysql user Host
150 def mysql PRIMARY def mysql user User
151 def performance_schema ACCOUNT def performance_schema accounts HOST
152 def performance_schema ACCOUNT def performance_schema accounts USER
153 def performance_schema ACCOUNT def performance_schema events_errors_summary_by_account_by_error ERROR_NUMBER
154 def performance_schema ACCOUNT def performance_schema events_errors_summary_by_account_by_error HOST
155 def performance_schema ACCOUNT def performance_schema events_errors_summary_by_account_by_error USER
156 def performance_schema ACCOUNT def performance_schema events_stages_summary_by_account_by_event_name EVENT_NAME
157 def performance_schema ACCOUNT def performance_schema events_stages_summary_by_account_by_event_name HOST
158 def performance_schema ACCOUNT def performance_schema events_stages_summary_by_account_by_event_name USER
159 def performance_schema ACCOUNT def performance_schema events_statements_summary_by_account_by_event_name EVENT_NAME
160 def performance_schema ACCOUNT def performance_schema events_statements_summary_by_account_by_event_name HOST
161 def performance_schema ACCOUNT def performance_schema events_statements_summary_by_account_by_event_name USER
162 def performance_schema ACCOUNT def performance_schema events_transactions_summary_by_account_by_event_name EVENT_NAME
163 def performance_schema ACCOUNT def performance_schema events_transactions_summary_by_account_by_event_name HOST
164 def performance_schema ACCOUNT def performance_schema events_transactions_summary_by_account_by_event_name USER
165 def performance_schema ACCOUNT def performance_schema events_waits_summary_by_account_by_event_name EVENT_NAME
166 def performance_schema ACCOUNT def performance_schema events_waits_summary_by_account_by_event_name HOST
167 def performance_schema ACCOUNT def performance_schema events_waits_summary_by_account_by_event_name USER
168 def performance_schema ACCOUNT def performance_schema memory_summary_by_account_by_event_name EVENT_NAME
169 def performance_schema ACCOUNT def performance_schema memory_summary_by_account_by_event_name HOST
170 def performance_schema ACCOUNT def performance_schema memory_summary_by_account_by_event_name USER
171 def performance_schema ACCOUNT def performance_schema status_by_account HOST
172 def performance_schema ACCOUNT def performance_schema status_by_account USER
173 def performance_schema ACCOUNT def performance_schema status_by_account VARIABLE_NAME
174 def performance_schema ERROR_NUMBER def performance_schema events_errors_summary_global_by_error ERROR_NUMBER
175 def performance_schema HOST def performance_schema events_errors_summary_by_host_by_error ERROR_NUMBER
176 def performance_schema HOST def performance_schema events_errors_summary_by_host_by_error HOST
177 def performance_schema HOST def performance_schema events_stages_summary_by_host_by_event_name EVENT_NAME
178 def performance_schema HOST def performance_schema events_stages_summary_by_host_by_event_name HOST
179 def performance_schema HOST def performance_schema events_statements_summary_by_host_by_event_name EVENT_NAME
180 def performance_schema HOST def performance_schema events_statements_summary_by_host_by_event_name HOST
181 def performance_schema HOST def performance_schema events_transactions_summary_by_host_by_event_name EVENT_NAME
182 def performance_schema HOST def performance_schema events_transactions_summary_by_host_by_event_name HOST
183 def performance_schema HOST def performance_schema events_waits_summary_by_host_by_event_name EVENT_NAME
184 def performance_schema HOST def performance_schema events_waits_summary_by_host_by_event_name HOST
185 def performance_schema HOST def performance_schema hosts HOST
186 def performance_schema HOST def performance_schema memory_summary_by_host_by_event_name EVENT_NAME
187 def performance_schema HOST def performance_schema memory_summary_by_host_by_event_name HOST
188 def performance_schema HOST def performance_schema status_by_host HOST
189 def performance_schema HOST def performance_schema status_by_host VARIABLE_NAME
190 def performance_schema OBJECT def performance_schema objects_summary_global_by_type OBJECT_NAME
191 def performance_schema OBJECT def performance_schema objects_summary_global_by_type OBJECT_SCHEMA
192 def performance_schema OBJECT def performance_schema objects_summary_global_by_type OBJECT_TYPE
193 def performance_schema OBJECT def performance_schema setup_objects OBJECT_NAME
194 def performance_schema OBJECT def performance_schema setup_objects OBJECT_SCHEMA
195 def performance_schema OBJECT def performance_schema setup_objects OBJECT_TYPE
196 def performance_schema OBJECT def performance_schema table_io_waits_summary_by_index_usage INDEX_NAME
197 def performance_schema OBJECT def performance_schema table_io_waits_summary_by_index_usage OBJECT_NAME
198 def performance_schema OBJECT def performance_schema table_io_waits_summary_by_index_usage OBJECT_SCHEMA
199 def performance_schema OBJECT def performance_schema table_io_waits_summary_by_index_usage OBJECT_TYPE
200 def performance_schema OBJECT def performance_schema table_io_waits_summary_by_table OBJECT_NAME
201 def performance_schema OBJECT def performance_schema table_io_waits_summary_by_table OBJECT_SCHEMA
202 def performance_schema OBJECT def performance_schema table_io_waits_summary_by_table OBJECT_TYPE
203 def performance_schema OBJECT def performance_schema table_lock_waits_summary_by_table OBJECT_NAME
204 def performance_schema OBJECT def performance_schema table_lock_waits_summary_by_table OBJECT_SCHEMA
205 def performance_schema OBJECT def performance_schema table_lock_waits_summary_by_table OBJECT_TYPE
206 def performance_schema OWNER_THREAD_ID def performance_schema prepared_statements_instances OWNER_EVENT_ID
207 def performance_schema OWNER_THREAD_ID def performance_schema prepared_statements_instances OWNER_THREAD_ID
208 def performance_schema PRIMARY def performance_schema cond_instances OBJECT_INSTANCE_BEGIN
209 def performance_schema PRIMARY def performance_schema data_lock_waits BLOCKING_ENGINE_LOCK_ID
210 def performance_schema PRIMARY def performance_schema data_lock_waits ENGINE
211 def performance_schema PRIMARY def performance_schema data_lock_waits REQUESTING_ENGINE_LOCK_ID
212 def performance_schema PRIMARY def performance_schema data_locks ENGINE
213 def performance_schema PRIMARY def performance_schema data_locks ENGINE_LOCK_ID
214 def performance_schema PRIMARY def performance_schema error_log LOGGED
215 def performance_schema PRIMARY def performance_schema events_stages_current EVENT_ID
216 def performance_schema PRIMARY def performance_schema events_stages_current THREAD_ID
217 def performance_schema PRIMARY def performance_schema events_stages_history EVENT_ID
218 def performance_schema PRIMARY def performance_schema events_stages_history THREAD_ID
219 def performance_schema PRIMARY def performance_schema events_stages_summary_by_thread_by_event_name EVENT_NAME
220 def performance_schema PRIMARY def performance_schema events_stages_summary_by_thread_by_event_name THREAD_ID
221 def performance_schema PRIMARY def performance_schema events_stages_summary_global_by_event_name EVENT_NAME
222 def performance_schema PRIMARY def performance_schema events_statements_current EVENT_ID
223 def performance_schema PRIMARY def performance_schema events_statements_current THREAD_ID
224 def performance_schema PRIMARY def performance_schema events_statements_histogram_global BUCKET_NUMBER
225 def performance_schema PRIMARY def performance_schema events_statements_history EVENT_ID
226 def performance_schema PRIMARY def performance_schema events_statements_history THREAD_ID
227 def performance_schema PRIMARY def performance_schema events_statements_summary_by_program OBJECT_NAME
228 def performance_schema PRIMARY def performance_schema events_statements_summary_by_program OBJECT_SCHEMA
229 def performance_schema PRIMARY def performance_schema events_statements_summary_by_program OBJECT_TYPE
230 def performance_schema PRIMARY def performance_schema events_statements_summary_by_thread_by_event_name EVENT_NAME
231 def performance_schema PRIMARY def performance_schema events_statements_summary_by_thread_by_event_name THREAD_ID
232 def performance_schema PRIMARY def performance_schema events_statements_summary_global_by_event_name EVENT_NAME
233 def performance_schema PRIMARY def performance_schema events_transactions_current EVENT_ID
234 def performance_schema PRIMARY def performance_schema events_transactions_current THREAD_ID
235 def performance_schema PRIMARY def performance_schema events_transactions_history EVENT_ID
236 def performance_schema PRIMARY def performance_schema events_transactions_history THREAD_ID
237 def performance_schema PRIMARY def performance_schema events_transactions_summary_by_thread_by_event_name EVENT_NAME
238 def performance_schema PRIMARY def performance_schema events_transactions_summary_by_thread_by_event_name THREAD_ID
239 def performance_schema PRIMARY def performance_schema events_transactions_summary_global_by_event_name EVENT_NAME
240 def performance_schema PRIMARY def performance_schema events_waits_current EVENT_ID
241 def performance_schema PRIMARY def performance_schema events_waits_current THREAD_ID
242 def performance_schema PRIMARY def performance_schema events_waits_history EVENT_ID
243 def performance_schema PRIMARY def performance_schema events_waits_history THREAD_ID
244 def performance_schema PRIMARY def performance_schema events_waits_summary_by_instance OBJECT_INSTANCE_BEGIN
245 def performance_schema PRIMARY def performance_schema events_waits_summary_by_thread_by_event_name EVENT_NAME
246 def performance_schema PRIMARY def performance_schema events_waits_summary_by_thread_by_event_name THREAD_ID
247 def performance_schema PRIMARY def performance_schema events_waits_summary_global_by_event_name EVENT_NAME
248 def performance_schema PRIMARY def performance_schema file_instances FILE_NAME
249 def performance_schema PRIMARY def performance_schema file_summary_by_event_name EVENT_NAME
250 def performance_schema PRIMARY def performance_schema file_summary_by_instance OBJECT_INSTANCE_BEGIN
251 def performance_schema PRIMARY def performance_schema global_status VARIABLE_NAME
252 def performance_schema PRIMARY def performance_schema global_variables VARIABLE_NAME
253 def performance_schema PRIMARY def performance_schema host_cache IP
254 def performance_schema PRIMARY def performance_schema memory_summary_by_thread_by_event_name EVENT_NAME
255 def performance_schema PRIMARY def performance_schema memory_summary_by_thread_by_event_name THREAD_ID
256 def performance_schema PRIMARY def performance_schema memory_summary_global_by_event_name EVENT_NAME
257 def performance_schema PRIMARY def performance_schema metadata_locks OBJECT_INSTANCE_BEGIN
258 def performance_schema PRIMARY def performance_schema mutex_instances OBJECT_INSTANCE_BEGIN
259 def performance_schema PRIMARY def performance_schema persisted_variables VARIABLE_NAME
260 def performance_schema PRIMARY def performance_schema prepared_statements_instances OBJECT_INSTANCE_BEGIN
261 def performance_schema PRIMARY def performance_schema processlist ID
262 def performance_schema PRIMARY def performance_schema replication_applier_configuration CHANNEL_NAME
263 def performance_schema PRIMARY def performance_schema replication_applier_status CHANNEL_NAME
264 def performance_schema PRIMARY def performance_schema replication_applier_status_by_coordinator CHANNEL_NAME
265 def performance_schema PRIMARY def performance_schema replication_applier_status_by_worker CHANNEL_NAME
266 def performance_schema PRIMARY def performance_schema replication_applier_status_by_worker WORKER_ID
267 def performance_schema PRIMARY def performance_schema replication_connection_configuration CHANNEL_NAME
268 def performance_schema PRIMARY def performance_schema replication_connection_status CHANNEL_NAME
269 def performance_schema PRIMARY def performance_schema rwlock_instances OBJECT_INSTANCE_BEGIN
270 def performance_schema PRIMARY def performance_schema session_account_connect_attrs ATTR_NAME
271 def performance_schema PRIMARY def performance_schema session_account_connect_attrs PROCESSLIST_ID
272 def performance_schema PRIMARY def performance_schema session_connect_attrs ATTR_NAME
273 def performance_schema PRIMARY def performance_schema session_connect_attrs PROCESSLIST_ID
274 def performance_schema PRIMARY def performance_schema session_status VARIABLE_NAME
275 def performance_schema PRIMARY def performance_schema session_variables VARIABLE_NAME
276 def performance_schema PRIMARY def performance_schema setup_actors HOST
277 def performance_schema PRIMARY def performance_schema setup_actors ROLE
278 def performance_schema PRIMARY def performance_schema setup_actors USER
279 def performance_schema PRIMARY def performance_schema setup_consumers NAME
280 def performance_schema PRIMARY def performance_schema setup_instruments NAME
281 def performance_schema PRIMARY def performance_schema setup_threads NAME
282 def performance_schema PRIMARY def performance_schema socket_instances OBJECT_INSTANCE_BEGIN
283 def performance_schema PRIMARY def performance_schema socket_summary_by_event_name EVENT_NAME
284 def performance_schema PRIMARY def performance_schema socket_summary_by_instance OBJECT_INSTANCE_BEGIN
285 def performance_schema PRIMARY def performance_schema status_by_thread THREAD_ID
286 def performance_schema PRIMARY def performance_schema status_by_thread VARIABLE_NAME
287 def performance_schema PRIMARY def performance_schema table_handles OBJECT_INSTANCE_BEGIN
288 def performance_schema PRIMARY def performance_schema threads THREAD_ID
289 def performance_schema PRIMARY def performance_schema user_defined_functions UDF_NAME
290 def performance_schema PRIMARY def performance_schema user_variables_by_thread THREAD_ID
291 def performance_schema PRIMARY def performance_schema user_variables_by_thread VARIABLE_NAME
292 def performance_schema PRIMARY def performance_schema variables_by_thread THREAD_ID
293 def performance_schema PRIMARY def performance_schema variables_by_thread VARIABLE_NAME
294 def performance_schema SCHEMA_NAME def performance_schema events_statements_histogram_by_digest BUCKET_NUMBER
295 def performance_schema SCHEMA_NAME def performance_schema events_statements_histogram_by_digest DIGEST
296 def performance_schema SCHEMA_NAME def performance_schema events_statements_histogram_by_digest SCHEMA_NAME
297 def performance_schema SCHEMA_NAME def performance_schema events_statements_summary_by_digest DIGEST
298 def performance_schema SCHEMA_NAME def performance_schema events_statements_summary_by_digest SCHEMA_NAME
299 def performance_schema THREAD_ID def performance_schema events_errors_summary_by_thread_by_error ERROR_NUMBER
300 def performance_schema THREAD_ID def performance_schema events_errors_summary_by_thread_by_error THREAD_ID
301 def performance_schema USER def performance_schema events_errors_summary_by_user_by_error ERROR_NUMBER
302 def performance_schema USER def performance_schema events_errors_summary_by_user_by_error USER
303 def performance_schema USER def performance_schema events_stages_summary_by_user_by_event_name EVENT_NAME
304 def performance_schema USER def performance_schema events_stages_summary_by_user_by_event_name USER
305 def performance_schema USER def performance_schema events_statements_summary_by_user_by_event_name EVENT_NAME
306 def performance_schema USER def performance_schema events_statements_summary_by_user_by_event_name USER
307 def performance_schema USER def performance_schema events_transactions_summary_by_user_by_event_name EVENT_NAME
308 def performance_schema USER def performance_schema events_transactions_summary_by_user_by_event_name USER
309 def performance_schema USER def performance_schema events_waits_summary_by_user_by_event_name EVENT_NAME
310 def performance_schema USER def performance_schema events_waits_summary_by_user_by_event_name USER
311 def performance_schema USER def performance_schema memory_summary_by_user_by_event_name EVENT_NAME
312 def performance_schema USER def performance_schema memory_summary_by_user_by_event_name USER
313 def performance_schema USER def performance_schema status_by_user USER
314 def performance_schema USER def performance_schema status_by_user VARIABLE_NAME
315 def performance_schema USER def performance_schema users USER
316 def sys PRIMARY def sys sys_config variable
317 ########################################################################################
318 # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.KEY_COLUMN_USAGE accessible information
319 ########################################################################################
320 DROP DATABASE IF EXISTS db_datadict;
321 CREATE DATABASE db_datadict;
322 DROP USER 'testuser1'@'localhost';
323 CREATE USER 'testuser1'@'localhost';
324 DROP USER 'testuser2'@'localhost';
325 CREATE USER 'testuser2'@'localhost';
326 USE db_datadict;
327 CREATE TABLE t1_1
328 (f1 INT NOT NULL, PRIMARY KEY(f1),
329 f2 INT, INDEX f2_ind(f2))
330 ENGINE = <engine_type>;
331 GRANT SELECT ON t1_1 to 'testuser1'@'localhost';
332 CREATE TABLE t1_2
333 (f1 INT NOT NULL, PRIMARY KEY(f1),
334 f2 INT, INDEX f2_ind(f2))
335 ENGINE = <engine_type>;
336 GRANT SELECT ON t1_2 to 'testuser2'@'localhost';
337 SELECT * FROM information_schema.key_column_usage
338 WHERE table_name LIKE 't1_%'
339 ORDER BY constraint_catalog, constraint_schema, constraint_name,
340 table_catalog, table_schema, table_name, ordinal_position;
341 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
342 def db_datadict PRIMARY def db_datadict t1_1 f1 1 NULL NULL NULL NULL
343 def db_datadict PRIMARY def db_datadict t1_2 f1 1 NULL NULL NULL NULL
344 # Establish connection testuser1 (user=testuser1)
345 SELECT * FROM information_schema.key_column_usage
346 WHERE table_name LIKE 't1_%'
347 ORDER BY constraint_catalog, constraint_schema, constraint_name,
348 table_catalog, table_schema, table_name, ordinal_position;
349 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
350 def db_datadict PRIMARY def db_datadict t1_1 f1 1 NULL NULL NULL NULL
351 # Establish connection testuser2 (user=testuser2)
352 SELECT * FROM information_schema.key_column_usage
353 WHERE table_name LIKE 't1_%'
354 ORDER BY constraint_catalog, constraint_schema, constraint_name,
355 table_catalog, table_schema, table_name, ordinal_position;
356 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
357 def db_datadict PRIMARY def db_datadict t1_2 f1 1 NULL NULL NULL NULL
358 # Switch to connection default and close connections testuser1, testuser2
359 DROP USER 'testuser1'@'localhost';
360 DROP USER 'testuser2'@'localhost';
361 DROP TABLE t1_1;
362 DROP TABLE t1_2;
363 DROP DATABASE IF EXISTS db_datadict;
364 ########################################################################################
365 # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.KEY_COLUMN_USAGE modifications
366 ########################################################################################
367 DROP DATABASE IF EXISTS db_datadict;
368 DROP TABLE IF EXISTS test.t1_my_table;
369 CREATE DATABASE db_datadict;
370 SELECT table_name FROM information_schema.key_column_usage
371 WHERE table_name LIKE 't1_my_table%';
372 TABLE_NAME
373 CREATE TABLE test.t1_my_table
374 (f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1,f2))
375 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
376 ENGINE = <engine_type>;
377 SELECT * FROM information_schema.key_column_usage
378 WHERE table_name = 't1_my_table' ORDER BY column_name;
379 CONSTRAINT_CATALOG def
380 CONSTRAINT_SCHEMA test
381 CONSTRAINT_NAME PRIMARY
382 TABLE_CATALOG def
383 TABLE_SCHEMA test
384 TABLE_NAME t1_my_table
385 COLUMN_NAME f1
386 ORDINAL_POSITION #
387 POSITION_IN_UNIQUE_CONSTRAINT NULL
388 REFERENCED_TABLE_SCHEMA NULL
389 REFERENCED_TABLE_NAME NULL
390 REFERENCED_COLUMN_NAME NULL
391 CONSTRAINT_CATALOG def
392 CONSTRAINT_SCHEMA test
393 CONSTRAINT_NAME PRIMARY
394 TABLE_CATALOG def
395 TABLE_SCHEMA test
396 TABLE_NAME t1_my_table
397 COLUMN_NAME f2
398 ORDINAL_POSITION #
399 POSITION_IN_UNIQUE_CONSTRAINT NULL
400 REFERENCED_TABLE_SCHEMA NULL
401 REFERENCED_TABLE_NAME NULL
402 REFERENCED_COLUMN_NAME NULL
403 SELECT DISTINCT table_name FROM information_schema.key_column_usage
404 WHERE table_name LIKE 't1_my_table%';
405 TABLE_NAME
406 t1_my_table
407 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
408 SELECT DISTINCT table_name FROM information_schema.key_column_usage
409 WHERE table_name LIKE 't1_my_table%';
410 TABLE_NAME
411 t1_my_tablex
412 SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
413 WHERE table_name = 't1_my_tablex';
414 TABLE_SCHEMA TABLE_NAME
415 test t1_my_tablex
416 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
417 SELECT DISTINCT table_schema,table_name FROM information_schema.key_column_usage
418 WHERE table_name = 't1_my_tablex';
419 TABLE_SCHEMA TABLE_NAME
420 db_datadict t1_my_tablex
421 SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
422 WHERE table_name = 't1_my_tablex'
423 ORDER BY table_name, column_name;
424 TABLE_NAME COLUMN_NAME
425 t1_my_tablex f1
426 t1_my_tablex f2
427 ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
428 SELECT DISTINCT table_name, column_name FROM information_schema.key_column_usage
429 WHERE table_name = 't1_my_tablex'
430 ORDER BY table_name, column_name;
431 TABLE_NAME COLUMN_NAME
432 t1_my_tablex f2
433 t1_my_tablex first_col
434 SELECT constraint_schema, constraint_name, table_schema,
435 table_name, column_name, ordinal_position
436 FROM information_schema.key_column_usage
437 WHERE table_name = 't1_my_tablex'
438 ORDER BY constraint_schema, constraint_name, table_schema,
439 table_name, ordinal_position;
440 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
441 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
442 db_datadict PRIMARY db_datadict t1_my_tablex f2 2
443 CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
444 SELECT constraint_schema, constraint_name, table_schema,
445 table_name, column_name, ordinal_position
446 FROM information_schema.key_column_usage
447 WHERE table_name = 't1_my_tablex'
448 ORDER BY constraint_schema, constraint_name, table_schema,
449 table_name, ordinal_position;
450 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
451 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
452 db_datadict PRIMARY db_datadict t1_my_tablex f2 2
453 DROP INDEX f2 ON db_datadict.t1_my_tablex;
454 SELECT constraint_schema, constraint_name, table_schema,
455 table_name, column_name, ordinal_position
456 FROM information_schema.key_column_usage
457 WHERE table_name = 't1_my_tablex'
458 ORDER BY constraint_schema, constraint_name, table_schema,
459 table_name, ordinal_position;
460 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
461 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
462 db_datadict PRIMARY db_datadict t1_my_tablex f2 2
463 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
464 SELECT constraint_schema, constraint_name, table_schema,
465 table_name, column_name, ordinal_position
466 FROM information_schema.key_column_usage
467 WHERE table_name = 't1_my_tablex'
468 ORDER BY constraint_schema, constraint_name, table_schema,
469 table_name, ordinal_position;
470 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
471 db_datadict f2 db_datadict t1_my_tablex f2 1
472 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
473 db_datadict PRIMARY db_datadict t1_my_tablex f2 2
474 DROP INDEX f2 ON db_datadict.t1_my_tablex;
475 SELECT constraint_schema, constraint_name, table_schema,
476 table_name, column_name, ordinal_position
477 FROM information_schema.key_column_usage
478 WHERE table_name = 't1_my_tablex'
479 ORDER BY constraint_schema, constraint_name, table_schema,
480 table_name, ordinal_position;
481 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
482 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
483 db_datadict PRIMARY db_datadict t1_my_tablex f2 2
484 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
485 SELECT constraint_schema, constraint_name, table_schema,
486 table_name, column_name, ordinal_position
487 FROM information_schema.key_column_usage
488 WHERE table_name = 't1_my_tablex'
489 ORDER BY constraint_schema, constraint_name, table_schema,
490 table_name, ordinal_position;
491 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
492 db_datadict my_idx db_datadict t1_my_tablex f2 1
493 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
494 db_datadict PRIMARY db_datadict t1_my_tablex f2 2
495 DROP INDEX my_idx ON db_datadict.t1_my_tablex;
496 SELECT constraint_schema, constraint_name, table_schema,
497 table_name, column_name, ordinal_position
498 FROM information_schema.key_column_usage
499 WHERE table_name = 't1_my_tablex'
500 ORDER BY constraint_schema, constraint_name, table_schema,
501 table_name, ordinal_position;
502 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
503 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
504 db_datadict PRIMARY db_datadict t1_my_tablex f2 2
505 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,first_col);
506 SELECT constraint_schema, constraint_name, table_schema,
507 table_name, column_name, ordinal_position
508 FROM information_schema.key_column_usage
509 WHERE table_name = 't1_my_tablex'
510 ORDER BY constraint_schema, constraint_name, table_schema,
511 table_name, ordinal_position;
512 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
513 db_datadict my_idx db_datadict t1_my_tablex f4 1
514 db_datadict my_idx db_datadict t1_my_tablex first_col 2
515 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
516 db_datadict PRIMARY db_datadict t1_my_tablex f2 2
517 SELECT constraint_schema, constraint_name, table_schema,
518 table_name, column_name, ordinal_position
519 FROM information_schema.key_column_usage
520 WHERE table_name = 't1_my_tablex'
521 ORDER BY constraint_schema, constraint_name, table_schema,
522 table_name, ordinal_position;
523 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
524 db_datadict my_idx db_datadict t1_my_tablex f4 1
525 db_datadict my_idx db_datadict t1_my_tablex first_col 2
526 db_datadict PRIMARY db_datadict t1_my_tablex first_col 1
527 db_datadict PRIMARY db_datadict t1_my_tablex f2 2
528 ALTER TABLE db_datadict.t1_my_tablex
529 DROP COLUMN first_col;
530 SELECT constraint_schema, constraint_name, table_schema,
531 table_name, column_name, ordinal_position
532 FROM information_schema.key_column_usage
533 WHERE table_name = 't1_my_tablex'
534 ORDER BY constraint_schema, constraint_name, table_schema,
535 table_name, ordinal_position;
536 CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME COLUMN_NAME ORDINAL_POSITION
537 db_datadict my_idx db_datadict t1_my_tablex f4 1
538 db_datadict PRIMARY db_datadict t1_my_tablex f2 1
539 SELECT table_name, column_name
540 FROM information_schema.key_column_usage
541 WHERE table_name = 't1_my_tablex'
542 ORDER BY table_name, column_name;
543 TABLE_NAME COLUMN_NAME
544 t1_my_tablex f2
545 t1_my_tablex f4
546 DROP TABLE db_datadict.t1_my_tablex;
547 SELECT table_name, column_name
548 FROM information_schema.key_column_usage
549 WHERE table_name = 't1_my_tablex';
550 TABLE_NAME COLUMN_NAME
551 SELECT table_name FROM information_schema.key_column_usage
552 WHERE table_name = 't1_my_tablex';
553 TABLE_NAME
554 CREATE TABLE db_datadict.t1_my_tablex
555 ENGINE = <engine_type> AS
556 SELECT 1 AS f1;
557 SELECT table_name FROM information_schema.key_column_usage
558 WHERE table_name = 't1_my_tablex';
559 TABLE_NAME
560 ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
561 SELECT table_name FROM information_schema.key_column_usage
562 WHERE table_name = 't1_my_tablex';
563 TABLE_NAME
564 t1_my_tablex
565 SELECT table_name FROM information_schema.key_column_usage
566 WHERE table_name = 't1_my_tablex';
567 TABLE_NAME
568 t1_my_tablex
569 DROP DATABASE db_datadict;
570 SELECT table_name FROM information_schema.key_column_usage
571 WHERE table_name = 't1_my_tablex';
572 TABLE_NAME
573 ########################################################################
574 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
575 # DDL on INFORMATION_SCHEMA table are not supported
576 ########################################################################
577 DROP DATABASE IF EXISTS db_datadict;
578 DROP TABLE IF EXISTS db_datadict.t1;
579 CREATE DATABASE db_datadict;
580 CREATE TABLE db_datadict.t1 (f1 BIGINT)
581 ENGINE = <engine_type>;
582 INSERT INTO information_schema.key_column_usage
583 (constraint_schema, constraint_name, table_name)
584 VALUES ( 'mysql', 'primary', 'db');
585 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
586 INSERT INTO information_schema.key_column_usage
587 SELECT * FROM information_schema.key_column_usage;
588 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
589 UPDATE information_schema.key_column_usage
590 SET table_name = 'db1' WHERE constraint_name = 'primary';
591 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
592 DELETE FROM information_schema.key_column_usage WHERE table_name = 't1';
593 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
594 TRUNCATE information_schema.key_column_usage;
595 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
596 CREATE INDEX i3 ON information_schema.key_column_usage(table_name);
597 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
598 ALTER TABLE information_schema.key_column_usage ADD f1 INT;
599 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
600 DROP TABLE information_schema.key_column_usage;
601 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
602 ALTER TABLE information_schema.key_column_usage
603 RENAME db_datadict.key_column_usage;
604 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
605 ALTER TABLE information_schema.key_column_usage
606 RENAME information_schema.xkey_column_usage;
607 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
608 DROP TABLE db_datadict.t1;
609 DROP DATABASE db_datadict;