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);
17
insert into t1 values (""),(null);
21
create table t1 (a int not null auto_increment,primary key (a)) engine=heap;
25
# Test of some CREATE TABLE'S that should fail
29
create table t2 engine=heap select * from t1;
31
create table t2 select auto+1 from t1;
32
drop table if exists t1,t2;
34
create table t1 (b char(0) not null, index(b));
36
create table t1 (a int not null,b text) engine=heap;
37
drop table if exists t1;
40
create table t1 (ordid int(8) not null auto_increment, ord varchar(50) not null, primary key (ord,ordid)) engine=heap;
43
create table not_existing_database.test (a int);
44
create table `a/a` (a int);
45
show create table `a/a`;
46
create table t1 like `a/a`;
50
create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);
52
create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);
55
# Some wrong defaults, so these creates should fail too (Bug #5902)
58
create table t1 (a datetime default now());
60
create table t1 (a datetime on update now());
62
create table t1 (a int default 100 auto_increment);
64
create table t1 (a tinyint default 1000);
66
create table t1 (a varchar(5) default 'abcdef');
68
create table t1 (a varchar(5) default 'abcde');
69
insert into t1 values();
72
alter table t1 alter column a set default 'abcdef';
76
# test of dummy table names
79
create table 1ea10 (1a20 int,1e int);
80
insert into 1ea10 values(1,1);
81
select 1ea10.1a20,1e+ 1e+10 from 1ea10;
83
create table t1 (t1.index int);
85
# Test that we get warning for this
86
drop database if exists mysqltest;
87
create database mysqltest;
88
create table mysqltest.$test1 (a$1 int, $b int, c$ int);
89
insert into mysqltest.$test1 values (1,2,3);
90
select a$1, $b, c$ from mysqltest.$test1;
91
create table mysqltest.test2$ (a int);
92
drop table mysqltest.test2$;
93
drop database mysqltest;
96
create table `` (a int);
98
drop table if exists ``;
100
create table t1 (`` int);
102
create table t1 (i int, index `` (i));
105
# Test of CREATE ... SELECT with indexes
108
create table t1 (a int auto_increment not null primary key, B CHAR(20));
109
insert into t1 (b) values ("hello"),("my"),("world");
110
create table t2 (key (b)) select * from t1;
111
explain select * from t2 where b="world";
112
select * from t2 where b="world";
116
# Test types after CREATE ... SELECT
119
create table t1(x varchar(50) );
120
create table t2 select x from t1 where 1=2;
124
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;
127
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;
132
# Test of CREATE ... SELECT with duplicate fields
135
create table t1 (a tinyint);
136
create table t2 (a int) select * from t1;
139
drop table if exists t2;
141
create table t2 (a int, a float) select * from t1;
142
drop table if exists t2;
144
create table t2 (a int) select a as b, a+1 as b from t1;
145
drop table if exists t2;
147
create table t2 (b int) select a as b, a+1 as b from t1;
148
drop table if exists t1,t2;
151
# Test CREATE ... SELECT when insert fails
154
CREATE TABLE t1 (a int not null);
155
INSERT INTO t1 values (1),(2),(1);
157
CREATE TABLE t2 (primary key(a)) SELECT * FROM t1;
161
DROP TABLE IF EXISTS t2;
164
# Test of primary key with 32 index
167
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));
168
show create table t1;
170
create table t1 select if(1,'1','0'), month("2002-08-02");
172
create table t1 select if('2002'='2002','Y','N');
174
drop table if exists t1;
177
# Test default table type
179
SET SESSION storage_engine="heap";
180
SELECT @@storage_engine;
181
CREATE TABLE t1 (a int not null);
182
show create table t1;
185
SET SESSION storage_engine="gemini";
186
SELECT @@storage_engine;
187
CREATE TABLE t1 (a int not null);
188
show create table t1;
189
SET SESSION storage_engine=default;
194
# ISO requires that primary keys are implicitly NOT NULL
196
create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));
197
insert into t1 values ("a", 1), ("b", 2);
199
insert into t1 values ("c", NULL);
201
insert into t1 values (NULL, 3);
203
insert into t1 values (NULL, NULL);
210
create table t1 select x'4132';
217
create table t1 select 1,2,3;
218
create table if not exists t1 select 1,2;
220
create table if not exists t1 select 1,2,3,4;
221
create table if not exists t1 select 1;
226
# Test create table if not exists with duplicate key error
230
create table t1 (a int not null, b int, primary key (a));
231
insert into t1 values (1,1);
232
create table if not exists t1 select 2;
234
create table if not exists t1 select 3 as 'a',4 as 'b';
236
create table if not exists t1 select 3 as 'a',3 as 'b';
238
show status like "Opened_tables";
244
# "Table truncated when creating another table name with Spaces"
248
create table `t1 `(a int);
250
create database `db1 `;
252
create table t1(`a ` int);
256
# "Parser permits multiple commas without syntax error"
260
create table t1 (a int,);
262
create table t1 (a int,,b int);
264
create table t1 (,b int);
267
# Test create with foreign keys
270
create table t1 (a int, key(a));
271
create table t2 (b int, foreign key(b) references t1(a), key(b));
272
drop table if exists t1,t2;
275
# Test for CREATE TABLE .. LIKE ..
278
create table t1(id int not null, name char(20));
279
insert into t1 values(10,'mysql'),(20,'monty- the creator');
280
create table t2(id int not null);
281
insert into t2 values(10),(20);
282
create table t3 like t1;
283
show create table t3;
285
# Disable PS becasue of @@warning_count
286
create table if not exists t3 like t1;
287
--disable_ps_protocol
288
select @@warning_count;
290
create temporary table t3 like t2;
291
show create table t3;
294
show create table t3;
297
create database mysqltest;
298
create table mysqltest.t3 like t1;
299
create temporary table t3 like mysqltest.t3;
300
show create table t3;
301
create table t2 like t3;
302
show create table t2;
304
create table t3 like t1;
306
create table t3 like mysqltest.t3;
308
create table non_existing_database.t1 like t1;
309
--error ER_NO_SUCH_TABLE
310
create table t3 like non_existing_table;
312
create temporary table t3 like t1;
313
drop table t1, t2, t3;
315
drop database mysqltest;
318
# Test default table type
320
SET SESSION storage_engine="heap";
321
SELECT @@storage_engine;
322
CREATE TABLE t1 (a int not null);
323
show create table t1;
326
SET SESSION storage_engine="gemini";
327
SELECT @@storage_engine;
328
CREATE TABLE t1 (a int not null);
329
show create table t1;
330
SET SESSION storage_engine=default;
334
# Test types of data for create select with functions
337
create table t1(a int,b int,c int unsigned,d date,e char,f datetime,g time,h blob);
338
insert into t1(a)values(1);
339
insert into t1(a,b,c,d,e,f,g,h)
340
values(2,-2,2,'1825-12-14','a','2003-1-1 3:2:1','4:3:2','binary data');
343
ifnull(b,cast(-7 as signed)) as b,
344
ifnull(c,cast(7 as unsigned)) as c,
345
ifnull(d,cast('2000-01-01' as date)) as d,
346
ifnull(e,cast('b' as char)) as e,
347
ifnull(f,cast('2000-01-01' as datetime)) as f,
348
ifnull(g,cast('5:4:3' as time)) as g,
349
ifnull(h,cast('yet another binary data' as binary)) as h,
350
addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd
356
ifnull(b,cast(-7 as signed)) as b,
357
ifnull(c,cast(7 as unsigned)) as c,
358
ifnull(d,cast('2000-01-01' as date)) as d,
359
ifnull(e,cast('b' as char)) as e,
360
ifnull(f,cast('2000-01-01' as datetime)) as f,
361
ifnull(g,cast('5:4:3' as time)) as g,
362
ifnull(h,cast('yet another binary data' as binary)) as h,
363
addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd
369
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));
370
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;
371
show create table t2;
377
create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');
378
insert into t1 values ('','',0,0.0);
380
create table t2 select default(str) as str, default(strnull) as strnull, default(intg) as intg, default(rel) as rel from t1;
388
create table t1(name varchar(10), age smallint default -1);
390
create table t2(name varchar(10), age smallint default - 1);
395
# test for bug #1427 "enum allows duplicate values in the list"
398
create table t1(cenum enum('a'), cset set('b'));
399
create table t2(cenum enum('a','a'), cset set('b','b'));
400
create table t3(cenum enum('a','A','a','c','c'), cset set('b','B','b','d','d'));
401
drop table t1, t2, t3;
407
create database mysqltest;
410
drop database mysqltest;
413
# Connect without a database as user mysqltest_1
414
create user mysqltest_1;
415
connect (user1,localhost,mysqltest_1,,*NO-ONE*);
417
select database(), user();
420
drop user mysqltest_1;
424
# Test for Bug 856 'Naming a key "Primary" causes trouble'
428
create table t1 (a int, index `primary` (a));
430
create table t1 (a int, index `PRIMARY` (a));
432
create table t1 (`primary` int, index(`primary`));
433
show create table t1;
434
create table t2 (`PRIMARY` int, index(`PRIMARY`));
435
show create table t2;
437
create table t3 (a int);
439
alter table t3 add index `primary` (a);
441
alter table t3 add index `PRIMARY` (a);
443
create table t4 (`primary` int);
444
alter table t4 add index(`primary`);
445
show create table t4;
446
create table t5 (`PRIMARY` int);
447
alter table t5 add index(`PRIMARY`);
448
show create table t5;
450
drop table t1, t2, t3, t4, t5;
453
# bug #3266 TEXT in CREATE TABLE SELECT
456
CREATE TABLE t1(id varchar(10) NOT NULL PRIMARY KEY, dsc longtext);
457
INSERT INTO t1 VALUES ('5000000001', NULL),('5000000003', 'Test'),('5000000004', NULL);
458
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));
460
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');
462
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;
464
drop table t1, t2, t3;
467
# Bug#9666: Can't use 'DEFAULT FALSE' for column of type bool
469
create table t1 (b bool not null default false);
470
create table t2 (b bool not null default true);
471
insert into t1 values ();
472
insert into t2 values ();
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
--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) CHARACTER SET utf8);
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;
533
# Bug #6859: Bogus error message on attempt to CREATE TABLE t LIKE view
535
create database mysqltest;
537
create view v1 as select 'foo' from dual;
539
create table t1 like v1;
541
drop database mysqltest;
542
# Bug #6008 MySQL does not create warnings when
543
# creating database and using IF NOT EXISTS
545
create database mysqltest;
546
create database if not exists mysqltest character set latin2;
547
show create database mysqltest;
548
drop database mysqltest;
550
create table t1 (a int);
551
create table if not exists t1 (a int);
556
a varchar(112) charset utf8 collate utf8_bin not null,
558
) select 'test' as a ;
560
show create table t1;
564
# BUG#14480: assert failure in CREATE ... SELECT because of wrong
565
# calculation of number of NULLs.
568
a int(11) default NULL
570
insert into t2 values(111);
574
a varchar(12) charset utf8 collate utf8_bin not null,
575
b int not null, primary key (a)
576
) select a, 1 as b from t2 ;
577
show create table t1;
582
a varchar(12) charset utf8 collate utf8_bin not null,
583
b int not null, primary key (a)
584
) select a, 1 as c from t2 ;
585
show create table t1;
590
a varchar(12) charset utf8 collate utf8_bin not null,
591
b int null, primary key (a)
592
) select a, 1 as c from t2 ;
593
show create table t1;
598
a varchar(12) charset utf8 collate utf8_bin not null,
599
b int not null, primary key (a)
600
) select 'a' as a , 1 as b from t2 ;
601
show create table t1;
606
a varchar(12) charset utf8 collate utf8_bin,
607
b int not null, primary key (a)
608
) select 'a' as a , 1 as b from t2 ;
609
show create table t1;
614
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
616
insert into t1 values (1,1,1, 1,1,1, 1,1,1);
620
a1 varchar(12) charset utf8 collate utf8_bin not null,
621
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,
623
) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;
628
a1 varchar(12) charset utf8 collate utf8_bin,
629
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
630
) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1;
635
a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
637
insert into t1 values (1,1,1, 1,1,1, 1,1,1);
641
a1 varchar(12) charset utf8 collate utf8_bin not null,
642
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,
644
) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;
646
# Test the default value
649
create table t2 ( a int default 3, b int default 3)
650
select a1,a2 from t1;
651
show create table t2;
656
# Bug #15316 SET value having comma not correctly handled
659
create table t1(a set("a,b","c,d") not null);
665
# Bug #14155: Maximum value of MAX_ROWS handled incorrectly on 64-bit
668
create table t1 (i int) engine=myisam max_rows=100000000000;
669
show create table t1;
670
alter table t1 max_rows=100;
671
show create table t1;
672
alter table t1 max_rows=100000000000;
673
show create table t1;
678
# Tests for errors happening at various stages of CREATE TABLES ... SELECT
680
# (Also checks that it behaves atomically in the sense that in case
681
# of error it is automatically dropped if it has not existed before.)
683
# Error during open_and_lock_tables() of tables
684
--error ER_NO_SUCH_TABLE
685
create table t1 select * from t2;
686
# Rather special error which also caught during open tables pahse
687
--error ER_UPDATE_TABLE_USED
688
create table t1 select * from t1;
689
# Error which happens before select_create::prepare()
690
--error ER_CANT_AGGREGATE_2COLLATIONS
691
create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
692
# Error during table creation
693
--error ER_KEY_COLUMN_DOES_NOT_EXITS
694
create table t1 (primary key(a)) select "b" as b;
695
# Error in select_create::prepare() which is not related to table creation
696
create table t1 (a int);
697
--error ER_WRONG_VALUE_COUNT_ON_ROW
698
create table if not exists t1 select 1 as a, 2 as b;
700
# Finally error which happens during insert
702
create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);
703
# What happens if table already exists ?
704
create table t1 (i int);
705
--error ER_TABLE_EXISTS_ERROR
706
create table t1 select 1 as i;
707
create table if not exists t1 select 1 as i;
709
# Error before select_create::prepare()
710
--error ER_CANT_AGGREGATE_2COLLATIONS
711
create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
713
# Error which happens during insertion of rows
714
alter table t1 add primary key (i);
716
create table if not exists t1 (select 2 as i) union all (select 2 as i);
721
# Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent
722
# results of CREATE TABLE ... SELECT when temporary table exists").
723
# In this situation we either have to create non-temporary table and
724
# insert data in it or insert data in temporary table without creation
725
# of permanent table. Since currently temporary tables always shadow
726
# permanent tables we adopt second approach.
727
create temporary table t1 (j int);
728
create table if not exists t1 select 1;
730
drop temporary table t1;
731
--error ER_NO_SUCH_TABLE
733
--error ER_BAD_TABLE_ERROR
738
# CREATE TABLE ... SELECT and LOCK TABLES
740
# There is little sense in using CREATE TABLE ... SELECT under
741
# LOCK TABLES as it mostly does not work. At least we check that
742
# the server doesn't crash, hang and produces sensible errors.
743
# Includes test for bug #20662 "Infinite loop in CREATE TABLE
744
# IF NOT EXISTS ... SELECT with locked tables".
745
create table t1 (i int);
746
insert into t1 values (1), (2);
748
--error ER_TABLE_NOT_LOCKED
749
create table t2 select * from t1;
750
--error ER_TABLE_NOT_LOCKED
751
create table if not exists t2 select * from t1;
753
create table t2 (j int);
755
--error ER_TABLE_NOT_LOCKED
756
create table t2 select * from t1;
757
# This should not be ever allowed as it will undermine
758
# lock-all-at-once approach
759
--error ER_TABLE_NOT_LOCKED
760
create table if not exists t2 select * from t1;
762
lock table t1 read, t2 read;
763
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
764
create table t2 select * from t1;
765
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
766
create table if not exists t2 select * from t1;
768
lock table t1 read, t2 write;
769
--error ER_TABLE_EXISTS_ERROR
770
create table t2 select * from t1;
771
# This is the only case which really works.
772
create table if not exists t2 select * from t1;
777
# OTOH CREATE TEMPORARY TABLE ... SELECT should work
778
# well under LOCK TABLES.
780
create temporary table t2 select * from t1;
781
create temporary table if not exists t2 select * from t1;
788
# Bug#21772: can not name a column 'upgrade' when create a table
790
create table t1 (upgrade int);
795
# Bug #26642: create index corrupts table definition in .frm
797
# Problem with creating keys with maximum key-parts and maximum name length
798
# This test is made for a mysql server supporting names up to 64 bytes
799
# and a maximum of 16 key segements per Key
803
c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int,
804
c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int,
806
key a001_long_123456789_123456789_123456789_123456789_123456789_1234 (
807
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
808
key a002_long_123456789_123456789_123456789_123456789_123456789_1234 (
809
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
810
key a003_long_123456789_123456789_123456789_123456789_123456789_1234 (
811
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
812
key a004_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 a005_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 a006_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 a007_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 a008_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 a009_long_123456789_123456789_123456789_123456789_123456789_1234 (
823
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
825
key a010_long_123456789_123456789_123456789_123456789_123456789_1234 (
826
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
827
key a011_long_123456789_123456789_123456789_123456789_123456789_1234 (
828
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
829
key a012_long_123456789_123456789_123456789_123456789_123456789_1234 (
830
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
831
key a013_long_123456789_123456789_123456789_123456789_123456789_1234 (
832
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
833
key a014_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 a015_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 a016_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 a017_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 a018_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 a019_long_123456789_123456789_123456789_123456789_123456789_1234 (
844
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
846
key a020_long_123456789_123456789_123456789_123456789_123456789_1234 (
847
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
848
key a021_long_123456789_123456789_123456789_123456789_123456789_1234 (
849
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
850
key a022_long_123456789_123456789_123456789_123456789_123456789_1234 (
851
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
852
key a023_long_123456789_123456789_123456789_123456789_123456789_1234 (
853
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
854
key a024_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 a025_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 a026_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 a027_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 a028_long_123456789_123456789_123456789_123456789_123456789_1234 (
863
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
864
key a029_long_123456789_123456789_123456789_123456789_123456789_1234 (
865
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
867
key a030_long_123456789_123456789_123456789_123456789_123456789_1234 (
868
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
869
key a031_long_123456789_123456789_123456789_123456789_123456789_1234 (
870
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
871
key a032_long_123456789_123456789_123456789_123456789_123456789_1234 (
872
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
873
key a033_long_123456789_123456789_123456789_123456789_123456789_1234 (
874
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
875
key a034_long_123456789_123456789_123456789_123456789_123456789_1234 (
876
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
877
key a035_long_123456789_123456789_123456789_123456789_123456789_1234 (
878
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
879
key a036_long_123456789_123456789_123456789_123456789_123456789_1234 (
880
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
881
key a037_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
key a038_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
key a039_long_123456789_123456789_123456789_123456789_123456789_1234 (
886
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
888
key a040_long_123456789_123456789_123456789_123456789_123456789_1234 (
889
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
890
key a041_long_123456789_123456789_123456789_123456789_123456789_1234 (
891
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
892
key a042_long_123456789_123456789_123456789_123456789_123456789_1234 (
893
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
894
key a043_long_123456789_123456789_123456789_123456789_123456789_1234 (
895
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
896
key a044_long_123456789_123456789_123456789_123456789_123456789_1234 (
897
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
898
key a045_long_123456789_123456789_123456789_123456789_123456789_1234 (
899
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
900
key a046_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
key a047_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
key a048_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
key a049_long_123456789_123456789_123456789_123456789_123456789_1234 (
907
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
909
key a050_long_123456789_123456789_123456789_123456789_123456789_1234 (
910
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
911
key a051_long_123456789_123456789_123456789_123456789_123456789_1234 (
912
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
913
key a052_long_123456789_123456789_123456789_123456789_123456789_1234 (
914
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
915
key a053_long_123456789_123456789_123456789_123456789_123456789_1234 (
916
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
917
key a054_long_123456789_123456789_123456789_123456789_123456789_1234 (
918
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
919
key a055_long_123456789_123456789_123456789_123456789_123456789_1234 (
920
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
921
key a056_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
key a057_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
key a058_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
key a059_long_123456789_123456789_123456789_123456789_123456789_1234 (
928
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
930
key a060_long_123456789_123456789_123456789_123456789_123456789_1234 (
931
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
932
key a061_long_123456789_123456789_123456789_123456789_123456789_1234 (
933
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
934
key a062_long_123456789_123456789_123456789_123456789_123456789_1234 (
935
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
936
key a063_long_123456789_123456789_123456789_123456789_123456789_1234 (
937
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
938
key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (
939
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16)
942
# Check that the table is not corrupted
943
show create table t1;
945
show create table t1;
947
# Repeat test using ALTER to add indexes
950
create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int,
951
c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int);
955
add key a001_long_123456789_123456789_123456789_123456789_123456789_1234 (
956
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
957
add key a002_long_123456789_123456789_123456789_123456789_123456789_1234 (
958
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
959
add key a003_long_123456789_123456789_123456789_123456789_123456789_1234 (
960
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
961
add key a004_long_123456789_123456789_123456789_123456789_123456789_1234 (
962
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
963
add key a005_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 a006_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 a007_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 a008_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 a009_long_123456789_123456789_123456789_123456789_123456789_1234 (
972
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
974
add key a010_long_123456789_123456789_123456789_123456789_123456789_1234 (
975
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
976
add key a011_long_123456789_123456789_123456789_123456789_123456789_1234 (
977
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
978
add key a012_long_123456789_123456789_123456789_123456789_123456789_1234 (
979
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
980
add key a013_long_123456789_123456789_123456789_123456789_123456789_1234 (
981
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
982
add key a014_long_123456789_123456789_123456789_123456789_123456789_1234 (
983
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
984
add key a015_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 a016_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 a017_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 a018_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 a019_long_123456789_123456789_123456789_123456789_123456789_1234 (
993
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
995
add key a020_long_123456789_123456789_123456789_123456789_123456789_1234 (
996
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
997
add key a021_long_123456789_123456789_123456789_123456789_123456789_1234 (
998
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
999
add key a022_long_123456789_123456789_123456789_123456789_123456789_1234 (
1000
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1001
add key a023_long_123456789_123456789_123456789_123456789_123456789_1234 (
1002
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1003
add key a024_long_123456789_123456789_123456789_123456789_123456789_1234 (
1004
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1005
add key a025_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 a026_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 a027_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 a028_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 a029_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
add key a030_long_123456789_123456789_123456789_123456789_123456789_1234 (
1017
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1018
add key a031_long_123456789_123456789_123456789_123456789_123456789_1234 (
1019
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1020
add key a032_long_123456789_123456789_123456789_123456789_123456789_1234 (
1021
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1022
add key a033_long_123456789_123456789_123456789_123456789_123456789_1234 (
1023
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1024
add key a034_long_123456789_123456789_123456789_123456789_123456789_1234 (
1025
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1026
add key a035_long_123456789_123456789_123456789_123456789_123456789_1234 (
1027
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1028
add key a036_long_123456789_123456789_123456789_123456789_123456789_1234 (
1029
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1030
add key a037_long_123456789_123456789_123456789_123456789_123456789_1234 (
1031
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1032
add key a038_long_123456789_123456789_123456789_123456789_123456789_1234 (
1033
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1034
add key a039_long_123456789_123456789_123456789_123456789_123456789_1234 (
1035
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1037
add key a040_long_123456789_123456789_123456789_123456789_123456789_1234 (
1038
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1039
add key a041_long_123456789_123456789_123456789_123456789_123456789_1234 (
1040
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1041
add key a042_long_123456789_123456789_123456789_123456789_123456789_1234 (
1042
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1043
add key a043_long_123456789_123456789_123456789_123456789_123456789_1234 (
1044
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1045
add key a044_long_123456789_123456789_123456789_123456789_123456789_1234 (
1046
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1047
add key a045_long_123456789_123456789_123456789_123456789_123456789_1234 (
1048
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1049
add key a046_long_123456789_123456789_123456789_123456789_123456789_1234 (
1050
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1051
add key a047_long_123456789_123456789_123456789_123456789_123456789_1234 (
1052
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1053
add key a048_long_123456789_123456789_123456789_123456789_123456789_1234 (
1054
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1055
add key a049_long_123456789_123456789_123456789_123456789_123456789_1234 (
1056
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1058
add key a050_long_123456789_123456789_123456789_123456789_123456789_1234 (
1059
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1060
add key a051_long_123456789_123456789_123456789_123456789_123456789_1234 (
1061
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1062
add key a052_long_123456789_123456789_123456789_123456789_123456789_1234 (
1063
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1064
add key a053_long_123456789_123456789_123456789_123456789_123456789_1234 (
1065
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1066
add key a054_long_123456789_123456789_123456789_123456789_123456789_1234 (
1067
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1068
add key a055_long_123456789_123456789_123456789_123456789_123456789_1234 (
1069
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1070
add key a056_long_123456789_123456789_123456789_123456789_123456789_1234 (
1071
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1072
add key a057_long_123456789_123456789_123456789_123456789_123456789_1234 (
1073
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1074
add key a058_long_123456789_123456789_123456789_123456789_123456789_1234 (
1075
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1076
add key a059_long_123456789_123456789_123456789_123456789_123456789_1234 (
1077
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1079
add key a060_long_123456789_123456789_123456789_123456789_123456789_1234 (
1080
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1081
add key a061_long_123456789_123456789_123456789_123456789_123456789_1234 (
1082
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1083
add key a062_long_123456789_123456789_123456789_123456789_123456789_1234 (
1084
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1085
add key a063_long_123456789_123456789_123456789_123456789_123456789_1234 (
1086
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1087
add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (
1088
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);
1090
show create table t1;
1092
show create table t1;
1094
# Test the server limits; if any of these pass, all above tests need
1095
# to be rewritten to hit the limit
1097
# Ensure limit is really 64 keys
1099
alter table t1 add key
1100
a065_long_123456789_123456789_123456789_123456789_123456789_1234 (
1101
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);
1105
# Ensure limit is really 16 key parts per key
1107
create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int,
1108
c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int,
1111
# Get error for max key parts
1113
alter table t1 add key i1 (
1114
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16, c17);
1116
# Get error for max key-name length
1118
alter table t1 add key
1119
a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);
1121
show create table t1;
1126
--echo Bug #26104 Bug on foreign key class constructor
1128
--echo Check that ref_columns is initalized correctly in the constructor
1129
--echo and semantic checks in mysql_prepare_table work.
1131
--echo We do not need a storage engine that supports foreign keys
1132
--echo for this test, as the checks are purely syntax-based, and the
1133
--echo syntax is supported for all engines.
1136
drop table if exists t1,t2;
1139
create table t1(a int not null, b int not null, primary key (a, b));
1140
--error ER_WRONG_FK_DEF
1141
create table t2(a int not null, b int not null, c int not null, primary key (a),
1142
foreign key fk_bug26104 (b,c) references t1(a));
1146
# Bug#15130:CREATE .. SELECT was denied to use advantages of the SQL_BIG_RESULT.
1148
create table t1(f1 int,f2 int);
1149
insert into t1 value(1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
1151
create table t2 select sql_big_result f1,count(f2) from t1 group by f1;
1152
show status like 'handler_read%';
1156
# Bug #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs on table creates
1159
# Show that the old syntax for index type is supported
1160
CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1));
1163
# Show that the new syntax for index type is supported
1164
CREATE TABLE t1(c1 VARCHAR(33), KEY (c1) USING BTREE);
1167
# Show that in case of multiple index type definitions, the last one takes
1170
CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY;
1174
CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY;
1179
--echo End of 5.0 tests
1182
# Test of behaviour with CREATE ... SELECT
1185
CREATE TABLE t1 (a int, b int);
1186
insert into t1 values (1,1),(1,2);
1187
--error ER_DUP_ENTRY
1188
CREATE TABLE t2 (primary key (a)) select * from t1;
1189
# This should give warning
1190
drop table if exists t2;
1191
--error ER_DUP_ENTRY
1192
CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
1193
# This should give warning
1194
drop table if exists t2;
1195
CREATE TABLE t2 (a int, b int, primary key (a));
1196
--error ER_DUP_ENTRY
1197
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
1200
--error ER_DUP_ENTRY
1201
INSERT INTO t2 select * from t1;
1205
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
1206
--error ER_DUP_ENTRY
1207
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
1210
--error ER_DUP_ENTRY
1211
INSERT INTO t2 select * from t1;
1217
# Test incorrect database names
1221
CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1223
DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1225
# TODO: enable these tests when RENAME DATABASE is implemented.
1227
# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;
1229
# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1230
# create database mysqltest;
1232
# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1233
# drop database mysqltest;
1236
USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1238
SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1241
# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte
1245
create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
1246
use имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
1250
select SCHEMA_NAME from information_schema.schemata
1251
where schema_name='имя_базы_в_кодировке_утф8_длиной_больше_чем_45';
1253
drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
1254
create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48
1256
имя_поля_в_кодировке_утф8_длиной_больше_чем_45 int,
1257
index имя_индекса_в_кодировке_утф8_длиной_больше_чем_48 (имя_поля_в_кодировке_утф8_длиной_больше_чем_45)
1260
create view имя_вью_кодировке_утф8_длиной_больше_чем_42 as
1261
select имя_поля_в_кодировке_утф8_длиной_больше_чем_45
1262
from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1264
# database, table, field, key, view
1265
select * from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1267
select TABLE_NAME from information_schema.tables where
1268
table_schema='test';
1270
select COLUMN_NAME from information_schema.columns where
1271
table_schema='test';
1273
select INDEX_NAME from information_schema.statistics where
1274
table_schema='test';
1276
select TABLE_NAME from information_schema.views where
1277
table_schema='test';
1279
show create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1280
show create view имя_вью_кодировке_утф8_длиной_больше_чем_42;
1282
# procedure, function, trigger
1284
create trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49
1285
before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1;
1286
select TRIGGER_NAME from information_schema.triggers where
1287
trigger_schema='test';
1288
drop trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49;
1291
очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66
1292
before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1;
1294
drop trigger очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66;
1296
create procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50()
1299
select ROUTINE_NAME from information_schema.routines where
1300
routine_schema='test';
1301
drop procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50;
1303
create procedure очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66()
1307
create function имя_функции_в_кодировке_утф8_длиной_больше_чем_49()
1310
select ROUTINE_NAME from information_schema.routines where
1311
routine_schema='test';
1312
drop function имя_функции_в_кодировке_утф8_длиной_больше_чем_49;
1314
create function очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66()
1318
drop view имя_вью_кодировке_утф8_длиной_больше_чем_42;
1319
drop table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1323
# Bug#21136 CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash
1327
drop table if exists t1,t2,t3;
1328
drop function if exists f1;
1332
create function f1() returns int
1335
create temporary table t3 select 1 i;
1336
set res:= (select count(*) from t1);
1337
drop temporary table t3;
1341
create table t1 as select 1;
1342
create table t2 as select f1() from t1;
1347
# Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA
1349
create table t1 like information_schema.processlist;
1350
show create table t1;
1352
create temporary table t1 like information_schema.processlist;
1353
show create table t1;
1355
create table t1 like information_schema.character_sets;
1356
show create table t1;
1359
###########################################################################
1363
--echo # -- Bug#21380: DEFAULT definition not always transfered by CREATE
1364
--echo # -- TABLE/SELECT to the new table.
1370
DROP TABLE IF EXISTS t1;
1371
DROP TABLE IF EXISTS t2;
1377
c1 INT DEFAULT 12 COMMENT 'column1',
1378
c2 INT NULL COMMENT 'column2',
1379
c3 INT NOT NULL COMMENT 'column3',
1380
c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a',
1381
c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b',
1387
SHOW CREATE TABLE t1;
1391
CREATE TABLE t2 AS SELECT * FROM t1;
1395
SHOW CREATE TABLE t2;
1402
--echo # -- End of test case for Bug#21380.
1404
###########################################################################
1408
--echo # -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields
1413
DROP TABLE IF EXISTS t1;
1414
DROP TABLE IF EXISTS t2;
1415
DROP TABLE IF EXISTS t3;
1420
CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);
1424
SET sql_mode = NO_ZERO_DATE;
1427
--error ER_INVALID_DEFAULT
1428
CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);
1431
--error ER_INVALID_DEFAULT
1432
CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);
1435
--echo # -- Check that NULL column still can be created.
1436
CREATE TABLE t2(c1 TIMESTAMP NULL);
1439
--echo # -- Check ALTER TABLE.
1440
--error ER_INVALID_DEFAULT
1441
ALTER TABLE t1 ADD INDEX(c1);
1444
--echo # -- Check DATETIME.
1449
CREATE TABLE t3(c1 DATETIME NOT NULL);
1450
INSERT INTO t3 VALUES (0);
1453
SET sql_mode = TRADITIONAL;
1456
--error ER_TRUNCATED_WRONG_VALUE
1457
ALTER TABLE t3 ADD INDEX(c1);
1460
--echo # -- Cleanup.
1468
--echo # -- End of Bug#18834.
1470
###########################################################################
1474
--echo # -- Bug#34274: Invalid handling of 'DEFAULT 0' for YEAR data type.
1479
DROP TABLE IF EXISTS t1;
1483
CREATE TABLE t1(c1 YEAR DEFAULT 2008, c2 YEAR DEFAULT 0);
1486
SHOW CREATE TABLE t1;
1489
INSERT INTO t1 VALUES();
1495
ALTER TABLE t1 MODIFY c1 YEAR DEFAULT 0;
1498
SHOW CREATE TABLE t1;
1501
INSERT INTO t1 VALUES();
1510
--echo # -- End of Bug#34274
1512
###########################################################################
1515
--echo End of 5.1 tests
added
removed
mysql-test/t/create.test
