Debian Codegraph @

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