Debian Codegraph @

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

raw
1 SHOW TABLES FROM information_schema LIKE 'TABLE_CONSTRAINTS';
2 Tables_in_information_schema (TABLE_CONSTRAINTS)
3 TABLE_CONSTRAINTS
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.TABLE_CONSTRAINTS;
11 CREATE PROCEDURE test.p1() SELECT * FROM information_schema.TABLE_CONSTRAINTS;
12 CREATE FUNCTION test.f1() returns BIGINT
13 BEGIN
14 DECLARE counter BIGINT DEFAULT NULL;
15 SELECT COUNT(*) INTO counter FROM information_schema.TABLE_CONSTRAINTS;
16 RETURN counter;
17 END//
18 # Attention: The printing of the next result sets is disabled.
19 SELECT * FROM information_schema.TABLE_CONSTRAINTS;
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.10.1: INFORMATION_SCHEMA.TABLE_CONSTRAINTS layout
28 #########################################################################
29 DESCRIBE information_schema.TABLE_CONSTRAINTS;
30 Field Type Null Key Default Extra
31 CONSTRAINT_CATALOG varchar(512) NO NULL
32 CONSTRAINT_SCHEMA varchar(64) NO NULL
33 CONSTRAINT_NAME varchar(64) NO NULL
34 TABLE_SCHEMA varchar(64) NO NULL
35 TABLE_NAME varchar(64) NO NULL
36 CONSTRAINT_TYPE varchar(64) NO NULL
37 SHOW CREATE TABLE information_schema.TABLE_CONSTRAINTS;
38 Table Create Table
39 TABLE_CONSTRAINTS CREATE TEMPORARY TABLE `TABLE_CONSTRAINTS` (
40 `CONSTRAINT_CATALOG` varchar(512) NOT NULL,
41 `CONSTRAINT_SCHEMA` varchar(64) NOT NULL,
42 `CONSTRAINT_NAME` varchar(64) NOT NULL,
43 `TABLE_SCHEMA` varchar(64) NOT NULL,
44 `TABLE_NAME` varchar(64) NOT NULL,
45 `CONSTRAINT_TYPE` varchar(64) NOT NULL
46 ) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_general_ci
47 SHOW COLUMNS FROM information_schema.TABLE_CONSTRAINTS;
48 Field Type Null Key Default Extra
49 CONSTRAINT_CATALOG varchar(512) NO NULL
50 CONSTRAINT_SCHEMA varchar(64) NO NULL
51 CONSTRAINT_NAME varchar(64) NO NULL
52 TABLE_SCHEMA varchar(64) NO NULL
53 TABLE_NAME varchar(64) NO NULL
54 CONSTRAINT_TYPE varchar(64) NO NULL
55 SELECT constraint_catalog, constraint_schema, constraint_name,
56 table_schema, table_name
57 FROM information_schema.table_constraints
58 WHERE constraint_catalog IS NOT NULL
59 ORDER BY constraint_schema, table_name, constraint_name;
60 constraint_catalog constraint_schema constraint_name table_schema table_name
61 def mysql PRIMARY mysql columns_priv
62 def mysql PRIMARY mysql column_stats
63 def mysql PRIMARY mysql db
64 def mysql PRIMARY mysql event
65 def mysql PRIMARY mysql func
66 def mysql PRIMARY mysql global_priv
67 def mysql Priv mysql global_priv
68 def mysql PRIMARY mysql gtid_slave_pos
69 def mysql name mysql help_category
70 def mysql PRIMARY mysql help_category
71 def mysql name mysql help_keyword
72 def mysql PRIMARY mysql help_keyword
73 def mysql PRIMARY mysql help_relation
74 def mysql name mysql help_topic
75 def mysql PRIMARY mysql help_topic
76 def mysql PRIMARY mysql index_stats
77 def mysql PRIMARY mysql innodb_index_stats
78 def mysql PRIMARY mysql innodb_table_stats
79 def mysql PRIMARY mysql plugin
80 def mysql PRIMARY mysql proc
81 def mysql PRIMARY mysql procs_priv
82 def mysql PRIMARY mysql proxies_priv
83 def mysql Host mysql roles_mapping
84 def mysql Options mysql servers
85 def mysql PRIMARY mysql servers
86 def mysql PRIMARY mysql tables_priv
87 def mysql PRIMARY mysql table_stats
88 def mysql PRIMARY mysql time_zone
89 def mysql PRIMARY mysql time_zone_leap_second
90 def mysql PRIMARY mysql time_zone_name
91 def mysql PRIMARY mysql time_zone_transition
92 def mysql PRIMARY mysql time_zone_transition_type
93 def mysql commit_id mysql transaction_registry
94 def mysql PRIMARY mysql transaction_registry
95 def sys PRIMARY sys sys_config
96 #########################################################################################
97 # Testcase 3.2.7.2 + 3.2.7.3: INFORMATION_SCHEMA.TABLE_CONSTRAINTS accessible information
98 #########################################################################################
99 CREATE DATABASE db_datadict;
100 CREATE TABLE db_datadict.t1 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT,
101 f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2))
102 ENGINE = <some_engine_type>;
103 CREATE UNIQUE INDEX my_idx1 ON db_datadict.t1(f6,f1);
104 CREATE UNIQUE INDEX my_idx2 ON db_datadict.t1(f3);
105 CREATE TABLE db_datadict.t2 (f1 BIGINT, f2 BIGINT, f3 BIGINT, f4 BIGINT,
106 f5 BIGINT, f6 BIGINT, PRIMARY KEY (f1,f2))
107 ENGINE = <some_engine_type>;
108 CREATE USER 'testuser1'@'localhost';
109 GRANT SELECT(f5), UPDATE(f6) ON db_datadict.t1 TO 'testuser1'@'localhost';
110 SHOW GRANTS FOR 'testuser1'@'localhost';
111 Grants for testuser1@localhost
112 GRANT USAGE ON *.* TO `testuser1`@`localhost`
113 GRANT SELECT (`f5`), UPDATE (`f6`) ON `db_datadict`.`t1` TO `testuser1`@`localhost`
114 SELECT * FROM information_schema.table_constraints
115 WHERE table_schema = 'db_datadict'
116 ORDER BY table_schema,table_name, constraint_name;
117 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
118 def db_datadict my_idx1 db_datadict t1 UNIQUE
119 def db_datadict my_idx2 db_datadict t1 UNIQUE
120 def db_datadict PRIMARY db_datadict t1 PRIMARY KEY
121 def db_datadict PRIMARY db_datadict t2 PRIMARY KEY
122 SHOW INDEXES FROM db_datadict.t1;
123 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
124 t1 0 PRIMARY 1 f1 ### ### ### ### ### ### ### NO
125 t1 0 PRIMARY 2 f2 ### ### ### ### ### ### ### NO
126 t1 0 my_idx1 1 f6 ### ### ### ### ### ### ### NO
127 t1 0 my_idx1 2 f1 ### ### ### ### ### ### ### NO
128 t1 0 my_idx2 1 f3 ### ### ### ### ### ### ### NO
129 SHOW INDEXES FROM db_datadict.t2;
130 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
131 t2 0 PRIMARY 1 f1 ### ### ### ### ### ### ### NO
132 t2 0 PRIMARY 2 f2 ### ### ### ### ### ### ### NO
133 connect testuser1, localhost, testuser1, , db_datadict;
134 SHOW GRANTS FOR 'testuser1'@'localhost';
135 Grants for testuser1@localhost
136 GRANT USAGE ON *.* TO `testuser1`@`localhost`
137 GRANT SELECT (`f5`), UPDATE (`f6`) ON `db_datadict`.`t1` TO `testuser1`@`localhost`
138 SELECT * FROM information_schema.table_constraints
139 WHERE table_schema = 'db_datadict'
140 ORDER BY table_schema,table_name, constraint_name;
141 CONSTRAINT_CATALOG CONSTRAINT_SCHEMA CONSTRAINT_NAME TABLE_SCHEMA TABLE_NAME CONSTRAINT_TYPE
142 def db_datadict my_idx1 db_datadict t1 UNIQUE
143 def db_datadict my_idx2 db_datadict t1 UNIQUE
144 def db_datadict PRIMARY db_datadict t1 PRIMARY KEY
145 SHOW INDEXES FROM db_datadict.t1;
146 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Ignored
147 SHOW INDEXES FROM db_datadict.t2;
148 ERROR 42000: SELECT command denied to user 'testuser1'@'localhost' for table `db_datadict`.`t2`
149 connection default;
150 disconnect testuser1;
151 DROP USER 'testuser1'@'localhost';
152 DROP DATABASE db_datadict;
153 #########################################################################################
154 # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.TABLE_CONSTRAINTS modifications
155 #########################################################################################
156 CREATE DATABASE db_datadict;
157 SELECT table_name FROM information_schema.table_constraints
158 WHERE table_name LIKE 't1_my_table%';
159 table_name
160 CREATE TABLE test.t1_my_table
161 (f1 CHAR(12), f2 TIMESTAMP, f4 BIGINT, PRIMARY KEY(f1))
162 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
163 ENGINE = <engine_type>;
164 SELECT constraint_name, table_schema, table_name, constraint_type
165 FROM information_schema.table_constraints
166 WHERE table_name = 't1_my_table';
167 constraint_name table_schema table_name constraint_type
168 PRIMARY test t1_my_table PRIMARY KEY
169 SELECT table_name FROM information_schema.table_constraints
170 WHERE table_name LIKE 't1_my_table%';
171 table_name
172 t1_my_table
173 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
174 SELECT table_name FROM information_schema.table_constraints
175 WHERE table_name LIKE 't1_my_table%';
176 table_name
177 t1_my_tablex
178 SELECT table_schema,table_name FROM information_schema.table_constraints
179 WHERE table_name = 't1_my_tablex';
180 table_schema table_name
181 test t1_my_tablex
182 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
183 SELECT table_schema,table_name FROM information_schema.table_constraints
184 WHERE table_name = 't1_my_tablex';
185 table_schema table_name
186 db_datadict t1_my_tablex
187 SELECT constraint_schema, constraint_name, table_schema,
188 table_name, constraint_type
189 FROM information_schema.table_constraints
190 WHERE table_name = 't1_my_tablex'
191 ORDER BY table_schema,table_name, constraint_name;
192 constraint_schema constraint_name table_schema table_name constraint_type
193 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
194 CREATE INDEX f2 ON db_datadict.t1_my_tablex(f2);
195 SELECT constraint_schema, constraint_name, table_schema,
196 table_name, constraint_type
197 FROM information_schema.table_constraints
198 WHERE table_name = 't1_my_tablex'
199 ORDER BY table_schema,table_name, constraint_name;
200 constraint_schema constraint_name table_schema table_name constraint_type
201 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
202 DROP INDEX f2 ON db_datadict.t1_my_tablex;
203 SELECT constraint_schema, constraint_name, table_schema,
204 table_name, constraint_type
205 FROM information_schema.table_constraints
206 WHERE table_name = 't1_my_tablex'
207 ORDER BY table_schema,table_name, constraint_name;
208 constraint_schema constraint_name table_schema table_name constraint_type
209 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
210 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE (f2);
211 SELECT constraint_schema, constraint_name, table_schema,
212 table_name, constraint_type
213 FROM information_schema.table_constraints
214 WHERE table_name = 't1_my_tablex'
215 ORDER BY table_schema,table_name, constraint_name;
216 constraint_schema constraint_name table_schema table_name constraint_type
217 db_datadict f2 db_datadict t1_my_tablex UNIQUE
218 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
219 DROP INDEX f2 ON db_datadict.t1_my_tablex;
220 SELECT constraint_schema, constraint_name, table_schema,
221 table_name, constraint_type
222 FROM information_schema.table_constraints
223 WHERE table_name = 't1_my_tablex'
224 ORDER BY table_schema,table_name, constraint_name;
225 constraint_schema constraint_name table_schema table_name constraint_type
226 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
227 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f4,f1);
228 SELECT constraint_schema, constraint_name, table_schema,
229 table_name, constraint_type
230 FROM information_schema.table_constraints
231 WHERE table_name = 't1_my_tablex'
232 ORDER BY table_schema,table_name, constraint_name;
233 constraint_schema constraint_name table_schema table_name constraint_type
234 db_datadict my_idx db_datadict t1_my_tablex UNIQUE
235 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
236 DROP INDEX my_idx ON db_datadict.t1_my_tablex;
237 SELECT constraint_schema, constraint_name, table_schema,
238 table_name, constraint_type
239 FROM information_schema.table_constraints
240 WHERE table_name = 't1_my_tablex'
241 ORDER BY table_schema,table_name, constraint_name;
242 constraint_schema constraint_name table_schema table_name constraint_type
243 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
244 ALTER TABLE db_datadict.t1_my_tablex ADD UNIQUE my_idx (f2);
245 SELECT constraint_schema, constraint_name, table_schema,
246 table_name, constraint_type
247 FROM information_schema.table_constraints
248 WHERE table_name = 't1_my_tablex'
249 ORDER BY table_schema,table_name, constraint_name;
250 constraint_schema constraint_name table_schema table_name constraint_type
251 db_datadict my_idx db_datadict t1_my_tablex UNIQUE
252 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
253 SELECT constraint_schema, constraint_name, table_schema,
254 table_name, constraint_type
255 FROM information_schema.table_constraints
256 WHERE table_name = 't1_my_tablex'
257 ORDER BY table_schema,table_name, constraint_name;
258 constraint_schema constraint_name table_schema table_name constraint_type
259 db_datadict my_idx db_datadict t1_my_tablex UNIQUE
260 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
261 ALTER TABLE db_datadict.t1_my_tablex
262 DROP COLUMN f2;
263 SELECT constraint_schema, constraint_name, table_schema,
264 table_name, constraint_type
265 FROM information_schema.table_constraints
266 WHERE table_name = 't1_my_tablex'
267 ORDER BY table_schema,table_name, constraint_name;
268 constraint_schema constraint_name table_schema table_name constraint_type
269 db_datadict PRIMARY db_datadict t1_my_tablex PRIMARY KEY
270 SELECT table_name
271 FROM information_schema.table_constraints
272 WHERE table_name = 't1_my_tablex';
273 table_name
274 t1_my_tablex
275 DROP TABLE db_datadict.t1_my_tablex;
276 SELECT table_name
277 FROM information_schema.table_constraints
278 WHERE table_name = 't1_my_tablex';
279 table_name
280 SELECT table_name FROM information_schema.table_constraints
281 WHERE table_name = 't1_my_tablex';
282 table_name
283 CREATE TABLE db_datadict.t1_my_tablex
284 ENGINE = <engine_type> AS
285 SELECT 1 AS f1;
286 SELECT table_name FROM information_schema.table_constraints
287 WHERE table_name = 't1_my_tablex';
288 table_name
289 ALTER TABLE db_datadict.t1_my_tablex ADD PRIMARY KEY(f1);
290 SELECT table_name FROM information_schema.table_constraints
291 WHERE table_name = 't1_my_tablex';
292 table_name
293 t1_my_tablex
294 SELECT table_name FROM information_schema.table_constraints
295 WHERE table_name = 't1_my_tablex';
296 table_name
297 t1_my_tablex
298 DROP DATABASE db_datadict;
299 SELECT table_name FROM information_schema.table_constraints
300 WHERE table_name = 't1_my_tablex';
301 table_name
302 ########################################################################
303 # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
304 # DDL on INFORMATION_SCHEMA tables are not supported
305 ########################################################################
306 CREATE DATABASE db_datadict;
307 CREATE TABLE db_datadict.t1 (f1 BIGINT, UNIQUE(f1))
308 ENGINE = <engine_type>;
309 INSERT INTO information_schema.table_constraints
310 (constraint_schema, constraint_name, table_name)
311 VALUES ( 'mysql', 'primary', 'db');
312 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
313 INSERT INTO information_schema.table_constraints
314 SELECT * FROM information_schema.table_constraints;
315 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
316 UPDATE information_schema.table_constraints
317 SET table_name = 'db1' WHERE constraint_name = 'primary';
318 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
319 DELETE FROM information_schema.table_constraints WHERE table_name = 't1';
320 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
321 TRUNCATE information_schema.table_constraints;
322 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
323 CREATE INDEX i3 ON information_schema.table_constraints(table_name);
324 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
325 ALTER TABLE information_schema.table_constraints ADD f1 INT;
326 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
327 DROP TABLE information_schema.table_constraints;
328 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
329 ALTER TABLE information_schema.table_constraints
330 RENAME db_datadict.table_constraints;
331 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
332 ALTER TABLE information_schema.table_constraints
333 RENAME information_schema.xtable_constraints;
334 ERROR 42000: Access denied for user 'root'@'localhost' to database 'information_schema'
335 DROP TABLE db_datadict.t1;
336 DROP DATABASE db_datadict;