← 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-14 02:02:48 UTC
  • mfrom: (1273.13.64 fix_is)
  • Revision ID: brian@gaz-20100214020248-bhovaejhz9fmer3q
Merge in data_dictionary.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/t/information_schema.test
1
 
# Test for information_schema.schemata &
 
1
# Test for data_dictionary.schemas &
2
2
# show databases
3
3
 
4
4
--disable_warnings
6
6
--enable_warnings
7
7
 
8
8
 
9
 
select * from information_schema.SCHEMATA where schema_name > 'm';
10
 
select schema_name from information_schema.schemata;
 
9
--replace_column 1 #
 
10
select count(*) from data_dictionary.schemas where schema_name > 'm';
 
11
select schema_name from data_dictionary.schemas;
11
12
show databases like 't%';
12
13
show databases;
13
14
 
14
 
# Test for information_schema.tables &
 
15
# Test for data_dictionary.tables &
15
16
# show tables
16
17
 
17
18
create database mysqltest;
22
23
create table t5 (id int auto_increment primary key);
23
24
insert into t5 values (10);
24
25
 
25
 
select table_name from information_schema.TABLES
 
26
select table_name from data_dictionary.tables
26
27
where table_schema = "mysqltest" and table_name like "t%";
27
28
 
28
 
select * from information_schema.STATISTICS where TABLE_SCHEMA = "mysqltest";
 
29
select * from information_schema.old_STATISTICS where TABLE_SCHEMA = "mysqltest";
29
30
 
30
31
show tables like 't%';
31
32
--replace_column 8 # 12 # 13 #
32
33
#show table status;
33
 
show full columns from t3 like "a%";
34
 
select * from information_schema.COLUMNS where table_name="t1"
 
34
show columns from t3 like "a%";
 
35
select * from information_schema.old_COLUMNS where table_name="t1"
35
36
and column_name= "a";
36
37
 
37
38
connect (user3,localhost,mysqltest_2,,);
38
39
connection user3;
39
 
select table_name, column_name, privileges from information_schema.columns 
 
40
select table_name, column_name, privileges from information_schema.old_columns 
40
41
where table_schema = 'mysqltest' and table_name = 't1';
41
42
show columns from mysqltest.t1;
42
43
connect (user4,localhost,mysqltest_3,,mysqltest);
46
47
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
47
48
drop database mysqltest;
48
49
 
49
 
# Test for information_schema.CHARACTER_SETS &
50
 
# SHOW CHARACTER SET
51
 
 
52
 
select * from information_schema.CHARACTER_SETS
 
50
# Test for information_schema.old_CHARACTER_SETS &
 
51
select * from data_dictionary.CHARACTER_SETS
53
52
where CHARACTER_SET_NAME like 'latin1%';
54
53
 
55
 
# Test for information_schema.COLLATIONS &
56
 
# SHOW COLLATION
 
54
# Test for information_schema.old_COLLATIONS &
57
55
 
58
56
--replace_column 5 #
59
 
select * from information_schema.COLLATIONS
60
 
where COLLATION_NAME like 'latin1%';
61
 
 
62
 
select * from information_schema.COLLATION_CHARACTER_SET_APPLICABILITY
 
57
select * from data_dictionary.COLLATIONS
63
58
where COLLATION_NAME like 'latin1%';
64
59
 
65
60
#
66
61
# Bug#7213: information_schema: redundant non-standard TABLE_NAMES table
67
62
#
68
63
--error 1109
69
 
select * from information_schema.table_names;
 
64
select * from information_schema.old_table_names;
70
65
 
71
66
#
72
67
# Bug#2719 information_schema: errors in "columns"
73
68
#
74
 
select column_type from information_schema.columns
 
69
select column_type from information_schema.old_columns
75
70
where table_schema="information_schema" and table_name="COLUMNS" and
76
71
(column_name="character_set_name" or column_name="collation_name");
77
72
 
78
73
#
79
74
# Bug#2718 information_schema: errors in "tables"
80
75
#
81
 
select TABLE_ROWS from information_schema.tables where 
 
76
--replace_column 1 #
 
77
select count(*) from data_dictionary.tables where 
82
78
table_schema="information_schema" and table_name="COLUMNS";
83
 
select table_type from information_schema.tables
 
79
 
 
80
--replace_column 1 #
 
81
select count(*) from data_dictionary.tables
84
82
where table_schema="mysql" and table_name="user";
85
83
 
86
84
#
87
85
# Bug #7215  information_schema: columns are longtext instead of varchar
88
86
# Bug #7217  information_schema: columns are varbinary() instead of timestamp
89
87
#
90
 
select table_schema,table_name, column_name from
91
 
information_schema.columns 
92
 
where data_type = 'longtext';
93
 
select table_name, column_name, data_type from information_schema.columns
94
 
where data_type = 'datetime';
 
88
select table_schema, table_name, column_name from data_dictionary.columns where data_type = 'longtext';
 
89
select table_name, column_name, data_type from data_dictionary.columns where data_type = 'datetime';
95
90
 
96
91
#
97
 
# Bug #8164  subquery with INFORMATION_SCHEMA.COLUMNS, 100 % CPU
 
92
# Bug #8164  subquery with data_dictionary.COLUMNS, 100 % CPU
98
93
#
99
 
SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES A
 
94
SELECT COUNT(*) FROM data_dictionary.tables A
100
95
WHERE NOT EXISTS 
101
 
(SELECT * FROM INFORMATION_SCHEMA.COLUMNS B
 
96
(SELECT * FROM data_dictionary.columns B
102
97
  WHERE A.TABLE_SCHEMA = B.TABLE_SCHEMA
103
98
  AND A.TABLE_NAME = B.TABLE_NAME);
104
99
 
116
111
  x_float FLOAT,
117
112
  x_double_precision DOUBLE PRECISION );
118
113
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH
119
 
FROM INFORMATION_SCHEMA.COLUMNS
 
114
FROM data_dictionary.columns
120
115
WHERE TABLE_NAME= 't1';
121
116
drop table t1;
122
117
 
124
119
# Bug #9404  information_schema: Weird error messages
125
120
# with SELECT SUM() ... GROUP BY queries
126
121
#
127
 
SELECT table_schema, count(*) FROM information_schema.TABLES
 
122
SELECT table_schema, count(*) FROM data_dictionary.tables
128
123
WHERE table_name NOT LIKE 'ndb_%' AND 
129
 
table_name NOT LIKE 'falcon%' AND
130
 
plugin_name IS NULL
131
 
GROUP BY TABLE_SCHEMA;
 
124
table_name NOT LIKE 'falcon%'
 
125
GROUP BY TABLE_SCHEMA ORDER BY table_schema;
132
126
 
133
127
#
134
128
# Bug #9434 SHOW CREATE DATABASE information_schema;
142
136
create table t1(f1 LONGBLOB, f2 LONGTEXT);
143
137
select column_name,data_type,CHARACTER_OCTET_LENGTH,
144
138
       CHARACTER_MAXIMUM_LENGTH
145
 
from information_schema.columns
 
139
from information_schema.old_columns
146
140
where table_name='t1';
147
141
drop table t1;
148
142
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
149
143
                f5 BIGINT, f6 int, f7 int);
150
144
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
151
 
from information_schema.columns
 
145
from information_schema.old_columns
152
146
where table_name='t1';
153
147
drop table t1;
154
148
 
157
151
#
158
152
create table t1 (a int not null, b int);
159
153
use information_schema;
160
 
select column_name, column_default from columns
 
154
select column_name, column_default from information_schema.old_columns
161
155
  where table_schema='test' and table_name='t1';
162
156
use test;
163
157
show columns from t1;
171
165
--error ER_DBACCESS_DENIED_ERROR
172
166
drop database information_schema;
173
167
use information_schema;
174
 
--error 1044
175
 
drop table tables;
176
 
--error 1044
177
 
alter table tables;
 
168
--error 1109
 
169
drop table old_tables;
 
170
--error 1109
 
171
alter table old_tables;
178
172
#
179
173
# Bug #9683 INFORMATION_SCH: Creation of temporary table allowed in Information_schema DB
180
174
#
181
175
use information_schema;
182
176
--error 1044
183
 
create temporary table schemata(f1 char(10));
 
177
create temporary table OLD_TABLE_CONSTRAINTS(f1 char(10));
184
178
#
185
179
#
186
180
# Bug#14089 FROM list subquery always fails when information_schema is current database
209
203
#
210
204
create table t1(f1 varbinary(32), f2 varbinary(64));
211
205
select character_maximum_length, character_octet_length
212
 
from information_schema.columns where table_name='t1';
 
206
from information_schema.old_columns where table_name='t1';
213
207
drop table t1;
214
208
 
215
209
#
216
210
# Bug#15307 GROUP_CONCAT() with ORDER BY returns empty set on information_schema
217
211
#
218
212
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
219
 
from information_schema.columns where
 
213
from information_schema.old_columns where
220
214
table_schema='information_schema' and
221
215
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
222
216
 or column_type = 'varchar(27)')
227
221
#
228
222
create table t1(f1 char(1) not null, f2 char(9) not null);
229
223
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
230
 
information_schema.columns where table_schema='test' and table_name = 't1';
 
224
information_schema.old_columns where table_schema='test' and table_name = 't1';
231
225
drop table t1;
232
226
 
233
227
#
237
231
create table t1(f1 char(5));
238
232
create table t2(f1 char(5));
239
233
select concat(@a, table_name), @a, table_name
240
 
from information_schema.tables where table_schema = 'test';
 
234
from data_dictionary.tables where table_schema = 'test';
241
235
drop table t1,t2;
242
236
 
243
237
#
245
239
#
246
240
 
247
241
SELECT t.table_name, c1.column_name
248
 
  FROM information_schema.tables t
 
242
  FROM data_dictionary.tables t
249
243
       INNER JOIN
250
 
       information_schema.columns c1
 
244
       information_schema.old_columns c1
251
245
       ON t.table_schema = c1.table_schema AND
252
246
          t.table_name = c1.table_name
253
247
  WHERE t.table_schema = 'information_schema' AND
254
248
        c1.ordinal_position =
255
249
        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
256
 
            FROM information_schema.columns c2
 
250
            FROM information_schema.old_columns c2
257
251
            WHERE c2.table_schema = t.table_schema AND
258
252
                  c2.table_name = t.table_name AND
259
253
                  c2.column_name LIKE '%SCHEMA%'
260
254
        )
261
255
  AND t.table_name NOT LIKE 'falcon%'
262
 
  AND t.plugin_name IS NULL;
 
256
  ORDER BY t.table_name, c1.column_name;
 
257
 
263
258
SELECT t.table_name, c1.column_name
264
 
  FROM information_schema.tables t
 
259
  FROM data_dictionary.tables t
265
260
       INNER JOIN
266
 
       information_schema.columns c1
 
261
       information_schema.old_columns c1
267
262
       ON t.table_schema = c1.table_schema AND
268
263
          t.table_name = c1.table_name
269
264
  WHERE t.table_schema = 'information_schema' AND
270
265
        c1.ordinal_position =
271
266
        ( SELECT COALESCE(MIN(c2.ordinal_position),1)
272
 
            FROM information_schema.columns c2
 
267
            FROM information_schema.old_columns c2
273
268
            WHERE c2.table_schema = 'information_schema' AND
274
269
                  c2.table_name = t.table_name AND
275
270
                  c2.column_name LIKE '%SCHEMA%'
276
271
        )
277
272
  AND t.table_name NOT LIKE 'falcon%'
278
 
  AND t.plugin_name IS NULL;
 
273
  ORDER BY t.table_name, c1.column_name;
279
274
 
280
275
#
281
276
# Bug#21231: query with a simple non-correlated subquery over
282
277
#            INFORMARTION_SCHEMA.TABLES 
283
278
#
284
279
 
285
 
SELECT MAX(table_name) FROM information_schema.tables;
286
 
SELECT table_name from information_schema.tables
 
280
SELECT MAX(table_name) FROM data_dictionary.tables;
 
281
SELECT table_name from data_dictionary.tables
287
282
  WHERE table_name=(SELECT MAX(table_name)
288
 
                      FROM information_schema.tables);
 
283
                      FROM data_dictionary.tables)
 
284
  ORDER BY table_name;
289
285
#
290
286
# Bug#23299 Some queries against INFORMATION_SCHEMA with subqueries fail
291
287
#
292
288
create table t1 (f1 int);
293
289
create table t2 (f1 int, f2 int);
294
290
 
295
 
select table_name from information_schema.tables
 
291
select table_name from data_dictionary.tables
296
292
where table_schema = 'test' and table_name not in
297
 
(select table_name from information_schema.columns
298
 
 where table_schema = 'test' and column_name = 'f3');
 
293
(select table_name from information_schema.old_columns
 
294
 where table_schema = 'test' and column_name = 'f3')
 
295
ORDER BY table_name;
 
296
 
299
297
drop table t1,t2;
300
298
 
301
299
 
302
300
#
303
301
# Bug#24630  Subselect query crashes mysqld
304
302
#
305
 
select 1 as f1 from information_schema.tables  where "CHARACTER_SETS"=
306
 
(select cast(table_name as char)  from information_schema.tables
 
303
select 1 as f1 from data_dictionary.tables  where "CHARACTER_SETS"=
 
304
(select cast(table_name as char)  from data_dictionary.tables
307
305
 order by table_name limit 1) limit 1;
308
306
 
309
307
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
310
308
       count(*) as num1
311
 
from information_schema.tables t
312
 
inner join information_schema.columns c1
 
309
from data_dictionary.tables t
 
310
inner join information_schema.old_columns c1
313
311
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
314
312
where t.table_schema = 'information_schema' AND
315
313
      t.table_name not like 'falcon%' AND
316
 
      t.plugin_name IS NULL AND
317
314
        c1.ordinal_position =
318
315
        (select isnull(c2.column_type) -
319
316
         isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
320
317
         count(*) as num
321
 
         from information_schema.columns c2 where
 
318
         from information_schema.old_columns c2 where
322
319
         c2.table_schema='information_schema' and
323
320
         (c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
324
321
          group by c2.column_type order by num limit 1)
345
342
  f7 datetime not null,
346
343
  f8 datetime default '2006-01-01'
347
344
);
348
 
select column_default from information_schema.columns where table_name= 't1';
 
345
select column_default from information_schema.old_columns where table_name= 't1' ORDER BY column_default;
349
346
show columns from t1;
350
347
drop table t1;
351
348
 
353
350
# Bug#30079 A check for "hidden" I_S tables is flawed
354
351
#
355
352
--error 1109
356
 
show fields from information_schema.table_names;
 
353
show fields from information_schema.old_table_names;
357
354
--error 1109
358
 
show keys from information_schema.table_names;
 
355
show keys from information_schema.old_table_names;
359
356
 
360
357
SET max_heap_table_size = DEFAULT;
361
358
USE test;
363
360
--echo End of 5.0 tests.
364
361
 
365
362
#
366
 
# Bug#30795 Query on INFORMATION_SCHEMA.SCHEMATA, wrong result
 
363
# Bug#30795 Query on data_dictionary.schemas, wrong result
367
364
#
368
 
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
 
365
SELECT SCHEMA_NAME FROM data_dictionary.schemas
369
366
WHERE SCHEMA_NAME ='information_schema';
370
367
                                                                                                 
371
368
#
372
369
# Bug#31381 Error in retrieving Data from INFORMATION_SCHEMA
373
370
#
374
 
SELECT TABLE_COLLATION FROM INFORMATION_SCHEMA.TABLES
 
371
SELECT TABLE_COLLATION FROM data_dictionary.tables
375
372
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
376
373
 
377
374
#
378
375
# Bug#31633 Information schema = NULL queries crash the server
379
376
#
380
 
select * from information_schema.columns where table_schema = NULL;
381
 
select * from `information_schema`.`COLUMNS` where `TABLE_NAME` = NULL;
382
 
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
383
 
select * from `information_schema`.`KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
384
 
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
385
 
select * from `information_schema`.`REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
386
 
select * from information_schema.schemata where schema_name = NULL;
387
 
select * from `information_schema`.`STATISTICS` where `TABLE_SCHEMA` = NULL;
388
 
select * from `information_schema`.`STATISTICS` where `TABLE_NAME` = NULL;
389
 
select * from information_schema.tables where table_schema = NULL;
390
 
select * from information_schema.tables where table_catalog = NULL;
391
 
select * from information_schema.tables where table_name = NULL;
392
 
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
393
 
select * from `information_schema`.`TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
 
377
select * from data_dictionary.columns where table_schema = NULL;
 
378
select * from `data_dictionary`.`columns` where `TABLE_NAME` = NULL;
 
379
select * from `information_schema`.`OLD_KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
 
380
select * from `information_schema`.`OLD_KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;
 
381
select * from `information_schema`.`OLD_REFERENTIAL_CONSTRAINTS` where `CONSTRAINT_SCHEMA` = NULL;
 
382
select * from `information_schema`.`OLD_REFERENTIAL_CONSTRAINTS` where `TABLE_NAME` = NULL;
 
383
select * from data_dictionary.schemas where schema_name = NULL;
 
384
select * from `information_schema`.`OLD_STATISTICS` where `TABLE_SCHEMA` = NULL;
 
385
select * from `information_schema`.`OLD_STATISTICS` where `TABLE_NAME` = NULL;
 
386
select * from data_dictionary.tables where table_schema = NULL;
 
387
select * from data_dictionary.tables where table_name = NULL;
 
388
select * from `information_schema`.`OLD_TABLE_CONSTRAINTS` where `TABLE_SCHEMA` = NULL;
 
389
select * from `information_schema`.`OLD_TABLE_CONSTRAINTS` where `TABLE_NAME` = NULL;
394
390
 
395
391
--echo #
396
392
--echo # Test that the query is visible to self and others.
397
393
--echo #
398
394
 
399
 
SELECT info FROM information_schema.processlist WHERE id = CONNECTION_ID();
 
395
SELECT info FROM data_dictionary.processlist WHERE id = CONNECTION_ID();
400
396
 
401
397
#
402
398
# test that SHOW PROCESSLIST works correctly
408
404
# do a query on the PROCESSLIST table in I_S to ensure it works correctly
409
405
#
410
406
SELECT info, command, db
411
 
FROM information_schema.processlist
 
407
FROM data_dictionary.processlist
412
408
WHERE id = CONNECTION_ID();
413
409
 
414
410
#
415
411
# do a query on the CHARACTER_SET table in I_S 
416
412
#
417
413
SELECT *
418
 
FROM information_schema.character_sets
 
414
FROM data_dictionary.character_sets
419
415
ORDER BY character_set_name;
420
416
 
421
417
#
422
418
# perform a query on the COLLATIONS table
423
419
#
424
420
SELECT *
425
 
FROM information_schema.collations
426
 
ORDER BY collation_name;
427
 
 
428
 
#
429
 
# perform a query on COLLATION_CHARACTER_SET_APPLICABILITY
430
 
#
431
 
SELECT * 
432
 
FROM information_schema.collation_character_set_applicability
 
421
FROM data_dictionary.collations
433
422
ORDER BY collation_name;
434
423
 
435
424
#
436
425
# perform a query on COLUMNS
437
426
#
438
427
SELECT table_name, column_name
439
 
FROM information_schema.columns
 
428
FROM data_dictionary.columns
440
429
WHERE table_name IN
441
 
(SELECT table_name FROM information_schema.tables 
442
 
 WHERE plugin_name IS NULL)
 
430
(SELECT table_name FROM data_dictionary.tables )
443
431
ORDER BY table_name;
444
432
 
445
433
#
446
434
# perform a query on KEY_COLUMN_USAGE
447
435
#
448
436
SELECT *
449
 
FROM information_schema.key_column_usage;
 
437
FROM information_schema.old_key_column_usage;
450
438
 
451
439
#
452
440
# perform a query on REFERENTIAL_CONSTRAINTS
453
441
#
454
442
SELECT * 
455
 
FROM information_schema.referential_constraints;
 
443
FROM information_schema.old_referential_constraints;
456
444
 
457
445
#
458
446
# query the SCHEMATA table
459
447
#
460
 
SELECT catalog_name, schema_name
461
 
FROM information_schema.schemata
462
 
ORDER BY schema_name;
463
 
 
464
 
#
465
 
# Query the STATUS and VARIABLES related
466
 
# I_S related tables. 
467
 
#
468
 
--replace_column 1 # 2 #
469
 
SELECT *
470
 
FROM information_schema.session_status
471
 
ORDER BY variable_name;
 
448
--replace_column 1 #
 
449
SELECT count(schema_name) FROM data_dictionary.schemas ORDER BY schema_name;
472
450
 
473
451
--replace_column 1 # 2 #
474
452
SHOW STATUS;
475
453
 
476
454
#--replace_column 1 # 2 #
477
455
#SELECT *
478
 
#FROM information_schema.session_variables
 
456
#FROM information_schema.old_session_variables
479
457
#ORDER BY variable_name;
480
458
 
481
459
#--replace_column 1 # 2 #
483
461
 
484
462
--replace_column 1 # 2 #
485
463
SELECT *
486
 
FROM information_schema.global_status
 
464
FROM information_schema.old_global_status
487
465
ORDER BY variable_name;
488
466
 
489
467
#--replace_column 1 # 2 #
490
468
#SELECT *
491
 
#FROM information_schema.global_variables
 
469
#FROM information_schema.old_global_variables
492
470
#ORDER BY variable_name;
493
471
 
494
472
#
495
473
# query TABLE_CONSTRAINTS table
496
474
#
497
475
SELECT *
498
 
FROM information_schema.table_constraints;
 
476
FROM information_schema.old_table_constraints;
499
477
 
500
478
#
501
479
# query TABLES
502
480
#
503
481
SELECT table_schema, table_name
504
 
FROM information_schema.tables
505
 
WHERE plugin_name IS NULL
 
482
FROM data_dictionary.tables
 
483
WHERE table_schema="data_dictionary"
506
484
ORDER BY table_name;
507
485
 
508
486
# 
510
488
# how do we test for this if the contents of this table can change
511
489
# depend on what plugins are configured for use?
512
490
#
513
 
#SELECT *
514
 
#FROM plugins;
 
491
--replace_column 1 #
 
492
SELECT count(*) FROM data_dictionary.plugins;