Debian Codegraph @

mysql-test/suite/funcs_1/t/is_columns.test

raw
1 # suite/funcs_1/t/is_columns.test
2 #
3 # Check the layout of information_schema.columns and the impact of
4 # CREATE/ALTER/DROP TABLE/VIEW/SCHEMA/COLUMN ... on its content.
5 #
6 # Note:
7 # This test is not intended
8 # - to show information about the all time existing tables
9 # within the databases information_schema and mysql
10 # - for checking storage engine properties
11 # Therefore please do not alter $engine_type and $other_engine_type.
12 #
13 # Author:
14 # 2008-01-23 mleich WL#4203 Reorganize and fix the data dictionary tests of
15 # testsuite funcs_1
16 # Create this script based on older scripts and new code.
17 #
18
19 # This test cannot be used for the embedded server because we check here
20 # privileges.
21 --source include/not_embedded.inc
22 --source include/default_charset.inc
23
24 let $engine_type = MEMORY;
25 let $other_engine_type = MyISAM;
26
27 let $is_table = COLUMNS;
28
29 # The table INFORMATION_SCHEMA.COLUMNS must exist
30 eval SHOW TABLES FROM information_schema LIKE '$is_table';
31
32 --echo #######################################################################
33 --echo # Testcase 3.2.1.1: INFORMATION_SCHEMA tables can be queried via SELECT
34 --echo #######################################################################
35 # Ensure that every INFORMATION_SCHEMA table can be queried with a SELECT
36 # statement, just as if it were an ordinary user-defined table.
37 #
38 --source suite/funcs_1/datadict/is_table_query.inc
39
40
41 --echo #########################################################################
42 --echo # Testcase 3.2.6.1: INFORMATION_SCHEMA.COLUMNS layout
43 --echo #########################################################################
44 # Ensure that the INFORMATION_SCHEMA.COLUMNS table has the following columns,
45 # in the following order:
46 #
47 # TABLE_CATALOG (always shows NULL),
48 # TABLE_SCHEMA (shows the name of the database, or schema, in which an
49 # accessible table resides),
50 # TABLE_NAME (shows the name of an accessible table),
51 # COLUMN_NAME (shows the name of a column within that table),
52 # ORDINAL_POSITION (shows the ordinal position of that column in that table),
53 # COLUMN_DEFAULT (shows the column's default value),
54 # IS_NULLABLE (shows whether the column may accept NULL values),
55 # DATA_TYPE (shows the column's defined data type; keyword only),
56 # CHARACTER_MAXIMUM_LENGTH (shows, for a string column, the column's defined
57 # maximum length in characters; otherwise NULL),
58 # CHARACTER_OCTET_LENGTH (shows, for a string column, the column's defined
59 # maximum length in octets; otherwise NULL),
60 # NUMERIC_PRECISION (shows, for a numeric column, the column's or data type's
61 # defined precision; otherwise NULL),
62 # NUMERIC_SCALE (shows, for a numeric column, the column's or data type's
63 # defined scale; otherwise NULL),
64 # CHARACTER_SET_NAME (shows, for a character string column, the column's default
65 # character set; otherwise NULL),
66 # COLLATION_NAME (shows, for a character string column, the column's default
67 # collation; otherwise NULL),
68 # COLUMN_TYPE (shows the column's complete, defined data type),
69 # COLUMN_KEY (shows whether the column is indexed; possible values are PRI if
70 # the column is part of a PRIMARY KEY, UNI if the column is part of a
71 # UNIQUE key, MUL if the column is part of an index key that allows
72 # duplicates),
73 # EXTRA (shows any additional column definition information, e.g. whether the
74 # column was defined with the AUTO_INCREMENT attribute),
75 # PRIVILEGES (shows the privileges available to the user on the column),
76 # COLUMN_COMMENT (shows the comment, if any, defined for the comment;
77 # otherwise NULL).
78 #
79 --source suite/funcs_1/datadict/datadict_bug_12777.inc
80 eval DESCRIBE information_schema.$is_table;
81 --source suite/funcs_1/datadict/datadict_bug_12777.inc
82 --replace_result ENGINE=MyISAM "" ENGINE=Aria "" " PAGE_CHECKSUM=1" "" " PAGE_CHECKSUM=0" ""
83 eval SHOW CREATE TABLE information_schema.$is_table;
84 --source suite/funcs_1/datadict/datadict_bug_12777.inc
85 eval SHOW COLUMNS FROM information_schema.$is_table;
86
87 # Note: Retrieval of information within information_schema.columns about
88 # information_schema.columns is in is_columns_is.test.
89
90 # Show that TABLE_CATALOG is always 'def'.
91 SELECT table_catalog, table_schema, table_name, column_name
92 FROM information_schema.columns WHERE table_catalog IS NULL OR table_catalog <> 'def';
93
94
95 --echo ###############################################################################
96 --echo # Testcase 3.2.6.2 + 3.2.6.3: INFORMATION_SCHEMA.COLUMNS accessible information
97 --echo ###############################################################################
98 # 3.2.6.2: Ensure that the table shows the relevant information on the columns
99 # of every table that is accessible to the current user or to PUBLIC.
100 # 3.2.6.3: Ensure that the table does not show any information on the columns
101 # of any table which is not accessible to the current user or PUBLIC.
102 #
103 # Note: Check of content within information_schema.columns about
104 # databases is in
105 # mysql is_columns_mysql.test
106 # information_schema is_columns_is.test
107 # test% is_columns_<engine>.test
108 #
109 --disable_warnings
110 DROP DATABASE IF EXISTS db_datadict;
111 --enable_warnings
112 CREATE DATABASE db_datadict;
113
114 --error 0,ER_CANNOT_USER
115 DROP USER 'testuser1'@'localhost';
116 CREATE USER 'testuser1'@'localhost';
117 --error 0,ER_CANNOT_USER
118 DROP USER 'testuser2'@'localhost';
119 CREATE USER 'testuser2'@'localhost';
120
121 --replace_result $other_engine_type <other_engine_type>
122 eval
123 CREATE TABLE db_datadict.t1
124 (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT AUTO_INCREMENT,
125 UNIQUE INDEX MUL_IDX(f1,f3), PRIMARY KEY (f4))
126 ENGINE = $other_engine_type;
127 CREATE VIEW db_datadict.v1 AS SELECT 1 AS f1, 1 AS f2;
128 GRANT SELECT(f1, f2) ON db_datadict.t1 TO 'testuser1'@'localhost';
129 GRANT SELECT(f2) ON db_datadict.v1 TO 'testuser1'@'localhost';
130
131 --replace_result $other_engine_type <other_engine_type>
132 eval
133 CREATE TABLE db_datadict.t2
134 (f1 CHAR(10), f2 TEXT, f3 DATE, f4 INT, PRIMARY KEY (f1,f4))
135 ENGINE = $other_engine_type;
136 GRANT INSERT(f1, f2) ON db_datadict.t2 TO 'testuser2'@'localhost';
137
138 let $my_select= SELECT * FROM information_schema.columns
139 WHERE table_schema = 'db_datadict'
140 ORDER BY table_schema, table_name, ordinal_position;
141 let $my_show1 = SHOW COLUMNS FROM db_datadict.t1;
142 let $my_show2 = SHOW COLUMNS FROM db_datadict.t2;
143 let $my_show3 = SHOW COLUMNS FROM db_datadict.v1;
144
145 # Point of view of user root.
146 --source suite/funcs_1/datadict/datadict_bug_12777.inc
147 eval $my_select;
148 eval $my_show1;
149 eval $my_show2;
150 eval $my_show3;
151
152 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
153 connect (testuser1, localhost, testuser1, , db_datadict);
154 --source suite/funcs_1/datadict/datadict_bug_12777.inc
155 eval $my_select;
156 eval $my_show1;
157 --error ER_TABLEACCESS_DENIED_ERROR
158 eval $my_show2;
159 eval $my_show3;
160
161 --replace_result $MASTER_MYPORT MYSQL_PORT $MASTER_MYSOCK MYSQL_SOCK
162 connect (testuser2, localhost, testuser2, , db_datadict);
163 --source suite/funcs_1/datadict/datadict_bug_12777.inc
164 eval $my_select;
165 --error ER_TABLEACCESS_DENIED_ERROR
166 eval $my_show1;
167 eval $my_show2;
168 --error ER_TABLEACCESS_DENIED_ERROR
169 eval $my_show3;
170
171 connection default;
172 disconnect testuser1;
173 disconnect testuser2;
174
175 # Cleanup
176 DROP USER 'testuser1'@'localhost';
177 DROP USER 'testuser2'@'localhost';
178 DROP DATABASE IF EXISTS db_datadict;
179
180
181 --echo ###############################################################################
182 --echo # Testcase 3.2.1.13+3.2.1.14+3.2.1.15: INFORMATION_SCHEMA.COLUMNS modifications
183 --echo ###############################################################################
184 # 3.2.1.13: Ensure that the creation of any new database object (e.g. table or
185 # column) automatically inserts all relevant information on that
186 # object into every appropriate INFORMATION_SCHEMA table.
187 # 3.2.1.14: Ensure that the alteration of any existing database object
188 # automatically updates all relevant information on that object in
189 # every appropriate INFORMATION_SCHEMA table.
190 # 3.2.1.15: Ensure that the dropping of any existing database object
191 # automatically deletes all relevant information on that object from
192 # every appropriate INFORMATION_SCHEMA table.
193 #
194 --disable_warnings
195 DROP TABLE IF EXISTS test.t1_my_table;
196 DROP DATABASE IF EXISTS db_datadict;
197 --enable_warnings
198 CREATE DATABASE db_datadict;
199
200 SELECT table_name FROM information_schema.columns
201 WHERE table_name LIKE 't1_my_table%';
202 --replace_result $engine_type <engine_type>
203 eval
204 CREATE TABLE test.t1_my_table (f1 CHAR(12))
205 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci
206 ENGINE = $engine_type;
207 # Settings used in CREATE TABLE must be visible in information_schema.columns.
208 --vertical_results
209 SELECT * FROM information_schema.columns
210 WHERE table_name = 't1_my_table';
211 --horizontal_results
212 #
213 # Check modification of TABLE_NAME
214 SELECT table_name FROM information_schema.columns
215 WHERE table_name LIKE 't1_my_table%';
216 RENAME TABLE test.t1_my_table TO test.t1_my_tablex;
217 SELECT table_name FROM information_schema.columns
218 WHERE table_name LIKE 't1_my_table%';
219 #
220 # Check modification of TABLE_SCHEMA
221 SELECT table_schema,table_name FROM information_schema.columns
222 WHERE table_name = 't1_my_tablex';
223 RENAME TABLE test.t1_my_tablex TO db_datadict.t1_my_tablex;
224 SELECT table_schema,table_name FROM information_schema.columns
225 WHERE table_name = 't1_my_tablex';
226 #
227 # Check modification of COLUMN_NAME
228 SELECT table_name, column_name FROM information_schema.columns
229 WHERE table_name = 't1_my_tablex';
230 ALTER TABLE db_datadict.t1_my_tablex CHANGE COLUMN f1 first_col CHAR(12);
231 SELECT table_name, column_name FROM information_schema.columns
232 WHERE table_name = 't1_my_tablex';
233 #
234 # Check modification of COLUMN size
235 SELECT table_name, column_name, character_maximum_length,
236 character_octet_length, column_type
237 FROM information_schema.columns
238 WHERE table_name = 't1_my_tablex';
239 ALTER TABLE db_datadict.t1_my_tablex
240 MODIFY COLUMN first_col CHAR(20);
241 SELECT table_name, column_name, character_maximum_length,
242 character_octet_length, column_type
243 FROM information_schema.columns
244 WHERE table_name = 't1_my_tablex';
245 #
246 # Check modification of COLUMN type
247 SELECT table_name, column_name, character_maximum_length,
248 character_octet_length, column_type
249 FROM information_schema.columns
250 WHERE table_name = 't1_my_tablex';
251 ALTER TABLE db_datadict.t1_my_tablex
252 MODIFY COLUMN first_col VARCHAR(20);
253 SELECT table_name, column_name, character_maximum_length,
254 character_octet_length, column_type
255 FROM information_schema.columns
256 WHERE table_name = 't1_my_tablex';
257 #
258 # Check modify COLUMN DEFAULT
259 SELECT table_name, column_name, column_default
260 FROM information_schema.columns
261 WHERE table_name = 't1_my_tablex';
262 ALTER TABLE db_datadict.t1_my_tablex
263 MODIFY COLUMN first_col CHAR(10) DEFAULT 'hello';
264 SELECT table_name, column_name, column_default
265 FROM information_schema.columns
266 WHERE table_name = 't1_my_tablex';
267 #
268 # Check modify IS_NULLABLE
269 SELECT table_name, column_name, is_nullable
270 FROM information_schema.columns
271 WHERE table_name = 't1_my_tablex';
272 ALTER TABLE db_datadict.t1_my_tablex
273 MODIFY COLUMN first_col CHAR(10) NOT NULL;
274 SELECT table_name, column_name, is_nullable
275 FROM information_schema.columns
276 WHERE table_name = 't1_my_tablex';
277 #
278 # Check modify COLLATION
279 SELECT table_name, column_name, collation_name
280 FROM information_schema.columns
281 WHERE table_name = 't1_my_tablex';
282 ALTER TABLE db_datadict.t1_my_tablex
283 MODIFY COLUMN first_col CHAR(10) COLLATE 'latin1_general_cs';
284 SELECT table_name, column_name, collation_name
285 FROM information_schema.columns
286 WHERE table_name = 't1_my_tablex';
287 #
288 # Check modify CHARACTER SET
289 SELECT table_name, column_name, character_maximum_length,
290 character_octet_length, character_set_name
291 FROM information_schema.columns
292 WHERE table_name = 't1_my_tablex';
293 ALTER TABLE db_datadict.t1_my_tablex
294 MODIFY COLUMN first_col CHAR(10) CHARACTER SET utf8;
295 SELECT table_name, column_name, character_maximum_length,
296 character_octet_length, character_set_name
297 FROM information_schema.columns
298 WHERE table_name = 't1_my_tablex';
299 #
300 # Check modify COLUMN_COMMENT
301 SELECT table_name, column_name, column_comment
302 FROM information_schema.columns
303 WHERE table_name = 't1_my_tablex';
304 ALTER TABLE db_datadict.t1_my_tablex
305 MODIFY COLUMN first_col CHAR(10) COMMENT 'Hello';
306 SELECT table_name, column_name, column_comment
307 FROM information_schema.columns
308 WHERE table_name = 't1_my_tablex';
309 #
310 # Check ADD COLUMN
311 SELECT table_name, column_name
312 FROM information_schema.columns
313 WHERE table_name = 't1_my_tablex';
314 ALTER TABLE db_datadict.t1_my_tablex
315 ADD COLUMN second_col CHAR(10);
316 SELECT table_name, column_name
317 FROM information_schema.columns
318 WHERE table_name = 't1_my_tablex';
319 #
320 # Check switch ordinal position of column
321 SELECT table_name, column_name, ordinal_position
322 FROM information_schema.columns
323 WHERE table_name = 't1_my_tablex'
324 ORDER BY table_name, column_name;
325 ALTER TABLE db_datadict.t1_my_tablex
326 MODIFY COLUMN second_col CHAR(10) FIRST;
327 SELECT table_name, column_name, ordinal_position
328 FROM information_schema.columns
329 WHERE table_name = 't1_my_tablex'
330 ORDER BY table_name, column_name;
331 #
332 # Check DROP COLUMN
333 SELECT table_name, column_name
334 FROM information_schema.columns
335 WHERE table_name = 't1_my_tablex';
336 ALTER TABLE db_datadict.t1_my_tablex
337 DROP COLUMN first_col;
338 SELECT table_name, column_name
339 FROM information_schema.columns
340 WHERE table_name = 't1_my_tablex';
341 #
342 # Check set COLUMN UNIQUE
343 SELECT table_name, column_name, column_key
344 FROM information_schema.columns
345 WHERE table_name = 't1_my_tablex';
346 ALTER TABLE db_datadict.t1_my_tablex
347 ADD UNIQUE INDEX IDX(second_col);
348 SELECT table_name, column_name, column_key
349 FROM information_schema.columns
350 WHERE table_name = 't1_my_tablex';
351 #
352 # Check impact of DROP TABLE
353 SELECT table_name, column_name
354 FROM information_schema.columns
355 WHERE table_name = 't1_my_tablex';
356 DROP TABLE db_datadict.t1_my_tablex;
357 SELECT table_name, column_name
358 FROM information_schema.columns
359 WHERE table_name = 't1_my_tablex';
360 #
361 # Check a VIEW
362 CREATE VIEW test.t1_my_tablex
363 AS SELECT 1 AS "col1", 'A' collate latin1_german1_ci AS "col2";
364 --vertical_results
365 SELECT * FROM information_schema.columns
366 WHERE table_name = 't1_my_tablex'
367 ORDER BY table_name, column_name;
368 --horizontal_results
369 DROP VIEW test.t1_my_tablex;
370 SELECT table_name FROM information_schema.columns
371 WHERE table_name = 't1_my_tablex';
372 #
373 # Check impact of DROP SCHEMA
374 --replace_result $engine_type <engine_type>
375 eval
376 CREATE TABLE db_datadict.t1_my_tablex
377 ENGINE = $engine_type AS
378 SELECT 1;
379 SELECT table_name FROM information_schema.columns
380 WHERE table_name = 't1_my_tablex';
381 DROP DATABASE db_datadict;
382 SELECT table_name FROM information_schema.columns
383 WHERE table_name = 't1_my_tablex';
384
385
386 --echo ########################################################################
387 --echo # Testcases 3.2.1.3-3.2.1.5 + 3.2.1.8-3.2.1.12: INSERT/UPDATE/DELETE and
388 --echo # DDL on INFORMATION_SCHEMA table are not supported
389 --echo ########################################################################
390 # 3.2.1.3: Ensure that no user may execute an INSERT statement on any
391 # INFORMATION_SCHEMA table.
392 # 3.2.1.4: Ensure that no user may execute an UPDATE statement on any
393 # INFORMATION_SCHEMA table.
394 # 3.2.1.5: Ensure that no user may execute a DELETE statement on any
395 # INFORMATION_SCHEMA table.
396 # 3.2.1.8: Ensure that no user may create an index on an
397 # INFORMATION_SCHEMA table.
398 # 3.2.1.9: Ensure that no user may alter the definition of an
399 # INFORMATION_SCHEMA table.
400 # 3.2.1.10: Ensure that no user may drop an INFORMATION_SCHEMA table.
401 # 3.2.1.11: Ensure that no user may move an INFORMATION_SCHEMA table to any
402 # other database.
403 # 3.2.1.12: Ensure that no user may directly add to, alter, or delete any data
404 # in an INFORMATION_SCHEMA table.
405 #
406 --disable_warnings
407 DROP DATABASE IF EXISTS db_datadict;
408 DROP TABLE IF EXISTS test.t1;
409 --enable_warnings
410 CREATE DATABASE db_datadict;
411 CREATE TABLE test.t1 (f1 BIGINT);
412
413 --error ER_DBACCESS_DENIED_ERROR
414 INSERT INTO information_schema.columns (table_schema,table_name,column_name)
415 VALUES('test','t1', 'f2');
416 --error ER_DBACCESS_DENIED_ERROR
417 INSERT INTO information_schema.columns (table_schema,table_name,column_name)
418 VALUES('test','t2', 'f1');
419
420 --error ER_DBACCESS_DENIED_ERROR
421 UPDATE information_schema.columns SET table_name = 't4' WHERE table_name = 't1';
422
423 --error ER_DBACCESS_DENIED_ERROR
424 DELETE FROM information_schema.columns WHERE table_name = 't1';
425 --error ER_DBACCESS_DENIED_ERROR
426 TRUNCATE information_schema.columns;
427
428 --error ER_DBACCESS_DENIED_ERROR
429 CREATE INDEX i3 ON information_schema.columns(table_name);
430
431 --error ER_DBACCESS_DENIED_ERROR
432 ALTER TABLE information_schema.columns ADD f1 INT;
433
434 --error ER_DBACCESS_DENIED_ERROR
435 DROP TABLE information_schema.columns;
436
437 --error ER_DBACCESS_DENIED_ERROR
438 ALTER TABLE information_schema.columns RENAME db_datadict.columns;
439 --error ER_DBACCESS_DENIED_ERROR
440 ALTER TABLE information_schema.columns RENAME information_schema.xcolumns;
441
442 # Cleanup
443 DROP TABLE test.t1;
444 DROP DATABASE db_datadict;
445