Re: [Maria-developers] Sachin weekly report

Hi Sergei! Actually I implemented optimization on where with update and delete , with minimal changes on optimizer. Also now we can use optimization in joins, select including subquery etc. I haven't written much test case, but I will write it soon. Please review it. Regards sachin On Wed, Sep 7, 2016 at 11:06 PM, Sachin Setia <sachinsetia1001@gmail.com> wrote:

Hi Sergei!

This code is about adding new long unique key, This is a section of code from mysql_prepare_create_table, The main problem is can we make this code more shorter ? And the reason for this code is that we need to add Alter_info::ALTER_ADD_CHECK_CONSTRAINT , only if there is unique key on long columns

Query statements can be like this

alter table t1 add unique key a; // here a is blob

or

alter table t1 add b blob unique; alter table t1 add c varchar(1000) unique;

This is code

{ Key *key; while ((key=key_it++)) // Add new keys { if (key->type == Key::FOREIGN_KEY && ((Foreign_key *)key)->validate(new_create_list)) goto err;

from here ==> if (key->type == Key::UNIQUE) { List_iterator_fast<Key_part_spec> li(key->columns); Key_part_spec *column; uint total_length= 0; Field **f, *field; Create_field *cf; bool is_hash_key= false; while ((column= li++)) { if (column->length > table->file->max_key_part_length()) { is_hash_key= true; break; } else if (!column->length) { for (f= table->field; f && (field= *f); f++) { if (!my_strcasecmp(system_charset_info, field->field_name, column->field_name.str)) { if (field->max_display_length() > table->file->max_key_part_length()) { is_hash_key= true; goto exit; } total_length+= field->max_display_length(); } } /* Suppose query is like alter table t1 add column a blob unique; alter table t2 add column a blob,add column c blob, add unique key(a,b); In this case we have to add ALTER_ADD_CHECK_CONSTRAINT_FLAG We cant simply add ALTER_ADD_CHECK_CONSTRAINT flag because it is expensive. And there is no other way of doing this type of check. */ field_it.rewind(); while((cf= field_it++)) { if (!my_strcasecmp(system_charset_info, cf->field_name, column->field_name.str)) { if (cf->sql_type == MYSQL_TYPE_TINY_BLOB || cf->sql_type == MYSQL_TYPE_MEDIUM_BLOB || cf->sql_type == MYSQL_TYPE_LONG_BLOB || cf->sql_type == MYSQL_TYPE_BLOB) { is_hash_key= true; break; } if ((cf->sql_type == MYSQL_TYPE_VARCHAR || cf->sql_type == MYSQL_TYPE_VAR_STRING) && cf->length > table->file->max_key_part_length()) { is_hash_key= true; break; } total_length+= cf->length; } } } } exit: if (is_hash_key || total_length > table->file->max_key_length()) { alter_info->flags |= Alter_info::ALTER_ADD_CHECK_CONSTRAINT; alter_info->flags |= Alter_info::ALTER_ADD_COLUMN; } } new_key_list.push_back(key, thd->mem_root); if (key->name.str && !my_strcasecmp(system_charset_info, key->name.str, primary_key_name)) { my_error(ER_WRONG_NAME_FOR_INDEX, MYF(0), key->name.str); goto err; } } }

On Wed, Aug 31, 2016 at 8:49 PM, sachin setiya <sachinsetia1001@gmail.com> 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@mariadb.org