path: root/mysql-test/r/ctype_ldml.result

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;
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#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
utf8_phone_ci	utf8	352			8
utf8_test_ci	utf8	353			8
ucs2_test_ci	ucs2	358			8
ucs2_vn_ci	ucs2	359			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
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