commit a

4 files changed, 2300 insertions, 0 deletions

diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result
new file mode 100644
index 00000000000..29d5a6b8a04
--- /dev/null
+++ b/mysql-test/main/long_unique.result
@@ -0,0 +1,1379 @@
+#Structure of tests
+#First we will check all option for
+#table containing single unique column
+#table containing keys like unique(a,b,c,d) etc
+#then table containing 2 blob unique etc 
+set @allowed_packet= @@max_allowed_packet;
+#table with single long blob column; 
+create table t1(a blob unique);
+insert into t1 values(1),(2),(3),(56),('sachin'),('maria'),(123456789034567891),(null),(null),(123456789034567890);
+#table structure;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	YES	UNI	NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  UNIQUE KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+show keys from t1;
+Table	t1
+Non_unique	0
+Key_name	a
+Seq_in_index	1
+Column_name	a
+Collation	A
+Cardinality	NULL
+Sub_part	NULL
+Packed	NULL
+Null	YES
+Index_type	HASH_INDEX
+Comment	
+Index_comment	
+
+MyISAM file:         DATADIR/test/t1
+Record format:       Packed
+Character set:       latin1_swedish_ci (8)
+Data records:                   10  Deleted blocks:                 0
+Recordlength:                   20
+
+table description:
+Key Start Len Index   Type
+1   12    8   multip. ulonglong NULL       
+select * from information_schema.columns where table_schema = 'test' and table_name = 't1';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
+def	test	t1	a	1	NULL	YES	blob	65535	65535	NULL	NULL	NULL	NULL	NULL	blob	UNI		select,insert,update,references		NEVER	NULL
+select * from information_schema.statistics where table_schema = 'test' and table_name = 't1';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	INDEX_COMMENT
+def	test	t1	0	test	a	1	a	A	NULL	NULL	NULL	YES	HASH_INDEX		
+select   * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1';
+CONSTRAINT_CATALOG	def
+CONSTRAINT_SCHEMA	test
+CONSTRAINT_NAME	a
+TABLE_CATALOG	def
+TABLE_SCHEMA	test
+TABLE_NAME	t1
+COLUMN_NAME	a
+ORDINAL_POSITION	1
+POSITION_IN_UNIQUE_CONSTRAINT	NULL
+REFERENCED_TABLE_SCHEMA	NULL
+REFERENCED_TABLE_NAME	NULL
+REFERENCED_COLUMN_NAME	NULL
+# table select we should not be able to see db_row_hash_column;
+select * from t1 order by a;
+a
+NULL
+NULL
+1
+123456789034567890
+123456789034567891
+2
+3
+56
+maria
+sachin
+select db_row_hash_1 from t1;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list'
+#duplicate entry test;
+insert into t1 values(2);
+ERROR 23000: Duplicate entry '2' for key 'a'
+insert into t1 values('sachin');
+ERROR 23000: Duplicate entry 'sachin' for key 'a'
+insert into t1 values(123456789034567891);
+ERROR 23000: Duplicate entry '123456789034567891' for key 'a'
+select * from t1 order by a;
+a
+NULL
+NULL
+1
+123456789034567890
+123456789034567891
+2
+3
+56
+maria
+sachin
+insert into t1 values(11),(22),(33);
+insert into t1 values(12),(22);
+ERROR 23000: Duplicate entry '22' for key 'a'
+select * from t1 order by a;
+a
+NULL
+NULL
+1
+11
+12
+123456789034567890
+123456789034567891
+2
+22
+3
+33
+56
+maria
+sachin
+insert into t1 values(repeat('s',4000*10)),(repeat('s',4001*10));
+insert into t1 values(repeat('m',4000*10)),(repeat('m',4000*10));
+ERROR 23000: Duplicate entry 'mmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm' for key 'a'
+insert into t1 values(repeat('m',4001)),(repeat('m',4002));
+truncate table t1;
+insert into t1 values(1),(2),(3),(4),(5),(8),(7);
+
+MyISAM file:         DATADIR/test/t1
+Record format:       Packed
+Character set:       latin1_swedish_ci (8)
+Data records:                    7  Deleted blocks:                 0
+Recordlength:                   20
+
+table description:
+Key Start Len Index   Type
+1   12    8   multip. ulonglong NULL       
+#now some alter commands;
+alter table t1 add column b int;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	YES	UNI	NULL	
+b	int(11)	YES		NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values(1,2);
+ERROR 23000: Duplicate entry '1' for key 'a'
+insert into t1 values(2,2);
+ERROR 23000: Duplicate entry '2' for key 'a'
+select db_row_hash_1 from t1;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list'
+#now try to change db_row_hash_1 column;
+alter table t1 drop column db_row_hash_1;
+ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists
+alter table t1 add column d int , add column e int , drop column db_row_hash_1;
+ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists
+alter table t1 modify column db_row_hash_1 int ;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 add column a int , add column b int, modify column db_row_hash_1 int ;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 change column db_row_hash_1 dsds int;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 add column asd int, change column db_row_hash_1 dsds int;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 drop column b , add column c int;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+#now add some column with name db_row_hash;
+alter table t1 add column db_row_hash_1 int unique;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535)),
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values(45,1,55),(46,1,55);
+ERROR 23000: Duplicate entry '55' for key 'db_row_hash_1'
+insert into t1 values(45,1,55),(45,1,55);
+ERROR 23000: Duplicate entry '45' for key 'a'
+alter table t1 add column db_row_hash_2 int, add column db_row_hash_3 int;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	YES	UNI	NULL	
+c	int(11)	YES		NULL	
+db_row_hash_1	int(11)	YES	UNI	NULL	
+db_row_hash_2	int(11)	YES		NULL	
+db_row_hash_3	int(11)	YES		NULL	
+#this should also drop the unique index ;
+alter table t1 drop column a;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_3` int(11) DEFAULT NULL,
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	db_row_hash_1	1	db_row_hash_1	A	NULL	NULL	NULL	YES	BTREE		
+#add column with unique index on blob ;
+alter table t1 add column a blob unique;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_3` int(11) DEFAULT NULL,
+  `a` blob DEFAULT NULL,
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+# try to change the blob unique name;
+#this will change index to b tree;
+alter table t1 change column a aa blob ;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_3` int(11) DEFAULT NULL,
+  `aa` blob DEFAULT NULL,
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `a` (`aa`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	db_row_hash_1	1	db_row_hash_1	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	a	1	aa	A	NULL	NULL	NULL	YES	HASH_INDEX		
+# try to change the blob unique datatype;
+#this will change index to b tree;
+alter table t1 modify  column aa  int ;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_3` int(11) DEFAULT NULL,
+  `aa` int(11) DEFAULT NULL,
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `a` (`aa`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	db_row_hash_1	1	db_row_hash_1	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	a	1	aa	A	NULL	NULL	NULL	YES	BTREE		
+alter table t1 add column clm blob unique;
+#try changing the name ;
+alter table t1 change column clm clm_changed blob;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_3` int(11) DEFAULT NULL,
+  `aa` int(11) DEFAULT NULL,
+  `clm_changed` blob DEFAULT NULL,
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `a` (`aa`),
+  UNIQUE KEY `clm` (`clm_changed`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	db_row_hash_1	1	db_row_hash_1	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	a	1	aa	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	clm	1	clm_changed	A	NULL	NULL	NULL	YES	HASH_INDEX		
+#now drop the unique key;
+alter table t1 drop key clm;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `c` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_3` int(11) DEFAULT NULL,
+  `aa` int(11) DEFAULT NULL,
+  `clm_changed` blob DEFAULT NULL,
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `a` (`aa`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	db_row_hash_1	1	db_row_hash_1	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	a	1	aa	A	NULL	NULL	NULL	YES	BTREE		
+drop table t1;
+create table t1 (a TEXT CHARSET latin1 COLLATE latin1_german2_ci unique);
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	text	YES	UNI	NULL	
+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	HASH_INDEX		
+insert into t1 values ('ae');
+insert into t1 values ('AE');
+ERROR 23000: Duplicate entry 'AE' for key 'a'
+insert  into t1 values ('Ä');
+drop table t1;
+create table t1 (a int primary key, b blob unique);
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	NO	PRI	NULL	
+b	blob	YES	UNI	NULL	
+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	PRIMARY	1	a	A	0	NULL	NULL		BTREE		
+t1	0	b	1	b	A	NULL	NULL	NULL	YES	HASH_INDEX		
+insert into t1 values(1,1),(2,2),(3,3);
+insert into t1 values(1,1);
+ERROR 23000: Duplicate entry '1' for key 'b'
+insert into t1 values(7,1);
+ERROR 23000: Duplicate entry '1' for key 'b'
+drop table t1;
+#table with multiple  long blob column and varchar text column ; 
+create table t1(a blob unique, b int , c blob unique , d text unique , e varchar(3000) unique);
+insert into t1 values(1,2,3,4,5),(2,11,22,33,44),(3111,222,333,444,555),(5611,2222,3333,4444,5555),
+('sachin',341,'fdf','gfgfgfg','hghgr'),('maria',345,'frter','dasd','utyuty'),
+(123456789034567891,353534,53453453453456,64565464564564,45435345345345),
+(123456789034567890,43545,657567567567,78967657567567,657567567567567676);
+#table structure;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	YES	UNI	NULL	
+b	int(11)	YES		NULL	
+c	blob	YES	UNI	NULL	
+d	text	YES	UNI	NULL	
+e	varchar(3000)	YES	UNI	NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` blob DEFAULT NULL,
+  `d` text DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`),
+  UNIQUE KEY `c` (`c`),
+  UNIQUE KEY `d` (`d`),
+  UNIQUE KEY `e` (`e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	HASH_INDEX		
+t1	0	c	1	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	d	1	d	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	1	e	A	NULL	NULL	NULL	YES	HASH_INDEX		
+
+MyISAM file:         DATADIR/test/t1
+Record format:       Packed
+Character set:       latin1_swedish_ci (8)
+Data records:                    8  Deleted blocks:                 0
+Recordlength:                 3072
+
+table description:
+Key Start Len Index   Type
+1   3063  8   multip. ulonglong NULL       
+2   3055  8   multip. ulonglong NULL       
+3   3047  8   multip. ulonglong NULL       
+4   3039  8   multip. ulonglong prefix NULL
+select * from information_schema.columns where table_schema = 'test' and table_name = 't1';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
+def	test	t1	a	1	NULL	YES	blob	65535	65535	NULL	NULL	NULL	NULL	NULL	blob	UNI		select,insert,update,references		NEVER	NULL
+def	test	t1	b	2	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references		NEVER	NULL
+def	test	t1	c	3	NULL	YES	blob	65535	65535	NULL	NULL	NULL	NULL	NULL	blob	UNI		select,insert,update,references		NEVER	NULL
+def	test	t1	d	4	NULL	YES	text	65535	65535	NULL	NULL	NULL	latin1	latin1_swedish_ci	text	UNI		select,insert,update,references		NEVER	NULL
+def	test	t1	e	5	NULL	YES	varchar	3000	3000	NULL	NULL	NULL	latin1	latin1_swedish_ci	varchar(3000)	UNI		select,insert,update,references		NEVER	NULL
+select * from information_schema.statistics where table_schema = 'test' and table_name = 't1';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	INDEX_COMMENT
+def	test	t1	0	test	a	1	a	A	NULL	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	c	1	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	d	1	d	A	NULL	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	e	1	e	A	NULL	NULL	NULL	YES	HASH_INDEX		
+select * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1';
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
+def	test	a	def	test	t1	a	1	NULL	NULL	NULL	NULL
+def	test	c	def	test	t1	c	1	NULL	NULL	NULL	NULL
+def	test	d	def	test	t1	d	1	NULL	NULL	NULL	NULL
+def	test	e	def	test	t1	e	1	NULL	NULL	NULL	NULL
+#table select we should not be able to see db_row_hash_1 column;
+select * from t1 order by a;
+a	b	c	d	e
+1	2	3	4	5
+123456789034567890	43545	657567567567	78967657567567	657567567567567676
+123456789034567891	353534	53453453453456	64565464564564	45435345345345
+2	11	22	33	44
+3111	222	333	444	555
+5611	2222	3333	4444	5555
+maria	345	frter	dasd	utyuty
+sachin	341	fdf	gfgfgfg	hghgr
+select db_row_hash_1 from t1;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list'
+select db_row_hash_2 from t1;
+ERROR 42S22: Unknown column 'db_row_hash_2' in 'field list'
+select db_row_hash_3 from t1;
+ERROR 42S22: Unknown column 'db_row_hash_3' in 'field list'
+#duplicate entry test;
+insert into t1 values(21,2,3,42,51);
+ERROR 23000: Duplicate entry '3' for key 'c'
+insert into t1 values('sachin',null,null,null,null);
+ERROR 23000: Duplicate entry 'sachin' for key 'a'
+insert into t1 values(1234567890345671890,4353451,6575675675617,789676575675617,657567567567567676);
+ERROR 23000: Duplicate entry '657567567567567676' for key 'e'
+select * from t1 order by a;
+a	b	c	d	e
+1	2	3	4	5
+123456789034567890	43545	657567567567	78967657567567	657567567567567676
+123456789034567891	353534	53453453453456	64565464564564	45435345345345
+2	11	22	33	44
+3111	222	333	444	555
+5611	2222	3333	4444	5555
+maria	345	frter	dasd	utyuty
+sachin	341	fdf	gfgfgfg	hghgr
+insert into t1 values(repeat('s',4000*10),100,repeat('s',4000*10),repeat('s',4000*10),
+repeat('s',400)),(repeat('s',4001*10),1000,repeat('s',4001*10),repeat('s',4001*10),
+repeat('s',2995));
+insert into t1 values(repeat('m',4000*11),10,repeat('s',4000*11),repeat('s',4000*11),repeat('s',2995));
+ERROR 23000: Duplicate entry 'ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss' for key 'e'
+truncate table t1;
+insert into t1 values(1,2,3,4,5),(2,11,22,33,44),(3111,222,333,444,555),(5611,2222,3333,4444,5555);
+#now some alter commands;
+alter table t1 add column f int;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	YES	UNI	NULL	
+b	int(11)	YES		NULL	
+c	blob	YES	UNI	NULL	
+d	text	YES	UNI	NULL	
+e	varchar(3000)	YES	UNI	NULL	
+f	int(11)	YES		NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` blob DEFAULT NULL,
+  `d` text DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535)),
+  UNIQUE KEY `c` (`c`(65535)),
+  UNIQUE KEY `d` (`d`(65535)),
+  UNIQUE KEY `e` (`e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+#unique key should not break;
+insert into t1 values(1,2,3,4,5,6);
+ERROR 23000: Duplicate entry '1' for key 'a'
+select db_row_hash_1 , db_row_hash_2, db_row_hash_3 from t1;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list'
+#now try to change db_row_hash_1 column;
+alter table t1 drop column db_row_hash_1, drop column db_row_hash_2, drop column db_row_hash_3;
+ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists
+alter table t1 add column dg int , add column ef int , drop column db_row_hash_1;
+ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists
+alter table t1 modify column db_row_hash_1 int , modify column db_row_hash_2 int, modify column db_row_hash_3 int;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 add column ar int , add column rb int, modify column db_row_hash_1 int , modify column db_row_hash_3 int;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 change column db_row_hash_1 dsds int , change column  db_row_hash_2  dfdf int , change column db_row_hash_3 gdfg int ;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 add column asd int, drop column a, change column db_row_hash_1 dsds int, change db_row_hash_3 fdfdfd int;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 drop column b , add column g int;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `c` blob DEFAULT NULL,
+  `d` text DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535)),
+  UNIQUE KEY `c` (`c`(65535)),
+  UNIQUE KEY `d` (`d`(65535)),
+  UNIQUE KEY `e` (`e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+#now add some column with name db_row_hash;
+alter table t1 add column db_row_hash_1 int unique;
+alter table t1 add column db_row_hash_2 int unique;
+alter table t1 add column db_row_hash_3 int unique;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `c` blob DEFAULT NULL,
+  `d` text DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_3` int(11) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535)),
+  UNIQUE KEY `c` (`c`(65535)),
+  UNIQUE KEY `d` (`d`(65535)),
+  UNIQUE KEY `e` (`e`),
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+  UNIQUE KEY `db_row_hash_3` (`db_row_hash_3`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+alter table t1 add column db_row_hash_7 int, add column db_row_hash_5 int , add column db_row_hash_4 int ;
+alter table t1 drop column db_row_hash_7,drop column db_row_hash_3, drop column db_row_hash_4;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	YES	UNI	NULL	
+c	blob	YES	UNI	NULL	
+d	text	YES	UNI	NULL	
+e	varchar(3000)	YES	UNI	NULL	
+f	int(11)	YES		NULL	
+g	int(11)	YES		NULL	
+db_row_hash_1	int(11)	YES	UNI	NULL	
+db_row_hash_2	int(11)	YES	UNI	NULL	
+db_row_hash_5	int(11)	YES		NULL	
+#this show now break anything;
+insert into t1 values(1,2,3,4,5,6,23,5,6);
+ERROR 23000: Duplicate entry '1' for key 'a'
+#this should also drop the unique index;
+alter table t1 drop column a, drop column c;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` text DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_5` int(11) DEFAULT NULL,
+  UNIQUE KEY `d` (`d`(65535)),
+  UNIQUE KEY `e` (`e`),
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	d	1	d	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	e	1	e	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	db_row_hash_1	1	db_row_hash_1	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	db_row_hash_2	1	db_row_hash_2	A	NULL	NULL	NULL	YES	BTREE		
+#add column with unique index on blob;
+alter table t1 add column a blob unique;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` text DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_5` int(11) DEFAULT NULL,
+  `a` blob DEFAULT NULL,
+  UNIQUE KEY `d` (`d`(65535)),
+  UNIQUE KEY `e` (`e`),
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+  UNIQUE KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	d	1	d	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	e	1	e	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	db_row_hash_1	1	db_row_hash_1	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	db_row_hash_2	1	db_row_hash_2	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	a	1	a	A	NULL	NULL	NULL	YES	HASH_INDEX		
+#try to change the blob unique column name;
+#this will change index to b tree;
+alter table t1 modify  column a  int ,  modify column e int;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` text DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_5` int(11) DEFAULT NULL,
+  `a` int(11) DEFAULT NULL,
+  UNIQUE KEY `d` (`d`(65535)),
+  UNIQUE KEY `e` (`e`),
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+  UNIQUE KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	d	1	d	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	e	1	e	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	db_row_hash_1	1	db_row_hash_1	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	db_row_hash_2	1	db_row_hash_2	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	a	1	a	A	NULL	NULL	NULL	YES	BTREE		
+alter table t1 add column clm1 blob unique,add column clm2 blob unique;
+#try changing the name; 
+alter table t1 change column clm1 clm_changed1 blob, change column clm2 clm_changed2 blob;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` text DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_5` int(11) DEFAULT NULL,
+  `a` int(11) DEFAULT NULL,
+  `clm_changed1` blob DEFAULT NULL,
+  `clm_changed2` blob DEFAULT NULL,
+  UNIQUE KEY `d` (`d`(65535)),
+  UNIQUE KEY `e` (`e`),
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+  UNIQUE KEY `a` (`a`),
+  UNIQUE KEY `clm1` (`clm_changed1`),
+  UNIQUE KEY `clm2` (`clm_changed2`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	d	1	d	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	e	1	e	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	db_row_hash_1	1	db_row_hash_1	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	db_row_hash_2	1	db_row_hash_2	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	a	1	a	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	clm1	1	clm_changed1	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	clm2	1	clm_changed2	A	NULL	NULL	NULL	YES	HASH_INDEX		
+#now drop the unique key;
+alter table t1 drop key clm1, drop key clm2;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `d` text DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  `db_row_hash_5` int(11) DEFAULT NULL,
+  `a` int(11) DEFAULT NULL,
+  `clm_changed1` blob DEFAULT NULL,
+  `clm_changed2` blob DEFAULT NULL,
+  UNIQUE KEY `d` (`d`(65535)),
+  UNIQUE KEY `e` (`e`),
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+  UNIQUE KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	d	1	d	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	e	1	e	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	db_row_hash_1	1	db_row_hash_1	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	db_row_hash_2	1	db_row_hash_2	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	a	1	a	A	NULL	NULL	NULL	YES	BTREE		
+drop table t1;
+#now the table with key on multiple columns; the ultimate test;
+create table t1(a blob, b int , c varchar(2000) , d text  , e varchar(3000) , f longblob , g int , h text ,
+unique(a,b,c), unique(c,d,e),unique(e,f,g,h), unique(b,d,g,h));
+insert into t1 values(1,1,1,1,1,1,1,1),(2,2,2,2,2,2,2,2),(3,3,3,3,3,3,3,3),(4,4,4,4,4,4,4,4),(5,5,5,5,5,5,5,5),
+('maria',6,'maria','maria','maria','maria',6,'maria'),('mariadb',7,'mariadb','mariadb','mariadb','mariadb',8,'mariadb')
+,(null,null,null,null,null,null,null,null),(null,null,null,null,null,null,null,null);
+#table structure;
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	YES	MUL	NULL	
+b	int(11)	YES	MUL	NULL	
+c	varchar(2000)	YES	MUL	NULL	
+d	text	YES		NULL	
+e	varchar(3000)	YES	MUL	NULL	
+f	longblob	YES		NULL	
+g	int(11)	YES		NULL	
+h	text	YES		NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` varchar(2000) DEFAULT NULL,
+  `d` text DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` longblob DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `h` text DEFAULT NULL,
+  UNIQUE KEY `a` (`a`,`b`,`c`),
+  UNIQUE KEY `c` (`c`,`d`,`e`),
+  UNIQUE KEY `e` (`e`,`f`,`g`,`h`),
+  UNIQUE KEY `b` (`b`,`d`,`g`,`h`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	HASH_INDEX		
+t1	0	a	2	b	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	3	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	1	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	2	d	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	3	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	1	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	2	f	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	4	h	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	1	b	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	2	d	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	4	h	A	0	NULL	NULL	YES	HASH_INDEX		
+
+MyISAM file:         DATADIR/test/t1
+Record format:       Packed
+Character set:       latin1_swedish_ci (8)
+Data records:                    9  Deleted blocks:                 0
+Recordlength:                 5092
+
+table description:
+Key Start Len Index   Type
+1   5081  8   multip. ulonglong prefix NULL
+2   5073  8   multip. ulonglong prefix NULL
+3   5065  8   multip. ulonglong prefix NULL
+4   5057  8   multip. ulonglong NULL       
+select * from information_schema.columns where table_schema = 'test' and table_name = 't1';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	COLUMN_DEFAULT	IS_NULLABLE	DATA_TYPE	CHARACTER_MAXIMUM_LENGTH	CHARACTER_OCTET_LENGTH	NUMERIC_PRECISION	NUMERIC_SCALE	DATETIME_PRECISION	CHARACTER_SET_NAME	COLLATION_NAME	COLUMN_TYPE	COLUMN_KEY	EXTRA	PRIVILEGES	COLUMN_COMMENT	IS_GENERATED	GENERATION_EXPRESSION
+def	test	t1	a	1	NULL	YES	blob	65535	65535	NULL	NULL	NULL	NULL	NULL	blob	MUL		select,insert,update,references		NEVER	NULL
+def	test	t1	b	2	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)	MUL		select,insert,update,references		NEVER	NULL
+def	test	t1	c	3	NULL	YES	varchar	2000	2000	NULL	NULL	NULL	latin1	latin1_swedish_ci	varchar(2000)	MUL		select,insert,update,references		NEVER	NULL
+def	test	t1	d	4	NULL	YES	text	65535	65535	NULL	NULL	NULL	latin1	latin1_swedish_ci	text			select,insert,update,references		NEVER	NULL
+def	test	t1	e	5	NULL	YES	varchar	3000	3000	NULL	NULL	NULL	latin1	latin1_swedish_ci	varchar(3000)	MUL		select,insert,update,references		NEVER	NULL
+def	test	t1	f	6	NULL	YES	longblob	4294967295	4294967295	NULL	NULL	NULL	NULL	NULL	longblob			select,insert,update,references		NEVER	NULL
+def	test	t1	g	7	NULL	YES	int	NULL	NULL	10	0	NULL	NULL	NULL	int(11)			select,insert,update,references		NEVER	NULL
+def	test	t1	h	8	NULL	YES	text	65535	65535	NULL	NULL	NULL	latin1	latin1_swedish_ci	text			select,insert,update,references		NEVER	NULL
+select * from information_schema.statistics where table_schema = 'test' and table_name = 't1';
+TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	NON_UNIQUE	INDEX_SCHEMA	INDEX_NAME	SEQ_IN_INDEX	COLUMN_NAME	COLLATION	CARDINALITY	SUB_PART	PACKED	NULLABLE	INDEX_TYPE	COMMENT	INDEX_COMMENT
+def	test	t1	0	test	a	1	a	A	NULL	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	a	2	b	A	NULL	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	a	3	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	c	1	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	c	2	d	A	0	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	c	3	e	A	0	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	e	1	e	A	0	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	e	2	f	A	0	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	e	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	e	4	h	A	0	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	b	1	b	A	0	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	b	2	d	A	0	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	b	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+def	test	t1	0	test	b	4	h	A	0	NULL	NULL	YES	HASH_INDEX		
+select * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1';
+CONSTRAINT_CATALOG	CONSTRAINT_SCHEMA	CONSTRAINT_NAME	TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	COLUMN_NAME	ORDINAL_POSITION	POSITION_IN_UNIQUE_CONSTRAINT	REFERENCED_TABLE_SCHEMA	REFERENCED_TABLE_NAME	REFERENCED_COLUMN_NAME
+def	test	a	def	test	t1	a	1	NULL	NULL	NULL	NULL
+def	test	a	def	test	t1	b	2	NULL	NULL	NULL	NULL
+def	test	a	def	test	t1	c	3	NULL	NULL	NULL	NULL
+def	test	c	def	test	t1	c	1	NULL	NULL	NULL	NULL
+def	test	c	def	test	t1	d	2	NULL	NULL	NULL	NULL
+def	test	c	def	test	t1	e	3	NULL	NULL	NULL	NULL
+def	test	e	def	test	t1	e	1	NULL	NULL	NULL	NULL
+def	test	e	def	test	t1	f	2	NULL	NULL	NULL	NULL
+def	test	e	def	test	t1	g	3	NULL	NULL	NULL	NULL
+def	test	e	def	test	t1	h	4	NULL	NULL	NULL	NULL
+def	test	b	def	test	t1	b	1	NULL	NULL	NULL	NULL
+def	test	b	def	test	t1	d	2	NULL	NULL	NULL	NULL
+def	test	b	def	test	t1	g	3	NULL	NULL	NULL	NULL
+def	test	b	def	test	t1	h	4	NULL	NULL	NULL	NULL
+# table select we should not be able to see db_row_hash_1 column;
+select * from t1 order by a;
+a	b	c	d	e	f	g	h
+NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL
+1	1	1	1	1	1	1	1
+2	2	2	2	2	2	2	2
+3	3	3	3	3	3	3	3
+4	4	4	4	4	4	4	4
+5	5	5	5	5	5	5	5
+maria	6	maria	maria	maria	maria	6	maria
+mariadb	7	mariadb	mariadb	mariadb	mariadb	8	mariadb
+select db_row_hash_1 from t1;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list'
+select db_row_hash_2 from t1;
+ERROR 42S22: Unknown column 'db_row_hash_2' in 'field list'
+select db_row_hash_3 from t1;
+ERROR 42S22: Unknown column 'db_row_hash_3' in 'field list'
+#duplicate entry test;
+#duplicate keys entry;
+insert into t1 values(1,1,1,0,0,0,0,0);
+ERROR 23000: Duplicate entry '1-1-1' for key 'a'
+insert into t1 values(0,0,1,1,1,0,0,0);
+ERROR 23000: Duplicate entry '1-1-1' for key 'c'
+insert into t1 values(0,0,0,0,1,1,1,1);
+ERROR 23000: Duplicate entry '1-1-1-1' for key 'e'
+insert into t1 values(1,1,1,1,1,0,0,0);
+ERROR 23000: Duplicate entry '1-1-1' for key 'a'
+insert into t1 values(0,0,0,0,1,1,1,1);
+ERROR 23000: Duplicate entry '1-1-1-1' for key 'e'
+insert into t1 values(1,1,1,1,1,1,1,1);
+ERROR 23000: Duplicate entry '1-1-1' for key 'a'
+select db_row_hash_1,db_row_hash_2,db_row_hash_3,db_row_hash_4,db_row_hash_5 from t1;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 'field list'
+alter table t1 drop column db_row_hash_1, drop column db_row_hash_2, drop column db_row_hash_3;
+ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists
+alter table t1 add column dg int , add column ef int , drop column db_row_hash_1;
+ERROR 42000: Can't DROP COLUMN `db_row_hash_1`; check that it exists
+alter table t1 modify column db_row_hash_1 int , modify column db_row_hash_2 int, modify column db_row_hash_3 int;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 add column ar int , add column rb int, modify column db_row_hash_1 int , modify column db_row_hash_3 int;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 change column db_row_hash_1 dsds int , change column  db_row_hash_2  dfdf int , change column db_row_hash_3 gdfg int ;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+alter table t1 add column asd int, drop column a, change column db_row_hash_1 dsds int, change db_row_hash_3 fdfdfd int;
+ERROR 42S22: Unknown column 'db_row_hash_1' in 't1'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` varchar(2000) DEFAULT NULL,
+  `d` text DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` longblob DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `h` text DEFAULT NULL,
+  UNIQUE KEY `a` (`a`,`b`,`c`),
+  UNIQUE KEY `c` (`c`,`d`,`e`),
+  UNIQUE KEY `e` (`e`,`f`,`g`,`h`),
+  UNIQUE KEY `b` (`b`,`d`,`g`,`h`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+# add column named db_row_hash_*;
+alter table t1 add column db_row_hash_7 int , add column db_row_hash_5 int,
+add column db_row_hash_1 int, add column db_row_hash_2 int;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` varchar(2000) DEFAULT NULL,
+  `d` text DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` longblob DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `h` text DEFAULT NULL,
+  `db_row_hash_7` int(11) DEFAULT NULL,
+  `db_row_hash_5` int(11) DEFAULT NULL,
+  `db_row_hash_1` int(11) DEFAULT NULL,
+  `db_row_hash_2` int(11) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535),`b`,`c`),
+  UNIQUE KEY `c` (`c`,`d`(65535),`e`),
+  UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)),
+  UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	65535	NULL	YES	HASH_INDEX		
+t1	0	a	2	b	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	3	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	1	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	c	3	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	1	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	2	f	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	4	h	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	1	b	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	4	h	A	0	65535	NULL	YES	HASH_INDEX		
+alter table t1 drop column db_row_hash_7  , drop column db_row_hash_5 ,
+drop column db_row_hash_1, drop column db_row_hash_2 ;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` varchar(2000) DEFAULT NULL,
+  `d` text DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` longblob DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `h` text DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535),`b`,`c`),
+  UNIQUE KEY `c` (`c`,`d`(65535),`e`),
+  UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)),
+  UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	65535	NULL	YES	HASH_INDEX		
+t1	0	a	2	b	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	3	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	1	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	c	3	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	1	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	2	f	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	4	h	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	1	b	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	4	h	A	0	65535	NULL	YES	HASH_INDEX		
+#try to change column names;
+alter table t1 change column a aa blob , change column b bb blob , change column d dd blob;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `aa` blob DEFAULT NULL,
+  `bb` blob DEFAULT NULL,
+  `c` varchar(2000) DEFAULT NULL,
+  `dd` blob DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` longblob DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `h` text DEFAULT NULL,
+  UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)),
+  UNIQUE KEY `a` (`aa`(65535),`bb`,`c`),
+  UNIQUE KEY `c` (`c`,`dd`(65535),`e`),
+  UNIQUE KEY `b` (`bb`,`dd`(65535),`g`,`h`(65535))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	e	1	e	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	2	f	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	3	g	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	4	h	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	a	1	aa	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	a	2	bb	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	3	c	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	1	c	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	2	dd	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	c	3	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	1	bb	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	2	dd	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	4	h	A	0	65535	NULL	YES	HASH_INDEX		
+alter table t1 change column aa a blob , change column bb b blob , change column dd d blob;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` blob DEFAULT NULL,
+  `c` varchar(2000) DEFAULT NULL,
+  `d` blob DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` longblob DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `h` text DEFAULT NULL,
+  UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)),
+  UNIQUE KEY `a` (`a`(65535),`b`,`c`),
+  UNIQUE KEY `c` (`c`,`d`(65535),`e`),
+  UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	e	1	e	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	2	f	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	3	g	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	4	h	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	a	1	a	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	a	2	b	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	3	c	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	1	c	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	c	3	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	1	b	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	4	h	A	0	65535	NULL	YES	HASH_INDEX		
+#now we will change the data type to int and varchar limit so that we no longer  require hash_index;
+#on key a_b_c;
+alter table t1 modify column a varchar(20) , modify column b varchar(20) , modify column c varchar(20);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(20) DEFAULT NULL,
+  `b` varchar(20) DEFAULT NULL,
+  `c` varchar(20) DEFAULT NULL,
+  `d` blob DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` longblob DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `h` text DEFAULT NULL,
+  UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)),
+  UNIQUE KEY `a` (`a`,`b`,`c`),
+  UNIQUE KEY `c` (`c`,`d`(65535),`e`),
+  UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	e	1	e	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	2	f	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	3	g	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	4	h	A	NULL	65535	NULL	YES	HASH_INDEX		
+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	3	c	A	0	NULL	NULL	YES	BTREE		
+t1	0	c	1	c	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	c	3	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	1	b	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	4	h	A	0	65535	NULL	YES	HASH_INDEX		
+#change it back;
+alter table t1 modify column a blob , modify column b blob , modify column c blob;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` blob DEFAULT NULL,
+  `c` blob DEFAULT NULL,
+  `d` blob DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` longblob DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `h` text DEFAULT NULL,
+  UNIQUE KEY `e` (`e`,`f`,`g`,`h`(65535)),
+  UNIQUE KEY `a` (`a`,`b`,`c`),
+  UNIQUE KEY `c` (`c`,`d`(65535),`e`),
+  UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	e	1	e	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	2	f	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	3	g	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	4	h	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	a	1	a	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	2	b	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	3	c	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	1	c	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	c	3	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	1	b	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	4	h	A	0	65535	NULL	YES	HASH_INDEX		
+#try to delete blob column in unique;
+truncate table t1;
+#now try to delete keys;
+alter table t1 drop key c, drop key e;
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` blob DEFAULT NULL,
+  `c` blob DEFAULT NULL,
+  `d` blob DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` longblob DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `h` text DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535),`b`(65535),`c`(65535)),
+  UNIQUE KEY `b` (`b`(65535),`d`(65535),`g`,`h`(65535))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	65535	NULL	YES	HASH_INDEX		
+t1	0	a	2	b	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	a	3	c	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	1	b	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	b	3	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	4	h	A	0	65535	NULL	YES	HASH_INDEX		
+drop table t1;
+#now alter table containing some data basically some tests with ignore;
+create table t1 (a blob);
+insert into t1 values(1),(2),(3);
+#normal alter table;
+alter table t1 add unique key(a);
+alter table t1 drop key a;
+truncate table t1;
+insert into t1 values(1),(1),(2),(2),(3);
+alter table t1 add unique key(a);
+ERROR 23000: Duplicate entry '1' for key 'a'
+alter ignore table t1 add unique key(a);
+select * from t1 order by a;
+a
+1
+2
+3
+insert into t1 values(1);
+ERROR 23000: Duplicate entry '1' for key 'a'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	65535	NULL	YES	HASH_INDEX		
+drop table t1;
+#Now with multiple keys;
+create table t1(a blob , b blob, c blob , d blob , e int);
+insert into t1 values (1,1,1,1,1);
+insert into t1 values (1,1,1,1,1);
+insert into t1 values (2,1,1,1,1);
+insert into t1 values (2,2,2,2,2);
+insert into t1 values (3,3,4,4,4);
+insert into t1 values (4,4,4,4,4);
+alter table t1 add unique key(a,c), add unique key(b,d), add unique key(e);
+ERROR 23000: Duplicate entry '1-1' for key 'a'
+alter ignore  table t1 add unique key(a,c), add unique key(b,d), add unique key(e);
+select * from t1 order by a;
+a	b	c	d	e
+1	1	1	1	1
+2	2	2	2	2
+3	3	4	4	4
+insert into t1 values (1,12,1,13,14);
+ERROR 23000: Duplicate entry '1-1' for key 'a'
+insert into t1 values (12,1,14,1,14);
+ERROR 23000: Duplicate entry '1-1' for key 'b'
+insert into t1 values (13,12,13,14,4);
+ERROR 23000: Duplicate entry '4' for key 'e'
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  `b` blob DEFAULT NULL,
+  `c` blob DEFAULT NULL,
+  `d` blob DEFAULT NULL,
+  `e` int(11) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(65535),`c`(65535)),
+  UNIQUE KEY `b` (`b`(65535),`d`(65535)),
+  UNIQUE KEY `e` (`e`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	65535	NULL	YES	HASH_INDEX		
+t1	0	a	2	c	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	b	1	b	A	NULL	65535	NULL	YES	HASH_INDEX		
+t1	0	b	2	d	A	0	65535	NULL	YES	HASH_INDEX		
+t1	0	e	1	e	A	0	NULL	NULL	YES	BTREE		
+drop table t1;
+#visibility of db_row_hash 
+create table t1 (a blob unique , b blob unique);
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	YES	UNI	NULL	
+b	blob	YES	UNI	NULL	
+insert into t1 values(1,19);
+insert into t1 values(2,29);
+insert into t1 values(3,39);
+insert into t1 values(4,49);
+create table t2 (DB_ROW_HASH_1 int, DB_ROW_HASH_2 int);
+insert into t2 values(11,1);
+insert into t2 values(22,2);
+insert into t2 values(33,3);
+insert into t2 values(44,4);
+select * from t1 order by a;
+a	b
+1	19
+2	29
+3	39
+4	49
+select * from t2 order by DB_ROW_HASH_1;
+DB_ROW_HASH_1	DB_ROW_HASH_2
+11	1
+22	2
+33	3
+44	4
+select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1;
+ERROR 42S22: Unknown column 'DB_ROW_HASH_1' in 'field list'
+#bug
+select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1,t2;
+DB_ROW_HASH_1	DB_ROW_HASH_2
+11	1
+11	1
+11	1
+11	1
+22	2
+22	2
+22	2
+22	2
+33	3
+33	3
+33	3
+33	3
+44	4
+44	4
+44	4
+44	4
+select * from t1 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t2);
+ERROR 42S22: Unknown column 'DB_ROW_HASH_1' in 'IN/ALL/ANY subquery'
+select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1,t2 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t2);
+DB_ROW_HASH_1	DB_ROW_HASH_2
+11	1
+22	2
+33	3
+44	4
+11	1
+22	2
+33	3
+44	4
+11	1
+22	2
+33	3
+44	4
+11	1
+22	2
+33	3
+44	4
+select * from t2 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t1);
+DB_ROW_HASH_1	DB_ROW_HASH_2
+11	1
+22	2
+33	3
+44	4
+select DB_ROW_HASH_1 from t1,t2 where t1.DB_ROW_HASH_1 = t2.DB_ROW_HASH_2;
+ERROR 42S22: Unknown column 't1.DB_ROW_HASH_1' in 'where clause'
+select DB_ROW_HASH_1 from t1 inner join t2 on t1.a = t2.DB_ROW_HASH_2;
+DB_ROW_HASH_1
+11
+22
+33
+44
+drop table t1,t2;
+#very long blob entry;
+SET @@GLOBAL.max_allowed_packet=67108864;
+connect  'newcon', localhost, root,,;
+connection newcon;
+show variables like 'max_allowed_packet';
+Variable_name	Value
+max_allowed_packet	67108864
+create table t1(a longblob unique, b longblob , c longblob , unique(b,c));
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	longblob	YES	UNI	NULL	
+b	longblob	YES	MUL	NULL	
+c	longblob	YES		NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` longblob DEFAULT NULL,
+  `b` longblob DEFAULT NULL,
+  `c` longblob DEFAULT NULL,
+  UNIQUE KEY `a` (`a`),
+  UNIQUE KEY `b` (`b`,`c`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	HASH_INDEX		
+t1	0	b	1	b	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	2	c	A	0	NULL	NULL	YES	HASH_INDEX		
+insert into t1 values(concat(repeat('sachin',10000000),'1'),concat(repeat('sachin',10000000),'1'),
+concat(repeat('sachin',10000000),'1'));
+insert into t1 values(concat(repeat('sachin',10000000),'2'),concat(repeat('sachin',10000000),'2'),
+concat(repeat('sachin',10000000),'1'));
+insert into t1 values(concat(repeat('sachin',10000000),'2'),concat(repeat('sachin',10000000),'2'),
+concat(repeat('sachin',10000000),'4'));
+ERROR 23000: Duplicate entry 'sachinsachinsachinsachinsachinsachinsachinsachinsachinsachinsach' for key 'a'
+insert into t1 values(concat(repeat('sachin',10000000),'3'),concat(repeat('sachin',10000000),'1'),
+concat(repeat('sachin',10000000),'1'));
+ERROR 23000: Duplicate entry 'sachinsachinsachinsachinsachinsachinsachinsachinsachinsachinsach' for key 'b'
+drop table t1;
+#long key unique with different key length
+create table t1(a blob, unique(a(3000)));
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	YES	UNI	NULL	
+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	3000	NULL	YES	HASH_INDEX		
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(3000))
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 value(concat(repeat('s',3000),'1'));
+insert into t1 value(concat(repeat('s',3000),'2'));
+ERROR 23000: Duplicate entry 'ssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssssss' for key 'a'
+insert into t1 value(concat(repeat('a',3000),'2'));
+drop table t1;
+create table t1(a varchar(4000), b longblob , c varchar(5000), d longblob, 
+unique(a(3500), b(1000000)), unique(c(4500), d(10000000)));
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	varchar(4000)	YES	MUL	NULL	
+b	longblob	YES		NULL	
+c	varchar(5000)	YES	MUL	NULL	
+d	longblob	YES		NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` varchar(4000) DEFAULT NULL,
+  `b` longblob DEFAULT NULL,
+  `c` varchar(5000) DEFAULT NULL,
+  `d` longblob DEFAULT NULL,
+  UNIQUE KEY `a` (`a`(3500),`b`),
+  UNIQUE KEY `c` (`c`(4500),`d`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	3500	NULL	YES	HASH_INDEX		
+t1	0	a	2	b	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	1	c	A	0	4500	NULL	YES	HASH_INDEX		
+t1	0	c	2	d	A	0	NULL	NULL	YES	HASH_INDEX		
+drop table t1;
+disconnect newcon;
+connection default;
+SET @@GLOBAL.max_allowed_packet=4194304;
+#ext bug
+create table t1(a int primary key, b blob unique, c int, d blob , index(c));
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` blob DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  `d` blob DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  UNIQUE KEY `b` (`b`),
+  KEY `c` (`c`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values(1,23,1,33);
+insert into t1 values(2,23,1,33);
+ERROR 23000: Duplicate entry '23' for key 'b'
+drop table t1;
+create table t2 (a blob unique , c int , index(c));
+show create table t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `a` blob DEFAULT NULL,
+  `c` int(11) DEFAULT NULL,
+  UNIQUE KEY `a` (`a`),
+  KEY `c` (`c`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t2 values(1,1);
+insert into t2 values(2,1);
+drop table t2;
+#not null test //todo solve warnings
+create table t1(a blob unique not null);
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	NO	PRI	NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob NOT NULL,
+  UNIQUE KEY `a` (`a`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values(1);
+insert into t1 values(3);
+insert into t1 values(1);
+ERROR 23000: Duplicate entry '1' for key 'a'
+drop table t1;
+create table t1(a int primary key, b blob unique , c blob unique not null);
+insert into t1 values(1,1,1);
+insert into t1 values(2,1,2);
+ERROR 23000: Duplicate entry '1' for key 'b'
+insert into t1 values(3,3,1);
+ERROR 23000: Duplicate entry '1' for key 'c'
+drop table t1;
+create table t1 (a blob unique not null, b blob not null, c blob not null, unique(b,c));
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	blob	NO	PRI	NULL	
+b	blob	NO	MUL	NULL	
+c	blob	NO		NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` blob NOT NULL,
+  `b` blob NOT NULL,
+  `c` blob NOT NULL,
+  UNIQUE KEY `a` (`a`),
+  UNIQUE KEY `b` (`b`,`c`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values (1, 2, 3);
+insert into t1 values (2, 1, 3);
+insert into t1 values (2, 1, 3);
+ERROR 23000: Duplicate entry '2' for key 'a'
+drop table t1;
+set @@GLOBAL.max_allowed_packet= @allowed_packet;
diff --git a/mysql-test/main/long_unique.test b/mysql-test/main/long_unique.test
new file mode 100644
index 00000000000..e70a38e0928
--- /dev/null
+++ b/mysql-test/main/long_unique.test
@@ -0,0 +1,468 @@
+let datadir=`select @@datadir`;
+--echo #Structure of tests
+--echo #First we will check all option for
+--echo #table containing single unique column
+--echo #table containing keys like unique(a,b,c,d) etc
+--echo #then table containing 2 blob unique etc 
+set @allowed_packet= @@max_allowed_packet;
+
+--echo #table with single long blob column; 
+create table t1(a blob unique);
+insert into t1 values(1),(2),(3),(56),('sachin'),('maria'),(123456789034567891),(null),(null),(123456789034567890);
+
+--echo #table structure;
+desc t1;
+show create table t1;
+query_vertical show keys from t1;
+replace_result $datadir DATADIR;
+exec $MYISAMCHK -d $datadir/test/t1;
+select * from information_schema.columns where table_schema = 'test' and table_name = 't1';
+select * from information_schema.statistics where table_schema = 'test' and table_name = 't1';
+query_vertical select   * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1';
+--echo # table select we should not be able to see db_row_hash_column;
+select * from t1 order by a;
+--error ER_BAD_FIELD_ERROR
+select db_row_hash_1 from t1;
+--echo #duplicate entry test;
+--error ER_DUP_ENTRY
+insert into t1 values(2);
+--error ER_DUP_ENTRY
+insert into t1 values('sachin');
+--error ER_DUP_ENTRY
+insert into t1 values(123456789034567891);
+select * from t1 order by a;
+insert into t1 values(11),(22),(33);
+--error ER_DUP_ENTRY
+insert into t1 values(12),(22);
+select * from t1 order by a;
+insert into t1 values(repeat('s',4000*10)),(repeat('s',4001*10));
+--error ER_DUP_ENTRY
+insert into t1 values(repeat('m',4000*10)),(repeat('m',4000*10));
+insert into t1 values(repeat('m',4001)),(repeat('m',4002));
+truncate table t1;
+insert into t1 values(1),(2),(3),(4),(5),(8),(7);
+replace_result $datadir DATADIR;
+exec $MYISAMCHK -d $datadir/test/t1;
+--echo #now some alter commands;
+alter table t1 add column b int;
+desc t1;
+show create table t1;
+--error ER_DUP_ENTRY
+insert into t1 values(1,2);
+--error ER_DUP_ENTRY
+insert into t1 values(2,2);
+--error ER_BAD_FIELD_ERROR
+select db_row_hash_1 from t1;
+--echo #now try to change db_row_hash_1 column;
+--error ER_CANT_DROP_FIELD_OR_KEY
+alter table t1 drop column db_row_hash_1;
+--error ER_CANT_DROP_FIELD_OR_KEY
+alter table t1 add column d int , add column e int , drop column db_row_hash_1;
+--error ER_BAD_FIELD_ERROR
+alter table t1 modify column db_row_hash_1 int ;
+--error ER_BAD_FIELD_ERROR
+alter table t1 add column a int , add column b int, modify column db_row_hash_1 int ;
+--error ER_BAD_FIELD_ERROR
+alter table t1 change column db_row_hash_1 dsds int;
+--error ER_BAD_FIELD_ERROR
+alter table t1 add column asd int, change column db_row_hash_1 dsds int;
+alter table t1 drop column b , add column c int;
+show create table t1;
+
+--echo #now add some column with name db_row_hash;
+alter table t1 add column db_row_hash_1 int unique;
+show create table t1;
+--error ER_DUP_ENTRY
+insert into t1 values(45,1,55),(46,1,55);
+--error ER_DUP_ENTRY
+insert into t1 values(45,1,55),(45,1,55);
+alter table t1 add column db_row_hash_2 int, add column db_row_hash_3 int;
+desc t1;
+--echo #this should also drop the unique index ;
+alter table t1 drop column a;
+show create table t1;
+show keys from t1;
+--echo #add column with unique index on blob ;
+alter table t1 add column a blob unique;
+show create table t1;
+--echo # try to change the blob unique name;
+--echo #this will change index to b tree;
+alter table t1 change column a aa blob ;
+show create table t1;
+show keys from t1;
+--echo # try to change the blob unique datatype;
+--echo #this will change index to b tree;
+alter table t1 modify  column aa  int ;
+show create table t1;
+show keys from t1;
+alter table t1 add column clm blob unique;
+--echo #try changing the name ;
+alter table t1 change column clm clm_changed blob;
+show create table t1;
+show keys from t1;
+--echo #now drop the unique key;
+alter table t1 drop key clm;
+show create table t1;
+show keys from t1;
+drop table t1;
+
+create table t1 (a TEXT CHARSET latin1 COLLATE latin1_german2_ci unique);
+desc t1;
+show keys from t1;
+ insert into t1 values ('ae');
+--error ER_DUP_ENTRY
+insert into t1 values ('AE');
+insert  into t1 values ('Ä');
+drop table t1;
+create table t1 (a int primary key, b blob unique);
+desc t1;
+show keys from t1;
+insert into t1 values(1,1),(2,2),(3,3);
+--error ER_DUP_ENTRY
+insert into t1 values(1,1);
+--error ER_DUP_ENTRY
+insert into t1 values(7,1);
+drop table t1;
+
+--echo #table with multiple  long blob column and varchar text column ; 
+create table t1(a blob unique, b int , c blob unique , d text unique , e varchar(3000) unique);
+insert into t1 values(1,2,3,4,5),(2,11,22,33,44),(3111,222,333,444,555),(5611,2222,3333,4444,5555),
+('sachin',341,'fdf','gfgfgfg','hghgr'),('maria',345,'frter','dasd','utyuty'),
+(123456789034567891,353534,53453453453456,64565464564564,45435345345345),
+(123456789034567890,43545,657567567567,78967657567567,657567567567567676);
+
+--echo #table structure;
+desc t1;
+show create table t1;
+show keys from t1;
+replace_result $datadir DATADIR;
+exec $MYISAMCHK -d $datadir/test/t1;
+select * from information_schema.columns where table_schema = 'test' and table_name = 't1';
+select * from information_schema.statistics where table_schema = 'test' and table_name = 't1';
+select * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1';
+--echo #table select we should not be able to see db_row_hash_1 column;
+select * from t1 order by a;
+--error ER_BAD_FIELD_ERROR
+select db_row_hash_1 from t1;
+--error ER_BAD_FIELD_ERROR
+select db_row_hash_2 from t1;
+--error ER_BAD_FIELD_ERROR
+select db_row_hash_3 from t1;
+--echo #duplicate entry test;
+--error ER_DUP_ENTRY
+insert into t1 values(21,2,3,42,51);
+--error ER_DUP_ENTRY
+insert into t1 values('sachin',null,null,null,null);
+--error ER_DUP_ENTRY
+insert into t1 values(1234567890345671890,4353451,6575675675617,789676575675617,657567567567567676);
+select * from t1 order by a;
+insert into t1 values(repeat('s',4000*10),100,repeat('s',4000*10),repeat('s',4000*10),
+repeat('s',400)),(repeat('s',4001*10),1000,repeat('s',4001*10),repeat('s',4001*10),
+repeat('s',2995));
+--error ER_DUP_ENTRY
+insert into t1 values(repeat('m',4000*11),10,repeat('s',4000*11),repeat('s',4000*11),repeat('s',2995));
+truncate table t1;
+insert into t1 values(1,2,3,4,5),(2,11,22,33,44),(3111,222,333,444,555),(5611,2222,3333,4444,5555);
+
+--echo #now some alter commands;
+alter table t1 add column f int;
+desc t1;
+show create table t1;
+--echo #unique key should not break;
+--error ER_DUP_ENTRY
+insert into t1 values(1,2,3,4,5,6);
+--error ER_BAD_FIELD_ERROR
+select db_row_hash_1 , db_row_hash_2, db_row_hash_3 from t1;
+--echo #now try to change db_row_hash_1 column;
+--error ER_CANT_DROP_FIELD_OR_KEY
+alter table t1 drop column db_row_hash_1, drop column db_row_hash_2, drop column db_row_hash_3;
+--error ER_CANT_DROP_FIELD_OR_KEY
+alter table t1 add column dg int , add column ef int , drop column db_row_hash_1;
+--error ER_BAD_FIELD_ERROR
+alter table t1 modify column db_row_hash_1 int , modify column db_row_hash_2 int, modify column db_row_hash_3 int;
+--error ER_BAD_FIELD_ERROR
+alter table t1 add column ar int , add column rb int, modify column db_row_hash_1 int , modify column db_row_hash_3 int;
+--error ER_BAD_FIELD_ERROR
+alter table t1 change column db_row_hash_1 dsds int , change column  db_row_hash_2  dfdf int , change column db_row_hash_3 gdfg int ;
+--error ER_BAD_FIELD_ERROR
+alter table t1 add column asd int, drop column a, change column db_row_hash_1 dsds int, change db_row_hash_3 fdfdfd int;
+alter table t1 drop column b , add column g int;
+show create table t1;
+
+--echo #now add some column with name db_row_hash;
+alter table t1 add column db_row_hash_1 int unique;
+alter table t1 add column db_row_hash_2 int unique;
+alter table t1 add column db_row_hash_3 int unique;
+show create table t1;
+
+alter table t1 add column db_row_hash_7 int, add column db_row_hash_5 int , add column db_row_hash_4 int ;
+alter table t1 drop column db_row_hash_7,drop column db_row_hash_3, drop column db_row_hash_4;
+desc t1;
+--echo #this show now break anything;
+--error ER_DUP_ENTRY
+insert into t1 values(1,2,3,4,5,6,23,5,6);
+--echo #this should also drop the unique index;
+alter table t1 drop column a, drop column c;
+show create table t1;
+show keys from t1;
+--echo #add column with unique index on blob;
+alter table t1 add column a blob unique;
+show create table t1;
+show keys from t1;
+--echo #try to change the blob unique column name;
+--echo #this will change index to b tree;
+alter table t1 modify  column a  int ,  modify column e int;
+show create table t1;
+show keys from t1;
+alter table t1 add column clm1 blob unique,add column clm2 blob unique;
+--echo #try changing the name; 
+alter table t1 change column clm1 clm_changed1 blob, change column clm2 clm_changed2 blob;
+show create table t1;
+show keys from t1;
+--echo #now drop the unique key;
+alter table t1 drop key clm1, drop key clm2;
+show create table t1;
+show keys from t1;
+drop table t1;
+
+--echo #now the table with key on multiple columns; the ultimate test;
+create table t1(a blob, b int , c varchar(2000) , d text  , e varchar(3000) , f longblob , g int , h text ,
+  unique(a,b,c), unique(c,d,e),unique(e,f,g,h), unique(b,d,g,h));
+
+insert into t1 values(1,1,1,1,1,1,1,1),(2,2,2,2,2,2,2,2),(3,3,3,3,3,3,3,3),(4,4,4,4,4,4,4,4),(5,5,5,5,5,5,5,5),
+('maria',6,'maria','maria','maria','maria',6,'maria'),('mariadb',7,'mariadb','mariadb','mariadb','mariadb',8,'mariadb')
+,(null,null,null,null,null,null,null,null),(null,null,null,null,null,null,null,null);
+
+--echo #table structure;
+desc t1;
+show create table t1;
+show keys from t1;
+replace_result $datadir DATADIR;
+exec $MYISAMCHK -d $datadir/test/t1;
+select * from information_schema.columns where table_schema = 'test' and table_name = 't1';
+select * from information_schema.statistics where table_schema = 'test' and table_name = 't1';
+select * from information_schema.key_column_usage where table_schema= 'test' and table_name= 't1';
+--echo # table select we should not be able to see db_row_hash_1 column;
+select * from t1 order by a;
+--error ER_BAD_FIELD_ERROR
+select db_row_hash_1 from t1;
+--error ER_BAD_FIELD_ERROR
+select db_row_hash_2 from t1;
+--error ER_BAD_FIELD_ERROR
+select db_row_hash_3 from t1;
+--echo #duplicate entry test;
+--echo #duplicate keys entry;
+--error ER_DUP_ENTRY
+insert into t1 values(1,1,1,0,0,0,0,0);
+--error ER_DUP_ENTRY
+insert into t1 values(0,0,1,1,1,0,0,0);
+--error ER_DUP_ENTRY
+insert into t1 values(0,0,0,0,1,1,1,1);
+--error ER_DUP_ENTRY
+insert into t1 values(1,1,1,1,1,0,0,0);
+--error ER_DUP_ENTRY
+insert into t1 values(0,0,0,0,1,1,1,1);
+--error ER_DUP_ENTRY
+insert into t1 values(1,1,1,1,1,1,1,1);
+--error ER_BAD_FIELD_ERROR
+select db_row_hash_1,db_row_hash_2,db_row_hash_3,db_row_hash_4,db_row_hash_5 from t1;
+--error ER_CANT_DROP_FIELD_OR_KEY
+alter table t1 drop column db_row_hash_1, drop column db_row_hash_2, drop column db_row_hash_3;
+--error ER_CANT_DROP_FIELD_OR_KEY
+alter table t1 add column dg int , add column ef int , drop column db_row_hash_1;
+--error ER_BAD_FIELD_ERROR
+alter table t1 modify column db_row_hash_1 int , modify column db_row_hash_2 int, modify column db_row_hash_3 int;
+--error ER_BAD_FIELD_ERROR
+alter table t1 add column ar int , add column rb int, modify column db_row_hash_1 int , modify column db_row_hash_3 int;
+--error ER_BAD_FIELD_ERROR
+alter table t1 change column db_row_hash_1 dsds int , change column  db_row_hash_2  dfdf int , change column db_row_hash_3 gdfg int ;
+--error ER_BAD_FIELD_ERROR
+alter table t1 add column asd int, drop column a, change column db_row_hash_1 dsds int, change db_row_hash_3 fdfdfd int;
+
+show create table t1;
+--echo # add column named db_row_hash_*;
+alter table t1 add column db_row_hash_7 int , add column db_row_hash_5 int,
+ add column db_row_hash_1 int, add column db_row_hash_2 int;
+show create table t1;
+show keys from t1;
+alter table t1 drop column db_row_hash_7  , drop column db_row_hash_5 ,
+ drop column db_row_hash_1, drop column db_row_hash_2 ;
+show create table t1;
+show keys from t1;
+
+--echo #try to change column names;
+alter table t1 change column a aa blob , change column b bb blob , change column d dd blob;
+show create table t1;
+show keys from t1;
+alter table t1 change column aa a blob , change column bb b blob , change column dd d blob;
+show create table t1;
+show keys from t1;
+
+--echo #now we will change the data type to int and varchar limit so that we no longer  require hash_index;
+--echo #on key a_b_c;
+alter table t1 modify column a varchar(20) , modify column b varchar(20) , modify column c varchar(20);
+show create table t1;
+show keys from t1;
+--echo #change it back;
+alter table t1 modify column a blob , modify column b blob , modify column c blob;
+show create table t1;
+show keys from t1;
+
+--echo #try to delete blob column in unique;
+truncate table t1;
+## this feature removed in 10.2
+#alter table t1 drop column a, drop column b, drop  column c;
+#show create table t1;
+#show keys from t1;
+--echo #now try to delete keys;
+alter table t1 drop key c, drop key e;
+show create table t1;
+show keys from t1;
+drop table t1;
+
+--echo #now alter table containing some data basically some tests with ignore;
+create table t1 (a blob);
+insert into t1 values(1),(2),(3);
+--echo #normal alter table;
+alter table t1 add unique key(a);
+alter table t1 drop key a;
+truncate table t1;
+insert into t1 values(1),(1),(2),(2),(3);
+--error ER_DUP_ENTRY
+alter table t1 add unique key(a);
+alter ignore table t1 add unique key(a);
+select * from t1 order by a;
+--error ER_DUP_ENTRY
+insert into t1 values(1);
+show create table t1;
+show keys from t1;
+drop table t1;
+
+--echo #Now with multiple keys;
+create table t1(a blob , b blob, c blob , d blob , e int);
+insert into t1 values (1,1,1,1,1);
+insert into t1 values (1,1,1,1,1);
+insert into t1 values (2,1,1,1,1);
+insert into t1 values (2,2,2,2,2);
+insert into t1 values (3,3,4,4,4);
+insert into t1 values (4,4,4,4,4);
+--error ER_DUP_ENTRY
+alter table t1 add unique key(a,c), add unique key(b,d), add unique key(e);
+alter ignore  table t1 add unique key(a,c), add unique key(b,d), add unique key(e);
+select * from t1 order by a;
+--error ER_DUP_ENTRY
+insert into t1 values (1,12,1,13,14);
+--error ER_DUP_ENTRY
+insert into t1 values (12,1,14,1,14);
+--error ER_DUP_ENTRY
+insert into t1 values (13,12,13,14,4);
+show create table t1;
+show keys from t1;
+drop table t1;
+
+--echo #visibility of db_row_hash 
+create table t1 (a blob unique , b blob unique);
+desc t1;
+insert into t1 values(1,19);
+insert into t1 values(2,29);
+insert into t1 values(3,39);
+insert into t1 values(4,49);
+create table t2 (DB_ROW_HASH_1 int, DB_ROW_HASH_2 int);
+insert into t2 values(11,1);
+insert into t2 values(22,2);
+insert into t2 values(33,3);
+insert into t2 values(44,4);
+select * from t1 order by a;
+select * from t2 order by DB_ROW_HASH_1;
+--error ER_BAD_FIELD_ERROR
+select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1;
+--echo #bug
+select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1,t2;
+--error ER_BAD_FIELD_ERROR
+select * from t1 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t2);
+select DB_ROW_HASH_1, DB_ROW_HASH_2 from t1,t2 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t2);
+select * from t2 where DB_ROW_HASH_1 in (select DB_ROW_HASH_1 from t1);
+--error ER_BAD_FIELD_ERROR
+select DB_ROW_HASH_1 from t1,t2 where t1.DB_ROW_HASH_1 = t2.DB_ROW_HASH_2;
+select DB_ROW_HASH_1 from t1 inner join t2 on t1.a = t2.DB_ROW_HASH_2;
+drop table t1,t2;
+
+--echo #very long blob entry;
+SET @@GLOBAL.max_allowed_packet=67108864;
+
+connect ('newcon', localhost, root,,);
+--connection newcon
+show variables like 'max_allowed_packet';
+create table t1(a longblob unique, b longblob , c longblob , unique(b,c));
+desc t1;
+show create table t1;
+show keys from t1;
+insert into t1 values(concat(repeat('sachin',10000000),'1'),concat(repeat('sachin',10000000),'1'),
+concat(repeat('sachin',10000000),'1'));
+insert into t1 values(concat(repeat('sachin',10000000),'2'),concat(repeat('sachin',10000000),'2'),
+concat(repeat('sachin',10000000),'1'));
+--error ER_DUP_ENTRY
+insert into t1 values(concat(repeat('sachin',10000000),'2'),concat(repeat('sachin',10000000),'2'),
+concat(repeat('sachin',10000000),'4'));
+--error ER_DUP_ENTRY
+insert into t1 values(concat(repeat('sachin',10000000),'3'),concat(repeat('sachin',10000000),'1'),
+concat(repeat('sachin',10000000),'1'));
+drop table t1;
+
+--echo #long key unique with different key length
+create table t1(a blob, unique(a(3000)));
+desc t1;
+show keys from t1;
+show create table t1;
+insert into t1 value(concat(repeat('s',3000),'1'));
+--error ER_DUP_ENTRY
+insert into t1 value(concat(repeat('s',3000),'2'));
+insert into t1 value(concat(repeat('a',3000),'2'));
+drop table t1;
+
+create table t1(a varchar(4000), b longblob , c varchar(5000), d longblob, 
+unique(a(3500), b(1000000)), unique(c(4500), d(10000000)));
+desc t1;
+show create table t1;
+show keys from t1;
+drop table t1;
+disconnect newcon;
+--connection default
+SET @@GLOBAL.max_allowed_packet=4194304;
+--echo #ext bug
+create table t1(a int primary key, b blob unique, c int, d blob , index(c));
+show create table t1;
+insert into t1 values(1,23,1,33);
+--error ER_DUP_ENTRY
+insert into t1 values(2,23,1,33);
+drop table t1;
+create table t2 (a blob unique , c int , index(c));
+show create table t2;
+insert into t2 values(1,1);
+insert into t2 values(2,1);
+drop table t2;
+--echo #not null test //todo solve warnings
+create table t1(a blob unique not null);
+desc t1;
+show create table t1;
+insert into t1 values(1);
+insert into t1 values(3);
+--error ER_DUP_ENTRY
+insert into t1 values(1);
+drop table t1;
+create table t1(a int primary key, b blob unique , c blob unique not null);
+insert into t1 values(1,1,1);
+--error ER_DUP_ENTRY
+insert into t1 values(2,1,2);
+--error ER_DUP_ENTRY
+insert into t1 values(3,3,1);
+drop table t1;
+create table t1 (a blob unique not null, b blob not null, c blob not null, unique(b,c));
+desc t1;
+show create table t1;
+insert into t1 values (1, 2, 3);
+insert into t1 values (2, 1, 3);
+--error ER_DUP_ENTRY
+insert into t1 values (2, 1, 3);
+drop table t1;
+set @@GLOBAL.max_allowed_packet= @allowed_packet;
diff --git a/mysql-test/main/long_unique_update.result b/mysql-test/main/long_unique_update.result
new file mode 100644
index 00000000000..6e887ba2e5f
--- /dev/null
+++ b/mysql-test/main/long_unique_update.result
@@ -0,0 +1,316 @@
+#structure of tests;
+#1 test of table containing single unique blob column;
+#2 test of table containing another unique int/ varchar etc column; 
+#3 test of table containing multiple unique blob column like unique(a),unique(b);
+#4 test of table containing multiple multiple unique blob column like unique(a,b...),unique(c,d....);
+#structure of each test;
+#test if update works;
+#test update for duplicate entry;
+#test update for no change keys;
+#test update for ignore ;
+#test 1
+create table t1 (a blob unique);
+show keys from t1;
+Table	t1
+Non_unique	0
+Key_name	a
+Seq_in_index	1
+Column_name	a
+Collation	A
+Cardinality	NULL
+Sub_part	NULL
+Packed	NULL
+Null	YES
+Index_type	HASH_INDEX
+Comment	
+Index_comment	
+insert into t1 values(1),(2),(3),(4),(5);
+select * from t1;
+a
+1
+2
+3
+4
+5
+update t1 set a=11 where a=5;
+update t1 set a=a+20 where a=1;
+select * from t1;
+a
+21
+2
+3
+4
+11
+update t1 set a=3 where a=2;
+ERROR 23000: Duplicate entry '3' for key 'a'
+update t1 set a=4 where a=3;
+ERROR 23000: Duplicate entry '4' for key 'a'
+#no change in blob key
+update t1 set a=3 where a=3;
+update t1 set a=2 where a=2;
+select* from t1;
+a
+21
+2
+3
+4
+11
+#IGNORE;
+update ignore t1 set a=3 where a=2;
+update ignore t1 set a=4 where a=3;
+select * from t1;
+a
+21
+2
+3
+4
+11
+drop table t1;
+#test 2;
+create table t1 (a int primary key, b blob unique , c int unique );
+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	PRIMARY	1	a	A	0	NULL	NULL		BTREE		
+t1	0	b	1	b	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	1	c	A	NULL	NULL	NULL	YES	BTREE		
+insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7);
+select * from t1 limit 3;
+a	b	c
+1	1	1
+2	2	2
+3	3	3
+update t1 set b=34 where a=1;
+update t1 set b=a+c+b+34 where b=2;
+update t1 set b=a+10+b where c=3;
+select * from t1;
+a	b	c
+1	34	1
+2	40	2
+3	16	3
+4	4	4
+5	5	5
+6	6	6
+7	7	7
+truncate table t1;
+insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7);
+update t1 set b=4 where a=3;
+ERROR 23000: Duplicate entry '4' for key 'b'
+update t1 set b=a+1 where b=3;
+ERROR 23000: Duplicate entry '4' for key 'b'
+update t1 set b=a+1 where c=3;
+ERROR 23000: Duplicate entry '4' for key 'b'
+#no change in blob key
+update t1 set b=3 where a=3;
+update t1 set b=2 where b=2;
+update t1 set b=5 where c=5;
+select* from t1;
+a	b	c
+1	1	1
+2	2	2
+3	3	3
+4	4	4
+5	5	5
+6	6	6
+7	7	7
+#IGNORE;
+update ignore t1 set b=3 where a=2;
+update ignore t1 set b=4 where b=3;
+update ignore t1 set b=5 where c=3;
+select * from t1;
+a	b	c
+1	1	1
+2	2	2
+3	3	3
+4	4	4
+5	5	5
+6	6	6
+7	7	7
+drop table t1;
+#test 3;
+create table t1 (a blob unique, b blob unique , c blob unique);
+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	HASH_INDEX		
+t1	0	b	1	b	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	c	1	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7);
+select * from t1 limit 3;
+a	b	c
+1	1	1
+2	2	2
+3	3	3
+update t1 set b=34 where a=1;
+update t1 set b=a+c+b+34 where b=2;
+update t1 set b=a+10+b where c=3;
+select * from t1;
+a	b	c
+1	34	1
+2	40	2
+3	16	3
+4	4	4
+5	5	5
+6	6	6
+7	7	7
+truncate table t1;
+insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7);
+update t1 set b=4 where a=3;
+ERROR 23000: Duplicate entry '4' for key 'b'
+update t1 set b=a+1 where b=3;
+ERROR 23000: Duplicate entry '4' for key 'b'
+update t1 set b=a+1 where c=3;
+ERROR 23000: Duplicate entry '4' for key 'b'
+#no change in blob key
+update t1 set b=3 where a=3;
+update t1 set b=2 where b=2;
+update t1 set b=5 where c=5;
+select* from t1;
+a	b	c
+1	1	1
+2	2	2
+3	3	3
+4	4	4
+5	5	5
+6	6	6
+7	7	7
+#IGNORE;
+update ignore t1 set b=3 where a=2;
+update ignore t1 set b=4 where b=3;
+update ignore t1 set b=5 where c=3;
+update ignore t1 set b=b+3 where a>1 or b>1 or c>1;
+select * from t1;
+a	b	c
+1	1	1
+2	2	2
+3	3	3
+4	4	4
+5	8	5
+6	9	6
+7	10	7
+update ignore t1 set b=b+5 where a>1 and  b<5 and  c<a+b;
+select * from t1;
+a	b	c
+1	1	1
+2	7	2
+3	3	3
+4	4	4
+5	8	5
+6	9	6
+7	10	7
+drop table t1;
+#test 4 ultimate test;
+create table t1 (a int primary key , b int, c blob , d blob , e  varchar(2000), f int , g text, 
+unique (b,c), unique (b,f),unique(e,g),unique(a,b,c,d,e,f,g));
+desc t1;
+Field	Type	Null	Key	Default	Extra
+a	int(11)	NO	PRI	NULL	
+b	int(11)	YES	MUL	NULL	
+c	blob	YES		NULL	
+d	blob	YES		NULL	
+e	varchar(2000)	YES	MUL	NULL	
+f	int(11)	YES		NULL	
+g	text	YES		NULL	
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` int(11) NOT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` blob DEFAULT NULL,
+  `d` blob DEFAULT NULL,
+  `e` varchar(2000) DEFAULT NULL,
+  `f` int(11) DEFAULT NULL,
+  `g` text DEFAULT NULL,
+  PRIMARY KEY (`a`),
+  UNIQUE KEY `b` (`b`,`c`),
+  UNIQUE KEY `b_2` (`b`,`f`),
+  UNIQUE KEY `e` (`e`,`g`),
+  UNIQUE KEY `a` (`a`,`b`,`c`,`d`,`e`,`f`,`g`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+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	PRIMARY	1	a	A	0	NULL	NULL		BTREE		
+t1	0	b	1	b	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	b	2	c	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	b_2	1	b	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	b_2	2	f	A	NULL	NULL	NULL	YES	BTREE		
+t1	0	e	1	e	A	NULL	NULL	NULL	YES	HASH_INDEX		
+t1	0	e	2	g	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	1	a	A	0	NULL	NULL		HASH_INDEX		
+t1	0	a	2	b	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	3	c	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	4	d	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	5	e	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	6	f	A	0	NULL	NULL	YES	HASH_INDEX		
+t1	0	a	7	g	A	0	NULL	NULL	YES	HASH_INDEX		
+insert into t1 values(1,1,1,1,1,1,1),(2,2,2,2,2,2,2),(3,3,3,3,3,3,3),(4,4,4,4,4,4,4),
+(5,5,5,5,5,5,5),(6,6,6,6,6,6,6),(7,7,7,7,7,7,7),(8,8,8,8,8,8,8),(9,9,9,9,9,9,9);
+select * from t1 limit 3;
+a	b	c	d	e	f	g
+1	1	1	1	1	1	1
+2	2	2	2	2	2	2
+3	3	3	3	3	3	3
+#key b_c
+update t1 set b=2 ,c=2 where a=1;
+ERROR 23000: Duplicate entry '2-2' for key 'b'
+update t1 set b=b+34, c=c+34 where e=1 and g=1 ;
+update t1 set b=35, c=35 where e=1 and g=1 ;
+update t1 set b=b+1, c=c+1 where a>0;
+update ignore t1 set b=b+1, c=c+1 where a>0;
+select * from t1 ;
+a	b	c	d	e	f	g
+1	35	35	1	1	1	1
+2	2	2	2	2	2	2
+3	3	3	3	3	3	3
+4	4	4	4	4	4	4
+5	5	5	5	5	5	5
+6	6	6	6	6	6	6
+7	7	7	7	7	7	7
+8	8	8	8	8	8	8
+9	9	9	9	9	9	9
+truncate table t1;
+insert into t1 values(1,1,1,1,1,1,1),(2,2,2,2,2,2,2),(3,3,3,3,3,3,3),(4,4,4,4,4,4,4),
+(5,5,5,5,5,5,5),(6,6,6,6,6,6,6),(7,7,7,7,7,7,7),(8,8,8,8,8,8,8),(9,9,9,9,9,9,9);
+#key b_f no hash key
+update t1 set b=2 , f=2 where a=1;
+ERROR 23000: Duplicate entry '2-2' for key 'b_2'
+update  t1 set b=b+33, f=f+33 where e=1 and g=1;
+update t1 set b=34, f=34 where e=1 and g=1 ;
+update t1 set b=b+1, f=f+1 where a>0;
+ERROR 23000: Duplicate entry '3-3' for key 'b_2'
+update ignore t1 set b=b+1, f=f+1 where a>0;
+select * from t1 ;
+a	b	c	d	e	f	g
+1	36	1	1	1	36	1
+2	2	2	2	2	2	2
+3	3	3	3	3	3	3
+4	4	4	4	4	4	4
+5	5	5	5	5	5	5
+6	6	6	6	6	6	6
+7	7	7	7	7	7	7
+8	8	8	8	8	8	8
+9	10	9	9	9	10	9
+truncate table t1;
+insert into t1 values(1,1,1,1,1,1,1),(2,2,2,2,2,2,2),(3,3,3,3,3,3,3),(4,4,4,4,4,4,4),
+(5,5,5,5,5,5,5),(6,6,6,6,6,6,6),(7,7,7,7,7,7,7),(8,8,8,8,8,8,8),(9,9,9,9,9,9,9);
+#key e_g
+update t1 set e=2 , g=2 where a=1;
+ERROR 23000: Duplicate entry '2-2' for key 'e'
+update  t1 set e=e+34, g=g+34 where a=1;
+update t1 set e=34, g=34 where e=1 and g=1 ;
+select * from t1 where  a=1;
+a	b	c	d	e	f	g
+1	1	1	1	35	1	35
+update t1 set e=e+1, g=g+1 where a>0;
+ERROR 23000: Duplicate entry '3-3' for key 'e'
+update ignore t1 set e=e+1, g=g+1 where a>0;
+select * from t1 ;
+a	b	c	d	e	f	g
+1	1	1	1	36	1	36
+2	2	2	2	2	2	2
+3	3	3	3	3	3	3
+4	4	4	4	4	4	4
+5	5	5	5	5	5	5
+6	6	6	6	6	6	6
+7	7	7	7	7	7	7
+8	8	8	8	8	8	8
+9	9	9	9	9	9	9
+drop table t1;
diff --git a/mysql-test/main/long_unique_update.test b/mysql-test/main/long_unique_update.test
new file mode 100644
index 00000000000..79cca079fe6
--- /dev/null
+++ b/mysql-test/main/long_unique_update.test
@@ -0,0 +1,137 @@
+--echo #structure of tests;
+--echo #1 test of table containing single unique blob column;
+--echo #2 test of table containing another unique int/ varchar etc column; 
+--echo #3 test of table containing multiple unique blob column like unique(a),unique(b);
+--echo #4 test of table containing multiple multiple unique blob column like unique(a,b...),unique(c,d....);
+--echo #structure of each test;
+--echo #test if update works;
+--echo #test update for duplicate entry;
+--echo #test update for no change keys;
+--echo #test update for ignore ;
+
+--echo #test 1
+create table t1 (a blob unique);
+query_vertical show keys from t1;
+insert into t1 values(1),(2),(3),(4),(5);
+select * from t1;
+update t1 set a=11 where a=5;
+update t1 set a=a+20 where a=1;
+select * from t1;
+--error ER_DUP_ENTRY
+update t1 set a=3 where a=2;
+--error ER_DUP_ENTRY
+update t1 set a=4 where a=3;
+--echo #no change in blob key
+update t1 set a=3 where a=3;
+update t1 set a=2 where a=2;
+select* from t1;
+--echo #IGNORE;
+update ignore t1 set a=3 where a=2;
+update ignore t1 set a=4 where a=3;
+select * from t1;
+drop table t1;
+
+--echo #test 2;
+create table t1 (a int primary key, b blob unique , c int unique );
+show keys from t1;
+insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7);
+select * from t1 limit 3;
+update t1 set b=34 where a=1;
+update t1 set b=a+c+b+34 where b=2;
+update t1 set b=a+10+b where c=3;
+select * from t1;
+truncate table t1;
+insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7);
+--error ER_DUP_ENTRY
+update t1 set b=4 where a=3;
+--error ER_DUP_ENTRY
+update t1 set b=a+1 where b=3;
+--error ER_DUP_ENTRY
+update t1 set b=a+1 where c=3;
+--echo #no change in blob key
+update t1 set b=3 where a=3;
+update t1 set b=2 where b=2;
+update t1 set b=5 where c=5;
+select* from t1;
+--echo #IGNORE;
+update ignore t1 set b=3 where a=2;
+update ignore t1 set b=4 where b=3;
+update ignore t1 set b=5 where c=3;
+select * from t1;
+drop table t1;
+
+--echo #test 3;
+create table t1 (a blob unique, b blob unique , c blob unique);
+show keys from t1;
+insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7);
+select * from t1 limit 3;
+update t1 set b=34 where a=1;
+update t1 set b=a+c+b+34 where b=2;
+update t1 set b=a+10+b where c=3;
+select * from t1;
+truncate table t1;
+insert into t1 values(1,1,1),(2,2,2),(3,3,3),(4,4,4),(5,5,5),(6,6,6),(7,7,7);
+--error ER_DUP_ENTRY
+update t1 set b=4 where a=3;
+--error ER_DUP_ENTRY
+update t1 set b=a+1 where b=3;
+--error ER_DUP_ENTRY
+update t1 set b=a+1 where c=3;
+--echo #no change in blob key
+update t1 set b=3 where a=3;
+update t1 set b=2 where b=2;
+update t1 set b=5 where c=5;
+select* from t1;
+--echo #IGNORE;
+update ignore t1 set b=3 where a=2;
+update ignore t1 set b=4 where b=3;
+update ignore t1 set b=5 where c=3;
+update ignore t1 set b=b+3 where a>1 or b>1 or c>1;
+select * from t1;
+update ignore t1 set b=b+5 where a>1 and  b<5 and  c<a+b;
+select * from t1;
+drop table t1;
+
+--echo #test 4 ultimate test;
+create table t1 (a int primary key , b int, c blob , d blob , e  varchar(2000), f int , g text, 
+unique (b,c), unique (b,f),unique(e,g),unique(a,b,c,d,e,f,g));
+desc t1;
+show create table t1;
+show keys from t1;
+insert into t1 values(1,1,1,1,1,1,1),(2,2,2,2,2,2,2),(3,3,3,3,3,3,3),(4,4,4,4,4,4,4),
+(5,5,5,5,5,5,5),(6,6,6,6,6,6,6),(7,7,7,7,7,7,7),(8,8,8,8,8,8,8),(9,9,9,9,9,9,9);
+select * from t1 limit 3;
+--echo #key b_c
+--error ER_DUP_ENTRY
+update t1 set b=2 ,c=2 where a=1;
+update t1 set b=b+34, c=c+34 where e=1 and g=1 ;
+update t1 set b=35, c=35 where e=1 and g=1 ;
+update t1 set b=b+1, c=c+1 where a>0;
+update ignore t1 set b=b+1, c=c+1 where a>0;
+select * from t1 ;
+truncate table t1;
+insert into t1 values(1,1,1,1,1,1,1),(2,2,2,2,2,2,2),(3,3,3,3,3,3,3),(4,4,4,4,4,4,4),
+(5,5,5,5,5,5,5),(6,6,6,6,6,6,6),(7,7,7,7,7,7,7),(8,8,8,8,8,8,8),(9,9,9,9,9,9,9);
+--echo #key b_f no hash key
+--error ER_DUP_ENTRY
+update t1 set b=2 , f=2 where a=1;
+update  t1 set b=b+33, f=f+33 where e=1 and g=1;
+update t1 set b=34, f=34 where e=1 and g=1 ;
+--error ER_DUP_ENTRY
+update t1 set b=b+1, f=f+1 where a>0;
+update ignore t1 set b=b+1, f=f+1 where a>0;
+select * from t1 ;
+truncate table t1;
+insert into t1 values(1,1,1,1,1,1,1),(2,2,2,2,2,2,2),(3,3,3,3,3,3,3),(4,4,4,4,4,4,4),
+(5,5,5,5,5,5,5),(6,6,6,6,6,6,6),(7,7,7,7,7,7,7),(8,8,8,8,8,8,8),(9,9,9,9,9,9,9);
+--echo #key e_g
+--error ER_DUP_ENTRY
+update t1 set e=2 , g=2 where a=1;
+update  t1 set e=e+34, g=g+34 where a=1;
+update t1 set e=34, g=34 where e=1 and g=1 ;
+select * from t1 where  a=1;
+--error ER_DUP_ENTRY
+update t1 set e=e+1, g=g+1 where a>0;
+update ignore t1 set e=e+1, g=g+1 where a>0;
+select * from t1 ;
+drop table t1;