26
26
# Test of some CREATE TABLE'S that should fail
29
--error ER_NO_SUCH_TABLE
30
30
create temporary table t2 engine=MEMORY select * from t1;
31
--error ER_NO_SUCH_TABLE
32
32
create table t2 select auto+1 from t1;
33
33
drop table if exists t1,t2;
34
--error ER_WRONG_KEY_COLUMN
35
35
create table t1 (b char(0) not null, index(b));
36
--error ER_TABLE_CANT_HANDLE_BLOB
37
37
create temporary table t1 (a int not null,b text) engine=MEMORY;
38
38
drop table if exists t1;
40
--error ER_WRONG_AUTO_KEY
41
41
create temporary table t1 (ordid int not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=MEMORY;
43
44
create table not_existing_database.test (a int);
44
45
create table `a/a` (a int);
45
46
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
46
47
show create table `a/a`;
47
48
create table t1 like `a/a`;
49
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
51
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
52
show create table `t1`;
55
--error ER_WRONG_TABLE_NAME
51
56
create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);
57
--error ER_TOO_LONG_IDENT
53
58
create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);
56
61
# Some wrong defaults, so these creates should fail too (Bug #5902)
63
--error ER_INVALID_DEFAULT
59
64
create table t1 (a datetime default now());
65
--error ER_INVALID_ON_UPDATE
61
66
create table t1 (a datetime on update now());
67
--error ER_INVALID_DEFAULT
63
68
create table t1 (a int default 100 auto_increment);
64
69
# TODO: Should this really fail? What's wrong with default 1000 ???
70
#--error ER_INVALID_DEFAULT
66
71
#create table t1 (a int default 1000);
72
--error ER_INVALID_DEFAULT
68
73
create table t1 (a varchar(5) default 'abcdef');
70
75
create table t1 (a varchar(5) default 'abcde');
71
76
insert into t1 values();
78
--error ER_INVALID_DEFAULT
74
79
alter table t1 alter column a set default 'abcdef';
95
99
drop table mysqltest.test2$;
96
100
drop database mysqltest;
102
--error ER_WRONG_TABLE_NAME
99
103
create table `` (a int);
104
--error ER_WRONG_TABLE_NAME
101
105
drop table if exists ``;
106
--error ER_WRONG_COLUMN_NAME
103
107
create table t1 (`` int);
108
--error ER_WRONG_NAME_FOR_INDEX
105
109
create table t1 (i int, index `` (i));
142
146
drop table if exists t2;
147
--error ER_DUP_FIELDNAME
144
148
create table t2 (a int, a float) select * from t1;
145
149
drop table if exists t2;
150
--error ER_DUP_FIELDNAME
147
151
create table t2 (a int) select a as b, a+1 as b from t1;
148
152
drop table if exists t2;
153
--error ER_DUP_FIELDNAME
150
154
create table t2 (b int) select a as b, a+1 as b from t1;
151
155
drop table if exists t1,t2;
200
204
create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));
201
205
insert into t1 values ("a", 1), ("b", 2);
206
--error ER_BAD_NULL_ERROR
203
207
insert into t1 values ("c", NULL);
208
--error ER_BAD_NULL_ERROR
205
209
insert into t1 values (NULL, 3);
210
--error ER_BAD_NULL_ERROR
207
211
insert into t1 values (NULL, NULL);
221
225
create table t1 select 1,2,3;
226
--error ER_NO_DEFAULT_FOR_FIELD
223
227
create table if not exists t1 select 1,2;
228
--error ER_WRONG_VALUE_COUNT_ON_ROW
225
229
create table if not exists t1 select 1,2,3,4;
230
--error ER_NO_DEFAULT_FOR_FIELD
227
231
create table if not exists t1 select 1;
228
232
select * from t1;
252
256
# "Table truncated when creating another table name with Spaces"
259
--error ER_WRONG_TABLE_NAME
256
260
create table `t1 `(a int);
261
--error ER_WRONG_DB_NAME
258
262
create database `db1 `;
263
--error ER_WRONG_COLUMN_NAME
260
264
create table t1(`a ` int);
264
268
# "Parser permits multiple commas without syntax error"
271
--error ER_PARSE_ERROR
268
272
create table t1 (a int,);
273
--error ER_PARSE_ERROR
270
274
create table t1 (a int,,b int);
275
--error ER_PARSE_ERROR
272
276
create table t1 (,b int);
278
282
create table t1 (a int, key(a));
279
283
create table t2 (b int, foreign key(b) references t1(a), key(b));
284
--error ER_ROW_IS_REFERENCED
281
285
drop table if exists t1,t2;
282
286
drop table if exists t2,t1;
316
320
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
317
321
show create table t2;
318
322
select * from t2;
323
--error ER_TABLE_EXISTS_ERROR
320
324
create table t3 like t1;
325
--error ER_TABLE_EXISTS_ERROR
322
326
create table t3 like mysqltest.t3;
327
--error ER_BAD_DB_ERROR
324
328
create table non_existing_database.t1 like t1;
325
329
--error ER_NO_SUCH_TABLE
326
330
create table t3 like non_existing_table;
331
--error ER_TABLE_EXISTS_ERROR
328
332
create temporary table t3 like t1;
329
333
drop table t1, t2, t3;
330
334
drop database mysqltest;
410
414
create table t1(cenum enum('a'));
415
--error ER_DUPLICATED_VALUE_IN_TYPE
412
416
create table t2(cenum enum('a','a'));
417
--error ER_DUPLICATED_VALUE_IN_TYPE
414
418
create table t3(cenum enum('a','A','a','c','c'));
432
436
## TODO: Is this really a bug? It works in Drizzle. Should it?
437
#--error ER_WRONG_NAME_FOR_INDEX
434
438
#create table t1 (a int, index `primary` (a));
439
#--error ER_WRONG_NAME_FOR_INDEX
436
440
#create table t1 (a int, index `PRIMARY` (a));
438
442
#create table t1 (`primary` int, index(`primary`));
443
447
#show create table t2;
445
449
#create table t3 (a int);
450
#--error ER_WRONG_NAME_FOR_INDEX
447
451
#alter table t3 add index `primary` (a);
452
#--error ER_WRONG_NAME_FOR_INDEX
449
453
#alter table t3 add index `PRIMARY` (a);
451
455
#create table t4 (`primary` int);
481
485
# an improper fix is present.
483
487
create table t1 (a int);
488
--error ER_UPDATE_TABLE_USED
485
489
create table t1 select * from t1;
486
490
## TODO: Huh? --error ER_WRONG_OBJECT
487
491
#create table t2 union = (t1) select * from t1;
493
497
# Bug#10413: Invalid column name is not rejected
499
--error ER_WRONG_TABLE_NAME
496
500
create table t1(column.name int);
501
--error ER_WRONG_TABLE_NAME
498
502
create table t1(test.column.name int);
503
--error ER_WRONG_DB_NAME
500
504
create table t1(xyz.t1.name int);
501
505
create table t1(t1.name int);
502
506
create table t2(test.t2.name int);
1023
1027
# to be rewritten to hit the limit
1025
1029
# Ensure limit is really 64 keys
1030
--error ER_TOO_MANY_KEYS
1027
1031
alter table t1 add key
1028
1032
a065_long_123456789_123456789_123456789_123456789_123456789_1234 (
1029
1033
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);
1037
1041
c16 int, c17 int);
1039
1043
# Get error for max key parts
1044
--error ER_TOO_MANY_KEY_PARTS
1041
1045
alter table t1 add key i1 (
1042
1046
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16, c17);
1044
1048
# Get error for max key-name length
1049
--error ER_TOO_LONG_IDENT
1046
1050
alter table t1 add key
1047
1051
a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);
1147
1151
# Test incorrect database names
1154
--error ER_WRONG_DB_NAME
1151
1155
CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1156
--error ER_WRONG_DB_NAME
1153
1157
DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1155
1159
# TODO: enable these tests when RENAME DATABASE is implemented.
1160
# --error ER_BAD_DB_ERROR
1157
1161
# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;
1162
# --error ER_WRONG_DB_NAME
1159
1163
# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1160
1164
# create database mysqltest;
1165
# --error ER_WRONG_DB_NAME
1162
1166
# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1163
1167
# drop database mysqltest;
1169
--error ER_WRONG_DB_NAME
1166
1170
USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1171
--error ER_WRONG_DB_NAME
1168
1172
SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1209
1213
# Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA
1215
--error ER_CANT_CREATE_TABLE
1212
1216
create table t1 like data_dictionary.processlist;
1213
1217
create table t1 like data_dictionary.processlist engine=innodb;
1214
1218
show create table t1;
1220
--error ER_CANT_CREATE_TABLE
1217
1221
create temporary table t1 like data_dictionary.processlist;
1218
1222
create temporary table t1 like data_dictionary.processlist engine=myisam;
1219
1223
show create table t1;