1
# Test for data_dictionary.schemas &
5
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
6
DROP SCHEMA IF EXISTS data_dictionary;
9
# Test for data_dictionary.tables &
12
create database mysqltest;
13
create table mysqltest.t1(a int, b VARCHAR(30), KEY string_data (b));
14
create table test.t2(a int);
15
create table t3(a int, KEY a_data (a));
16
create table mysqltest.t4(a int);
17
create table t5 (id int auto_increment primary key);
18
insert into t5 values (10);
20
select table_name from data_dictionary.tables
21
where table_schema = "mysqltest" and table_name like "t%";
23
select * from data_dictionary.indexes where TABLE_SCHEMA = "mysqltest";
25
show tables like 't%';
26
--replace_column 1 # 6 # 7 # 8 # 9 # 10 #
28
show columns from t3 like "a%";
29
select * from data_dictionary.columns where table_name="t1"
32
connect (user3,localhost,mysqltest_2,,);
34
select table_name, column_name from data_dictionary.columns
35
where table_schema = 'mysqltest' and table_name = 't1';
36
show columns from mysqltest.t1;
37
connect (user4,localhost,mysqltest_3,,mysqltest);
41
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
42
drop database mysqltest;
45
# Bug#2719 information_schema: errors in "columns"
47
select column_type from data_dictionary.columns
48
where table_schema="data_dictionary" and table_name="COLUMNS" and
49
(column_name="character_set_name" or column_name="collation_name");
52
# Bug#2718 information_schema: errors in "tables"
55
select count(*) from data_dictionary.tables where
56
table_schema="data_dictionary" and table_name="COLUMNS";
59
select count(*) from data_dictionary.tables
60
where table_schema="mysql" and table_name="user";
63
# Bug #7215 information_schema: columns are longtext instead of varchar
64
# Bug #7217 information_schema: columns are varbinary() instead of timestamp
66
select table_schema, table_name, column_name from data_dictionary.columns where data_type = 'longtext';
67
select table_name, column_name, data_type from data_dictionary.columns where data_type = 'datetime';
70
# Bug #8164 subquery with data_dictionary.COLUMNS, 100 % CPU
72
SELECT COUNT(*) FROM data_dictionary.tables A
74
(SELECT * FROM data_dictionary.columns B
75
WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
76
AND A.TABLE_NAME = B.TABLE_NAME);
79
# Bug #9344 INFORMATION_SCHEMA, wrong content, numeric columns
86
x_decimal DECIMAL(5,3),
87
x_numeric NUMERIC(5,3),
90
x_double_precision DOUBLE PRECISION );
91
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
92
FROM data_dictionary.columns
93
WHERE TABLE_NAME= 't1';
97
# Bug #9404 information_schema: Weird error messages
98
# with SELECT SUM() ... GROUP BY queries
100
SELECT table_schema, count(*) FROM data_dictionary.tables
101
WHERE table_name NOT LIKE 'ndb_%' AND
102
table_name NOT LIKE 'falcon%'
103
GROUP BY TABLE_SCHEMA ORDER BY table_schema;
106
# Bug #11057 information_schema: columns table has some questionable contents
107
# Bug #12301 information_schema: NUMERIC_SCALE must be 0 for integer columns
109
create table t1(f1 LONGBLOB, f2 LONGTEXT);
110
select column_name,data_type,CHARACTER_OCTET_LENGTH,
111
CHARACTER_MAXIMUM_LENGTH
112
from data_dictionary.columns
113
where table_name='t1';
115
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
116
f5 BIGINT, f6 int, f7 int);
117
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
118
from data_dictionary.columns
119
where table_name='t1';
123
# Bug#12518 COLUMN_DEFAULT has wrong value if NOT NULL is set
125
create table t1 (a int not null, b int);
126
select column_name, column_default from data_dictionary.columns
127
where table_schema='test' and table_name='t1';
129
show columns from t1;
134
# Bug#14089 FROM list subquery always fails when information_schema is current database
137
create table t1(id int);
138
insert into t1(id) values (1);
139
select 1 from (select 1 from test.t1) a;
141
select 1 from (select 1 from test.t1) a;
146
# Bug#14271 I_S: columns has no size for (var)binary columns
148
create table t1(f1 varbinary(32), f2 varbinary(64));
149
select character_maximum_length, character_octet_length
150
from data_dictionary.columns where table_name='t1';
154
## Bug#15307 GROUP_CONCAT() with ORDER BY returns empty set on information_schema
156
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
157
from data_dictionary.columns where
158
table_schema='data_dictionary' and
159
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
160
or column_type = 'varchar(27)')
161
group by column_type order by column_type, num;
164
# Bug#19236 bad COLUMNS.CHARACTER_MAXIMUM_LENGHT and CHARACTER_OCTET_LENGTH
166
create table t1(f1 char(1) not null, f2 char(9) not null);
167
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
168
data_dictionary.columns where table_schema='test' and table_name = 't1';
172
# Bug#19599 duplication of information_schema column value in a CONCAT expr with user var
175
create table t1(f1 char(5));
176
create table t2(f1 char(5));
177
select concat(@a, table_name), @a, table_name
178
from data_dictionary.tables where table_schema = 'test';
182
# Bug#18925: subqueries with MIN/MAX functions on INFORMARTION_SCHEMA
185
SELECT t.table_name, c1.column_name
186
FROM data_dictionary.tables t
188
data_dictionary.columns c1
189
ON t.table_schema = c1.table_schema AND
190
t.table_name = c1.table_name
191
WHERE t.table_schema = 'data_dictionary' AND
192
c1.ordinal_position =
193
( SELECT COALESCE(MIN(c2.ordinal_position),1)
194
FROM data_dictionary.columns c2
195
WHERE c2.table_schema = t.table_schema AND
196
c2.table_name = t.table_name AND
197
c2.column_name LIKE '%SCHEMA%'
199
AND t.table_name NOT LIKE 'falcon%'
200
ORDER BY t.table_name, c1.column_name;
202
SELECT t.table_name, c1.column_name
203
FROM data_dictionary.tables t
205
data_dictionary.columns c1
206
ON t.table_schema = c1.table_schema AND
207
t.table_name = c1.table_name
208
WHERE t.table_schema = 'data_dictionary' AND
209
c1.ordinal_position =
210
( SELECT COALESCE(MIN(c2.ordinal_position),1)
211
FROM data_dictionary.columns c2
212
WHERE c2.table_schema = 'data_dictionary' AND
213
c2.table_name = t.table_name AND
214
c2.column_name LIKE '%SCHEMA%'
216
AND t.table_name NOT LIKE 'falcon%'
217
ORDER BY t.table_name, c1.column_name;
220
# Bug#21231: query with a simple non-correlated subquery over
221
# INFORMARTION_SCHEMA.TABLES
224
SELECT MAX(table_name) FROM data_dictionary.tables;
225
SELECT table_name from data_dictionary.tables
226
WHERE table_name=(SELECT MAX(table_name)
227
FROM data_dictionary.tables)
230
# Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail
232
create table t1 (f1 int);
233
create table t2 (f1 int, f2 int);
235
#select table_name from data_dictionary.tables
236
#where table_schema = 'test' and table_name not in
237
#(select table_name from data_dictionary.columns
238
# where table_schema = 'test' and column_name = 'f3')
239
#ORDER BY table_name;
245
# Bug#24630 Subselect query crashes mysqld
247
select 1 as f1 from data_dictionary.tables where "CHARACTER_SETS"=
248
(select cast(table_name as char) from data_dictionary.tables
249
order by table_name limit 1) limit 1;
251
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
253
from data_dictionary.tables t
254
inner join data_dictionary.columns c1
255
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
256
where t.table_schema = 'data_dictionary' AND
257
t.table_name not like 'falcon%' AND
258
c1.ordinal_position =
259
(select isnull(c2.column_type) -
260
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
262
from data_dictionary.columns c2 where
263
c2.table_schema='data_dictionary' and
264
(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
265
group by c2.column_type order by num limit 1)
266
group by t.table_name order by num1, t.table_name;
269
# Bug#25859 ALTER DATABASE works w/o parameters
271
--error ER_PARSE_ERROR
273
--error ER_PARSE_ERROR
277
# Bug#27747 database metadata doesn't return sufficient column default info
281
f2 varchar(50) not null,
282
f3 varchar(50) default '',
283
f4 varchar(50) default NULL,
285
f6 bigint not null default 10,
286
f7 datetime not null,
287
f8 datetime default '2006-01-01'
289
#select column_default from data_dictionary.columns where table_name= 't1' ORDER BY column_default;
290
show columns from t1;
293
SET max_heap_table_size = DEFAULT;
296
--echo End of 5.0 tests.
299
# Bug#30795 Query on data_dictionary.schemas, wrong result
301
SELECT SCHEMA_NAME FROM data_dictionary.schemas
302
WHERE SCHEMA_NAME ='data_dictionary';
305
# Bug#31381 Error in retrieving Data from INFORMATION_SCHEMA
307
SELECT TABLE_COLLATION FROM data_dictionary.tables
308
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
311
--echo # Test that the query is visible to self and others.
314
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID();
317
# do a query on the PROCESSLIST table in I_S to ensure it works correctly
319
SELECT info, command, db
320
FROM data_dictionary.processlist
321
WHERE id = CONNECTION_ID();
326
#SELECT table_schema, table_name
327
#FROM data_dictionary.tables
328
#WHERE table_schema="data_dictionary"
329
#ORDER BY table_name;