1
by Norbert Tretkowski
Import upstream version 5.1.45 |
1 |
drop table if exists t1; |
2 |
SET NAMES cp1251; |
|
3 |
create table t1 (a varchar(10) not null) character set cp1251; |
|
4 |
insert into t1 values ("a"),("ab"),("abc"); |
|
5 |
select * from t1; |
|
6 |
a
|
|
7 |
a
|
|
8 |
ab
|
|
9 |
abc
|
|
10 |
select a, left(a,1) as b from t1; |
|
11 |
a b |
|
12 |
a a |
|
13 |
ab a |
|
14 |
abc a |
|
15 |
select a, left(a,1) as b from t1 group by a; |
|
16 |
a b |
|
17 |
a a |
|
18 |
ab a |
|
19 |
abc a |
|
20 |
SELECT DISTINCT RIGHT(a,1) from t1; |
|
21 |
RIGHT(a,1) |
|
22 |
a
|
|
23 |
b
|
|
24 |
c
|
|
25 |
drop table t1; |
|
26 |
create table t1 (a char(3) binary, b binary(3)) character set cp1251; |
|
27 |
insert into t1 values ('aaa','bbb'),('AAA','BBB'); |
|
28 |
select upper(a),upper(b) from t1; |
|
29 |
upper(a) upper(b) |
|
30 |
AAA bbb |
|
31 |
AAA BBB |
|
32 |
select lower(a),lower(b) from t1; |
|
33 |
lower(a) lower(b) |
|
34 |
aaa bbb |
|
35 |
aaa BBB |
|
36 |
select * from t1 where upper(a)='AAA'; |
|
37 |
a b |
|
38 |
aaa bbb |
|
39 |
AAA BBB |
|
40 |
select * from t1 where lower(a)='aaa'; |
|
41 |
a b |
|
42 |
aaa bbb |
|
43 |
AAA BBB |
|
44 |
select * from t1 where upper(b)='BBB'; |
|
45 |
a b |
|
46 |
AAA BBB |
|
47 |
select * from t1 where lower(b)='bbb'; |
|
48 |
a b |
|
49 |
aaa bbb |
|
50 |
select charset(a), charset(b), charset(binary 'ccc') from t1 limit 1; |
|
51 |
charset(a) charset(b) charset(binary 'ccc') |
|
52 |
cp1251 binary binary |
|
53 |
select collation(a), collation(b), collation(binary 'ccc') from t1 limit 1; |
|
54 |
collation(a) collation(b) collation(binary 'ccc') |
|
55 |
cp1251_bin binary binary |
|
56 |
drop table t1; |
|
57 |
create table t1 ( |
|
58 |
a varchar(16) character set cp1251 collate cp1251_bin not null, |
|
59 |
b int(10) default null, |
|
60 |
primary key(a) |
|
61 |
) charset=cp1251; |
|
62 |
insert into t1 (a) values ('air'), |
|
63 |
('we'),('g'),('we_toshko'), ('s0urce'),('we_ivo'),('we_iliyan'), |
|
64 |
('we_martin'),('vw_grado'),('vw_vasko'),('tn_vili'),('tn_kalina'), |
|
65 |
('tn_fakira'),('vw_silvia'),('vw_starshi'),('vw_geo'),('vw_b0x1'); |
|
66 |
select * from t1 where a like 'we_%'; |
|
67 |
a b |
|
68 |
we_iliyan NULL |
|
69 |
we_ivo NULL |
|
70 |
we_martin NULL |
|
71 |
we_toshko NULL |
|
72 |
drop table t1; |
|
1.4.1
by Marc Deslauriers
Import upstream version 5.1.61 |
73 |
#
|
74 |
# Start of 5.1 tests
|
|
75 |
#
|
|
76 |
SELECT @@collation_connection; |
|
77 |
@@collation_connection |
|
78 |
cp1251_general_ci
|
|
79 |
CREATE TABLE t1 AS SELECT ' ' AS a LIMIT 0; |
|
80 |
INSERT INTO t1 VALUES (0x00),(0x01),(0x02),(0x03),(0x04),(0x05),(0x06),(0x07); |
|
81 |
INSERT INTO t1 VALUES (0x08),(0x09),(0x0A),(0x0B),(0x0C),(0x0D),(0x0E),(0x0F); |
|
82 |
INSERT INTO t1 VALUES (0x10),(0x11),(0x12),(0x13),(0x14),(0x15),(0x16),(0x17); |
|
83 |
INSERT INTO t1 VALUES (0x18),(0x19),(0x1A),(0x1B),(0x1C),(0x1D),(0x1E),(0x1F); |
|
84 |
INSERT INTO t1 VALUES (0x20),(0x21),(0x22),(0x23),(0x24),(0x25),(0x26),(0x27); |
|
85 |
INSERT INTO t1 VALUES (0x28),(0x29),(0x2A),(0x2B),(0x2C),(0x2D),(0x2E),(0x2F); |
|
86 |
INSERT INTO t1 VALUES (0x30),(0x31),(0x32),(0x33),(0x34),(0x35),(0x36),(0x37); |
|
87 |
INSERT INTO t1 VALUES (0x38),(0x39),(0x3A),(0x3B),(0x3C),(0x3D),(0x3E),(0x3F); |
|
88 |
INSERT INTO t1 VALUES (0x40),(0x41),(0x42),(0x43),(0x44),(0x45),(0x46),(0x47); |
|
89 |
INSERT INTO t1 VALUES (0x48),(0x49),(0x4A),(0x4B),(0x4C),(0x4D),(0x4E),(0x4F); |
|
90 |
INSERT INTO t1 VALUES (0x50),(0x51),(0x52),(0x53),(0x54),(0x55),(0x56),(0x57); |
|
91 |
INSERT INTO t1 VALUES (0x58),(0x59),(0x5A),(0x5B),(0x5C),(0x5D),(0x5E),(0x5F); |
|
92 |
INSERT INTO t1 VALUES (0x60),(0x61),(0x62),(0x63),(0x64),(0x65),(0x66),(0x67); |
|
93 |
INSERT INTO t1 VALUES (0x68),(0x69),(0x6A),(0x6B),(0x6C),(0x6D),(0x6E),(0x6F); |
|
94 |
INSERT INTO t1 VALUES (0x70),(0x71),(0x72),(0x73),(0x74),(0x75),(0x76),(0x77); |
|
95 |
INSERT INTO t1 VALUES (0x78),(0x79),(0x7A),(0x7B),(0x7C),(0x7D),(0x7E),(0x7F); |
|
96 |
INSERT INTO t1 VALUES (0x80),(0x81),(0x82),(0x83),(0x84),(0x85),(0x86),(0x87); |
|
97 |
INSERT INTO t1 VALUES (0x88),(0x89),(0x8A),(0x8B),(0x8C),(0x8D),(0x8E),(0x8F); |
|
98 |
INSERT INTO t1 VALUES (0x90),(0x91),(0x92),(0x93),(0x94),(0x95),(0x96),(0x97); |
|
99 |
INSERT INTO t1 VALUES (0x98),(0x99),(0x9A),(0x9B),(0x9C),(0x9D),(0x9E),(0x9F); |
|
100 |
INSERT INTO t1 VALUES (0xA0),(0xA1),(0xA2),(0xA3),(0xA4),(0xA5),(0xA6),(0xA7); |
|
101 |
INSERT INTO t1 VALUES (0xA8),(0xA9),(0xAA),(0xAB),(0xAC),(0xAD),(0xAE),(0xAF); |
|
102 |
INSERT INTO t1 VALUES (0xB0),(0xB1),(0xB2),(0xB3),(0xB4),(0xB5),(0xB6),(0xB7); |
|
103 |
INSERT INTO t1 VALUES (0xB8),(0xB9),(0xBA),(0xBB),(0xBC),(0xBD),(0xBE),(0xBF); |
|
104 |
INSERT INTO t1 VALUES (0xC0),(0xC1),(0xC2),(0xC3),(0xC4),(0xC5),(0xC6),(0xC7); |
|
105 |
INSERT INTO t1 VALUES (0xC8),(0xC9),(0xCA),(0xCB),(0xCC),(0xCD),(0xCE),(0xCF); |
|
106 |
INSERT INTO t1 VALUES (0xD0),(0xD1),(0xD2),(0xD3),(0xD4),(0xD5),(0xD6),(0xD7); |
|
107 |
INSERT INTO t1 VALUES (0xD8),(0xD9),(0xDA),(0xDB),(0xDC),(0xDD),(0xDE),(0xDF); |
|
108 |
INSERT INTO t1 VALUES (0xE0),(0xE1),(0xE2),(0xE3),(0xE4),(0xE5),(0xE6),(0xE7); |
|
109 |
INSERT INTO t1 VALUES (0xE8),(0xE9),(0xEA),(0xEB),(0xEC),(0xED),(0xEE),(0xEF); |
|
110 |
INSERT INTO t1 VALUES (0xF0),(0xF1),(0xF2),(0xF3),(0xF4),(0xF5),(0xF6),(0xF7); |
|
111 |
INSERT INTO t1 VALUES (0xF8),(0xF9),(0xFA),(0xFB),(0xFC),(0xFD),(0xFE),(0xFF); |
|
112 |
SELECT
|
|
113 |
HEX(a) AS chr, |
|
114 |
HEX(LOWER(a)) AS upper, |
|
115 |
HEX(LOWER(a)) AS lower, |
|
116 |
HEX(@utf8:=CONVERT(a USING utf8)) AS utf8, |
|
117 |
HEX(@roundtrip:=CAST(@utf8 AS CHAR)) AS roundtrip, |
|
118 |
if(a=BINARY @roundtrip,'','Round trip unsafe') AS issafe |
|
119 |
FROM t1 ORDER BY chr; |
|
120 |
chr upper lower utf8 roundtrip issafe |
|
121 |
00 00 00 00 00 |
|
122 |
01 01 01 01 01 |
|
123 |
02 02 02 02 02 |
|
124 |
03 03 03 03 03 |
|
125 |
04 04 04 04 04 |
|
126 |
05 05 05 05 05 |
|
127 |
06 06 06 06 06 |
|
128 |
07 07 07 07 07 |
|
129 |
08 08 08 08 08 |
|
130 |
09 09 09 09 09 |
|
131 |
0A 0A 0A 0A 0A |
|
132 |
0B 0B 0B 0B 0B |
|
133 |
0C 0C 0C 0C 0C |
|
134 |
0D 0D 0D 0D 0D |
|
135 |
0E 0E 0E 0E 0E |
|
136 |
0F 0F 0F 0F 0F |
|
137 |
10 10 10 10 10 |
|
138 |
11 11 11 11 11 |
|
139 |
12 12 12 12 12 |
|
140 |
13 13 13 13 13 |
|
141 |
14 14 14 14 14 |
|
142 |
15 15 15 15 15 |
|
143 |
16 16 16 16 16 |
|
144 |
17 17 17 17 17 |
|
145 |
18 18 18 18 18 |
|
146 |
19 19 19 19 19 |
|
147 |
1A 1A 1A 1A 1A |
|
148 |
1B 1B 1B 1B 1B |
|
149 |
1C 1C 1C 1C 1C |
|
150 |
1D 1D 1D 1D 1D |
|
151 |
1E 1E 1E 1E 1E |
|
152 |
1F 1F 1F 1F 1F |
|
153 |
20 20 20 20 20 |
|
154 |
21 21 21 21 21 |
|
155 |
22 22 22 22 22 |
|
156 |
23 23 23 23 23 |
|
157 |
24 24 24 24 24 |
|
158 |
25 25 25 25 25 |
|
159 |
26 26 26 26 26 |
|
160 |
27 27 27 27 27 |
|
161 |
28 28 28 28 28 |
|
162 |
29 29 29 29 29 |
|
163 |
2A 2A 2A 2A 2A |
|
164 |
2B 2B 2B 2B 2B |
|
165 |
2C 2C 2C 2C 2C |
|
166 |
2D 2D 2D 2D 2D |
|
167 |
2E 2E 2E 2E 2E |
|
168 |
2F 2F 2F 2F 2F |
|
169 |
30 30 30 30 30 |
|
170 |
31 31 31 31 31 |
|
171 |
32 32 32 32 32 |
|
172 |
33 33 33 33 33 |
|
173 |
34 34 34 34 34 |
|
174 |
35 35 35 35 35 |
|
175 |
36 36 36 36 36 |
|
176 |
37 37 37 37 37 |
|
177 |
38 38 38 38 38 |
|
178 |
39 39 39 39 39 |
|
179 |
3A 3A 3A 3A 3A |
|
180 |
3B 3B 3B 3B 3B |
|
181 |
3C 3C 3C 3C 3C |
|
182 |
3D 3D 3D 3D 3D |
|
183 |
3E 3E 3E 3E 3E |
|
184 |
3F 3F 3F 3F 3F |
|
185 |
40 40 40 40 40 |
|
186 |
41 61 61 41 41 |
|
187 |
42 62 62 42 42 |
|
188 |
43 63 63 43 43 |
|
189 |
44 64 64 44 44 |
|
190 |
45 65 65 45 45 |
|
191 |
46 66 66 46 46 |
|
192 |
47 67 67 47 47 |
|
193 |
48 68 68 48 48 |
|
194 |
49 69 69 49 49 |
|
195 |
4A 6A 6A 4A 4A |
|
196 |
4B 6B 6B 4B 4B |
|
197 |
4C 6C 6C 4C 4C |
|
198 |
4D 6D 6D 4D 4D |
|
199 |
4E 6E 6E 4E 4E |
|
200 |
4F 6F 6F 4F 4F |
|
201 |
50 70 70 50 50 |
|
202 |
51 71 71 51 51 |
|
203 |
52 72 72 52 52 |
|
204 |
53 73 73 53 53 |
|
205 |
54 74 74 54 54 |
|
206 |
55 75 75 55 55 |
|
207 |
56 76 76 56 56 |
|
208 |
57 77 77 57 57 |
|
209 |
58 78 78 58 58 |
|
210 |
59 79 79 59 59 |
|
211 |
5A 7A 7A 5A 5A |
|
212 |
5B 5B 5B 5B 5B |
|
213 |
5C 5C 5C 5C 5C |
|
214 |
5D 5D 5D 5D 5D |
|
215 |
5E 5E 5E 5E 5E |
|
216 |
5F 5F 5F 5F 5F |
|
217 |
60 60 60 60 60 |
|
218 |
61 61 61 61 61 |
|
219 |
62 62 62 62 62 |
|
220 |
63 63 63 63 63 |
|
221 |
64 64 64 64 64 |
|
222 |
65 65 65 65 65 |
|
223 |
66 66 66 66 66 |
|
224 |
67 67 67 67 67 |
|
225 |
68 68 68 68 68 |
|
226 |
69 69 69 69 69 |
|
227 |
6A 6A 6A 6A 6A |
|
228 |
6B 6B 6B 6B 6B |
|
229 |
6C 6C 6C 6C 6C |
|
230 |
6D 6D 6D 6D 6D |
|
231 |
6E 6E 6E 6E 6E |
|
232 |
6F 6F 6F 6F 6F |
|
233 |
70 70 70 70 70 |
|
234 |
71 71 71 71 71 |
|
235 |
72 72 72 72 72 |
|
236 |
73 73 73 73 73 |
|
237 |
74 74 74 74 74 |
|
238 |
75 75 75 75 75 |
|
239 |
76 76 76 76 76 |
|
240 |
77 77 77 77 77 |
|
241 |
78 78 78 78 78 |
|
242 |
79 79 79 79 79 |
|
243 |
7A 7A 7A 7A 7A |
|
244 |
7B 7B 7B 7B 7B |
|
245 |
7C 7C 7C 7C 7C |
|
246 |
7D 7D 7D 7D 7D |
|
247 |
7E 7E 7E 7E 7E |
|
248 |
7F 7F 7F 7F 7F |
|
249 |
80 90 90 D082 80 |
|
250 |
81 83 83 D083 81 |
|
251 |
82 82 82 E2809A 82 |
|
252 |
83 83 83 D193 83 |
|
253 |
84 84 84 E2809E 84 |
|
254 |
85 85 85 E280A6 85 |
|
255 |
86 86 86 E280A0 86 |
|
256 |
87 87 87 E280A1 87 |
|
257 |
88 88 88 E282AC 88 |
|
258 |
89 89 89 E280B0 89 |
|
259 |
8A 9A 9A D089 8A |
|
260 |
8B 8B 8B E280B9 8B |
|
261 |
8C 9C 9C D08A 8C |
|
262 |
8D 9D 9D D08C 8D |
|
263 |
8E 9E 9E D08B 8E |
|
264 |
8F 9F 9F D08F 8F |
|
265 |
90 90 90 D192 90 |
|
266 |
91 91 91 E28098 91 |
|
267 |
92 92 92 E28099 92 |
|
268 |
93 93 93 E2809C 93 |
|
269 |
94 94 94 E2809D 94 |
|
270 |
95 95 95 E280A2 95 |
|
271 |
96 96 96 E28093 96 |
|
272 |
97 97 97 E28094 97 |
|
273 |
98 98 98 3F 3F Round trip unsafe |
|
274 |
99 99 99 E284A2 99 |
|
275 |
9A 9A 9A D199 9A |
|
276 |
9B 9B 9B E280BA 9B |
|
277 |
9C 9C 9C D19A 9C |
|
278 |
9D 9D 9D D19C 9D |
|
279 |
9E 9E 9E D19B 9E |
|
280 |
9F 9F 9F D19F 9F |
|
281 |
A0 A0 A0 C2A0 A0 |
|
282 |
A1 A2 A2 D08E A1 |
|
283 |
A2 A2 A2 D19E A2 |
|
284 |
A3 BC BC D088 A3 |
|
285 |
A4 A4 A4 C2A4 A4 |
|
286 |
A5 B4 B4 D290 A5 |
|
287 |
A6 A6 A6 C2A6 A6 |
|
288 |
A7 A7 A7 C2A7 A7 |
|
289 |
A8 B8 B8 D081 A8 |
|
290 |
A9 A9 A9 C2A9 A9 |
|
291 |
AA BA BA D084 AA |
|
292 |
AB AB AB C2AB AB |
|
293 |
AC AC AC C2AC AC |
|
294 |
AD AD AD C2AD AD |
|
295 |
AE AE AE C2AE AE |
|
296 |
AF BF BF D087 AF |
|
297 |
B0 B0 B0 C2B0 B0 |
|
298 |
B1 B1 B1 C2B1 B1 |
|
299 |
B2 B3 B3 D086 B2 |
|
300 |
B3 B3 B3 D196 B3 |
|
301 |
B4 B4 B4 D291 B4 |
|
302 |
B5 B5 B5 C2B5 B5 |
|
303 |
B6 B6 B6 C2B6 B6 |
|
304 |
B7 B7 B7 C2B7 B7 |
|
305 |
B8 B8 B8 D191 B8 |
|
306 |
B9 B9 B9 E28496 B9 |
|
307 |
BA BA BA D194 BA |
|
308 |
BB BB BB C2BB BB |
|
309 |
BC BC BC D198 BC |
|
310 |
BD BE BE D085 BD |
|
311 |
BE BE BE D195 BE |
|
312 |
BF BF BF D197 BF |
|
313 |
C0 E0 E0 D090 C0 |
|
314 |
C1 E1 E1 D091 C1 |
|
315 |
C2 E2 E2 D092 C2 |
|
316 |
C3 E3 E3 D093 C3 |
|
317 |
C4 E4 E4 D094 C4 |
|
318 |
C5 E5 E5 D095 C5 |
|
319 |
C6 E6 E6 D096 C6 |
|
320 |
C7 E7 E7 D097 C7 |
|
321 |
C8 E8 E8 D098 C8 |
|
322 |
C9 E9 E9 D099 C9 |
|
323 |
CA EA EA D09A CA |
|
324 |
CB EB EB D09B CB |
|
325 |
CC EC EC D09C CC |
|
326 |
CD ED ED D09D CD |
|
327 |
CE EE EE D09E CE |
|
328 |
CF EF EF D09F CF |
|
329 |
D0 F0 F0 D0A0 D0 |
|
330 |
D1 F1 F1 D0A1 D1 |
|
331 |
D2 F2 F2 D0A2 D2 |
|
332 |
D3 F3 F3 D0A3 D3 |
|
333 |
D4 F4 F4 D0A4 D4 |
|
334 |
D5 F5 F5 D0A5 D5 |
|
335 |
D6 F6 F6 D0A6 D6 |
|
336 |
D7 F7 F7 D0A7 D7 |
|
337 |
D8 F8 F8 D0A8 D8 |
|
338 |
D9 F9 F9 D0A9 D9 |
|
339 |
DA FA FA D0AA DA |
|
340 |
DB FB FB D0AB DB |
|
341 |
DC FC FC D0AC DC |
|
342 |
DD FD FD D0AD DD |
|
343 |
DE FE FE D0AE DE |
|
344 |
DF FF FF D0AF DF |
|
345 |
E0 E0 E0 D0B0 E0 |
|
346 |
E1 E1 E1 D0B1 E1 |
|
347 |
E2 E2 E2 D0B2 E2 |
|
348 |
E3 E3 E3 D0B3 E3 |
|
349 |
E4 E4 E4 D0B4 E4 |
|
350 |
E5 E5 E5 D0B5 E5 |
|
351 |
E6 E6 E6 D0B6 E6 |
|
352 |
E7 E7 E7 D0B7 E7 |
|
353 |
E8 E8 E8 D0B8 E8 |
|
354 |
E9 E9 E9 D0B9 E9 |
|
355 |
EA EA EA D0BA EA |
|
356 |
EB EB EB D0BB EB |
|
357 |
EC EC EC D0BC EC |
|
358 |
ED ED ED D0BD ED |
|
359 |
EE EE EE D0BE EE |
|
360 |
EF EF EF D0BF EF |
|
361 |
F0 F0 F0 D180 F0 |
|
362 |
F1 F1 F1 D181 F1 |
|
363 |
F2 F2 F2 D182 F2 |
|
364 |
F3 F3 F3 D183 F3 |
|
365 |
F4 F4 F4 D184 F4 |
|
366 |
F5 F5 F5 D185 F5 |
|
367 |
F6 F6 F6 D186 F6 |
|
368 |
F7 F7 F7 D187 F7 |
|
369 |
F8 F8 F8 D188 F8 |
|
370 |
F9 F9 F9 D189 F9 |
|
371 |
FA FA FA D18A FA |
|
372 |
FB FB FB D18B FB |
|
373 |
FC FC FC D18C FC |
|
374 |
FD FD FD D18D FD |
|
375 |
FE FE FE D18E FE |
|
376 |
FF FF FF D18F FF |
|
377 |
DROP TABLE t1; |
|
378 |
set global LC_TIME_NAMES=convert((-8388608) using cp1251); |
|
379 |
ERROR HY000: Unknown locale: '-8388608' |
|
380 |
#
|
|
381 |
# End of 5.1 tests
|
|
382 |
#
|