Debian Codegraph @

mysql-test/suite/innodb/t/innodb-ucs2.test

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