Debian Codegraph @

mysql-test/suite/information_schema/t/information_schema_db.test

raw
1
2 #Don't run this test when thread_pool active
3 --source include/not_threadpool.inc
4
5 -- source include/testdb_only.inc
6
7 set @orig_sql_mode= @@sql_mode;
8
9 --disable_warnings
10 drop table if exists t1,t2;
11 drop view if exists v1,v2;
12 drop function if exists f1;
13 drop function if exists f2;
14 --enable_warnings
15
16 use INFORMATION_SCHEMA;
17 sorted_result;
18 --replace_result Tables_in_INFORMATION_SCHEMA Tables_in_information_schema
19 show tables where Tables_in_INFORMATION_SCHEMA NOT LIKE 'INNODB%' and Tables_in_INFORMATION_SCHEMA NOT LIKE 'ndb%';
20 sorted_result;
21 --replace_result 'Tables_in_INFORMATION_SCHEMA (T%)' 'Tables_in_information_schema (T%)'
22 show tables from INFORMATION_SCHEMA like 'T%';
23 create database `inf%`;
24 create database mbase;
25 use `inf%`;
26 show tables;
27
28 #
29 # Bug#18113 SELECT * FROM information_schema.xxx crashes server
30 # Bug#17204 second CALL to procedure crashes Server
31 # Crash happened when one selected data from one of INFORMATION_SCHEMA
32 # tables and in order to build its contents server had to open view which
33 # used stored function and table or view on which one had not global or
34 # database-level privileges (e.g. had only table-level or had no
35 # privileges at all).
36 #
37 create user 'mysqltest_1'@'localhost';
38 grant all privileges on `inf%`.* to 'mysqltest_1'@'localhost';
39 grant all privileges on `mbase`.* to 'mysqltest_1'@'localhost';
40 create table t1 (f1 int);
41 delimiter |;
42 create function func1(curr_int int) returns int
43 begin
44 declare ret_val int;
45 select max(f1) from t1 into ret_val;
46 return ret_val;
47 end|
48 delimiter ;|
49 create view v1 as select f1 from t1 where f1 = func1(f1);
50 create function func2() returns int return 1;
51
52 use mbase;
53 delimiter |;
54 create procedure p1 ()
55 begin
56 select table_name from information_schema.key_column_usage
57 order by table_name;
58 end|
59 delimiter ;|
60
61 create table t1
62 (f1 int(10) unsigned not null,
63 f2 varchar(100) not null,
64 primary key (f1), unique key (f2));
65
66 connect (user1,localhost,mysqltest_1,,);
67 connection user1;
68 --disable_result_log
69 select * from information_schema.tables;
70 call mbase.p1();
71 call mbase.p1();
72 call mbase.p1();
73 --enable_result_log
74
75 connection default;
76 use `inf%`;
77 drop user mysqltest_1@localhost;
78 drop table t1;
79 select table_name, table_type, table_comment from information_schema.tables
80 where table_schema='inf%' and func2();
81 select table_name, table_type, table_comment from information_schema.tables
82 where table_schema='inf%' and func2();
83 drop view v1;
84 drop function func1;
85 drop function func2;
86
87 drop database `inf%`;
88 drop procedure mbase.p1;
89 drop database mbase;
90 disconnect user1;
91
92 #
93 # Bug#18282 INFORMATION_SCHEMA.TABLES provides inconsistent info about invalid views
94 #
95 use test;
96 create table t1 (i int);
97 create function f1 () returns int return (select max(i) from t1);
98 create view v1 as select f1();
99 create table t2 (id int);
100 create function f2 () returns int return (select max(i) from t2);
101 create view v2 as select f2();
102 drop table t2;
103 select table_name, table_type, table_comment from information_schema.tables
104 where table_schema='test';
105 drop table t1;
106 select table_name, table_type, table_comment from information_schema.tables
107 where table_schema='test';
108 drop function f1;
109 drop function f2;
110 drop view v1, v2;
111
112 #
113 # Bug#20543 select on information_schema strange warnings, view, different
114 # schemas/users
115 #
116 #
117 create database testdb_1;
118 create user testdb_1@localhost;
119 grant all on testdb_1.* to testdb_1@localhost with grant option;
120
121 create user testdb_2@localhost;
122 grant all on test.* to testdb_2@localhost with grant option;
123
124 connect (testdb_1,localhost,testdb_1,,test);
125 use testdb_1;
126 create table t1 (f1 char(4));
127 create view v1 as select f1 from t1;
128 grant insert on v1 to testdb_2@localhost;
129
130 create view v5 as select f1 from t1;
131 grant select, show view on v5 to testdb_2@localhost;
132
133 --error ER_SPECIFIC_ACCESS_DENIED_ERROR
134 create definer=`no_such_user`@`no_such_host` view v6 as select f1 from t1;
135
136 connection default;
137 use testdb_1;
138 create view v6 as select f1 from t1;
139 grant select, show view on v6 to testdb_2@localhost;
140
141 create table t2 (f1 char(4));
142 create definer=`no_such_user`@`no_such_host` view v7 as select * from t2;
143
144 show fields from testdb_1.v6;
145 show create view testdb_1.v6;
146
147 show create view testdb_1.v7;
148 show fields from testdb_1.v7;
149
150 connection testdb_1;
151
152 create table t3 (f1 char(4), f2 char(4));
153 create view v3 as select f1,f2 from t3;
154 grant insert(f1), insert(f2) on v3 to testdb_2@localhost;
155
156 connect (testdb_2,localhost,testdb_2,,test);
157 create view v2 as select f1 from testdb_1.v1;
158 create view v4 as select f1,f2 from testdb_1.v3;
159
160 show fields from testdb_1.v5;
161 show create view testdb_1.v5;
162
163 show fields from testdb_1.v6;
164 show create view testdb_1.v6;
165
166 connection testdb_1;
167 show fields from testdb_1.v7;
168 show create view testdb_1.v7;
169
170 revoke insert(f1) on v3 from testdb_2@localhost;
171 revoke select,show view on v5 from testdb_2@localhost;
172 connection default;
173 use testdb_1;
174 revoke select,show view on v6 from testdb_2@localhost;
175 connection testdb_2;
176
177 --error ER_TABLEACCESS_DENIED_ERROR
178 show fields from testdb_1.v5;
179 --error ER_TABLEACCESS_DENIED_ERROR
180 show create view testdb_1.v5;
181
182 --error ER_TABLEACCESS_DENIED_ERROR
183 show fields from testdb_1.v6;
184 --error ER_TABLEACCESS_DENIED_ERROR
185 show create view testdb_1.v6;
186
187 --error ER_TABLEACCESS_DENIED_ERROR
188 show fields from testdb_1.v7;
189 --error ER_TABLEACCESS_DENIED_ERROR
190 show create view testdb_1.v7;
191
192 show create view v4;
193 #--error ER_VIEW_NO_EXPLAIN
194 show fields from v4;
195
196 show fields from v2;
197 show fields from testdb_1.v1;
198 show create view v2;
199 --error ER_TABLEACCESS_DENIED_ERROR
200 show create view testdb_1.v1;
201
202 select table_name from information_schema.columns a
203 where a.table_name = 'v2';
204 select view_definition from information_schema.views a
205 where a.table_name = 'v2';
206 select view_definition from information_schema.views a
207 where a.table_name = 'testdb_1.v1';
208
209 --error ER_VIEW_INVALID
210 select * from v2;
211
212 connection default;
213 use test;
214 drop view testdb_1.v1, v2, testdb_1.v3, v4;
215 drop database testdb_1;
216 connection testdb_1;
217 disconnect testdb_1;
218 --source include/wait_until_disconnected.inc
219 connection testdb_2;
220 disconnect testdb_2;
221 --source include/wait_until_disconnected.inc
222 connection default;
223 drop user testdb_1@localhost;
224 drop user testdb_2@localhost;
225
226 #
227 # Bug#22763 Disrepancy between SHOW CREATE VIEW and I_S.VIEWS
228 #
229 create database testdb_1;
230 create table testdb_1.t1 (a int);
231 create view testdb_1.v1 as select * from testdb_1.t1;
232
233 create user mysqltest_1@localhost;
234 grant show view on testdb_1.* to mysqltest_1@localhost;
235 grant select on testdb_1.v1 to mysqltest_1@localhost;
236
237 connect (user1,localhost,mysqltest_1,,test);
238 connection user1;
239 select table_schema, table_name, view_definition from information_schema.views
240 where table_name='v1';
241 show create view testdb_1.v1;
242
243 connection default;
244 revoke select on testdb_1.v1 from mysqltest_1@localhost;
245 connection user1;
246 select table_schema, table_name, view_definition from information_schema.views
247 where table_name='v1';
248 --error ER_TABLEACCESS_DENIED_ERROR
249 show create view testdb_1.v1;
250
251 connection default;
252 drop user mysqltest_1@localhost;
253 drop database testdb_1;
254 set sql_mode= @orig_sql_mode;
255 connection user1;
256 disconnect user1;
257 --source include/wait_until_disconnected.inc
258 connection default;
259
260 --echo #
261 --echo # Bug#30158484 FULLY QUALIFIED COLUMN NAMES IN WHERE CLAUSE ARE
262 --echo # FAILING WITH INFORMATION_SCHEMA TABLES IN 8.0
263
264 --echo #
265 --echo # Case 1: INFORMATION_SCHEMA name in SELECT clause with mixed case.
266 --echo #
267 --echo # These commands fail with ER_BAD_FIELD_ERROR without the fix.
268 SELECT INFORMATION_schema.TABLES.TABLE_NAME,
269 information_SCHEMA.TABLES.TABLE_NAME,
270 INFORMATION_SCHEMA.TABLES.TABLE_NAME,
271 information_schema.TABLES.TABLE_NAME
272 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='non_existing_table';
273
274 SELECT INFORMATION_schema.TABLES.TABLE_NAME,
275 information_SCHEMA.TABLES.TABLE_NAME,
276 INFORMATION_SCHEMA.TABLES.TABLE_NAME,
277 information_schema.TABLES.TABLE_NAME
278 FROM information_schema.TABLES WHERE TABLE_NAME='non_existing_table';
279
280 SELECT INFORMATION_schema.TABLES.TABLE_NAME,
281 information_SCHEMA.TABLES.TABLE_NAME,
282 INFORMATION_SCHEMA.TABLES.TABLE_NAME,
283 information_schema.TABLES.TABLE_NAME
284 FROM information_SCHEMA.TABLES WHERE TABLE_NAME='non_existing_table';
285
286 --echo #
287 --echo # Case 2: INFORMATION_SCHEMA name used to specify '*' in SELECT
288 --echo # clause with mixed case.
289 --echo #
290 SELECT INFORMATION_SCHEMA.TABLES.*
291 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='non_existing_table';
292
293 SELECT information_schema.TABLES.*
294 FROM information_schema.TABLES WHERE TABLE_NAME='non_existing_table';
295
296 SELECT INFORMATION_SCHEMA.TABLES.*
297 FROM information_schema.TABLES WHERE TABLE_NAME='non_existing_table';
298
299 SELECT INFORMATION_schema.TABLES.*
300 FROM information_SCHEMA.TABLES WHERE TABLE_NAME='non_existing_table';
301
302 --echo #
303 --echo # Case 3: INFORMATION_SCHEMA name in WHERE clause with mixed case.
304 --echo #
305 SELECT COUNT(*) > 1 FROM INFORMATION_SCHEMA.TABLES
306 WHERE INFORMATION_schema.TABLES.TABLE_NAME LIKE 'a%' AND
307 information_SCHEMA.TABLES.TABLE_NAME LIKE 'b%' AND
308 information_schema.TABLES.TABLE_NAME LIKE 'c%' AND
309 INFORMATION_SCHEMA.TABLES.TABLE_NAME LIKE 'd%';
310
311 SELECT COUNT(*) > 1 FROM information_schema.TABLES
312 WHERE INFORMATION_schema.TABLES.TABLE_NAME LIKE 'a%' AND
313 information_SCHEMA.TABLES.TABLE_NAME LIKE 'b%' AND
314 information_schema.TABLES.TABLE_NAME LIKE 'c%' AND
315 INFORMATION_SCHEMA.TABLES.TABLE_NAME LIKE 'd%';
316
317 SELECT COUNT(*) > 1 FROM INFORMATION_schema.TABLES
318 WHERE INFORMATION_schema.TABLES.TABLE_NAME LIKE 'a%' AND
319 information_SCHEMA.TABLES.TABLE_NAME LIKE 'b%' AND
320 information_schema.TABLES.TABLE_NAME LIKE 'c%' AND
321 INFORMATION_SCHEMA.TABLES.TABLE_NAME LIKE 'd%';
322
323 --echo #
324 --echo # Case 4: INFORMATION_SCHEMA name in ORDER BY clause with mixed case.
325 --echo #
326 SELECT COUNT(*) > 1 FROM INFORMATION_SCHEMA.TABLES
327 ORDER BY INFORMATION_schema.TABLES.TABLE_NAME,
328 information_SCHEMA.TABLES.TABLE_NAME,
329 information_schema.TABLES.TABLE_NAME,
330 INFORMATION_SCHEMA.TABLES.TABLE_NAME;
331
332 SELECT COUNT(*) > 1 FROM information_schema.TABLES
333 ORDER BY INFORMATION_schema.TABLES.TABLE_NAME,
334 information_SCHEMA.TABLES.TABLE_NAME,
335 information_schema.TABLES.TABLE_NAME,
336 INFORMATION_SCHEMA.TABLES.TABLE_NAME;
337
338 SELECT COUNT(*) > 1 FROM INFORMATION_schema.TABLES
339 ORDER BY INFORMATION_schema.TABLES.TABLE_NAME,
340 information_SCHEMA.TABLES.TABLE_NAME,
341 information_schema.TABLES.TABLE_NAME,
342 INFORMATION_SCHEMA.TABLES.TABLE_NAME;
343
344 --echo #
345 --echo # Case 5: INFORMATION_SCHEMA name in GROUP BY clause with mixed case.
346 --echo #
347 SELECT COUNT(*) > 1 FROM INFORMATION_SCHEMA.TABLES
348 GROUP BY INFORMATION_schema.TABLES.TABLE_NAME,
349 information_SCHEMA.TABLES.TABLE_NAME,
350 information_schema.TABLES.TABLE_NAME,
351 INFORMATION_SCHEMA.TABLES.TABLE_NAME LIMIT 1;
352
353 SELECT COUNT(*) > 1 FROM information_schema.TABLES
354 GROUP BY INFORMATION_schema.TABLES.TABLE_NAME,
355 information_SCHEMA.TABLES.TABLE_NAME,
356 information_schema.TABLES.TABLE_NAME,
357 INFORMATION_SCHEMA.TABLES.TABLE_NAME LIMIT 1;
358
359 SELECT COUNT(*) > 1 FROM INFORMATION_schema.TABLES
360 GROUP BY INFORMATION_schema.TABLES.TABLE_NAME,
361 information_SCHEMA.TABLES.TABLE_NAME,
362 information_schema.TABLES.TABLE_NAME,
363 INFORMATION_SCHEMA.TABLES.TABLE_NAME LIMIT 1;
364
365 --echo #
366 --echo # Case 6: Test behavior using INFORMATION_SCHEMA name, I_S table name
367 --echo # and I_S column name with mixed cases.
368 --echo #
369 # EXPECTATION:
370 # - INFORMATION_SCHEMA name is treated as case-insensitive, in any of SELECT
371 # clause.
372 #
373 # - I_S COLUMN name is treated as case-insensitive, in any of SELECT clause.
374 #
375 # - I_S table name is allowed with mixed case in FROM clause. However, the
376 # name used in the FROM clause should match the I_S table name used in
377 # other SELECT clause or else we get ER_BAD_FIELD_ERROR in setting
378 # lower_case_table_names=0. The I_S table name are treated as case
379 # insensitive in setting lower_case_table_names > 0.
380
381
382 --let $error_code= `SELECT IF(@@lower_case_table_names>0,'0','ER_BAD_FIELD_ERROR')`
383 --disable_result_log
384 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
385 WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = 'unknowdb';
386 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
387 WHERE INFORMATION_SCHEMA.SCHEMATA.schema_name = 'unknowdb';
388 --error $error_code
389 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
390 WHERE INFORMATION_SCHEMA.schemata.SCHEMA_NAME = 'unknowdb';
391 --error $error_code
392 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
393 WHERE INFORMATION_SCHEMA.schemata.schema_name = 'unknowdb';
394 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
395 WHERE information_schema.SCHEMATA.SCHEMA_NAME = 'unknowdb';
396 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
397 WHERE information_schema.SCHEMATA.schema_name = 'unknowdb';
398 --error $error_code
399 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
400 WHERE information_schema.schemata.SCHEMA_NAME = 'unknowdb';
401 --error $error_code
402 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
403 WHERE information_schema.schemata.schema_name = 'unknowdb';
404 --error $error_code
405 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata
406 WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = 'unknowdb';
407 --error $error_code
408 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata
409 WHERE INFORMATION_SCHEMA.SCHEMATA.schema_name = 'unknowdb';
410 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata
411 WHERE INFORMATION_SCHEMA.schemata.SCHEMA_NAME = 'unknowdb';
412 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata
413 WHERE INFORMATION_SCHEMA.schemata.schema_name = 'unknowdb';
414 --error $error_code
415 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata
416 WHERE information_schema.SCHEMATA.SCHEMA_NAME = 'unknowdb';
417 --error $error_code
418 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata
419 WHERE information_schema.SCHEMATA.schema_name = 'unknowdb';
420 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata
421 WHERE information_schema.schemata.SCHEMA_NAME = 'unknowdb';
422 SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.schemata
423 WHERE information_schema.schemata.schema_name = 'unknowdb';
424 SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
425 WHERE INFORMATION_SCHEMA.SCHEMATA.SCHEMA_NAME = 'unknowdb';
426 SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
427 WHERE INFORMATION_SCHEMA.SCHEMATA.schema_name = 'unknowdb';
428 --error $error_code
429 SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
430 WHERE INFORMATION_SCHEMA.schemata.SCHEMA_NAME = 'unknowdb';
431 --error $error_code
432 SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
433 WHERE INFORMATION_SCHEMA.schemata.schema_name = 'unknowdb';
434 SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
435 WHERE information_schema.SCHEMATA.SCHEMA_NAME = 'unknowdb';
436 SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
437 WHERE information_schema.SCHEMATA.schema_name = 'unknowdb';
438 --error $error_code
439 SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
440 WHERE information_schema.schemata.SCHEMA_NAME = 'unknowdb';
441 --error $error_code
442 SELECT SCHEMA_NAME FROM information_schema.SCHEMATA
443 WHERE information_schema.schemata.schema_name = 'unknowdb';
444 --enable_result_log