commit a

20 files changed, 3145 insertions, 39 deletions

diff --git a/include/my_base.h b/include/my_base.h
index c36072c0bfa..9a16d4b624b 100644
--- a/include/my_base.h
+++ b/include/my_base.h
@@ -99,7 +99,8 @@ enum ha_key_alg {
   HA_KEY_ALG_BTREE=	1,		/* B-tree, default one          */
   HA_KEY_ALG_RTREE=	2,		/* R-tree, for spatial searches */
   HA_KEY_ALG_HASH=	3,		/* HASH keys (HEAP tables) */
-  HA_KEY_ALG_FULLTEXT=	4		/* FULLTEXT (MyISAM tables) */
+  HA_KEY_ALG_FULLTEXT=	4,		/* FULLTEXT (MyISAM tables) */
+  HA_KEY_ALG_LONG_HASH= 5   /* long BLOB keys */
 };
 
         /* Storage media types */ 
@@ -290,6 +291,11 @@ enum ha_base_keytype {
 #define HA_KEY_HAS_PART_KEY_SEG 65536
 /* Internal Flag Can be calcaluted */
 #define HA_INVISIBLE_KEY 2<<18
+/*
+  Flag for long unique hash key
+  calculated in the init_from_binary_frm_image
+*/
+#define HA_LONG_UNIQUE_HASH   2<<19
 	/* Automatic bits in key-flag */
 
 #define HA_SPACE_PACK_USED	 4	/* Test for if SPACE_PACK used */
diff --git a/include/mysql_com.h b/include/mysql_com.h
index 8b0847ab399..09491ee81c8 100644
--- a/include/mysql_com.h
+++ b/include/mysql_com.h
@@ -203,6 +203,8 @@ enum enum_indicator_type
 #define VERS_UPDATE_UNVERSIONED_FLAG (1 << 29) /* column that doesn't support
                                                 system versioning when table
                                                 itself supports it*/
+#define LONG_UNIQUE_HASH_FIELD       (1<< 30) /* This field will store hash for unique
+                                             column */
 
 #define REFRESH_GRANT           (1ULL << 0)  /* Refresh grant tables */
 #define REFRESH_LOG             (1ULL << 1)  /* Start on new log file */
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;
diff --git a/sql/field.cc b/sql/field.cc
index dd125a06bad..dd0efc75e4b 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -9811,7 +9811,7 @@ int Field_bit::key_cmp(const uchar *str, uint length)
 }
 
 
-int Field_bit::cmp_offset(uint row_offset)
+int Field_bit::cmp_offset(long row_offset)
 {
   if (bit_len)
   {
diff --git a/sql/field.h b/sql/field.h
index e762e45c024..bd7cdd1e60d 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -543,6 +543,7 @@ public:
   LEX_CSTRING name;                             /* Name of constraint */
   /* see VCOL_* (VCOL_FIELD_REF, ...) */
   uint flags;
+  LEX_CSTRING hash_expr;
 
   Virtual_column_info()
   : vcol_type((enum_vcol_info_type)VCOL_TYPE_NONE),
@@ -552,8 +553,14 @@ public:
   {
     name.str= NULL;
     name.length= 0;
+    hash_expr.str= NULL;
+    hash_expr.length= 0;
   };
-  ~Virtual_column_info() {}
+  ~Virtual_column_info()
+  {
+    if (!hash_expr.length)
+      my_free((void *)hash_expr.str);
+  }
   enum_vcol_info_type get_vcol_type() const
   {
     return vcol_type;
@@ -687,7 +694,7 @@ public:
     GEOM_MULTIPOINT = 4, GEOM_MULTILINESTRING = 5, GEOM_MULTIPOLYGON = 6,
     GEOM_GEOMETRYCOLLECTION = 7
   };
-  enum imagetype { itRAW, itMBR};
+  enum imagetype { itRAW, itMBR, itHASH};
 
   utype		unireg_check;
   uint32	field_length;		// Length of field
@@ -1083,7 +1090,7 @@ public:
   virtual int cmp(const uchar *,const uchar *)=0;
   virtual int cmp_binary(const uchar *a,const uchar *b, uint32 max_length=~0U)
   { return memcmp(a,b,pack_length()); }
-  virtual int cmp_offset(uint row_offset)
+  virtual int cmp_offset(long row_offset)
   { return cmp(ptr,ptr+row_offset); }
   virtual int cmp_binary_offset(uint row_offset)
   { return cmp_binary(ptr, ptr+row_offset); };
@@ -4311,7 +4318,7 @@ public:
   int key_cmp(const uchar *a, const uchar *b)
   { return cmp_binary((uchar *) a, (uchar *) b); }
   int key_cmp(const uchar *str, uint length);
-  int cmp_offset(uint row_offset);
+  int cmp_offset(long row_offset);
   bool update_min(Field *min_val, bool force_update)
   { 
     longlong val= val_int();
diff --git a/sql/handler.cc b/sql/handler.cc
index 001055cd475..3dbb21d39c5 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -2710,6 +2710,7 @@ int handler::ha_open(TABLE *table_arg, const char *name, int mode,
               test_if_locked));
 
   table= table_arg;
+  setup_table_hash(table);
   DBUG_ASSERT(table->s == table_share);
   DBUG_ASSERT(m_lock_type == F_UNLCK);
   DBUG_PRINT("info", ("old m_lock_type: %d F_UNLCK %d", m_lock_type, F_UNLCK));
@@ -2726,6 +2727,7 @@ int handler::ha_open(TABLE *table_arg, const char *name, int mode,
       error=open(name,O_RDONLY,test_if_locked);
     }
   }
+  re_setup_table(table);
   if (unlikely(error))
   {
     my_errno= error;                            /* Safeguard */
@@ -4108,6 +4110,8 @@ uint handler::get_dup_key(int error)
               m_lock_type != F_UNLCK);
   DBUG_ENTER("handler::get_dup_key");
   table->file->errkey  = (uint) -1;
+  if (table->dupp_hash_key != -1)
+    DBUG_RETURN(table->dupp_hash_key);
   if (error == HA_ERR_FOUND_DUPP_KEY ||
       error == HA_ERR_FOREIGN_DUPLICATE_KEY ||
       error == HA_ERR_FOUND_DUPP_UNIQUE || error == HA_ERR_NULL_IN_SPATIAL ||
@@ -4627,7 +4631,9 @@ handler::ha_create(const char *name, TABLE *form, HA_CREATE_INFO *info_arg)
 {
   DBUG_ASSERT(m_lock_type == F_UNLCK);
   mark_trx_read_write();
+  setup_table_hash(form);
   int error= create(name, form, info_arg);
+  re_setup_table(form);
   if (!error &&
       !(info_arg->options & (HA_LEX_CREATE_TMP_TABLE | HA_CREATE_TMP_ALTER)))
     mysql_audit_create_table(form);
@@ -6256,6 +6262,185 @@ int handler::ha_reset()
   DBUG_RETURN(reset());
 }
 
+static int check_duplicate_long_entry_key(TABLE *table, handler *h, uchar *new_rec,
+                                   uint key_no)
+{
+  Field *hash_field;
+  int result, error= 0;
+  if (!(table->key_info[key_no].user_defined_key_parts == 1
+        && table->key_info[key_no].key_part->field->flags & LONG_UNIQUE_HASH_FIELD ))
+    return 0;
+  hash_field= table->key_info[key_no].key_part->field;
+  DBUG_ASSERT((table->key_info[key_no].flags & HA_NULL_PART_KEY &&
+      table->key_info[key_no].key_length == HA_HASH_KEY_LENGTH_WITH_NULL)
+    || table->key_info[key_no].key_length == HA_HASH_KEY_LENGTH_WITHOUT_NULL);
+  uchar ptr[HA_HASH_KEY_LENGTH_WITH_NULL];
+
+  if (hash_field->is_real_null())
+    return 0;
+
+  key_copy(ptr, new_rec, &table->key_info[key_no],
+              table->key_info[key_no].key_length, false);
+
+  if (!table->check_unique_buf)
+    table->check_unique_buf= (uchar *)alloc_root(&table->mem_root,
+                                    table->s->reclength*sizeof(uchar));
+
+  result= h->ha_index_init(key_no, 0);
+  if (result)
+    return result;
+  result= h->ha_index_read_map(table->check_unique_buf,
+                               ptr, HA_WHOLE_KEY, HA_READ_KEY_EXACT);
+  if (!result)
+  {
+    bool is_same;
+    do
+    {
+      Item_func_or_sum * temp= static_cast<Item_func_or_sum *>(hash_field->
+                                                               vcol_info->expr);
+      Item_args * t_item= static_cast<Item_args *>(temp);
+      uint arg_count= t_item->argument_count();
+      Item ** arguments= t_item->arguments();
+      long diff= table->check_unique_buf - new_rec;
+      Field * t_field;
+      is_same= true;
+      for (uint j=0; j < arg_count; j++)
+      {
+        DBUG_ASSERT(arguments[j]->type() == Item::FIELD_ITEM ||
+                    // this one for left(fld_name,length)
+                    arguments[j]->type() == Item::FUNC_ITEM);
+        if (arguments[j]->type() == Item::FIELD_ITEM)
+        {
+          t_field= static_cast<Item_field *>(arguments[j])->field;
+          if (t_field->cmp_offset(diff))
+            is_same= false;
+        }
+        else
+        {
+          Item_func_left *fnc= static_cast<Item_func_left *>(arguments[j]);
+          DBUG_ASSERT(!my_strcasecmp(system_charset_info, "left", fnc->func_name()));
+          //item_data= fnc->val_str(&tmp1);
+          DBUG_ASSERT(fnc->arguments()[0]->type() == Item::FIELD_ITEM);
+          t_field= static_cast<Item_field *>(fnc->arguments()[0])->field;
+          //        field_data= t_field->val_str(&tmp2);
+          //        if (my_strnncoll(t_field->charset(),(const uchar *)item_data->ptr(),
+          //                         item_data->length(),
+          //                         (const uchar *)field_data.ptr(),
+          //                         item_data->length()))
+          //          return 0;
+          uint length= fnc->arguments()[1]->val_int();
+          if (t_field->cmp_max(t_field->ptr, t_field->ptr + diff, length))
+            is_same= false;
+        }
+      }
+    }
+    while (!is_same && !(result= table->file->ha_index_next_same(table->check_unique_buf,
+                         ptr, table->key_info[key_no].key_length)));
+    if (is_same)
+    {
+      table->dupp_hash_key= key_no;
+      error= HA_ERR_FOUND_DUPP_KEY;
+      goto exit;
+    }
+    else
+      goto exit;
+  }
+  if (result == HA_ERR_LOCK_WAIT_TIMEOUT)
+  {
+    table->dupp_hash_key= key_no;
+    //TODO check if this is the only case
+    error= HA_ERR_FOUND_DUPP_KEY;
+  }
+  exit:
+  h->ha_index_end();
+  return error;
+}
+/** @brief
+    check whether inserted/updated records breaks the
+    unique constraint on long columns.
+    In the case of update we just need to check the specic key
+    reason for that is consider case
+    create table t1(a blob , b blob , x blob , y blob ,unique(a,b)
+                                                    ,unique(x,y))
+    and update statement like this
+    update t1 set a=23+a; in this case if we try to scan for
+    whole keys in table then index scan on x_y will return 0
+    because data is same so in the case of update we take
+    key as a parameter in normal insert key should be -1
+   @returns 0 if no duplicate else returns error
+  */
+static int check_duplicate_long_entries(TABLE *table, handler *h, uchar *new_rec)
+{
+  table->dupp_hash_key= -1;
+  int result;
+  for (uint i= 0; i < table->s->keys; i++)
+  {
+    if ((result= check_duplicate_long_entry_key(table, h, new_rec, i)))
+      return result;
+  }
+  return 0;
+}
+
+/** @brief
+    check whether updated records breaks the
+    unique constraint on long columns.
+   @returns 0 if no duplicate else returns error
+  */
+static int check_duplicate_long_entries_update(TABLE *table, handler *h, uchar *new_rec)
+{
+  Field **f, *field;
+  Item *h_item;
+  int error= 0;
+  bool is_update_handler_null= false;
+  /*
+     Here we are comparing whether new record and old record are same
+     with respect to fields in hash_str
+   */
+  long reclength= table->record[1]-table->record[0];
+  for (uint i= 0; i < table->s->keys; i++)
+  {
+    if (table->key_info[i].user_defined_key_parts == 1 &&
+          table->key_info[i].key_part->field->flags & LONG_UNIQUE_HASH_FIELD)
+    {
+      /*
+         Currently mysql_update is pacthed so that it will automatically set the
+         Update handler and then free it but ha_update_row is used in many function (
+         like in case of reinsert) Instead of patching them all here we check is
+         update_handler is null then set it  And then set it null again
+       */
+      if (!table->update_handler)
+      {
+        create_update_handler(current_thd, table);
+        is_update_handler_null= true;
+      }
+      h_item= table->key_info[i].key_part->field->vcol_info->expr;
+      for (f= table->field; f && (field= *f); f++)
+      {
+        if ( find_field_pos_in_hash(h_item, field->field_name.str) != -1)
+        {
+          /* Compare fields if they are different then check for duplicates*/
+          if(field->cmp_binary_offset(reclength))
+          {
+            if((error= check_duplicate_long_entry_key(table, table->update_handler,
+                                                   new_rec, i)))
+              goto exit;
+            /*
+              break beacuse check_duplicate_long_entrie_key will
+              take care of remaning fields
+             */
+            break;
+          }
+        }
+      }
+    }
+  }
+  exit:
+  if (is_update_handler_null)
+  {
+    delete_update_handler(current_thd, table);
+  }
+  return error;
+}
 
 int handler::ha_write_row(uchar *buf)
 {
@@ -6266,14 +6451,21 @@ int handler::ha_write_row(uchar *buf)
   DBUG_ENTER("handler::ha_write_row");
   DEBUG_SYNC_C("ha_write_row_start");
 
+  setup_table_hash(table);
   MYSQL_INSERT_ROW_START(table_share->db.str, table_share->table_name.str);
   mark_trx_read_write();
   increment_statistics(&SSV::ha_write_count);
 
+  if ((error= check_duplicate_long_entries(table, table->file, buf)))
+  {
+    re_setup_table(table);
+    DBUG_RETURN(error);
+  }
   TABLE_IO_WAIT(tracker, m_psi, PSI_TABLE_WRITE_ROW, MAX_KEY, 0,
                       { error= write_row(buf); })
 
   MYSQL_INSERT_ROW_DONE(error);
+  re_setup_table(table);
   if (likely(!error) && !row_already_logged)
   {
     rows_changed++;
@@ -6298,9 +6490,15 @@ int handler::ha_update_row(const uchar *old_data, const uchar *new_data)
   DBUG_ASSERT(new_data == table->record[0]);
   DBUG_ASSERT(old_data == table->record[1]);
 
+  setup_table_hash(table);
   MYSQL_UPDATE_ROW_START(table_share->db.str, table_share->table_name.str);
   mark_trx_read_write();
   increment_statistics(&SSV::ha_update_count);
+  if ((error= check_duplicate_long_entries_update(table, table->file, (uchar *)new_data)))
+  {
+    re_setup_table(table);
+    return error;
+  }
 
   TABLE_IO_WAIT(tracker, m_psi, PSI_TABLE_UPDATE_ROW, active_index, 0,
                       { error= update_row(old_data, new_data);})
@@ -6311,6 +6509,7 @@ int handler::ha_update_row(const uchar *old_data, const uchar *new_data)
     rows_changed++;
     error= binlog_log_row(table, old_data, new_data, log_func);
   }
+  re_setup_table(table);
   return error;
 }
 
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 1f949a4445b..24bb3d19cc5 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1719,6 +1719,36 @@ bool Item_func_mod::fix_length_and_dec()
 }
 
 
+longlong  Item_func_hash::val_int()
+{
+  unsigned_flag= true;
+  ulong nr1= 1,nr2= 4;
+  CHARSET_INFO *cs;
+  String * str;
+  for(uint i= 0;i<arg_count;i++)
+  {
+    str = args[i]->val_str();
+    if(args[i]->null_value)
+    {
+      null_value= 1;
+      return 0;
+    }
+   calc_hash_for_unique(nr1, nr2, str);
+  }
+  null_value= 0;
+  return   (longlong)nr1;
+}
+
+
+void  Item_func_hash::fix_length_and_dec()
+{
+  maybe_null= 1;
+  decimals= 0;
+  max_length= 8;
+}
+
+
+
 double Item_func_neg::real_op()
 {
   double value= args[0]->val_real();
diff --git a/sql/item_func.h b/sql/item_func.h
index 1081f2919c8..c80f48f15b1 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1019,6 +1019,19 @@ public:
 };
 
 
+class Item_func_hash: public Item_int_func
+{
+public:
+  Item_func_hash(THD *thd, List<Item> &item): Item_int_func(thd, item)
+  {}
+  longlong val_int();
+  void fix_length_and_dec();
+  const Type_handler *type_handler() const { return &type_handler_long; }
+  Item *get_copy(THD *thd)
+  { return get_item_copy<Item_func_hash>(thd, this); }
+  const char *func_name() const { return "HASH"; }
+};
+
 class Item_longlong_func: public Item_int_func
 {
 public:
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 04baf5737c6..c660af38118 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -17296,6 +17296,9 @@ create_tmp_table(THD *thd, TMP_TABLE_PARAM *param, List<Item> &fields,
   table->intersect_keys.init();
   table->keys_in_use_for_query.init();
   table->no_rows_with_nulls= param->force_not_null_cols;
+  table->update_handler= NULL;
+  table->dupp_hash_key= -1;
+  table->check_unique_buf= NULL;
 
   table->s= share;
   init_tmp_table_share(thd, share, "", 0, tmpname, tmpname);
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 5adee6731b5..885c989e197 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -2306,7 +2306,7 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet,
       */
       packet->append(STRING_WITH_LEN("PRIMARY KEY"));
     }
-    else if (key_info->flags & HA_NOSAME)
+    else if (key_info->flags & HA_NOSAME || key_info->flags & HA_LONG_UNIQUE_HASH)
       packet->append(STRING_WITH_LEN("UNIQUE KEY "));
     else if (key_info->flags & HA_FULLTEXT)
       packet->append(STRING_WITH_LEN("FULLTEXT KEY "));
@@ -6593,8 +6593,13 @@ static int get_schema_stat_record(THD *thd, TABLE_LIST *tables,
             table->field[9]->store((longlong) records, TRUE);
             table->field[9]->set_notnull();
           }
-          const char *tmp= show_table->file->index_type(i);
-          table->field[13]->store(tmp, strlen(tmp), cs);
+          if (key->flags & HA_LONG_UNIQUE_HASH)
+            table->field[13]->store(STRING_WITH_LEN("HASH_INDEX"), cs);
+          else
+          {
+            const char *tmp= show_table->file->index_type(i);
+            table->field[13]->store(tmp, strlen(tmp), cs);
+          }
         }
         if (!(key_info->flags & HA_FULLTEXT) &&
             (key_part->field &&
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 666c6fb325a..0389dc970a3 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3323,6 +3323,87 @@ mysql_add_invisible_index(THD *thd, List<Key> *key_list,
   key_list->push_back(key, thd->mem_root);
   return key;
 }
+/**
+  Add hidden level 3 hash field to table in case of long
+  unique column
+  @param  thd           Thread Context.
+  @param  create_list   List of table fields.
+  @param  cs            Field Charset
+  @param  key_info      Whole Keys buffer
+  @param  key_index     Index of current key
+*/
+
+static void add_hash_field(THD * thd, List<Create_field> *create_list,
+                           CHARSET_INFO *cs, KEY *key_info, int key_index)
+{
+  List_iterator<Create_field> it(*create_list);
+  Create_field *dup_field, *cf= new (thd->mem_root) Create_field();
+  cf->flags|= UNSIGNED_FLAG | LONG_UNIQUE_HASH_FIELD;
+  cf->charset= cs;
+  cf->decimals= 0;
+  cf->length= cf->char_length= cf->pack_length= HA_HASH_FIELD_LENGTH;
+  cf->invisible= INVISIBLE_FULL;
+  cf->pack_flag|= FIELDFLAG_MAYBE_NULL;
+  uint num= 1;
+  char *temp_name= (char *)thd->alloc(30);
+  my_snprintf(temp_name, 30, "DB_ROW_HASH_%u", num);
+  /*
+    Check for collusions
+   */
+  while ((dup_field= it++))
+  {
+    if (!my_strcasecmp(system_charset_info, temp_name, dup_field->field_name.str))
+    {
+      num++;
+      my_snprintf(temp_name, 30, "DB_ROW_HASH_%u", num);
+      it.rewind();
+    }
+  }
+  it.rewind();
+  cf->field_name.str= temp_name;
+  cf->field_name.length= strlen(temp_name);
+  cf->set_handler(&type_handler_longlong);
+  /*
+    We have added db_row_hash field in starting of
+    fields array , So we have to change key_part
+    field index
+  for (int i= 0; i <= key_index; i++, key_info++)
+  {
+    KEY_PART_INFO *info= key_info->key_part;
+    for (uint j= 0; j <  key_info->user_defined_key_parts; j++, info++)
+    {
+      info->fieldnr+= 1;
+      info->offset+= HA_HASH_FIELD_LENGTH;
+    }
+  }*/
+  key_info[key_index].flags|= HA_NOSAME;
+  key_info[key_index].algorithm= HA_KEY_ALG_LONG_HASH;
+  it.rewind();
+  uint record_offset= 0;
+  while ((dup_field= it++))
+  {
+    dup_field->offset= record_offset;
+    if (dup_field->stored_in_db())
+      record_offset+= dup_field->pack_length;
+  }
+  cf->offset= record_offset;
+  /*
+  it.rewind();
+  while ((sql_field= it++))
+  {
+    if (!sql_field->stored_in_db())
+    {
+      sql_field->offset= record_offset;
+      record_offset+= sql_field->pack_length;
+    }
+  }
+   */
+  /* hash column should be fully hidden */
+  //prepare_create_field(cf, NULL, 0);
+  create_list->push_back(cf,thd->mem_root);
+}
+
+
 /*
   Preparation for table creation
 
@@ -3368,6 +3449,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
   uint total_uneven_bit_length= 0;
   int select_field_count= C_CREATE_SELECT(create_table_mode);
   bool tmp_table= create_table_mode == C_ALTER_TABLE;
+  bool is_hash_field_added= false;
   DBUG_ENTER("mysql_prepare_create_table");
 
   DBUG_EXECUTE_IF("test_pseudo_invisible",{
@@ -3685,6 +3767,7 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
     uint key_length=0;
     Key_part_spec *column;
 
+    is_hash_field_added= false;
     if (key->name.str == ignore_key)
     {
       /* ignore redundant keys */
@@ -3852,6 +3935,8 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
       }
 
       cols2.rewind();
+      key_part_info->fieldnr= field;
+      key_part_info->offset=  (uint16) sql_field->offset;
       if (key->type == Key::FULLTEXT)
       {
 	if ((sql_field->real_field_type() != MYSQL_TYPE_STRING &&
@@ -3906,8 +3991,19 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
             column->length= MAX_LEN_GEOM_POINT_FIELD;
 	  if (!column->length)
 	  {
-	    my_error(ER_BLOB_KEY_WITHOUT_LENGTH, MYF(0), column->field_name.str);
-	    DBUG_RETURN(TRUE);
+        if (key->type == Key::PRIMARY)
+        {
+	      my_error(ER_BLOB_KEY_WITHOUT_LENGTH, MYF(0), column->field_name.str);
+	      DBUG_RETURN(TRUE);
+        }
+        else if (!is_hash_field_added)
+        {
+          add_hash_field(thd, &alter_info->create_list,
+                       create_info->default_table_charset,
+                       *key_info_buffer, key_number);
+          column->length= 0;
+          is_hash_field_added= true;
+        }
 	  }
 	}
 #ifdef HAVE_SPATIAL
@@ -3971,8 +4067,6 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 	}
       }
 
-      key_part_info->fieldnr= field;
-      key_part_info->offset=  (uint16) sql_field->offset;
       key_part_info->key_type=sql_field->pack_flag;
       uint key_part_length= sql_field->key_length;
 
@@ -3986,9 +4080,9 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 	  if (key_part_length > max_key_length ||
 	      key_part_length > file->max_key_part_length())
 	  {
-	    key_part_length= MY_MIN(max_key_length, file->max_key_part_length());
 	    if (key->type == Key::MULTIPLE)
 	    {
+	      key_part_length= MY_MIN(max_key_length, file->max_key_part_length());
 	      /* not a critical problem */
 	      push_warning_printf(thd, Sql_condition::WARN_LEVEL_NOTE,
                                   ER_TOO_LONG_KEY,
@@ -3997,11 +4091,13 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
               /* Align key length to multibyte char boundary */
               key_part_length-= key_part_length % sql_field->charset->mbmaxlen;
 	    }
-	    else
-	    {
-	      my_error(ER_TOO_LONG_KEY, MYF(0), key_part_length);
-	      DBUG_RETURN(TRUE);
-	    }
+	    else if (!is_hash_field_added)
+        {
+          add_hash_field(thd, &alter_info->create_list,
+                         create_info->default_table_charset,
+                         *key_info_buffer, key_number);
+          is_hash_field_added= true;
+        }
 	  }
 	}
         // Catch invalid use of partial keys 
@@ -4025,7 +4121,8 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 	else if (!(file->ha_table_flags() & HA_NO_PREFIX_CHAR_KEYS))
 	  key_part_length= column->length;
       }
-      else if (key_part_length == 0 && (sql_field->flags & NOT_NULL_FLAG))
+      else if (key_part_length == 0 && (sql_field->flags & NOT_NULL_FLAG) &&
+              !is_hash_field_added)
       {
 	my_error(ER_WRONG_KEY_COLUMN, MYF(0), file->table_type(),
                  column->field_name.str);
@@ -4034,9 +4131,9 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
       if (key_part_length > file->max_key_part_length() &&
           key->type != Key::FULLTEXT)
       {
-        key_part_length= file->max_key_part_length();
 	if (key->type == Key::MULTIPLE)
 	{
+      key_part_length= file->max_key_part_length();
 	  /* not a critical problem */
 	  push_warning_printf(thd, Sql_condition::WARN_LEVEL_WARN,
                               ER_TOO_LONG_KEY, ER_THD(thd, ER_TOO_LONG_KEY),
@@ -4046,11 +4143,29 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
 	}
 	else
 	{
-	  my_error(ER_TOO_LONG_KEY, MYF(0), key_part_length);
-	  DBUG_RETURN(TRUE);
-	}
+      if(key->type == Key::UNIQUE)
+      {
+        if (!is_hash_field_added)
+        {
+          add_hash_field(thd, &alter_info->create_list,
+                        create_info->default_table_charset,
+                       *key_info_buffer, key_number);
+          is_hash_field_added= true;
+        }
+      }
+      else
+      {
+	    my_error(ER_TOO_LONG_KEY, MYF(0), key_part_length);
+	    DBUG_RETURN(TRUE);
+	  }
+    }
       }
-      key_part_info->length= (uint16) key_part_length;
+      /* We can not store key_part_length more then 2^16 - 1 in frm
+         So we will simply make it zero */
+      if (is_hash_field_added && key_part_length > (2<<16) - 1)
+        key_part_info->length= 0;
+      else
+        key_part_info->length= (uint16) key_part_length;
       /* Use packed keys for long strings on the first column */
       if (!((*db_options) & HA_OPTION_NO_PACK_KEYS) &&
           !((create_info->table_options & HA_OPTION_NO_PACK_KEYS)) &&
@@ -4106,12 +4221,37 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
     if (key->type == Key::UNIQUE && !(key_info->flags & HA_NULL_PART_KEY))
       unique_key=1;
     key_info->key_length=(uint16) key_length;
-    if (key_length > max_key_length && key->type != Key::FULLTEXT)
+    if (key_length > max_key_length && key->type != Key::FULLTEXT &&
+            !is_hash_field_added)
     {
       my_error(ER_TOO_LONG_KEY,MYF(0),max_key_length);
       DBUG_RETURN(TRUE);
     }
 
+    if (is_hash_field_added)
+    {
+      if (key_info->flags & HA_NULL_PART_KEY)
+         null_fields++;
+      else
+      {
+        uint elements= alter_info->create_list.elements;
+        Create_field *hash_fld= static_cast<Create_field *>(alter_info->
+                                                     create_list.elem(elements -1 ));
+        hash_fld->flags|= NOT_NULL_FLAG;
+        hash_fld->pack_flag&= ~FIELDFLAG_MAYBE_NULL;
+       /*
+          Althought we do not need default value anywhere in code , but if we create
+          table with non null long columns , then at the time of insert we get warning.
+          So default value is used so solve this warning.
+        Virtual_column_info *default_value= new (thd->mem_root) Virtual_column_info();
+        char * def_str= (char *)alloc_root(thd->mem_root, 2);
+        strncpy(def_str, "0", 1);
+        default_value->expr_str.str= def_str;
+        default_value->expr_str.length= 1;
+        default_value->expr_item= new (thd->mem_root) Item_int(thd,0);
+        hash_fld->default_value= default_value; */
+      }
+    }
     if (validate_comment_length(thd, &key->key_create_info.comment,
                                 INDEX_COMMENT_MAXLEN,
                                 ER_TOO_LONG_INDEX_COMMENT,
@@ -8319,6 +8459,11 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
       enum Key::Keytype key_type;
       LEX_CSTRING tmp_name;
       bzero((char*) &key_create_info, sizeof(key_create_info));
+      if (key_info->flags & HA_LONG_UNIQUE_HASH)
+      {
+        key_info->flags&= ~(HA_LONG_UNIQUE_HASH);
+        key_info->algorithm= HA_KEY_ALG_UNDEF;
+      }
       key_create_info.algorithm= key_info->algorithm;
       /*
         We copy block size directly as some engines, like Area, sets this
@@ -8358,6 +8503,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
 
       tmp_name.str= key_name;
       tmp_name.length= strlen(key_name);
+      /* We dont need LONG_UNIQUE_HASH_FIELD flag because it will be autogenerated */
       key= new Key(key_type, &tmp_name, &key_create_info,
                    MY_TEST(key_info->flags & HA_GENERATED_KEY),
                    &key_parts, key_info->option_list, DDL_options());
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index d74da408dfc..b5395c1afa9 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -861,6 +861,7 @@ update_begin:
   */
   can_compare_record= records_are_comparable(table);
   explain->tracker.on_scan_init();
+  create_update_handler(thd, table);
 
   THD_STAGE_INFO(thd, stage_updating);
   while (!(error=info.read_record()) && !thd->killed)
@@ -1055,6 +1056,7 @@ update_begin:
       break;
     }
   }
+  delete_update_handler(thd, table);
   ANALYZE_STOP_TRACKING(&explain->command_tracker);
   table->auto_increment_field_not_null= FALSE;
   dup_key_found= 0;
@@ -2092,6 +2094,7 @@ multi_update::initialize_tables(JOIN *join)
 
     if (ignore)
       table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
+    create_update_handler(join->thd, table);
     if (table == main_table)			// First table in join
     {
       if (safe_update_on_fly(thd, join->join_tab, table_ref, all_tables))
@@ -2268,7 +2271,8 @@ multi_update::~multi_update()
   TABLE_LIST *table;
   for (table= update_tables ; table; table= table->next_local)
   {
-    table->table->no_keyread= 0;
+    table->table->no_keyread= table->table->no_cache= 0;
+    delete_update_handler(thd, table->table);
     if (ignore)
       table->table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
   }
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index f628ef098a1..2177ec22351 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -10820,6 +10820,12 @@ function_call_conflict:
             if (unlikely($$ == NULL))
               MYSQL_YYABORT;
           }
+        | HASH_SYM '(' expr_list ')'
+          {
+            $$= new (thd->mem_root)Item_func_hash(thd,*$3);
+            if (unlikely($$ == NULL))
+              MYSQL_YYABORT;
+          }
           /* LAST_VALUE here conflicts with the definition for window functions.
              We have these 2 separate rules to remove the shift/reduce conflict.
           */
diff --git a/sql/table.cc b/sql/table.cc
index 0a40c5fb341..bf13b6aca2d 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -44,6 +44,7 @@
 #include "sql_cte.h"
 #include "ha_sequence.h"
 #include "sql_show.h"
+#include "my_base.h"
 
 /* For MySQL 5.7 virtual fields */
 #define MYSQL57_GENERATED_FIELD 128
@@ -747,7 +748,13 @@ static bool create_key_infos(const uchar *strpos, const uchar *frm_image_end,
     if (i == 0)
     {
       ext_key_parts+= (share->use_ext_keys ? first_keyinfo->user_defined_key_parts*(keys-1) : 0); 
-      n_length=keys * sizeof(KEY) + ext_key_parts * sizeof(KEY_PART_INFO);
+      /*
+        Some keys can be HA_LONG_UNIQUE_HASH , but we do not know at this point ,
+        how many ?, but will always be less than or equal to total num of
+        keys. Each HA_LONG_UNIQUE_HASH key require one extra key_part in which
+        it stored hash. On safe side we will allocate memory for each key.
+       */
+      n_length=keys * sizeof(KEY) + (ext_key_parts +keys) * sizeof(KEY_PART_INFO);
       if (!(keyinfo= (KEY*) alloc_root(&share->mem_root,
 				       n_length + len)))
         return 1;
@@ -798,6 +805,14 @@ static bool create_key_infos(const uchar *strpos, const uchar *frm_image_end,
       }
       key_part->store_length=key_part->length;
     }
+    if (keyinfo->algorithm == HA_KEY_ALG_LONG_HASH)
+    {
+      keyinfo->flags|= HA_LONG_UNIQUE_HASH | HA_NOSAME;
+      keyinfo->key_length= 0;
+      share->ext_key_parts++;
+      // This empty key_part for storing Hash
+      key_part++;
+    }
 
     /*
       Add primary key to end of extended keys for non unique keys for
@@ -1048,6 +1063,7 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table,
   Query_arena backup_arena;
   Virtual_column_info *vcol= 0;
   StringBuffer<MAX_FIELD_WIDTH> expr_str;
+  uint length= 0;
   bool res= 1;
   DBUG_ENTER("parse_vcol_defs");
 
@@ -1143,13 +1159,21 @@ bool parse_vcol_defs(THD *thd, MEM_ROOT *mem_root, TABLE *table,
     pos+= expr_length;
   }
 
-  /* Now, initialize CURRENT_TIMESTAMP fields */
+  /* Now, initialize CURRENT_TIMESTAMP and UNIQUE_INDEX_HASH_FIELD fields */
   for (field_ptr= table->field; *field_ptr; field_ptr++)
   {
     Field *field= *field_ptr;
-    if (field->has_default_now_unireg_check())
+    if (field->vcol_info && (length = field->vcol_info->hash_expr.length))
     {
       expr_str.length(parse_vcol_keyword.length);
+      expr_str.append((char*)field->vcol_info->hash_expr.str, length);
+      vcol= unpack_vcol_info_from_frm(thd, mem_root, table, &expr_str,
+                                    &(field->vcol_info), error_reported);
+      *(vfield_ptr++)= *field_ptr;
+
+    }
+    if (field->has_default_now_unireg_check())
+    {
       expr_str.append(STRING_WITH_LEN("current_timestamp("));
       expr_str.append_ulonglong(field->decimals());
       expr_str.append(')');
@@ -1251,12 +1275,14 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
   uint db_create_options, keys, key_parts, n_length;
   uint com_length, null_bit_pos, UNINIT_VAR(mysql57_vcol_null_bit_pos), bitmap_count;
   uint i;
+  uint field_additional_property_length= 0;
   bool use_hash, mysql57_null_bits= 0;
   char *keynames, *names, *comment_pos;
   const uchar *forminfo, *extra2;
   const uchar *frm_image_end = frm_image + frm_length;
   uchar *record, *null_flags, *null_pos, *UNINIT_VAR(mysql57_vcol_null_pos);
   const uchar *disk_buff, *strpos;
+  const uchar * field_properties=NULL;
   ulong pos, record_offset;
   ulong rec_buff_length;
   handler *handler_file= 0;
@@ -2107,7 +2133,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
       uchar flags= *extra2_field_flags++;
       if (flags & VERS_OPTIMIZED_UPDATE)
         reg_field->flags|= VERS_UPDATE_UNVERSIONED_FLAG;
-
+      if (flags & EXTRA2_LONG_UNIQUE_HASH_FIELD)
+        reg_field->flags|= LONG_UNIQUE_HASH_FIELD;
       reg_field->invisible= f_visibility(flags);
     }
     if (reg_field->invisible == INVISIBLE_USER)
@@ -2178,6 +2205,66 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
   /* Fix key->name and key_part->field */
   if (key_parts)
   {
+    keyinfo= share->key_info;
+    uint hash_field_used_no= share->fields -1 ;
+    KEY_PART_INFO *hash_keypart, *temp_key_part;
+    Field *hash_fld, *temp_fld;
+    for (uint i= 0; i < share->keys; i++, keyinfo++)
+    {
+       /*
+         1. We need set value in hash key_part
+         2. Set vcol_info in corresponding db_row_hash_ field
+       */
+      if (keyinfo->flags & HA_LONG_UNIQUE_HASH)
+      {
+        DBUG_ASSERT(share->field[hash_field_used_no]->flags & LONG_UNIQUE_HASH_FIELD);
+        hash_keypart= keyinfo->key_part + keyinfo->user_defined_key_parts;
+        /* Last n fields are unique_index_hash fields*/
+        hash_keypart->fieldnr= hash_field_used_no + 1;
+        hash_keypart->length= HA_HASH_KEY_LENGTH_WITHOUT_NULL;
+        hash_keypart->store_length= hash_keypart->length;
+        hash_keypart->type= HA_KEYTYPE_ULONGLONG;
+        hash_keypart->key_part_flag= 0;
+        hash_keypart->key_type= 32834;
+        hash_keypart->offset= share->reclength
+                       - HA_HASH_FIELD_LENGTH*(share->fields - hash_field_used_no);
+        hash_fld= share->field[hash_field_used_no];
+        temp_key_part= keyinfo->key_part;
+        Virtual_column_info *v= new (&share->mem_root) Virtual_column_info();
+        String hash_str;
+        hash_str.append(ha_hash_str.str,ha_hash_str.length);
+        hash_str.append(STRING_WITH_LEN("("));
+        for (uint j= 0; j < keyinfo->user_defined_key_parts; j++,
+                 temp_key_part++)
+        {
+          if (j)
+            hash_str.append(STRING_WITH_LEN(" , "));
+          temp_fld= share->field[temp_key_part->fieldnr-1];
+          DBUG_ASSERT(temp_fld);
+          if (!temp_key_part->length ||
+              temp_key_part->length == temp_fld->max_display_length())
+            append_identifier(thd, &hash_str, temp_fld->field_name.str,
+                            strlen(temp_fld->field_name.str));
+          else
+          {
+            hash_str.append(STRING_WITH_LEN(" LEFT("));
+            append_identifier(thd, &hash_str, temp_fld->field_name.str,
+                            strlen(temp_fld->field_name.str));
+            char temp[20];
+            my_snprintf((char *)temp, 20, ", %u )", temp_key_part->length);
+            hash_str.append((char *)temp);
+          }
+        }
+        hash_str.append(STRING_WITH_LEN(")"));
+        char * expr_str= (char *)alloc_root(&share->mem_root, hash_str.length()+1);
+        strncpy(expr_str, hash_str.ptr(), hash_str.length());
+        v->hash_expr.str= expr_str;
+        v->hash_expr.length= hash_str.length();
+        hash_fld->vcol_info= v;
+        share->virtual_fields++;
+        hash_field_used_no--;
+      }
+    }
     uint add_first_key_parts= 0;
     longlong ha_option= handler_file->ha_table_flags();
     keyinfo= share->key_info;
@@ -2284,9 +2371,10 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
           if (field->null_ptr)
             len_null_byte= HA_KEY_NULL_LENGTH;
 
-          if (field->type() == MYSQL_TYPE_BLOB ||
+          if ((field->type() == MYSQL_TYPE_BLOB ||
              field->real_type() == MYSQL_TYPE_VARCHAR ||
-             field->type() == MYSQL_TYPE_GEOMETRY)
+             field->type() == MYSQL_TYPE_GEOMETRY) &&
+             !(keyinfo->flags & HA_LONG_UNIQUE_HASH))
           {
             length_bytes= HA_KEY_BLOB_LENGTH;
           }
@@ -2351,6 +2439,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
       key_part= keyinfo->key_part;
       uint key_parts= share->use_ext_keys ? keyinfo->ext_key_parts :
 	                                    keyinfo->user_defined_key_parts;
+      if (keyinfo->flags & HA_LONG_UNIQUE_HASH)
+        key_parts++;
       for (i=0; i < key_parts; key_part++, i++)
       {
         Field *field;
@@ -2364,7 +2454,13 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
 
         field= key_part->field= share->field[key_part->fieldnr-1];
         key_part->type= field->key_type();
-        if (field->invisible > INVISIBLE_USER && !field->vers_sys_field())
+        if (keyinfo->flags & HA_LONG_UNIQUE_HASH
+            &&(key_part->length > handler_file->max_key_part_length()
+             || key_part->length == 0))
+          key_part->store_length= HA_HASH_KEY_PART_LENGTH;
+        /* Invisible Full is currently used by long uniques */
+        if ((field->invisible ==  INVISIBLE_USER ||
+                field->invisible == INVISIBLE_SYSTEM )&& !field->vers_sys_field())
           keyinfo->flags |= HA_INVISIBLE_KEY;
         if (field->null_ptr)
         {
@@ -2429,7 +2525,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
               field->part_of_sortkey= share->keys_in_use;
           }
         }
-        if (field->key_length() != key_part->length)
+        if (field->key_length() != key_part->length &&
+             !(keyinfo->flags & HA_LONG_UNIQUE_HASH))
         {
 #ifndef TO_BE_DELETED_ON_PRODUCTION
           if (field->type() == MYSQL_TYPE_NEWDECIMAL)
@@ -2471,7 +2568,8 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
         if (!(key_part->key_part_flag & (HA_BLOB_PART | HA_VAR_LENGTH_PART |
                                          HA_BIT_PART)) &&
             key_part->type != HA_KEYTYPE_FLOAT &&
-            key_part->type != HA_KEYTYPE_DOUBLE)
+            key_part->type == HA_KEYTYPE_DOUBLE &&
+            !(keyinfo->flags & HA_LONG_UNIQUE_HASH))
           key_part->key_part_flag|= HA_CAN_MEMCMP;
       }
       keyinfo->usable_key_parts= usable_parts; // Filesort
@@ -3165,7 +3263,8 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
   const char *tmp_alias;
   bool error_reported= FALSE;
   uchar *record, *bitmaps;
-  Field **field_ptr;
+  Field **field_ptr, *field;
+  KEY *key_info;
   uint8 save_context_analysis_only= thd->lex->context_analysis_only;
   TABLE_SHARE::enum_v_keys check_set_initialized= share->check_set_initialized;
   DBUG_ENTER("open_table_from_share");
@@ -3180,6 +3279,7 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
   outparam->s= share;
   outparam->db_stat= db_stat;
   outparam->write_row_record= NULL;
+  outparam->dupp_hash_key= -1;
 
   if (share->incompatible_version &&
       !(ha_open_flags & (HA_OPEN_FOR_ALTER | HA_OPEN_FOR_REPAIR)))
@@ -3322,6 +3422,8 @@ enum open_frm_error open_table_from_share(THD *thd, TABLE_SHARE *share,
 
       key_part_end= key_part + (share->use_ext_keys ? key_info->ext_key_parts :
 			                              key_info->user_defined_key_parts) ;
+      if (key_info->flags & HA_LONG_UNIQUE_HASH)
+        key_part_end++;
       for ( ; key_part < key_part_end; key_part++)
       {
         Field *field= key_part->field= outparam->field[key_part->fieldnr - 1];
@@ -4656,6 +4758,9 @@ void TABLE::init(THD *thd, TABLE_LIST *tl)
   created= TRUE;
   cond_selectivity= 1.0;
   cond_selectivity_sampling_explain= NULL;
+  update_handler= NULL;
+  dupp_hash_key= -1;
+  check_unique_buf= NULL;
 #ifdef HAVE_REPLICATION
   /* used in RBR Triggers */
   master_had_triggers= 0;
@@ -8498,6 +8603,235 @@ double KEY::actual_rec_per_key(uint i)
 }
 
 
+/*
+   find out the field positoin in hash_str()
+   position starts from 0
+   else return -1;
+*/
+int find_field_pos_in_hash(Item *hash_item, const  char * field_name)
+{
+  Item_func_or_sum * temp= static_cast<Item_func_or_sum *>(hash_item);
+  Item_args * t_item= static_cast<Item_args *>(temp);
+  uint arg_count= t_item->argument_count();
+  Item ** arguments= t_item->arguments();
+  Field * t_field;
+
+  for (uint j=0; j < arg_count; j++)
+  {
+    DBUG_ASSERT(arguments[j]->type() == Item::FIELD_ITEM ||
+                arguments[j]->type() == Item::FUNC_ITEM);
+    if (arguments[j]->type() == Item::FIELD_ITEM)
+    {
+      t_field= static_cast<Item_field *>(arguments[j])->field;
+    }
+    else
+    {
+      Item_func_left *fnc= static_cast<Item_func_left *>(arguments[j]);
+      t_field= static_cast<Item_field *>(fnc->arguments()[0])->field;
+    }
+    if (!my_strcasecmp(system_charset_info, t_field->field_name.str, field_name))
+      return j;
+  }
+  return -1;
+}
+
+/*
+   find total number of field in hash_str
+*/
+int fields_in_hash_str(Item * hash_item)
+{
+  Item_func_or_sum * temp= static_cast<Item_func_or_sum *>(hash_item);
+  Item_args * t_item= static_cast<Item_args *>(temp);
+  return t_item->argument_count();
+}
+
+/**
+  @brief clone of current handler.
+  Creates a clone of handler used in update for
+  unique hash key.
+  @param thd            Thread Object
+  @param table          Table Object
+  @return    handler object
+*/
+void create_update_handler(THD *thd, TABLE *table)
+{
+  handler *update_handler= NULL;
+    for (uint i= 0; i < table->s->keys; i++)
+    {
+      if (table->key_info[i].flags & HA_LONG_UNIQUE_HASH)
+      {
+        update_handler= table->file->clone(table->s->normalized_path.str,
+                                           thd->mem_root);
+        update_handler->ha_external_lock(thd, F_RDLCK);
+        table->update_handler= update_handler;
+        return;
+      }
+    }
+    return;
+}
+
+/**
+ @brief Deletes update handler object
+ @param thd   Thread Object
+ @param table Table Object
+*/
+void delete_update_handler(THD *thd, TABLE *table)
+{
+  if (table->update_handler)
+  {
+    table->update_handler->ha_external_lock(thd, F_UNLCK);
+    table->update_handler->ha_close();
+    delete table->update_handler;
+    table->update_handler= NULL;
+  }
+}
+/**
+ @brief This function makes table object with
+        long unique keys ready for storage engine.
+        It makes key_part of HA_LONG_UNIQUE_HASH point to
+        hash key_part.
+ @param table Table object
+ */
+void setup_table_hash(TABLE *table)
+{
+  /*
+     Extra parts of long unique key which are used only at server level
+     for example in key  unique(a, b, c)   //a b c are blob
+     extra_key_part_hash is 3
+   */
+  uint extra_key_part_hash= 0;
+  uint hash_parts= 0;
+  KEY *s_keyinfo= table->s->key_info;
+  KEY *keyinfo= table->key_info;
+  /*
+     Sometime s_keyinfo or key_info can be null. So
+     two different loop for keyinfo and s_keyinfo
+     reference test case:- main.subselect_sj2
+   */
+
+  if (keyinfo)
+  {
+    for (uint i= 0; i < table->s->keys; i++, keyinfo++)
+    {
+      if (keyinfo->flags & HA_LONG_UNIQUE_HASH)
+      {
+        DBUG_ASSERT(keyinfo->user_defined_key_parts ==
+                    keyinfo->ext_key_parts);
+        keyinfo->flags&= ~(HA_NOSAME | HA_LONG_UNIQUE_HASH);
+        keyinfo->algorithm= HA_KEY_ALG_UNDEF;
+        extra_key_part_hash+= keyinfo->ext_key_parts;
+        hash_parts++;
+        keyinfo->key_part= keyinfo->key_part+ keyinfo->ext_key_parts;
+        keyinfo->user_defined_key_parts= keyinfo->usable_key_parts=
+            keyinfo->ext_key_parts= 1;
+        keyinfo->key_length= keyinfo->key_part->store_length;
+      }
+    }
+    table->s->key_parts-= extra_key_part_hash;
+    table->s->key_parts+= hash_parts;
+    table->s->ext_key_parts-= extra_key_part_hash;
+  }
+  if (s_keyinfo)
+  {
+    for (uint i= 0; i < table->s->keys; i++, s_keyinfo++)
+    {
+      if (s_keyinfo->flags & HA_LONG_UNIQUE_HASH)
+      {
+        DBUG_ASSERT(s_keyinfo->user_defined_key_parts ==
+                    s_keyinfo->ext_key_parts);
+        s_keyinfo->flags&= ~(HA_NOSAME | HA_LONG_UNIQUE_HASH);
+        s_keyinfo->algorithm= HA_KEY_ALG_BTREE;
+        extra_key_part_hash+= s_keyinfo->ext_key_parts;
+        s_keyinfo->key_part= s_keyinfo->key_part+ s_keyinfo->ext_key_parts;
+        s_keyinfo->user_defined_key_parts= s_keyinfo->usable_key_parts=
+            s_keyinfo->ext_key_parts= 1;
+        s_keyinfo->key_length= s_keyinfo->key_part->store_length;
+      }
+    }
+    if (!keyinfo)
+    {
+      table->s->key_parts-= extra_key_part_hash;
+      table->s->key_parts+= hash_parts;
+      table->s->ext_key_parts-= extra_key_part_hash;
+    }
+  }
+}
+
+/**
+ @brief Revert the effect of setup_table_hash
+ @param table Table Object
+ */
+void re_setup_table(TABLE *table)
+{
+  //extra key parts excluding hash , which needs to be added in keyparts
+  uint extra_key_parts_ex_hash= 0;
+  uint extra_hash_parts= 0; // this var for share->extra_hash_parts
+  KEY *s_keyinfo= table->s->key_info;
+  KEY *keyinfo= table->key_info;
+  /*
+     Sometime s_keyinfo can be null so
+     two different loop for keyinfo and s_keyinfo
+     ref test case:- main.subselect_sj2
+   */
+  if (keyinfo)
+  {
+    for (uint i= 0; i < table->s->keys; i++, keyinfo++)
+    {
+      if (keyinfo->user_defined_key_parts == 1 &&
+          keyinfo->key_part->field->flags & LONG_UNIQUE_HASH_FIELD)
+      {
+        keyinfo->flags|= (HA_NOSAME | HA_LONG_UNIQUE_HASH);
+        keyinfo->algorithm= HA_KEY_ALG_LONG_HASH;
+        /* Sometimes it can happen, that we does not parsed hash_str.
+           Like when this function is called in ha_create. So we will
+           Use field from  table->field rather then share->field*/
+        Item *h_item= table->field[keyinfo->key_part->fieldnr - 1]->
+                                                   vcol_info->expr;
+        uint hash_parts= fields_in_hash_str(h_item);
+        keyinfo->key_part= keyinfo->key_part- hash_parts;
+        keyinfo->user_defined_key_parts= keyinfo->usable_key_parts=
+            keyinfo->ext_key_parts= hash_parts;
+        extra_key_parts_ex_hash+= hash_parts;
+        extra_hash_parts++;
+        keyinfo->key_length= -1;
+      }
+    }
+    table->s->key_parts-= extra_hash_parts;
+    table->s->key_parts+= extra_key_parts_ex_hash;
+    table->s->ext_key_parts+= extra_key_parts_ex_hash + extra_hash_parts;
+  } 
+  if (s_keyinfo)
+  {
+    for (uint i= 0; i < table->s->keys; i++, s_keyinfo++)
+    {
+      if (s_keyinfo->user_defined_key_parts == 1 &&
+          s_keyinfo->key_part->field->flags & LONG_UNIQUE_HASH_FIELD)
+      {
+        s_keyinfo->flags|= (HA_NOSAME | HA_LONG_UNIQUE_HASH);
+        s_keyinfo->algorithm= HA_KEY_ALG_LONG_HASH;
+        extra_hash_parts++;
+        /* Sometimes it can happen, that we does not parsed hash_str.
+           Like when this function is called in ha_create. So we will
+           Use field from  table->field rather then share->field*/
+        Item *h_item= table->field[s_keyinfo->key_part->fieldnr - 1]->
+                                                   vcol_info->expr;
+        uint hash_parts= fields_in_hash_str(h_item);
+        s_keyinfo->key_part= s_keyinfo->key_part- hash_parts;
+        s_keyinfo->user_defined_key_parts= s_keyinfo->usable_key_parts=
+            s_keyinfo->ext_key_parts= hash_parts;
+        extra_key_parts_ex_hash+= hash_parts;
+        s_keyinfo->key_length= -1;
+      }
+    }
+    if (!keyinfo)
+    {
+      table->s->key_parts-= extra_hash_parts;
+      table->s->key_parts+= extra_key_parts_ex_hash;
+      table->s->ext_key_parts+= extra_key_parts_ex_hash + extra_hash_parts;
+    }
+  }
+}
+
 LEX_CSTRING *fk_option_name(enum_fk_option opt)
 {
   static LEX_CSTRING names[]=
@@ -8967,3 +9301,15 @@ bool TABLE::export_structure(THD *thd, Row_definition_list *defs)
   }
   return false;
 }
+
+void calc_hash_for_unique(ulong &nr1, ulong &nr2, String *str)
+{
+  CHARSET_INFO *cs;
+  uchar l[4];
+  int4store(l, str->length());
+  cs= &my_charset_bin;
+  cs->coll->hash_sort(cs, l, sizeof(l), &nr1, &nr2);
+  cs= str->charset();
+  cs->coll->hash_sort(cs, (uchar *)str->ptr(), str->length(), &nr1, &nr2);
+  sql_print_information("setiya %lu, %s", nr1, str->ptr());
+}
diff --git a/sql/table.h b/sql/table.h
index 1720a212a1f..c8d7decd8e4 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -346,8 +346,32 @@ enum field_visibility_t {
   INVISIBLE_FULL
 };
 
-#define INVISIBLE_MAX_BITS 3
+#define INVISIBLE_MAX_BITS              3
+/* We will store the info into 3rd bit if field is hash field */
+#define HASH_FIELD_MASK                 15
+#define HASH_FIELD_MASK_SHIFT           4
+#define HA_HASH_FIELD_LENGTH            8
+#define HA_HASH_KEY_LENGTH_WITHOUT_NULL 8
+#define HA_HASH_KEY_PART_LENGTH         4 + 8 // 4 for length , 8 for portable size of char ptr
 
+const LEX_CSTRING ha_hash_str           {STRING_WITH_LEN("HASH")};
+
+
+int find_field_pos_in_hash(Item *hash_item, const char * field_name);
+
+int fields_in_hash_str(Item *hash_item);
+
+Field * field_ptr_in_hash_str(Item *hash_item, int index);
+
+void calc_hash_for_unique(ulong &nr1, ulong &nr2, String *str);
+
+void create_update_handler(THD *thd, TABLE *table);
+
+void delete_update_handler(THD *thd, TABLE *table);
+
+void setup_table_hash(TABLE *table);
+
+void re_setup_table(TABLE *table);
 
 /**
   Category of table found in the table share.
@@ -1105,6 +1129,17 @@ public:
   THD	*in_use;                        /* Which thread uses this */
 
   uchar *record[3];			/* Pointer to records */
+  /* record buf to resolve hash collisions for long UNIQUE constraints */
+  uchar *check_unique_buf;
+  handler *update_handler;  /* Handler used in case of update */
+  /*
+     In the case of write row for long unique we are unable of find
+     Whick key is voilated. Because we in case of duplicate hash we never reach
+     handler write_row function. So print_error will always print that
+     key 0 is voilated. We store which key is voilated in this variable
+     by default this should be initialized to -1
+   */
+  int dupp_hash_key;
   uchar *write_row_record;		/* Used as optimisation in
 					   THD::write_row */
   uchar *insert_values;                  /* used by INSERT ... UPDATE */
@@ -2929,6 +2964,7 @@ void append_unescaped(String *res, const char *pos, size_t length);
 void prepare_frm_header(THD *thd, uint reclength, uchar *fileinfo,
                         HA_CREATE_INFO *create_info, uint keys, KEY *key_info);
 const char *fn_frm_ext(const char *name);
+void calc_hash_for_unique(ulong &nr1, ulong &nr2, String *str);
 
 /* Check that the integer is in the internal */
 static inline int set_zone(int nr,int min_zone,int max_zone)
diff --git a/sql/unireg.cc b/sql/unireg.cc
index 4692b2d74d1..44e24554691 100644
--- a/sql/unireg.cc
+++ b/sql/unireg.cc
@@ -101,6 +101,8 @@ static uchar *extra2_write_field_properties(uchar *pos,
     uchar flags= cf->invisible;
     if (cf->flags & VERS_UPDATE_UNVERSIONED_FLAG)
       flags|= VERS_OPTIMIZED_UPDATE;
+    if (cf->flags & LONG_UNIQUE_HASH_FIELD)
+      flags|= EXTRA2_LONG_UNIQUE_HASH_FIELD;
     *pos++= flags;
   }
   return pos;
diff --git a/sql/unireg.h b/sql/unireg.h
index 6f224ab4894..1e360d61e36 100644
--- a/sql/unireg.h
+++ b/sql/unireg.h
@@ -179,7 +179,8 @@ enum extra2_frm_value_type {
 };
 
 enum extra2_field_flags {
-  VERS_OPTIMIZED_UPDATE= 1 << INVISIBLE_MAX_BITS
+  VERS_OPTIMIZED_UPDATE= 1 << INVISIBLE_MAX_BITS,
+  EXTRA2_LONG_UNIQUE_HASH_FIELD= 1 << HASH_FIELD_MASK_SHIFT
 };
 
 int rea_create_table(THD *thd, LEX_CUSTRING *frm,