create.test 9.82 KB
Newer Older
1 2 3 4
#
# Check some special create statements.
#

5
--disable_warnings
unknown's avatar
unknown committed
6
drop table if exists t1,t2,t3;
7
drop database if exists test_$1;
8 9
--enable_warnings

10 11 12 13 14 15 16 17 18
create table t1 (b char(0));
insert into t1 values (""),(null);
select * from t1;
drop table if exists t1;

create table t1 (b char(0) not null);
create table if not exists t1 (b char(0) not null);
insert into t1 values (""),(null);
select * from t1;
19
drop table t1;
20

unknown's avatar
unknown committed
21
create table t1 (a int not null auto_increment,primary key (a)) engine=heap;
unknown's avatar
unknown committed
22 23
drop table t1;

24 25 26 27
#
# Test of some CREATE TABLE'S that should fail
#

28
--error 1146
unknown's avatar
unknown committed
29
create table t2 engine=heap select * from t1;
30 31
--error 1146
create table t2 select auto+1 from t1;
32
drop table if exists t1,t2;
33 34 35
--error 1167
create table t1 (b char(0) not null, index(b));
--error 1163
unknown's avatar
unknown committed
36
create table t1 (a int not null,b text) engine=heap;
37 38
drop table if exists t1;

unknown's avatar
unknown committed
39
--error 1075
unknown's avatar
unknown committed
40
create table t1 (ordid int(8) not null auto_increment, ord  varchar(50) not null, primary key (ord,ordid)) engine=heap;
41

42 43
-- error 1044,1
create table not_existing_database.test (a int);
44
--error 1103
45
create table `a/a` (a int);
46
--error 1103
47 48 49
create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);
--error 1059
create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);
50 51 52 53 54 55 56 57 58 59 60

#
# test of dummy table names
#

create table 1ea10 (1a20 int,1e int);
insert into 1ea10 values(1,1);
select 1ea10.1a20,1e+ 1e+10 from 1ea10;
drop table 1ea10;
create table t1 (t1.index int);
drop table t1;
61
# Test that we get warning for this
62 63 64 65 66 67 68 69
drop database if exists test_$1;
create database test_$1;
create table test_$1.$test1 (a$1 int, $b int, c$ int);
insert into test_$1.$test1 values (1,2,3);
select a$1, $b, c$ from test_$1.$test1;
create table test_$1.test2$ (a int);
drop table test_$1.test2$;
drop database test_$1;
70

71
--error 1103
unknown's avatar
unknown committed
72
create table `` (a int);
73
--error 1103
unknown's avatar
unknown committed
74
drop table if exists ``;
75
--error 1166
unknown's avatar
unknown committed
76
create table t1 (`` int);
unknown's avatar
unknown committed
77
--error 1280
78
create table t1 (i int, index `` (i)); 
unknown's avatar
unknown committed
79

80 81 82 83 84 85 86 87 88 89
#
# Test of CREATE ... SELECT with indexes
#

create table t1 (a int auto_increment not null primary key, B CHAR(20));
insert into t1 (b) values ("hello"),("my"),("world");
create table t2 (key (b)) select * from t1;
explain select * from t2 where b="world";
select * from t2 where b="world";
drop table t1,t2;
unknown's avatar
unknown committed
90 91 92 93 94 95 96 97 98

#
# Test types after CREATE ... SELECT
#

create table t1(x varchar(50) );
create table t2 select x from t1 where 1=2;
describe t1;
describe t2;
unknown's avatar
unknown committed
99 100 101
drop table t2;
create table t2 select now() as a , curtime() as b, curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f;
describe t2;
102
drop table t2;
unknown's avatar
unknown committed
103
create table t2 select CAST("2001-12-29" AS DATE) as d, CAST("20:45:11" AS TIME) as t, CAST("2001-12-29  20:45:11" AS DATETIME) as dt;
104
describe t2;
unknown's avatar
unknown committed
105
drop table t1,t2;
unknown's avatar
merge  
unknown committed
106

107 108 109 110 111 112 113 114 115
#
# Test of CREATE ... SELECT with duplicate fields
#

create table t1 (a tinyint);
create table t2 (a int) select * from t1;                        
describe t1;
describe t2;
drop table if exists t2;
116 117
--error 1060
create table t2 (a int, a float) select * from t1;               
118
drop table if exists t2;
119 120
--error 1060
create table t2 (a int) select a as b, a+1 as b from t1;         
121
drop table if exists t2;
122 123
--error 1060
create table t2 (b int) select a as b, a+1 as b from t1;         
124 125
drop table if exists t1,t2;

126 127 128 129 130 131 132 133 134 135 136 137 138
#
# Test CREATE ... SELECT when insert fails
#

CREATE TABLE t1 (a int not null);
INSERT INTO t1 values (1),(2),(1);
--error 1062
CREATE TABLE t2 (primary key(a)) SELECT * FROM t1;
--error 1146
SELECT * from t2;
DROP TABLE t1;
DROP TABLE IF EXISTS t2;

unknown's avatar
unknown committed
139 140 141 142 143 144 145
#
# Test of primary key with 32 index
#

create table t1 (a int not null, b int, primary key(a), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b), key (b));
show create table t1;
drop table t1;
146 147
create table t1 select if(1,'1','0'), month("2002-08-02");
drop table t1;
unknown's avatar
unknown committed
148 149 150
create table t1 select if('2002'='2002','Y','N');
select * from t1;
drop table if exists t1;
151 152 153 154

#
# Test default table type
#
unknown's avatar
unknown committed
155 156
SET SESSION storage_engine="heap";
SELECT @@storage_engine;
157 158 159
CREATE TABLE t1 (a int not null);
show create table t1;
drop table t1;
unknown's avatar
unknown committed
160
--error 1286
unknown's avatar
unknown committed
161 162
SET SESSION storage_engine="gemini";
SELECT @@storage_engine;
163 164
CREATE TABLE t1 (a int not null);
show create table t1;
unknown's avatar
unknown committed
165
SET SESSION storage_engine=default;
166 167
drop table t1;

168 169 170 171 172 173

#
# ISO requires that primary keys are implicitly NOT NULL
#
create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));
insert into t1 values ("a", 1), ("b", 2);
unknown's avatar
unknown committed
174 175 176 177 178 179 180
--error 1048
insert into t1 values ("c", NULL);
--error 1048
insert into t1 values (NULL, 3);
--error 1048
insert into t1 values (NULL, NULL);
drop table t1;
unknown's avatar
unknown committed
181

182
#
unknown's avatar
unknown committed
183
# Bug # 801
184 185 186 187
#

create table t1 select x'4132';
drop table t1;
unknown's avatar
unknown committed
188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207

#
# bug #1434
#

create table t1 select 1,2,3;
create table if not exists t1 select 1,2;
--error 1136
create table if not exists t1 select 1,2,3,4;
create table if not exists t1 select 1;
select * from t1;
drop table t1;
create table t1 select 1,2,3;
create table if not exists t1 select 1,2;
--error 1136
create table if not exists t1 select 1,2,3,4;
create table if not exists t1 select 1;
select * from t1;
drop table t1;

208 209 210 211 212 213 214 215 216 217 218 219 220
#
# Test create table if not exists with duplicate key error
#

create table t1 (a int not null, b int, primary key (a));
insert into t1 values (1,1);
create table if not exists t1 select 2;
select * from t1;
create table if not exists t1 select 3 as 'a',4 as 'b';
--error 1062
create table if not exists t1 select 3 as 'a',3 as 'b';
select * from t1;
drop table t1;
unknown's avatar
unknown committed
221

unknown's avatar
unknown committed
222 223 224 225
#
# Test create with foreign keys
#

unknown's avatar
unknown committed
226 227 228
create table t1 (a int, key(a));
create table t2 (b int, foreign key(b) references t1(a), key(b));
drop table if exists t1,t2;
unknown's avatar
unknown committed
229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248

#
# Test for CREATE TABLE .. LIKE ..
#

create table t1(id int not null, name char(20));
insert into t1 values(10,'mysql'),(20,'monty- the creator');
create table t2(id int not null);
insert into t2 values(10),(20);
create table t3 like t1;
show create table t3;
select * from t3;
create table if not exists t3 like t1;
select @@warning_count;
create temporary table t3 like t2;
show create table t3;
select * from t3;
drop table t3;
show create table t3;
select * from t3;
unknown's avatar
unknown committed
249
drop table t2, t3;
unknown's avatar
unknown committed
250 251 252 253
create database test_$1;
create table test_$1.t3 like t1;
create temporary table t3 like test_$1.t3;
show create table t3;
unknown's avatar
unknown committed
254 255 256
create table t2 like t3;
show create table t2;
select * from t2;
unknown's avatar
unknown committed
257
create table t3 like t1;
258 259
--error 1050
create table t3 like test_$1.t3;
unknown's avatar
unknown committed
260 261
--error 1044,1
create table non_existing_database.t1 like t1;
262 263 264 265
--error 1051
create table t3 like non_existing_table;
--error 1050
create temporary table t3 like t1;
266
--error 1103
267
create table t3 like `a/a`;
unknown's avatar
unknown committed
268 269 270 271
drop table t1, t2, t3;
drop table t3;
drop database test_$1;

272 273 274
#
# Test default table type
#
unknown's avatar
unknown committed
275 276
SET SESSION storage_engine="heap";
SELECT @@storage_engine;
277 278 279
CREATE TABLE t1 (a int not null);
show create table t1;
drop table t1;
unknown's avatar
unknown committed
280
--error 1286
unknown's avatar
unknown committed
281 282
SET SESSION storage_engine="gemini";
SELECT @@storage_engine;
283 284
CREATE TABLE t1 (a int not null);
show create table t1;
unknown's avatar
unknown committed
285
SET SESSION storage_engine=default;
286
drop table t1;
287

288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322
#
# Test types of data for create select with functions
#

create table t1(a int,b int,c int unsigned,d date,e char,f datetime,g time,h blob);
insert into t1(a)values(1);
insert into t1(a,b,c,d,e,f,g,h)
values(2,-2,2,'1825-12-14','a','2003-1-1 3:2:1','4:3:2','binary data');
select * from t1;
select a, 
    ifnull(b,cast(-7 as signed)) as b, 
    ifnull(c,cast(7 as unsigned)) as c, 
    ifnull(d,cast('2000-01-01' as date)) as d, 
    ifnull(e,cast('b' as char)) as e,
    ifnull(f,cast('2000-01-01' as datetime)) as f, 
    ifnull(g,cast('5:4:3' as time)) as g,
    ifnull(h,cast('yet another binary data' as binary)) as h,
    addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd 
from t1;

create table t2
select
    a, 
    ifnull(b,cast(-7                        as signed))   as b,
    ifnull(c,cast(7                         as unsigned)) as c,
    ifnull(d,cast('2000-01-01'              as date))     as d,
    ifnull(e,cast('b'                       as char))     as e,
    ifnull(f,cast('2000-01-01'              as datetime)) as f,
    ifnull(g,cast('5:4:3'                   as time))     as g,
    ifnull(h,cast('yet another binary data' as binary))   as h,
    addtime(cast('1:0:0' as time),cast('1:0:0' as time))  as dd
from t1;
explain t2;
select * from t2;
drop table t1, t2;
unknown's avatar
Merge  
unknown committed
323

324 325 326 327 328 329 330 331
create table t1 (a tinyint, b smallint, c mediumint, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), i year, j date, k timestamp, l datetime, m enum('a','b'), n set('a','b'), o char(10));
create table t2 select ifnull(a,a), ifnull(b,b), ifnull(c,c), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(i,i), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(n,n), ifnull(o,o) from t1;
show create table t2;
drop table t1,t2;

#
# Test of default()
#
332 333 334 335 336 337 338
create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');
insert into t1 values ('','',0,0.0);
describe t1;
create table t2 select default(str) as str, default(strnull) as strnull, default(intg) as intg, default(rel) as rel from t1;
describe t2;
drop table t1, t2;

339 340 341 342 343 344 345 346 347 348
#
# Bug #2075
#

create table t1(name varchar(10), age smallint default -1);
describe t1;
create table t2(name varchar(10), age smallint default - 1);
describe t2;
drop table t1, t2;

349 350 351 352 353 354 355 356 357
#
# test for bug #1427 "enum allows duplicate values in the list"
#

create table t1(cenum enum('a'), cset set('b'));
create table t2(cenum enum('a','a'), cset set('b','b'));
create table t3(cenum enum('a','A','a','c','c'), cset set('b','B','b','d','d'));
drop table t1, t2, t3;

358 359 360 361 362 363 364 365 366 367 368 369 370
#
# Bug #1209
#

create database test_$1;
use test_$1;
select database();
drop database test_$1;
select database();

# Connect without a database
connect (user4,localhost,mysqltest_1,,*NO-ONE*);
select database();