From 62d2f051df5e50d623c7d30ffa51f54cd3b12ea4 Mon Sep 17 00:00:00 2001 From: Sachin Date: Wed, 9 Jan 2019 11:43:37 +0530 Subject: Final Commit --- mysql-test/main/long_unique.result | 216 +++++++++++++++++++------------------ mysql-test/main/long_unique.test | 10 +- 2 files changed, 120 insertions(+), 106 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/long_unique.result b/mysql-test/main/long_unique.result index 47d3ec988ba..1b52c4cbb51 100644 --- a/mysql-test/main/long_unique.result +++ b/mysql-test/main/long_unique.result @@ -147,7 +147,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` blob DEFAULT NULL, `b` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`) + UNIQUE KEY `a` (`a`(65535)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(1,2); ERROR 23000: Duplicate entry '1' for key 'a' @@ -174,7 +174,7 @@ Table Create Table t1 CREATE TABLE `t1` ( `a` blob DEFAULT NULL, `c` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`) + UNIQUE KEY `a` (`a`(65535)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 #now add some column with name db_row_hash; alter table t1 add column db_row_hash_1 int unique; @@ -184,7 +184,7 @@ t1 CREATE TABLE `t1` ( `a` blob DEFAULT NULL, `c` int(11) DEFAULT NULL, `db_row_hash_1` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`), + UNIQUE KEY `a` (`a`(65535)), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values(45,1,55),(46,1,55); @@ -454,9 +454,9 @@ t1 CREATE TABLE `t1` ( `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 `a` (`a`(65535)), + UNIQUE KEY `c` (`c`(65535)), + UNIQUE KEY `d` (`d`(65535)), UNIQUE KEY `e` (`e`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 #unique key should not break; @@ -487,9 +487,9 @@ t1 CREATE TABLE `t1` ( `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 `a` (`a`(65535)), + UNIQUE KEY `c` (`c`(65535)), + UNIQUE KEY `d` (`d`(65535)), UNIQUE KEY `e` (`e`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 #now add some column with name db_row_hash; @@ -508,9 +508,9 @@ t1 CREATE TABLE `t1` ( `db_row_hash_1` int(11) DEFAULT NULL, `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_3` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`), - UNIQUE KEY `c` (`c`), - UNIQUE KEY `d` (`d`), + UNIQUE KEY `a` (`a`(65535)), + UNIQUE KEY `c` (`c`(65535)), + UNIQUE KEY `d` (`d`(65535)), UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), @@ -544,14 +544,14 @@ t1 CREATE TABLE `t1` ( `db_row_hash_1` int(11) DEFAULT NULL, `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_5` int(11) DEFAULT NULL, - UNIQUE KEY `d` (`d`), + UNIQUE KEY `d` (`d`(65535)), UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 d 1 d A NULL 65535 NULL YES HASH t1 0 e 1 e A NULL NULL NULL YES HASH 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 @@ -568,7 +568,7 @@ t1 CREATE TABLE `t1` ( `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_5` int(11) DEFAULT NULL, `a` blob DEFAULT NULL, - UNIQUE KEY `d` (`d`), + UNIQUE KEY `d` (`d`(65535)), UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), @@ -576,7 +576,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 d 1 d A NULL 65535 NULL YES HASH t1 0 e 1 e A NULL NULL NULL YES HASH 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 @@ -595,7 +595,7 @@ t1 CREATE TABLE `t1` ( `db_row_hash_2` int(11) DEFAULT NULL, `db_row_hash_5` int(11) DEFAULT NULL, `a` int(11) DEFAULT NULL, - UNIQUE KEY `d` (`d`), + UNIQUE KEY `d` (`d`(65535)), UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), @@ -603,7 +603,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 d 1 d A NULL 65535 NULL YES HASH t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE @@ -624,7 +624,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `clm_changed1` blob DEFAULT NULL, `clm_changed2` blob DEFAULT NULL, - UNIQUE KEY `d` (`d`), + UNIQUE KEY `d` (`d`(65535)), UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), @@ -634,7 +634,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 d 1 d A NULL 65535 NULL YES HASH t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE @@ -656,7 +656,7 @@ t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL, `clm_changed1` blob DEFAULT NULL, `clm_changed2` blob DEFAULT NULL, - UNIQUE KEY `d` (`d`), + UNIQUE KEY `d` (`d`(65535)), UNIQUE KEY `e` (`e`), UNIQUE KEY `db_row_hash_1` (`db_row_hash_1`), UNIQUE KEY `db_row_hash_2` (`db_row_hash_2`), @@ -664,7 +664,7 @@ t1 CREATE TABLE `t1` ( ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 d 1 d A NULL NULL NULL YES HASH +t1 0 d 1 d A NULL 65535 NULL YES HASH t1 0 e 1 e A NULL NULL NULL YES BTREE t1 0 db_row_hash_1 1 db_row_hash_1 A NULL NULL NULL YES BTREE t1 0 db_row_hash_2 1 db_row_hash_2 A NULL NULL NULL YES BTREE @@ -854,27 +854,27 @@ t1 CREATE TABLE `t1` ( `db_row_hash_5` int(11) DEFAULT NULL, `db_row_hash_1` int(11) DEFAULT NULL, `db_row_hash_2` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`,`b`,`c`), - UNIQUE KEY `c` (`c`,`d`,`e`), - UNIQUE KEY `e` (`e`,`f`,`g`,`h`), - UNIQUE KEY `b` (`b`,`d`,`g`,`h`) + UNIQUE KEY `a` (`a`(65535),`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `e` (`e`,`f`(65535),`g`,`h`(65535)), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH +t1 0 a 1 a A NULL 65535 NULL YES HASH t1 0 a 2 b A NULL NULL NULL YES HASH t1 0 a 3 c A NULL NULL NULL YES HASH t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A 0 NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH t1 0 c 3 e A 0 NULL NULL YES HASH t1 0 e 1 e A 0 NULL NULL YES HASH -t1 0 e 2 f A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH t1 0 e 3 g A 0 NULL NULL YES HASH -t1 0 e 4 h A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH t1 0 b 1 b A 0 NULL NULL YES HASH -t1 0 b 2 d A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH t1 0 b 3 g A 0 NULL NULL YES HASH -t1 0 b 4 h A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH 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; @@ -888,27 +888,27 @@ t1 CREATE TABLE `t1` ( `f` longblob DEFAULT NULL, `g` int(11) DEFAULT NULL, `h` text DEFAULT NULL, - UNIQUE KEY `a` (`a`,`b`,`c`), - UNIQUE KEY `c` (`c`,`d`,`e`), - UNIQUE KEY `e` (`e`,`f`,`g`,`h`), - UNIQUE KEY `b` (`b`,`d`,`g`,`h`) + UNIQUE KEY `a` (`a`(65535),`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(65535),`g`,`h`(65535)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH +t1 0 a 1 a A NULL 65535 NULL YES HASH t1 0 a 2 b A NULL NULL NULL YES HASH t1 0 a 3 c A NULL NULL NULL YES HASH t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A 0 NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH t1 0 c 3 e A 0 NULL NULL YES HASH -t1 0 e 1 e A 0 NULL NULL YES HASH -t1 0 e 2 f A 0 NULL NULL YES HASH -t1 0 e 3 g A 0 NULL NULL YES HASH -t1 0 e 4 h A 0 NULL NULL YES HASH t1 0 b 1 b A 0 NULL NULL YES HASH -t1 0 b 2 d A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH t1 0 b 3 g A 0 NULL NULL YES HASH -t1 0 b 4 h A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH #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; @@ -922,27 +922,27 @@ t1 CREATE TABLE `t1` ( `f` longblob DEFAULT NULL, `g` int(11) DEFAULT NULL, `h` text DEFAULT NULL, - UNIQUE KEY `a` (`aa`,`bb`,`c`), - UNIQUE KEY `c` (`c`,`dd`,`e`), - UNIQUE KEY `e` (`e`,`f`,`g`,`h`), - UNIQUE KEY `b` (`bb`,`dd`,`g`,`h`) + UNIQUE KEY `a` (`aa`(65535),`bb`,`c`), + UNIQUE KEY `c` (`c`,`dd`(65535),`e`), + UNIQUE KEY `b` (`bb`,`dd`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(65535),`g`,`h`(65535)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 aa A NULL NULL NULL YES HASH +t1 0 a 1 aa A NULL 65535 NULL YES HASH t1 0 a 2 bb A NULL NULL NULL YES HASH t1 0 a 3 c A NULL NULL NULL YES HASH t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 dd A 0 NULL NULL YES HASH +t1 0 c 2 dd A 0 65535 NULL YES HASH t1 0 c 3 e A 0 NULL NULL YES HASH -t1 0 e 1 e A 0 NULL NULL YES HASH -t1 0 e 2 f A 0 NULL NULL YES HASH -t1 0 e 3 g A 0 NULL NULL YES HASH -t1 0 e 4 h A 0 NULL NULL YES HASH t1 0 b 1 bb A 0 NULL NULL YES HASH -t1 0 b 2 dd A 0 NULL NULL YES HASH +t1 0 b 2 dd A 0 65535 NULL YES HASH t1 0 b 3 g A 0 NULL NULL YES HASH -t1 0 b 4 h A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH 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 @@ -955,27 +955,27 @@ t1 CREATE TABLE `t1` ( `f` longblob DEFAULT NULL, `g` int(11) DEFAULT NULL, `h` text DEFAULT NULL, - UNIQUE KEY `a` (`a`,`b`,`c`), - UNIQUE KEY `c` (`c`,`d`,`e`), - UNIQUE KEY `e` (`e`,`f`,`g`,`h`), - UNIQUE KEY `b` (`b`,`d`,`g`,`h`) + UNIQUE KEY `a` (`a`(65535),`b`,`c`), + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(65535),`g`,`h`(65535)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH +t1 0 a 1 a A NULL 65535 NULL YES HASH t1 0 a 2 b A NULL NULL NULL YES HASH t1 0 a 3 c A NULL NULL NULL YES HASH t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A 0 NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH t1 0 c 3 e A 0 NULL NULL YES HASH -t1 0 e 1 e A 0 NULL NULL YES HASH -t1 0 e 2 f A 0 NULL NULL YES HASH -t1 0 e 3 g A 0 NULL NULL YES HASH -t1 0 e 4 h A 0 NULL NULL YES HASH t1 0 b 1 b A 0 NULL NULL YES HASH -t1 0 b 2 d A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH t1 0 b 3 g A 0 NULL NULL YES HASH -t1 0 b 4 h A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH #now we will change the data type to int and varchar limit so that we no longer require hash_index; #on key a_b_c; alter table t1 modify column a varchar(20) , modify column b varchar(20) , modify column c varchar(20); @@ -991,9 +991,9 @@ t1 CREATE TABLE `t1` ( `g` int(11) DEFAULT NULL, `h` text DEFAULT NULL, UNIQUE KEY `a` (`a`,`b`,`c`), - UNIQUE KEY `c` (`c`,`d`,`e`), - UNIQUE KEY `e` (`e`,`f`,`g`,`h`), - UNIQUE KEY `b` (`b`,`d`,`g`,`h`) + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(65535),`g`,`h`(65535)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment @@ -1001,16 +1001,16 @@ t1 0 a 1 a A NULL NULL NULL YES BTREE t1 0 a 2 b A NULL NULL NULL YES BTREE t1 0 a 3 c A NULL NULL NULL YES BTREE t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A NULL NULL NULL YES HASH +t1 0 c 2 d A NULL 65535 NULL YES HASH t1 0 c 3 e A NULL NULL NULL YES HASH -t1 0 e 1 e A 0 NULL NULL YES HASH -t1 0 e 2 f A 0 NULL NULL YES HASH -t1 0 e 3 g A 0 NULL NULL YES HASH -t1 0 e 4 h A 0 NULL NULL YES HASH t1 0 b 1 b A 0 NULL NULL YES HASH -t1 0 b 2 d A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH t1 0 b 3 g A 0 NULL NULL YES HASH -t1 0 b 4 h A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH #change it back; alter table t1 modify column a blob , modify column b blob , modify column c blob; show create table t1; @@ -1025,9 +1025,9 @@ t1 CREATE TABLE `t1` ( `g` int(11) DEFAULT NULL, `h` text DEFAULT NULL, UNIQUE KEY `a` (`a`,`b`,`c`), - UNIQUE KEY `c` (`c`,`d`,`e`), - UNIQUE KEY `e` (`e`,`f`,`g`,`h`), - UNIQUE KEY `b` (`b`,`d`,`g`,`h`) + UNIQUE KEY `c` (`c`,`d`(65535),`e`), + UNIQUE KEY `b` (`b`,`d`(65535),`g`,`h`(65535)), + UNIQUE KEY `e` (`e`,`f`(65535),`g`,`h`(65535)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment @@ -1035,16 +1035,16 @@ t1 0 a 1 a A NULL NULL NULL YES HASH t1 0 a 2 b A NULL NULL NULL YES HASH t1 0 a 3 c A NULL NULL NULL YES HASH t1 0 c 1 c A NULL NULL NULL YES HASH -t1 0 c 2 d A 0 NULL NULL YES HASH +t1 0 c 2 d A 0 65535 NULL YES HASH t1 0 c 3 e A 0 NULL NULL YES HASH -t1 0 e 1 e A 0 NULL NULL YES HASH -t1 0 e 2 f A 0 NULL NULL YES HASH -t1 0 e 3 g A 0 NULL NULL YES HASH -t1 0 e 4 h A 0 NULL NULL YES HASH t1 0 b 1 b A 0 NULL NULL YES HASH -t1 0 b 2 d A 0 NULL NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH t1 0 b 3 g A 0 NULL NULL YES HASH -t1 0 b 4 h A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH +t1 0 e 1 e A 0 NULL NULL YES HASH +t1 0 e 2 f A 0 65535 NULL YES HASH +t1 0 e 3 g A 0 NULL NULL YES HASH +t1 0 e 4 h A 0 65535 NULL YES HASH #try to delete blob column in unique; truncate table t1; #now try to delete keys; @@ -1060,18 +1060,18 @@ t1 CREATE TABLE `t1` ( `f` longblob DEFAULT NULL, `g` int(11) DEFAULT NULL, `h` text DEFAULT NULL, - UNIQUE KEY `a` (`a`,`b`,`c`), - UNIQUE KEY `b` (`b`,`d`,`g`,`h`) + UNIQUE KEY `a` (`a`(65535),`b`(65535),`c`(65535)), + UNIQUE KEY `b` (`b`(65535),`d`(65535),`g`,`h`(65535)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 b A NULL NULL NULL YES HASH -t1 0 a 3 c A 0 NULL NULL YES HASH -t1 0 b 1 b A 0 NULL NULL YES HASH -t1 0 b 2 d A 0 NULL NULL YES HASH +t1 0 a 1 a A NULL 65535 NULL YES HASH +t1 0 a 2 b A NULL 65535 NULL YES HASH +t1 0 a 3 c A 0 65535 NULL YES HASH +t1 0 b 1 b A 0 65535 NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH t1 0 b 3 g A 0 NULL NULL YES HASH -t1 0 b 4 h A 0 NULL NULL YES HASH +t1 0 b 4 h A 0 65535 NULL YES HASH drop table t1; #now alter table containing some data basically some tests with ignore; create table t1 (a blob); @@ -1095,11 +1095,11 @@ show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` blob DEFAULT NULL, - UNIQUE KEY `a` (`a`) + UNIQUE KEY `a` (`a`(65535)) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH +t1 0 a 1 a A NULL 65535 NULL YES HASH drop table t1; #Now with multiple keys; create table t1(a blob , b blob, c blob , d blob , e int); @@ -1131,16 +1131,16 @@ t1 CREATE TABLE `t1` ( `c` blob DEFAULT NULL, `d` blob DEFAULT NULL, `e` int(11) DEFAULT NULL, - UNIQUE KEY `a` (`a`,`c`), - UNIQUE KEY `b` (`b`,`d`), + UNIQUE KEY `a` (`a`(65535),`c`(65535)), + UNIQUE KEY `b` (`b`(65535),`d`(65535)), UNIQUE KEY `e` (`e`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 show keys from t1; Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment -t1 0 a 1 a A NULL NULL NULL YES HASH -t1 0 a 2 c A NULL NULL NULL YES HASH -t1 0 b 1 b A NULL NULL NULL YES HASH -t1 0 b 2 d A 0 NULL NULL YES HASH +t1 0 a 1 a A NULL 65535 NULL YES HASH +t1 0 a 2 c A NULL 65535 NULL YES HASH +t1 0 b 1 b A NULL 65535 NULL YES HASH +t1 0 b 2 d A 0 65535 NULL YES HASH t1 0 e 1 e A 0 NULL NULL YES BTREE drop table t1; #visibility of db_row_hash @@ -1284,7 +1284,7 @@ ERROR 23000: Duplicate entry 'ssssssssssssssssssssssssssssssssssssssssssssssssss insert into t1 value(concat(repeat('a',3000),'2')); drop table t1; create table t1(a varchar(4000), b longblob , c varchar(5000), d longblob, -unique(a(3500), b(1000000)), unique(c(4500), d(10000000))); +unique(a(3500), b), unique(c(4500), d)); desc t1; Field Type Null Key Default Extra a varchar(4000) YES MUL NULL @@ -1383,4 +1383,10 @@ insert into t1 values (2, 1, 3); insert into t1 values (2, 1, 3); ERROR 23000: Duplicate entry '2' for key 'a' drop table t1; +#partition +create table t1(a blob unique) partition by hash(a); +ERROR HY000: A BLOB field is not allowed in partition function +#key length > 2^15 -1 +create table t1(a blob, unique(a(100001))); +ERROR 42000: Specified key was too long; max key length is 1000 bytes set @@GLOBAL.max_allowed_packet= @allowed_packet; diff --git a/mysql-test/main/long_unique.test b/mysql-test/main/long_unique.test index 93c70eac80e..fe76cefbfad 100644 --- a/mysql-test/main/long_unique.test +++ b/mysql-test/main/long_unique.test @@ -427,7 +427,7 @@ insert into t1 value(concat(repeat('a',3000),'2')); drop table t1; create table t1(a varchar(4000), b longblob , c varchar(5000), d longblob, -unique(a(3500), b(1000000)), unique(c(4500), d(10000000))); +unique(a(3500), b), unique(c(4500), d)); desc t1; show create table t1; show keys from t1; @@ -471,4 +471,12 @@ insert into t1 values (2, 1, 3); --error ER_DUP_ENTRY insert into t1 values (2, 1, 3); drop table t1; + +--echo #partition +--error ER_BLOB_FIELD_IN_PART_FUNC_ERROR +create table t1(a blob unique) partition by hash(a); +--echo #key length > 2^15 -1 +--error ER_TOO_LONG_KEY +create table t1(a blob, unique(a(100001))); + set @@GLOBAL.max_allowed_packet= @allowed_packet; -- cgit v1.2.1