From 6cf9fd7cdb9d102ee1e12408f5a8e871d069618d Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 19 Aug 2004 15:00:55 +0500 Subject: ctype_utf8.test, ctype_utf8.result: Typo fix mysql-test/r/ctype_utf8.result: Typo fix mysql-test/t/ctype_utf8.test: Typo fix --- mysql-test/r/ctype_utf8.result | 4 ++-- mysql-test/t/ctype_utf8.test | 2 +- 2 files changed, 3 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 793e2ceff19..0cc3ea2cf17 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -362,13 +362,13 @@ c_a б drop table t1; create table t1 ( -c char(10) character set utf8 collate utf8_bin, +c char(10) character set utf8, unique key a using btree (c(1)) ) engine=heap; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( - `c` char(10) character set utf8 collate utf8_bin default NULL, + `c` char(10) character set utf8 default NULL, UNIQUE KEY `a` TYPE BTREE (`c`(1)) ) ENGINE=HEAP DEFAULT CHARSET=latin1 insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 808e04c56d8..0d3bec258bc 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -265,7 +265,7 @@ drop table t1; # Check HEAP+BTREE, case insensitive collation # create table t1 ( -c char(10) character set utf8 collate utf8_bin, +c char(10) character set utf8, unique key a using btree (c(1)) ) engine=heap; show create table t1; -- cgit v1.2.1 From 7fbc796d4ad9681fa2381791f8c895be4cbd738b Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 19 Aug 2004 15:15:10 +0500 Subject: Bug#4521: unique key prefix interacts poorly with utf8. Fix for binary collations for MyISAM and HEAP BTREE. This patch also changes trailing spaces behaviour for binary collations. Binary collations now have PAD characteristic too. --- mysql-test/r/binary.result | 2 + mysql-test/r/ctype_utf8.result | 92 +++++++++++++++++++++++++++++++++++ mysql-test/r/endspace.result | 2 +- mysql-test/r/myisam.result | 1 + mysql-test/t/ctype_utf8.test | 106 ++++++++++++++++++++++++++++++++++++++++- 5 files changed, 200 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/binary.result b/mysql-test/r/binary.result index 000c0c16d77..a4ced14bb12 100644 --- a/mysql-test/r/binary.result +++ b/mysql-test/r/binary.result @@ -59,8 +59,10 @@ concat("-",a,"-",b,"-") -hello-hello- select concat("-",a,"-",b,"-") from t1 where b="hello "; concat("-",a,"-",b,"-") +-hello-hello- select concat("-",a,"-",b,"-") from t1 ignore index (b) where b="hello "; concat("-",a,"-",b,"-") +-hello-hello- alter table t1 modify b tinytext not null, drop key b, add key (b(100)); select concat("-",a,"-",b,"-") from t1; concat("-",a,"-",b,"-") diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index 0cc3ea2cf17..cfad82fa053 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -397,3 +397,95 @@ select c as c_a from t1 where c='б'; c_a б drop table t1; +create table t1 (c varchar(30) character set utf8 collate utf8_bin, unique(c(10))); +insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); +insert into t1 values ('aaaaaaaaaa'); +insert into t1 values ('aaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaaa' for key 1 +insert into t1 values ('aaaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaaaa' for key 1 +insert into t1 values (repeat('b',20)); +select c c1 from t1 where c='1'; +c1 +1 +select c c2 from t1 where c='2'; +c2 +2 +select c c3 from t1 where c='3'; +c3 +3 +select c cx from t1 where c='x'; +cx +x +select c cy from t1 where c='y'; +cy +y +select c cz from t1 where c='z'; +cz +z +select c ca10 from t1 where c='aaaaaaaaaa'; +ca10 +aaaaaaaaaa +select c cb20 from t1 where c=repeat('b',20); +cb20 +bbbbbbbbbbbbbbbbbbbb +drop table t1; +create table t1 (c char(3) character set utf8 collate utf8_bin, unique (c(2))); +insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); +insert into t1 values ('a'); +insert into t1 values ('aa'); +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('b'); +insert into t1 values ('bb'); +insert into t1 values ('bbb'); +ERROR 23000: Duplicate entry 'bbb' for key 1 +insert into t1 values ('а'); +insert into t1 values ('аа'); +insert into t1 values ('ааа'); +ERROR 23000: Duplicate entry 'ааа' for key 1 +insert into t1 values ('б'); +insert into t1 values ('бб'); +insert into t1 values ('ббб'); +ERROR 23000: Duplicate entry 'ббб' for key 1 +insert into t1 values ('ꪪ'); +insert into t1 values ('ꪪꪪ'); +insert into t1 values ('ꪪꪪꪪ'); +ERROR 23000: Duplicate entry 'ꪪꪪ' for key 1 +drop table t1; +create table t1 ( +c char(10) character set utf8 collate utf8_bin, +unique key a using btree (c(1)) +) engine=heap; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) character set utf8 collate utf8_bin default NULL, + UNIQUE KEY `a` TYPE BTREE (`c`(1)) +) ENGINE=HEAP DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'aa' for key 1 +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('б'); +insert into t1 values ('бб'); +ERROR 23000: Duplicate entry 'бÐ' for key 1 +insert into t1 values ('ббб'); +ERROR 23000: Duplicate entry 'бÐ' for key 1 +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +б +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='б'; +c_a +б +drop table t1; diff --git a/mysql-test/r/endspace.result b/mysql-test/r/endspace.result index 4800bbf4ecb..167adea6674 100644 --- a/mysql-test/r/endspace.result +++ b/mysql-test/r/endspace.result @@ -19,7 +19,7 @@ select 'a a' > 'a', 'a \0' < 'a'; 1 1 select binary 'a a' > 'a', binary 'a \0' > 'a', binary 'a\0' > 'a'; binary 'a a' > 'a' binary 'a \0' > 'a' binary 'a\0' > 'a' -1 1 1 +1 0 0 create table t1 (text1 varchar(32) not NULL, KEY key1 (text1)); insert into t1 values ('teststring'), ('nothing'), ('teststring\t'); check table t1; diff --git a/mysql-test/r/myisam.result b/mysql-test/r/myisam.result index 354675cd4d4..0109097d3a1 100644 --- a/mysql-test/r/myisam.result +++ b/mysql-test/r/myisam.result @@ -412,6 +412,7 @@ aaa. aaa . select concat(a,'.') from t1 where binary a='aaa'; concat(a,'.') +aaa . aaa. update t1 set a='bbb' where a='aaa'; select concat(a,'.') from t1; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 0d3bec258bc..a8a02118269 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -189,7 +189,7 @@ drop table t2; # # Bug 4521: unique key prefix interacts poorly with utf8 -# Check keys with prefix compression +# MYISAM: keys with prefix compression, case insensitive collation. # create table t1 (c varchar(30) character set utf8, unique(c(10))); insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); @@ -211,7 +211,8 @@ drop table t1; # # Bug 4521: unique key prefix interacts poorly with utf8 -# Check fixed length keys +# MYISAM: fixed length keys, case insensitive collation +# create table t1 (c char(3) character set utf8, unique (c(2))); insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); insert into t1 values ('a'); @@ -283,3 +284,104 @@ select c as c_all from t1 order by c; select c as c_a from t1 where c='a'; select c as c_a from t1 where c='б'; drop table t1; + + +# +# Bug 4521: unique key prefix interacts poorly with utf8 +# MYISAM: keys with prefix compression, binary collation. +# +create table t1 (c varchar(30) character set utf8 collate utf8_bin, unique(c(10))); +insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); +insert into t1 values ('aaaaaaaaaa'); +--error 1062 +insert into t1 values ('aaaaaaaaaaa'); +--error 1062 +insert into t1 values ('aaaaaaaaaaaa'); +insert into t1 values (repeat('b',20)); +select c c1 from t1 where c='1'; +select c c2 from t1 where c='2'; +select c c3 from t1 where c='3'; +select c cx from t1 where c='x'; +select c cy from t1 where c='y'; +select c cz from t1 where c='z'; +select c ca10 from t1 where c='aaaaaaaaaa'; +select c cb20 from t1 where c=repeat('b',20); +drop table t1; + +# +# Bug 4521: unique key prefix interacts poorly with utf8 +# MYISAM: fixed length keys, binary collation +# +create table t1 (c char(3) character set utf8 collate utf8_bin, unique (c(2))); +insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); +insert into t1 values ('a'); +insert into t1 values ('aa'); +--error 1062 +insert into t1 values ('aaa'); +insert into t1 values ('b'); +insert into t1 values ('bb'); +--error 1062 +insert into t1 values ('bbb'); +insert into t1 values ('а'); +insert into t1 values ('аа'); +--error 1062 +insert into t1 values ('ааа'); +insert into t1 values ('б'); +insert into t1 values ('бб'); +--error 1062 +insert into t1 values ('ббб'); +insert into t1 values ('ꪪ'); +insert into t1 values ('ꪪꪪ'); +--error 1062 +insert into t1 values ('ꪪꪪꪪ'); +drop table t1; + +# +# Bug 4531: unique key prefix interacts poorly with utf8 +# Check HEAP+HASH, binary collation +# +# This doesn't work correctly yet. +# +#create table t1 ( +#c char(10) character set utf8 collate utf8_bin, +#unique key a using hash (c(1)) +#) engine=heap; +#show create table t1; +#insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +#--error 1062 +#insert into t1 values ('aa'); +#--error 1062 +#insert into t1 values ('aaa'); +#insert into t1 values ('б'); +#--error 1062 +#insert into t1 values ('бб'); +#--error 1062 +#insert into t1 values ('ббб'); +#select c as c_all from t1 order by c; +#select c as c_a from t1 where c='a'; +#select c as c_a from t1 where c='б'; +#drop table t1; + +# +# Bug 4531: unique key prefix interacts poorly with utf8 +# Check HEAP+BTREE, binary collation +# +create table t1 ( +c char(10) character set utf8 collate utf8_bin, +unique key a using btree (c(1)) +) engine=heap; +show create table t1; +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +--error 1062 +insert into t1 values ('aa'); +--error 1062 +insert into t1 values ('aaa'); +insert into t1 values ('б'); +--error 1062 +insert into t1 values ('бб'); +--error 1062 +insert into t1 values ('ббб'); +select c as c_all from t1 order by c; +select c as c_a from t1 where c='a'; +select c as c_a from t1 where c='б'; +drop table t1; -- cgit v1.2.1 From cba27e4a91d18439f421b3a33d27df8a2bfa04c1 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 19 Aug 2004 15:21:35 +0500 Subject: Fix trailing spaces behaviour for binary collation. --- mysql-test/r/ctype_utf8.result | 15 +++++++++++++++ mysql-test/t/ctype_utf8.test | 9 +++++++++ 2 files changed, 24 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index cfad82fa053..d00092a806f 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -79,6 +79,21 @@ SELECT 'a\t' < 'a'; SELECT 'a\t' < 'a '; 'a\t' < 'a ' 1 +SELECT 'a' = 'a ' collate utf8_bin; +'a' = 'a ' collate utf8_bin +1 +SELECT 'a\0' < 'a' collate utf8_bin; +'a\0' < 'a' collate utf8_bin +1 +SELECT 'a\0' < 'a ' collate utf8_bin; +'a\0' < 'a ' collate utf8_bin +1 +SELECT 'a\t' < 'a' collate utf8_bin; +'a\t' < 'a' collate utf8_bin +1 +SELECT 'a\t' < 'a ' collate utf8_bin; +'a\t' < 'a ' collate utf8_bin +1 CREATE TABLE t1 (a char(10) character set utf8 not null); INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a '); SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index a8a02118269..4c2898adae7 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -46,6 +46,15 @@ SELECT 'a\0' < 'a '; SELECT 'a\t' < 'a'; SELECT 'a\t' < 'a '; +# +# The same for binary collation +# +SELECT 'a' = 'a ' collate utf8_bin; +SELECT 'a\0' < 'a' collate utf8_bin; +SELECT 'a\0' < 'a ' collate utf8_bin; +SELECT 'a\t' < 'a' collate utf8_bin; +SELECT 'a\t' < 'a ' collate utf8_bin; + CREATE TABLE t1 (a char(10) character set utf8 not null); INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a '); SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1; -- cgit v1.2.1 From cc6d65b8d8227740aeba00a59e1de7c386967d52 Mon Sep 17 00:00:00 2001 From: unknown Date: Thu, 19 Aug 2004 16:07:18 +0500 Subject: Bug 4531: unique key prefix interacts poorly with utf8 Check HEAP+HASH, binary collation --- mysql-test/r/ctype_utf8.result | 36 ++++++++++++++++++++++++++++++++++++ mysql-test/t/ctype_utf8.test | 40 +++++++++++++++++++--------------------- 2 files changed, 55 insertions(+), 21 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/r/ctype_utf8.result b/mysql-test/r/ctype_utf8.result index d00092a806f..6c6e5114cf8 100644 --- a/mysql-test/r/ctype_utf8.result +++ b/mysql-test/r/ctype_utf8.result @@ -470,6 +470,42 @@ ERROR 23000: Duplicate entry 'ꪪꪪ' for key 1 drop table t1; create table t1 ( c char(10) character set utf8 collate utf8_bin, +unique key a using hash (c(1)) +) engine=heap; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) character set utf8 collate utf8_bin default NULL, + UNIQUE KEY `a` (`c`(1)) +) ENGINE=HEAP DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'aa' for key 1 +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aaa' for key 1 +insert into t1 values ('б'); +insert into t1 values ('бб'); +ERROR 23000: Duplicate entry 'бÐ' for key 1 +insert into t1 values ('ббб'); +ERROR 23000: Duplicate entry 'бÐ' for key 1 +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +б +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='б'; +c_a +б +drop table t1; +create table t1 ( +c char(10) character set utf8 collate utf8_bin, unique key a using btree (c(1)) ) engine=heap; show create table t1; diff --git a/mysql-test/t/ctype_utf8.test b/mysql-test/t/ctype_utf8.test index 4c2898adae7..21880732e47 100644 --- a/mysql-test/t/ctype_utf8.test +++ b/mysql-test/t/ctype_utf8.test @@ -349,27 +349,25 @@ drop table t1; # Bug 4531: unique key prefix interacts poorly with utf8 # Check HEAP+HASH, binary collation # -# This doesn't work correctly yet. -# -#create table t1 ( -#c char(10) character set utf8 collate utf8_bin, -#unique key a using hash (c(1)) -#) engine=heap; -#show create table t1; -#insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); -#--error 1062 -#insert into t1 values ('aa'); -#--error 1062 -#insert into t1 values ('aaa'); -#insert into t1 values ('б'); -#--error 1062 -#insert into t1 values ('бб'); -#--error 1062 -#insert into t1 values ('ббб'); -#select c as c_all from t1 order by c; -#select c as c_a from t1 where c='a'; -#select c as c_a from t1 where c='б'; -#drop table t1; +create table t1 ( +c char(10) character set utf8 collate utf8_bin, +unique key a using hash (c(1)) +) engine=heap; +show create table t1; +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +--error 1062 +insert into t1 values ('aa'); +--error 1062 +insert into t1 values ('aaa'); +insert into t1 values ('б'); +--error 1062 +insert into t1 values ('бб'); +--error 1062 +insert into t1 values ('ббб'); +select c as c_all from t1 order by c; +select c as c_a from t1 where c='a'; +select c as c_a from t1 where c='б'; +drop table t1; # # Bug 4531: unique key prefix interacts poorly with utf8 -- cgit v1.2.1