1
drop table if exists t1;
2
In the following tests we change the order of letter "b"
3
making it equal to letter "a", and check that it works
4
with all Unicode character sets
6
show variables like 'character_sets_dir%';
8
character_sets_dir MYSQL_TEST_DIR/std_data/
9
show collation like 'utf8_phone_ci';
10
Collation Charset Id Default Compiled Sortlen
11
utf8_phone_ci utf8 352 8
14
phone VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_phone_ci
16
INSERT INTO t1 VALUES ('Svoj','+7 912 800 80 02');
17
INSERT INTO t1 VALUES ('Hf','+7 (912) 800 80 04');
18
INSERT INTO t1 VALUES ('Bar','+7-912-800-80-01');
19
INSERT INTO t1 VALUES ('Ramil','(7912) 800 80 03');
20
INSERT INTO t1 VALUES ('Sanja','+380 (912) 8008005');
21
SELECT * FROM t1 ORDER BY phone;
23
Sanja +380 (912) 8008005
26
Ramil (7912) 800 80 03
28
SELECT * FROM t1 WHERE phone='+7(912)800-80-01';
31
SELECT * FROM t1 WHERE phone='79128008001';
34
SELECT * FROM t1 WHERE phone='7 9 1 2 8 0 0 8 0 0 1';
38
show collation like 'utf8_test_ci';
39
Collation Charset Id Default Compiled Sortlen
40
utf8_test_ci utf8 353 8
41
create table t1 (c1 char(1) character set utf8 collate utf8_test_ci);
42
insert into t1 values ('a');
43
select * from t1 where c1='b';
47
show collation like 'ucs2_test_ci';
48
Collation Charset Id Default Compiled Sortlen
49
ucs2_test_ci ucs2 358 8
50
create table t1 (c1 char(1) character set ucs2 collate ucs2_test_ci);
51
insert into t1 values ('a');
52
select * from t1 where c1='b';
56
show collation like 'utf8mb4_test_ci';
57
Collation Charset Id Default Compiled Sortlen
58
utf8mb4_test_ci utf8mb4 326 8
59
create table t1 (c1 char(1) character set utf8mb4 collate utf8mb4_test_ci);
60
insert into t1 values ('a');
61
select * from t1 where c1='b';
65
show collation like 'utf16_test_ci';
66
Collation Charset Id Default Compiled Sortlen
67
utf16_test_ci utf16 327 8
68
create table t1 (c1 char(1) character set utf16 collate utf16_test_ci);
69
insert into t1 values ('a');
70
select * from t1 where c1='b';
74
show collation like 'utf32_test_ci';
75
Collation Charset Id Default Compiled Sortlen
76
utf32_test_ci utf32 391 8
77
create table t1 (c1 char(1) character set utf32 collate utf32_test_ci);
78
insert into t1 values ('a');
79
select * from t1 where c1='b';
84
col1 varchar(100) character set utf8 collate utf8_test_ci
86
INSERT INTO t1 (col1) VALUES ('abcd'),('efgh'),('ijkl');
87
ALTER TABLE t1 ADD FULLTEXT INDEX (col1);
88
SELECT * FROM t1 where match (col1) against ('abcd');
91
SELECT * FROM t1 where match (col1) against ('abcd' IN BOOLEAN MODE);
94
ALTER TABLE t1 ADD (col2 varchar(100) character set latin1);
95
UPDATE t1 SET col2=col1;
96
SELECT * FROM t1 WHERE col1=col2 ORDER BY col1;
103
# Bug#45645 Mysql server close all connection and restart using lower function
105
CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_test_ci;
106
INSERT INTO t1 (a) VALUES ('hello!');
107
SELECT * FROM t1 WHERE LOWER(a)=LOWER('N');
111
# Bug#51976 LDML collations issue (cyrillic example)
113
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci);
114
INSERT INTO t1 (a) VALUES ('Hello');
115
SELECT a, UPPER(a), LOWER(a) FROM t1;
120
# Bug#43827 Server closes connections and restarts
122
CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci);
123
INSERT INTO t1 SELECT REPEAT('a',11);
125
Warning 1265 Data truncated for column 'c1' at row 1
127
Vietnamese experimental collation
128
show collation like 'ucs2_vn_ci';
129
Collation Charset Id Default Compiled Sortlen
130
ucs2_vn_ci ucs2 359 8
131
create table t1 (c1 char(1) character set ucs2 collate ucs2_vn_ci);
132
insert into t1 values (0x0061),(0x0041),(0x00E0),(0x00C0),(0x1EA3),(0x1EA2),
133
(0x00E3),(0x00C3),(0x00E1),(0x00C1),(0x1EA1),(0x1EA0);
134
insert into t1 values (0x0103),(0x0102),(0x1EB1),(0x1EB0),(0x1EB3),(0x1EB2),
135
(0x1EB5),(0x1EB4),(0x1EAF),(0x1EAE),(0x1EB7),(0x1EB6);
136
insert into t1 values (0x00E2),(0x00C2),(0x1EA7),(0x1EA6),(0x1EA9),(0x1EA8),
137
(0x1EAB),(0x1EAA),(0x1EA5),(0x1EA4),(0x1EAD),(0x1EAC);
138
insert into t1 values ('b'),('B'),('c'),('C');
139
insert into t1 values ('d'),('D'),(0x0111),(0x0110);
140
insert into t1 values (0x0065),(0x0045),(0x00E8),(0x00C8),(0x1EBB),(0x1EBA),
141
(0x1EBD),(0x1EBC),(0x00E9),(0x00C9),(0x1EB9),(0x1EB8);
142
insert into t1 values (0x00EA),(0x00CA),(0x1EC1),(0x1EC0),(0x1EC3),(0x1EC2),
143
(0x1EC5),(0x1EC4),(0x1EBF),(0x1EBE),(0x1EC7),(0x1EC6);
144
insert into t1 values ('g'),('G'),('h'),('H');
145
insert into t1 values (0x0069),(0x0049),(0x00EC),(0x00CC),(0x1EC9),(0x1EC8),
146
(0x0129),(0x0128),(0x00ED),(0x00CD),(0x1ECB),(0x1ECA);
147
insert into t1 values ('k'),('K'),('l'),('L'),('m'),('M');
148
insert into t1 values (0x006F),(0x004F),(0x00F2),(0x00D2),(0x1ECF),(0x1ECE),
149
(0x00F5),(0x00D5),(0x00F3),(0x00D3),(0x1ECD),(0x1ECC);
150
insert into t1 values (0x00F4),(0x00D4),(0x1ED3),(0x1ED2),(0x1ED5),(0x1ED4),
151
(0x1ED7),(0x1ED6),(0x1ED1),(0x1ED0),(0x1ED9),(0x1ED8);
152
insert into t1 values (0x01A1),(0x01A0),(0x1EDD),(0x1EDC),(0x1EDF),(0x1EDE),
153
(0x1EE1),(0x1EE0),(0x1EDB),(0x1EDA),(0x1EE3),(0x1EE2);
154
insert into t1 values ('p'),('P'),('q'),('Q'),('r'),('R'),('s'),('S'),('t'),('T');
155
insert into t1 values (0x0075),(0x0055),(0x00F9),(0x00D9),(0x1EE7),(0x1EE6),
156
(0x0169),(0x0168),(0x00FA),(0x00DA),(0x1EE5),(0x1EE4);
157
insert into t1 values (0x01B0),(0x01AF),(0x1EEB),(0x1EEA),(0x1EED),(0x1EEC),
158
(0x1EEF),(0x1EEE),(0x1EE9),(0x1EE8),(0x1EF1),(0x1EF0);
159
insert into t1 values ('v'),('V'),('x'),('X');
160
insert into t1 values (0x0079),(0x0059),(0x1EF3),(0x1EF2),(0x1EF7),(0x1EF6),
161
(0x1EF9),(0x1EF8),(0x00FD),(0x00DD),(0x1EF5),(0x1EF4);
162
select hex(c1) as h, c1 from t1 order by c1, h;
340
select group_concat(hex(c1) order by hex(c1)) from t1 group by c1;
341
group_concat(hex(c1) order by hex(c1))
342
0041,0061,00C0,00C1,00C3,00E0,00E1,00E3,1EA0,1EA1,1EA2,1EA3
343
0102,0103,1EAE,1EAF,1EB0,1EB1,1EB2,1EB3,1EB4,1EB5,1EB6,1EB7
344
00C2,00E2,1EA4,1EA5,1EA6,1EA7,1EA8,1EA9,1EAA,1EAB,1EAC,1EAD
349
0045,0065,00C8,00C9,00E8,00E9,1EB8,1EB9,1EBA,1EBB,1EBC,1EBD
350
00CA,00EA,1EBE,1EBF,1EC0,1EC1,1EC2,1EC3,1EC4,1EC5,1EC6,1EC7
353
0049,0069,00CC,00CD,00EC,00ED,0128,0129,1EC8,1EC9,1ECA,1ECB
357
004F,006F,00D2,00D3,00D5,00F2,00F3,00F5,1ECC,1ECD,1ECE,1ECF
358
00D4,00F4,1ED0,1ED1,1ED2,1ED3,1ED4,1ED5,1ED6,1ED7,1ED8,1ED9
359
01A0,01A1,1EDA,1EDB,1EDC,1EDD,1EDE,1EDF,1EE0,1EE1,1EE2,1EE3
365
0055,0075,00D9,00DA,00F9,00FA,0168,0169,1EE4,1EE5,1EE6,1EE7
366
01AF,01B0,1EE8,1EE9,1EEA,1EEB,1EEC,1EED,1EEE,1EEF,1EF0,1EF1
369
0059,0079,00DD,00FD,1EF2,1EF3,1EF4,1EF5,1EF6,1EF7,1EF8,1EF9
370
select group_concat(c1 order by hex(c1) SEPARATOR '') from t1 group by c1;
371
group_concat(c1 order by hex(c1) SEPARATOR '')
401
Bug#46448 trailing spaces are not ignored when user collation maps space != 0x20
403
show collation like 'latin1_test';
404
Collation Charset Id Default Compiled Sortlen
405
latin1_test latin1 99 Yes 1
406
select "foo" = "foo " collate latin1_test;
407
"foo" = "foo " collate latin1_test
409
The following tests check that two-byte collation IDs work
410
select * from information_schema.collations where id>256 order by id;
411
COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN
412
utf8mb4_test_ci utf8mb4 326 8
413
utf16_test_ci utf16 327 8
414
utf8_phone_ci utf8 352 8
415
utf8_test_ci utf8 353 8
416
ucs2_test_ci ucs2 358 8
417
ucs2_vn_ci ucs2 359 8
418
utf32_test_ci utf32 391 8
419
utf8_maxuserid_ci utf8 2047 8
420
show collation like '%test%';
421
Collation Charset Id Default Compiled Sortlen
422
latin1_test latin1 99 Yes 1
423
utf8_test_ci utf8 353 8
424
ucs2_test_ci ucs2 358 8
425
utf8mb4_test_ci utf8mb4 326 8
426
utf16_test_ci utf16 327 8
427
utf32_test_ci utf32 391 8
428
show collation like 'ucs2_vn_ci';
429
Collation Charset Id Default Compiled Sortlen
430
ucs2_vn_ci ucs2 359 8
431
create table t1 (c1 char(1) character set ucs2 collate ucs2_vn_ci);
432
insert into t1 values (0x0061);
433
set @@character_set_results=NULL;
435
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
436
def test t1 t1 c1 c1 254 2 2 Y 0 0 359