← Back to branch summary

~drizzle-trunk/drizzle/development

« back to all changes in this revision

Viewing changes to tests/r/information_schema.result

  • Committer: Brian Aker
  • Date: 2010-02-19 20:32:03 UTC
  • mto: (1273.13.97 build)
  • mto: This revision was merged to the branch mainline in revision 1309.
  • Revision ID: brian@gaz-20100219203203-r35xkf1q3qhodqpy
Remove the old columns I_S table.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/information_schema.result
21
21
t5
22
22
show columns from t3 like "a%";
23
23
Field   Type    Null    Default Default is NULL On Update
24
 
select * from information_schema.old_COLUMNS where table_name="t1"
 
24
select * from data_dictionary.columns where table_name="t1"
25
25
and column_name= "a";
26
 
TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     ORDINAL_POSITION        COLUMN_DEFAULT  IS_NULLABLE     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   CHARACTER_SET_NAME      COLLATION_NAME  COLUMN_TYPE     COLUMN_KEY      EXTRA   PRIVILEGES      COLUMN_COMMENT  STORAGE FORMAT
27
 
NULL    mysqltest       t1      a       1       NULL    YES     int     NULL    NULL    10      0       NULL    NULL    int                                     Default Default
28
 
select table_name, column_name, privileges from information_schema.old_columns 
 
26
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     COLUMN_TYPE     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_NULLABLE     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   COLLATION_NAME  COLUMN_COMMENT
 
27
mysqltest       t1      a       INTEGER 0       0       TRUE            TRUE    INTEGER 0       0       0       0       utf8_general_ci 
 
28
select table_name, column_name from data_dictionary.columns 
29
29
where table_schema = 'mysqltest' and table_name = 't1';
30
 
table_name      column_name     privileges
31
 
t1      a       
32
 
t1      b       
 
30
table_name      column_name
 
31
t1      a
 
32
t1      b
33
33
show columns from mysqltest.t1;
34
34
Field   Type    Null    Default Default is NULL On Update
35
35
a       INTEGER TRUE            TRUE    
38
38
drop database mysqltest;
39
39
select * from information_schema.old_table_names;
40
40
ERROR 42S02: Unknown table 'old_table_names' in information_schema
41
 
select column_type from information_schema.old_columns
 
41
select column_type from data_dictionary.columns
42
42
where table_schema="information_schema" and table_name="COLUMNS" and
43
43
(column_name="character_set_name" or column_name="collation_name");
44
44
column_type
90
90
table_name NOT LIKE 'falcon%'
91
91
GROUP BY TABLE_SCHEMA ORDER BY table_schema;
92
92
table_schema    count(*)
93
 
information_schema      12
 
93
information_schema      11
94
94
show create database information_schema;
95
95
Database        Create Database
96
96
information_schema      CREATE DATABASE `information_schema`
97
97
create table t1(f1 LONGBLOB, f2 LONGTEXT);
98
98
select column_name,data_type,CHARACTER_OCTET_LENGTH,
99
99
CHARACTER_MAXIMUM_LENGTH
100
 
from information_schema.old_columns
 
100
from data_dictionary.columns
101
101
where table_name='t1';
102
102
column_name     data_type       CHARACTER_OCTET_LENGTH  CHARACTER_MAXIMUM_LENGTH
103
 
f1      blob    4294967295      4294967295
104
 
f2      text    4294967295      4294967295
 
103
f1      BLOB    0       0
 
104
f2      BLOB    0       0
105
105
drop table t1;
106
106
create table t1(f1 int, f2 int, f3 BIGINT, f4 int,
107
107
f5 BIGINT, f6 int, f7 int);
108
108
select column_name, NUMERIC_PRECISION, NUMERIC_SCALE
109
 
from information_schema.old_columns
 
109
from data_dictionary.columns
110
110
where table_name='t1';
111
111
column_name     NUMERIC_PRECISION       NUMERIC_SCALE
112
 
f1      10      0
113
 
f2      10      0
114
 
f3      19      0
115
 
f4      10      0
116
 
f5      19      0
117
 
f6      10      0
118
 
f7      10      0
 
112
f1      0       0
 
113
f2      0       0
 
114
f3      0       0
 
115
f4      0       0
 
116
f5      0       0
 
117
f6      0       0
 
118
f7      0       0
119
119
drop table t1;
120
120
create table t1 (a int not null, b int);
121
121
use information_schema;
122
 
select column_name, column_default from information_schema.old_columns
 
122
select column_name, column_default from data_dictionary.columns
123
123
where table_schema='test' and table_name='t1';
124
124
column_name     column_default
125
 
a       NULL
126
 
b       NULL
 
125
a       0
 
126
b       0
127
127
use test;
128
128
show columns from t1;
129
129
Field   Type    Null    Default Default is NULL On Update
156
156
drop table t1;
157
157
create table t1(f1 varbinary(32), f2 varbinary(64));
158
158
select character_maximum_length, character_octet_length
159
 
from information_schema.old_columns where table_name='t1';
 
159
from data_dictionary.columns where table_name='t1';
160
160
character_maximum_length        character_octet_length
161
 
32      32
162
 
64      64
 
161
32      128
 
162
64      256
163
163
drop table t1;
164
164
select column_type, group_concat(table_schema, '.', table_name), count(*) as num
165
 
from information_schema.old_columns where
 
165
from data_dictionary.columns where
166
166
table_schema='information_schema' and
167
167
(column_type = 'varchar(7)' or column_type = 'varchar(20)'
168
168
 or column_type = 'varchar(27)')
169
169
group by column_type order by column_type, num;
170
170
column_type     group_concat(table_schema, '.', table_name)     num
171
 
varchar(27)     information_schema.OLD_COLUMNS  1
172
171
create table t1(f1 char(1) not null, f2 char(9) not null);
173
172
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
174
 
information_schema.old_columns where table_schema='test' and table_name = 't1';
 
173
data_dictionary.columns where table_schema='test' and table_name = 't1';
175
174
CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH
176
175
1       4
177
176
9       36
188
187
SELECT t.table_name, c1.column_name
189
188
FROM data_dictionary.tables t
190
189
INNER JOIN
191
 
information_schema.old_columns c1
 
190
data_dictionary.columns c1
192
191
ON t.table_schema = c1.table_schema AND
193
192
t.table_name = c1.table_name
194
193
WHERE t.table_schema = 'information_schema' AND
195
194
c1.ordinal_position =
196
195
( SELECT COALESCE(MIN(c2.ordinal_position),1)
197
 
FROM information_schema.old_columns c2
 
196
FROM data_dictionary.columns c2
198
197
WHERE c2.table_schema = t.table_schema AND
199
198
c2.table_name = t.table_name AND
200
199
c2.column_name LIKE '%SCHEMA%'
202
201
AND t.table_name NOT LIKE 'falcon%'
203
202
  ORDER BY t.table_name, c1.column_name;
204
203
table_name      column_name
205
 
INNODB_CMP      page_size
206
 
INNODB_CMPMEM   page_size
207
 
INNODB_CMPMEM_RESET     page_size
208
 
INNODB_CMP_RESET        page_size
209
 
INNODB_LOCKS    lock_id
210
 
INNODB_LOCK_WAITS       requesting_trx_id
211
 
INNODB_TRX      trx_id
212
 
OLD_COLUMNS     TABLE_SCHEMA
 
204
INNODB_CMP      compress_ops
 
205
INNODB_CMPMEM   pages_used
 
206
INNODB_CMPMEM_RESET     pages_used
 
207
INNODB_CMP_RESET        compress_ops
 
208
INNODB_LOCKS    lock_trx_id
 
209
INNODB_LOCK_WAITS       requested_lock_id
 
210
INNODB_TRX      trx_state
213
211
OLD_KEY_COLUMN_USAGE    CONSTRAINT_SCHEMA
214
212
OLD_REFERENTIAL_CONSTRAINTS     CONSTRAINT_SCHEMA
215
213
OLD_STATISTICS  TABLE_SCHEMA
217
215
SELECT t.table_name, c1.column_name
218
216
FROM data_dictionary.tables t
219
217
INNER JOIN
220
 
information_schema.old_columns c1
 
218
data_dictionary.columns c1
221
219
ON t.table_schema = c1.table_schema AND
222
220
t.table_name = c1.table_name
223
221
WHERE t.table_schema = 'information_schema' AND
224
222
c1.ordinal_position =
225
223
( SELECT COALESCE(MIN(c2.ordinal_position),1)
226
 
FROM information_schema.old_columns c2
 
224
FROM data_dictionary.columns c2
227
225
WHERE c2.table_schema = 'information_schema' AND
228
226
c2.table_name = t.table_name AND
229
227
c2.column_name LIKE '%SCHEMA%'
231
229
AND t.table_name NOT LIKE 'falcon%'
232
230
  ORDER BY t.table_name, c1.column_name;
233
231
table_name      column_name
234
 
INNODB_CMP      page_size
235
 
INNODB_CMPMEM   page_size
236
 
INNODB_CMPMEM_RESET     page_size
237
 
INNODB_CMP_RESET        page_size
238
 
INNODB_LOCKS    lock_id
239
 
INNODB_LOCK_WAITS       requesting_trx_id
240
 
INNODB_TRX      trx_id
241
 
OLD_COLUMNS     TABLE_SCHEMA
 
232
INNODB_CMP      compress_ops
 
233
INNODB_CMPMEM   pages_used
 
234
INNODB_CMPMEM_RESET     pages_used
 
235
INNODB_CMP_RESET        compress_ops
 
236
INNODB_LOCKS    lock_trx_id
 
237
INNODB_LOCK_WAITS       requested_lock_id
 
238
INNODB_TRX      trx_state
242
239
OLD_KEY_COLUMN_USAGE    CONSTRAINT_SCHEMA
243
240
OLD_REFERENTIAL_CONSTRAINTS     CONSTRAINT_SCHEMA
244
241
OLD_STATISTICS  TABLE_SCHEMA
262
259
select t.table_name, group_concat(t.table_schema, '.', t.table_name),
263
260
count(*) as num1
264
261
from data_dictionary.tables t
265
 
inner join information_schema.old_columns c1
 
262
inner join data_dictionary.columns c1
266
263
on t.table_schema = c1.table_schema AND t.table_name = c1.table_name
267
264
where t.table_schema = 'information_schema' AND
268
265
t.table_name not like 'falcon%' AND
270
267
(select isnull(c2.column_type) -
271
268
isnull(group_concat(c2.table_schema, '.', c2.table_name)) +
272
269
count(*) as num
273
 
from information_schema.old_columns c2 where
 
270
from data_dictionary.columns c2 where
274
271
c2.table_schema='information_schema' and
275
272
(c2.column_type = 'varchar(7)' or c2.column_type = 'varchar(20)')
276
273
group by c2.column_type order by num limit 1)
312
309
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
313
310
TABLE_COLLATION
314
311
select * from data_dictionary.columns where table_schema = NULL;
315
 
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_NULLABLE     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   COLLATION_NAME  COLUMN_COMMENT
 
312
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     COLUMN_TYPE     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_NULLABLE     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   COLLATION_NAME  COLUMN_COMMENT
316
313
select * from `data_dictionary`.`columns` where `TABLE_NAME` = NULL;
317
 
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_NULLABLE     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   COLLATION_NAME  COLUMN_COMMENT
 
314
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     COLUMN_TYPE     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_NULLABLE     DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   COLLATION_NAME  COLUMN_COMMENT
318
315
select * from `information_schema`.`OLD_KEY_COLUMN_USAGE` where `TABLE_SCHEMA` = NULL;
319
316
CONSTRAINT_CATALOG      CONSTRAINT_SCHEMA       CONSTRAINT_NAME TABLE_CATALOG   TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     ORDINAL_POSITION        POSITION_IN_UNIQUE_CONSTRAINT   REFERENCED_TABLE_SCHEMA REFERENCED_TABLE_NAME   REFERENCED_COLUMN_NAME
320
317
select * from `information_schema`.`OLD_KEY_COLUMN_USAGE` where `TABLE_NAME` = NULL;