--source include/have_ucs2.inc --source include/have_utf8mb4.inc --source include/have_utf16.inc --source include/have_utf32.inc --disable_warnings drop table if exists t1; --enable_warnings --echo In the following tests we change the order of letter "b" --echo making it equal to letter "a", and check that it works --echo with all Unicode character sets set names utf8; --replace_result $MYSQL_TEST_DIR MYSQL_TEST_DIR show variables like 'character_sets_dir%'; show collation like 'utf8_phone_ci'; 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; SELECT * FROM t1 WHERE phone='+7(912)800-80-01'; SELECT * FROM t1 WHERE phone='79128008001'; SELECT * FROM t1 WHERE phone='7 9 1 2 8 0 0 8 0 0 1'; DROP TABLE t1; show collation like 'utf8_test_ci'; create table t1 (c1 char(1) character set utf8 collate utf8_test_ci); insert into t1 values ('a'); select * from t1 where c1='b'; drop table t1; show collation like 'ucs2_test_ci'; create table t1 (c1 char(1) character set ucs2 collate ucs2_test_ci); insert into t1 values ('a'); select * from t1 where c1='b'; drop table t1; show collation like 'utf8mb4_test_ci'; create table t1 (c1 char(1) character set utf8mb4 collate utf8mb4_test_ci); insert into t1 values ('a'); select * from t1 where c1='b'; drop table t1; show collation like 'utf16_test_ci'; create table t1 (c1 char(1) character set utf16 collate utf16_test_ci); insert into t1 values ('a'); select * from t1 where c1='b'; drop table t1; show collation like 'utf32_test_ci'; create table t1 (c1 char(1) character set utf32 collate utf32_test_ci); insert into t1 values ('a'); select * from t1 where c1='b'; drop table t1; # # Bug#41084 full-text index added to custom UCA collation not working # 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'); SELECT * FROM t1 where match (col1) against ('abcd' IN BOOLEAN MODE); ALTER TABLE t1 ADD (col2 varchar(100) character set latin1); UPDATE t1 SET col2=col1; SELECT * FROM t1 WHERE col1=col2 ORDER BY col1; DROP TABLE t1; --echo # --echo # Bug#45645 Mysql server close all connection and restart using lower function --echo # 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'); DROP TABLE t1; --echo # --echo # Bug#51976 LDML collations issue (cyrillic example) --echo # 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; DROP TABLE t1; --echo # --echo # Bug#43827 Server closes connections and restarts --echo # # Crash happened with a user-defined utf8 collation, # on attempt to insert a string longer than the column can store. CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_test_ci); INSERT INTO t1 SELECT REPEAT('a',11); DROP TABLE t1; # # Vietnamese experimental collation # --echo Vietnamese experimental collation show collation like 'ucs2_vn_ci'; 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; select group_concat(hex(c1) order by hex(c1)) from t1 group by c1; select group_concat(c1 order by hex(c1) SEPARATOR '') from t1 group by c1; drop table t1; --echo Bug#46448 trailing spaces are not ignored when user collation maps space != 0x20 set names latin1; show collation like 'latin1_test'; select "foo" = "foo " collate latin1_test; -- echo The following tests check that two-byte collation IDs work # The file ../std-data/Index.xml has a number of collations with high IDs. # Test that the "ID" column in I_S and SHOW queries can handle two bytes select * from information_schema.collations where id>256 order by id; show collation like '%test%'; # Test that two-byte collation ID is correctly transfered to the client side. show collation like 'ucs2_vn_ci'; create table t1 (c1 char(1) character set ucs2 collate ucs2_vn_ci); insert into t1 values (0x0061); --enable_metadata set @@character_set_results=NULL; select * from t1; --disable_metadata drop table t1; # # Check maximum collation ID (2047 as of MySQL-6.0.9) # 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; DROP TABLE t1; # # Bug#47756 Setting 2byte collation ID with 'set names' crashes the server # SET NAMES utf8 COLLATE utf8_phone_ci; SHOW COLLATION LIKE 'utf8_phone_ci'; SET NAMES utf8;