Debian Codegraph @

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

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