← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/t/information_schema.test

  • Committer: Brian Aker
  • Date: 2010-02-19 23:07:45 UTC
  • mto: (1273.13.97 build)
  • mto: This revision was merged to the branch mainline in revision 1309.
  • Revision ID: brian@gaz-20100219230745-cmurfbxj845it9z7
This remove the original info_schema system for the server. More still to
cut, but we are on our way.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/information_schema.test
3
3
 
4
4
--disable_warnings
5
5
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
 
6
DROP SCHEMA IF EXISTS data_dictionary;
6
7
--enable_warnings
7
8
 
 
9
CREATE SCHEMA data_dictionary;
 
10
 
8
11
 
9
12
# Test for data_dictionary.tables &
10
13
# show tables
20
23
select table_name from data_dictionary.tables
21
24
where table_schema = "mysqltest" and table_name like "t%";
22
25
 
23
 
select * from information_schema.old_STATISTICS where TABLE_SCHEMA = "mysqltest";
 
26
select * from data_dictionary.indexes where TABLE_SCHEMA = "mysqltest";
24
27
 
25
28
show tables like 't%';
26
29
--replace_column 8 # 12 # 13 #
42
45
drop database mysqltest;
43
46
 
44
47
#
45
 
# Bug#7213: information_schema: redundant non-standard TABLE_NAMES table
46
 
#
47
 
--error 1109
48
 
select * from information_schema.old_table_names;
49
 
 
50
 
#
51
48
# Bug#2719 information_schema: errors in "columns"
52
49
#
53
50
select column_type from data_dictionary.columns
54
 
where table_schema="information_schema" and table_name="COLUMNS" and
 
51
where table_schema="data_dictionary" and table_name="COLUMNS" and
55
52
(column_name="character_set_name" or column_name="collation_name");
56
53
 
57
54
#
59
56
#
60
57
--replace_column 1 #
61
58
select count(*) from data_dictionary.tables where 
62
 
table_schema="information_schema" and table_name="COLUMNS";
 
59
table_schema="data_dictionary" and table_name="COLUMNS";
63
60
 
64
61
--replace_column 1 #
65
62
select count(*) from data_dictionary.tables
109
106
GROUP BY TABLE_SCHEMA ORDER BY table_schema;
110
107
 
111
108
#
112
 
# Bug #9434 SHOW CREATE DATABASE information_schema;
113
 
#
114
 
show create database information_schema;
115
 
 
116
 
#
117
109
# Bug #11057 information_schema: columns table has some questionable contents
118
110
# Bug #12301 information_schema: NUMERIC_SCALE must be 0 for integer columns
119
111
#
134
126
# Bug#12518 COLUMN_DEFAULT has wrong value if NOT NULL is set
135
127
#
136
128
create table t1 (a int not null, b int);
137
 
use information_schema;
138
129
select column_name, column_default from data_dictionary.columns
139
130
  where table_schema='test' and table_name='t1';
140
131
use test;
142
133
drop table t1;
143
134
 
144
135
#
145
 
# Bug #9846 Inappropriate error displayed while dropping table from 'INFORMATION_SCHEMA'
146
 
#
147
 
--error ER_PARSE_ERROR
148
 
alter database information_schema;
149
 
--error ER_DBACCESS_DENIED_ERROR
150
 
drop database information_schema;
151
 
use information_schema;
152
 
--error 1109
153
 
drop table old_tables;
154
 
--error 1109
155
 
alter table old_tables;
156
 
#
157
 
# Bug #9683 INFORMATION_SCH: Creation of temporary table allowed in Information_schema DB
158
 
#
159
 
use information_schema;
160
 
--error 1044
161
 
create temporary table OLD_TABLE_CONSTRAINTS(f1 char(10));
162
 
#
163
136
#
164
137
# Bug#14089 FROM list subquery always fails when information_schema is current database
165
138
#
167
140
create table t1(id int);
168
141
insert into t1(id) values (1);
169
142
select 1 from (select 1 from test.t1) a;
170
 
use information_schema;
 
143
use data_dictionary;
171
144
select 1 from (select 1 from test.t1) a;
172
145
use test;
173
146
drop table t1;
185
158
##
186
159
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
187
160
from data_dictionary.columns where
188
 
table_schema='information_schema' and
 
161
table_schema='data_dictionary' and
189
162
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
190
163
 or column_type = 'varchar(27)')
191
164
group by column_type order by column_type, num;
218
191
       data_dictionary.columns c1
219
192
       ON t.table_schema = c1.table_schema AND
220
193
          t.table_name = c1.table_name
221
 
  WHERE t.table_schema = 'information_schema' AND
 
194
  WHERE t.table_schema = 'data_dictionary' AND
222
195
        c1.ordinal_position =
223
196
        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
224
197
            FROM data_dictionary.columns c2
235
208
       data_dictionary.columns c1
236
209
       ON t.table_schema = c1.table_schema AND
237
210
          t.table_name = c1.table_name
238
 
  WHERE t.table_schema = 'information_schema' AND
 
211
  WHERE t.table_schema = 'data_dictionary' AND
239
212
        c1.ordinal_position =
240
213
        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
241
214
            FROM data_dictionary.columns c2
242
 
            WHERE c2.table_schema = 'information_schema' AND
 
215
            WHERE c2.table_schema = 'data_dictionary' AND
243
216
                  c2.table_name = t.table_name AND
244
217
                  c2.column_name LIKE '%SCHEMA%'
245
218
        )
283
256
from data_dictionary.tables t
284
257
inner join data_dictionary.columns c1
285
258
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
286
 
where t.table_schema = 'information_schema' AND
 
259
where t.table_schema = 'data_dictionary' AND
287
260
      t.table_name not like 'falcon%' AND
288
261
        c1.ordinal_position =
289
262
        (select isnull(c2.column_type) -
290
263
         isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
291
264
         count(*) as num
292
265
         from data_dictionary.columns c2 where
293
 
         c2.table_schema='information_schema' and
 
266
         c2.table_schema='data_dictionary' and
294
267
         (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
295
268
          group by c2.column_type order by num limit 1)
296
269
group by t.table_name order by num1, t.table_name;
329
302
# Bug#30795 Query on data_dictionary.schemas, wrong result
330
303
#
331
304
SELECT SCHEMA_NAME FROM data_dictionary.schemas
332
 
WHERE SCHEMA_NAME ='information_schema';
 
305
WHERE SCHEMA_NAME ='data_dictionary';
333
306
                                                                                                 
334
307
#
335
308
# Bug#31381 Error in retrieving Data from INFORMATION_SCHEMA
337
310
SELECT TABLE_COLLATION FROM data_dictionary.tables
338
311
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
339
312
 
340
 
#
341
 
# Bug#31633 Information schema = NULL queries crash the server
342
 
#
343
 
select * from data_dictionary.columns where table_schema = NULL;
344
 
select * from `data_dictionary`.`columns` where `TABLE_NAME` = NULL;
345
 
select * from `information_schema`.`OLD_KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
346
 
select * from `information_schema`.`OLD_KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
347
 
select * from `information_schema`.`OLD_REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
348
 
select * from `information_schema`.`OLD_REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
349
 
select * from data_dictionary.schemas where schema_name = NULL;
350
 
select * from `information_schema`.`OLD_STATISTICS` where `TABLE_SCHEMA` = NULL;
351
 
select * from `information_schema`.`OLD_STATISTICS` where `TABLE_NAME` = NULL;
352
 
select * from data_dictionary.tables where table_schema = NULL;
353
 
select * from data_dictionary.tables where table_name = NULL;
354
 
select * from `information_schema`.`OLD_TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
355
 
select * from `information_schema`.`OLD_TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
356
 
 
357
313
--echo #
358
314
--echo # Test that the query is visible to self and others.
359
315
--echo #
368
324
WHERE id = CONNECTION_ID();
369
325
 
370
326
#
371
 
# perform a query on COLUMNS
372
 
#
373
 
#SELECT table_name, column_name
374
 
#FROM data_dictionary.columns
375
 
#WHERE table_name IN
376
 
#(SELECT table_name FROM data_dictionary.tables )
377
 
#ORDER BY table_name;
378
 
 
379
 
#
380
 
# perform a query on KEY_COLUMN_USAGE
381
 
#
382
 
SELECT *
383
 
FROM information_schema.old_key_column_usage;
384
 
 
385
 
#
386
 
# perform a query on REFERENTIAL_CONSTRAINTS
387
 
#
388
 
SELECT * 
389
 
FROM information_schema.old_referential_constraints;
390
 
 
391
 
#
392
 
# query the SCHEMATA table
393
 
#
394
 
#--replace_column 1 #
395
 
#SELECT count(schema_name) FROM data_dictionary.schemas ORDER BY schema_name;
396
 
#
397
 
 
398
 
#
399
 
# query TABLE_CONSTRAINTS table
400
 
#
401
 
SELECT *
402
 
FROM information_schema.old_table_constraints;
403
 
 
404
 
#
405
327
# query TABLES
406
328
#
407
329
#SELECT table_schema, table_name
408
330
#FROM data_dictionary.tables
409
331
#WHERE table_schema="data_dictionary"
410
332
#ORDER BY table_name;
 
333
 
 
334
 
 
335
DROP SCHEMA data_dictionary;