maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #09952
Re: Sachin weekly report
Hi Sergei!
Actually I implemented optimization on where with update and delete ,
with minimal changes on optimizer. Also now we can
use optimization in joins, select including subquery etc. I haven't
written much test case, but I will write it soon.
Please review it.
Regards
sachin
On Wed, Sep 7, 2016 at 11:06 PM, Sachin Setia <sachinsetia1001@xxxxxxxxx> wrote:
> Hi Sergei!
>
> This code is about adding new long unique key, This is a section of
> code from mysql_prepare_create_table, The main problem is can we make
> this code more shorter ? And the reason for this code is that we need
> to add
> Alter_info::ALTER_ADD_CHECK_CONSTRAINT , only if there is unique key
> on long columns
>
> Query statements can be like this
>
> alter table t1 add unique key a; // here a is blob
>
> or
>
> alter table t1 add b blob unique;
> alter table t1 add c varchar(1000) unique;
>
> This is code
>
> {
> Key *key;
> while ((key=key_it++)) // Add new keys
> {
> if (key->type == Key::FOREIGN_KEY &&
> ((Foreign_key *)key)->validate(new_create_list))
> goto err;
>
> from here
> ==> if (key->type == Key::UNIQUE)
> {
> List_iterator_fast<Key_part_spec> li(key->columns);
> Key_part_spec *column;
> uint total_length= 0;
> Field **f, *field;
> Create_field *cf;
> bool is_hash_key= false;
> while ((column= li++))
> {
> if (column->length > table->file->max_key_part_length())
> {
> is_hash_key= true;
> break;
> }
> else if (!column->length)
> {
> for (f= table->field; f && (field= *f); f++)
> {
> if (!my_strcasecmp(system_charset_info, field->field_name,
> column->field_name.str))
> {
> if (field->max_display_length() >
> table->file->max_key_part_length())
> {
> is_hash_key= true;
> goto exit;
> }
> total_length+= field->max_display_length();
> }
> }
> /*
> Suppose query is like
> alter table t1 add column a blob unique;
> alter table t2 add column a blob,add column c blob,
> add unique key(a,b);
> In this case we have to add ALTER_ADD_CHECK_CONSTRAINT_FLAG
> We cant simply add ALTER_ADD_CHECK_CONSTRAINT flag
> because it is expensive.
> And there is no other way of doing this type of check.
> */
> field_it.rewind();
> while((cf= field_it++))
> {
> if (!my_strcasecmp(system_charset_info, cf->field_name,
> column->field_name.str))
> {
> if (cf->sql_type == MYSQL_TYPE_TINY_BLOB ||
> cf->sql_type == MYSQL_TYPE_MEDIUM_BLOB ||
> cf->sql_type == MYSQL_TYPE_LONG_BLOB ||
> cf->sql_type == MYSQL_TYPE_BLOB)
> {
> is_hash_key= true;
> break;
> }
> if ((cf->sql_type == MYSQL_TYPE_VARCHAR ||
> cf->sql_type == MYSQL_TYPE_VAR_STRING) &&
> cf->length > table->file->max_key_part_length())
> {
> is_hash_key= true;
> break;
> }
> total_length+= cf->length;
> }
> }
> }
> }
> exit:
> if (is_hash_key || total_length > table->file->max_key_length())
> {
> alter_info->flags |= Alter_info::ALTER_ADD_CHECK_CONSTRAINT;
> alter_info->flags |= Alter_info::ALTER_ADD_COLUMN;
> }
> }
> new_key_list.push_back(key, thd->mem_root);
> if (key->name.str &&
> !my_strcasecmp(system_charset_info, key->name.str, primary_key_name))
> {
> my_error(ER_WRONG_NAME_FOR_INDEX, MYF(0), key->name.str);
> goto err;
> }
> }
> }
>
> On Wed, Aug 31, 2016 at 8:49 PM, sachin setiya
> <sachinsetia1001@xxxxxxxxx> wrote:
>> 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(©_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@xxxxxxxxxxx
>>
>>
References
-
Re: Sachin weekly report
From: Sergei Golubchik, 2016-06-14
-
Re: Sachin weekly report
From: Sachin Setia, 2016-06-20
-
Re: Sachin weekly report
From: Sachin Setia, 2016-06-26
-
Re: Sachin weekly report
From: Sachin Setia, 2016-07-05
-
Re: Sachin weekly report
From: Sergei Golubchik, 2016-07-11
-
Re: Sachin weekly report
From: sachin setiya, 2016-08-13
-
Re: Sachin weekly report
From: Sachin Setia, 2016-08-13
-
Re: Sachin weekly report
From: Sergei Golubchik, 2016-08-24
-
Re: Sachin weekly report
From: sachin setiya, 2016-08-31
-
Re: Sachin weekly report
From: Sachin Setia, 2016-09-07
