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;
42
create table not_existing_database.test (a int);
43
create table `a/a` (a int);
44
show create table `a/a`;
45
create table t1 like `a/a`;
49
create table `aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` (aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa int);
51
create table a (`aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa` int);
54
# Some wrong defaults, so these creates should fail too (Bug #5902)
57
create table t1 (a datetime default now());
59
create table t1 (a datetime on update now());
61
create table t1 (a int default 100 auto_increment);
63
create table t1 (a int default 1000);
65
create table t1 (a varchar(5) default 'abcdef');
67
create table t1 (a varchar(5) default 'abcde');
68
insert into t1 values();
71
alter table t1 alter column a set default 'abcdef';
75
# test of dummy table names
78
create table 1ea10 (1a20 int,1e int);
79
insert into 1ea10 values(1,1);
80
select 1ea10.1a20,1e+ 1e+10 from 1ea10;
82
create table t1 (t1.index int);
84
# Test that we get warning for this
85
drop database if exists mysqltest;
86
create database mysqltest;
87
create table mysqltest.$test1 (a$1 int, $b int, c$ int);
88
insert into mysqltest.$test1 values (1,2,3);
89
select a$1, $b, c$ from mysqltest.$test1;
90
create table mysqltest.test2$ (a int);
91
drop table mysqltest.test2$;
92
drop database mysqltest;
95
create table `` (a int);
97
drop table if exists ``;
99
create table t1 (`` int);
101
create table t1 (i int, index `` (i));
104
# Test of CREATE ... SELECT with indexes
107
create table t1 (a int auto_increment not null primary key, B CHAR(20));
108
insert into t1 (b) values ("hello"),("my"),("world");
109
create table t2 (key (b)) select * from t1;
110
explain select * from t2 where b="world";
111
select * from t2 where b="world";
115
# Test types after CREATE ... SELECT
118
create table t1(x varchar(50) );
119
create table t2 select x from t1 where 1=2;
123
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;
126
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;
131
# Test of CREATE ... SELECT with duplicate fields
134
create table t1 (a int);
135
create table t2 (a int) select * from t1;
138
drop table if exists t2;
140
create table t2 (a int, a float) select * from t1;
141
drop table if exists t2;
143
create table t2 (a int) select a as b, a+1 as b from t1;
144
drop table if exists t2;
146
create table t2 (b int) select a as b, a+1 as b from t1;
147
drop table if exists t1,t2;
150
# Test CREATE ... SELECT when insert fails
153
CREATE TABLE t1 (a int not null);
154
INSERT INTO t1 values (1),(2),(1);
156
CREATE TABLE t2 (primary key(a)) SELECT * FROM t1;
160
DROP TABLE IF EXISTS t2;
163
# Test of primary key with 32 index
166
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));
167
show create table t1;
169
create table t1 select if(1,'1','0'), month("2002-08-02");
171
create table t1 select if('2002'='2002','Y','N');
173
drop table if exists t1;
176
# Test default table type
178
SET SESSION storage_engine="heap";
179
SELECT @@storage_engine;
180
CREATE TABLE t1 (a int not null);
181
show create table t1;
184
SET SESSION storage_engine="gemini";
185
SELECT @@storage_engine;
186
CREATE TABLE t1 (a int not null);
187
show create table t1;
188
SET SESSION storage_engine=default;
193
# ISO requires that primary keys are implicitly NOT NULL
195
create table t1 ( k1 varchar(2), k2 int, primary key(k1,k2));
196
insert into t1 values ("a", 1), ("b", 2);
198
insert into t1 values ("c", NULL);
200
insert into t1 values (NULL, 3);
202
insert into t1 values (NULL, NULL);
209
create table t1 select x'4132';
216
create table t1 select 1,2,3;
217
create table if not exists t1 select 1,2;
219
create table if not exists t1 select 1,2,3,4;
220
create table if not exists t1 select 1;
225
# Test create table if not exists with duplicate key error
229
create table t1 (a int not null, b int, primary key (a));
230
insert into t1 values (1,1);
231
create table if not exists t1 select 2;
233
create table if not exists t1 select 3 as 'a',4 as 'b';
235
create table if not exists t1 select 3 as 'a',3 as 'b';
237
show status like "Opened_tables";
243
# "Table truncated when creating another table name with Spaces"
247
create table `t1 `(a int);
249
create database `db1 `;
251
create table t1(`a ` int);
255
# "Parser permits multiple commas without syntax error"
259
create table t1 (a int,);
261
create table t1 (a int,,b int);
263
create table t1 (,b int);
266
# Test create with foreign keys
269
create table t1 (a int, key(a));
270
create table t2 (b int, foreign key(b) references t1(a), key(b));
271
drop table if exists t1,t2;
274
# Test for CREATE TABLE .. LIKE ..
277
create table t1(id int not null, name char(20));
278
insert into t1 values(10,'mysql'),(20,'monty- the creator');
279
create table t2(id int not null);
280
insert into t2 values(10),(20);
281
create table t3 like t1;
282
show create table t3;
284
# Disable PS becasue of @@warning_count
285
create table if not exists t3 like t1;
286
--disable_ps_protocol
287
select @@warning_count;
289
create temporary table t3 like t2;
290
show create table t3;
293
show create table t3;
296
create database mysqltest;
297
create table mysqltest.t3 like t1;
298
create temporary table t3 like mysqltest.t3;
299
show create table t3;
300
create table t2 like t3;
301
show create table t2;
303
create table t3 like t1;
305
create table t3 like mysqltest.t3;
307
create table non_existing_database.t1 like t1;
308
--error ER_NO_SUCH_TABLE
309
create table t3 like non_existing_table;
311
create temporary table t3 like t1;
312
drop table t1, t2, t3;
314
drop database mysqltest;
317
# Test default table type
319
SET SESSION storage_engine="heap";
320
SELECT @@storage_engine;
321
CREATE TABLE t1 (a int not null);
322
show create table t1;
325
SET SESSION storage_engine="gemini";
326
SELECT @@storage_engine;
327
CREATE TABLE t1 (a int not null);
328
show create table t1;
329
SET SESSION storage_engine=default;
333
# Test types of data for create select with functions
336
create table t1(a int,b int,c int,d date,e char,f datetime,g time,h blob);
337
insert into t1(a)values(1);
338
insert into t1(a,b,c,d,e,f,g,h)
339
values(2,-2,2,'1825-12-14','a','2003-1-1 3:2:1','4:3:2','binary data');
342
ifnull(b,cast(-7 as signed)) as b,
343
ifnull(c,cast(7 as)) as c,
344
ifnull(d,cast('2000-01-01' as date)) as d,
345
ifnull(e,cast('b' as char)) as e,
346
ifnull(f,cast('2000-01-01' as datetime)) as f,
347
ifnull(g,cast('5:4:3' as time)) as g,
348
ifnull(h,cast('yet another binary data' as binary)) as h,
349
addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd
355
ifnull(b,cast(-7 as signed)) as b,
356
ifnull(c,cast(7 as)) as c,
357
ifnull(d,cast('2000-01-01' as date)) as d,
358
ifnull(e,cast('b' as char)) as e,
359
ifnull(f,cast('2000-01-01' as datetime)) as f,
360
ifnull(g,cast('5:4:3' as time)) as g,
361
ifnull(h,cast('yet another binary data' as binary)) as h,
362
addtime(cast('1:0:0' as time),cast('1:0:0' as time)) as dd
368
create table t1 (a int, b int, 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));
369
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;
370
show create table t2;
376
create table t1(str varchar(10) default 'def',strnull varchar(10),intg int default '10',rel double default '3.14');
377
insert into t1 values ('','',0,0.0);
379
create table t2 select default(str) as str, default(strnull) as strnull, default(intg) as intg, default(rel) as rel from t1;
387
create table t1(name varchar(10), age int default -1);
389
create table t2(name varchar(10), age int default - 1);
394
# test for bug #1427 "enum allows duplicate values in the list"
397
create table t1(cenum enum('a'), cset set('b'));
398
create table t2(cenum enum('a','a'), cset set('b','b'));
399
create table t3(cenum enum('a','A','a','c','c'), cset set('b','B','b','d','d'));
400
drop table t1, t2, t3;
406
create database mysqltest;
409
drop database mysqltest;
412
# Connect without a database as user mysqltest_1
413
create user mysqltest_1;
414
connect (user1,localhost,mysqltest_1,,*NO-ONE*);
416
select database(), user();
419
drop user mysqltest_1;
423
# Test for Bug 856 'Naming a key "Primary" causes trouble'
427
create table t1 (a int, index `primary` (a));
429
create table t1 (a int, index `PRIMARY` (a));
431
create table t1 (`primary` int, index(`primary`));
432
show create table t1;
433
create table t2 (`PRIMARY` int, index(`PRIMARY`));
434
show create table t2;
436
create table t3 (a int);
438
alter table t3 add index `primary` (a);
440
alter table t3 add index `PRIMARY` (a);
442
create table t4 (`primary` int);
443
alter table t4 add index(`primary`);
444
show create table t4;
445
create table t5 (`PRIMARY` int);
446
alter table t5 add index(`PRIMARY`);
447
show create table t5;
449
drop table t1, t2, t3, t4, t5;
452
# bug #3266 TEXT in CREATE TABLE SELECT
455
CREATE TABLE t1(id varchar(10) NOT NULL PRIMARY KEY, dsc longtext);
456
INSERT INTO t1 VALUES ('5000000001', NULL),('5000000003', 'Test'),('5000000004', NULL);
457
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));
459
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');
461
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;
463
drop table t1, t2, t3;
466
# Bug#9666: Can't use 'DEFAULT FALSE' for column of type bool
468
create table t1 (b bool not null default false);
469
create table t2 (b bool not null default true);
470
insert into t1 values ();
471
insert into t2 values ();
477
# Bug#10224 - ANALYZE TABLE crashing with simultaneous
478
# CREATE ... SELECT statement.
479
# This tests two additional possible errors and a hang if
480
# an improper fix is present.
482
create table t1 (a int);
484
create table t1 select * from t1;
485
--error ER_WRONG_OBJECT
486
create table t2 union = (t1) select * from t1;
487
flush tables with read lock;
492
# Bug#10413: Invalid column name is not rejected
495
create table t1(column.name int);
497
create table t1(test.column.name int);
499
create table t1(xyz.t1.name int);
500
create table t1(t1.name int);
501
create table t2(test.t2.name int);
505
# Bug #12537: UNION produces longtext instead of varchar
507
CREATE TABLE t1 (f1 VARCHAR(255) CHARACTER SET utf8);
508
CREATE TABLE t2 AS SELECT LEFT(f1,171) AS f2 FROM t1 UNION SELECT LEFT(f1,171) AS f2 FROM t1;
513
# Bug#12913 Simple SQL can crash server or connection
515
CREATE TABLE t12913 (f1 ENUM ('a','b')) AS SELECT 'a' AS f1;
516
SELECT * FROM t12913;
520
# Bug#11028: Crash on create table like
522
create database mysqltest;
524
drop database mysqltest;
525
--error ER_NO_DB_ERROR
526
create table test.t1 like x;
528
drop table if exists test.t1;
532
# Bug #6859: Bogus error message on attempt to CREATE TABLE t LIKE view
534
create database mysqltest;
536
create view v1 as select 'foo' from dual;
538
create table t1 like v1;
540
drop database mysqltest;
541
# Bug #6008 MySQL does not create warnings when
542
# creating database and using IF NOT EXISTS
544
create database mysqltest;
545
create database if not exists mysqltest character set latin2;
546
show create database mysqltest;
547
drop database mysqltest;
549
create table t1 (a int);
550
create table if not exists t1 (a int);
555
a varchar(112) charset utf8 collate utf8_bin not null,
557
) select 'test' as a ;
559
show create table t1;
563
# BUG#14480: assert failure in CREATE ... SELECT because of wrong
564
# calculation of number of NULLs.
567
a int(11) default NULL
569
insert into t2 values(111);
573
a varchar(12) charset utf8 collate utf8_bin not null,
574
b int not null, primary key (a)
575
) select a, 1 as b from t2 ;
576
show create table t1;
581
a varchar(12) charset utf8 collate utf8_bin not null,
582
b int not null, primary key (a)
583
) select a, 1 as c from t2 ;
584
show create table t1;
589
a varchar(12) charset utf8 collate utf8_bin not null,
590
b int null, primary key (a)
591
) select a, 1 as c from t2 ;
592
show create table t1;
597
a varchar(12) charset utf8 collate utf8_bin not null,
598
b int not null, primary key (a)
599
) select 'a' as a , 1 as b from t2 ;
600
show create table t1;
605
a varchar(12) charset utf8 collate utf8_bin,
606
b int not null, primary key (a)
607
) select 'a' as a , 1 as b from t2 ;
608
show create table t1;
613
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
615
insert into t1 values (1,1,1, 1,1,1, 1,1,1);
619
a1 varchar(12) charset utf8 collate utf8_bin not null,
620
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,
622
) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;
627
a1 varchar(12) charset utf8 collate utf8_bin,
628
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
629
) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1;
634
a1 int, a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int
636
insert into t1 values (1,1,1, 1,1,1, 1,1,1);
640
a1 varchar(12) charset utf8 collate utf8_bin not null,
641
a2 int, a3 int, a4 int, a5 int, a6 int, a7 int, a8 int, a9 int,
643
) select a1,a2,a3,a4,a5,a6,a7,a8,a9 from t1 ;
645
# Test the default value
648
create table t2 ( a int default 3, b int default 3)
649
select a1,a2 from t1;
650
show create table t2;
655
# Bug #15316 SET value having comma not correctly handled
658
create table t1(a set("a,b","c,d") not null);
664
# Bug #14155: Maximum value of MAX_ROWS handled incorrectly on 64-bit
667
create table t1 (i int) engine=myisam max_rows=100000000000;
668
show create table t1;
669
alter table t1 max_rows=100;
670
show create table t1;
671
alter table t1 max_rows=100000000000;
672
show create table t1;
677
# Tests for errors happening at various stages of CREATE TABLES ... SELECT
679
# (Also checks that it behaves atomically in the sense that in case
680
# of error it is automatically dropped if it has not existed before.)
682
# Error during open_and_lock_tables() of tables
683
--error ER_NO_SUCH_TABLE
684
create table t1 select * from t2;
685
# Rather special error which also caught during open tables pahse
686
--error ER_UPDATE_TABLE_USED
687
create table t1 select * from t1;
688
# Error which happens before select_create::prepare()
689
--error ER_CANT_AGGREGATE_2COLLATIONS
690
create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
691
# Error during table creation
692
--error ER_KEY_COLUMN_DOES_NOT_EXITS
693
create table t1 (primary key(a)) select "b" as b;
694
# Error in select_create::prepare() which is not related to table creation
695
create table t1 (a int);
696
--error ER_WRONG_VALUE_COUNT_ON_ROW
697
create table if not exists t1 select 1 as a, 2 as b;
699
# Finally error which happens during insert
701
create table t1 (primary key (a)) (select 1 as a) union all (select 1 as a);
702
# What happens if table already exists ?
703
create table t1 (i int);
704
--error ER_TABLE_EXISTS_ERROR
705
create table t1 select 1 as i;
706
create table if not exists t1 select 1 as i;
708
# Error before select_create::prepare()
709
--error ER_CANT_AGGREGATE_2COLLATIONS
710
create table t1 select coalesce('a' collate latin1_swedish_ci,'b' collate latin1_bin);
712
# Error which happens during insertion of rows
713
alter table t1 add primary key (i);
715
create table if not exists t1 (select 2 as i) union all (select 2 as i);
720
# Base vs temporary tables dillema (a.k.a. bug#24508 "Inconsistent
721
# results of CREATE TABLE ... SELECT when temporary table exists").
722
# In this situation we either have to create non-temporary table and
723
# insert data in it or insert data in temporary table without creation
724
# of permanent table. Since currently temporary tables always shadow
725
# permanent tables we adopt second approach.
726
create temporary table t1 (j int);
727
create table if not exists t1 select 1;
729
drop temporary table t1;
730
--error ER_NO_SUCH_TABLE
732
--error ER_BAD_TABLE_ERROR
737
# CREATE TABLE ... SELECT and LOCK TABLES
739
# There is little sense in using CREATE TABLE ... SELECT under
740
# LOCK TABLES as it mostly does not work. At least we check that
741
# the server doesn't crash, hang and produces sensible errors.
742
# Includes test for bug #20662 "Infinite loop in CREATE TABLE
743
# IF NOT EXISTS ... SELECT with locked tables".
744
create table t1 (i int);
745
insert into t1 values (1), (2);
747
--error ER_TABLE_NOT_LOCKED
748
create table t2 select * from t1;
749
--error ER_TABLE_NOT_LOCKED
750
create table if not exists t2 select * from t1;
752
create table t2 (j int);
754
--error ER_TABLE_NOT_LOCKED
755
create table t2 select * from t1;
756
# This should not be ever allowed as it will undermine
757
# lock-all-at-once approach
758
--error ER_TABLE_NOT_LOCKED
759
create table if not exists t2 select * from t1;
761
lock table t1 read, t2 read;
762
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
763
create table t2 select * from t1;
764
--error ER_TABLE_NOT_LOCKED_FOR_WRITE
765
create table if not exists t2 select * from t1;
767
lock table t1 read, t2 write;
768
--error ER_TABLE_EXISTS_ERROR
769
create table t2 select * from t1;
770
# This is the only case which really works.
771
create table if not exists t2 select * from t1;
776
# OTOH CREATE TEMPORARY TABLE ... SELECT should work
777
# well under LOCK TABLES.
779
create temporary table t2 select * from t1;
780
create temporary table if not exists t2 select * from t1;
787
# Bug#21772: can not name a column 'upgrade' when create a table
789
create table t1 (upgrade int);
794
# Bug #26642: create index corrupts table definition in .frm
796
# Problem with creating keys with maximum key-parts and maximum name length
797
# This test is made for a mysql server supporting names up to 64 bytes
798
# and a maximum of 16 key segements per Key
802
c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int, c8 int,
803
c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int,
805
key a001_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 a002_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 a003_long_123456789_123456789_123456789_123456789_123456789_1234 (
810
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
811
key a004_long_123456789_123456789_123456789_123456789_123456789_1234 (
812
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
813
key a005_long_123456789_123456789_123456789_123456789_123456789_1234 (
814
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
815
key a006_long_123456789_123456789_123456789_123456789_123456789_1234 (
816
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
817
key a007_long_123456789_123456789_123456789_123456789_123456789_1234 (
818
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
819
key a008_long_123456789_123456789_123456789_123456789_123456789_1234 (
820
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
821
key a009_long_123456789_123456789_123456789_123456789_123456789_1234 (
822
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
824
key a010_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 a011_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 a012_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 a013_long_123456789_123456789_123456789_123456789_123456789_1234 (
831
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
832
key a014_long_123456789_123456789_123456789_123456789_123456789_1234 (
833
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
834
key a015_long_123456789_123456789_123456789_123456789_123456789_1234 (
835
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
836
key a016_long_123456789_123456789_123456789_123456789_123456789_1234 (
837
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
838
key a017_long_123456789_123456789_123456789_123456789_123456789_1234 (
839
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
840
key a018_long_123456789_123456789_123456789_123456789_123456789_1234 (
841
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
842
key a019_long_123456789_123456789_123456789_123456789_123456789_1234 (
843
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
845
key a020_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 a021_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 a022_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 a023_long_123456789_123456789_123456789_123456789_123456789_1234 (
852
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
853
key a024_long_123456789_123456789_123456789_123456789_123456789_1234 (
854
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
855
key a025_long_123456789_123456789_123456789_123456789_123456789_1234 (
856
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
857
key a026_long_123456789_123456789_123456789_123456789_123456789_1234 (
858
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
859
key a027_long_123456789_123456789_123456789_123456789_123456789_1234 (
860
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
861
key a028_long_123456789_123456789_123456789_123456789_123456789_1234 (
862
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
863
key a029_long_123456789_123456789_123456789_123456789_123456789_1234 (
864
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
866
key a030_long_123456789_123456789_123456789_123456789_123456789_1234 (
867
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
868
key a031_long_123456789_123456789_123456789_123456789_123456789_1234 (
869
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
870
key a032_long_123456789_123456789_123456789_123456789_123456789_1234 (
871
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
872
key a033_long_123456789_123456789_123456789_123456789_123456789_1234 (
873
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
874
key a034_long_123456789_123456789_123456789_123456789_123456789_1234 (
875
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
876
key a035_long_123456789_123456789_123456789_123456789_123456789_1234 (
877
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
878
key a036_long_123456789_123456789_123456789_123456789_123456789_1234 (
879
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
880
key a037_long_123456789_123456789_123456789_123456789_123456789_1234 (
881
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
882
key a038_long_123456789_123456789_123456789_123456789_123456789_1234 (
883
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
884
key a039_long_123456789_123456789_123456789_123456789_123456789_1234 (
885
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
887
key a040_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
key a041_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
key a042_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
key a043_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
key a044_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
key a045_long_123456789_123456789_123456789_123456789_123456789_1234 (
898
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
899
key a046_long_123456789_123456789_123456789_123456789_123456789_1234 (
900
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
901
key a047_long_123456789_123456789_123456789_123456789_123456789_1234 (
902
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
903
key a048_long_123456789_123456789_123456789_123456789_123456789_1234 (
904
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
905
key a049_long_123456789_123456789_123456789_123456789_123456789_1234 (
906
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
908
key a050_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
key a051_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
key a052_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
key a053_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
key a054_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
key a055_long_123456789_123456789_123456789_123456789_123456789_1234 (
919
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
920
key a056_long_123456789_123456789_123456789_123456789_123456789_1234 (
921
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
922
key a057_long_123456789_123456789_123456789_123456789_123456789_1234 (
923
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
924
key a058_long_123456789_123456789_123456789_123456789_123456789_1234 (
925
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
926
key a059_long_123456789_123456789_123456789_123456789_123456789_1234 (
927
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
929
key a060_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
key a061_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
key a062_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
key a063_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
key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (
938
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16)
941
# Check that the table is not corrupted
942
show create table t1;
944
show create table t1;
946
# Repeat test using ALTER to add indexes
949
create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int,
950
c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int, c16 int);
954
add key a001_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 a002_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 a003_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 a004_long_123456789_123456789_123456789_123456789_123456789_1234 (
961
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
962
add key a005_long_123456789_123456789_123456789_123456789_123456789_1234 (
963
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
964
add key a006_long_123456789_123456789_123456789_123456789_123456789_1234 (
965
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
966
add key a007_long_123456789_123456789_123456789_123456789_123456789_1234 (
967
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
968
add key a008_long_123456789_123456789_123456789_123456789_123456789_1234 (
969
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
970
add key a009_long_123456789_123456789_123456789_123456789_123456789_1234 (
971
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
973
add key a010_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 a011_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 a012_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 a013_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 a014_long_123456789_123456789_123456789_123456789_123456789_1234 (
982
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
983
add key a015_long_123456789_123456789_123456789_123456789_123456789_1234 (
984
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
985
add key a016_long_123456789_123456789_123456789_123456789_123456789_1234 (
986
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
987
add key a017_long_123456789_123456789_123456789_123456789_123456789_1234 (
988
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
989
add key a018_long_123456789_123456789_123456789_123456789_123456789_1234 (
990
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
991
add key a019_long_123456789_123456789_123456789_123456789_123456789_1234 (
992
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
994
add key a020_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 a021_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 a022_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 a023_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 a024_long_123456789_123456789_123456789_123456789_123456789_1234 (
1003
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1004
add key a025_long_123456789_123456789_123456789_123456789_123456789_1234 (
1005
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1006
add key a026_long_123456789_123456789_123456789_123456789_123456789_1234 (
1007
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1008
add key a027_long_123456789_123456789_123456789_123456789_123456789_1234 (
1009
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1010
add key a028_long_123456789_123456789_123456789_123456789_123456789_1234 (
1011
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1012
add key a029_long_123456789_123456789_123456789_123456789_123456789_1234 (
1013
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1015
add key a030_long_123456789_123456789_123456789_123456789_123456789_1234 (
1016
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1017
add key a031_long_123456789_123456789_123456789_123456789_123456789_1234 (
1018
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1019
add key a032_long_123456789_123456789_123456789_123456789_123456789_1234 (
1020
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1021
add key a033_long_123456789_123456789_123456789_123456789_123456789_1234 (
1022
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1023
add key a034_long_123456789_123456789_123456789_123456789_123456789_1234 (
1024
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1025
add key a035_long_123456789_123456789_123456789_123456789_123456789_1234 (
1026
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1027
add key a036_long_123456789_123456789_123456789_123456789_123456789_1234 (
1028
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1029
add key a037_long_123456789_123456789_123456789_123456789_123456789_1234 (
1030
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1031
add key a038_long_123456789_123456789_123456789_123456789_123456789_1234 (
1032
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1033
add key a039_long_123456789_123456789_123456789_123456789_123456789_1234 (
1034
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1036
add key a040_long_123456789_123456789_123456789_123456789_123456789_1234 (
1037
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1038
add key a041_long_123456789_123456789_123456789_123456789_123456789_1234 (
1039
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1040
add key a042_long_123456789_123456789_123456789_123456789_123456789_1234 (
1041
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1042
add key a043_long_123456789_123456789_123456789_123456789_123456789_1234 (
1043
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1044
add key a044_long_123456789_123456789_123456789_123456789_123456789_1234 (
1045
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1046
add key a045_long_123456789_123456789_123456789_123456789_123456789_1234 (
1047
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1048
add key a046_long_123456789_123456789_123456789_123456789_123456789_1234 (
1049
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1050
add key a047_long_123456789_123456789_123456789_123456789_123456789_1234 (
1051
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1052
add key a048_long_123456789_123456789_123456789_123456789_123456789_1234 (
1053
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1054
add key a049_long_123456789_123456789_123456789_123456789_123456789_1234 (
1055
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1057
add key a050_long_123456789_123456789_123456789_123456789_123456789_1234 (
1058
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1059
add key a051_long_123456789_123456789_123456789_123456789_123456789_1234 (
1060
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1061
add key a052_long_123456789_123456789_123456789_123456789_123456789_1234 (
1062
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1063
add key a053_long_123456789_123456789_123456789_123456789_123456789_1234 (
1064
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1065
add key a054_long_123456789_123456789_123456789_123456789_123456789_1234 (
1066
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1067
add key a055_long_123456789_123456789_123456789_123456789_123456789_1234 (
1068
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1069
add key a056_long_123456789_123456789_123456789_123456789_123456789_1234 (
1070
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1071
add key a057_long_123456789_123456789_123456789_123456789_123456789_1234 (
1072
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1073
add key a058_long_123456789_123456789_123456789_123456789_123456789_1234 (
1074
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1075
add key a059_long_123456789_123456789_123456789_123456789_123456789_1234 (
1076
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1078
add key a060_long_123456789_123456789_123456789_123456789_123456789_1234 (
1079
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1080
add key a061_long_123456789_123456789_123456789_123456789_123456789_1234 (
1081
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1082
add key a062_long_123456789_123456789_123456789_123456789_123456789_1234 (
1083
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1084
add key a063_long_123456789_123456789_123456789_123456789_123456789_1234 (
1085
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16),
1086
add key a064_long_123456789_123456789_123456789_123456789_123456789_1234 (
1087
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);
1089
show create table t1;
1091
show create table t1;
1093
# Test the server limits; if any of these pass, all above tests need
1094
# to be rewritten to hit the limit
1096
# Ensure limit is really 64 keys
1098
alter table t1 add key
1099
a065_long_123456789_123456789_123456789_123456789_123456789_1234 (
1100
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16);
1104
# Ensure limit is really 16 key parts per key
1106
create table t1 (c1 int, c2 int, c3 int, c4 int, c5 int, c6 int, c7 int,
1107
c8 int, c9 int, c10 int, c11 int, c12 int, c13 int, c14 int, c15 int,
1110
# Get error for max key parts
1112
alter table t1 add key i1 (
1113
c1,c2,c3,c4,c5,c6,c7,c8,c9,c10,c11,c12,c13,c14,c15,c16, c17);
1115
# Get error for max key-name length
1117
alter table t1 add key
1118
a001_long_123456789_123456789_123456789_123456789_123456789_12345 (c1);
1120
show create table t1;
1125
--echo Bug #26104 Bug on foreign key class constructor
1127
--echo Check that ref_columns is initalized correctly in the constructor
1128
--echo and semantic checks in mysql_prepare_table work.
1130
--echo We do not need a storage engine that supports foreign keys
1131
--echo for this test, as the checks are purely syntax-based, and the
1132
--echo syntax is supported for all engines.
1135
drop table if exists t1,t2;
1138
create table t1(a int not null, b int not null, primary key (a, b));
1139
--error ER_WRONG_FK_DEF
1140
create table t2(a int not null, b int not null, c int not null, primary key (a),
1141
foreign key fk_bug26104 (b,c) references t1(a));
1145
# Bug#15130:CREATE .. SELECT was denied to use advantages of the SQL_BIG_RESULT.
1147
create table t1(f1 int,f2 int);
1148
insert into t1 value(1,1),(1,2),(1,3),(2,1),(2,2),(2,3);
1150
create table t2 select sql_big_result f1,count(f2) from t1 group by f1;
1151
show status like 'handler_read%';
1155
# Bug #25162: Backing up DB from 5.1 adds 'USING BTREE' to KEYs on table creates
1158
# Show that the old syntax for index type is supported
1159
CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1));
1162
# Show that the new syntax for index type is supported
1163
CREATE TABLE t1(c1 VARCHAR(33), KEY (c1) USING BTREE);
1166
# Show that in case of multiple index type definitions, the last one takes
1169
CREATE TABLE t1(c1 VARCHAR(33), KEY USING BTREE (c1) USING HASH) ENGINE=MEMORY;
1173
CREATE TABLE t1(c1 VARCHAR(33), KEY USING HASH (c1) USING BTREE) ENGINE=MEMORY;
1178
--echo End of 5.0 tests
1181
# Test of behaviour with CREATE ... SELECT
1184
CREATE TABLE t1 (a int, b int);
1185
insert into t1 values (1,1),(1,2);
1186
--error ER_DUP_ENTRY
1187
CREATE TABLE t2 (primary key (a)) select * from t1;
1188
# This should give warning
1189
drop table if exists t2;
1190
--error ER_DUP_ENTRY
1191
CREATE TEMPORARY TABLE t2 (primary key (a)) select * from t1;
1192
# This should give warning
1193
drop table if exists t2;
1194
CREATE TABLE t2 (a int, b int, primary key (a));
1195
--error ER_DUP_ENTRY
1196
CREATE TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
1199
--error ER_DUP_ENTRY
1200
INSERT INTO t2 select * from t1;
1204
CREATE TEMPORARY TABLE t2 (a int, b int, primary key (a));
1205
--error ER_DUP_ENTRY
1206
CREATE TEMPORARY TABLE IF NOT EXISTS t2 (primary key (a)) select * from t1;
1209
--error ER_DUP_ENTRY
1210
INSERT INTO t2 select * from t1;
1216
# Test incorrect database names
1220
CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1222
DROP DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1224
# TODO: enable these tests when RENAME DATABASE is implemented.
1226
# RENAME DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa TO a;
1228
# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1229
# create database mysqltest;
1231
# RENAME DATABASE mysqltest TO aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1232
# drop database mysqltest;
1235
USE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1237
SHOW CREATE DATABASE aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa;
1240
# Bug#21432 Database/Table name limited to 64 bytes, not chars, problems with multi-byte
1244
create database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
1245
use имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
1249
select SCHEMA_NAME from information_schema.schemata
1250
where schema_name='имя_базы_в_кодировке_утф8_длиной_больше_чем_45';
1252
drop database имя_базы_в_кодировке_утф8_длиной_больше_чем_45;
1253
create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48
1255
имя_поля_в_кодировке_утф8_длиной_больше_чем_45 int,
1256
index имя_индекса_в_кодировке_утф8_длиной_больше_чем_48 (имя_поля_в_кодировке_утф8_длиной_больше_чем_45)
1259
create view имя_вью_кодировке_утф8_длиной_больше_чем_42 as
1260
select имя_поля_в_кодировке_утф8_длиной_больше_чем_45
1261
from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1263
# database, table, field, key, view
1264
select * from имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1266
select TABLE_NAME from information_schema.tables where
1267
table_schema='test';
1269
select COLUMN_NAME from information_schema.columns where
1270
table_schema='test';
1272
select INDEX_NAME from information_schema.statistics where
1273
table_schema='test';
1275
select TABLE_NAME from information_schema.views where
1276
table_schema='test';
1278
show create table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1279
show create view имя_вью_кодировке_утф8_длиной_больше_чем_42;
1281
# procedure, function, trigger
1283
create trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49
1284
before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1;
1285
select TRIGGER_NAME from information_schema.triggers where
1286
trigger_schema='test';
1287
drop trigger имя_триггера_в_кодировке_утф8_длиной_больше_чем_49;
1290
очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66
1291
before insert on имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48 for each row set @a:=1;
1293
drop trigger очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66;
1295
create procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50()
1298
select ROUTINE_NAME from information_schema.routines where
1299
routine_schema='test';
1300
drop procedure имя_процедуры_в_кодировке_утф8_длиной_больше_чем_50;
1302
create procedure очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66()
1306
create function имя_функции_в_кодировке_утф8_длиной_больше_чем_49()
1309
select ROUTINE_NAME from information_schema.routines where
1310
routine_schema='test';
1311
drop function имя_функции_в_кодировке_утф8_длиной_больше_чем_49;
1313
create function очень_очень_очень_очень_очень_очень_очень_очень_длинная_строка_66()
1317
drop view имя_вью_кодировке_утф8_длиной_больше_чем_42;
1318
drop table имя_таблицы_в_кодировке_утф8_длиной_больше_чем_48;
1322
# Bug#21136 CREATE TABLE SELECT within CREATE TABLE SELECT causes server crash
1326
drop table if exists t1,t2,t3;
1327
drop function if exists f1;
1331
create function f1() returns int
1334
create temporary table t3 select 1 i;
1335
set res:= (select count(*) from t1);
1336
drop temporary table t3;
1340
create table t1 as select 1;
1341
create table t2 as select f1() from t1;
1346
# Bug#25629 CREATE TABLE LIKE does not work with INFORMATION_SCHEMA
1348
create table t1 like information_schema.processlist;
1349
show create table t1;
1351
create temporary table t1 like information_schema.processlist;
1352
show create table t1;
1354
create table t1 like information_schema.character_sets;
1355
show create table t1;
1358
###########################################################################
1362
--echo # -- Bug#21380: DEFAULT definition not always transfered by CREATE
1363
--echo # -- TABLE/SELECT to the new table.
1369
DROP TABLE IF EXISTS t1;
1370
DROP TABLE IF EXISTS t2;
1376
c1 INT DEFAULT 12 COMMENT 'column1',
1377
c2 INT NULL COMMENT 'column2',
1378
c3 INT NOT NULL COMMENT 'column3',
1379
c4 VARCHAR(255) CHARACTER SET utf8 NOT NULL DEFAULT 'a',
1380
c5 VARCHAR(255) COLLATE utf8_unicode_ci NULL DEFAULT 'b',
1386
SHOW CREATE TABLE t1;
1390
CREATE TABLE t2 AS SELECT * FROM t1;
1394
SHOW CREATE TABLE t2;
1401
--echo # -- End of test case for Bug#21380.
1403
###########################################################################
1407
--echo # -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields
1412
DROP TABLE IF EXISTS t1;
1413
DROP TABLE IF EXISTS t2;
1414
DROP TABLE IF EXISTS t3;
1419
CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);
1423
SET sql_mode = NO_ZERO_DATE;
1426
--error ER_INVALID_DEFAULT
1427
CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);
1430
--error ER_INVALID_DEFAULT
1431
CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);
1434
--echo # -- Check that NULL column still can be created.
1435
CREATE TABLE t2(c1 TIMESTAMP NULL);
1438
--echo # -- Check ALTER TABLE.
1439
--error ER_INVALID_DEFAULT
1440
ALTER TABLE t1 ADD INDEX(c1);
1443
--echo # -- Check DATETIME.
1448
CREATE TABLE t3(c1 DATETIME NOT NULL);
1449
INSERT INTO t3 VALUES (0);
1452
SET sql_mode = TRADITIONAL;
1455
--error ER_TRUNCATED_WRONG_VALUE
1456
ALTER TABLE t3 ADD INDEX(c1);
1459
--echo # -- Cleanup.
1467
--echo # -- End of Bug#18834.
1469
###########################################################################
1473
--echo # -- Bug#34274: Invalid handling of 'DEFAULT 0' for YEAR data type.
1478
DROP TABLE IF EXISTS t1;
1482
CREATE TABLE t1(c1 YEAR DEFAULT 2008, c2 YEAR DEFAULT 0);
1485
SHOW CREATE TABLE t1;
1488
INSERT INTO t1 VALUES();
1494
ALTER TABLE t1 MODIFY c1 YEAR DEFAULT 0;
1497
SHOW CREATE TABLE t1;
1500
INSERT INTO t1 VALUES();
1509
--echo # -- End of Bug#34274
1511
###########################################################################
1514
--echo End of 5.1 tests
added
removed
tests/t/create.test
