1# 2# Tests from mix2.inc which require ucs2 character sets should go here 3# 4 5# 6# BUG 14056 Column prefix index on UTF-8 primary key column causes: Can't find record.. 7# 8 9eval create table t1 ( 10 a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2)) 11) character set utf8 engine = $engine_type; 12eval create table t2 ( 13 a int, b char(10), c char(10), filler char(10), primary key(a, b(2)), unique key (a, c(2)) 14) character set ucs2 engine = $engine_type; 15insert into t1 values (1,'abcdefg','abcdefg','one'); 16insert into t1 values (2,'ijkilmn','ijkilmn','two'); 17insert into t1 values (3,'qrstuvw','qrstuvw','three'); 18insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); 19insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); 20insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six'); 21insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven'); 22insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); 23insert into t2 values (1,'abcdefg','abcdefg','one'); 24insert into t2 values (2,'ijkilmn','ijkilmn','two'); 25insert into t2 values (3,'qrstuvw','qrstuvw','three'); 26insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); 27insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); 28insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); 29insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); 30insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); 31insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); 32insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven'); 33insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point'); 34insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); 35update t1 set filler = 'boo' where a = 1; 36update t2 set filler ='email' where a = 4; 37select a,hex(b),hex(c),filler from t1 order by filler; 38select a,hex(b),hex(c),filler from t2 order by filler; 39drop table t1; 40drop table t2; 41 42eval create table t1 ( 43 a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2)) 44) character set utf8 engine = $engine_type; 45eval create table t2 ( 46 a int, b varchar(10), c varchar(10), filler varchar(10), primary key(a, b(2)), unique key (a, c(2)) 47) character set ucs2 engine = $engine_type; 48insert into t1 values (1,'abcdefg','abcdefg','one'); 49insert into t1 values (2,'ijkilmn','ijkilmn','two'); 50insert into t1 values (3,'qrstuvw','qrstuvw','three'); 51insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); 52insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); 53insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six'); 54insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven'); 55insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); 56insert into t2 values (1,'abcdefg','abcdefg','one'); 57insert into t2 values (2,'ijkilmn','ijkilmn','two'); 58insert into t2 values (3,'qrstuvw','qrstuvw','three'); 59 60 61insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); 62insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); 63insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); 64insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); 65insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); 66insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); 67insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven'); 68insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point'); 69insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); 70update t1 set filler = 'boo' where a = 1; 71update t2 set filler ='email' where a = 4; 72select a,hex(b),hex(c),filler from t1 order by filler; 73select a,hex(b),hex(c),filler from t2 order by filler; 74drop table t1; 75drop table t2; 76 77eval create table t1 ( 78 a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2)) 79) character set utf8 engine = $engine_type; 80eval create table t2 ( 81 a int, b text(10), c text(10), filler text(10), primary key(a, b(2)), unique key (a, c(2)) 82) character set ucs2 engine = $engine_type; 83insert into t1 values (1,'abcdefg','abcdefg','one'); 84insert into t1 values (2,'ijkilmn','ijkilmn','two'); 85insert into t1 values (3,'qrstuvw','qrstuvw','three'); 86insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); 87insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); 88insert into t1 values (4,_utf8 0xe880bde880bd,_utf8 0xe880bde880bd,'six'); 89insert into t1 values (4,_utf8 0xe880bdD0B1e880bd,_utf8 0xe880bdD0B1e880bd,'seven'); 90insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); 91insert into t2 values (1,'abcdefg','abcdefg','one'); 92insert into t2 values (2,'ijkilmn','ijkilmn','two'); 93insert into t2 values (3,'qrstuvw','qrstuvw','three'); 94insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); 95insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); 96insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); 97insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); 98insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); 99insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); 100insert into t2 values (4,_ucs2 0x05630563,_ucs2 0x05630563,'eleven'); 101insert into t2 values (4,_ucs2 0x0563001fc0563,_ucs2 0x0563001fc0563,'point'); 102insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); 103update t1 set filler = 'boo' where a = 1; 104update t2 set filler ='email' where a = 4; 105select a,hex(b),hex(c),filler from t1 order by filler; 106select a,hex(b),hex(c),filler from t2 order by filler; 107drop table t1; 108drop table t2; 109 110eval create table t1 ( 111 a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2)) 112) character set utf8 engine = $engine_type; 113eval create table t2 ( 114 a int, b blob(10), c blob(10), filler blob(10), primary key(a, b(2)), unique key (a, c(2)) 115) character set ucs2 engine = $engine_type; 116insert into t1 values (1,'abcdefg','abcdefg','one'); 117insert into t1 values (2,'ijkilmn','ijkilmn','two'); 118insert into t1 values (3,'qrstuvw','qrstuvw','three'); 119insert into t1 values (4,_utf8 0xe880bd,_utf8 0xe880bd,'four'); 120insert into t1 values (4,_utf8 0x5b,_utf8 0x5b,'five'); 121insert into t1 values (4,_utf8 0xD0B1,_utf8 0xD0B1,'eight'); 122insert into t2 values (1,'abcdefg','abcdefg','one'); 123insert into t2 values (2,'ijkilmn','ijkilmn','two'); 124insert into t2 values (3,'qrstuvw','qrstuvw','three'); 125insert into t2 values (4,_ucs2 0x00e400,_ucs2 0x00e400,'four'); 126insert into t2 values (4,_ucs2 0x00640065,_ucs2 0x00640065,'five'); 127insert into t2 values (4,_ucs2 0x00e400e50068,_ucs2 0x00e400e50068,'six'); 128insert into t2 values (4,_ucs2 0x01fc,_ucs2 0x01fc,'seven'); 129insert into t2 values (4,_ucs2 0x0120,_ucs2 0x0120,'eight'); 130insert into t2 values (4,_ucs2 0x0563,_ucs2 0x0563,'ten'); 131insert into t2 values (4,_ucs2 0x05612020,_ucs2 0x05612020,'taken'); 132update t1 set filler = 'boo' where a = 1; 133update t2 set filler ='email' where a = 4; 134select a,hex(b),hex(c),filler from t1 order by filler; 135select a,hex(b),hex(c),filler from t2 order by filler; 136drop table t1; 137drop table t2; 138commit; 139 140# 141# Test cases for bug #15308 Problem of Order with Enum Column in Primary Key 142# 143eval CREATE TABLE t1 ( 144 ind enum('0','1','2') NOT NULL default '0', 145 string1 varchar(250) NOT NULL, 146 PRIMARY KEY (ind) 147) ENGINE=$engine_type DEFAULT CHARSET=utf8; 148eval CREATE TABLE t2 ( 149 ind enum('0','1','2') NOT NULL default '0', 150 string1 varchar(250) NOT NULL, 151 PRIMARY KEY (ind) 152) ENGINE=$engine_type DEFAULT CHARSET=ucs2; 153 154INSERT INTO t1 VALUES ('1', ''),('2', ''); 155INSERT INTO t2 VALUES ('1', ''),('2', ''); 156SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1; 157SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1; 158drop table t1,t2; 159 160eval CREATE TABLE t1 ( 161 ind set('0','1','2') NOT NULL default '0', 162 string1 varchar(250) NOT NULL, 163 PRIMARY KEY (ind) 164) ENGINE=$engine_type DEFAULT CHARSET=utf8; 165eval CREATE TABLE t2 ( 166 ind set('0','1','2') NOT NULL default '0', 167 string1 varchar(250) NOT NULL, 168 PRIMARY KEY (ind) 169) ENGINE=$engine_type DEFAULT CHARSET=ucs2; 170 171INSERT INTO t1 VALUES ('1', ''),('2', ''); 172INSERT INTO t2 VALUES ('1', ''),('2', ''); 173SELECT hex(ind),hex(string1) FROM t1 ORDER BY string1; 174SELECT hex(ind),hex(string1) FROM t2 ORDER BY string1; 175drop table t1,t2; 176 177eval CREATE TABLE t1 ( 178 ind bit not null, 179 string1 varchar(250) NOT NULL, 180 PRIMARY KEY (ind) 181) ENGINE=$engine_type DEFAULT CHARSET=utf8; 182eval CREATE TABLE t2 ( 183 ind bit not null, 184 string1 varchar(250) NOT NULL, 185 PRIMARY KEY (ind) 186) ENGINE=$engine_type DEFAULT CHARSET=ucs2; 187insert into t1 values(0,''),(1,''); 188insert into t2 values(0,''),(1,''); 189select hex(ind),hex(string1) from t1 order by string1; 190select hex(ind),hex(string1) from t2 order by string1; 191drop table t1,t2; 192 193# tests for bug #14056 Column prefix index on UTF-8 primary key column causes 'Can't find record..' 194 195eval create table t2 ( 196 a int, b char(10), filler char(10), primary key(a, b(2)) 197) character set utf8 engine = $engine_type; 198 199insert into t2 values (1,'abcdefg','one'); 200insert into t2 values (2,'ijkilmn','two'); 201insert into t2 values (3, 'qrstuvw','three'); 202update t2 set a=5, filler='booo' where a=1; 203drop table t2; 204eval create table t2 ( 205 a int, b char(10), filler char(10), primary key(a, b(2)) 206) character set ucs2 engine = $engine_type; 207 208insert into t2 values (1,'abcdefg','one'); 209insert into t2 values (2,'ijkilmn','two'); 210insert into t2 values (3, 'qrstuvw','three'); 211update t2 set a=5, filler='booo' where a=1; 212drop table t2; 213 214eval create table t1(a int not null, b char(110),primary key(a,b(100))) engine=$engine_type default charset=utf8; 215insert into t1 values(1,'abcdefg'),(2,'defghijk'); 216insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1); 217insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2); 218select a,hex(b) from t1 order by b; 219update t1 set b = 'three' where a = 6; 220drop table t1; 221eval create table t1(a int not null, b text(110),primary key(a,b(100))) engine=$engine_type default charset=utf8; 222insert into t1 values(1,'abcdefg'),(2,'defghijk'); 223insert into t1 values(6,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1); 224insert into t1 values(7,_utf8 0xD0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B1D0B2); 225select a,hex(b) from t1 order by b; 226update t1 set b = 'three' where a = 6; 227drop table t1; 228 229