drop table if exists t1;
In the following tests we change the order of letter "b"
making it equal to letter "a", and check that it works
with all Unicode character sets
set names utf8;
show variables like 'character_sets_dir%';
Variable_name Value
character_sets_dir MYSQL_TEST_DIR/std_data/
show collation like 'utf8_phone_ci';
Collation Charset Id Default Compiled Sortlen
utf8_phone_ci utf8 352 8
CREATE TABLE t1 (
name VARCHAR(64),
phone VARCHAR(64) CHARACTER SET utf8 COLLATE utf8_phone_ci
);
INSERT INTO t1 VALUES ('Svoj','+7 912 800 80 02');
INSERT INTO t1 VALUES ('Hf','+7 (912) 800 80 04');
INSERT INTO t1 VALUES ('Bar','+7-912-800-80-01');
INSERT INTO t1 VALUES ('Ramil','(7912) 800 80 03');
INSERT INTO t1 VALUES ('Sanja','+380 (912) 8008005');
SELECT * FROM t1 ORDER BY phone;
name phone
Sanja +380 (912) 8008005
Bar +7-912-800-80-01
Svoj +7 912 800 80 02
Ramil (7912) 800 80 03
Hf +7 (912) 800 80 04
SELECT * FROM t1 WHERE phone='+7(912)800-80-01';
name phone
Bar +7-912-800-80-01
SELECT * FROM t1 WHERE phone='79128008001';
name phone
Bar +7-912-800-80-01
SELECT * FROM t1 WHERE phone='7 9 1 2 8 0 0 8 0 0 1';
name phone
Bar +7-912-800-80-01
DROP TABLE t1;
show collation like 'utf8_test_ci';
Collation Charset Id Default Compiled Sortlen
utf8_test_ci utf8 353 8
create table t1 (c1 char(1) character set utf8 collate utf8_test_ci);
insert into t1 values ('a');
select * from t1 where c1='b';
c1
a
drop table t1;
show collation like 'ucs2_test_ci';
Collation Charset Id Default Compiled Sortlen
ucs2_test_ci ucs2 358 8
create table t1 (c1 char(1) character set ucs2 collate ucs2_test_ci);
insert into t1 values ('a');
select * from t1 where c1='b';
c1
a
drop table t1;
show collation like 'utf8mb4_test_ci';
Collation Charset Id Default Compiled Sortlen
utf8mb4_test_ci utf8mb4 326 8
create table t1 (c1 char(1) character set utf8mb4 collate utf8mb4_test_ci);
insert into t1 values ('a');
select * from t1 where c1='b';
c1
a
drop table t1;
show collation like 'utf16_test_ci';
Collation Charset Id Default Compiled Sortlen
utf16_test_ci utf16 327 8
create table t1 (c1 char(1) character set utf16 collate utf16_test_ci);
insert into t1 values ('a');
select * from t1 where c1='b';
c1
a
drop table t1;
show collation like 'utf32_test_ci';
Collation Charset Id Default Compiled Sortlen
utf32_test_ci utf32 391 8
create table t1 (c1 char(1) character set utf32 collate utf32_test_ci);
insert into t1 values ('a');
select * from t1 where c1='b';
c1
a
drop table t1;
SELECT hex(weight_string(_utf8mb4'a' collate utf8mb4_test_ci));
hex(weight_string(_utf8mb4'a' collate utf8mb4_test_ci))
120F
SELECT hex(weight_string(convert(_utf32 0x10002 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x10002 using utf8mb4) collate utf8mb4_test_ci))
314A
SELECT hex(@a:=convert(_utf32 0x10400 using utf8mb4) collate utf8mb4_test_ci), hex(lower(@a));
hex(@a:=convert(_utf32 0x10400 using utf8mb4) collate utf8mb4_test_ci) hex(lower(@a))
F0909080 F09090A8
SELECT hex(@a:=convert(_utf32 0x10428 using utf8mb4) collate utf8mb4_test_ci), hex(upper(@a));
hex(@a:=convert(_utf32 0x10428 using utf8mb4) collate utf8mb4_test_ci) hex(upper(@a))
F09090A8 F0909080
SELECT hex(@a:=convert(_utf32 0x2C00 using utf8mb4) collate utf8mb4_test_ci), hex(lower(@a));
hex(@a:=convert(_utf32 0x2C00 using utf8mb4) collate utf8mb4_test_ci) hex(lower(@a))
E2B080 E2B0B0
SELECT hex(@a:=convert(_utf32 0x2C30 using utf8mb4) collate utf8mb4_test_ci), hex(upper(@a));
hex(@a:=convert(_utf32 0x2C30 using utf8mb4) collate utf8mb4_test_ci) hex(upper(@a))
E2B0B0 E2B080
SELECT hex(weight_string(convert(_utf32 0x61 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x61 using utf8mb4) collate utf8mb4_test_ci))
120F
SELECT hex(weight_string(convert(_utf32 0x62 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x62 using utf8mb4) collate utf8mb4_test_ci))
120F
SELECT hex(weight_string(convert(_utf32 0x10062 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x10062 using utf8mb4) collate utf8mb4_test_ci))
120F
SELECT hex(weight_string(convert(_utf32 0x10400 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x10400 using utf8mb4) collate utf8mb4_test_ci))
30D2
SELECT hex(weight_string(convert(_utf32 0x100400 using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_utf32 0x100400 using utf8mb4) collate utf8mb4_test_ci))
30D2
SELECT hex(weight_string(_utf8mb4 0x64 collate utf8mb4_test_ci));
hex(weight_string(_utf8mb4 0x64 collate utf8mb4_test_ci))
1250
SELECT hex(weight_string(convert(_ucs2 0x0064017e using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_ucs2 0x0064017e using utf8mb4) collate utf8mb4_test_ci))
1251
SELECT hex(weight_string(convert(_ucs2 0x0044017e using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_ucs2 0x0044017e using utf8mb4) collate utf8mb4_test_ci))
1251
SELECT hex(weight_string(convert(_ucs2 0x0044017d using utf8mb4) collate utf8mb4_test_ci));
hex(weight_string(convert(_ucs2 0x0044017d using utf8mb4) collate utf8mb4_test_ci))
1251
CREATE TABLE t1 (
col1 varchar(100) character set utf8 collate utf8_test_ci
);
INSERT INTO t1 (col1) VALUES ('abcd'),('efgh'),('ijkl');
ALTER TABLE t1 ADD FULLTEXT INDEX (col1);
SELECT * FROM t1 where match (col1) against ('abcd');
col1
abcd
SELECT * FROM t1 where match (col1) against ('abcd' IN BOOLEAN MODE);
col1
abcd
ALTER TABLE t1 ADD (col2 varchar(100) character set latin1);
UPDATE t1 SET col2=col1;
SELECT * FROM t1 WHERE col1=col2 ORDER BY col1;
col1 col2
abcd abcd
efgh efgh
ijkl ijkl
DROP TABLE t1;
#
# Bug#45645 Mysql server close all connection and restart using lower function
#
CREATE TABLE t1 (a VARCHAR(10)) CHARACTER SET utf8 COLLATE utf8_test_ci;
INSERT INTO t1 (a) VALUES ('hello!');
SELECT * FROM t1 WHERE LOWER(a)=LOWER('N');
a
DROP TABLE t1;
#
# Bug#51976 LDML collations issue (cyrillic example)
#
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci);
INSERT INTO t1 (a) VALUES ('Hello');
SELECT a, UPPER(a), LOWER(a) FROM t1;
a UPPER(a) LOWER(a)
Hello HELLO hello
DROP TABLE t1;
#
# Bug#43827 Server closes connections and restarts
#
CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci);
INSERT INTO t1 SELECT REPEAT('a',11);
Warnings:
Warning 1265 Data truncated for column 'c1' at row 1
DROP TABLE t1;
Vietnamese experimental collation
show collation like 'ucs2_vn_ci';
Collation Charset Id Default Compiled Sortlen
ucs2_vn_ci ucs2 359 8
create table t1 (c1 char(1) character set ucs2 collate ucs2_vn_ci);
insert into t1 values (0x0061),(0x0041),(0x00E0),(0x00C0),(0x1EA3),(0x1EA2),
(0x00E3),(0x00C3),(0x00E1),(0x00C1),(0x1EA1),(0x1EA0);
insert into t1 values (0x0103),(0x0102),(0x1EB1),(0x1EB0),(0x1EB3),(0x1EB2),
(0x1EB5),(0x1EB4),(0x1EAF),(0x1EAE),(0x1EB7),(0x1EB6);
insert into t1 values (0x00E2),(0x00C2),(0x1EA7),(0x1EA6),(0x1EA9),(0x1EA8),
(0x1EAB),(0x1EAA),(0x1EA5),(0x1EA4),(0x1EAD),(0x1EAC);
insert into t1 values ('b'),('B'),('c'),('C');
insert into t1 values ('d'),('D'),(0x0111),(0x0110);
insert into t1 values (0x0065),(0x0045),(0x00E8),(0x00C8),(0x1EBB),(0x1EBA),
(0x1EBD),(0x1EBC),(0x00E9),(0x00C9),(0x1EB9),(0x1EB8);
insert into t1 values (0x00EA),(0x00CA),(0x1EC1),(0x1EC0),(0x1EC3),(0x1EC2),
(0x1EC5),(0x1EC4),(0x1EBF),(0x1EBE),(0x1EC7),(0x1EC6);
insert into t1 values ('g'),('G'),('h'),('H');
insert into t1 values (0x0069),(0x0049),(0x00EC),(0x00CC),(0x1EC9),(0x1EC8),
(0x0129),(0x0128),(0x00ED),(0x00CD),(0x1ECB),(0x1ECA);
insert into t1 values ('k'),('K'),('l'),('L'),('m'),('M');
insert into t1 values (0x006F),(0x004F),(0x00F2),(0x00D2),(0x1ECF),(0x1ECE),
(0x00F5),(0x00D5),(0x00F3),(0x00D3),(0x1ECD),(0x1ECC);
insert into t1 values (0x00F4),(0x00D4),(0x1ED3),(0x1ED2),(0x1ED5),(0x1ED4),
(0x1ED7),(0x1ED6),(0x1ED1),(0x1ED0),(0x1ED9),(0x1ED8);
insert into t1 values (0x01A1),(0x01A0),(0x1EDD),(0x1EDC),(0x1EDF),(0x1EDE),
(0x1EE1),(0x1EE0),(0x1EDB),(0x1EDA),(0x1EE3),(0x1EE2);
insert into t1 values ('p'),('P'),('q'),('Q'),('r'),('R'),('s'),('S'),('t'),('T');
insert into t1 values (0x0075),(0x0055),(0x00F9),(0x00D9),(0x1EE7),(0x1EE6),
(0x0169),(0x0168),(0x00FA),(0x00DA),(0x1EE5),(0x1EE4);
insert into t1 values (0x01B0),(0x01AF),(0x1EEB),(0x1EEA),(0x1EED),(0x1EEC),
(0x1EEF),(0x1EEE),(0x1EE9),(0x1EE8),(0x1EF1),(0x1EF0);
insert into t1 values ('v'),('V'),('x'),('X');
insert into t1 values (0x0079),(0x0059),(0x1EF3),(0x1EF2),(0x1EF7),(0x1EF6),
(0x1EF9),(0x1EF8),(0x00FD),(0x00DD),(0x1EF5),(0x1EF4);
select hex(c1) as h, c1 from t1 order by c1, h;
h c1
0041 A
0061 a
00C0 À
00C1 Á
00C3 Ã
00E0 à
00E1 á
00E3 ã
1EA0 Ạ
1EA1 ạ
1EA2 Ả
1EA3 ả
0102 Ă
0103 ă
1EAE Ắ
1EAF ắ
1EB0 Ằ
1EB1 ằ
1EB2 Ẳ
1EB3 ẳ
1EB4 Ẵ
1EB5 ẵ
1EB6 Ặ
1EB7 ặ
00C2 Â
00E2 â
1EA4 Ấ
1EA5 ấ
1EA6 Ầ
1EA7 ầ
1EA8 Ẩ
1EA9 ẩ
1EAA Ẫ
1EAB ẫ
1EAC Ậ
1EAD ậ
0042 B
0062 b
0043 C
0063 c
0044 D
0064 d
0110 Đ
0111 đ
0045 E
0065 e
00C8 È
00C9 É
00E8 è
00E9 é
1EB8 Ẹ
1EB9 ẹ
1EBA Ẻ
1EBB ẻ
1EBC Ẽ
1EBD ẽ
00CA Ê
00EA ê
1EBE Ế
1EBF ế
1EC0 Ề
1EC1 ề
1EC2 Ể
1EC3 ể
1EC4 Ễ
1EC5 ễ
1EC6 Ệ
1EC7 ệ
0047 G
0067 g
0048 H
0068 h
0049 I
0069 i
00CC Ì
00CD Í
00EC ì
00ED í
0128 Ĩ
0129 ĩ
1EC8 Ỉ
1EC9 ỉ
1ECA Ị
1ECB ị
004B K
006B k
004C L
006C l
004D M
006D m
004F O
006F o
00D2 Ò
00D3 Ó
00D5 Õ
00F2 ò
00F3 ó
00F5 õ
1ECC Ọ
1ECD ọ
1ECE Ỏ
1ECF ỏ
00D4 Ô
00F4 ô
1ED0 Ố
1ED1 ố
1ED2 Ồ
1ED3 ồ
1ED4 Ổ
1ED5 ổ
1ED6 Ỗ
1ED7 ỗ
1ED8 Ộ
1ED9 ộ
01A0 Ơ
01A1 ơ
1EDA Ớ
1EDB ớ
1EDC Ờ
1EDD ờ
1EDE Ở
1EDF ở
1EE0 Ỡ
1EE1 ỡ
1EE2 Ợ
1EE3 ợ
0050 P
0070 p
0051 Q
0071 q
0052 R
0072 r
0053 S
0073 s
0054 T
0074 t
0055 U
0075 u
00D9 Ù
00DA Ú
00F9 ù
00FA ú
0168 Ũ
0169 ũ
1EE4 Ụ
1EE5 ụ
1EE6 Ủ
1EE7 ủ
01AF Ư
01B0 ư
1EE8 Ứ
1EE9 ứ
1EEA Ừ
1EEB ừ
1EEC Ử
1EED ử
1EEE Ữ
1EEF ữ
1EF0 Ự
1EF1 ự
0056 V
0076 v
0058 X
0078 x
0059 Y
0079 y
00DD Ý
00FD ý
1EF2 Ỳ
1EF3 ỳ
1EF4 Ỵ
1EF5 ỵ
1EF6 Ỷ
1EF7 ỷ
1EF8 Ỹ
1EF9 ỹ
select group_concat(hex(c1) order by hex(c1)) from t1 group by c1;
group_concat(hex(c1) order by hex(c1))
0041,0061,00C0,00C1,00C3,00E0,00E1,00E3,1EA0,1EA1,1EA2,1EA3
0102,0103,1EAE,1EAF,1EB0,1EB1,1EB2,1EB3,1EB4,1EB5,1EB6,1EB7
00C2,00E2,1EA4,1EA5,1EA6,1EA7,1EA8,1EA9,1EAA,1EAB,1EAC,1EAD
0042,0062
0043,0063
0044,0064
0110,0111
0045,0065,00C8,00C9,00E8,00E9,1EB8,1EB9,1EBA,1EBB,1EBC,1EBD
00CA,00EA,1EBE,1EBF,1EC0,1EC1,1EC2,1EC3,1EC4,1EC5,1EC6,1EC7
0047,0067
0048,0068
0049,0069,00CC,00CD,00EC,00ED,0128,0129,1EC8,1EC9,1ECA,1ECB
004B,006B
004C,006C
004D,006D
004F,006F,00D2,00D3,00D5,00F2,00F3,00F5,1ECC,1ECD,1ECE,1ECF
00D4,00F4,1ED0,1ED1,1ED2,1ED3,1ED4,1ED5,1ED6,1ED7,1ED8,1ED9
01A0,01A1,1EDA,1EDB,1EDC,1EDD,1EDE,1EDF,1EE0,1EE1,1EE2,1EE3
0050,0070
0051,0071
0052,0072
0053,0073
0054,0074
0055,0075,00D9,00DA,00F9,00FA,0168,0169,1EE4,1EE5,1EE6,1EE7
01AF,01B0,1EE8,1EE9,1EEA,1EEB,1EEC,1EED,1EEE,1EEF,1EF0,1EF1
0056,0076
0058,0078
0059,0079,00DD,00FD,1EF2,1EF3,1EF4,1EF5,1EF6,1EF7,1EF8,1EF9
select group_concat(c1 order by hex(c1) SEPARATOR '') from t1 group by c1;
group_concat(c1 order by hex(c1) SEPARATOR '')
AaÀÁÃàáãẠạẢả
ĂăẮắẰằẲẳẴẵẶặ
ÂâẤấẦầẨẩẪẫẬậ
Bb
Cc
Dd
Đđ
EeÈÉèéẸẹẺẻẼẽ
ÊêẾếỀềỂểỄễỆệ
Gg
Hh
IiÌÍìíĨĩỈỉỊị
Kk
Ll
Mm
OoÒÓÕòóõỌọỎỏ
ÔôỐốỒồỔổỖỗỘộ
ƠơỚớỜờỞởỠỡỢợ
Pp
Qq
Rr
Ss
Tt
UuÙÚùúŨũỤụỦủ
ƯưỨứỪừỬửỮữỰự
Vv
Xx
YyÝýỲỳỴỵỶỷỸỹ
drop table t1;
Bug#46448 trailing spaces are not ignored when user collation maps space != 0x20
set names latin1;
show collation like 'latin1_test';
Collation Charset Id Default Compiled Sortlen
latin1_test latin1 99 Yes 1
select "foo" = "foo " collate latin1_test;
"foo" = "foo " collate latin1_test
1
The following tests check that two-byte collation IDs work
select * from information_schema.collations where id>256 order by id;
COLLATION_NAME CHARACTER_SET_NAME ID IS_DEFAULT IS_COMPILED SORTLEN
utf8mb4_test_ci utf8mb4 326 8
utf16_test_ci utf16 327 8
utf8mb4_test_400_ci utf8mb4 328 8
utf8_bengali_standard_ci utf8 336 8
utf8_bengali_traditional_ci utf8 337 8
utf8_phone_ci utf8 352 8
utf8_test_ci utf8 353 8
utf8_5624_1 utf8 354 8
utf8_5624_2 utf8 355 8
utf8_5624_3 utf8 356 8
utf8_5624_4 utf8 357 8
ucs2_test_ci ucs2 358 8
ucs2_vn_ci ucs2 359 8
ucs2_5624_1 ucs2 360 8
utf8_5624_5 utf8 368 8
utf32_test_ci utf32 391 8
utf8_maxuserid_ci utf8 2047 8
show collation like '%test%';
Collation Charset Id Default Compiled Sortlen
latin1_test latin1 99 Yes 1
utf8_test_ci utf8 353 8
ucs2_test_ci ucs2 358 8
utf8mb4_test_ci utf8mb4 326 8
utf8mb4_test_400_ci utf8mb4 328 8
utf16_test_ci utf16 327 8
utf32_test_ci utf32 391 8
show collation like 'ucs2_vn_ci';
Collation Charset Id Default Compiled Sortlen
ucs2_vn_ci ucs2 359 8
create table t1 (c1 char(1) character set ucs2 collate ucs2_vn_ci);
insert into t1 values (0x0061);
set @@character_set_results=NULL;
select * from t1;
Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr
def test t1 t1 c1 c1 254 2 2 Y 0 0 359
c1
a
drop table t1;
CREATE TABLE t1 (s1 char(10) character set utf8 collate utf8_maxuserid_ci);
INSERT INTO t1 VALUES ('a'),('b');
SELECT * FROM t1 WHERE s1='a' ORDER BY BINARY s1;
s1
a
b
DROP TABLE t1;
SET NAMES utf8 COLLATE utf8_phone_ci;
SHOW COLLATION LIKE 'utf8_phone_ci';
Collation Charset Id Default Compiled Sortlen
utf8_phone_ci utf8 352 8
SET NAMES utf8;
SELECT hex(weight_string(_utf8mb4'a' collate utf8mb4_test_400_ci));
hex(weight_string(_utf8mb4'a' collate utf8mb4_test_400_ci))
0E33
SELECT hex(weight_string(convert(_utf32 0x10002 using utf8mb4) collate utf8mb4_test_400_ci));
hex(weight_string(convert(_utf32 0x10002 using utf8mb4) collate utf8mb4_test_400_ci))
FFFD
SELECT hex(@a:=convert(_utf32 0x10400 using utf8mb4) collate utf8mb4_test_400_ci), hex(lower(@a));
hex(@a:=convert(_utf32 0x10400 using utf8mb4) collate utf8mb4_test_400_ci) hex(lower(@a))
F0909080 F0909080
SELECT hex(@a:=convert(_utf32 0x10428 using utf8mb4) collate utf8mb4_test_400_ci), hex(upper(@a));
hex(@a:=convert(_utf32 0x10428 using utf8mb4) collate utf8mb4_test_400_ci) hex(upper(@a))
F09090A8 F09090A8
SELECT hex(@a:=convert(_utf32 0x2C00 using utf8mb4) collate utf8mb4_test_400_ci), hex(lower(@a));
hex(@a:=convert(_utf32 0x2C00 using utf8mb4) collate utf8mb4_test_400_ci) hex(lower(@a))
E2B080 E2B080
SELECT hex(@a:=convert(_utf32 0x2C30 using utf8mb4) collate utf8mb4_test_400_ci), hex(upper(@a));
hex(@a:=convert(_utf32 0x2C30 using utf8mb4) collate utf8mb4_test_400_ci) hex(upper(@a))
E2B0B0 E2B0B0
#
# WL#5624 Collation customization improvements
#
SET NAMES utf8 COLLATE utf8_5624_1;
CREATE TABLE t1 AS SELECT REPEAT(' ', 16) AS a LIMIT 0;
INSERT INTO t1 VALUES ('012345'),('001234'),('000123'),('000012'),('000001');
INSERT INTO t1 VALUES ('12345'),('01234'),('00123'),('00012'),('00001');
INSERT INTO t1 VALUES ('1234'),('0123'),('0012'),('0001');
INSERT INTO t1 VALUES ('123'),('012'),('001');
INSERT INTO t1 VALUES ('12'),('01');
INSERT INTO t1 VALUES ('1'),('9');
INSERT INTO t1 VALUES ('ГАИ'),('ГИБДД');
INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e');
INSERT INTO t1 VALUES ('cz'),('Ċ'),('ċ');
INSERT INTO t1 VALUES ('f'),('fz'),('g'),('Ġ'),('ġ');
INSERT INTO t1 VALUES ('h'),('hz'),('GĦ'),('Għ'),('gĦ'),('għ');
INSERT INTO t1 VALUES ('i'),('iz'),('Ħ'),('ħ');
INSERT INTO t1 VALUES ('y'),('yz'),('z'),('Ż'),('ż');
INSERT INTO t1 VALUES ('ā'),('Ā'),('á'),('Á'),('à'),('À');
INSERT INTO t1 VALUES ('ē'),('é'),('ě'),('ê'),('Ē'),('É'),('Ě'),('Ê');
INSERT INTO t1 VALUES ('a'),('~'),('!'),('@'),('#'),('$'),('%'),('^');
INSERT INTO t1 VALUES ('('),(')'),('-'),('+'),('|'),('='),(':'),(';');
INSERT INTO t1 VALUES ('"'),('\''),('?');
INSERT INTO t1 VALUES ('ch'),('k'),('cs'),('ccs'),('cscs');
INSERT INTO t1 VALUES ('aa-'),('ab-'),('ac-'),('ad-'),('ae-'),('af-'),('az-');
INSERT INTO t1 VALUES ('lp-fni'),('lp-lni');
INSERT INTO t1 VALUES ('lp-fpi'),('lp-lpi');
INSERT INTO t1 VALUES ('lp-fsi'),('lp-lsi');
INSERT INTO t1 VALUES ('lp-fti'),('lp-lti');
INSERT INTO t1 VALUES ('lp-ft'),('lp-lt');
INSERT INTO t1 VALUES ('lp-fv'),('lp-lv');
INSERT INTO t1 VALUES ('lb-fni'),('lb-lni');
INSERT INTO t1 VALUES ('lb-fv'),('lb-lv');
INSERT INTO t1 VALUES (_ucs2 0x3106),(_ucs2 0x3110), (_ucs2 0x3111), (_ucs2 0x3112);
INSERT INTO t1 VALUES (_ucs2 0x32A3), (_ucs2 0x3231);
INSERT INTO t1 VALUES (_ucs2 0x84D9), (_ucs2 0x98F5), (_ucs2 0x7CF3), (_ucs2 0x5497);
SELECT a, HEX(WEIGHT_STRING(a)) FROM t1 ORDER BY a, LENGTH(a), BINARY a;
a HEX(WEIGHT_STRING(a))
lp-ft 0001
lp-lt 0001
lp-fpi 0001
lp-fsi 0001
lp-fti 0001
lp-lpi 0001
lp-lsi 0001
lp-lti 0001
lb-fv 0200233E
lb-fni 0200233E
lp-fv 0202
lp-fni 0202
- 0221
= 042D
| 0430
lb-lv 0DD9233E
lp-lv 0DDB
1 0E2A
01 0E2A
001 0E2A
0001 0E2A
00001 0E2A
000001 0E2A
12 0E2A0E2B
012 0E2A0E2B
0012 0E2A0E2B
00012 0E2A0E2B
000012 0E2A0E2B
123 0E2A0E2B0E2C
0123 0E2A0E2B0E2C
00123 0E2A0E2B0E2C
000123 0E2A0E2B0E2C
1234 0E2A0E2B0E2C0E2D
01234 0E2A0E2B0E2C0E2D
001234 0E2A0E2B0E2C0E2D
12345 0E2A0E2B0E2C0E2D0E2E
012345 0E2A0E2B0E2C0E2D0E2E
9 0E32
~ 0E32233E
! 0E32233F
@ 0E322340
# 0E322341
$ 0E322342
% 0E322343
^ 0E322344
( 0E322346
) 0E322347
+ 0E322348
: 0E322349
; 0E32234A
" 0E32234B
' 0E32234C
? 0E32234D
a 0E33
a 0E33
aa- 0E330E330221
ab- 0E330E4A0E34
ac- 0E330E600E60
ad- 0E330E6D0E6D
ae- 0E330E8B0E8B
af- 0E330EB90EB9
az- 0E33106A0221
b 0E4A
À 0E4A
Á 0E4A
à 0E4A
á 0E4A
Ā 0E4A
ā 0E4A
c 0E60
k 0E600EE1
ch 0E600EE1
cs 0E600FEA
ccs 0E600FEA0E600FEA
cscs 0E600FEA0E600FEA
cz 0E60106A
Ċ 0E6C233E
ċ 0E6C233E
d 0E6D
É 0E6D
Ê 0E6D
é 0E6D
ê 0E6D
Ē 0E6D
ē 0E6D
Ě 0E6D
ě 0E6D
e 0E8B
f 0EB9
fz 0EB9106A
Ġ 0EC0233E
ġ 0EC0233E
g 0EC1
GĦ 0EE0233E
Għ 0EE0233E
gĦ 0EE0233E
għ 0EE0233E
h 0EE1
hz 0EE1106A
Ħ 0EFA233E
ħ 0EFA233E
i 0EFB
iz 0EFB106A
y 105E
yz 105E106A
Ż 1069233E
ż 1069233E
z 106A
ГАИ 11341114117C
ГИБДД 11341114117C
lb-lni 233C233E
lp-lni 233E
ㄆ 233F
ㄐ 2349
ㄑ 234A
ㄒ 234B
㊣ 7147
㈱ 72D5
蓙 753C
飵 753D
糳 753E
咗 753F
#
# WL#5624, the same test with UCS2
#
ALTER TABLE t1 CONVERT TO CHARACTER SET ucs2 COLLATE ucs2_5624_1;
SELECT a, HEX(WEIGHT_STRING(a)) FROM t1 ORDER BY a, LENGTH(a), BINARY(a);
a HEX(WEIGHT_STRING(a))
lp-ft 0001
lp-lt 0001
lp-fpi 0001
lp-fsi 0001
lp-fti 0001
lp-lpi 0001
lp-lsi 0001
lp-lti 0001
lb-fv 0200233E
lb-fni 0200233E
lp-fv 0202
lp-fni 0202
- 0221
= 042D
| 0430
lb-lv 0DD9233E
lp-lv 0DDB
1 0E2A
01 0E2A
001 0E2A
0001 0E2A
00001 0E2A
000001 0E2A
12 0E2A0E2B
012 0E2A0E2B
0012 0E2A0E2B
00012 0E2A0E2B
000012 0E2A0E2B
123 0E2A0E2B0E2C
0123 0E2A0E2B0E2C
00123 0E2A0E2B0E2C
000123 0E2A0E2B0E2C
1234 0E2A0E2B0E2C0E2D
01234 0E2A0E2B0E2C0E2D
001234 0E2A0E2B0E2C0E2D
12345 0E2A0E2B0E2C0E2D0E2E
012345 0E2A0E2B0E2C0E2D0E2E
9 0E32
~ 0E32233E
! 0E32233F
@ 0E322340
# 0E322341
$ 0E322342
% 0E322343
^ 0E322344
( 0E322346
) 0E322347
+ 0E322348
: 0E322349
; 0E32234A
" 0E32234B
' 0E32234C
? 0E32234D
a 0E33
a 0E33
aa- 0E330E330221
ab- 0E330E4A0E34
ac- 0E330E600E60
ad- 0E330E6D0E6D
ae- 0E330E8B0E8B
af- 0E330EB90EB9
az- 0E33106A0221
b 0E4A
À 0E4A
Á 0E4A
à 0E4A
á 0E4A
Ā 0E4A
ā 0E4A
c 0E60
k 0E600EE1
ch 0E600EE1
cs 0E600FEA
ccs 0E600FEA0E600FEA
cscs 0E600FEA0E600FEA
cz 0E60106A
Ċ 0E6C233E
ċ 0E6C233E
d 0E6D
É 0E6D
Ê 0E6D
é 0E6D
ê 0E6D
Ē 0E6D
ē 0E6D
Ě 0E6D
ě 0E6D
e 0E8B
f 0EB9
fz 0EB9106A
Ġ 0EC0233E
ġ 0EC0233E
g 0EC1
GĦ 0EE0233E
Għ 0EE0233E
gĦ 0EE0233E
għ 0EE0233E
h 0EE1
hz 0EE1106A
Ħ 0EFA233E
ħ 0EFA233E
i 0EFB
iz 0EFB106A
y 105E
yz 105E106A
Ż 1069233E
ż 1069233E
z 106A
ГАИ 11341114117C
ГИБДД 11341114117C
lb-lni 233C233E
lp-lni 233E
ㄆ 233F
ㄐ 2349
ㄑ 234A
ㄒ 234B
㊣ 7147
㈱ 72D5
蓙 753C
飵 753D
糳 753E
咗 753F
DROP TABLE t1;
#
# WL#5624, unsupported features
#
SET NAMES utf8 COLLATE utf8_5624_2;
ERROR HY000: Unknown collation: 'utf8_5624_2'
SHOW WARNINGS;
Level Code Message
Error 1273 Unknown collation: 'utf8_5624_2'
Warning 1273 Syntax error at '[strength tertiary]' for COLLATION : utf8_5624_2
#
# WL#5624, reset before primary ignorable
#
SET NAMES utf8 COLLATE utf8_5624_3;
ERROR HY000: Unknown collation: 'utf8_5624_3'
SHOW WARNINGS;
Level Code Message
Error 1273 Unknown collation: 'utf8_5624_3'
Warning 1273 Can't reset before a primary ignorable character U+A48C
#
# WL#5624, \u without hex digits is equal to {'\', 'u'}
#
SET NAMES utf8 COLLATE utf8_5624_4;
CREATE TABLE t1 AS SELECT REPEAT(' ', 10) AS a LIMIT 0;
INSERT INTO t1 VALUES ('\\'),('u'),('x'),('X');
SELECT a, HEX(WEIGHT_STRING(a)) FROM t1 ORDER BY a, LENGTH(a), BINARY(a);
a HEX(WEIGHT_STRING(a))
\ 02CE
x 02CE101F
u 101F
X 105A
DROP TABLE t1;
#
# WL#5624, testing Bengali collations
#
SET NAMES utf8, collation_connection=utf8_bengali_standard_ci;
CREATE TABLE t1 AS SELECT REPEAT (' ', 10) AS a LIMIT 0;
INSERT INTO t1 VALUES (_ucs2 0x09FA), (_ucs2 0x09F8), (_ucs2 0x09F9), (_ucs2 0x09F2);
INSERT INTO t1 VALUES (_ucs2 0x09DC), (_ucs2 0x09A109BC);
INSERT INTO t1 VALUES (_ucs2 0x09A2), (_ucs2 0x09DD), (_ucs2 0x09A209BC);
INSERT INTO t1 VALUES (_ucs2 0x09A3);
SELECT HEX(WEIGHT_STRING(a)), HEX(CONVERT(a USING ucs2)), HEX(a)
FROM t1 ORDER BY a, BINARY a;
HEX(WEIGHT_STRING(a)) HEX(CONVERT(a USING ucs2)) HEX(a)
0350 09FA E0A7BA
0351 09F8 E0A7B8
0352 09F9 E0A7B9
0353 09F2 E0A7B2
0374 09A109BC E0A6A1E0A6BC
0374 09DC E0A79C
0375 09A2 E0A6A2
0376 09A209BC E0A6A2E0A6BC
0376 09DD E0A79D
0377 09A3 E0A6A3
DROP TABLE t1;
SET NAMES utf8, collation_connection=utf8_bengali_traditional_ci;
CREATE TABLE t1 AS SELECT REPEAT (' ', 10) AS a LIMIT 0;
INSERT INTO t1 VALUES
(_ucs2 0x0985),(_ucs2 0x0986),(_ucs2 0x0987),(_ucs2 0x0988),
(_ucs2 0x0989),(_ucs2 0x098A),(_ucs2 0x098B),(_ucs2 0x09E0),
(_ucs2 0x098C),(_ucs2 0x09E1),(_ucs2 0x098F),(_ucs2 0x0990),
(_ucs2 0x0993);
INSERT INTO t1 VALUES
(_ucs2 0x0994),(_ucs2 0x0982),(_ucs2 0x0983),(_ucs2 0x0981),
(_ucs2 0x099509CD), (_ucs2 0x099609CD), (_ucs2 0x099709CD), (_ucs2 0x099809CD),
(_ucs2 0x099909CD), (_ucs2 0x099A09CD), (_ucs2 0x099B09CD), (_ucs2 0x099C09CD),
(_ucs2 0x099D09CD), (_ucs2 0x099E09CD), (_ucs2 0x099F09CD), (_ucs2 0x09A009CD),
(_ucs2 0x09A109CD), (_ucs2 0x09A209CD), (_ucs2 0x09A309CD),
(_ucs2 0x09CE), (_ucs2 0x09A409CD200D), (_ucs2 0x09A409CD),
(_ucs2 0x09A509CD),(_ucs2 0x09A609CD),
(_ucs2 0x09A709CD), (_ucs2 0x09A809CD), (_ucs2 0x09AA09CD), (_ucs2 0x09AB09CD),
(_ucs2 0x09AC09CD), (_ucs2 0x09AD09CD), (_ucs2 0x09AE09CD), (_ucs2 0x09AF09CD),
(_ucs2 0x09B009CD), (_ucs2 0x09F009CD), (_ucs2 0x09B209CD), (_ucs2 0x09F109CD),
(_ucs2 0x09B609CD), (_ucs2 0x09B709CD), (_ucs2 0x09B809CD), (_ucs2 0x09B909CD);
INSERT INTO t1 VALUES
(_ucs2 0x099509CD0985),(_ucs2 0x0995),
(_ucs2 0x099509CD0986),(_ucs2 0x099509BE),
(_ucs2 0x099509CD0987),(_ucs2 0x099509BF),
(_ucs2 0x099509CD0988),(_ucs2 0x099509C0),
(_ucs2 0x099509CD0989),(_ucs2 0x099509C1),
(_ucs2 0x099509CD098A),(_ucs2 0x099509C2),
(_ucs2 0x099509CD098B),(_ucs2 0x099509C3),
(_ucs2 0x099509CD09E0),(_ucs2 0x099509C4),
(_ucs2 0x099509CD098C),(_ucs2 0x099509E2),
(_ucs2 0x099509CD09E1),(_ucs2 0x099509E3),
(_ucs2 0x099509CD098F),(_ucs2 0x099509C7),
(_ucs2 0x099509CD0990),(_ucs2 0x099509C8),
(_ucs2 0x099509CD0993),(_ucs2 0x099509CB),
(_ucs2 0x099509CD0994),(_ucs2 0x099509CC);
SELECT HEX(WEIGHT_STRING(a)), HEX(CONVERT(a USING ucs2)), HEX(a)
FROM t1 ORDER BY a, BINARY(a);
HEX(WEIGHT_STRING(a)) HEX(CONVERT(a USING ucs2)) HEX(a)
15A2 0985 E0A685
15A3 0986 E0A686
15A4 0987 E0A687
15A5 0988 E0A688
15A6 0989 E0A689
15A7 098A E0A68A
15A8 098B E0A68B
15A9 09E0 E0A7A0
15AA 098C E0A68C
15AB 09E1 E0A7A1
15AC 098F E0A68F
15AD 0990 E0A690
15AE 0993 E0A693
15AF 0994 E0A694
15B0 0982 E0A682
15B1 0983 E0A683
15B2 0981 E0A681
15B3 099509CD E0A695E0A78D
15B315A2 0995 E0A695
15B315A2 099509CD0985 E0A695E0A78DE0A685
15B315A3 099509BE E0A695E0A6BE
15B315A3 099509CD0986 E0A695E0A78DE0A686
15B315A4 099509BF E0A695E0A6BF
15B315A4 099509CD0987 E0A695E0A78DE0A687
15B315A5 099509C0 E0A695E0A780
15B315A5 099509CD0988 E0A695E0A78DE0A688
15B315A6 099509C1 E0A695E0A781
15B315A6 099509CD0989 E0A695E0A78DE0A689
15B315A7 099509C2 E0A695E0A782
15B315A7 099509CD098A E0A695E0A78DE0A68A
15B315A8 099509C3 E0A695E0A783
15B315A8 099509CD098B E0A695E0A78DE0A68B
15B315A9 099509C4 E0A695E0A784
15B315A9 099509CD09E0 E0A695E0A78DE0A7A0
15B315AA 099509CD098C E0A695E0A78DE0A68C
15B315AA 099509E2 E0A695E0A7A2
15B315AB 099509CD09E1 E0A695E0A78DE0A7A1
15B315AB 099509E3 E0A695E0A7A3
15B315AC 099509C7 E0A695E0A787
15B315AC 099509CD098F E0A695E0A78DE0A68F
15B315AD 099509C8 E0A695E0A788
15B315AD 099509CD0990 E0A695E0A78DE0A690
15B315AE 099509CB E0A695E0A78B
15B315AE 099509CD0993 E0A695E0A78DE0A693
15B315AF 099509CC E0A695E0A78C
15B315AF 099509CD0994 E0A695E0A78DE0A694
15B4 099609CD E0A696E0A78D
15B5 099709CD E0A697E0A78D
15B6 099809CD E0A698E0A78D
15B7 099909CD E0A699E0A78D
15B8 099A09CD E0A69AE0A78D
15B9 099B09CD E0A69BE0A78D
15BA 099C09CD E0A69CE0A78D
15BB 099D09CD E0A69DE0A78D
15BC 099E09CD E0A69EE0A78D
15BD 099F09CD E0A69FE0A78D
15BE 09A009CD E0A6A0E0A78D
15BF 09A109CD E0A6A1E0A78D
15C0 09A209CD E0A6A2E0A78D
15C1 09A309CD E0A6A3E0A78D
15C2 09A409CD E0A6A4E0A78D
15C2 09A409CD200D E0A6A4E0A78DE2808D
15C2 09CE E0A78E
15C3 09A509CD E0A6A5E0A78D
15C4 09A609CD E0A6A6E0A78D
15C5 09A709CD E0A6A7E0A78D
15C6 09A809CD E0A6A8E0A78D
15C7 09AA09CD E0A6AAE0A78D
15C8 09AB09CD E0A6ABE0A78D
15C9 09AC09CD E0A6ACE0A78D
15CA 09AD09CD E0A6ADE0A78D
15CB 09AE09CD E0A6AEE0A78D
15CC 09AF09CD E0A6AFE0A78D
15CD 09B009CD E0A6B0E0A78D
15CE 09F009CD E0A7B0E0A78D
15CF 09B209CD E0A6B2E0A78D
15D0 09F109CD E0A7B1E0A78D
15D1 09B609CD E0A6B6E0A78D
15D2 09B709CD E0A6B7E0A78D
15D3 09B809CD E0A6B8E0A78D
15D4 09B909CD E0A6B9E0A78D
SELECT HEX(WEIGHT_STRING(a)) as wa,
GROUP_CONCAT(HEX(CONVERT(a USING ucs2)) ORDER BY LENGTH(a), BINARY a)
FROM t1 GROUP BY a ORDER BY a;
wa GROUP_CONCAT(HEX(CONVERT(a USING ucs2)) ORDER BY LENGTH(a), BINARY a)
15A2 0985
15A3 0986
15A4 0987
15A5 0988
15A6 0989
15A7 098A
15A8 098B
15A9 09E0
15AA 098C
15AB 09E1
15AC 098F
15AD 0990
15AE 0993
15AF 0994
15B0 0982
15B1 0983
15B2 0981
15B3 099509CD
15B315A2 0995,099509CD0985
15B315A3 099509BE,099509CD0986
15B315A4 099509BF,099509CD0987
15B315A5 099509C0,099509CD0988
15B315A6 099509C1,099509CD0989
15B315A7 099509C2,099509CD098A
15B315A8 099509C3,099509CD098B
15B315A9 099509C4,099509CD09E0
15B315AA 099509E2,099509CD098C
15B315AB 099509E3,099509CD09E1
15B315AC 099509C7,099509CD098F
15B315AD 099509C8,099509CD0990
15B315AE 099509CB,099509CD0993
15B315AF 099509CC,099509CD0994
15B4 099609CD
15B5 099709CD
15B6 099809CD
15B7 099909CD
15B8 099A09CD
15B9 099B09CD
15BA 099C09CD
15BB 099D09CD
15BC 099E09CD
15BD 099F09CD
15BE 09A009CD
15BF 09A109CD
15C0 09A209CD
15C1 09A309CD
15C2 09CE,09A409CD,09A409CD200D
15C3 09A509CD
15C4 09A609CD
15C5 09A709CD
15C6 09A809CD
15C7 09AA09CD
15C8 09AB09CD
15C9 09AC09CD
15CA 09AD09CD
15CB 09AE09CD
15CC 09AF09CD
15CD 09B009CD
15CE 09F009CD
15CF 09B209CD
15D0 09F109CD
15D1 09B609CD
15D2 09B709CD
15D3 09B809CD
15D4 09B909CD
DROP TABLE t1;
#
# WL#5624, shift after, using expansion
#
SET NAMES utf8 COLLATE utf8_5624_5;
CREATE TABLE t1 AS SELECT REPEAT(' ', 10) AS a LIMIT 0;
INSERT INTO t1 VALUES ('0'),('1'),('0z'),(_ucs2 0x0030FF9D);
INSERT INTO t1 VALUES ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i');
INSERT INTO t1 VALUES ('j'),('k'),('l'),('m'),('n'),('o'),('p'),('q'),('r');
INSERT INTO t1 VALUES ('s'),('t'),('u'),('v'),('w'),('x'),('y'),('z');
INSERT INTO t1 VALUES ('aa'),('aaa');
INSERT INTO t1 VALUES ('A'),('B'),('C'),('D'),('E'),('F'),('G'),('H'),('I');
INSERT INTO t1 VALUES ('J'),('K'),('L'),('M'),('N'),('O'),('P'),('Q'),('R');
INSERT INTO t1 VALUES ('S'),('T'),('U'),('V'),('W'),('X'),('Y'),('Z');
INSERT INTO t1 VALUES ('AA'),('AAA');
SELECT a, HEX(WEIGHT_STRING(a)) FROM t1 ORDER BY a, LENGTH(a), BINARY(a);
a HEX(WEIGHT_STRING(a))
0 0E29
0z 0E290E292357
0ﾝ 0E291E81
a 0E29233E
b 0E29233F
c 0E292340
d 0E292341
e 0E292342
f 0E292343
g 0E292344
h 0E292345
i 0E292346
j 0E292347
k 0E292348
l 0E292349
m 0E29234A
n 0E29234B
o 0E29234C
p 0E29234D
q 0E29234E
r 0E29234F
s 0E292350
t 0E292351
u 0E292352
v 0E292353
w 0E292354
x 0E292355
y 0E292356
z 0E292357
aa 0E292358
aaa 0E292359
A 0E29333E
B 0E29333F
C 0E293340
D 0E293341
E 0E293342
F 0E293343
G 0E293344
H 0E293345
I 0E293346
J 0E293347
K 0E293348
L 0E293349
M 0E29334A
N 0E29334B
O 0E29334C
P 0E29334D
Q 0E29334E
R 0E29334F
S 0E293350
T 0E293351
U 0E293352
V 0E293353
W 0E293354
X 0E293355
Y 0E293356
Z 0E293357
AA 0E293358
AAA 0E293359
1 0E2A
DROP TABLE t1;
#
# End of WL#5624
#
#
# Bug#14197426 PARSE ERRORS IN LOADABLE UCA / LDML COLLATIONS ARE SILENTLY IGNORED
#
# Search for occurrences of [ERROR] Syntax error at '[strength tertiary]'
Occurances : 1