Merge mskold@build.mysql.com:/home/bk/mysql-4.1

into mysql.com:/usr/local/home/marty/MySQL/mysql-4.1-ndb

5 files changed, 258 insertions, 3 deletions

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 793e2ceff19..6c6e5114cf8 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;
@@ -362,6 +377,134 @@ c_a
 б
 drop table t1;
 create table t1 (
+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 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;
+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 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;
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 808e04c56d8..21880732e47 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;
@@ -189,7 +198,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 +220,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');
@@ -265,6 +275,105 @@ drop table t1;
 # Check HEAP+BTREE, case insensitive collation
 #
 create table t1 (
+c char(10) character set utf8,
+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;
+
+
+#
+# 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
+#
+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;