Hi Sergei!


On 08/24/2016 11:05 PM, Sergei Golubchik wrote:
Hi, Sachin!

On Aug 13, Sachin Setia wrote:
Hello Sergei!
Please review  commit 71f9069 onward i have changed
mysql_prepare_alter_table func.
Okay, here it is. Up to the 03e29c6 (this is one after 71f9069).

Short summary - this all looks pretty good. There're issues, but as a
whole - great work!

diff --git a/include/my_base.h b/include/my_base.h
index 1317639..d03ca0f 100644
--- a/include/my_base.h
+++ b/include/my_base.h
@@ -241,6 +241,19 @@ enum ha_base_keytype {
   HA_KEYTYPE_BIT=19
 };
 
+/* Add some constant related to unique long hash column like length hash string etc*/
+
+#define HA_HASH_KEY_LENGTH_WITHOUT_NULL 8
+#define HA_HASH_FIELD_LENGTH            8
+#define HA_HASH_KEY_LENGTH_WITH_NULL    9
+#define HA_HASH_STR_HEAD                "hash(`" //used in mysql_prepare_create_table
+#define HA_HASH_STR_HEAD_LEN            strlen(HA_HASH_STR_HEAD_LEN)
+#define HA_HASH_STR                     "hash"
+#define HA_HASH_STR_LEN                 strlen(HA_HASH_STR)
+#define HA_HASH_STR_INDEX               "HASH_INDEX"
+#define HA_HASH_STR_INDEX_LEN           strlen(HA_HASH_STR_INDEX)
+#define HA_DB_ROW_HASH_STR              "DB_ROW_HASH_"
no need to put all these constants into the very global my_base.h.
better to define them in sql_show.cc or sql_table.cc (depending on where
they're used)
Actually first three are needed in files like opt_range.cc. I moved this to table.h
+
 #define HA_MAX_KEYTYPE 31              /* Must be log2-1 */
 
 /*
diff --git a/mysql-test/r/features.result b/mysql-test/r/features.result
index 52650d1..e050efb 100644
--- a/mysql-test/r/features.result
+++ b/mysql-test/r/features.result
@@ -7,6 +7,7 @@ Feature_delay_key_write 0
 Feature_dynamic_columns        0
 Feature_fulltext       0
 Feature_gis    0
+Feature_hidden_column  0
Great!
Please, add also a test where it's not 0. I mean, in your hidden_field.test
you can add

  FLUSH STATUS;

at the beginning of the file and SHOW STATUS LIKE 'Feature_hidden_column';
somewhere later. This will show that Feature_hidden_column is incremented
accordingly.

Done.
 Feature_locale 0
 Feature_subquery       0
 Feature_timezone       0
diff --git a/mysql-test/r/hidden_field.result b/mysql-test/r/hidden_field.result
new file mode 100644
index 0000000..09a2c21
--- /dev/null
+++ b/mysql-test/r/hidden_field.result
@@ -0,0 +1,367 @@
+create table h_1(abc int primary key, xyz int hidden);
+desc h_1;
+Field  Type    Null    Key     Default Extra
+abc    int(11) NO      PRI     NULL    
+xyz    int(11) YES             NULL    HIDDEN
+show create table h_1;
+Table  Create Table
+h_1    CREATE TABLE `h_1` (
+  `abc` int(11) NOT NULL,
+  `xyz` int(11) HIDDEN DEFAULT NULL,
+  PRIMARY KEY (`abc`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+drop table h_1;
+create table h_2(a1 int hidden);
+ERROR 42000: A table must have at least 1 column
+create table h_3(a1 blob,hidden(a1));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'hidden(a1))' at line 1
+create table h_4(a1 int primary key hidden ,a2 int unique hidden , a3 blob,a4
+int not null hidden unique);
+ERROR HY000: Hidden column 'a1' either allow null values or it must have default value
+create table h_5(abc int not null hidden);
+ERROR HY000: Hidden column 'abc' either allow null values or it must have default value
+create table t1(a int hidden, b int);
+insert into t1 values(1);
+insert into t1(a) values(1);
+insert into t1(b) values(1);
better insert 1,2,3 not 1,1,1
so that in SELECT you could unambigously see what INSERT has added what row

Changed.
+insert into t1(a,b) values(5,5);
+select * from t1;
+b
+1
+NULL
+1
+5
+select a,b from t1;
+a      b
+NULL   1
+1      NULL
+NULL   1
+5      5
+delete from t1;
+insert into t1 values(1),(2),(3),(4);
+select * from t1;
+b
+1
+2
+3
+4
+select a from t1;
+a
+NULL
+NULL
+NULL
+NULL
+drop table t1;
+create table t1(a int , b int hidden , c int hidden auto_increment unique, d blob , e int unique, f int);
+desc t1;
+Field  Type    Null    Key     Default Extra
+a      int(11) YES             NULL    
+b      int(11) YES             NULL    HIDDEN
+c      int(11) NO      PRI     NULL    auto_increment , HIDDEN
why a space before the comma?
Okay Reverted.
+d      blob    YES             NULL    
+e      int(11) YES     UNI     NULL    
+f      int(11) YES             NULL    
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select * from t1;
+a      d       e       f
+1      d blob  1       1
+1      d blob  11      1
+1      d blob  2       1
+1      d blob  3       1
+1      d blob  41      1
+drop table t1;
+create table sdsdsd(a int , b int, hidden(a,b));
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'hidden(a,b))' at line 1
+create table t1(a int,abc int as (a mod 3) virtual hidden);
+desc t1;
+Field  Type    Null    Key     Default Extra
+a      int(11) YES             NULL    
+abc    int(11) YES             NULL    VIRTUAL , HIDDEN
+insert into t1 values(1,default);
+ERROR 21S01: Column count doesn't match value count at row 1
+insert into t1 values(1),(22),(233);
+select * from t1;
+a
+1
+22
+233
+select a,abc from t1;
+a      abc
+1      1
+22     1
+233    2
+drop table t1;
+create table t1(abc int primary key hidden auto_increment, a int);
+desc t1;
+Field  Type    Null    Key     Default Extra
+abc    int(11) NO      PRI     NULL    auto_increment , HIDDEN
+a      int(11) YES             NULL    
+show create table t1;
+Table  Create Table
+t1     CREATE TABLE `t1` (
+  `abc` int(11) NOT NULL HIDDEN AUTO_INCREMENT,
+  `a` int(11) DEFAULT NULL,
+  PRIMARY KEY (`abc`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values(1);
+insert into t1 values(2);
+insert into t1 values(3);
+select * from t1;
+a
+1
+2
+3
+select abc,a from t1;
+abc    a
+1      1
+2      2
+3      3
+delete  from t1;
+insert into t1 values(1),(2),(3),(4),(6);
+select abc,a from t1;
+abc    a
+4      1
+5      2
+6      3
+7      4
+8      6
+drop table t1;
+create table t1(abc int);
+alter table t1 change abc ss int hidden;
+ERROR 42000: A table must have at least 1 column
+alter table t1 add column xyz int;
+alter table t1 modify column abc  int ;
+desc t1;
+Field  Type    Null    Key     Default Extra
+abc    int(11) YES             NULL    
+xyz    int(11) YES             NULL    
+insert into t1 values(22);
+ERROR 21S01: Column count doesn't match value count at row 1
+alter table t1 modify column abc  int hidden;
please, add also tests for making hidden fields visible again.
I think that

  alter table t1 modify column abc int;

will do the job
Okay, Added.
+desc t1;
+Field  Type    Null    Key     Default Extra
+abc    int(11) YES             NULL    HIDDEN
+xyz    int(11) YES             NULL    
+insert into t1 values(12);
+drop table t1;
+some test on copy table structure with table data;
+table with hidden fields and unique keys;
+create table t1(a int , b int hidden , c int hidden auto_increment unique, d blob , e int unique, f int);
+desc t1;
+Field  Type    Null    Key     Default Extra
+a      int(11) YES             NULL    
+b      int(11) YES             NULL    HIDDEN
+c      int(11) NO      PRI     NULL    auto_increment , HIDDEN
+d      blob    YES             NULL    
+e      int(11) YES     UNI     NULL    
+f      int(11) YES             NULL    
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select * from t1;
+a      d       e       f
+1      d blob  1       1
+1      d blob  11      1
+1      d blob  2       1
+1      d blob  3       1
+1      d blob  41      1
+select a,b,c,d,e,f from t1;
+a      b       c       d       e       f
+1      NULL    1       d blob  1       1
+1      NULL    2       d blob  11      1
+1      NULL    3       d blob  2       1
+1      NULL    4       d blob  3       1
+1      NULL    5       d blob  41      1
+this wont copy hidden fields and keys;
+create table t2 as select * from t1;
+desc t2;
+Field  Type    Null    Key     Default Extra
+a      int(11) YES             NULL    
+d      blob    YES             NULL    
+e      int(11) YES             NULL    
+f      int(11) YES             NULL    
+select * from t2;
+a      d       e       f
+1      d blob  1       1
+1      d blob  11      1
+1      d blob  2       1
+1      d blob  3       1
+1      d blob  41      1
+select a,b,c,d,e,f from t2;
+ERROR 42S22: Unknown column 'b' in 'field list'
+drop table t2;
+now this will copy hidden fields 
+create table t2 as select a,b,c,d,e,f from t1;
+desc t2;
+Field  Type    Null    Key     Default Extra
+a      int(11) YES             NULL    
+b      int(11) YES             NULL    HIDDEN
+c      int(11) NO              0       HIDDEN
+d      blob    YES             NULL    
+e      int(11) YES             NULL    
+f      int(11) YES             NULL    
+select * from t2;
+a      d       e       f
+1      d blob  1       1
+1      d blob  11      1
+1      d blob  2       1
+1      d blob  3       1
+1      d blob  41      1
+select a,b,c,d,e,f from t2;
+a      b       c       d       e       f
+1      NULL    1       d blob  1       1
+1      NULL    2       d blob  11      1
+1      NULL    3       d blob  2       1
+1      NULL    4       d blob  3       1
+1      NULL    5       d blob  41      1
+drop table t2,t1;
+some test related to copy of data from one table to another;
+create table t1(a int , b int hidden , c int hidden auto_increment unique, d blob , e int unique, f int);
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select a,b,c,d,e,f from t1;
+a      b       c       d       e       f
+1      NULL    1       d blob  1       1
+1      NULL    2       d blob  11      1
+1      NULL    3       d blob  2       1
+1      NULL    4       d blob  3       1
+1      NULL    5       d blob  41      1
+create table t2(a int , b int hidden , c int hidden , d blob , e int unique, f int);
+insert into t2 select * from t1;
+select a,b,c,d,e,f from t2;
+a      b       c       d       e       f
+1      NULL    NULL    d blob  1       1
+1      NULL    NULL    d blob  11      1
+1      NULL    NULL    d blob  2       1
+1      NULL    NULL    d blob  3       1
+1      NULL    NULL    d blob  41      1
+truncate t2;
+insert into t2 (a,b,c,d,e,f) select a,b,c,d,e,f from t1;
+select a,b,c,d,e,f from t2;
+a      b       c       d       e       f
+1      NULL    1       d blob  1       1
+1      NULL    2       d blob  11      1
+1      NULL    3       d blob  2       1
+1      NULL    4       d blob  3       1
+1      NULL    5       d blob  41      1
+truncate t2;
+drop table t1,t2;
+some test related to creating view on table with hidden column;
+create table t1(a int , b int hidden , c int hidden auto_increment unique, d blob , e int unique, f int);
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+create view v as select * from t1;
+desc v;
+Field  Type    Null    Key     Default Extra
+a      int(11) YES             NULL    
+d      blob    YES             NULL    
+e      int(11) YES             NULL    
+f      int(11) YES             NULL    
+select * from v;
+a      d       e       f
+1      d blob  1       1
+1      d blob  11      1
+1      d blob  2       1
+1      d blob  3       1
+1      d blob  41      1
+v does not have hidden column;
+select a,b,c,d,e,f from v;
+ERROR 42S22: Unknown column 'b' in 'field list'
+insert into v values(1,21,32,4);
+select * from v;
+a      d       e       f
+1      d blob  1       1
+1      d blob  11      1
+1      d blob  2       1
+1      d blob  3       1
+1      d blob  41      1
+1      21      32      4
+insert into v(a,b,c,d,e,f) values(1,12,3,4,5,6);
+ERROR 42S22: Unknown column 'b' in 'field list'
+drop view v;
+create view v as select a,b,c,d,e,f from t1;
+desc v;
+Field  Type    Null    Key     Default Extra
+a      int(11) YES             NULL    
+b      int(11) YES             NULL    HIDDEN
+c      int(11) NO              0       HIDDEN
+d      blob    YES             NULL    
+e      int(11) YES             NULL    
+f      int(11) YES             NULL    
+select * from v;
+a      b       c       d       e       f
+1      NULL    1       d blob  1       1
+1      NULL    2       d blob  11      1
+1      NULL    3       d blob  2       1
+1      NULL    4       d blob  3       1
+1      NULL    5       d blob  41      1
+1      NULL    6       21      32      4
+v does  have hidden column;
+select a,b,c,d,e,f from v;
+a      b       c       d       e       f
+1      NULL    1       d blob  1       1
+1      NULL    2       d blob  11      1
+1      NULL    3       d blob  2       1
+1      NULL    4       d blob  3       1
+1      NULL    5       d blob  41      1
+1      NULL    6       21      32      4
+insert into v values(1,26,33,4,45,66);
+select a,b,c,d,e,f from v;
+a      b       c       d       e       f
+1      NULL    1       d blob  1       1
+1      NULL    2       d blob  11      1
+1      NULL    3       d blob  2       1
+1      NULL    4       d blob  3       1
+1      NULL    5       d blob  41      1
+1      NULL    6       21      32      4
+1      26      33      4       45      66
+insert into v(a,b,c,d,e,f) values(1,32,31,41,5,6);
+select a,b,c,d,e,f from v;
+a      b       c       d       e       f
+1      NULL    1       d blob  1       1
+1      NULL    2       d blob  11      1
+1      NULL    3       d blob  2       1
+1      NULL    4       d blob  3       1
+1      NULL    5       d blob  41      1
+1      NULL    6       21      32      4
+1      26      33      4       45      66
+1      32      31      41      5       6
+drop view v;
+drop table t1;
+now hidden column in where and some join query i think no use of this test but anyway;
+create table t1 (a int unique , b int hidden unique, c int unique  hidden);
+insert into t1(a,b,c) values(1,1,1);
+insert into t1(a,b,c) values(2,2,2);
+insert into t1(a,b,c) values(3,3,3);
+insert into t1(a,b,c) values(4,4,4);
+insert into t1(a,b,c) values(21,21,26);
+insert into t1(a,b,c) values(31,31,35);
+insert into t1(a,b,c) values(41,41,45);
+insert into t1(a,b,c) values(22,22,24);
+insert into t1(a,b,c) values(32,32,33);
+insert into t1(a,b,c) values(42,42,43);
+explain select * from t1 where b=3;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   b       b       5       const   1       
+select * from t1 where b=3;
+a
+3
+explain select * from t1 where c=3;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   c       c       5       const   1       
+select * from t1 where c=3;
+a
+3
+create table t2 as select a,b,c from t1;
+desc t2;
+Field  Type    Null    Key     Default Extra
+a      int(11) YES             NULL    
+b      int(11) YES             NULL    HIDDEN
+c      int(11) YES             NULL    HIDDEN
+explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t2      ALL     NULL    NULL    NULL    NULL    10      
+1      SIMPLE  t1      ALL     b,c     NULL    NULL    NULL    10      Using where; Using join buffer (flat, BNL join)
+select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+a      a
+1      1
+2      2
+3      3
+4      4
+drop table t1,t2;
diff --git a/mysql-test/r/long_unique_where.result b/mysql-test/r/long_unique_where.result
new file mode 100644
index 0000000..e9bd416
--- /dev/null
+++ b/mysql-test/r/long_unique_where.result
@@ -0,0 +1,340 @@
+create table t1(abc blob unique);
+insert into t1 values(1),(2),(3),(4),(5),(11),(12),(31),(14),(15),(NULL)
+,(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
+(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),
+(NULL),(NULL),(NULL),(NULL),(NULL);
+insert into t1 value(1);
+ERROR 23000: Duplicate entry '1' for key 'abc'
+explain select * from t1 where abc=31;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   abc     abc     9       const   1       
+select * from t1 where abc=31;
+abc
+31
+# in case of null we do not use any optimization
+explain select * from t1 where abc is  NULL;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    36      Using where
+select * from t1 where abc is  NULL limit 1;
+abc
+NULL
+#range query
please clarify it here, for example, like

 #range query (index not used, because it's a hash)

Copied , :).
+explain select * from t1 where abc >1  limit 1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    36      Using where
+select * from t1 where abc >1 limit 1;
+abc
+2
+explain select * from t1 where abc >1 and abc <4;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    36      Using where
+select * from t1 where abc >1 and abc <4;
+abc
+2
+3
+explain select * from t1 where abc =15 or abc <4 ;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    36      Using where
+select * from t1 where abc =15 or abc <4 ;
+abc
+1
+2
+3
+15
+drop table t1;
+create table t1(abc blob unique, xyz int );
+insert into t1 values(1,1),(2,1),(3,3),(4,1),(5,6),(NULL,3),(NULL,1),
+(NULL,NULL),(11,11),(12,11),(31,31),
+(14,1),(15,61),(NULL,32),(NULL,12),(NULL,NULL);
+insert into t1 value(1,NULL);
+ERROR 23000: Duplicate entry '1' for key 'abc'
+explain select * from t1 where abc=15;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   abc     abc     9       const   1       
+select * from t1 where abc= 15;
+abc    xyz
+15     61
+explain select * from t1 where abc=15 and xyz =61;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   abc     abc     9       const   1       
+select * from t1 where abc= 15 and 61;
type in the where clause
Changed.
+abc    xyz
+15     61
+# now xyz has different value
what do you mean by that?
echo # now xyz has a value which is not present in table;
Added.
+explain select * from t1 where abc=1000;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+select * from t1 where abc= 1000;
+abc    xyz
+explain select * from t1 where abc=14 and xyz =56;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+select * from t1 where abc=14 and xyz =56;
+abc    xyz
+#range query
+explain select * from t1 where abc >5 limit 1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    16      Using where
+select * from t1 where abc >5 limit 1;
+abc    xyz
+11     11
+explain select * from t1 where abc=5 and xyz <56;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   abc     abc     9       const   1       
+select * from t1 where abc=5 and xyz <56;
+abc    xyz
+5      6
+explain select * from t1 where abc>=5 and xyz <56;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    16      Using where
+select * from t1 where abc>=5 and xyz <56;
+abc    xyz
+5      6
+11     11
+12     11
+31     31
+14     1
+explain select * from t1 where abc>5 and xyz =56;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    16      Using where
+select * from t1 where abc>5 and xyz =56;
+abc    xyz
+drop table t1;
+create table t1(abc blob unique, xyz blob unique );
+insert into t1 values(1,1),(2,11),(3,31),(4,12),(5,63),(NULL,2),(NULL,NULL),
+(91,19),(92,119),(93,391),(94,192),(95,693);
+insert into t1 value(1,NULL);
+ERROR 23000: Duplicate entry '1' for key 'abc'
+explain select * from t1 where abc=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   abc     abc     9       const   1       
+select * from t1 where abc=1;
+abc    xyz
+1      1
+explain select * from t1 where xyz=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   xyz     xyz     9       const   1       
+select * from t1 where xyz=1;
+abc    xyz
+1      1
+explain select * from t1 where abc=5 and xyz=63;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   abc     abc     9       const   1       
+select * from t1 where abc=5 and xyz=63;
+abc    xyz
+5      63
+explain select * from t1 where xyz=63 and abc=5;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   abc     abc     9       const   1       
+select * from t1 where xyz=63 and abc=5;
+abc    xyz
+5      63
+explain select * from t1 where xyz=63 or abc=5;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    12      Using where
+select * from t1 where xyz=63 or abc=5;
+abc    xyz
+5      63
+# now the first one does not exist 
+explain select * from t1 where abc=51 and xyz=63;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+select * from t1 where abc=51 and xyz=63;
+abc    xyz
+# now the second one does not exist 
+explain select * from t1 where abc=5 and xyz=613;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+select * from t1 where abc=5 and xyz=613;
+abc    xyz
+explain select * from t1 where abc is NULL;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    12      Using where
+select * from t1 where abc is NULL;
+abc    xyz
+NULL   2
+NULL   NULL
+explain select * from t1 where abc is NULL and xyz=2;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   xyz     xyz     9       const   1       
+select * from t1 where abc is NULL and xyz=2;
+abc    xyz
+NULL   2
+#range conditions
+explain select * from t1 where abc>11 limit 5;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    12      Using where
+select * from t1 where abc>11 limit 5;
+abc    xyz
+91     19
+92     119
+93     391
+94     192
+95     693
+explain select * from t1 where xyz<11;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    12      Using where
+select * from t1 where xyz<11;
+abc    xyz
+1      1
+NULL   2
+explain select * from t1 where abc>=51 and xyz<=63;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    12      Using where
+select * from t1 where abc>=51 and xyz<=63;
+abc    xyz
+91     19
+explain select * from t1 where abc>5 and xyz<613;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    12      Using where
+select * from t1 where abc>5 and xyz<613;
+abc    xyz
+91     19
+92     119
+93     391
+94     192
+explain select * from t1 where abc=5 and xyz>1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   abc     abc     9       const   1       
+select * from t1 where abc=5 and xyz>1;
+abc    xyz
+5      63
+explain select * from t1 where abc<55 and xyz=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   xyz     xyz     9       const   1       
+select * from t1 where abc<55 and xyz=1;
+abc    xyz
+1      1
+explain select * from t1 where abc<=55 or xyz>=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    12      Using where
+select * from t1 where abc<=55 and xyz>=1;
+abc    xyz
+1      1
+2      11
+3      31
+4      12
+5      63
+drop table t1;
+#now the unique (a,b,c..........)
+#let us do more hard test by using unique(a,b,c,d) key first 
+create table t1 (a blob , b blob , c blob , d blob ,unique(a,b,c,d),
+unique(a,b,c) ,unique (b,c,d));
hmm, better do not use redundant keys. it works now, but tenchnically
we can implement the optimization where the server will internally
remove "unique(a,b,c,d)" - will store it in the frm, but won't tell
the engine about it - because unique(a,b,c) guarantees that (a,b,c,d)
is *always* unique.

so, if we add this optimization someday your test case will no longer test
what it was supposed to.
Okay , Changed.
+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,
+  UNIQUE KEY `a_b_c_d`(`a`,`b`,`c`,`d`),
+  UNIQUE KEY `a_b_c`(`a`,`b`,`c`),
+  UNIQUE KEY `b_c_d`(`b`,`c`,`d`)
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values(1,1,1,1),(2,1,2,1),(1,3,3,1),(4,1,4,1),(1,5,1,5),
+(6,1,6,1),(1,7,1,7),(8,1,1,8),(1,9,9,1),(2,2,2,2),(4,4,4,4),(5,5,5,5),(6,6,6,6),
+(126,216,603,640),(603,460,660,706),(806,609,609,605),(62,62,22,33),(64,65,66,76),
+(16,26,63,64),(63,46,66,76),(86,69,69,65),(622,622,622,633),(644,655,666,776);
+select * from t1 limit 3;
+a      b       c       d
+1      1       1       1
+2      1       2       1
+1      3       3       1
+#simple where
+#key (a,b,c)
+explain  select * from t1 where a=1 and b=1 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   a_b_c   a_b_c   9       const   1       
+select * from t1 where a=1 and b=1 and c=1;
+a      b       c       d
+1      1       1       1
+explain  select * from t1 where a=1 and b=4444 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+select * from t1 where a=1 and b=4444 and c=1;
+a      b       c       d
+#incomplete key
+explain  select * from t1 where a=1 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      Using where
+select * from t1 where a=1 and c=1;
+a      b       c       d
+1      1       1       1
+1      5       1       5
+1      7       1       7
+explain  select * from t1 where a=1 and b=4444 ;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      Using where
+select * from t1 where a=1 and b=4444 ;
+a      b       c       d
+explain  select * from t1 where b=1 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      Using where
+select * from t1 where b=1 and c=1;
+a      b       c       d
+1      1       1       1
+8      1       1       8
+#key (b,c,d)
+explain  select * from t1 where d=1 and b=1 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   b_c_d   b_c_d   9       const   1       
+select * from t1 where d=1 and b=1 and c=1;
+a      b       c       d
+1      1       1       1
+explain  select * from t1 where d=1 and b=4444 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+select * from t1 where d=1 and b=4444 and c=1;
+a      b       c       d
+#incomplete key
+explain  select * from t1 where d=1 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      Using where
+select * from t1 where d=1 and c=1;
+a      b       c       d
+1      1       1       1
+explain  select * from t1 where d=1 and b=4444 ;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      Using where
+select * from t1 where d=1 and b=4444 ;
+a      b       c       d
+explain  select * from t1 where b=1 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      Using where
+select * from t1 where b=1 and c=1;
+a      b       c       d
+1      1       1       1
+8      1       1       8
+#key (a,b,c,d)
+explain  select * from t1 where a=1 and d=1 and b=1 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      const   a_b_c_d a_b_c_d 9       const   1       
+select * from t1 where a=1 and d=1 and b=1 and c=1;
+a      b       c       d
+1      1       1       1
+explain  select * from t1 where a=1 and d=1 and b=4444 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  NULL    NULL    NULL    NULL    NULL    NULL    NULL    Impossible WHERE noticed after reading const tables
+select * from t1 where a=1 and d=1 and b=4444 and c=1;
+a      b       c       d
+#incomplete key
+explain  select * from t1 where a=1 and d=1 and c=1 ;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      Using where
+select * from t1 where a=1 and d=1 and c=1 ;
+a      b       c       d
+1      1       1       1
+explain  select * from t1 where d=1 and b=4444 and a=1 ;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      Using where
+select * from t1 where d=1 and b=4444 and a=1 ;
+a      b       c       d
+explain  select * from t1 where a=1 and c=1;
+id     select_type     table   type    possible_keys   key     key_len ref     rows    Extra
+1      SIMPLE  t1      ALL     NULL    NULL    NULL    NULL    23      Using where
+select * from t1 where a=1 and c=1;
+a      b       c       d
+1      1       1       1
+1      5       1       5
+1      7       1       7
+drop table t1;
diff --git a/mysql-test/r/long_uniques.result b/mysql-test/r/long_uniques.result
new file mode 100644
index 0000000..e410fc1
--- /dev/null
+++ b/mysql-test/r/long_uniques.result
@@ -0,0 +1,1163 @@
+#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 
+#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;
for these statements, use "query_vertical" prefix, like

 query_vertical show keys from t1;

it works better when the result has only few (best: one) row
Added.
+Table  Non_unique      Key_name        Seq_in_index    Column_name     Collation       Cardinality     Sub_part        Packed  Null    Index_type      Comment Index_comment
+t1     1       a       1       a       A       0       NULL    NULL    YES     HASH_INDEX              
here, notice that your index is shown as "non-unique"
Corrected.
+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
+def    test    t1      a       1       NULL    YES     blob    65535   65535   NULL    NULL    NULL    NULL    NULL    blob    UNI             select,insert,update,references 
+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      1       test    a       1       a       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
+# table select we should not be able to see db_row_hash_column;
"not able to see db_row_hash_1 column"

Changed, Sorry.
+select * from t1;
+a
+1
+2
+3
+56
+sachin
+maria
+123456789034567891
+NULL
+NULL
+123456789034567890
+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 'sachi' for key 'a'
+insert into t1 values(123456789034567891);
+ERROR 23000: Duplicate entry '12345' for key 'a'
+select * from t1;
+a
+1
+2
+3
+56
+sachin
+maria
+123456789034567891
+NULL
+NULL
+123456789034567890
+insert into t1 values(11),(22),(33);
+insert into t1 values(12),(22);
+ERROR 23000: Duplicate entry '22' for key 'a'
+select * from t1;
+a
+1
+2
+3
+56
+sachin
+maria
+123456789034567891
+NULL
+NULL
+123456789034567890
+11
+22
+33
+12
+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 'mmmmm' 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);
+#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`)
+) 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`)
+) 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 `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `a`(`a`)
+) 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'
+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 column name;
+#this will change index to b tree;
+alter table t1 modify  column a  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,
+  `a` int(11) DEFAULT NULL,
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  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       db_row_hash_1   1       db_row_hash_1   A       NULL    NULL    NULL    YES     BTREE           
+t1     0       a       1       a       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,
+  `a` int(11) DEFAULT NULL,
+  `clm_changed` blob DEFAULT NULL,
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `a` (`a`),
+  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       a       A       NULL    NULL    NULL    YES     BTREE           
+t1     1       clm     1       clm_changed     A       0       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,
+  `a` int(11) DEFAULT NULL,
+  `clm_changed` blob DEFAULT NULL,
+  UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`),
+  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       db_row_hash_1   1       db_row_hash_1   A       NULL    NULL    NULL    YES     BTREE           
+t1     0       a       1       a       A       NULL    NULL    NULL    YES     BTREE           
you can use myisamchk to see *actually created* indexes in MYI file.
examples are in myisam.test, but in short, it's something like

 let datadir=`select @@datadir`;
 replace_result $datadir DATADIR;
 exec $MYISAMCHK -d $datadir/test/t1

the first command needs to be done only once, in the beginning of the file,
usually. The other pair you do whenever you want to examine the table,
like, where you do "desc t1" or "show keys".
Done.
+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     1       a       1       a       A       0       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 ('Ä');
good!

+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','ff','fdf','gfgfgfg','hghgr'),('maria','db','frter','dasd','utyuty'),
+(123456789034567891,3534534534534,53453453453456,64565464564564,45435345345345),
+(123456789034567890,435345345345,657567567567,78967657567567,657567567567567676);
+Warnings:
+Warning        1366    Incorrect integer value: 'ff' for column 'b' at row 5
+Warning        1366    Incorrect integer value: 'db' for column 'b' at row 6
+Warning        1264    Out of range value for column 'b' at row 7
+Warning        1264    Out of range value for column 'b' at row 8
did you really need to insert invalid values in this test?
No, I did not see Warning.
+#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     1       a       1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c       1       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e       1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+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
+def    test    t1      a       1       NULL    YES     blob    65535   65535   NULL    NULL    NULL    NULL    NULL    blob    UNI             select,insert,update,references 
+def    test    t1      b       2       NULL    YES     int     NULL    NULL    10      0       NULL    NULL    NULL    int(11)                 select,insert,update,references 
+def    test    t1      c       3       NULL    YES     blob    65535   65535   NULL    NULL    NULL    NULL    NULL    blob    UNI             select,insert,update,references 
+def    test    t1      d       4       NULL    YES     text    65535   65535   NULL    NULL    NULL    latin1  latin1_swedish_ci       text    UNI             select,insert,update,references 
+def    test    t1      e       5       NULL    YES     varchar 3000    3000    NULL    NULL    NULL    latin1  latin1_swedish_ci       varchar(3000)   UNI             select,insert,update,references 
+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      1       test    a       1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    c       1       c       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    d       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    e       1       e       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    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_column;
+select * from t1;
+a      b       c       d       e
+1      2       3       4       5
+2      11      22      33      44
+3111   222     333     444     555
+5611   2222    3333    4444    5555
+sachin 0       fdf     gfgfgfg hghgr
+maria  0       frter   dasd    utyuty
+123456789034567891     2147483647      53453453453456  64565464564564  45435345345345
+123456789034567890     2147483647      657567567567    78967657567567  657567567567567676
+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 'sachi' for key 'a'
+insert into t1 values(1234567890345671890,4353453453451,6575675675617,789676575675617,657567567567567676);
+ERROR 23000: Duplicate entry '65756' for key 'e'
+select * from t1;
+a      b       c       d       e
+1      2       3       4       5
+2      11      22      33      44
+3111   222     333     444     555
+5611   2222    3333    4444    5555
+sachin 0       fdf     gfgfgfg hghgr
+maria  0       frter   dasd    utyuty
+123456789034567891     2147483647      53453453453456  64565464564564  45435345345345
+123456789034567890     2147483647      657567567567    78967657567567  657567567567567676
+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',401));
+insert into t1 values(repeat('m',4000*11),10,repeat('s',4000*11),repeat('s',4000*11),repeat('s',400));
+ERROR 23000: Duplicate entry 'sssss' for key 'e'
400 characters (that you insert into 'e') - that looks a bit too short for
varchar(3000). Why wouldn't you insert, say, 2990 characters?
400 is something that even b-tree can handle, I suspect.
Changed.
+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`),
+  UNIQUE KEY `c`(`c`),
+  UNIQUE KEY `d`(`d`),
+  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`),
+  UNIQUE KEY `c`(`c`),
+  UNIQUE KEY `d`(`d`),
+  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 `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`),
+  UNIQUE KEY `a`(`a`),
+  UNIQUE KEY `c`(`c`),
+  UNIQUE KEY `d`(`d`),
+  UNIQUE KEY `e`(`e`)
+) 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 `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+  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       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     1       d       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e       1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+#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 `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+  UNIQUE KEY `d`(`d`),
+  UNIQUE KEY `e`(`e`),
+  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       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     1       d       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e       1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a       1       a       A       0       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 `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+  UNIQUE KEY `e` (`e`),
+  UNIQUE KEY `a` (`a`),
+  UNIQUE KEY `d`(`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       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       e       1       e       A       NULL    NULL    NULL    YES     BTREE           
+t1     0       a       1       a       A       NULL    NULL    NULL    YES     BTREE           
+t1     1       d       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+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 `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+  UNIQUE KEY `e` (`e`),
+  UNIQUE KEY `a` (`a`),
+  UNIQUE KEY `d`(`d`),
+  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       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       e       1       e       A       NULL    NULL    NULL    YES     BTREE           
+t1     0       a       1       a       A       NULL    NULL    NULL    YES     BTREE           
+t1     1       d       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       clm1    1       clm_changed1    A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       clm2    1       clm_changed2    A       0       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 `db_row_hash_1` (`db_row_hash_1`),
+  UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`),
+  UNIQUE KEY `e` (`e`),
+  UNIQUE KEY `a` (`a`),
+  UNIQUE KEY `d`(`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       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       e       1       e       A       NULL    NULL    NULL    YES     BTREE           
+t1     0       a       1       a       A       NULL    NULL    NULL    YES     BTREE           
+t1     1       d       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
test also

  alter table ... add unique key (blob_column)

in three different variants:
1. there were no duplicates in blob_column, key added successfully,
2. there were duplicates, alter table failed.
3. there were duplicates, ALTER IGNORE TABLE... succeeds

Already added in later commit.
+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(a,b,c,d,e,f),unique(d,e,f,g,h),unique(a,b,c,d,e,f,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');
+#table structure;
+desc t1;
+Field  Type    Null    Key     Default Extra
+a      blob    YES     MUL     NULL    
+b      int(11) YES             NULL    
+c      varchar(2000)   YES     MUL     NULL    
+d      text    YES     MUL     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_b_c`(`a`,`b`,`c`),
+  UNIQUE KEY `c_d_e`(`c`,`d`,`e`),
+  UNIQUE KEY `e_f_g_h`(`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f`(`a`,`b`,`c`,`d`,`e`,`f`),
+  UNIQUE KEY `d_e_f_g_h`(`d`,`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f_g_h`(`a`,`b`,`c`,`d`,`e`,`f`,`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     1       a_b_c   1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   1       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   2       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   3       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 2       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 3       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 4       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 7       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 8       h       A       0       NULL    NULL    YES     HASH_INDEX              
+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
+def    test    t1      a       1       NULL    YES     blob    65535   65535   NULL    NULL    NULL    NULL    NULL    blob    MUL             select,insert,update,references 
+def    test    t1      b       2       NULL    YES     int     NULL    NULL    10      0       NULL    NULL    NULL    int(11)                 select,insert,update,references 
+def    test    t1      c       3       NULL    YES     varchar 2000    2000    NULL    NULL    NULL    latin1  latin1_swedish_ci       varchar(2000)   MUL             select,insert,update,references 
+def    test    t1      d       4       NULL    YES     text    65535   65535   NULL    NULL    NULL    latin1  latin1_swedish_ci       text    MUL             select,insert,update,references 
+def    test    t1      e       5       NULL    YES     varchar 3000    3000    NULL    NULL    NULL    latin1  latin1_swedish_ci       varchar(3000)   MUL             select,insert,update,references 
+def    test    t1      f       6       NULL    YES     longblob        4294967295      4294967295      NULL    NULL    NULL    NULL    NULL    longblob                        select,insert,update,references 
+def    test    t1      g       7       NULL    YES     int     NULL    NULL    10      0       NULL    NULL    NULL    int(11)                 select,insert,update,references 
+def    test    t1      h       8       NULL    YES     text    65535   65535   NULL    NULL    NULL    latin1  latin1_swedish_ci       text                    select,insert,update,references 
+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      1       test    a_b_c   1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c   2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c   3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    c_d_e   1       c       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    c_d_e   2       d       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    c_d_e   3       e       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    e_f_g_h 1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    e_f_g_h 2       f       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    e_f_g_h 3       g       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    e_f_g_h 4       h       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f     1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f     2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f     3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f     4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f     5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f     6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    d_e_f_g_h       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    d_e_f_g_h       2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    d_e_f_g_h       3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    d_e_f_g_h       4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    d_e_f_g_h       5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f_g_h 1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f_g_h 2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f_g_h 3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f_g_h 4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f_g_h 5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f_g_h 6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f_g_h 7       g       A       0       NULL    NULL    YES     HASH_INDEX              
+def    test    t1      1       test    a_b_c_d_e_f_g_h 8       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_b_c   def     test    t1      a       1       NULL    NULL    NULL    NULL
+def    test    a_b_c   def     test    t1      b       2       NULL    NULL    NULL    NULL
+def    test    a_b_c   def     test    t1      c       3       NULL    NULL    NULL    NULL
+def    test    c_d_e   def     test    t1      c       1       NULL    NULL    NULL    NULL
+def    test    c_d_e   def     test    t1      d       2       NULL    NULL    NULL    NULL
+def    test    c_d_e   def     test    t1      e       3       NULL    NULL    NULL    NULL
+def    test    e_f_g_h def     test    t1      e       1       NULL    NULL    NULL    NULL
+def    test    e_f_g_h def     test    t1      f       2       NULL    NULL    NULL    NULL
+def    test    e_f_g_h def     test    t1      g       3       NULL    NULL    NULL    NULL
+def    test    e_f_g_h def     test    t1      h       4       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f     def     test    t1      a       1       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f     def     test    t1      b       2       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f     def     test    t1      c       3       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f     def     test    t1      d       4       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f     def     test    t1      e       5       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f     def     test    t1      f       6       NULL    NULL    NULL    NULL
+def    test    d_e_f_g_h       def     test    t1      d       1       NULL    NULL    NULL    NULL
+def    test    d_e_f_g_h       def     test    t1      e       2       NULL    NULL    NULL    NULL
+def    test    d_e_f_g_h       def     test    t1      f       3       NULL    NULL    NULL    NULL
+def    test    d_e_f_g_h       def     test    t1      g       4       NULL    NULL    NULL    NULL
+def    test    d_e_f_g_h       def     test    t1      h       5       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f_g_h def     test    t1      a       1       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f_g_h def     test    t1      b       2       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f_g_h def     test    t1      c       3       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f_g_h def     test    t1      d       4       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f_g_h def     test    t1      e       5       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f_g_h def     test    t1      f       6       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f_g_h def     test    t1      g       7       NULL    NULL    NULL    NULL
+def    test    a_b_c_d_e_f_g_h def     test    t1      h       8       NULL    NULL    NULL    NULL
+# table select we should not be able to see db_row_hash_column;
+select * from t1;
+a      b       c       d       e       f       g       h
+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_b_c'
+insert into t1 values(0,0,1,1,1,0,0,0);
+ERROR 23000: Duplicate entry '1-1-1' for key 'c_d_e'
+insert into t1 values(0,0,0,0,1,1,1,1);
+ERROR 23000: Duplicate entry '1-1-1-1' for key 'e_f_g_h'
+insert into t1 values(1,1,1,1,1,0,0,0);
+ERROR 23000: Duplicate entry '1-1-1' for key 'a_b_c'
+insert into t1 values(0,0,0,0,1,1,1,1);
+ERROR 23000: Duplicate entry '1-1-1-1' for key 'e_f_g_h'
+insert into t1 values(1,1,1,1,1,1,1,1);
+ERROR 23000: Duplicate entry '1-1-1' for key 'a_b_c'
+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_b_c`(`a`,`b`,`c`),
+  UNIQUE KEY `c_d_e`(`c`,`d`,`e`),
+  UNIQUE KEY `e_f_g_h`(`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f`(`a`,`b`,`c`,`d`,`e`,`f`),
+  UNIQUE KEY `d_e_f_g_h`(`d`,`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f_g_h`(`a`,`b`,`c`,`d`,`e`,`f`,`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_b_c`(`a`,`b`,`c`),
+  UNIQUE KEY `c_d_e`(`c`,`d`,`e`),
+  UNIQUE KEY `e_f_g_h`(`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f`(`a`,`b`,`c`,`d`,`e`,`f`),
+  UNIQUE KEY `d_e_f_g_h`(`d`,`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f_g_h`(`a`,`b`,`c`,`d`,`e`,`f`,`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     1       a_b_c   1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   1       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   2       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   3       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 2       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 3       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 4       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 7       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 8       h       A       0       NULL    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_b_c`(`a`,`b`,`c`),
+  UNIQUE KEY `c_d_e`(`c`,`d`,`e`),
+  UNIQUE KEY `e_f_g_h`(`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f`(`a`,`b`,`c`,`d`,`e`,`f`),
+  UNIQUE KEY `d_e_f_g_h`(`d`,`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f_g_h`(`a`,`b`,`c`,`d`,`e`,`f`,`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     1       a_b_c   1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   1       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   2       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   3       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 2       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 3       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 4       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 7       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 8       h       A       0       NULL    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 `a_b_c`(`aa`,`bb`,`c`),
+  UNIQUE KEY `c_d_e`(`c`,`dd`,`e`),
+  UNIQUE KEY `e_f_g_h`(`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f`(`aa`,`bb`,`c`,`dd`,`e`,`f`),
+  UNIQUE KEY `d_e_f_g_h`(`dd`,`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f_g_h`(`aa`,`bb`,`c`,`dd`,`e`,`f`,`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     1       a_b_c   1       aa      A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   2       bb      A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   1       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   2       dd      A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   3       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 2       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 3       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 4       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     1       aa      A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     2       bb      A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     4       dd      A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       1       dd      A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 1       aa      A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 2       bb      A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 4       dd      A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 7       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 8       h       A       0       NULL    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 `a_b_c`(`a`,`b`,`c`),
+  UNIQUE KEY `c_d_e`(`c`,`d`,`e`),
+  UNIQUE KEY `e_f_g_h`(`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f`(`a`,`b`,`c`,`d`,`e`,`f`),
+  UNIQUE KEY `d_e_f_g_h`(`d`,`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f_g_h`(`a`,`b`,`c`,`d`,`e`,`f`,`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     1       a_b_c   1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   1       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   2       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   3       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 2       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 3       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 4       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 7       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 8       h       A       0       NULL    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 int , modify column b int , modify column c int;
+Warnings:
+Warning        1292    Truncated incorrect INTEGER value: 'maria'
+Warning        1292    Truncated incorrect INTEGER value: 'maria'
+Warning        1292    Truncated incorrect INTEGER value: 'mariadb'
+Warning        1292    Truncated incorrect INTEGER value: 'mariadb'
+show create table t1;
+Table  Create Table
+t1     CREATE TABLE `t1` (
+  `a` int(11) DEFAULT NULL,
+  `b` int(11) DEFAULT NULL,
+  `c` int(11) 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_b_c` (`a`,`b`,`c`),
+  UNIQUE KEY `c_d_e`(`c`,`d`,`e`),
+  UNIQUE KEY `e_f_g_h`(`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f`(`a`,`b`,`c`,`d`,`e`,`f`),
+  UNIQUE KEY `d_e_f_g_h`(`d`,`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f_g_h`(`a`,`b`,`c`,`d`,`e`,`f`,`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_b_c   1       a       A       NULL    NULL    NULL    YES     BTREE           
+t1     0       a_b_c   2       b       A       NULL    NULL    NULL    YES     BTREE           
+t1     0       a_b_c   3       c       A       NULL    NULL    NULL    YES     BTREE           
+t1     1       c_d_e   1       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   2       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   3       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 2       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 3       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 4       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 7       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 8       h       A       0       NULL    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 `a_b_c`(`a`,`b`,`c`),
+  UNIQUE KEY `c_d_e`(`c`,`d`,`e`),
+  UNIQUE KEY `e_f_g_h`(`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f`(`a`,`b`,`c`,`d`,`e`,`f`),
+  UNIQUE KEY `d_e_f_g_h`(`d`,`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f_g_h`(`a`,`b`,`c`,`d`,`e`,`f`,`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     1       a_b_c   1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c   3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   1       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   2       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   3       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 2       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 3       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 4       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 1       a       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 2       b       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 3       c       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 4       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 5       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 6       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 7       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 8       h       A       0       NULL    NULL    YES     HASH_INDEX              
+#try to delete blob column in unique;
+truncate table t1;
+alter table t1 drop column a, drop column b, drop  column c;
+show create table t1;
+Table  Create Table
+t1     CREATE TABLE `t1` (
+  `d` blob DEFAULT NULL,
+  `e` varchar(3000) DEFAULT NULL,
+  `f` longblob DEFAULT NULL,
+  `g` int(11) DEFAULT NULL,
+  `h` text DEFAULT NULL,
+  UNIQUE KEY `c_d_e`(`d`,`e`),
+  UNIQUE KEY `e_f_g_h`(`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f`(`d`,`e`,`f`),
+  UNIQUE KEY `d_e_f_g_h`(`d`,`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f_g_h`(`d`,`e`,`f`,`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     1       c_d_e   1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       c_d_e   2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 1       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 2       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 3       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       e_f_g_h 4       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+#now try to delete keys;
+alter table t1 drop key c_d_e, drop key e_f_g_h;
+show create table t1;
+Table  Create Table
+t1     CREATE TABLE `t1` (
+  `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_b_c_d_e_f`(`d`,`e`,`f`),
+  UNIQUE KEY `d_e_f_g_h`(`d`,`e`,`f`,`g`,`h`),
+  UNIQUE KEY `a_b_c_d_e_f_g_h`(`d`,`e`,`f`,`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     1       a_b_c_d_e_f     1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f     3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       d_e_f_g_h       5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 1       d       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 2       e       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 3       f       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 4       g       A       0       NULL    NULL    YES     HASH_INDEX              
+t1     1       a_b_c_d_e_f_g_h 5       h       A       0       NULL    NULL    YES     HASH_INDEX              
+drop table t1;
diff --git a/mysql-test/t/hidden_field.test b/mysql-test/t/hidden_field.test
new file mode 100644
index 0000000..8f3b452
--- /dev/null
+++ b/mysql-test/t/hidden_field.test
@@ -0,0 +1,152 @@
+create table h_1(abc int primary key, xyz int hidden);
+desc h_1;
+show create table h_1;
+drop table h_1;
+--error ER_TABLE_MUST_HAVE_COLUMNS
+create table h_2(a1 int hidden);
+--error ER_PARSE_ERROR
+create table h_3(a1 blob,hidden(a1));
+--error ER_HIDDEN_NOT_NULL_WOUT_DEFAULT 
+create table h_4(a1 int primary key hidden ,a2 int unique hidden , a3 blob,a4
+int not null hidden unique);
+--error ER_HIDDEN_NOT_NULL_WOUT_DEFAULT
+create table h_5(abc int not null hidden);
+create table t1(a int hidden, b int);
+#should automatically add null
+insert into t1 values(1);
+insert into t1(a) values(1);
+insert into t1(b) values(1);
+insert into t1(a,b) values(5,5);
+select * from t1;
+select a,b from t1;
+delete from t1;
+insert into t1 values(1),(2),(3),(4);
+select * from t1;
+select a from t1;
+drop table t1;
+#echo more complex case of hidden 
+create table t1(a int , b int hidden , c int hidden auto_increment unique, d blob , e int unique, f int);
+desc t1;
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select * from t1;
+drop table t1;
+--error ER_PARSE_ERROR
+create table sdsdsd(a int , b int, hidden(a,b));
+create table t1(a int,abc int as (a mod 3) virtual hidden);
+desc t1;
+--error ER_WRONG_VALUE_COUNT_ON_ROW
+insert into t1 values(1,default);
+insert into t1 values(1),(22),(233);
+select * from t1;
+select a,abc from t1;
+drop table t1;
+create table t1(abc int primary key hidden auto_increment, a int);
+desc t1;
+show create table t1;
+insert into t1 values(1);
+insert into t1 values(2);
+insert into t1 values(3);
+select * from t1;
+select abc,a from t1;
+delete  from t1;
+insert into t1 values(1),(2),(3),(4),(6);
+select abc,a from t1;
+drop table t1;
+create table t1(abc int);
+--error ER_TABLE_MUST_HAVE_COLUMNS
+alter table t1 change abc ss int hidden;
+alter table t1 add column xyz int;
+alter table t1 modify column abc  int ;
+desc t1;
+--error ER_WRONG_VALUE_COUNT_ON_ROW
+insert into t1 values(22);
+alter table t1 modify column abc  int hidden;
+desc t1;
+insert into t1 values(12);
+drop table t1;
+
+--echo some test on copy table structure with table data;
in echo's start the message from #-sign to make it better visible in a result
Changed.
+
+--echo table with hidden fields and unique keys;
+
+create table t1(a int , b int hidden , c int hidden auto_increment unique, d blob , e int unique, f int);
+desc t1;
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select * from t1;
+select a,b,c,d,e,f from t1;
+--echo this wont copy hidden fields and keys;
+create table t2 as select * from t1;
+desc t2;
+select * from t2;
+--error ER_BAD_FIELD_ERROR
+select a,b,c,d,e,f from t2;
+drop table t2;
+--echo now this will copy hidden fields 
+create table t2 as select a,b,c,d,e,f from t1;
+desc t2;
+select * from t2;
+select a,b,c,d,e,f from t2;
+drop table t2,t1;
+
+--echo some test related to copy of data from one table to another;
+create table t1(a int , b int hidden , c int hidden auto_increment unique, d blob , e int unique, f int);
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+select a,b,c,d,e,f from t1;
+create table t2(a int , b int hidden , c int hidden , d blob , e int unique, f int);
+insert into t2 select * from t1;
+select a,b,c,d,e,f from t2;
+truncate t2;
+insert into t2 (a,b,c,d,e,f) select a,b,c,d,e,f from t1;
+select a,b,c,d,e,f from t2;
+truncate t2;
+drop table t1,t2;
+
+--echo some test related to creating view on table with hidden column;
+create table t1(a int , b int hidden , c int hidden auto_increment unique, d blob , e int unique, f int);
+insert into t1 values(1,'d blob',1,1),(1,'d blob',11,1),(1,'d blob',2,1),(1,'d blob',3,1),(1,'d blob',41,1);
+create view v as select * from t1;
+desc v;
+select * from v;
+--echo v does not have hidden column;
+--error ER_BAD_FIELD_ERROR
+select a,b,c,d,e,f from v;
+insert into v values(1,21,32,4);
+select * from v;
+--error ER_BAD_FIELD_ERROR
+insert into v(a,b,c,d,e,f) values(1,12,3,4,5,6);
+drop view v;
+
+create view v as select a,b,c,d,e,f from t1;
+desc v;
+select * from v;
+--echo v does  have hidden column;
+select a,b,c,d,e,f from v;
+insert into v values(1,26,33,4,45,66);
+select a,b,c,d,e,f from v;
+insert into v(a,b,c,d,e,f) values(1,32,31,41,5,6);
+select a,b,c,d,e,f from v;
+drop view v;
+drop table t1;
+
+--echo now hidden column in where and some join query i think no use of this test but anyway;
+create table t1 (a int unique , b int hidden unique, c int unique  hidden);
+insert into t1(a,b,c) values(1,1,1);
+insert into t1(a,b,c) values(2,2,2);
+insert into t1(a,b,c) values(3,3,3);
+insert into t1(a,b,c) values(4,4,4);
+insert into t1(a,b,c) values(21,21,26);
+insert into t1(a,b,c) values(31,31,35);
+insert into t1(a,b,c) values(41,41,45);
+insert into t1(a,b,c) values(22,22,24);
+insert into t1(a,b,c) values(32,32,33);
+insert into t1(a,b,c) values(42,42,43);
+explain select * from t1 where b=3;
+select * from t1 where b=3;
+explain select * from t1 where c=3;
+select * from t1 where c=3;
+
+create table t2 as select a,b,c from t1;
+desc t2;
+explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
+drop table t1,t2;
diff --git a/mysql-test/t/long_uniques.test b/mysql-test/t/long_uniques.test
new file mode 100644
index 0000000..8777aaa
--- /dev/null
+++ b/mysql-test/t/long_uniques.test
@@ -0,0 +1,294 @@
please add also a test case for innodb. In a separate file, long_uniques_innodb.test
in particular, test this case:

   connection con1;
   start transaction;
   insert ('bbbb')
   connection con2;
   start transaction;
   insert ('bbbb')

see? insert the conflicting value in two simultaneously running transactions.
try that in different transaction isolation levels.
normally the second transaction should *not* see the first 'bbbb' because
the first transaction is not committed yet. But InnoDB next-key locks
should guarantee that the second transaction will wait for the first.
TODO
+--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 
+
+--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;
+show keys from 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_column;
+select * from t1;
+--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;
+insert into t1 values(11),(22),(33);
+--error ER_DUP_ENTRY
+insert into t1 values(12),(22);
+select * from t1;
+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);
+
+--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);
+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 column name;
+--echo #this will change index to b tree;
+alter table t1 modify  column a  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;
+
+--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','ff','fdf','gfgfgfg','hghgr'),('maria','db','frter','dasd','utyuty'),
+(123456789034567891,3534534534534,53453453453456,64565464564564,45435345345345),
+(123456789034567890,435345345345,657567567567,78967657567567,657567567567567676);
+
+--echo #table structure;
+desc t1;
+show create table t1;
+show keys from 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_column;
+select * from t1;
+--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,4353453453451,6575675675617,789676575675617,657567567567567676);
+select * from t1;
+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',401));
+--error ER_DUP_ENTRY
+insert into t1 values(repeat('m',4000*11),10,repeat('s',4000*11),repeat('s',4000*11),repeat('s',400));
+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(a,b,c,d,e,f),unique(d,e,f,g,h),unique(a,b,c,d,e,f,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');
+
+--echo #table structure;
+desc t1;
+show create table t1;
+show keys from 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_column;
+select * from t1;
+--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 int , modify column b int , modify column c int;
+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;
+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_d_e, drop key e_f_g_h;
+show create table t1;
+show keys from t1;
+drop table t1;
diff --git a/sql/share/errmsg-utf8.txt b/sql/share/errmsg-utf8.txt
index 8dfa519..9d5b4f5 100644
--- a/sql/share/errmsg-utf8.txt
+++ b/sql/share/errmsg-utf8.txt
@@ -7214,3 +7214,5 @@ ER_CALCULATING_DEFAULT_VALUE
         eng "Got an error when calculating default value for %`s"
 ER_EXPRESSION_REFERS_TO_UNINIT_FIELD 01000
         eng "Expression for field %`-.64s is refering to uninitialized field %`s"
+ER_HIDDEN_NOT_NULL_WOUT_DEFAULT
+        eng "Hidden column '%s' either allow null values or it must have default value"
"... should either allow null values ..."
or
"... should either be nullable ..."
Done.
diff --git a/sql/table.h b/sql/table.h
index 651fab7..9d2b279 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -330,7 +330,28 @@ enum enum_vcol_update_mode
   VCOL_UPDATE_ALL
 };
 
+/* Field visibility enums */
 
+enum  field_visible_type{
+       NOT_HIDDEN=0,
+       USER_DEFINED_HIDDEN,
+       MEDIUM_HIDDEN,
+       FULL_HIDDEN
this could use some comments. Or better names.
NOT_HIDDEN is very clear.
USER_DEFINED_HIDDEN is kind of ok too.
But MEDIUM_HIDDEN is not - what does that mean?
Please, either add a comment, like
// pseudo-columns (like ROWID). Can be queried explicitly in SELECT, otherwise hidden from anything
or rename MEDIUM_HIDDEN to something self-explanatory (may be,
PSEUDO_COLUMN_HIDDEN?).
FULL_HIDDEN is ok, although FULLY_HIDDEN or COMPLETELY_HIDDEN is a bit
easier to read, I'd think.


+};
+
+int rem_field_from_hash_col_str(LEX_STRING *hash_lex, const char *field_name);
confus way to abbr words. plz rena to smth lik

  remove_field_from_hash_index

or, may be

  remove_field_from_hash_index_def
  remove_field_from_hash_index_sql

by the way, this function isn't used anywhere. Forgot to remove it?
Removed.
+
+int change_field_from_hash_col_str(LEX_STRING *hash_lex,
+                                                                                                                                        const char *old_name, char *new_name);
1. notice many <Tab> characters on the previous line that indent
that line way too much.
2. this function doesn't seem to be used either
Removed.
+
+int find_field_name_in_hash(char * hash_str, const char *field_name,
+                                                                                                               int hash_str_length);
again, many <Tab>s on the previous line. Prefer spaces, please.

+
+int find_field_index_in_hash(LEX_STRING * hash_lex, const char * field_name);
name's pretty bad, compared to find_field_name_in_hash.
better rename the previous function to find_field_pos_in_hash_str
or something like that.

+
+int fields_in_hash_str(LEX_STRING *hash_lex);
+
+Field * field_ptr_in_hash_str(LEX_STRING * hash_str, TABLE *table, int index);
 /**
   Category of table found in the table share.
 */
@@ -1031,6 +1052,19 @@ struct TABLE
   Field **field;                       /* Pointer to fields */
 
   uchar *record[2];                    /* Pointer to records */
+  uchar *check_unique_buf;  /* Pointer to record with same hash */
better comment: /* record buf to resolve hash collisions for long UNIQUE constraints */
Changed.

      
+  handler *update_handler;  /* Handler used in case of update */
I don't think you need to store it in the TABLE. You can
either pass it as an argument to ha_update_row() or
invoke check_duplicate_long_entries() before ha_update_row() not from it.

+  /*
+     In the case of write row for long unique we are unable of find
+     Whick key is voilated because we in case of duplicate 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_key;
+  /* If dupp != -1 then this string
+     store message which should be printed */
+  char *err_message;
I wonder whether you need it. You can issue an error (with my_error())
directly from check_duplicate_long_entries(), no need to store
the error till later. If you issue an error early, you'll
simply skip handler::print_keydup_error() later.
This wont work because there can be statements like
alter ignore table t1 add unique key(a);
Anyway I have removed err_message from table class
but dupp_key is still there. I need this here
uint handler::get_dup_key(int error)
{
  DBUG_ASSERT(table_share->tmp_table != NO_TMP_TABLE ||
              m_lock_type != F_UNLCK);
  DBUG_ENTER("handler::get_dup_key");
  table->file->errkey  = (uint) -1;
  if (table->dupp_key != -1)
    DBUG_RETURN(table->dupp_key);

      
   uchar *write_row_record;             /* Used as optimisation in
                                           THD::write_row */
   uchar *insert_values;                  /* used by INSERT ... UPDATE */
diff --git a/sql/field.h b/sql/field.h
index 05e0615..35a7006 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -998,9 +1001,9 @@ class Field: public Value_source
   virtual int cmp(const uchar *,const uchar *)=0;
   virtual int cmp_binary(const uchar *a,const uchar *b, uint32 max_length=~0L)
   { return memcmp(a,b,pack_length()); }
-  virtual int cmp_offset(uint row_offset)
+  virtual int cmp_offset(long row_offset)
1. why is that? because old offset was unsigned?
Yes
2. better use my_ptrdiff_t type here
Changed.
   { return cmp(ptr,ptr+row_offset); }
-  virtual int cmp_binary_offset(uint row_offset)
+  virtual int cmp_binary_offset(long row_offset)
   { return cmp_binary(ptr, ptr+row_offset); };
   virtual int key_cmp(const uchar *a,const uchar *b)
   { return cmp(a, b); }
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 92ba74f..dd55d94 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -1818,6 +1825,27 @@ class store_key_field: public store_key
       When the implementation of this function will be replaced for a proper
       full version this statement probably should be removed.
     */  
+    if (is_hash)
+    {
+      Field *f= copy_field.from_field;
+      String str;
Normally in these cases you should use StringBuffer<MAX_FIELD_WIDTH>
instead of String. It will allocate a char[MAX_FIELD_WIDTH] buffer
on the stack so the following val_str() will not need to call malloc
if the value is shorter than MAX_FIELD_WIDTH.

But in this particular case, you're doing long unique, probably for blobs,
so here the result will typically be larger than MAX_FIELD_WIDTH,
and this StringBuffer optimization will not actually help.

+      f->val_str(&str);
+      if (f->is_null())
+      {
+        *(copy_field.to_ptr-1)= 1; //set it null
why do you write to copy_field.to_ptr directly?
store_key methods don't do that anywhere else
Okay, I will change It. But I think in newer version I do not
require this code. So I will change it later on.
+        null_key= true;
where you reset null_key and copy_field.ptr[-1] back to 0?

+        dbug_tmp_restore_column_map(table->write_set, old_map);
+        return STORE_KEY_OK;
+      }
+      CHARSET_INFO* cs= str.charset();
+      uchar l[4];
+      int4store(l,str.length());
+      cs->coll->hash_sort(cs, l, sizeof(l), &nr1, &nr2);
cs= &my_charset_bin; for hash_sort() above,
cs= str.charset(); for hash_sort() below.

+      cs->coll->hash_sort(cs, (uchar *)str.ptr(), str.length(), &nr1, &nr2);
+      int8store(copy_field.to_ptr, nr1);
+      dbug_tmp_restore_column_map(table->write_set, old_map);
+      return STORE_KEY_OK;
I suppose you don't need to calculate hashes if null_key is already true for
this row (if it's a multi-column key and one of the previous columns was
NULL)

btw, could you add a test case for that? a test case where f->is_null() is true?
like, add assert(0); under that if() and try to create a test case that
will cause it to crash.

+    }
     bzero(copy_field.to_ptr,copy_field.to_length);
 
     copy_field.do_copy(&copy_field);
@@ -1860,6 +1889,26 @@ class store_key_item :public store_key
                                                      table->write_set);
     int res= FALSE;
 
+    if (is_hash)
+    {
+      String *str= item->val_str();
+      if (item->null_value)
+      {
+        *(to_field->ptr - 1)= 1;
+        null_key= true;
+        dbug_tmp_restore_column_map(table->write_set, old_map);
+        return STORE_KEY_OK;
+      }
+      CHARSET_INFO *cs= str->charset();
+      uchar l[4];
+      int4store(l,str->length());
+      cs->coll->hash_sort(cs,l,sizeof(l), &nr1, &nr2);
+      cs->coll->hash_sort(cs, (uchar *)str->ptr(), str->length(), &nr1, &nr2);
+      int8store(to_field->ptr, nr1);
+      //no idea what it does
remove this comment, please :)
and if you're curious what it does, you can always comment
dbug_tmp_use_all_columns() and dbug_tmp_restore_column_map() in this function
out and run the test suite in debug build, you'll see how it will crash :)
or you can ask, that works too...
Okay
+      dbug_tmp_restore_column_map(table->write_set, old_map);
+      return STORE_KEY_OK;
+    }
     /* 
       It looks like the next statement is needed only for a simplified
       hash function over key values used now in BNLH join.
@@ -2271,4 +2320,11 @@ class Pushdown_query: public Sql_alloc
 bool test_if_order_compatible(SQL_I_List<ORDER> &a, SQL_I_List<ORDER> &b);
 int test_if_group_changed(List<Cached_item> &list);
 int create_sort_index(THD *thd, JOIN *join, JOIN_TAB *tab, Filesort *fsort);
+/*
+ It compares the record with same hash to key if
+ record is equal then return 0 else fetches next
+ record with same hash and so on if some error
+ then returns error
+*/
better put this comment where a function is defined, in sql_select.cc

+int compare_hash_and_fetch_next(JOIN_TAB *join);
 #endif /* SQL_SELECT_INCLUDED */
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index fa8f143..eb1769b 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -8394,6 +8394,7 @@ SHOW_VAR status_vars[]= {
   {"Feature_dynamic_columns",  (char*) offsetof(STATUS_VAR, feature_dynamic_columns), SHOW_LONG_STATUS},
   {"Feature_fulltext",         (char*) offsetof(STATUS_VAR, feature_fulltext), SHOW_LONG_STATUS},
   {"Feature_gis",              (char*) offsetof(STATUS_VAR, feature_gis), SHOW_LONG_STATUS},
+  {"Feature_hidden_column",    (char*) offsetof(STATUS_VAR, feature_hidden_column), SHOW_LONG_STATUS},
I feel that Feature_hidden_columns (plural) would look better here.

I know we aren't consistent, there's Feature_dynamic_columns (plural)
but Feature_subquery, Feature_timezone (singular), so there is no rule here,
unfortunately.
Done
   {"Feature_locale",           (char*) offsetof(STATUS_VAR, feature_locale), SHOW_LONG_STATUS},
   {"Feature_subquery",         (char*) offsetof(STATUS_VAR, feature_subquery), SHOW_LONG_STATUS},
   {"Feature_timezone",         (char*) offsetof(STATUS_VAR, feature_timezone), SHOW_LONG_STATUS},
diff --git a/sql/field.cc b/sql/field.cc
index c684e6a..8c17b76 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -7671,7 +7672,6 @@ uint32 Field_varstring::data_length()
 {
   return length_bytes == 1 ? (uint32) *ptr : uint2korr(ptr);
 }
-
restore the empty line, please

 /*
   Functions to create a packed row.
   Here the number of length bytes are depending on the given max_length
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 8bb12ce..45e6cce 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -3899,6 +3899,25 @@ bool subselect_uniquesubquery_engine::copy_ref_key(bool skip_constants)
       */
       DBUG_RETURN(true);
     }
+    if ((*copy)->is_hash)
+    {
+      if (!(*copy)->null_key && *(copy+1))
+      {
+        (*(copy+1))->nr1= (*copy)->nr1;
+        (*(copy+1))->nr2= (*copy)->nr2;
I don't get it, why *(copy+1) ?
Supoose a key is like unique(a,b,c) then I need to transfer
nr1,nr2 from one copy var  to another copy var. That is why
I am doing this.
 
+      }
+      else
+        break;
+    }
+  }
+  //reset nr1 and nr2
+  for (store_key **copy=tab->ref.key_copy ; *copy ; copy++)
+  {
+    if ((*copy)->is_hash)
+    {
+      (*copy)->nr1= 1;
+      (*copy)->nr2= 4;
+    }
   }
   DBUG_RETURN(false);
 }
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 7f8c89c..3c71e8c 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1839,6 +1839,40 @@ void Item_func_int_div::fix_length_and_dec()
 }
 
 
+longlong  Item_func_hash::val_int()
+{
+  unsigned_flag= true;
+  ulong nr1= 1,nr2= 4;
+  CHARSET_INFO *cs;
+  for(uint i= 0;i<arg_count;i++)
+  {
+    String * str = args[i]->val_str();
+    if(args[i]->null_value)
+    {
+      null_value= 1;
+      return 0;
+    }
+    cs= str->charset();
+    uchar l[4];
+    int4store(l, str->length());
+    cs->coll->hash_sort(cs, l, sizeof(l), &nr1, &nr2);
+    cs->coll->hash_sort(cs, (uchar *)str->ptr(), str->length(), &nr1, &nr2);
that's the third time I see these sequence of lines
(and there's one more below). Why not to put it in a reusable function?
like

 calc_hash_for_unique(&nr1, &nr2, str) {
   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);
 }
Done.
+  }
+  null_value= 0;
+  //for testing purpose
+  //nr1=12;
remove that, please ^^^
Removed.
+  return   (longlong)nr1;
+}
+
+
+void  Item_func_hash::fix_length_and_dec()
+{
+  maybe_null= 1;
+  decimals= 0;
+  max_length= 8;
+}
+
+
 longlong Item_func_mod::int_op()
 {
   DBUG_ASSERT(fixed == 1);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 9d7e735..61022e6 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -6191,6 +6192,11 @@ vcol_attribute:
             lex->alter_info.flags|= Alter_info::ALTER_ADD_INDEX;
           }
         | COMMENT_SYM TEXT_STRING_sys { Lex->last_field->comment= $2; }
+        | HIDDEN_SYM
+          {
+              LEX *lex =Lex;
+              lex->last_field->field_visibility=USER_DEFINED_HIDDEN;
+          }
Please, make HIDDEN keyword non-reserved. For that it should be added
to the keyword_sp rule. And add a test case for it, like

  --echo # HIDDEN is not reserved
  create table t1 (hidden int);
  drop table t1;
Done.
         ;
 
 parse_vcol_expr:
@@ -9482,6 +9476,12 @@ function_call_keyword:
             if ($$ == NULL)
               MYSQL_YYABORT;
           }
+        |HASH_SYM '(' expr_list ')'
You've added it to function_call_keyword rule, but it should
have been added to the function_call_conflict rule.
See comments before these both rules.
Done.
+            {
+                $$= new (thd->mem_root)Item_func_hash(thd,*$3);
+                if($$==NULL)
+                    MYSQL_YYABORT;
+            }
         | INSERT '(' expr ',' expr ',' expr ',' expr ')'
           {
             $$= new (thd->mem_root) Item_func_insert(thd, $3, $5, $7, $9);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 8028779..41f5a55 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -198,6 +198,17 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
   TABLE *table= table_list->table;
   DBUG_ENTER("check_insert_fields");
 
+  List_iterator<Item> i_iter(values);
1. you don't use i_iter here
2. I'd move the loop into a separate function or a method of TABLE:

   uint num_of_hiddens_fields() {
     for (...)
   }

see below

+  int num_of_hiddens_fields= 0;
+  if (!fields.elements)
+  {
+    Field ** f= table->field, *field;
+    for (; f && (field= *f); f++)
+    {
+      if (field->field_visibility != NOT_HIDDEN)
+        num_of_hiddens_fields++;
+    }
+   }
   if (!table_list->single_table_updatable())
   {
     my_error(ER_NON_INSERTABLE_TABLE, MYF(0), table_list->alias, "INSERT");
@@ -212,7 +223,7 @@ static int check_insert_fields(THD *thd, TABLE_LIST *table_list,
                table_list->view_db.str, table_list->view_name.str);
       DBUG_RETURN(-1);
     }
-    if (values.elements != table->s->fields)
+    if (values.elements+num_of_hiddens_fields != table->s->fields)
then this if() becomes:

 if (values.elements + num_of_hiddens_fields() != table->s->fields)

in fact, you can have a function that counts *visible* fields, then
this if() becomes simply

 if (values.elements != table->not_hidden_fields())

     {
       my_error(ER_WRONG_VALUE_COUNT_ON_ROW, MYF(0), 1L);
       DBUG_RETURN(-1);
@@ -1485,7 +1497,28 @@ bool mysql_prepare_insert(THD *thd, TABLE_LIST *table_list,
                                update_values, false, &map);
       select_lex->no_wrap_view_item= FALSE;
     }
-
+    /*
+      Reason for this condition
+      suppose this
+        create table t1 (a int , b int , c int hidden , d int);
+        create view v as select a,b,c,d from t1;
+      now query like this fails
+        insert into v values(1,1,1)
+      because in insert_view_fields we copy all the fields
+      whether they are hidden or not we can not do the change
+      there because there we have only fields name so we need
+      to manually setup fields as insert_view_fields is called
+      by only mysql_prepare_insert_check_table function and
+      mysql_prepare_insert_check_table is called by only by this
+      function so it is safe to do here
+
+      NOT YET IMPLEMENTED
+    if (insert_into_view && !is_field_specified_for_view
+         && fields.elements)
+    {
+      Item *ii= fields.pop();
+    }
+    **/
1. I didn't understand the comment, couldn't parse it. Could you, please
   add some punctuation to it? :)
2. I believe you have a test case for it, and it works. Does it mean
   that the whole commented block is obsolete and should be removed?
   If yes, don't forget to remove is_field_specified_for_view declaration.
No It does not work.
I will try to solve this.
Actually the main problem is
suppose
create table t1(a int , b int , c int hidden, d int);
create view v as select a,b,c,d from t1;
then query like
            insert into t1 values(1,1,1);
fails.
The problem is desc v shows it is hidden. But it
is not setting default values for field c
     /* Restore the current context. */
     ctx_state.restore_state(context, table_list);
   }
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index d59b8b7..dfe83e7 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -729,7 +729,17 @@ int mysql_update(THD *thd,
   */
   can_compare_record= records_are_comparable(table);
   explain->tracker.on_scan_init();
-
+  for (uint i= 0; i < table->s->keys; i++)
+  {
+    if (table->key_info[i].flags & HA_UNIQUE_HASH)
+    {
+      if (!table->update_handler)
Can table->update_handler be possibly not NULL here?
No it cant be. Changed.
+        table->update_handler= table->file->clone(table->s->normalized_path.str,
+                                                  &table->mem_root);
Note that you create this new handler on the table's memroot!
This is wrong, as I've explained in an earlier review, memroot can
only be freed as a whole, so by repeating UPDATE statements, you
will allocate more and more memory in the table's memroot for
update_handler's. You need to allocate it in the thd's memroot, because
update_handler's has a life time of only one statement.

+      table->update_handler->ha_external_lock(current_thd, F_RDLCK);
+      break;
+    }
+  }
   while (!(error=info.read_record(&info)) && !thd->killed)
   {
     explain->tracker.on_record_read();
@@ -1912,6 +1929,19 @@ multi_update::initialize_tables(JOIN *join)
 
     if (ignore)
       table->file->extra(HA_EXTRA_IGNORE_DUP_KEY);
+
+    for (uint i= 0; i < table->s->keys; i++)
+    {
+      if (table->key_info[i].flags & HA_UNIQUE_HASH)
+      {
+        if (!table->update_handler)
+          table->update_handler= table->file->clone(table->s->normalized_path.str,
+                                                    &table->mem_root);
+        table->update_handler->ha_external_lock(current_thd, F_RDLCK);
+        break;
+      }
+    }
same code as in mysql_update(), right?
better move it to a small function. For example,

 handler *create_update_handler(THD *thd, TABLE *table)
 {
   handler *update_handler= 0;
   for (uint i= 0; i < table->s->keys; i++)
   {
     if (table->key_info[i].flags & HA_UNIQUE_HASH)
     {
       update_handler= table->file->clone(table->s->normalized_path.str,
                                          &table->mem_root);
       update_handler->ha_external_lock(thd, F_RDLCK);
       return update_handler;
     }
   }
   return NULL;
 }

+
     if (table == main_table)                   // First table in join
     {
       if (safe_update_on_fly(thd, join->join_tab, table_ref, all_tables))
@@ -2039,6 +2069,13 @@ multi_update::~multi_update()
   for (table= update_tables ; table; table= table->next_local)
   {
     table->table->no_keyread= table->table->no_cache= 0;
+    if (table->table->update_handler)
+    {
+      table->table->update_handler->ha_external_lock(current_thd, F_UNLCK);
+      table->table->update_handler->ha_close();
+      delete table->table->update_handler;
+      table->table->update_handler= NULL;
+    }
same here:

 void delete_update_handler(THD *thd, handler *h)
 {
   if (h)
   {
     h->ha_external_lock(thd, F_UNLCK);
     h->ha_close();
     delete h;
   }
}

by the way, try to avoid current_thd, if possible. it is farily
expensive on some platforms.
Done

      
     if (ignore)
       table->table->file->extra(HA_EXTRA_NO_IGNORE_DUP_KEY);
   }
diff --git a/sql/unireg.cc b/sql/unireg.cc
index 19d03d2..d502b68 100644
--- a/sql/unireg.cc
+++ b/sql/unireg.cc
@@ -89,6 +89,26 @@ static uchar *extra2_write(uchar *pos, enum extra2_frm_value_type type,
   return extra2_write(pos, type, reinterpret_cast<LEX_STRING *>(str));
 }
 
+static uchar *extra2_write_field_visibility_hash_info(uchar *pos,
please, rename to extra2_write_additional_field_properties
or something like that.

Done.
+                   int number_of_fields,List_iterator<Create_field> * it)
+{
+  *pos++=EXTRA2_FIELD_FLAGS;
+  /*
+   always 2  first for field visibility
+   second for is this column represent long unique hash
+   */
+  size_t len = 2*number_of_fields;
+  pos= extra2_write_len(pos,len);
+  Create_field *cf;
+  while((cf=(*it)++))
+  {
+    *pos++=cf->field_visibility;
+    *pos++=cf->is_long_column_hash;
you can do one byte per field, field_visibility is only two bits,
is_long_column_hash is one bit.
Okay , I tried this , but I am not sure if it is rigth or not.
+  }
+  return pos;
+}
+
+
 /**
   Create a frm (table definition) file
 
@@ -121,6 +141,22 @@ LEX_CUSTRING build_frm_image(THD *thd, const char *table,
   uchar *frm_ptr, *pos;
   LEX_CUSTRING frm= {0,0};
   DBUG_ENTER("build_frm_image");
+  List_iterator<Create_field> it(create_fields);
+  Create_field *field;
+  bool is_hidden_fields_present= false;
please, rename to "have_additional_field_properties"

Okay.
+  /*
+    Loop througt the iterator to find whether we have any field whose
+    visibility_type != NOT_HIDDEN
+  */
Please remove the comment above. Remember, a comment needs to say *why* you
are doing something, it should not say *what* you are doing.  In this case
your comment merely says what the loop below is doing, the comment is in
English, the loop in C++, but otherwise they say exactly the same thing. If
you rename the variable (as I suggested above) this loop will be completely
clear without any comments anyway.

+  while ((field=it++))
+  {
+    if (field->field_visibility != NOT_HIDDEN)
+    {
+      is_hidden_fields_present= true;
+      break;
+    }
+  }
+  it.rewind();
 
  /* If fixed row records, we need one bit to check for deleted rows */
   if (!(create_info->table_options & HA_OPTION_PACK_RECORD))
@@ -265,7 +303,9 @@ LEX_CUSTRING build_frm_image(THD *thd, const char *table,
     pos+= gis_field_options_image(pos, create_fields);
   }
 #endif /*HAVE_SPATIAL*/
-
+  if (is_hidden_fields_present)
+    pos=extra2_write_field_visibility_hash_info(pos,create_fields.elements,&it);
+  it.rewind();
you can rewind from inside extra2_write_field_visibility_hash_info.
seems logical - it moves the iterator, it should restore it
Okay
   int4store(pos, filepos); // end of the extra2 segment
   pos+= 4;
 
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 3481bf1..ce63a88 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5287,6 +5287,8 @@ find_field_in_table(THD *thd, TABLE *table, const char *name, uint length,
 
   if (field_ptr && *field_ptr)
   {
+    if ((*field_ptr)->field_visibility == FULL_HIDDEN)
+       DBUG_RETURN((Field*) 0);
good. please add a test case for that, with a subquery and an outer reference,
as I wrote earlier.
Added , but there are some bugs,
     *cached_field_index_ptr= field_ptr - table->field;
     field= *field_ptr;
   }
@@ -7351,6 +7353,10 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
 
     for (; !field_iterator.end_of_fields(); field_iterator.next())
     {
+      /* Field can be null here details in test case*/
"details in test case" is not very helpful. in what case can field be 0 here?
Yes it can be
Test case
create table t1 (empnum smallint, grp int);
create table t2 (empnum int, name char(5));
insert into t1 values(1,1);
insert into t2 values(1,'bob');
create view v1 as select * from t2 inner join t1 using (empnum);
select * from v1;
+      if ((field= field_iterator.field()) &&
+               field->field_visibility != NOT_HIDDEN)
+        continue;
       Item *item;
 
       if (!(item= field_iterator.create_item(thd)))
@@ -7986,6 +7992,42 @@ fill_record(THD *thd, TABLE *table, Field **ptr, List<Item> &values,
     only one row.
   */
   table->auto_increment_field_not_null= FALSE;
+  Field **f;
+  List_iterator<Item> i_iter(values);
+  uint field_count= 0;
+  for (f= ptr; f && (field= *f); f++)
+    field_count++;
+  /*
+    This if is required in query like
+    suppose table
+      create table t1 (a int , b int hidden , c int , d int hidden );
+    and query is
+      create table t2 as select a,b,c,d from t1;
+    in this case field count will be equal to values.elements
+   */
+  if (field_count != values.elements)
+  {
+    Name_resolution_context *context= & thd->lex->select_lex.context;
+    for (f= ptr; f && (field= *f); f++)
+    {
+      if (field->field_visibility!=NOT_HIDDEN)
+      {
+        if (f == ptr)
+        {
+          values.push_front(new (thd->mem_root)
+                            Item_default_value(thd,context),thd->mem_root);
+          i_iter.rewind();
+          i_iter++;
+        }
+        else
+          i_iter.after(new (thd->mem_root) Item_default_value(thd,context));
+      }
+      else
+        i_iter++;
+    }
+    f= ptr;
+    i_iter.rewind();
+  }
I don't understand what this is doing and why. Could you explain, please?
Actually comment is obsolute , so I removed It , It basically  enter the deafult values in hidden fields
For example
Create table t1 (a int , b int hidden);
insert into t1 value(1);
here field_count is 2 but values count is 1 so I have to artifically enter default value.

      
   while ((field = *ptr++) && ! thd->is_error())
   {
     /* Ensure that all fields are from the same table */
diff --git a/sql/handler.cc b/sql/handler.cc
index 3fbd1b3..99e043b 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -5875,6 +5883,132 @@ int handler::ha_reset()
   DBUG_RETURN(reset());
 }
 
+/** @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(c,d))
+    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 c_d 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
Sorry, I didn't understand this comment. Could you rephrase it?
btw, there are no columns 'c' and 'd' in your table example.

also, please, explain all function parameters here (use @param).

+   @returns 0 if no duplicate else returns error
+  */
+int check_duplicate_long_entries(TABLE *table, handler *h, uchar *new_rec,
+                                  int key)
+{
+  Field *hash_field;
+  int result;
+  table->dupp_key= -1;
+  for (uint i= 0; i < table->s->keys; i++)
+  {
+    if (key != -1)
+      i= key;
+    if (table->key_info[i].flags & HA_UNIQUE_HASH)
+    {
you invoke check_duplicate_long_entries() only in two places.
one is in  check_duplicate_long_entries_update(), inside the loop
that checks for (table->key_info[i].flags & HA_UNIQUE_HASH).
So I really don't see why you need to repeat this loop and the check
here. The second invocation is in ha_write_row(), where key==-1
and you need a loop, indeed.
So, the logical thing to do would be to have a function (let's call if F())
which is the content of this if() and always takes a valid key number
as an argument. Then check_duplicate_long_entries() would do:

 for (uint i= 0; i < table->s->keys; i++)
 {
   if (table->key_info[i].flags & HA_UNIQUE_HASH)
     F(table, h, new_rec, i);
 }

and check_duplicate_long_entries_update() would invoke F() directly.

also, note that if a function is only used in one file - like this
your check_duplicate_long_entries(), it should be declared static.

+      hash_field= table->key_info[i].key_part->field;
+      DBUG_ASSERT(table->key_info[i].key_length == HA_HASH_KEY_LENGTH_WITH_NULL);
+      uchar ptr[HA_HASH_KEY_LENGTH_WITH_NULL];
+
+      if (hash_field->is_null())
+        continue;
+
+      key_copy(ptr, new_rec, &table->key_info[i],
+                      table->key_info[i].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_read_idx_map(table->check_unique_buf,
+                                  i, ptr, HA_WHOLE_KEY, HA_READ_KEY_EXACT);
+      if (!result)
+      {
+        Item_func_or_sum * temp= static_cast<Item_func_or_sum *>(hash_field->
+                                                        vcol_info->expr_item);
+        Item_args * t_item= static_cast<Item_args *>(temp);
+        uint arg_count= t_item->argument_count();
+        Item ** arguments= t_item->arguments();
+        int diff= table->check_unique_buf-new_rec;
+        Field * t_field;
+
+        for (uint j=0; j < arg_count; j++)
+        {
Add DBUG_ASSERT(arguments[j]->type() == FIELD_ITEM);

+          t_field= static_cast<Item_field *>(arguments[j])->field;
+          if(t_field->cmp_binary_offset(diff))
+            continue;
+        }
+        table->dupp_key= i;
+        if (!table->err_message)
+        {
+          table->err_message= (char *) alloc_root(&table->mem_root,
+                                                 MAX_KEY_LENGTH);
+        }
+        StringBuffer<MAX_KEY_LENGTH> str;
+        str.length(0);
+        for(uint i= 0; i < arg_count; i++)
+        {
+          t_field= ((Item_field *)arguments[i])->field;
+          if (str.length())
+            str.append('-');
+          field_unpack(&str, t_field, new_rec, 5,//since blob can be to long
+                       false);
truncation is a great idea. Two comments:
1. make this 5 a defined constant. Like

  #define MAX_BLOB_LEN_FOR_ERRMSG 5

2. perhaps 5 is too short? dunno.
3. indicate the truncation. Like

  if (t_field->pack_length() > MAX_BLOB_LEN_FOR_ERRMSG)
    str.append(STRING_WITH_LEN("..."));
TODO

      
+        }
+        memcpy(table->err_message,str.ptr(),str.length());
+        return HA_ERR_FOUND_DUPP_KEY;
+      }
+    }
+    if (key != -1)
+      break;
+  }
+  return 0;
+}
+
+/** @brief
+    check whether updated records breaks the
+    unique constraint on long columns.
+   @returns 0 if no duplicate else returns error
+  */
+int check_duplicate_long_entries_update(TABLE *table, handler *h, uchar *new_rec)
+{
+  Field **f, *field;
+  LEX_STRING *ls;
+  int error;
+  /*
+     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].flags & HA_UNIQUE_HASH)
+    {
+      ls= &table->key_info[i].key_part->field->vcol_info->expr_str;
+      for (f= table->field; f && (field= *f); f++)
+      {
+        if (find_field_name_in_hash(ls->str, (char *)field->field_name, ls->length) != -1)
ok, I'm confused. Why are you doing that?
For INSERT you take the HASH Item and iterate its argument array.
That's fine, I get it.
For UPDATE you get the vcol expression as a *string* (!!!) and extract column
names from there by looking at commas (???). What was that?

+        {
+          /* Compare fields if they are different then check for duplicates*/
+          if(field->cmp_binary_offset(reclength))
+          {
+            if((error= check_duplicate_long_entries(table, table->update_handler,
+                                                   new_rec, i)))
+              return error;
+            /*
+              break beacuse check_duplicate_long_entries will
+              take care of remaning fields
+             */
+            break;
+          }
+        }
+      }
+    }
+  }
+  return 0;
+}
 
 int handler::ha_write_row(uchar *buf)
 {
@@ -5921,6 +6057,8 @@ int handler::ha_update_row(const uchar *old_data, uchar *new_data)
note that mysql_update can use ha_bulk_update_row() instead of ha_update_row()
for some storage engine. I don't think bulk update can work with
your code at all, so you need to make sure will_batch is always false
if long unique indexes are used (this is easy). See sql_update.cc

And there's bulk insert too, start_bulk_insert(), that you probably
need to disable as well.
TODO

      
   mark_trx_read_write();
   increment_statistics(&SSV::ha_update_count);
 
+  if ((error= check_duplicate_long_entries_update(table, table->file, new_data)))
+    return error;
   TABLE_IO_WAIT(tracker, m_psi, PSI_TABLE_UPDATE_ROW, active_index, 0,
                       { error= update_row(old_data, new_data);})
 
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 30c65e6..a55cccd 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -1972,6 +1976,25 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet,
     }
     append_create_options(thd, packet, field->option_list, check_options,
                           hton->field_options);
+    //TODO need a better logic to find wheter to put comma or not
+    int i=1;
+    bool is_comma_needed=false;
+    if (*(ptr+i)!=NULL)
+    {
+      is_comma_needed=true;
+      while((*(ptr+i))->field_visibility==MEDIUM_HIDDEN ||
+            (*(ptr+i))->field_visibility==FULL_HIDDEN)
+      {
+        i++;
+        if(!*(ptr+i))
+        {
+          is_comma_needed =false;
+          break;
+        }
+      }
+    }
+    if(is_comma_needed)
+     packet->append(STRING_WITH_LEN(",\n"));
What was wrong with the old logic? Print the comma before every visible
column, excluding the first one. You only need to replace the "first one"
condition, like

-   if (ptr != table->field)
+   if (not_the_first_field)
     packet->append(STRING_WITH_LEN(",\n"));
+   not_the_first_field= true;

Changed.
   }
 
   key_info= table->key_info;
@@ -1986,6 +2009,19 @@ int show_create_table(THD *thd, TABLE_LIST *table_list, String *packet,
   for (uint i=0 ; i < share->keys ; i++,key_info++)
   {
     KEY_PART_INFO *key_part= key_info->key_part;
+    if (key_info->flags & HA_UNIQUE_HASH)
+    {
+      char * column_names= key_part->field->vcol_info->
+                          expr_str.str + HA_HASH_STR_LEN;
+      int length= key_part->field->vcol_info->expr_str.length;
+      length-= HA_HASH_STR_LEN;
+      packet->append(STRING_WITH_LEN(",\n"));
+      packet->append(STRING_WITH_LEN("  UNIQUE KEY `"));
+      packet->append(key_info->name, strlen(key_info->name));
+      packet->append(STRING_WITH_LEN("`"));
+      packet->append(column_names, length);
No, I'm afraid you cannot do that. You need to generate the column
list by iterating arguments and printing them. Add this test case to your
long_uniques.test:

   create table t1 (a int, b int, c blob, d blob, unique(a,b), unique(c,d));
   set sql_quote_show_create=0;
   show create table t1;
   set sql_quote_show_create=default;
   show create table t1;
   set sql_mode=ansi_quotes;
   show create table t1;
   set sql_mode=default;

(you don't need create table as above, of course, you can use any
existing table in the test file).
Changed, Now it uses old logic.

      
+      continue;
+    }
     bool found_primary=0;
     packet->append(STRING_WITH_LEN(",\n  "));
 
@@ -5411,6 +5447,10 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
 
   for (; (field= *ptr) ; ptr++)
   {
+    if(field->field_visibility == FULL_HIDDEN ||
+           field->field_visibility == MEDIUM_HIDDEN)
+      continue;
+    /* For now we will only show UNI or MUL for TODO  */
what do you mean by this comment?
It says I have to setup MUL and UNI for desc table having blob unique columns.

     uchar *pos;
     char tmp[MAX_FIELD_WIDTH];
     String type(tmp,sizeof(tmp), system_charset_info);
@@ -5465,20 +5505,48 @@ static int get_schema_column_record(THD *thd, TABLE_LIST *tables,
     pos=(uchar*) ((field->flags & PRI_KEY_FLAG) ? "PRI" :
                  (field->flags & UNIQUE_KEY_FLAG) ? "UNI" :
                  (field->flags & MULTIPLE_KEY_FLAG) ? "MUL":"");
+    KEY *key= show_table->key_info;
+    for (int i=0; i<show_table->s->keys; i++, key++)
+    {
+      if (key->flags & HA_UNIQUE_HASH)
+      {
+        LEX_STRING * ls= &key->key_part->field->vcol_info->expr_str;
+        int position= find_field_index_in_hash(ls, field->field_name);
+        int fields= fields_in_hash_str(ls);
+        //this is for single  hash(`abc`)
+        if (position == 0 && fields == 1)
+        {
+            pos= (uchar *) "UNI";
+        }
+        //this is for   hash(`abc`,`xyzs`)
+        if (position == 0 && fields > 1)
+        {
+          pos=(uchar *) "MUL";
+        }
+      }
+    }
really? why wouldn't you set UNIQUE_KEY_FLAG or MULTIPLE_KEY_FLAG instead
so that the old code would just work for your new keys?

Done.
     table->field[16]->store((const char*) pos,
                             strlen((const char*) pos), cs);
-
+    StringBuffer<256> buf;
     if (field->unireg_check == Field::NEXT_NUMBER)
-      table->field[17]->store(STRING_WITH_LEN("auto_increment"), cs);
+      buf.set(STRING_WITH_LEN("auto_increment"),cs);
     if (print_on_update_clause(field, &type, true))
-      table->field[17]->store(type.ptr(), type.length(), cs);
+      buf.set(type.ptr(), type.length(),cs);
     if (field->vcol_info)
     {
       if (field->vcol_info->stored_in_db)
-        table->field[17]->store(STRING_WITH_LEN("PERSISTENT"), cs);
+        buf.set(STRING_WITH_LEN("PERSISTENT"), cs);
       else
-        table->field[17]->store(STRING_WITH_LEN("VIRTUAL"), cs);
+        buf.set(STRING_WITH_LEN("VIRTUAL"), cs);
+    }
+    /*hidden can coexist with auto_increment and virtual */
+    if(field->field_visibility==USER_DEFINED_HIDDEN)
+    {
+      if (buf.length())
+        buf.append(STRING_WITH_LEN(" , "));
no space before the comma
Changed.
+      buf.append(STRING_WITH_LEN("HIDDEN"),cs);
     }
+    table->field[17]->store(buf.ptr(), buf.length(), cs);
     table->field[19]->store(field->comment.str, field->comment.length, cs);
     if (schema_table_store_record(thd, table))
       DBUG_RETURN(1);
@@ -6030,6 +6098,89 @@ int fill_schema_proc(THD *thd, TABLE_LIST *tables, COND *cond)
   DBUG_RETURN(res);
 }
 
+static int print_get_schema_stat_keypart(THD *thd, TABLE_LIST *tables,
+                                                                                                                                                                TABLE *table,TABLE *show_table,
+                                                                                                                                                                LEX_STRING *db_name,
+                                                                                                                                                                LEX_STRING *table_name,
+                                                                                                                                                                KEY * key_info,KEY_PART_INFO
+                                                                                                                                                                *key_part,Field * field ,int i,int j)
1. lots of tabs above that mess up the indentation.
2. Function comment, please.
3. i and j are *really* lousy argument names, please rename.

Removed this whole function.
+{
+  CHARSET_INFO *cs= system_charset_info;
+  const char *str;
+  restore_record(table, s->default_values);
+  table->field[0]->store(STRING_WITH_LEN("def"), cs);
+  table->field[1]->store(db_name->str, db_name->length, cs);
+  table->field[2]->store(table_name->str, table_name->length, cs);
+  table->field[3]->store((longlong) ((key_info->flags &
+                                      HA_NOSAME) ? 0 : 1), TRUE);
+  table->field[4]->store(db_name->str, db_name->length, cs);
+  table->field[5]->store(key_info->name, strlen(key_info->name), cs);
+  table->field[6]->store((longlong) (j+1), TRUE);
+  str=field ? field->field_name :"?unknown field?";
+  table->field[7]->store(str, strlen(str), cs);
+  if (show_table->file)
+  {
+    if (show_table->file->index_flags(i, j, 0) & HA_READ_ORDER)
+    {
+      table->field[8]->store(((key_part->key_part_flag &
+                               HA_REVERSE_SORT) ?
+                              "D" : "A"), 1, cs);
+      table->field[8]->set_notnull();
+    }
+    KEY *key=show_table->key_info+i;
+    if (key->rec_per_key[j])
+    {
+      ha_rows records= (ha_rows) ((double) show_table->stat_records() /
+                                  key->actual_rec_per_key(j));
+      table->field[9]->store((longlong) records, TRUE);
+      table->field[9]->set_notnull();
+    }
+    /*
+      In the case of long unique hash as we try
+      to calc key->rec_per_key[j] it will give zero
+      so cardinality will be set to null we do not want
+      this so
+     */
why not? if you don't know the cardinality - set it to NULL.
but, in fact, it shouldn't be zero, why is it?

+    if (key_info->flags & HA_UNIQUE_HASH)
+    {
+      table->field[9]->store(0, TRUE);
+      table->field[9]->set_notnull();
+    }
+    if (key_info->flags & HA_UNIQUE_HASH)
+      table->field[13]->store(HA_HASH_STR_INDEX,HA_HASH_STR_INDEX_LEN , cs);
I don't think you need to define that string, just put it here like

 table->field[13]->store(STRING_WITH_LEN("HASH_INDEX") , cs);

+    else
+    {
+      str= show_table->file->index_type(i);
+      table->field[13]->store(str, strlen(str), cs);
+    }
+  }
+  if (!(key_info->flags & HA_FULLTEXT) &&
+      (key_part->field &&
+       key_part->length !=
+       show_table->s->field[key_part->fieldnr-1]->key_length()))
+  {
+    table->field[10]->store((longlong) key_part->length /
+                            key_part->field->charset()->mbmaxlen, TRUE);
+    table->field[10]->set_notnull();
+  }
+  uint flags= key_part->field ? key_part->field->flags : 0;
+  const char *pos=(char*) ((flags & NOT_NULL_FLAG) ? "" : "YES");
+  table->field[12]->store(pos, strlen(pos), cs);
+  if (!show_table->s->keys_in_use.is_set(i))
+    table->field[14]->store(STRING_WITH_LEN("disabled"), cs);
+  else
+    table->field[14]->store("", 0, cs);
+  table->field[14]->set_notnull();
+  DBUG_ASSERT(MY_TEST(key_info->flags & HA_USES_COMMENT) ==
+             (key_info->comment.length > 0));
+  if (key_info->flags & HA_USES_COMMENT)
+    table->field[15]->store(key_info->comment.str,
+                            key_info->comment.length, cs);
+  if (schema_table_store_record(thd, table))
+    return 1;
+  return 0;
+}
+
 
 static int get_schema_stat_record(THD *thd, TABLE_LIST *tables,
                                  TABLE *table, bool res,
@@ -6066,67 +6217,34 @@ static int get_schema_stat_record(THD *thd, TABLE_LIST *tables,
                              HA_STATUS_TIME);
       set_statistics_for_table(thd, show_table);
     }
     for (uint i=0 ; i < show_table->s->keys ; i++, key_info++)
     {
       KEY_PART_INFO *key_part= key_info->key_part;
-      const char *str;
       for (uint j=0 ; j < key_info->user_defined_key_parts; j++, key_part++)
       {
-        restore_record(table, s->default_values);
-        table->field[0]->store(STRING_WITH_LEN("def"), cs);
-        table->field[1]->store(db_name->str, db_name->length, cs);
-        table->field[2]->store(table_name->str, table_name->length, cs);
-        table->field[3]->store((longlong) ((key_info->flags &
-                                            HA_NOSAME) ? 0 : 1), TRUE);
-        table->field[4]->store(db_name->str, db_name->length, cs);
-        table->field[5]->store(key_info->name, strlen(key_info->name), cs);
-        table->field[6]->store((longlong) (j+1), TRUE);
-        str=(key_part->field ? key_part->field->field_name :
-             "?unknown field?");
-        table->field[7]->store(str, strlen(str), cs);
-        if (show_table->file)
+
+        if (key_info->flags & HA_UNIQUE_HASH && key_info->key_part->field)
         {
-          if (show_table->file->index_flags(i, j, 0) & HA_READ_ORDER)
+          LEX_STRING *ls = &key_info->key_part->field->vcol_info->expr_str;
+          int total_fields= fields_in_hash_str(ls);
+          int counter= 0;
+          Field *fld;
+          while (counter < total_fields)
           {
-            table->field[8]->store(((key_part->key_part_flag &
-                                     HA_REVERSE_SORT) ?
-                                    "D" : "A"), 1, cs);
-            table->field[8]->set_notnull();
-          }
-          KEY *key=show_table->key_info+i;
-          if (key->rec_per_key[j])
-          {
-            ha_rows records= (ha_rows) ((double) show_table->stat_records() /
-                                        key->actual_rec_per_key(j));
-            table->field[9]->store((longlong) records, TRUE);
-            table->field[9]->set_notnull();
+            fld= field_ptr_in_hash_str(ls, show_table, counter);
parsing strings, again? don't do that, please.

+            if(print_get_schema_stat_keypart(thd, tables, table,
+                                             show_table, db_name,
+                                             table_name, key_info,
+                                             key_part, fld, i, counter))
+              DBUG_RETURN(1);
+            counter++;
           }
-          str= show_table->file->index_type(i);
-          table->field[13]->store(str, strlen(str), cs);
-        }
-        if (!(key_info->flags & HA_FULLTEXT) &&
-            (key_part->field &&
-             key_part->length !=
-             show_table->s->field[key_part->fieldnr-1]->key_length()))
-        {
-          table->field[10]->store((longlong) key_part->length /
-                                  key_part->field->charset()->mbmaxlen, TRUE);
-          table->field[10]->set_notnull();
+
+          continue;
         }
-        uint flags= key_part->field ? key_part->field->flags : 0;
-        const char *pos=(char*) ((flags & NOT_NULL_FLAG) ? "" : "YES");
-        table->field[12]->store(pos, strlen(pos), cs);
-        if (!show_table->s->keys_in_use.is_set(i))
-          table->field[14]->store(STRING_WITH_LEN("disabled"), cs);
-        else
-          table->field[14]->store("", 0, cs);
-        table->field[14]->set_notnull();
-        DBUG_ASSERT(MY_TEST(key_info->flags & HA_USES_COMMENT) ==
-                   (key_info->comment.length > 0));
-        if (key_info->flags & HA_USES_COMMENT)
-          table->field[15]->store(key_info->comment.str, 
-                                  key_info->comment.length, cs);
-        if (schema_table_store_record(thd, table))
+
+        if(print_get_schema_stat_keypart(thd, tables,table,show_table,db_name,
+                                         table_name,key_info,key_part,key_part->field,i,j))
           DBUG_RETURN(1);
       }
     }
@@ -6134,7 +6252,6 @@ static int get_schema_stat_record(THD *thd, TABLE_LIST *tables,
   DBUG_RETURN(res);
 }
 
-
add the empty line back, please

 static int get_schema_views_record(THD *thd, TABLE_LIST *tables,
                                   TABLE *table, bool res,
                                   LEX_STRING *db_name,
diff --git a/sql/table.cc b/sql/table.cc
index 640ab82..5d5be01 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -685,7 +685,7 @@ static bool create_key_infos(const uchar *strpos, const uchar *frm_image_end,
                              uint keys, KEY *keyinfo,
                              uint new_frm_ver, uint &ext_key_parts,
                              TABLE_SHARE *share, uint len,
-                             KEY *first_keyinfo, char* &keynames)
+                             KEY *first_keyinfo, char* &keynames,const uchar *key_ex_flags)
this new argument seems to be unused
Reverted, was used in previous versions.
 {
   uint i, j, n_length;
   KEY_PART_INFO *key_part= NULL;
@@ -738,7 +738,6 @@ static bool create_key_infos(const uchar *strpos, const uchar *frm_image_end,
       keyinfo->algorithm= HA_KEY_ALG_UNDEF;
       strpos+=4;
     }
-
don't delete empty lines, please, restore them all

     if (i == 0)
     {
       ext_key_parts+= (share->use_ext_keys ? first_keyinfo->user_defined_key_parts*(keys-1) : 0); 
@@ -801,7 +799,8 @@ static bool create_key_infos(const uchar *strpos, const uchar *frm_image_end,
     keyinfo->ext_key_parts= keyinfo->user_defined_key_parts;
     keyinfo->ext_key_flags= keyinfo->flags;
     keyinfo->ext_key_part_map= 0;
-    if (share->use_ext_keys && i && !(keyinfo->flags & HA_NOSAME))
+    if (share->use_ext_keys && i && !(keyinfo->flags & HA_NOSAME)
+  )
revert

     {
       for (j= 0; 
            j < first_key_parts && keyinfo->ext_key_parts < MAX_REF_PARTS;
@@ -989,6 +988,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
   const uchar *frm_image_end = frm_image + frm_length;
   uchar *record, *null_flags, *null_pos, *mysql57_vcol_null_pos;
   const uchar *disk_buff, *strpos;
+  const uchar * field_properties=NULL,*key_ex_flags=NULL;
this key_ex_flags is not used for anything

   ulong pos, record_offset; 
   ulong rec_buff_length;
   handler *handler_file= 0;
@@ -1056,7 +1056,7 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
         if (length < 256)
           goto err;
       }
-      if (extra2 + length > e2end)
+      if ( extra2 + length > e2end)
revert

         goto err;
       switch (type) {
       case EXTRA2_TABLEDEF_VERSION:
@@ -1101,6 +1101,9 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
         }
 #endif /*HAVE_SPATIAL*/
         break;
+      case EXTRA2_FIELD_FLAGS:
+         field_properties = extra2;
imagine in some later MariaDB version we'll need more flags.
there may be more than two bytes per column.
so here you can check the length and fail with an error (no assert!)
if it's not num_of_fields*2.
Added.
+        break;
       default:
         /* abort frm parsing if it's an unknown but important extra2 value */
         if (type >= EXTRA2_ENGINE_IMPORTANT)
@@ -1795,6 +1797,16 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
     reg_field->field_index= i;
     reg_field->comment=comment;
     reg_field->vcol_info= vcol_info;
+    if(field_properties!=NULL)
+    {
+      reg_field->field_visibility=static_cast<field_visible_type>(*field_properties++);
+      reg_field->is_long_column_hash=static_cast<bool>(*field_properties++);
why did you need a cast here? was there compiler warning?
Changed.
+    }
+    /*
+       We will add status variable only when we find a user defined hidden column
"increment". or, better, remove this comment completely, it's obvious anyway
Removed
+    */
+    if (reg_field->field_visibility == USER_DEFINED_HIDDEN)
+      status_var_increment(thd->status_var.feature_hidden_column);
     if (field_type == MYSQL_TYPE_BIT && !f_bit_as_char(pack_flag))
     {
       null_bits_are_used= 1;
@@ -2004,13 +2016,27 @@ 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();
+        /*
+          Add HA_UNIQUE_HASH flag if keyinfo has only one field
+          and field has is_long_column_hash flag on
+        */
+        if (keyinfo->user_defined_key_parts == 1 &&
+             field->is_long_column_hash)
+        {
+          keyinfo->flags|= HA_UNIQUE_HASH;
+          keyinfo->ext_key_flags|= HA_UNIQUE_HASH;
why do you set it in ext_key_flags?
It was used in optimizer. Anyway new version does not use this code.
+        }
         if (field->null_ptr)
         {
           key_part->null_offset=(uint) ((uchar*) field->null_ptr -
                                         share->default_values);
           key_part->null_bit= field->null_bit;
           key_part->store_length+=HA_KEY_NULL_LENGTH;
-          keyinfo->flags|=HA_NULL_PART_KEY;
+          if (keyinfo->flags & HA_UNIQUE_HASH &&
+              !(keyinfo->flags & HA_NULL_PART_KEY))
+          {}
+          else
+            keyinfo->flags|=HA_NULL_PART_KEY;
1. why not simply

  if (!(keyinfo->flags & HA_UNIQUE_HASH))
    keyinfo->flags|=HA_NULL_PART_KEY;
Actually this was more complex. If keyinfo does not have
HA_NULL_PART_KEY flag for HA_UNIQUE_HASH , then this means
Key can not be null. But removed this in newer version.
2. this needs a comment
           keyinfo->key_length+= HA_KEY_NULL_LENGTH;
         }
         if (field->type() == MYSQL_TYPE_BLOB ||
@@ -2124,6 +2149,11 @@ int TABLE_SHARE::init_from_binary_frm_image(THD *thd, bool write,
       if ((keyinfo->flags & HA_NOSAME) ||
           (ha_option & HA_ANY_INDEX_MAY_BE_UNIQUE))
         set_if_bigger(share->max_unique_length,keyinfo->key_length);
+      if (keyinfo->flags & HA_UNIQUE_HASH)
+      {
+        keyinfo->ext_key_parts= 1;
+        keyinfo->ext_key_part_map= 0;
+      }
why?
This is a create_key_info code.
if (share->use_ext_keys && i && !(keyinfo->flags &HA_NOSAME))
    {
      for (j= 0;
           j < first_key_parts && keyinfo->ext_key_parts < MAX_REF_PARTS;
           j++)
      {
        uint key_parts= keyinfo->user_defined_key_parts;
        KEY_PART_INFO* curr_key_part= keyinfo->key_part;
        KEY_PART_INFO* curr_key_part_end= curr_key_part+key_parts;
        for ( ; curr_key_part < curr_key_part_end; curr_key_part++)
        {
          if (curr_key_part->fieldnr == first_key_part[j].fieldnr)
            break;
        }
        if (curr_key_part == curr_key_part_end)
        {
          *key_part++= first_key_part[j];
          *rec_per_key++= 0;
          keyinfo->ext_key_parts++;
          keyinfo->ext_key_part_map|= 1 << j;
        }
      }
      if (j == first_key_parts)
        keyinfo->ext_key_flags= keyinfo->flags | HA_EXT_NOSAME;
hash key_info does not have HA_NOSAME flag so, its keyinfo->ext_key_parts
is added. But we do not want this. Anyway removed this in new version.
Now HA_UNIQUE_HASH key also have HA_NOSAME flag.
     }
     if (primary_key < MAX_KEY &&
        (share->keys_in_use.is_set(primary_key)))
@@ -7808,3 +7842,193 @@ double KEY::actual_rec_per_key(uint i)
   return (is_statistics_from_stat_tables ?
           read_stats->get_avg_frequency(i) : (double) rec_per_key[i]);
 }
+
+/*
+   find out that whether field name exists in hash_str
+   return index of  hash_str  if found other wise returns
+   -1
+*/
+int find_field_name_in_hash(char * hash_str, const  char * field_name,
+                            int hash_str_length)
ok, all these manipulations with the expression *string*
should be removed. Column names might contain commas,
column names might contain backticks - you don't handle that.
And you should not, really, don't use vcol_info->expr_str, use
vcol_info->expr_item instead.

Removed. Now I use expr_item.
+{
+
+  int j= 0, i= 0;
+  for (i= 0; i < hash_str_length; i++)
+  {
+    while (*(hash_str+i) == *(field_name+j))
+    {
+      i++;
+      j++;
+      if(*(field_name+j)=='\0' &&*(hash_str+i)=='`')
+        goto done;
+    }
+    j=0;
+  }
+  return -1;
+  done:
+  return i;
+}
+
+/*
+   find out the field positoin in hash_str()
+   position starts from 0
+   else return -1;
+*/
+int find_field_index_in_hash(LEX_STRING *hash_lex, const char * field_name)
+{
+  char *hash_str= hash_lex->str;
+  int hash_str_length= hash_lex->length;
+  int field_name_position= find_field_name_in_hash(hash_str, field_name, hash_str_length);
+  if (field_name_position == -1)
+    return -1;
+  int index= 0;
+  for (int i= 0; i < field_name_position; i++)
+  {
+    if (hash_str[i] == ',')
+      index++;
+  }
+  return index;
+}
+
+/*
+   find total number of field in hash_str
+*/
+int fields_in_hash_str(LEX_STRING * hash_lex)
+{
+  int hash_str_length= hash_lex->length;
+  char *hash_str= hash_lex->str;
+  int num_of_fields= 1;
+  for (int i= 0; i<hash_str_length; i++)
+  {
+    if (hash_str[i] == ',' && hash_str[i-1] == '`'
+         && hash_str[i+1] == '`' )
+      num_of_fields++;
+  }
+  return num_of_fields;
+}
+
+/*
+   return fields ptr given by hash_str index
+   for example
+   hash(`abc`,`xyz`)
+   index 1 will return pointer to xyz field
+*/
+Field * field_ptr_in_hash_str(LEX_STRING * hash_str, TABLE *table, int index)
+{
+  char field_name[100]; // 100 is enough i think
+  int temp_index= 0;
+  char *str= hash_str->str;
+  int i= strlen("hash"), j;
+  Field **f, *field;
+  while (i < hash_str->length)
+  {
+    if (str[i] == ',')
+      temp_index++;
+    if (temp_index >= index)
+      break;
+    i++;
+  }
+  i+= 2;  // now i point to first character of field name
+  for (j= 0; str[i+j] !=  '`'; j++)
+    field_name[j]= str[i+j];
+  field_name[j]= '\0';
+  for (f= table->field; f && (field= *f); f++)
+  {
+    if (!my_strcasecmp(system_charset_info, field->field_name, field_name))
+      break;
+  }
+  return field;
+}
+
+/*
+  Remove field name from db_row_hash_* column vcol info str
+  For example
+
+  hash(`abc`,`xyz`)
+  remove "abc" will return
+  0 and hash_str will be set hash(`xyz`) and length will be set
+
+  hash(`xyz`)
+  remove "xyz" will return
+  0 and hash_str will be set NULL and length will be 0
+  hash(`xyz`)
+  remove "xyzff" will return
+  1 no change to hash_str and length
+  TODO a better and less complex logic
+*/
+int rem_field_from_hash_col_str(LEX_STRING * hash_lex, const char * field_name)
+{
+   /* first of all find field_name in hash_str*/
+  char * temp= hash_lex->str;
+  const char * t_field= field_name;
+  int i= find_field_name_in_hash(temp, field_name, hash_lex->length);
+  if ( i != -1)
+  {
+    /*
+      We found the field location
+      First of all we need to find the
+      , position and there can be three
+      situations
+      1. two , not a problem remove any one
+      2. one , remove this
+      3  no , return
+   */
+    // see if there is , before field name
+    int j= strlen(field_name);
+    if (*(temp + i -j-2) == ',')
+    {
+      hash_lex->length= hash_lex->length- j-2-1;//-2 for two '`' and -1 for ','
+      memmove(temp+i-j-2, temp+i+1, hash_lex->length);
+      return 0;
+    }
+    if (*(temp+i+1) == ',')
+    {
+      hash_lex->length= hash_lex->length-j-2-1;//-2 for two '`' and -1 for ','
+      memmove(temp+i-j-1, temp+i+2, hash_lex->length);
+      return 0;
+    }
+    if (*(temp+i+1) == ')')
+    {
+      hash_lex->length= 0;
+      hash_lex->str= NULL;
+      return 0;
+    }
+  }
+  return 1;
+}
+/*   returns 1 if old_name not found in hash_lex 0 other wise*/
+int  change_field_from_hash_col_str(LEX_STRING * hash_lex, const char * old_name,
+                                    char * new_name)
+{
+  /* first of all find field_name in hash_lex*/
+  char * temp= hash_lex->str;
+  const char * t_field= old_name;
+  int i= find_field_name_in_hash(temp, old_name, hash_lex->length);
+  if (i != -1)
+  {
+    int len= hash_lex->length-strlen(old_name) + strlen(new_name);
+    int num= 0;
+    char  temp_arr[len];
+    int s_c_position= i - strlen(old_name);//here it represent the posotion of
+                                          //'`' before old f_name
+    for (int index= 0; index < len; index++)
+    {
+      if (index >= s_c_position && index < s_c_position+strlen(new_name))
+      {
+        temp_arr[index]= new_name[index-s_c_position];
+        continue;
+      }
+      if (index >= s_c_position+strlen(new_name))
+      {
+        temp_arr[index]= temp[i+num];
+        num++;
+        continue;
+      }
+      temp_arr[index]= temp[index];
+    }
+    strcpy(hash_lex->str, temp_arr);
+    hash_lex->length= len;
+    return 0;
+  }
+  return 1;
+}
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5cc7798..921cf18 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
this is impressive :)
but to send you something sooner, I'm going to skip your optimizer
changes now, I'll send the review of the original project (long UNIQUE
constraint) first, then I'll finish the review of the optimizer part.

diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index e745fe8..ed8aa8f 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3186,6 +3186,177 @@ static void check_duplicate_key(THD *thd,
   }
 }
 
+/*
+  Add hidden level 3 hash field to table in case of long
+  unique column
+  Returns 0 on success
+  else 1
+*/
+
+int add_hash_field(THD * thd, Alter_info *alter_info, Key *current_key,
+                KEY *current_key_info, KEY *key_info, CHARSET_INFO *cs)
should be declared static

Done.
+{
+  int num= 1;
+  List_iterator<Key> key_iter(alter_info->key_list);
+  List_iterator<Key_part_spec> key_part_iter(current_key->columns);
+  List_iterator<Create_field> it(alter_info->create_list);
+  Create_field *dup_field, * sql_field;
+  Key_part_spec *temp_colms;
+
+  Create_field *cf= new (thd->mem_root) Create_field();
+  cf->flags|= UNSIGNED_FLAG;
+  cf->length= cf->char_length= HA_HASH_FIELD_LENGTH;
+  cf->charset= NULL;
+  cf->decimals= 0;
+  char *temp_name= (char *)thd->alloc(30);
+  strcpy(temp_name, HA_DB_ROW_HASH_STR);
+  char num_holder[10];    //10 is way more but i think it is ok
+  sprintf(num_holder, "%d",num);
+  strcat(temp_name, num_holder);
1. my_snprintf, not sprintf. for just one number - strtol
2. "%u" not "%d"
3. with sprintf, you don't need strcat:

   my_snprintf(temp_name, sizeof(temp_name), "DB_ROW_HASH_%u", num);
Okay , Changed.
+  /*
+    Check for collusions
+   */
+  while ((dup_field= it++))
+  {
+    if (!my_strcasecmp(system_charset_info, temp_name, dup_field->field_name))
+    {
+      temp_name[12]= '\0'; //now temp_name='DB_ROW_HASH_'
+      num++;
+      sprintf(num_holder, "%d",num);
+      strcat(temp_name, num_holder);
+      it.rewind();
+    }
+  }
+  it.rewind();
+  cf->field_name= temp_name;
+  cf->sql_type= MYSQL_TYPE_LONGLONG;
+  /* hash column should be atmost hidden */
should be "fully hidden"
Changed
+  cf->field_visibility= FULL_HIDDEN;
+  cf->is_long_column_hash= true;
+  /* add the virtual colmn info */
+  Virtual_column_info *v= new (thd->mem_root) Virtual_column_info();
+  char * hash_exp= (char *)thd->alloc(1024);
+  char * key_name= (char *)thd->alloc(252);
+  strcpy(hash_exp, HA_HASH_STR_HEAD);
+  temp_colms= key_part_iter++;
+  strcat(hash_exp, temp_colms->field_name.str);
+  strcpy(key_name, temp_colms->field_name.str);
+  strcat(hash_exp, "`");
+  while ((temp_colms= key_part_iter++))
+  {
+    while ((sql_field= it++) &&
+           my_strcasecmp(system_charset_info,
+              temp_colms->field_name.str, sql_field->field_name))
+    {}
+    it.rewind();
+    /*
+      There should be only one key for db_row_hash_* column
+      we need to give user a error when the accidently query
+      like
+
+      create table t1(abc blob unique, unique(db_row_hash_1));
+      alter table t2 add column abc blob unique,add unique key(db_row_hash_1);
+
+      for this we will iterate through the key_list and
+      find if and key_part has the same name as of temp_name
+     */
+    if (!sql_field || sql_field->is_long_column_hash)
+    {
+      my_error(ER_KEY_COLUMN_DOES_NOT_EXITS, MYF(0), temp_name);
+      return 1;
+    }
hmm, are you sure this check is needed?
you've just added a column, there can be no existing key that
refers to it, can it?

Or, may be, you added a column before the code that checks whether a key is
valid? In that case, that code will still fail with
ER_KEY_COLUMN_DOES_NOT_EXITS, because your column is fully hidden.

Either way, your check looks redundant.
Removed this whole part.
+    /*
+      This test for wrong query like
+      create table t1(a blob ,unique(a,a));
+    */
+    if (find_field_name_in_hash(hash_exp,
+                 temp_colms->field_name.str, strlen(hash_exp))!=-1)
+    {
+      my_error(ER_DUP_FIELDNAME, MYF(0), temp_colms->field_name.str);
+      return 1;
+    }
+    /* If any field can be null add flag */
+    if (!sql_field->flags & NOT_NULL_FLAG)
+      current_key_info->flags|= HA_NULL_PART_KEY;
+    strcat(hash_exp, (const char * )",");
+    strcat(key_name, "_");
+    strcat(hash_exp, "`");
+    strcat(hash_exp, temp_colms->field_name.str);
+    strcat(key_name, temp_colms->field_name.str);
+    strcat(hash_exp, "`");
1. hash_exp is generated incorrectly, you forgot that a column
   name itself can contain backticks. There are quoting functions
   in sql_show.cc and as my_snprintf("%`s")
Changed.
2. do you really need to create a true virtual column here, with the
   hash expression and store it in the frm? you can store the key as a
   normal key (with actual blobs, not uint for the hash value),
   and generate the vcol in init_from_binary_frm_image().
This is a big change. Took me 3-4 days , But anyway done.
+  }
+  strcat(hash_exp, (const char * )")");
+  v->expr_str.str= hash_exp;
+  v->expr_str.length= strlen(hash_exp);
+  v->expr_item= NULL;
+  v->set_stored_in_db_flag(true);
+  cf->vcol_info= v;
+  cf->charset= cs;
+  cf->create_length_to_internal_length();
+  cf->length= cf->char_length= cf->pack_length;
+  prepare_create_field(cf, NULL, 0);
+  if (!current_key_info->flags & HA_NULL_PART_KEY)
+  {
+    cf->pack_flag^= FIELDFLAG_MAYBE_NULL;
+    cf->flags^= NOT_NULL_FLAG;
+  }
+  alter_info->create_list.push_front(cf,thd->mem_root);
+  /* Update row offset because field is added in first position */
+  int offset=0;
+  it.rewind();
+  while ((dup_field= it++))
+  {
+    dup_field->offset= offset;
+    if (dup_field->stored_in_db())
+      offset+= dup_field->pack_length;
+  }
+  it.rewind();
+  while ((dup_field= it++))
+  {
+    if (!dup_field->stored_in_db())
+    {
+      dup_field->offset= offset;
+      offset+= dup_field->pack_length;
+    }
+  }
+  if(current_key->name.length==0)
+  {
+    current_key_info->name= key_name;
+    current_key_info->name_length= strlen(key_name);
+    key_name= make_unique_key_name(thd, key_name,
+          key_info, current_key_info);
+  }
+  else
+    current_key_info->name= current_key->name.str;
+  if (check_if_keyname_exists(current_key_info->name, key_info,
+                              current_key_info))
+  {
+    my_error(ER_DUP_KEYNAME, MYF(0), key_name);
+    return 1;
+  }
+  current_key->type= Key::MULTIPLE;
+  current_key_info->key_length= cf->pack_length; //length of mysql long column
+  current_key_info->user_defined_key_parts= 1;
+  current_key_info->flags= 0;
+  current_key_info->key_part->fieldnr= 0;
+  current_key_info->key_part->offset= 0;
+  current_key_info->key_part->key_type= cf->pack_flag;
+  current_key_info->key_part->length= cf->pack_length;
+  /* As key is added in front so update update keyinfo field ref and offset*/
+  KEY * t_key = key_info;
+  KEY_PART_INFO *t_key_part;
+  while (t_key != current_key_info)
+  {
+    t_key_part= t_key->key_part;
+    for (int i= 0; i < t_key->user_defined_key_parts; i++,t_key_part++)
+    {
+      t_key_part->fieldnr+= 1;
+      t_key_part->offset+= cf->pack_length;
+    }
+    t_key++;
+  }
+  return 0;
+}
 
 /*
   Preparation for table creation
@@ -3283,7 +3454,13 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
       /* Fix for prepare statement */
       thd->change_item_tree(&sql_field->default_value->expr_item, item);
     }
-
+    if (sql_field->field_visibility == USER_DEFINED_HIDDEN &&
+        sql_field->flags & NOT_NULL_FLAG &&
+        sql_field->flags & NO_DEFAULT_VALUE_FLAG)
+    {
+      my_error(ER_HIDDEN_NOT_NULL_WOUT_DEFAULT, MYF(0), sql_field->field_name);
say "WITHOUT", this "WOUT" looks weird
Changed, I thought short form will work.
+      DBUG_RETURN(TRUE);
+    }
     if (sql_field->sql_type == MYSQL_TYPE_SET ||
         sql_field->sql_type == MYSQL_TYPE_ENUM)
     {
@@ -3884,10 +4070,24 @@ mysql_prepare_create_table(THD *thd, HA_CREATE_INFO *create_info,
           if (f_is_geom(sql_field->pack_flag) && sql_field->geom_type ==
               Field::GEOM_POINT)
             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 (!column->length)
+    {
+      if (key->type == Key::PRIMARY)
+      { //todo change error message
agree, ER_TOO_LONG_KEY would be better here
Changed
+        my_error(ER_BLOB_KEY_WITHOUT_LENGTH, MYF(0), column->field_name.str);
+        DBUG_RETURN(TRUE);
+      }
+      if (!add_hash_field(thd, alter_info, key, key_info,
+                          *key_info_buffer, create_info->default_table_charset))
+      {
+        key_part_info= key_info->key_part;
+        key_part_info++;
+        null_fields++;
+        key_length= HA_HASH_KEY_LENGTH_WITHOUT_NULL;
+        break;
+      }
+      else
+        DBUG_RETURN(TRUE);
          }
        }
 #ifdef HAVE_SPATIAL
@@ -3974,9 +4174,9 @@ 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);
-           }
+                               my_error(ER_TOO_LONG_KEY, MYF(0), key_part_length);
+                               DBUG_RETURN(TRUE);
why don't you call add_hash_field() here?
if this place is now impossible, add a DBUG_ASSERT(0) there or remove the
if() completely and replace it with an assert. I mean:

  if (condition)
  {
     something;
  }
  else
  {
     something else;
  }

becomes

  DBUG_ASSERT(condition);
  something;

+                       }
          }
        }
         // Catch invalid use of partial keys 
@@ -4021,8 +4221,23 @@ 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)
+               {
+                       my_error(ER_TOO_LONG_KEY, MYF(0), key_part_length);
+                       DBUG_RETURN(TRUE);
+               }
+               //todo we does not respect length given by user in calculating hash
oh. that's a bug. good that you have a comment about it,
there're more urgent issues that this one, but it should be fixed eventually
//TODO
+               if(!add_hash_field(thd, alter_info, key, key_info,
+                                                                                        *key_info_buffer, create_info->default_table_charset))
+               {
+                       key_part_info= key_info->key_part;
+                       key_part_info++;
+                       null_fields++;
+                       key_length= HA_HASH_KEY_LENGTH_WITHOUT_NULL;
+                       break;
+               }
+               else
+                       DBUG_RETURN(TRUE);
        }
       }
       key_part_info->length= (uint16) key_part_length;
@@ -7502,6 +7717,8 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
   */
   for (f_ptr=table->field ; (field= *f_ptr) ; f_ptr++)
   {
+    if (field->field_visibility == FULL_HIDDEN)
+      continue;
ok, so you recreate DB_ROW_HASH_xxx columns every time
Yes, BTW I changed this If condition to this one
if  (field->is_long_unique_hash)
    continue;
     Alter_drop *drop;
     if (field->type() == MYSQL_TYPE_VARCHAR)
       create_info->varchar= TRUE;
@@ -7818,7 +8057,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table,
 
       if (key_info->flags & HA_SPATIAL)
         key_type= Key::SPATIAL;
-      else if (key_info->flags & HA_NOSAME)
+      else if (key_info->flags & HA_NOSAME || key_info->flags & HA_UNIQUE_HASH)
       {
         if (! my_strcasecmp(system_charset_info, key_name, primary_key_name))
           key_type= Key::PRIMARY;
huh? I thought your HA_UNIQUE_HASH indexes cannot be Key::PRIMARY
Sorry , I did not see this.
Regards,
Sergei
Chief Architect MariaDB
and security@mariadb.org