← 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-25 07:54:52 UTC
  • mfrom: (1273.13.101 build)
  • Revision ID: brian@gaz-20100225075452-19eozreshbrerypu
Merge of all patches in build.

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
 
8
 
 
9
9
# Test for data_dictionary.tables &
10
10
# show tables
11
11
 
20
20
select table_name from data_dictionary.tables
21
21
where table_schema = "mysqltest" and table_name like "t%";
22
22
 
23
 
select * from information_schema.old_STATISTICS where TABLE_SCHEMA = "mysqltest";
 
23
select * from data_dictionary.indexes where TABLE_SCHEMA = "mysqltest";
24
24
 
25
25
show tables like 't%';
26
26
--replace_column 8 # 12 # 13 #
27
27
#show table status;
28
28
show columns from t3 like "a%";
29
 
select * from information_schema.old_COLUMNS where table_name="t1"
 
29
select * from data_dictionary.columns where table_name="t1"
30
30
and column_name= "a";
31
31
 
32
32
connect (user3,localhost,mysqltest_2,,);
33
33
connection user3;
34
 
select table_name, column_name, privileges from information_schema.old_columns 
 
34
select table_name, column_name from data_dictionary.columns 
35
35
where table_schema = 'mysqltest' and table_name = 't1';
36
36
show columns from mysqltest.t1;
37
37
connect (user4,localhost,mysqltest_3,,mysqltest);
42
42
drop database mysqltest;
43
43
 
44
44
#
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
45
# Bug#2719 information_schema: errors in "columns"
52
46
#
53
 
select column_type from information_schema.old_columns
54
 
where table_schema="information_schema" and table_name="COLUMNS" and
 
47
select column_type from data_dictionary.columns
 
48
where table_schema="data_dictionary" and table_name="COLUMNS" and
55
49
(column_name="character_set_name" or column_name="collation_name");
56
50
 
57
51
#
59
53
#
60
54
--replace_column 1 #
61
55
select count(*) from data_dictionary.tables where 
62
 
table_schema="information_schema" and table_name="COLUMNS";
 
56
table_schema="data_dictionary" and table_name="COLUMNS";
63
57
 
64
58
--replace_column 1 #
65
59
select count(*) from data_dictionary.tables
109
103
GROUP BY TABLE_SCHEMA ORDER BY table_schema;
110
104
 
111
105
#
112
 
# Bug #9434 SHOW CREATE DATABASE information_schema;
113
 
#
114
 
show create database information_schema;
115
 
 
116
 
#
117
106
# Bug #11057 information_schema: columns table has some questionable contents
118
107
# Bug #12301 information_schema: NUMERIC_SCALE must be 0 for integer columns
119
108
#
120
109
create table t1(f1 LONGBLOB, f2 LONGTEXT);
121
110
select column_name,data_type,CHARACTER_OCTET_LENGTH,
122
111
       CHARACTER_MAXIMUM_LENGTH
123
 
from information_schema.old_columns
 
112
from data_dictionary.columns
124
113
where table_name='t1';
125
114
drop table t1;
126
115
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
127
116
                f5 BIGINT, f6 int, f7 int);
128
117
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
129
 
from information_schema.old_columns
 
118
from data_dictionary.columns
130
119
where table_name='t1';
131
120
drop table t1;
132
121
 
134
123
# Bug#12518 COLUMN_DEFAULT has wrong value if NOT NULL is set
135
124
#
136
125
create table t1 (a int not null, b int);
137
 
use information_schema;
138
 
select column_name, column_default from information_schema.old_columns
 
126
select column_name, column_default from data_dictionary.columns
139
127
  where table_schema='test' and table_name='t1';
140
128
use test;
141
129
show columns from t1;
142
130
drop table t1;
143
131
 
144
132
#
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
133
#
164
134
# Bug#14089 FROM list subquery always fails when information_schema is current database
165
135
#
167
137
create table t1(id int);
168
138
insert into t1(id) values (1);
169
139
select 1 from (select 1 from test.t1) a;
170
 
use information_schema;
 
140
use data_dictionary;
171
141
select 1 from (select 1 from test.t1) a;
172
142
use test;
173
143
drop table t1;
174
144
 
175
 
# Bug #14387 SHOW COLUMNS doesn't work on temporary tables
176
 
# Bug #15224 SHOW INDEX from temporary table doesn't work
177
 
# Bug #12770 DESC cannot display the info. about temporary table
178
 
#
179
 
create temporary table t1(f1 int, index(f1));
180
 
show columns from t1;
181
 
describe t1;
182
 
show indexes from t1;
183
 
drop table t1;
184
 
 
185
145
#
186
146
# Bug#14271 I_S: columns has no size for (var)binary columns
187
147
#
188
148
create table t1(f1 varbinary(32), f2 varbinary(64));
189
149
select character_maximum_length, character_octet_length
190
 
from information_schema.old_columns where table_name='t1';
 
150
from data_dictionary.columns where table_name='t1';
191
151
drop table t1;
192
152
 
193
153
##
194
154
## Bug#15307 GROUP_CONCAT() with ORDER BY returns empty set on information_schema
195
155
##
196
156
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
197
 
from information_schema.old_columns where
198
 
table_schema='information_schema' and
 
157
from data_dictionary.columns where
 
158
table_schema='data_dictionary' and
199
159
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
200
160
 or column_type = 'varchar(27)')
201
161
group by column_type order by column_type, num;
205
165
#
206
166
create table t1(f1 char(1) not null, f2 char(9) not null);
207
167
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
208
 
information_schema.old_columns where table_schema='test' and table_name = 't1';
 
168
data_dictionary.columns where table_schema='test' and table_name = 't1';
209
169
drop table t1;
210
170
 
211
171
#
225
185
SELECT t.table_name, c1.column_name
226
186
  FROM data_dictionary.tables t
227
187
       INNER JOIN
228
 
       information_schema.old_columns c1
 
188
       data_dictionary.columns c1
229
189
       ON t.table_schema = c1.table_schema AND
230
190
          t.table_name = c1.table_name
231
 
  WHERE t.table_schema = 'information_schema' AND
 
191
  WHERE t.table_schema = 'data_dictionary' AND
232
192
        c1.ordinal_position =
233
193
        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
234
 
            FROM information_schema.old_columns c2
 
194
            FROM data_dictionary.columns c2
235
195
            WHERE c2.table_schema = t.table_schema AND
236
196
                  c2.table_name = t.table_name AND
237
197
                  c2.column_name LIKE '%SCHEMA%'
242
202
SELECT t.table_name, c1.column_name
243
203
  FROM data_dictionary.tables t
244
204
       INNER JOIN
245
 
       information_schema.old_columns c1
 
205
       data_dictionary.columns c1
246
206
       ON t.table_schema = c1.table_schema AND
247
207
          t.table_name = c1.table_name
248
 
  WHERE t.table_schema = 'information_schema' AND
 
208
  WHERE t.table_schema = 'data_dictionary' AND
249
209
        c1.ordinal_position =
250
210
        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
251
 
            FROM information_schema.old_columns c2
252
 
            WHERE c2.table_schema = 'information_schema' AND
 
211
            FROM data_dictionary.columns c2
 
212
            WHERE c2.table_schema = 'data_dictionary' AND
253
213
                  c2.table_name = t.table_name AND
254
214
                  c2.column_name LIKE '%SCHEMA%'
255
215
        )
274
234
 
275
235
#select table_name from data_dictionary.tables
276
236
#where table_schema = 'test' and table_name not in
277
 
#(select table_name from information_schema.old_columns
 
237
#(select table_name from data_dictionary.columns
278
238
# where table_schema = 'test' and column_name = 'f3')
279
239
#ORDER BY table_name;
280
240
 
291
251
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
292
252
       count(*) as num1
293
253
from data_dictionary.tables t
294
 
inner join information_schema.old_columns c1
 
254
inner join data_dictionary.columns c1
295
255
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
296
 
where t.table_schema = 'information_schema' AND
 
256
where t.table_schema = 'data_dictionary' AND
297
257
      t.table_name not like 'falcon%' AND
298
258
        c1.ordinal_position =
299
259
        (select isnull(c2.column_type) -
300
260
         isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
301
261
         count(*) as num
302
 
         from information_schema.old_columns c2 where
303
 
         c2.table_schema='information_schema' and
 
262
         from data_dictionary.columns c2 where
 
263
         c2.table_schema='data_dictionary' and
304
264
         (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
305
265
          group by c2.column_type order by num limit 1)
306
266
group by t.table_name order by num1, t.table_name;
326
286
  f7 datetime not null,
327
287
  f8 datetime default '2006-01-01'
328
288
);
329
 
#select column_default from information_schema.old_columns where table_name= 't1' ORDER BY column_default;
 
289
#select column_default from data_dictionary.columns where table_name= 't1' ORDER BY column_default;
330
290
show columns from t1;
331
291
drop table t1;
332
292
 
333
 
#
334
 
# Bug#30079 A check for "hidden" I_S tables is flawed
335
 
#
336
 
--error 1109
337
 
show fields from information_schema.old_table_names;
338
 
--error 1109
339
 
show keys from information_schema.old_table_names;
340
 
 
341
293
SET max_heap_table_size = DEFAULT;
342
294
USE test;
343
295
 
347
299
# Bug#30795 Query on data_dictionary.schemas, wrong result
348
300
#
349
301
SELECT SCHEMA_NAME FROM data_dictionary.schemas
350
 
WHERE SCHEMA_NAME ='information_schema';
 
302
WHERE SCHEMA_NAME ='data_dictionary';
351
303
                                                                                                 
352
304
#
353
305
# Bug#31381 Error in retrieving Data from INFORMATION_SCHEMA
355
307
SELECT TABLE_COLLATION FROM data_dictionary.tables
356
308
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
357
309
 
358
 
#
359
 
# Bug#31633 Information schema = NULL queries crash the server
360
 
#
361
 
select * from data_dictionary.columns where table_schema = NULL;
362
 
select * from `data_dictionary`.`columns` where `TABLE_NAME` = NULL;
363
 
select * from `information_schema`.`OLD_KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
364
 
select * from `information_schema`.`OLD_KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
365
 
select * from `information_schema`.`OLD_REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
366
 
select * from `information_schema`.`OLD_REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
367
 
select * from data_dictionary.schemas where schema_name = NULL;
368
 
select * from `information_schema`.`OLD_STATISTICS` where `TABLE_SCHEMA` = NULL;
369
 
select * from `information_schema`.`OLD_STATISTICS` where `TABLE_NAME` = NULL;
370
 
select * from data_dictionary.tables where table_schema = NULL;
371
 
select * from data_dictionary.tables where table_name = NULL;
372
 
select * from `information_schema`.`OLD_TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
373
 
select * from `information_schema`.`OLD_TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
374
 
 
375
310
--echo #
376
311
--echo # Test that the query is visible to self and others.
377
312
--echo #
386
321
WHERE id = CONNECTION_ID();
387
322
 
388
323
#
389
 
# perform a query on COLUMNS
390
 
#
391
 
#SELECT table_name, column_name
392
 
#FROM data_dictionary.columns
393
 
#WHERE table_name IN
394
 
#(SELECT table_name FROM data_dictionary.tables )
395
 
#ORDER BY table_name;
396
 
 
397
 
#
398
 
# perform a query on KEY_COLUMN_USAGE
399
 
#
400
 
SELECT *
401
 
FROM information_schema.old_key_column_usage;
402
 
 
403
 
#
404
 
# perform a query on REFERENTIAL_CONSTRAINTS
405
 
#
406
 
SELECT * 
407
 
FROM information_schema.old_referential_constraints;
408
 
 
409
 
#
410
 
# query the SCHEMATA table
411
 
#
412
 
#--replace_column 1 #
413
 
#SELECT count(schema_name) FROM data_dictionary.schemas ORDER BY schema_name;
414
 
#
415
 
 
416
 
#
417
 
# query TABLE_CONSTRAINTS table
418
 
#
419
 
SELECT *
420
 
FROM information_schema.old_table_constraints;
421
 
 
422
 
#
423
324
# query TABLES
424
325
#
425
326
#SELECT table_schema, table_name