← Back to branch summary

~ubuntu-branches/ubuntu/trusty/drizzle/trusty

« back to all changes in this revision

Viewing changes to tests/r/data_dictionary_like_info.result

  • Committer: Bazaar Package Importer
  • Author(s): Monty Taylor
  • Date: 2010-10-02 14:17:48 UTC
  • mfrom: (1.1.1 upstream)
  • mto: (2.1.17 sid)
  • mto: This revision was merged to the branch mainline in revision 3.
  • Revision ID: james.westby@ubuntu.com-20101002141748-m6vbfbfjhrw1153e
Tags: 2010.09.1802-1
* New upstream release.
* Removed pid-file argument hack.
* Updated GPL-2 address to be new address.
* Directly copy in drizzledump.1 since debian doesn't have sphinx 1.0 yet.
* Link to jquery from libjs-jquery. Add it as a depend.
* Add drizzled.8 symlink to the install files.

Show diffs side-by-side

added added

removed removed

Lines of Context:
tests/r/data_dictionary_like_info.result
1
1
DROP TABLE IF EXISTS t0,t1,t2,t3,t4,t5;
2
 
DROP SCHEMA IF EXISTS data_dictionary;
3
2
select count(*) from data_dictionary.SCHEMAS where schema_name > 'm';
4
3
count(*)
5
4
#
11
10
test
12
11
show databases;
13
12
Database
14
 
data_dictionary
15
 
information_schema
 
13
DATA_DICTIONARY
 
14
INFORMATION_SCHEMA
16
15
mysql
17
16
test
18
17
create database mysqltest;
37
36
#       test    t5      STANDARD        InnoDB  #       #       #       #       #
38
37
show columns from t3 like "a%";
39
38
Field   Type    Null    Default Default_is_NULL On_Update
40
 
a       INTEGER TRUE            TRUE    
 
39
a       INTEGER YES             YES     
41
40
select * from data_dictionary.COLUMNS where table_name="t1"
42
41
and column_name= "a";
43
 
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     COLUMN_TYPE     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_NULLABLE     IS_INDEXED      IS_USED_IN_PRIMARY      IS_UNIQUE       IS_MULTI        IS_FIRST_IN_MULTI       INDEXES_FOUND_IN        DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   COLLATION_NAME  COLUMN_COMMENT
44
 
mysqltest       t1      a       INTEGER 0               TRUE            TRUE    FALSE   FALSE   FALSE   FALSE   FALSE   0       INTEGER 0       0       0       0               
 
42
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     COLUMN_TYPE     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_AUTO_INCREMENT       IS_NULLABLE     IS_INDEXED      IS_USED_IN_PRIMARY      IS_UNIQUE       IS_MULTI        IS_FIRST_IN_MULTI       INDEXES_FOUND_IN        DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   ENUM_VALUES     COLLATION_NAME  COLUMN_COMMENT
 
43
mysqltest       t1      a       INTEGER 0       NULL    YES             NO      YES     NO      NO      NO      NO      NO      0       INTEGER 0       0       0       0       NULL            NULL
45
44
select table_name, column_name from data_dictionary.columns 
46
45
where table_schema = 'mysqltest' and table_name = 't1';
47
46
table_name      column_name
49
48
t1      b
50
49
show columns from mysqltest.t1;
51
50
Field   Type    Null    Default Default_is_NULL On_Update
52
 
a       INTEGER TRUE            TRUE    
53
 
b       VARCHAR TRUE            TRUE    
 
51
a       INTEGER YES             YES     
 
52
b       VARCHAR YES             YES     
54
53
drop tables mysqltest.t4, mysqltest.t1, t2, t3, t5;
55
54
drop database mysqltest;
56
55
select * from data_dictionary.CHARACTER_SETS
99
98
WHERE TABLE_NAME= 't1';
100
99
COLUMN_NAME     CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH
101
100
x_bigint        0       0
 
101
x_decimal       0       0
 
102
x_double_precision      0       0
 
103
x_float 0       0
 
104
x_int   0       0
102
105
x_integer       0       0
103
 
x_int   0       0
104
 
x_decimal       0       0
105
106
x_numeric       0       0
106
107
x_real  0       0
107
 
x_float 0       0
108
 
x_double_precision      0       0
109
108
drop table t1;
110
109
SELECT table_schema, count(*) FROM data_dictionary.TABLES
111
110
WHERE table_name NOT LIKE 'ndb_%' AND 
138
137
drop table t1;
139
138
create table t1 (a int not null, b int);
140
139
use data_dictionary;
141
 
select column_name, column_default from columns
 
140
select column_name, column_default,column_default_is_null from columns
142
141
where table_schema='test' and table_name='t1';
143
 
column_name     column_default
144
 
a       
145
 
b       
 
142
column_name     column_default  column_default_is_null
 
143
a       NULL    NO
 
144
b       NULL    YES
146
145
use test;
147
146
show columns from t1;
148
147
Field   Type    Null    Default Default_is_NULL On_Update
149
 
a       INTEGER FALSE           FALSE   
150
 
b       INTEGER TRUE            TRUE    
 
148
a       INTEGER NO              NO      
 
149
b       INTEGER YES             YES     
151
150
drop table t1;
152
151
alter database data_dictionary;
153
152
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near '' at line 1
170
169
create temporary table t1(f1 int, index(f1));
171
170
show columns from t1;
172
171
Field   Type    Null    Default Default_is_NULL On_Update
173
 
f1      INTEGER TRUE            TRUE    
 
172
f1      INTEGER YES             YES     
174
173
describe t1;
175
174
Field   Type    Null    Default Default_is_NULL On_Update
176
 
f1      INTEGER TRUE            TRUE    
 
175
f1      INTEGER YES             YES     
177
176
show indexes from t1;
178
177
Table   Unique  Key_name        Seq_in_index    Column_name
179
 
t1      FALSE   f1      1       f1
 
178
t1      NO      f1      1       f1
180
179
drop table t1;
181
180
create table t1(f1 varbinary(32), f2 varbinary(64));
182
181
select character_maximum_length, character_octet_length
185
184
32      128
186
185
64      256
187
186
drop table t1;
188
 
select DATA_TYPE, group_concat(table_schema, '.', table_name), count(*) as num
 
187
select DATA_TYPE, group_concat(table_schema, '.', table_name) as a, count(*) as num
189
188
from data_dictionary.columns where
190
189
table_schema='data_dictionary' and
191
190
(DATA_TYPE = 'varchar' or DATA_TYPE = 'varchar'
192
191
 or DATA_TYPE = 'varchar')
193
192
group by DATA_TYPE order by DATA_TYPE, num;
194
 
DATA_TYPE       group_concat(table_schema, '.', table_name)     num
195
 
VARCHAR data_dictionary.CHARACTER_SETS,data_dictionary.CHARACTER_SETS,data_dictionary.CHARACTER_SETS,data_dictionary.COLLATIONS,data_dictionary.COLLATIONS,data_dictionary.COLLATIONS,data_dictionary.COLLATIONS,data_dictionary.COLLATIONS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.COLUMNS,data_dictionary.GLOBAL_STATEMENTS,data_dictionary.GLOBAL_STATEMENTS,data_dictionary.GLOBAL_STATUS,data_dictionary.GLOBAL_STATUS,data_dictionary.GLOBAL_VARIABLES,data_dictionary.GLOBAL_VARIABLES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEXES,data_dictionary.INDEX_PART        139
 
193
DATA_TYPE       a       num
 
194
VARCHAR #       168
196
195
Warnings:
197
 
Warning 1260    1 line(s) were cut by GROUP_CONCAT()
 
196
Warning #       1 line(s) were cut by GROUP_CONCAT()
198
197
create table t1(f1 char(1) not null, f2 char(9) not null);
199
198
select CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH from
200
199
data_dictionary.columns where table_schema='test' and table_name = 't1';
247
246
table_name      column_name
248
247
SELECT MAX(table_name) FROM data_dictionary.tables;
249
248
MAX(table_name)
250
 
TABLE_DEFINITION_CACHE
 
249
VIEW_TABLE_USAGE
251
250
SELECT table_name from data_dictionary.tables
252
251
WHERE table_name=(SELECT MAX(table_name)
253
252
FROM data_dictionary.tables);
254
253
table_name
255
 
TABLE_DEFINITION_CACHE
 
254
VIEW_TABLE_USAGE
256
255
create table t1 (f1 int);
257
256
create table t2 (f1 int, f2 int);
258
257
select table_name from data_dictionary.tables
300
299
f7 datetime not null,
301
300
f8 datetime default '2006-01-01'
302
301
);
303
 
select column_default from data_dictionary.columns where table_name= 't1';
304
 
column_default
305
 
 
306
 
 
307
 
 
308
 
 
309
 
 
310
 
10
311
 
10
312
 
2006-01-01
 
302
select column_name,column_default,column_default_is_null from data_dictionary.columns where table_name= 't1';
 
303
column_name     column_default  column_default_is_null
 
304
f1      NULL    YES
 
305
f2      NULL    NO
 
306
f3              NO
 
307
f4      NULL    YES
 
308
f5      NULL    NO
 
309
f6      10      NO
 
310
f7      NULL    NO
 
311
f8      2006-01-01 00:00:00     NO
313
312
show columns from t1;
314
313
Field   Type    Null    Default Default_is_NULL On_Update
315
 
f1      VARCHAR TRUE            TRUE    
316
 
f2      VARCHAR FALSE           FALSE   
317
 
f3      VARCHAR TRUE            FALSE   
318
 
f4      VARCHAR TRUE            TRUE    
319
 
f5      BIGINT  FALSE           FALSE   
320
 
f6      BIGINT  FALSE   10      FALSE   
321
 
f7      DATETIME        FALSE   10      FALSE   
322
 
f8      DATETIME        TRUE    2006-01-01      FALSE   
 
314
f1      VARCHAR YES             YES     
 
315
f2      VARCHAR NO              NO      
 
316
f3      VARCHAR YES             NO      
 
317
f4      VARCHAR YES             YES     
 
318
f5      BIGINT  NO              NO      
 
319
f6      BIGINT  NO      10      NO      
 
320
f7      DATETIME        NO              NO      
 
321
f8      DATETIME        YES     2006-01-01 00:00:00     NO      
323
322
drop table t1;
324
323
SET max_heap_table_size = DEFAULT;
325
324
USE test;
332
331
WHERE TABLE_SCHEMA='mysql' and TABLE_NAME= 'db';
333
332
TABLE_COLLATION
334
333
select * from data_dictionary.columns where table_schema = NULL;
335
 
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     COLUMN_TYPE     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_NULLABLE     IS_INDEXED      IS_USED_IN_PRIMARY      IS_UNIQUE       IS_MULTI        IS_FIRST_IN_MULTI       INDEXES_FOUND_IN        DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   COLLATION_NAME  COLUMN_COMMENT
 
334
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     COLUMN_TYPE     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_AUTO_INCREMENT       IS_NULLABLE     IS_INDEXED      IS_USED_IN_PRIMARY      IS_UNIQUE       IS_MULTI        IS_FIRST_IN_MULTI       INDEXES_FOUND_IN        DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   ENUM_VALUES     COLLATION_NAME  COLUMN_COMMENT
336
335
select * from `data_dictionary`.`COLUMNS` where `TABLE_NAME` = NULL;
337
 
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     COLUMN_TYPE     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_NULLABLE     IS_INDEXED      IS_USED_IN_PRIMARY      IS_UNIQUE       IS_MULTI        IS_FIRST_IN_MULTI       INDEXES_FOUND_IN        DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   COLLATION_NAME  COLUMN_COMMENT
 
336
TABLE_SCHEMA    TABLE_NAME      COLUMN_NAME     COLUMN_TYPE     ORDINAL_POSITION        COLUMN_DEFAULT  COLUMN_DEFAULT_IS_NULL  COLUMN_DEFAULT_UPDATE   IS_AUTO_INCREMENT       IS_NULLABLE     IS_INDEXED      IS_USED_IN_PRIMARY      IS_UNIQUE       IS_MULTI        IS_FIRST_IN_MULTI       INDEXES_FOUND_IN        DATA_TYPE       CHARACTER_MAXIMUM_LENGTH        CHARACTER_OCTET_LENGTH  NUMERIC_PRECISION       NUMERIC_SCALE   ENUM_VALUES     COLLATION_NAME  COLUMN_COMMENT
338
337
select * from `data_dictionary`.`INDEXES` where `TABLE_SCHEMA` = NULL;
339
338
TABLE_SCHEMA    TABLE_NAME      INDEX_NAME      IS_USED_IN_PRIMARY      IS_UNIQUE       IS_NULLABLE     KEY_LENGTH      INDEX_TYPE      INDEX_COMMENT
340
339
select * from `data_dictionary`.`INDEXES` where `TABLE_NAME` = NULL;
341
340
TABLE_SCHEMA    TABLE_NAME      INDEX_NAME      IS_USED_IN_PRIMARY      IS_UNIQUE       IS_NULLABLE     KEY_LENGTH      INDEX_TYPE      INDEX_COMMENT
342
341
select * from data_dictionary.schemas where schema_name = NULL;
343
 
SCHEMA_NAME     DEFAULT_COLLATION_NAME
 
342
SCHEMA_NAME     DEFAULT_COLLATION_NAME  SCHEMA_CREATION_TIME    SCHEMA_UPDATE_TIME
344
343
select * from data_dictionary.tables where table_schema = NULL;
345
 
TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE      ENGINE  ROW_FORMAT      TABLE_COLLATION TABLE_COMMENT
 
344
TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE      ENGINE  ROW_FORMAT      TABLE_COLLATION TABLE_CREATION_TIME     TABLE_UPDATE_TIME       TABLE_COMMENT
346
345
select * from data_dictionary.tables where table_name = NULL;
347
 
TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE      ENGINE  ROW_FORMAT      TABLE_COLLATION TABLE_COMMENT
 
346
TABLE_SCHEMA    TABLE_NAME      TABLE_TYPE      ENGINE  ROW_FORMAT      TABLE_COLLATION TABLE_CREATION_TIME     TABLE_UPDATE_TIME       TABLE_COMMENT
348
347
#
349
348
# Test that the query is visible to self and others.
350
349
#
357
356
#       #       #       4
358
357
SELECT * FROM data_dictionary.collations ORDER BY collation_name;
359
358
CHARACTER_SET_NAME      COLLATION_NAME  DESCRIPTION     ID      IS_DEFAULT      IS_COMPILED     SORTLEN
360
 
binary  binary  binary  63      TRUE    TRUE    1
361
 
utf8_general_ci utf8_bin        utf8    46      FALSE   TRUE    1
362
 
utf8_general_ci utf8_czech_ci   utf8    234     FALSE   TRUE    8
363
 
utf8_general_ci utf8_danish_ci  utf8    235     FALSE   TRUE    8
364
 
utf8_general_ci utf8_esperanto_ci       utf8    241     FALSE   TRUE    8
365
 
utf8_general_ci utf8_estonian_ci        utf8    230     FALSE   TRUE    8
366
 
utf8_general_ci utf8_general_ci utf8    45      TRUE    TRUE    1
367
 
utf8_general_ci utf8_hungarian_ci       utf8    242     FALSE   TRUE    8
368
 
utf8_general_ci utf8_icelandic_ci       utf8    225     FALSE   TRUE    8
369
 
utf8_general_ci utf8_latvian_ci utf8    226     FALSE   TRUE    8
370
 
utf8_general_ci utf8_lithuanian_ci      utf8    236     FALSE   TRUE    8
371
 
utf8_general_ci utf8_persian_ci utf8    240     FALSE   TRUE    8
372
 
utf8_general_ci utf8_polish_ci  utf8    229     FALSE   TRUE    8
373
 
utf8_general_ci utf8_romanian_ci        utf8    227     FALSE   TRUE    8
374
 
utf8_general_ci utf8_roman_ci   utf8    239     FALSE   TRUE    8
375
 
utf8_general_ci utf8_sinhala_ci utf8    243     FALSE   TRUE    8
376
 
utf8_general_ci utf8_slovak_ci  utf8    237     FALSE   TRUE    8
377
 
utf8_general_ci utf8_slovenian_ci       utf8    228     FALSE   TRUE    8
378
 
utf8_general_ci utf8_spanish2_ci        utf8    238     FALSE   TRUE    8
379
 
utf8_general_ci utf8_spanish_ci utf8    231     FALSE   TRUE    8
380
 
utf8_general_ci utf8_swedish_ci utf8    232     FALSE   TRUE    8
381
 
utf8_general_ci utf8_turkish_ci utf8    233     FALSE   TRUE    8
382
 
utf8_general_ci utf8_unicode_ci utf8    224     FALSE   TRUE    8
 
359
binary  binary  binary  63      YES     YES     1
 
360
utf8_general_ci utf8_bin        utf8    46      NO      YES     1
 
361
utf8_general_ci utf8_czech_ci   utf8    234     NO      YES     8
 
362
utf8_general_ci utf8_danish_ci  utf8    235     NO      YES     8
 
363
utf8_general_ci utf8_esperanto_ci       utf8    241     NO      YES     8
 
364
utf8_general_ci utf8_estonian_ci        utf8    230     NO      YES     8
 
365
utf8_general_ci utf8_general_ci utf8    45      YES     YES     1
 
366
utf8_general_ci utf8_hungarian_ci       utf8    242     NO      YES     8
 
367
utf8_general_ci utf8_icelandic_ci       utf8    225     NO      YES     8
 
368
utf8_general_ci utf8_latvian_ci utf8    226     NO      YES     8
 
369
utf8_general_ci utf8_lithuanian_ci      utf8    236     NO      YES     8
 
370
utf8_general_ci utf8_persian_ci utf8    240     NO      YES     8
 
371
utf8_general_ci utf8_polish_ci  utf8    229     NO      YES     8
 
372
utf8_general_ci utf8_romanian_ci        utf8    227     NO      YES     8
 
373
utf8_general_ci utf8_roman_ci   utf8    239     NO      YES     8
 
374
utf8_general_ci utf8_sinhala_ci utf8    243     NO      YES     8
 
375
utf8_general_ci utf8_slovak_ci  utf8    237     NO      YES     8
 
376
utf8_general_ci utf8_slovenian_ci       utf8    228     NO      YES     8
 
377
utf8_general_ci utf8_spanish2_ci        utf8    238     NO      YES     8
 
378
utf8_general_ci utf8_spanish_ci utf8    231     NO      YES     8
 
379
utf8_general_ci utf8_swedish_ci utf8    232     NO      YES     8
 
380
utf8_general_ci utf8_turkish_ci utf8    233     NO      YES     8
 
381
utf8_general_ci utf8_unicode_ci utf8    224     NO      YES     8
383
382
SELECT table_name, column_name
384
383
FROM data_dictionary.columns
385
384
WHERE table_name IN