maria-developers team mailing list archive
-
maria-developers team
-
Mailing list archive
-
Message #09943
Re: Sachin weekly report
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
>
>
Follow ups
References
