The patch for the task mdev-539.

The patch lifts the limitation of the current implementation
of ALTER TABLE that does not allow to build unique/primary
indexes by sort for MyISAM and Aria engines.

8 files changed, 591 insertions, 8 deletions

diff --git a/mysql-test/include/alter_table_mdev539.inc b/mysql-test/include/alter_table_mdev539.inc
new file mode 100644
index 00000000000..a246434b779
--- /dev/null
+++ b/mysql-test/include/alter_table_mdev539.inc
@@ -0,0 +1,65 @@
+--echo #
+--echo # mdev-539: fast build of unique/primary indexes for MyISAM/Aria
+--echo #
+
+--disable_warnings
+DROP DATABASE IF EXISTS dbt3_s001;
+--enable_warnings
+CREATE DATABASE dbt3_s001;
+
+use dbt3_s001;
+ 
+--disable_query_log
+--disable_result_log
+--disable_warnings
+--source include/dbt3_s001.inc
+--enable_warnings
+--enable_result_log
+--enable_query_log
+
+
+drop index `primary` on lineitem;
+show create table lineitem;
+alter table lineitem add primary key (l_orderkey, l_linenumber);
+show create table lineitem;
+drop index `primary` on lineitem;
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+insert into lineitem values
+(1,68,9,2,36,34850.16,0.07,0.06,'N','O','1996-04-12','1996-02-28','1996-04-20','TAKE BACK RETURN','MAIL','slyly bold pinto beans detect s');
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+--error ER_DUP_ENTRY
+alter table lineitem add primary key (l_orderkey, l_linenumber);
+show create table lineitem;
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+delete from lineitem where l_orderkey=1 and l_linenumber=2 and l_discount=0.07;
+alter table lineitem add primary key (l_orderkey, l_linenumber);
+show create table lineitem;
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+
+create unique index i_c_name on customer(c_name);
+show create table customer;
+select * from customer where c_name='Customer#000000003';
+drop index i_c_name on customer;
+insert into customer values
+(303,'Customer#000000003','MG9kdTD2WBHm',1,'11-719-748-3364',7498.12,'AUTOMOBILE','special packages wake. slyly reg');
+select * from customer where c_name='Customer#000000003';
+--error ER_DUP_ENTRY
+alter table customer add unique index i_c_name(c_name);
+show create table customer;
+select * from customer where c_name='Customer#000000003';
+delete from customer where c_custkey=303; 
+select * from customer where c_name='Customer#000000003';
+alter table customer add unique index i_c_name(c_name);
+show create table customer;
+select * from customer where c_name='Customer#000000003';
+
+drop index `primary` on customer;
+show create table customer;
+insert into customer values
+(3,'Customer#000000303','MG9kdTD2WBHm',1,'11-719-748-3364',7498.12,'AUTOMOBILE','special packages wake. slyly reg');
+alter ignore table customer add primary key (c_custkey);
+show create table customer;
+select * from customer where c_custkey=3; 
+
+DROP DATABASE dbt3_s001;
+ 
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result
index 624cc7afa98..b2cfd079ff5 100644
--- a/mysql-test/r/alter_table.result
+++ b/mysql-test/r/alter_table.result
@@ -176,12 +176,12 @@ create table t1 (a int, b int);
 alter table t1 add unique (a,b), add key (b);
 show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
-t1	0	a	1	a	A	NULL	NULL	NULL	YES	BTREE		
-t1	0	a	2	b	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	a	1	a	A	3	NULL	NULL	YES	BTREE		
+t1	0	a	2	b	A	300	NULL	NULL	YES	BTREE		
 t1	1	b	1	b	A	100	NULL	NULL	YES	BTREE		
 analyze table t1;
 Table	Op	Msg_type	Msg_text
-test.t1	analyze	status	OK
+test.t1	analyze	status	Table is already up to date
 show keys from t1;
 Table	Non_unique	Key_name	Seq_in_index	Column_name	Collation	Cardinality	Sub_part	Packed	Null	Index_type	Comment	Index_comment
 t1	0	a	1	a	A	3	NULL	NULL	YES	BTREE		
diff --git a/mysql-test/r/alter_table_mdev539_maria.result b/mysql-test/r/alter_table_mdev539_maria.result
new file mode 100644
index 00000000000..703908825d2
--- /dev/null
+++ b/mysql-test/r/alter_table_mdev539_maria.result
@@ -0,0 +1,252 @@
+#
+set @@storage_engine= Aria;
+#
+# mdev-539: fast build of unique/primary indexes for MyISAM/Aria
+#
+DROP DATABASE IF EXISTS dbt3_s001;
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+drop index `primary` on lineitem;
+show create table lineitem;
+Table	Create Table
+lineitem	CREATE TABLE `lineitem` (
+  `l_orderkey` int(11) NOT NULL DEFAULT '0',
+  `l_partkey` int(11) DEFAULT NULL,
+  `l_suppkey` int(11) DEFAULT NULL,
+  `l_linenumber` int(11) NOT NULL DEFAULT '0',
+  `l_quantity` double DEFAULT NULL,
+  `l_extendedprice` double DEFAULT NULL,
+  `l_discount` double DEFAULT NULL,
+  `l_tax` double DEFAULT NULL,
+  `l_returnflag` char(1) DEFAULT NULL,
+  `l_linestatus` char(1) DEFAULT NULL,
+  `l_shipDATE` date DEFAULT NULL,
+  `l_commitDATE` date DEFAULT NULL,
+  `l_receiptDATE` date DEFAULT NULL,
+  `l_shipinstruct` char(25) DEFAULT NULL,
+  `l_shipmode` char(10) DEFAULT NULL,
+  `l_comment` varchar(44) DEFAULT NULL,
+  KEY `i_l_shipdate` (`l_shipDATE`),
+  KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
+  KEY `i_l_partkey` (`l_partkey`),
+  KEY `i_l_suppkey` (`l_suppkey`),
+  KEY `i_l_receiptdate` (`l_receiptDATE`),
+  KEY `i_l_orderkey` (`l_orderkey`),
+  KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
+  KEY `i_l_commitdate` (`l_commitDATE`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+alter table lineitem add primary key (l_orderkey, l_linenumber);
+show create table lineitem;
+Table	Create Table
+lineitem	CREATE TABLE `lineitem` (
+  `l_orderkey` int(11) NOT NULL DEFAULT '0',
+  `l_partkey` int(11) DEFAULT NULL,
+  `l_suppkey` int(11) DEFAULT NULL,
+  `l_linenumber` int(11) NOT NULL DEFAULT '0',
+  `l_quantity` double DEFAULT NULL,
+  `l_extendedprice` double DEFAULT NULL,
+  `l_discount` double DEFAULT NULL,
+  `l_tax` double DEFAULT NULL,
+  `l_returnflag` char(1) DEFAULT NULL,
+  `l_linestatus` char(1) DEFAULT NULL,
+  `l_shipDATE` date DEFAULT NULL,
+  `l_commitDATE` date DEFAULT NULL,
+  `l_receiptDATE` date DEFAULT NULL,
+  `l_shipinstruct` char(25) DEFAULT NULL,
+  `l_shipmode` char(10) DEFAULT NULL,
+  `l_comment` varchar(44) DEFAULT NULL,
+  PRIMARY KEY (`l_orderkey`,`l_linenumber`),
+  KEY `i_l_shipdate` (`l_shipDATE`),
+  KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
+  KEY `i_l_partkey` (`l_partkey`),
+  KEY `i_l_suppkey` (`l_suppkey`),
+  KEY `i_l_receiptdate` (`l_receiptDATE`),
+  KEY `i_l_orderkey` (`l_orderkey`),
+  KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
+  KEY `i_l_commitdate` (`l_commitDATE`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+drop index `primary` on lineitem;
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipDATE	l_commitDATE	l_receiptDATE	l_shipinstruct	l_shipmode	l_comment
+1	68	9	2	36	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+insert into lineitem values
+(1,68,9,2,36,34850.16,0.07,0.06,'N','O','1996-04-12','1996-02-28','1996-04-20','TAKE BACK RETURN','MAIL','slyly bold pinto beans detect s');
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipDATE	l_commitDATE	l_receiptDATE	l_shipinstruct	l_shipmode	l_comment
+1	68	9	2	36	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+1	68	9	2	36	34850.16	0.07	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+alter table lineitem add primary key (l_orderkey, l_linenumber);
+ERROR 23000: Duplicate entry '1-2' for key 'PRIMARY'
+show create table lineitem;
+Table	Create Table
+lineitem	CREATE TABLE `lineitem` (
+  `l_orderkey` int(11) NOT NULL DEFAULT '0',
+  `l_partkey` int(11) DEFAULT NULL,
+  `l_suppkey` int(11) DEFAULT NULL,
+  `l_linenumber` int(11) NOT NULL DEFAULT '0',
+  `l_quantity` double DEFAULT NULL,
+  `l_extendedprice` double DEFAULT NULL,
+  `l_discount` double DEFAULT NULL,
+  `l_tax` double DEFAULT NULL,
+  `l_returnflag` char(1) DEFAULT NULL,
+  `l_linestatus` char(1) DEFAULT NULL,
+  `l_shipDATE` date DEFAULT NULL,
+  `l_commitDATE` date DEFAULT NULL,
+  `l_receiptDATE` date DEFAULT NULL,
+  `l_shipinstruct` char(25) DEFAULT NULL,
+  `l_shipmode` char(10) DEFAULT NULL,
+  `l_comment` varchar(44) DEFAULT NULL,
+  KEY `i_l_shipdate` (`l_shipDATE`),
+  KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
+  KEY `i_l_partkey` (`l_partkey`),
+  KEY `i_l_suppkey` (`l_suppkey`),
+  KEY `i_l_receiptdate` (`l_receiptDATE`),
+  KEY `i_l_orderkey` (`l_orderkey`),
+  KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
+  KEY `i_l_commitdate` (`l_commitDATE`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipDATE	l_commitDATE	l_receiptDATE	l_shipinstruct	l_shipmode	l_comment
+1	68	9	2	36	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+1	68	9	2	36	34850.16	0.07	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+delete from lineitem where l_orderkey=1 and l_linenumber=2 and l_discount=0.07;
+alter table lineitem add primary key (l_orderkey, l_linenumber);
+show create table lineitem;
+Table	Create Table
+lineitem	CREATE TABLE `lineitem` (
+  `l_orderkey` int(11) NOT NULL DEFAULT '0',
+  `l_partkey` int(11) DEFAULT NULL,
+  `l_suppkey` int(11) DEFAULT NULL,
+  `l_linenumber` int(11) NOT NULL DEFAULT '0',
+  `l_quantity` double DEFAULT NULL,
+  `l_extendedprice` double DEFAULT NULL,
+  `l_discount` double DEFAULT NULL,
+  `l_tax` double DEFAULT NULL,
+  `l_returnflag` char(1) DEFAULT NULL,
+  `l_linestatus` char(1) DEFAULT NULL,
+  `l_shipDATE` date DEFAULT NULL,
+  `l_commitDATE` date DEFAULT NULL,
+  `l_receiptDATE` date DEFAULT NULL,
+  `l_shipinstruct` char(25) DEFAULT NULL,
+  `l_shipmode` char(10) DEFAULT NULL,
+  `l_comment` varchar(44) DEFAULT NULL,
+  PRIMARY KEY (`l_orderkey`,`l_linenumber`),
+  KEY `i_l_shipdate` (`l_shipDATE`),
+  KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
+  KEY `i_l_partkey` (`l_partkey`),
+  KEY `i_l_suppkey` (`l_suppkey`),
+  KEY `i_l_receiptdate` (`l_receiptDATE`),
+  KEY `i_l_orderkey` (`l_orderkey`),
+  KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
+  KEY `i_l_commitdate` (`l_commitDATE`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipDATE	l_commitDATE	l_receiptDATE	l_shipinstruct	l_shipmode	l_comment
+1	68	9	2	36	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+create unique index i_c_name on customer(c_name);
+show create table customer;
+Table	Create Table
+customer	CREATE TABLE `customer` (
+  `c_custkey` int(11) NOT NULL,
+  `c_name` varchar(25) DEFAULT NULL,
+  `c_address` varchar(40) DEFAULT NULL,
+  `c_nationkey` int(11) DEFAULT NULL,
+  `c_phone` char(15) DEFAULT NULL,
+  `c_acctbal` double DEFAULT NULL,
+  `c_mktsegment` char(10) DEFAULT NULL,
+  `c_comment` varchar(117) DEFAULT NULL,
+  PRIMARY KEY (`c_custkey`),
+  UNIQUE KEY `i_c_name` (`c_name`),
+  KEY `i_c_nationkey` (`c_nationkey`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+select * from customer where c_name='Customer#000000003';
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+drop index i_c_name on customer;
+insert into customer values
+(303,'Customer#000000003','MG9kdTD2WBHm',1,'11-719-748-3364',7498.12,'AUTOMOBILE','special packages wake. slyly reg');
+select * from customer where c_name='Customer#000000003';
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+303	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+alter table customer add unique index i_c_name(c_name);
+ERROR 23000: Duplicate entry 'Customer#000000003' for key 'i_c_name'
+show create table customer;
+Table	Create Table
+customer	CREATE TABLE `customer` (
+  `c_custkey` int(11) NOT NULL,
+  `c_name` varchar(25) DEFAULT NULL,
+  `c_address` varchar(40) DEFAULT NULL,
+  `c_nationkey` int(11) DEFAULT NULL,
+  `c_phone` char(15) DEFAULT NULL,
+  `c_acctbal` double DEFAULT NULL,
+  `c_mktsegment` char(10) DEFAULT NULL,
+  `c_comment` varchar(117) DEFAULT NULL,
+  PRIMARY KEY (`c_custkey`),
+  KEY `i_c_nationkey` (`c_nationkey`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+select * from customer where c_name='Customer#000000003';
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+303	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+delete from customer where c_custkey=303;
+select * from customer where c_name='Customer#000000003';
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+alter table customer add unique index i_c_name(c_name);
+show create table customer;
+Table	Create Table
+customer	CREATE TABLE `customer` (
+  `c_custkey` int(11) NOT NULL,
+  `c_name` varchar(25) DEFAULT NULL,
+  `c_address` varchar(40) DEFAULT NULL,
+  `c_nationkey` int(11) DEFAULT NULL,
+  `c_phone` char(15) DEFAULT NULL,
+  `c_acctbal` double DEFAULT NULL,
+  `c_mktsegment` char(10) DEFAULT NULL,
+  `c_comment` varchar(117) DEFAULT NULL,
+  PRIMARY KEY (`c_custkey`),
+  UNIQUE KEY `i_c_name` (`c_name`),
+  KEY `i_c_nationkey` (`c_nationkey`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+select * from customer where c_name='Customer#000000003';
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+drop index `primary` on customer;
+show create table customer;
+Table	Create Table
+customer	CREATE TABLE `customer` (
+  `c_custkey` int(11) NOT NULL,
+  `c_name` varchar(25) DEFAULT NULL,
+  `c_address` varchar(40) DEFAULT NULL,
+  `c_nationkey` int(11) DEFAULT NULL,
+  `c_phone` char(15) DEFAULT NULL,
+  `c_acctbal` double DEFAULT NULL,
+  `c_mktsegment` char(10) DEFAULT NULL,
+  `c_comment` varchar(117) DEFAULT NULL,
+  UNIQUE KEY `i_c_name` (`c_name`),
+  KEY `i_c_nationkey` (`c_nationkey`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+insert into customer values
+(3,'Customer#000000303','MG9kdTD2WBHm',1,'11-719-748-3364',7498.12,'AUTOMOBILE','special packages wake. slyly reg');
+alter ignore table customer add primary key (c_custkey);
+show create table customer;
+Table	Create Table
+customer	CREATE TABLE `customer` (
+  `c_custkey` int(11) NOT NULL,
+  `c_name` varchar(25) DEFAULT NULL,
+  `c_address` varchar(40) DEFAULT NULL,
+  `c_nationkey` int(11) DEFAULT NULL,
+  `c_phone` char(15) DEFAULT NULL,
+  `c_acctbal` double DEFAULT NULL,
+  `c_mktsegment` char(10) DEFAULT NULL,
+  `c_comment` varchar(117) DEFAULT NULL,
+  PRIMARY KEY (`c_custkey`),
+  UNIQUE KEY `i_c_name` (`c_name`),
+  KEY `i_c_nationkey` (`c_nationkey`)
+) ENGINE=Aria DEFAULT CHARSET=latin1 PAGE_CHECKSUM=1
+select * from customer where c_custkey=3;
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+DROP DATABASE dbt3_s001;
+set @@storage_engine= default;
diff --git a/mysql-test/r/alter_table_mdev539_myisam.result b/mysql-test/r/alter_table_mdev539_myisam.result
new file mode 100644
index 00000000000..7140c544836
--- /dev/null
+++ b/mysql-test/r/alter_table_mdev539_myisam.result
@@ -0,0 +1,252 @@
+#
+set @@storage_engine= MyISAM;
+#
+# mdev-539: fast build of unique/primary indexes for MyISAM/Aria
+#
+DROP DATABASE IF EXISTS dbt3_s001;
+CREATE DATABASE dbt3_s001;
+use dbt3_s001;
+drop index `primary` on lineitem;
+show create table lineitem;
+Table	Create Table
+lineitem	CREATE TABLE `lineitem` (
+  `l_orderkey` int(11) NOT NULL DEFAULT '0',
+  `l_partkey` int(11) DEFAULT NULL,
+  `l_suppkey` int(11) DEFAULT NULL,
+  `l_linenumber` int(11) NOT NULL DEFAULT '0',
+  `l_quantity` double DEFAULT NULL,
+  `l_extendedprice` double DEFAULT NULL,
+  `l_discount` double DEFAULT NULL,
+  `l_tax` double DEFAULT NULL,
+  `l_returnflag` char(1) DEFAULT NULL,
+  `l_linestatus` char(1) DEFAULT NULL,
+  `l_shipDATE` date DEFAULT NULL,
+  `l_commitDATE` date DEFAULT NULL,
+  `l_receiptDATE` date DEFAULT NULL,
+  `l_shipinstruct` char(25) DEFAULT NULL,
+  `l_shipmode` char(10) DEFAULT NULL,
+  `l_comment` varchar(44) DEFAULT NULL,
+  KEY `i_l_shipdate` (`l_shipDATE`),
+  KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
+  KEY `i_l_partkey` (`l_partkey`),
+  KEY `i_l_suppkey` (`l_suppkey`),
+  KEY `i_l_receiptdate` (`l_receiptDATE`),
+  KEY `i_l_orderkey` (`l_orderkey`),
+  KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
+  KEY `i_l_commitdate` (`l_commitDATE`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table lineitem add primary key (l_orderkey, l_linenumber);
+show create table lineitem;
+Table	Create Table
+lineitem	CREATE TABLE `lineitem` (
+  `l_orderkey` int(11) NOT NULL DEFAULT '0',
+  `l_partkey` int(11) DEFAULT NULL,
+  `l_suppkey` int(11) DEFAULT NULL,
+  `l_linenumber` int(11) NOT NULL DEFAULT '0',
+  `l_quantity` double DEFAULT NULL,
+  `l_extendedprice` double DEFAULT NULL,
+  `l_discount` double DEFAULT NULL,
+  `l_tax` double DEFAULT NULL,
+  `l_returnflag` char(1) DEFAULT NULL,
+  `l_linestatus` char(1) DEFAULT NULL,
+  `l_shipDATE` date DEFAULT NULL,
+  `l_commitDATE` date DEFAULT NULL,
+  `l_receiptDATE` date DEFAULT NULL,
+  `l_shipinstruct` char(25) DEFAULT NULL,
+  `l_shipmode` char(10) DEFAULT NULL,
+  `l_comment` varchar(44) DEFAULT NULL,
+  PRIMARY KEY (`l_orderkey`,`l_linenumber`),
+  KEY `i_l_shipdate` (`l_shipDATE`),
+  KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
+  KEY `i_l_partkey` (`l_partkey`),
+  KEY `i_l_suppkey` (`l_suppkey`),
+  KEY `i_l_receiptdate` (`l_receiptDATE`),
+  KEY `i_l_orderkey` (`l_orderkey`),
+  KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
+  KEY `i_l_commitdate` (`l_commitDATE`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop index `primary` on lineitem;
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipDATE	l_commitDATE	l_receiptDATE	l_shipinstruct	l_shipmode	l_comment
+1	68	9	2	36	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+insert into lineitem values
+(1,68,9,2,36,34850.16,0.07,0.06,'N','O','1996-04-12','1996-02-28','1996-04-20','TAKE BACK RETURN','MAIL','slyly bold pinto beans detect s');
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipDATE	l_commitDATE	l_receiptDATE	l_shipinstruct	l_shipmode	l_comment
+1	68	9	2	36	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+1	68	9	2	36	34850.16	0.07	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+alter table lineitem add primary key (l_orderkey, l_linenumber);
+ERROR 23000: Duplicate entry '1-2' for key 'PRIMARY'
+show create table lineitem;
+Table	Create Table
+lineitem	CREATE TABLE `lineitem` (
+  `l_orderkey` int(11) NOT NULL DEFAULT '0',
+  `l_partkey` int(11) DEFAULT NULL,
+  `l_suppkey` int(11) DEFAULT NULL,
+  `l_linenumber` int(11) NOT NULL DEFAULT '0',
+  `l_quantity` double DEFAULT NULL,
+  `l_extendedprice` double DEFAULT NULL,
+  `l_discount` double DEFAULT NULL,
+  `l_tax` double DEFAULT NULL,
+  `l_returnflag` char(1) DEFAULT NULL,
+  `l_linestatus` char(1) DEFAULT NULL,
+  `l_shipDATE` date DEFAULT NULL,
+  `l_commitDATE` date DEFAULT NULL,
+  `l_receiptDATE` date DEFAULT NULL,
+  `l_shipinstruct` char(25) DEFAULT NULL,
+  `l_shipmode` char(10) DEFAULT NULL,
+  `l_comment` varchar(44) DEFAULT NULL,
+  KEY `i_l_shipdate` (`l_shipDATE`),
+  KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
+  KEY `i_l_partkey` (`l_partkey`),
+  KEY `i_l_suppkey` (`l_suppkey`),
+  KEY `i_l_receiptdate` (`l_receiptDATE`),
+  KEY `i_l_orderkey` (`l_orderkey`),
+  KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
+  KEY `i_l_commitdate` (`l_commitDATE`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipDATE	l_commitDATE	l_receiptDATE	l_shipinstruct	l_shipmode	l_comment
+1	68	9	2	36	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+1	68	9	2	36	34850.16	0.07	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+delete from lineitem where l_orderkey=1 and l_linenumber=2 and l_discount=0.07;
+alter table lineitem add primary key (l_orderkey, l_linenumber);
+show create table lineitem;
+Table	Create Table
+lineitem	CREATE TABLE `lineitem` (
+  `l_orderkey` int(11) NOT NULL DEFAULT '0',
+  `l_partkey` int(11) DEFAULT NULL,
+  `l_suppkey` int(11) DEFAULT NULL,
+  `l_linenumber` int(11) NOT NULL DEFAULT '0',
+  `l_quantity` double DEFAULT NULL,
+  `l_extendedprice` double DEFAULT NULL,
+  `l_discount` double DEFAULT NULL,
+  `l_tax` double DEFAULT NULL,
+  `l_returnflag` char(1) DEFAULT NULL,
+  `l_linestatus` char(1) DEFAULT NULL,
+  `l_shipDATE` date DEFAULT NULL,
+  `l_commitDATE` date DEFAULT NULL,
+  `l_receiptDATE` date DEFAULT NULL,
+  `l_shipinstruct` char(25) DEFAULT NULL,
+  `l_shipmode` char(10) DEFAULT NULL,
+  `l_comment` varchar(44) DEFAULT NULL,
+  PRIMARY KEY (`l_orderkey`,`l_linenumber`),
+  KEY `i_l_shipdate` (`l_shipDATE`),
+  KEY `i_l_suppkey_partkey` (`l_partkey`,`l_suppkey`),
+  KEY `i_l_partkey` (`l_partkey`),
+  KEY `i_l_suppkey` (`l_suppkey`),
+  KEY `i_l_receiptdate` (`l_receiptDATE`),
+  KEY `i_l_orderkey` (`l_orderkey`),
+  KEY `i_l_orderkey_quantity` (`l_orderkey`,`l_quantity`),
+  KEY `i_l_commitdate` (`l_commitDATE`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from lineitem where l_orderkey=1 and l_linenumber=2;
+l_orderkey	l_partkey	l_suppkey	l_linenumber	l_quantity	l_extendedprice	l_discount	l_tax	l_returnflag	l_linestatus	l_shipDATE	l_commitDATE	l_receiptDATE	l_shipinstruct	l_shipmode	l_comment
+1	68	9	2	36	34850.16	0.09	0.06	N	O	1996-04-12	1996-02-28	1996-04-20	TAKE BACK RETURN	MAIL	slyly bold pinto beans detect s
+create unique index i_c_name on customer(c_name);
+show create table customer;
+Table	Create Table
+customer	CREATE TABLE `customer` (
+  `c_custkey` int(11) NOT NULL,
+  `c_name` varchar(25) DEFAULT NULL,
+  `c_address` varchar(40) DEFAULT NULL,
+  `c_nationkey` int(11) DEFAULT NULL,
+  `c_phone` char(15) DEFAULT NULL,
+  `c_acctbal` double DEFAULT NULL,
+  `c_mktsegment` char(10) DEFAULT NULL,
+  `c_comment` varchar(117) DEFAULT NULL,
+  PRIMARY KEY (`c_custkey`),
+  UNIQUE KEY `i_c_name` (`c_name`),
+  KEY `i_c_nationkey` (`c_nationkey`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from customer where c_name='Customer#000000003';
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+drop index i_c_name on customer;
+insert into customer values
+(303,'Customer#000000003','MG9kdTD2WBHm',1,'11-719-748-3364',7498.12,'AUTOMOBILE','special packages wake. slyly reg');
+select * from customer where c_name='Customer#000000003';
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+303	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+alter table customer add unique index i_c_name(c_name);
+ERROR 23000: Duplicate entry 'Customer#000000003' for key 'i_c_name'
+show create table customer;
+Table	Create Table
+customer	CREATE TABLE `customer` (
+  `c_custkey` int(11) NOT NULL,
+  `c_name` varchar(25) DEFAULT NULL,
+  `c_address` varchar(40) DEFAULT NULL,
+  `c_nationkey` int(11) DEFAULT NULL,
+  `c_phone` char(15) DEFAULT NULL,
+  `c_acctbal` double DEFAULT NULL,
+  `c_mktsegment` char(10) DEFAULT NULL,
+  `c_comment` varchar(117) DEFAULT NULL,
+  PRIMARY KEY (`c_custkey`),
+  KEY `i_c_nationkey` (`c_nationkey`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from customer where c_name='Customer#000000003';
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+303	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+delete from customer where c_custkey=303;
+select * from customer where c_name='Customer#000000003';
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+alter table customer add unique index i_c_name(c_name);
+show create table customer;
+Table	Create Table
+customer	CREATE TABLE `customer` (
+  `c_custkey` int(11) NOT NULL,
+  `c_name` varchar(25) DEFAULT NULL,
+  `c_address` varchar(40) DEFAULT NULL,
+  `c_nationkey` int(11) DEFAULT NULL,
+  `c_phone` char(15) DEFAULT NULL,
+  `c_acctbal` double DEFAULT NULL,
+  `c_mktsegment` char(10) DEFAULT NULL,
+  `c_comment` varchar(117) DEFAULT NULL,
+  PRIMARY KEY (`c_custkey`),
+  UNIQUE KEY `i_c_name` (`c_name`),
+  KEY `i_c_nationkey` (`c_nationkey`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from customer where c_name='Customer#000000003';
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+drop index `primary` on customer;
+show create table customer;
+Table	Create Table
+customer	CREATE TABLE `customer` (
+  `c_custkey` int(11) NOT NULL,
+  `c_name` varchar(25) DEFAULT NULL,
+  `c_address` varchar(40) DEFAULT NULL,
+  `c_nationkey` int(11) DEFAULT NULL,
+  `c_phone` char(15) DEFAULT NULL,
+  `c_acctbal` double DEFAULT NULL,
+  `c_mktsegment` char(10) DEFAULT NULL,
+  `c_comment` varchar(117) DEFAULT NULL,
+  UNIQUE KEY `i_c_name` (`c_name`),
+  KEY `i_c_nationkey` (`c_nationkey`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into customer values
+(3,'Customer#000000303','MG9kdTD2WBHm',1,'11-719-748-3364',7498.12,'AUTOMOBILE','special packages wake. slyly reg');
+alter ignore table customer add primary key (c_custkey);
+show create table customer;
+Table	Create Table
+customer	CREATE TABLE `customer` (
+  `c_custkey` int(11) NOT NULL,
+  `c_name` varchar(25) DEFAULT NULL,
+  `c_address` varchar(40) DEFAULT NULL,
+  `c_nationkey` int(11) DEFAULT NULL,
+  `c_phone` char(15) DEFAULT NULL,
+  `c_acctbal` double DEFAULT NULL,
+  `c_mktsegment` char(10) DEFAULT NULL,
+  `c_comment` varchar(117) DEFAULT NULL,
+  PRIMARY KEY (`c_custkey`),
+  UNIQUE KEY `i_c_name` (`c_name`),
+  KEY `i_c_nationkey` (`c_nationkey`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+select * from customer where c_custkey=3;
+c_custkey	c_name	c_address	c_nationkey	c_phone	c_acctbal	c_mktsegment	c_comment
+3	Customer#000000003	MG9kdTD2WBHm	1	11-719-748-3364	7498.12	AUTOMOBILE	special packages wake. slyly reg
+DROP DATABASE dbt3_s001;
+set @@storage_engine= default;
diff --git a/mysql-test/r/range_vs_index_merge.result b/mysql-test/r/range_vs_index_merge.result
index cc8a345a2ff..424b1009a97 100644
--- a/mysql-test/r/range_vs_index_merge.result
+++ b/mysql-test/r/range_vs_index_merge.result
@@ -1576,7 +1576,7 @@ update t1 set c1=lpad(id+1000, 12, ' '), c2=lpad(id+10000, 15, ' ');
 alter table t1 add unique index (c1), add unique index (c2), add index (c3);
 analyze table t1;
 Table	Op	Msg_type	Msg_text
-test.t1	analyze	status	OK
+test.t1	analyze	status	Table is already up to date
 explain
 select * from t1 where (c1='      100000' or c2='         2000000');
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
diff --git a/mysql-test/r/subselect_mat_cost.result b/mysql-test/r/subselect_mat_cost.result
index 081196a227b..51ac98ca1a6 100644
--- a/mysql-test/r/subselect_mat_cost.result
+++ b/mysql-test/r/subselect_mat_cost.result
@@ -399,10 +399,10 @@ WHERE Code = Country GROUP BY Code)
 order by Country;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	CountryLanguage	index	NULL	PRIMARY	33	NULL	984	Using where; Using index
-3	MATERIALIZED	CountryLanguage	index	PRIMARY	PRIMARY	33	NULL	984	Using index; Using temporary
-3	MATERIALIZED	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using index
-2	MATERIALIZED	CountryLanguage	index	PRIMARY	PRIMARY	33	NULL	984	Using index; Using temporary
-2	MATERIALIZED	Country	eq_ref	PRIMARY	PRIMARY	3	world.CountryLanguage.Country	1	Using index
+3	MATERIALIZED	Country	index	PRIMARY	PRIMARY	3	NULL	239	Using index
+3	MATERIALIZED	CountryLanguage	ref	PRIMARY	PRIMARY	3	world.Country.Code	4	Using index
+2	MATERIALIZED	Country	index	PRIMARY	PRIMARY	3	NULL	239	Using index
+2	MATERIALIZED	CountryLanguage	ref	PRIMARY	PRIMARY	3	world.Country.Code	4	Using index
 select count(*)
 from CountryLanguage
 where
diff --git a/mysql-test/t/alter_table_mdev539_maria.test b/mysql-test/t/alter_table_mdev539_maria.test
new file mode 100644
index 00000000000..7e01bc3be84
--- /dev/null
+++ b/mysql-test/t/alter_table_mdev539_maria.test
@@ -0,0 +1,7 @@
+
+--echo #
+set @@storage_engine= Aria;
+
+--source include/alter_table_mdev539.inc
+
+set @@storage_engine= default;
diff --git a/mysql-test/t/alter_table_mdev539_myisam.test b/mysql-test/t/alter_table_mdev539_myisam.test
new file mode 100644
index 00000000000..0a5669088bf
--- /dev/null
+++ b/mysql-test/t/alter_table_mdev539_myisam.test
@@ -0,0 +1,7 @@
+
+--echo #
+set @@storage_engine= MyISAM;
+
+--source include/alter_table_mdev539.inc
+
+set @@storage_engine= default;