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