2 files changed, 126 insertions, 0 deletions
diff --git a/mysql-test/r/key.result b/mysql-test/r/key.result
index 095c8798b29..75676507760 100644
--- a/mysql-test/r/key.result
+++ b/mysql-test/r/key.result
@@ -396,3 +396,71 @@ a	int(11)	NO	PRI
 b	varchar(20)	NO	MUL		
 c	varchar(20)	NO			
 drop table t1;
+create table t1 (
+c1 int,
+c2 char(12),
+c3 varchar(123),
+c4 timestamp,
+index (c1),
+index i1 (c1),
+index i2 (c2),
+index i3 (c3),
+unique i4 (c4),
+index i5 (c1, c2, c3, c4),
+primary key (c2, c3),
+index (c2, c4));
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) default NULL,
+  `c2` char(12) NOT NULL default '',
+  `c3` varchar(123) NOT NULL default '',
+  `c4` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+  PRIMARY KEY  (`c2`,`c3`),
+  UNIQUE KEY `i4` (`c4`),
+  KEY `c1` (`c1`),
+  KEY `i1` (`c1`),
+  KEY `i2` (`c2`),
+  KEY `i3` (`c3`),
+  KEY `i5` (`c1`,`c2`,`c3`,`c4`),
+  KEY `c2` (`c2`,`c4`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t1 drop index c1;
+alter table t1 add index (c1);
+alter table t1 add index (c1);
+alter table t1 drop index i3;
+alter table t1 add index i3 (c3);
+alter table t1 drop index i2, drop index i4;
+alter table t1 add index i2 (c2), add index i4 (c4);
+alter table t1 drop index i2, drop index i4, add index i6 (c2, c4);
+alter table t1 add index i2 (c2), add index i4 (c4), drop index i6;
+alter table t1 drop index i2, drop index i4, add unique i4 (c4);
+alter table t1 add index i2 (c2), drop index i4, add index i4 (c4);
+alter table t1 drop index c2, add index (c2(4),c3(7));
+alter table t1 drop index c2, add index (c2(4),c3(7));
+alter table t1 add primary key (c1, c2), drop primary key;
+alter table t1 drop primary key;
+alter table t1 add primary key (c1, c2), drop primary key;
+ERROR 42000: Can't DROP 'PRIMARY'; check that column/key exists
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c1` int(11) NOT NULL default '0',
+  `c2` char(12) NOT NULL default '',
+  `c3` varchar(123) NOT NULL default '',
+  `c4` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+  KEY `i1` (`c1`),
+  KEY `i5` (`c1`,`c2`,`c3`,`c4`),
+  KEY `c1` (`c1`),
+  KEY `c1_2` (`c1`),
+  KEY `i3` (`c3`),
+  KEY `i2` (`c2`),
+  KEY `i4` (`c4`),
+  KEY `c2` (`c2`(4),`c3`(7))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values(1, 'a', 'a', NULL);
+insert into t1 values(1, 'b', 'b', NULL);
+alter table t1 drop index i3, drop index i2, drop index i1;
+alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
+ERROR 23000: Duplicate entry '1' for key 1
+drop table t1;
diff --git a/mysql-test/t/key.test b/mysql-test/t/key.test
index 31763b84379..f95cee8e8eb 100644
--- a/mysql-test/t/key.test
+++ b/mysql-test/t/key.test
@@ -372,3 +372,61 @@ desc t1;
 drop table t1;
 
 # End of 4.1 tests
+
+#
+# WL#1563 - Modify MySQL to support on-line CREATE/DROP INDEX
+# To test if this really works, you need to run with --debug
+# and check the trace file.
+#
+# Create a table with named and unnamed indexes.
+create table t1 (
+    c1 int,
+    c2 char(12),
+    c3 varchar(123),
+    c4 timestamp,
+    index (c1),
+    index i1 (c1),
+    index i2 (c2),
+    index i3 (c3),
+    unique i4 (c4),
+    index i5 (c1, c2, c3, c4),
+    primary key (c2, c3),
+    index (c2, c4));
+show create table t1;
+# Some simple tests.
+alter table t1 drop index c1;
+alter table t1 add index (c1);
+# This creates index 'c1_2'.
+alter table t1 add index (c1);
+alter table t1 drop index i3;
+alter table t1 add index i3 (c3);
+# Two indexes at the same time.
+alter table t1 drop index i2, drop index i4;
+alter table t1 add index i2 (c2), add index i4 (c4);
+# Three indexes, one of them reversely.
+alter table t1 drop index i2, drop index i4, add index i6 (c2, c4);
+alter table t1 add index i2 (c2), add index i4 (c4), drop index i6;
+# include an unique index.
+alter table t1 drop index i2, drop index i4, add unique i4 (c4);
+alter table t1 add index i2 (c2), drop index i4, add index i4 (c4);
+# Modify an index by changing its definition.
+alter table t1 drop index c2, add index (c2(4),c3(7));
+# Change nothing. The new key definition is the same as the old one.
+alter table t1 drop index c2, add index (c2(4),c3(7));
+# Test primary key handling.
+alter table t1 add primary key (c1, c2), drop primary key;
+alter table t1 drop primary key;
+# Drop is checked first. Primary key must exist.
+--error 1091
+alter table t1 add primary key (c1, c2), drop primary key;
+show create table t1;
+# Insert non-unique values.
+insert into t1 values(1, 'a', 'a', NULL);
+insert into t1 values(1, 'b', 'b', NULL);
+# Drop some indexes for new adds.
+alter table t1 drop index i3, drop index i2, drop index i1;
+# Add indexes, one is unique on non-unique values.
+--error 1062
+alter table t1 add index i3 (c3), add index i2 (c2), add unique index i1 (c1);
+drop table t1;
+