2
# Check some special create statements.
6
drop table if exists t1,t2,t3,t4,t5;
7
drop database if exists mysqltest;
10
create table t1 (b char(0));
11
insert into t1 values (""),(null);
13
drop table if exists t1;
15
create table t1 (b char(0) not null);
16
create table if not exists t1 (b char(0) not null);
18
insert into t1 values (""),(null);
22
create temporary table t1 (a int not null auto_increment,primary key (a)) engine=MEMORY;
26
# Test of some CREATE TABLE'S that should fail
30
create temporary table t2 engine=MEMORY select * from t1;
32
create table t2 select auto+1 from t1;
33
drop table if exists t1,t2;
35
create table t1 (b char(0) not null, index(b));
37
create temporary table t1 (a int not null,b text) engine=MEMORY;
38
drop table if exists t1;
41
create temporary table t1 (ordid int not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=MEMORY;
44
create table not_existing_database.test (a int);
45
create table `a/a` (a int);
46
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
47
show create table `a/a`;
48
create table t1 like `a/a`;
52
create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);
54
create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);
57
# Some wrong defaults, so these creates should fail too (Bug #5902)
60
create table t1 (a datetime default now());
62
create table t1 (a datetime on update now());
64
create table t1 (a int default 100 auto_increment);
65
# TODO: Should this really fail? What's wrong with default 1000 ???
67
#create table t1 (a int default 1000);
69
create table t1 (a varchar(5) default 'abcdef');
71
create table t1 (a varchar(5) default 'abcde');
72
insert into t1 values();
75
alter table t1 alter column a set default 'abcdef';
79
# test of dummy table names
82
create table 1ea10 (1a20 int,1e int);
83
insert into 1ea10 values(1,1);
84
select 1ea10.1a20,1e+ 1e+10 from 1ea10;
86
create table t1 (t1.index int);
88
# Test that we get warning for this
89
drop database if exists mysqltest;
90
create database mysqltest;
91
create table mysqltest.$test1 (a$1 int, $b int, c$ int);
92
insert into mysqltest.$test1 values (1,2,3);
93
select a$1, $b, c$ from mysqltest.$test1;
94
create table mysqltest.test2$ (a int);
95
drop table mysqltest.test2$;
96
drop database mysqltest;
99
create table `` (a int);
101
drop table if exists ``;
103
create table t1 (`` int);
105
create table t1 (i int, index `` (i));
108
# Test of CREATE ... SELECT with indexes
111
create table t1 (a int auto_increment not null primary key, B CHAR(20));
112
insert into t1 (b) values ("hello"),("my"),("world");
113
create table t2 (key (b)) select * from t1;
114
explain select * from t2 where b="world";
115
select * from t2 where b="world";
119
# Test types after CREATE ... SELECT
122
create table t1(x varchar(50) );
123
create table t2 select x from t1 where 1=2;
127
create table t2 select now() as a , curdate() as c , 1+1 as d , 1.0 + 1 as e , 33333333333333333 + 3 as f;
130
create table t2 select CAST("2001-12-29" AS DATE) as d, CAST("2001-12-29 20:45:11" AS DATETIME) as dt;
135
# Test of CREATE ... SELECT with duplicate fields
138
create table t1 (a int);
139
create table t2 (a int) select * from t1;
142
drop table if exists t2;
144
create table t2 (a int, a float) select * from t1;
145
drop table if exists t2;
147
create table t2 (a int) select a as b, a+1 as b from t1;
148
drop table if exists t2;
150
create table t2 (b int) select a as b, a+1 as b from t1;
151
drop table if exists t1,t2;
154
# Test CREATE ... SELECT when insert fails
157
CREATE TABLE t1 (a int not null);
158
INSERT INTO t1 values (1),(2),(1);
160
CREATE TABLE t2 (primary key(a)) SELECT * FROM t1;
164
DROP TABLE IF EXISTS t2;
167
# Test of primary key with 32 index
170
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));
171
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
172
show create table t1;
174
create table t1 select if(1,'1','0'), month("2002-08-02");
176
create table t1 select if('2002'='2002','Y','N');
178
drop table if exists t1;
181
# Test default table type
183
SET SESSION storage_engine="MEMORY";
184
SELECT @@storage_engine;
185
CREATE TEMPORARY TABLE t1 (a int not null);
186
show create table t1;
189
SET SESSION storage_engine="gemini";
190
SELECT @@storage_engine;
191
CREATE TEMPORARY TABLE t1 (a int not null);
192
show create table t1;
193
SET SESSION storage_engine=default;
198
# ISO requires that primary keys are implicitly NOT NULL
200
create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));
201
insert into t1 values ("a", 1), ("b", 2);
203
insert into t1 values ("c", NULL);
205
insert into t1 values (NULL, 3);
207
insert into t1 values (NULL, NULL);
214
create table t1 select x'4132';
221
create table t1 select 1,2,3;
223
create table if not exists t1 select 1,2;
225
create table if not exists t1 select 1,2,3,4;
227
create table if not exists t1 select 1;
232
# Test create table if not exists with duplicate key error
236
create table t1 (a int not null, b int, primary key (a));
237
insert into t1 values (1,1);
238
# TODO: BUG here, this is filling in right to left for some reason
239
#create table if not exists t1 select 2;
241
create table if not exists t1 select 3 as 'a',4 as 'b';
243
create table if not exists t1 select 3 as 'a',3 as 'b';
245
--replace_column 3 # 4 # 5 #
246
select * from DATA_DICTIONARY.TABLE_DEFINITION_CACHE WHERE TABLE_COUNT > 1 ORDER BY TABLE_SCHEMA, TABLE_NAME;
252
# "Table truncated when creating another table name with Spaces"
256
create table `t1 `(a int);
258
create database `db1 `;
260
create table t1(`a ` int);
264
# "Parser permits multiple commas without syntax error"
268
create table t1 (a int,);
270
create table t1 (a int,,b int);
272
create table t1 (,b int);
275
# Test create with foreign keys
278
create table t1 (a int, key(a));
279
create table t2 (b int, foreign key(b) references t1(a), key(b));
281
drop table if exists t1,t2;
282
drop table if exists t2,t1;
285
# Test for CREATE TABLE .. LIKE ..
288
create table t1(id int not null, name char(20));
289
insert into t1 values(10,'mysql'),(20,'monty- the creator');
290
create table t2(id int not null);
291
insert into t2 values(10),(20);
292
create table t3 like t1;
293
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
294
show create table t3;
296
# Disable PS becasue of @@warning_count
297
create table if not exists t3 like t1;
298
--disable_ps_protocol
299
select @@warning_count;
301
create temporary table t3 like t2;
302
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
303
show create table t3;
306
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
307
show create table t3;
310
create database mysqltest;
311
create table mysqltest.t3 like t1;
312
create temporary table t3 like mysqltest.t3;
313
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
314
show create table t3;
315
create table t2 like t3;
316
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
317
show create table t2;
320
create table t3 like t1;
322
create table t3 like mysqltest.t3;
324
create table non_existing_database.t1 like t1;
325
--error ER_NO_SUCH_TABLE
326
create table t3 like non_existing_table;
328
create temporary table t3 like t1;
329
drop table t1, t2, t3;
330
drop database mysqltest;
333
# Test default table type
335
SET SESSION storage_engine="MEMORY";
336
SELECT @@storage_engine;
337
CREATE TEMPORARY TABLE t1 (a int not null);
338
show create table t1;
341
SET SESSION storage_engine="gemini";
342
SELECT @@storage_engine;
343
CREATE TEMPORARY TABLE t1 (a int not null);
344
show create table t1;
345
SET SESSION storage_engine=default;
349
# Test types of data for create select with functions
352
create table t1(a int,b int,c int,d date,e char,f datetime,h blob);
353
insert into t1(a)values(1);
354
insert into t1(a,b,c,d,e,f,h)
355
values(2,-2,2,'1825-12-14','a','2003-01-01 03:02:01','binary data');
360
ifnull(d,cast('2000-01-01' as date)) as d,
361
ifnull(e,cast('b' as char)) as e,
362
ifnull(f,cast('2000-01-01' as datetime)) as f,
363
ifnull(h,cast('yet another binary data' as binary)) as h
371
ifnull(d,cast('2000-01-01' as date)) as d,
372
ifnull(e,cast('b' as char)) as e,
373
ifnull(f,cast('2000-01-01' as datetime)) as f,
374
ifnull(h,cast('yet another binary data' as binary)) as h
380
create table t1 (a int, b int, d int, e bigint, f float(3,2), g double(4,3), h decimal(5,4), j date, k timestamp, l datetime, m enum('a','b'), o char(10));
381
create table t2 select ifnull(a,a), ifnull(b,b), ifnull(d,d), ifnull(e,e), ifnull(f,f), ifnull(g,g), ifnull(h,h), ifnull(j,j), ifnull(k,k), ifnull(l,l), ifnull(m,m), ifnull(o,o) from t1;
382
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
383
show create table t2;
389
create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');
390
insert into t1 values ('','',0,0.0);
392
create table t2 select default(str) as str, default(strnull) as strnull, default(intg) as intg, default(rel) as rel from t1;
400
create table t1(name varchar(10), age int default -1);
402
create table t2(name varchar(10), age int default - 1);
407
# test for bug #1427 "enum allows duplicate values in the list"
410
create table t1(cenum enum('a'));
412
create table t2(cenum enum('a','a'));
414
create table t3(cenum enum('a','A','a','c','c'));
421
create database mysqltest;
424
drop database mysqltest;
429
# Test for Bug 856 'Naming a key "Primary" causes trouble'
432
## TODO: Is this really a bug? It works in Drizzle. Should it?
434
#create table t1 (a int, index `primary` (a));
436
#create table t1 (a int, index `PRIMARY` (a));
438
#create table t1 (`primary` int, index(`primary`));
439
#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
440
#show create table t1;
441
#create table t2 (`PRIMARY` int, index(`PRIMARY`));
442
#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
443
#show create table t2;
445
#create table t3 (a int);
447
#alter table t3 add index `primary` (a);
449
#alter table t3 add index `PRIMARY` (a);
451
#create table t4 (`primary` int);
452
#alter table t4 add index(`primary`);
453
#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
454
#show create table t4;
455
#create table t5 (`PRIMARY` int);
456
#alter table t5 add index(`PRIMARY`);
457
#--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
458
#show create table t5;
460
#drop table t1, t2, t3, t4, t5;
463
# bug #3266 TEXT in CREATE TABLE SELECT
466
CREATE TABLE t1(id varchar(10) NOT NULL PRIMARY KEY, dsc longtext);
467
INSERT INTO t1 VALUES ('5000000001', NULL),('5000000003', 'Test'),('5000000004', NULL);
468
CREATE TABLE t2(id varchar(15) NOT NULL, proc varchar(100) NOT NULL, runID varchar(16) NOT NULL, start datetime NOT NULL, PRIMARY KEY (id,proc,runID,start));
470
INSERT INTO t2 VALUES ('5000000001', 'proc01', '20031029090650', '2003-10-29 13:38:40'),('5000000001', 'proc02', '20031029090650', '2003-10-29 13:38:51'),('5000000001', 'proc03', '20031029090650', '2003-10-29 13:38:11'),('5000000002', 'proc09', '20031024013310', '2003-10-24 01:33:11'),('5000000002', 'proc09', '20031024153537', '2003-10-24 15:36:04'),('5000000004', 'proc01', '20031024013641', '2003-10-24 01:37:29'),('5000000004', 'proc02', '20031024013641', '2003-10-24 01:37:39');
472
CREATE TABLE t3 SELECT t1.dsc,COUNT(DISTINCT t2.id) AS countOfRuns FROM t1 LEFT JOIN t2 ON (t1.id=t2.id) GROUP BY t1.id;
474
drop table t1, t2, t3;
478
# Bug#10224 - ANALYZE TABLE crashing with simultaneous
479
# CREATE ... SELECT statement.
480
# This tests two additional possible errors and a hang if
481
# an improper fix is present.
483
create table t1 (a int);
485
create table t1 select * from t1;
486
## TODO: Huh? --error ER_WRONG_OBJECT
487
#create table t2 union = (t1) select * from t1;
488
flush tables with read lock;
493
# Bug#10413: Invalid column name is not rejected
496
create table t1(column.name int);
498
create table t1(test.column.name int);
500
create table t1(xyz.t1.name int);
501
create table t1(t1.name int);
502
create table t2(test.t2.name int);
506
# Bug #12537: UNION produces longtext instead of varchar
508
CREATE TABLE t1 (f1 VARCHAR(255));
509
CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;
514
# Bug#12913 Simple SQL can crash server or connection
516
CREATE TABLE t12913 (f1 ENUM ('a','b')) AS SELECT 'a' AS f1;
517
SELECT * FROM t12913;
521
# Bug#11028: Crash on create table like
523
create database mysqltest;
525
drop database mysqltest;
526
--error ER_NO_DB_ERROR
527
create table test.t1 like x;
529
drop table if exists test.t1;
532
# Bug #6008 MySQL does not create warnings when
533
# creating database and using IF NOT EXISTS
535
create database mysqltest;
536
create database if not exists mysqltest;
537
show create database mysqltest;
538
drop database mysqltest;
540
create table t1 (a int);
541
create table if not exists t1 (a int);
546
a varchar(112) collate utf8_bin not null,
548
) select 'test' as a ;
550
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
551
show create table t1;
555
# BUG#14480: assert failure in CREATE ... SELECT because of wrong
556
# calculation of number of NULLs.
561
insert into t2 values(111);
565
a varchar(12) collate utf8_bin not null,
566
b int not null, primary key (a)
567
) select a, 1 as b from t2 ;
568
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
569
show create table t1;
574
a varchar(12) collate utf8_bin not null,
575
b int not null, primary key (a)
576
) select a, 1 as c from t2 ;
579
a varchar(12) collate utf8_bin not null,
580
b int null, primary key (a)
581
) select a, 1 as c from t2 ;
582
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
583
show create table t1;
587
a varchar(12) collate utf8_bin not null,
588
b int not null, primary key (a)
589
) select 'a' as a , 1 as b from t2 ;
590
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
591
show create table t1;
595
a varchar(12) collate utf8_bin,
596
b int not null, primary key (a)
597
) select 'a' as a , 1 as b from t2 ;
598
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
599
show create table t1;
604
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
606
insert into t1 values (1,1,1, 1,1,1, 1,1,1);
610
a1 varchar(12) collate utf8_bin not null,
611
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,
613
) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;
618
a1 varchar(12) collate utf8_bin,
619
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
620
) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1;
625
a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
627
insert into t1 values (1,1,1, 1,1,1, 1,1,1);
631
a1 varchar(12) collate utf8_bin not null,
632
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,
634
) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;
636
# Test the default value
639
create table t2 ( a int default 3, b int default 3)
640
select a1,a2 from t1;
641
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
642
show create table t2;
648
# Tests for errors happening at various stages of CREATE TABLES ... SELECT
650
# (Also checks that it behaves atomically in the sense that in case
651
# of error it is automatically dropped if it has not existed before.)
653
# Error during open_and_lock_tables() of tables
654
--error ER_NO_SUCH_TABLE
655
create table t1 select * from t2;
656
# Rather special error which also caught during open tables pahse
657
--error ER_UPDATE_TABLE_USED
658
create table t1 select * from t1;
659
# Error which happens before select_create::prepare()
660
--error ER_CANT_AGGREGATE_2COLLATIONS
661
create table t1 select coalesce('a' collate utf8_swedish_ci,'b' collate utf8_bin);
662
# Error during table creation
663
--error ER_KEY_COLUMN_DOES_NOT_EXITS
664
create table t1 (primary key(a)) select "b" as b;
665
# Error in select_create::prepare() which is not related to table creation
666
# TODO: This really should be failing...
667
# create table t1 (a int);
668
# --error ER_WRONG_VALUE_COUNT_ON_ROW
669
# create table if not exists t1 select 1 as a, 2 as b;
671
# Finally error which happens during insert
673
create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);
674
# What happens if table already exists ?
675
create table t1 (i int);
676
# TODO: BUG lp:311045
677
#--error ER_TABLE_EXISTS_ERROR
678
#create table t1 select 1 as i;
679
create table if not exists t1 select 1 as i;
682
# Error before select_create::prepare()
683
--error ER_CANT_AGGREGATE_2COLLATIONS
684
create table t1 select coalesce('a' collate utf8_swedish_ci,'b' collate utf8_bin);
685
# Error which happens during insertion of rows
686
# TODO: Bug lp:311072
687
# create table t1 (i int);
688
# alter table t1 add primary key (i);
689
# --error ER_DUP_ENTRY
690
# create table if not exists t1 (select 2 as i) union all (select 2 as i);
695
# Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent
696
# results of CREATE TABLE ... SELECT when temporary table exists").
697
# In this situation we either have to create non-temporary table and
698
# insert data in it or insert data in temporary table without creation
699
# of permanent table. Since currently temporary tables always shadow
700
# permanent tables we adopt second approach.
701
create temporary table t1 (j int);
702
create table if not exists t1 select 1;
704
drop temporary table t1;
705
--error ER_NO_SUCH_TABLE
707
--error ER_BAD_TABLE_ERROR
712
# Bug#21772: can not name a column 'upgrade' when create a table
714
create table t1 (upgrade int);
719
# Bug #26642: create index corrupts table definition in .frm
721
# Problem with creating keys with maximum key-parts and maximum name length
722
# This test is made for a mysql server supporting names up to 64 bytes
723
# and a maximum of 16 key segements per Key
727
c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int,
728
c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int,
730
key a001_long_123456789_123456789_123456789_123456789_123456789_1234 (
731
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
732
key a002_long_123456789_123456789_123456789_123456789_123456789_1234 (
733
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
734
key a003_long_123456789_123456789_123456789_123456789_123456789_1234 (
735
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
736
key a004_long_123456789_123456789_123456789_123456789_123456789_1234 (
737
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
738
key a005_long_123456789_123456789_123456789_123456789_123456789_1234 (
739
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
740
key a006_long_123456789_123456789_123456789_123456789_123456789_1234 (
741
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
742
key a007_long_123456789_123456789_123456789_123456789_123456789_1234 (
743
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
744
key a008_long_123456789_123456789_123456789_123456789_123456789_1234 (
745
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
746
key a009_long_123456789_123456789_123456789_123456789_123456789_1234 (
747
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
749
key a010_long_123456789_123456789_123456789_123456789_123456789_1234 (
750
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
751
key a011_long_123456789_123456789_123456789_123456789_123456789_1234 (
752
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
753
key a012_long_123456789_123456789_123456789_123456789_123456789_1234 (
754
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
755
key a013_long_123456789_123456789_123456789_123456789_123456789_1234 (
756
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
757
key a014_long_123456789_123456789_123456789_123456789_123456789_1234 (
758
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
759
key a015_long_123456789_123456789_123456789_123456789_123456789_1234 (
760
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
761
key a016_long_123456789_123456789_123456789_123456789_123456789_1234 (
762
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
763
key a017_long_123456789_123456789_123456789_123456789_123456789_1234 (
764
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
765
key a018_long_123456789_123456789_123456789_123456789_123456789_1234 (
766
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
767
key a019_long_123456789_123456789_123456789_123456789_123456789_1234 (
768
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
770
key a020_long_123456789_123456789_123456789_123456789_123456789_1234 (
771
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
772
key a021_long_123456789_123456789_123456789_123456789_123456789_1234 (
773
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
774
key a022_long_123456789_123456789_123456789_123456789_123456789_1234 (
775
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
776
key a023_long_123456789_123456789_123456789_123456789_123456789_1234 (
777
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
778
key a024_long_123456789_123456789_123456789_123456789_123456789_1234 (
779
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
780
key a025_long_123456789_123456789_123456789_123456789_123456789_1234 (
781
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
782
key a026_long_123456789_123456789_123456789_123456789_123456789_1234 (
783
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
784
key a027_long_123456789_123456789_123456789_123456789_123456789_1234 (
785
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
786
key a028_long_123456789_123456789_123456789_123456789_123456789_1234 (
787
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
788
key a029_long_123456789_123456789_123456789_123456789_123456789_1234 (
789
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
791
key a030_long_123456789_123456789_123456789_123456789_123456789_1234 (
792
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
793
key a031_long_123456789_123456789_123456789_123456789_123456789_1234 (
794
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
795
key a032_long_123456789_123456789_123456789_123456789_123456789_1234 (
796
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
797
key a033_long_123456789_123456789_123456789_123456789_123456789_1234 (
798
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
799
key a034_long_123456789_123456789_123456789_123456789_123456789_1234 (
800
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
801
key a035_long_123456789_123456789_123456789_123456789_123456789_1234 (
802
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
803
key a036_long_123456789_123456789_123456789_123456789_123456789_1234 (
804
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
805
key a037_long_123456789_123456789_123456789_123456789_123456789_1234 (
806
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
807
key a038_long_123456789_123456789_123456789_123456789_123456789_1234 (
808
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
809
key a039_long_123456789_123456789_123456789_123456789_123456789_1234 (
810
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
812
key a040_long_123456789_123456789_123456789_123456789_123456789_1234 (
813
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
814
key a041_long_123456789_123456789_123456789_123456789_123456789_1234 (
815
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
816
key a042_long_123456789_123456789_123456789_123456789_123456789_1234 (
817
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
818
key a043_long_123456789_123456789_123456789_123456789_123456789_1234 (
819
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
820
key a044_long_123456789_123456789_123456789_123456789_123456789_1234 (
821
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
822
key a045_long_123456789_123456789_123456789_123456789_123456789_1234 (
823
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
824
key a046_long_123456789_123456789_123456789_123456789_123456789_1234 (
825
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
826
key a047_long_123456789_123456789_123456789_123456789_123456789_1234 (
827
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
828
key a048_long_123456789_123456789_123456789_123456789_123456789_1234 (
829
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
830
key a049_long_123456789_123456789_123456789_123456789_123456789_1234 (
831
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
833
key a050_long_123456789_123456789_123456789_123456789_123456789_1234 (
834
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
835
key a051_long_123456789_123456789_123456789_123456789_123456789_1234 (
836
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
837
key a052_long_123456789_123456789_123456789_123456789_123456789_1234 (
838
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
839
key a053_long_123456789_123456789_123456789_123456789_123456789_1234 (
840
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
841
key a054_long_123456789_123456789_123456789_123456789_123456789_1234 (
842
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
843
key a055_long_123456789_123456789_123456789_123456789_123456789_1234 (
844
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
845
key a056_long_123456789_123456789_123456789_123456789_123456789_1234 (
846
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
847
key a057_long_123456789_123456789_123456789_123456789_123456789_1234 (
848
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
849
key a058_long_123456789_123456789_123456789_123456789_123456789_1234 (
850
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
851
key a059_long_123456789_123456789_123456789_123456789_123456789_1234 (
852
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
854
key a060_long_123456789_123456789_123456789_123456789_123456789_1234 (
855
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
856
key a061_long_123456789_123456789_123456789_123456789_123456789_1234 (
857
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
858
key a062_long_123456789_123456789_123456789_123456789_123456789_1234 (
859
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
860
key a063_long_123456789_123456789_123456789_123456789_123456789_1234 (
861
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
862
key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (
863
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16)
866
# Check that the table is not corrupted
867
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
868
show create table t1;
870
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
871
show create table t1;
873
# Repeat test using ALTER to add indexes
876
create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int,
877
c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int);
881
add key a001_long_123456789_123456789_123456789_123456789_123456789_1234 (
882
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
883
add key a002_long_123456789_123456789_123456789_123456789_123456789_1234 (
884
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
885
add key a003_long_123456789_123456789_123456789_123456789_123456789_1234 (
886
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
887
add key a004_long_123456789_123456789_123456789_123456789_123456789_1234 (
888
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
889
add key a005_long_123456789_123456789_123456789_123456789_123456789_1234 (
890
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
891
add key a006_long_123456789_123456789_123456789_123456789_123456789_1234 (
892
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
893
add key a007_long_123456789_123456789_123456789_123456789_123456789_1234 (
894
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
895
add key a008_long_123456789_123456789_123456789_123456789_123456789_1234 (
896
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
897
add key a009_long_123456789_123456789_123456789_123456789_123456789_1234 (
898
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
900
add key a010_long_123456789_123456789_123456789_123456789_123456789_1234 (
901
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
902
add key a011_long_123456789_123456789_123456789_123456789_123456789_1234 (
903
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
904
add key a012_long_123456789_123456789_123456789_123456789_123456789_1234 (
905
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
906
add key a013_long_123456789_123456789_123456789_123456789_123456789_1234 (
907
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
908
add key a014_long_123456789_123456789_123456789_123456789_123456789_1234 (
909
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
910
add key a015_long_123456789_123456789_123456789_123456789_123456789_1234 (
911
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
912
add key a016_long_123456789_123456789_123456789_123456789_123456789_1234 (
913
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
914
add key a017_long_123456789_123456789_123456789_123456789_123456789_1234 (
915
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
916
add key a018_long_123456789_123456789_123456789_123456789_123456789_1234 (
917
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
918
add key a019_long_123456789_123456789_123456789_123456789_123456789_1234 (
919
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
921
add key a020_long_123456789_123456789_123456789_123456789_123456789_1234 (
922
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
923
add key a021_long_123456789_123456789_123456789_123456789_123456789_1234 (
924
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
925
add key a022_long_123456789_123456789_123456789_123456789_123456789_1234 (
926
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
927
add key a023_long_123456789_123456789_123456789_123456789_123456789_1234 (
928
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
929
add key a024_long_123456789_123456789_123456789_123456789_123456789_1234 (
930
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
931
add key a025_long_123456789_123456789_123456789_123456789_123456789_1234 (
932
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
933
add key a026_long_123456789_123456789_123456789_123456789_123456789_1234 (
934
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
935
add key a027_long_123456789_123456789_123456789_123456789_123456789_1234 (
936
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
937
add key a028_long_123456789_123456789_123456789_123456789_123456789_1234 (
938
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
939
add key a029_long_123456789_123456789_123456789_123456789_123456789_1234 (
940
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
942
add key a030_long_123456789_123456789_123456789_123456789_123456789_1234 (
943
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
944
add key a031_long_123456789_123456789_123456789_123456789_123456789_1234 (
945
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
946
add key a032_long_123456789_123456789_123456789_123456789_123456789_1234 (
947
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
948
add key a033_long_123456789_123456789_123456789_123456789_123456789_1234 (
949
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
950
add key a034_long_123456789_123456789_123456789_123456789_123456789_1234 (
951
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
952
add key a035_long_123456789_123456789_123456789_123456789_123456789_1234 (
953
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
954
add key a036_long_123456789_123456789_123456789_123456789_123456789_1234 (
955
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
956
add key a037_long_123456789_123456789_123456789_123456789_123456789_1234 (
957
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
958
add key a038_long_123456789_123456789_123456789_123456789_123456789_1234 (
959
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
960
add key a039_long_123456789_123456789_123456789_123456789_123456789_1234 (
961
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
963
add key a040_long_123456789_123456789_123456789_123456789_123456789_1234 (
964
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
965
add key a041_long_123456789_123456789_123456789_123456789_123456789_1234 (
966
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
967
add key a042_long_123456789_123456789_123456789_123456789_123456789_1234 (
968
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
969
add key a043_long_123456789_123456789_123456789_123456789_123456789_1234 (
970
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
971
add key a044_long_123456789_123456789_123456789_123456789_123456789_1234 (
972
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
973
add key a045_long_123456789_123456789_123456789_123456789_123456789_1234 (
974
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
975
add key a046_long_123456789_123456789_123456789_123456789_123456789_1234 (
976
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
977
add key a047_long_123456789_123456789_123456789_123456789_123456789_1234 (
978
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
979
add key a048_long_123456789_123456789_123456789_123456789_123456789_1234 (
980
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
981
add key a049_long_123456789_123456789_123456789_123456789_123456789_1234 (
982
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
984
add key a050_long_123456789_123456789_123456789_123456789_123456789_1234 (
985
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
986
add key a051_long_123456789_123456789_123456789_123456789_123456789_1234 (
987
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
988
add key a052_long_123456789_123456789_123456789_123456789_123456789_1234 (
989
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
990
add key a053_long_123456789_123456789_123456789_123456789_123456789_1234 (
991
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
992
add key a054_long_123456789_123456789_123456789_123456789_123456789_1234 (
993
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
994
add key a055_long_123456789_123456789_123456789_123456789_123456789_1234 (
995
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
996
add key a056_long_123456789_123456789_123456789_123456789_123456789_1234 (
997
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
998
add key a057_long_123456789_123456789_123456789_123456789_123456789_1234 (
999
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1000
add key a058_long_123456789_123456789_123456789_123456789_123456789_1234 (
1001
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1002
add key a059_long_123456789_123456789_123456789_123456789_123456789_1234 (
1003
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1005
add key a060_long_123456789_123456789_123456789_123456789_123456789_1234 (
1006
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1007
add key a061_long_123456789_123456789_123456789_123456789_123456789_1234 (
1008
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1009
add key a062_long_123456789_123456789_123456789_123456789_123456789_1234 (
1010
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1011
add key a063_long_123456789_123456789_123456789_123456789_123456789_1234 (
1012
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1013
add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (
1014
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);
1016
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
1017
show create table t1;
1019
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
1020
show create table t1;
1022
# Test the server limits; if any of these pass, all above tests need
1023
# to be rewritten to hit the limit
1025
# Ensure limit is really 64 keys
1027
alter table t1 add key
1028
a065_long_123456789_123456789_123456789_123456789_123456789_1234 (
1029
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);
1033
# Ensure limit is really 16 key parts per key
1035
create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int,
1036
c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int,
1039
# Get error for max key parts
1041
alter table t1 add key i1 (
1042
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16, c17);
1044
# Get error for max key-name length
1046
alter table t1 add key
1047
a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);
1049
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
1050
show create table t1;
1055
--echo Bug #26104 Bug on foreign key class constructor
1057
--echo Check that ref_columns is initalized correctly in the constructor
1058
--echo and semantic checks in mysql_prepare_table work.
1060
--echo We do not need a storage engine that supports foreign keys
1061
--echo for this test, as the checks are purely syntax-based, and the
1062
--echo syntax is supported for all engines.
1065
drop table if exists t1,t2;
1068
create table t1(a int not null, b int not null, primary key (a, b));
1069
--error ER_WRONG_FK_DEF
1070
create table t2(a int not null, b int not null, c int not null, primary key (a),
1071
foreign key fk_bug26104 (b,c) references t1(a));
1075
# Bug#15130:CREATE .. SELECT was denied to use advantages of the SQL_BIG_RESULT.
1077
create table t1(f1 int,f2 int);
1078
insert into t1 value(1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
1080
create table t2 select sql_big_result f1,count(f2) from t1 group by f1;
1081
show status like 'handler_read%';
1085
# Bug #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs on table creates
1088
# Show that the old syntax for index type is supported
1089
CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1));
1092
# Show that the new syntax for index type is supported
1093
CREATE TABLE t1(c1 VARCHAR(33), KEY (c1) USING BTREE);
1096
# Show that in case of multiple index type definitions, the last one takes
1099
CREATE TEMPORARY TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY;
1100
#SHOW INDEX FROM t1;
1103
CREATE TEMPORARY TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY;
1104
#SHOW INDEX FROM t1;
1108
--echo End of 5.0 tests
1111
# Test of behaviour with CREATE ... SELECT
1114
CREATE TABLE t1 (a int, b int);
1115
insert into t1 values (1,1),(1,2);
1116
--error ER_DUP_ENTRY
1117
CREATE TABLE t2 (primary key (a)) select * from t1;
1118
# This should give warning
1119
drop table if exists t2;
1120
--error ER_DUP_ENTRY
1121
CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
1122
# This should give warning
1123
drop table if exists t2;
1124
# TODO: Bug lp:311072
1125
#CREATE TABLE t2 (a int, b int, primary key (a));
1126
#--error ER_DUP_ENTRY
1127
#CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
1130
#--error ER_DUP_ENTRY
1131
#INSERT INTO t2 select * from t1;
1135
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
1136
--error ER_DUP_ENTRY
1137
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
1140
--error ER_DUP_ENTRY
1141
INSERT INTO t2 select * from t1;
1147
# Test incorrect database names
1151
CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1153
DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1155
# TODO: enable these tests when RENAME DATABASE is implemented.
1157
# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;
1159
# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1160
# create database mysqltest;
1162
# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1163
# drop database mysqltest;
1166
USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1168
SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1171
## Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte
1174
create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
1175
use имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
1179
select SCHEMA_NAME from data_dictionary.schemas
1180
where schema_name='имя_базы_в_кодировке_утф8_длиной_больше_чем_45';
1182
drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
1183
create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48
1185
имя_поля_в_кодировке_утф8_длиной_больше_чем_45 int,
1186
index имя_индекса_в_кодировке_утф8_длиной_больше_чем_48 (имя_поля_в_кодировке_утф8_длиной_больше_чем_45)
1190
# database, table, field, key
1191
select * from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1193
select TABLE_NAME from data_dictionary.tables where
1194
table_schema='test';
1196
select COLUMN_NAME from data_dictionary.columns where
1197
table_schema='test';
1199
select INDEX_NAME from data_dictionary.indexes where
1200
table_schema='test';
1202
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
1203
show create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1205
drop table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1209
# Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA
1212
create table t1 like data_dictionary.processlist;
1213
create table t1 like data_dictionary.processlist engine=innodb;
1214
show create table t1;
1217
create temporary table t1 like data_dictionary.processlist;
1218
create temporary table t1 like data_dictionary.processlist engine=myisam;
1219
show create table t1;
1222
###########################################################################
1226
--echo # -- Bug#21380: DEFAULT definition not always transfered by CREATE
1227
--echo # -- TABLE/SELECT to the new table.
1233
DROP TABLE IF EXISTS t1;
1234
DROP TABLE IF EXISTS t2;
1240
c1 INT DEFAULT 12 COMMENT 'column1',
1241
c2 INT NULL COMMENT 'column2',
1242
c3 INT NOT NULL COMMENT 'column3',
1243
c4 VARCHAR(255) NOT NULL DEFAULT 'a',
1244
c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b',
1250
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
1251
SHOW CREATE TABLE t1;
1255
CREATE TABLE t2 AS SELECT * FROM t1;
1259
--replace_regex /ENGINE=[a-zA-Z]+/ENGINE=DEFAULT/
1260
SHOW CREATE TABLE t2;
1267
--echo # -- End of test case for Bug#21380.
1269
###########################################################################
1273
--echo # -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields
1278
DROP TABLE IF EXISTS t1;
1279
DROP TABLE IF EXISTS t2;
1280
DROP TABLE IF EXISTS t3;
1285
CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);
1290
CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP NULL);
1293
CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT '1982-01-29');
1297
CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);
1301
--echo # -- Check that NULL column still can be created.
1302
CREATE TABLE t2(c1 TIMESTAMP NULL);
1305
--echo # -- Check ALTER TABLE.
1306
ALTER TABLE t1 ADD INDEX(c1);
1309
--echo # -- Check DATETIME.
1312
CREATE TABLE t3(c1 DATETIME NOT NULL);
1313
--error 1686 # Bad datetime
1314
INSERT INTO t3 VALUES (0);
1317
ALTER TABLE t3 ADD INDEX(c1);
1320
--echo # -- Cleanup.
1327
--echo # -- End of Bug#18834.
added
removed
tests/t/create.test
