B4BY.588
Home
Terminal
Upload
information
Create File
Create Folder
About
Tools
:
/
usr
/
share
/
mysql-test
/
r
/
Filename :
ctype_utf16.result
back
Copy
DROP TABLE IF EXISTS t1; # # Start of 5.5 tests # SET NAMES latin1; SET character_set_connection=utf16; select hex('a'), hex('a '); hex('a') hex('a ') 0061 00610020 select 'a' = 'a', 'a' = 'a ', 'a ' = 'a'; 'a' = 'a' 'a' = 'a ' 'a ' = 'a' 1 1 1 select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a'; 'a\0' = 'a' 'a\0' < 'a' 'a\0' > 'a' 0 1 0 select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0'; 'a' = 'a\0' 'a' < 'a\0' 'a' > 'a\0' 0 0 1 select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a '; 'a\0' = 'a ' 'a\0' < 'a ' 'a\0' > 'a ' 0 1 0 select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0'; 'a ' = 'a\0' 'a ' < 'a\0' 'a ' > 'a\0' 0 0 1 select 'a a' > 'a', 'a \0' < 'a'; 'a a' > 'a' 'a \0' < 'a' 1 1 select binary 'a a' > 'a', binary 'a \0' > 'a', binary 'a\0' > 'a'; binary 'a a' > 'a' binary 'a \0' > 'a' binary 'a\0' > 'a' 1 1 1 select hex(_utf16 0x44); hex(_utf16 0x44) 0044 select hex(_utf16 0x3344); hex(_utf16 0x3344) 3344 select hex(_utf16 0x113344); hex(_utf16 0x113344) 00113344 CREATE TABLE t1 (word VARCHAR(64), word2 CHAR(64)) CHARACTER SET utf16; INSERT INTO t1 VALUES (_koi8r 0xF2, _koi8r 0xF2), (X'2004',X'2004'); SELECT hex(word) FROM t1 ORDER BY word; hex(word) 0420 2004 SELECT hex(word2) FROM t1 ORDER BY word2; hex(word2) 0420 2004 DELETE FROM t1; INSERT INTO t1 VALUES (X'042000200020',X'042000200020'), (X'200400200020', X'200400200020'); SELECT hex(word) FROM t1 ORDER BY word; hex(word) 042000200020 200400200020 SELECT hex(word2) FROM t1 ORDER BY word2; hex(word2) 0420 2004 DROP TABLE t1; SELECT hex(LPAD(_utf16 X'0420',10,_utf16 X'0421')); hex(LPAD(_utf16 X'0420',10,_utf16 X'0421')) 0421042104210421042104210421042104210420 SELECT hex(LPAD(_utf16 X'0420',10,_utf16 X'04210422')); hex(LPAD(_utf16 X'0420',10,_utf16 X'04210422')) 0421042204210422042104220421042204210420 SELECT hex(LPAD(_utf16 X'0420',10,_utf16 X'042104220423')); hex(LPAD(_utf16 X'0420',10,_utf16 X'042104220423')) 0421042204230421042204230421042204230420 SELECT hex(LPAD(_utf16 X'0420042104220423042404250426042704280429042A042B',10,_utf16 X'042104220423')); hex(LPAD(_utf16 X'0420042104220423042404250426042704280429042A042B',10,_utf16 X'042104220423')) 0420042104220423042404250426042704280429 SELECT hex(LPAD(_utf16 X'D800DC00', 10, _utf16 X'0421')); hex(LPAD(_utf16 X'D800DC00', 10, _utf16 X'0421')) 042104210421042104210421042104210421D800DC00 SELECT hex(LPAD(_utf16 X'0421', 10, _utf16 X'D800DC00')); hex(LPAD(_utf16 X'0421', 10, _utf16 X'D800DC00')) D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC000421 SELECT hex(RPAD(_utf16 X'0420',10,_utf16 X'0421')); hex(RPAD(_utf16 X'0420',10,_utf16 X'0421')) 0420042104210421042104210421042104210421 SELECT hex(RPAD(_utf16 X'0420',10,_utf16 X'04210422')); hex(RPAD(_utf16 X'0420',10,_utf16 X'04210422')) 0420042104220421042204210422042104220421 SELECT hex(RPAD(_utf16 X'0420',10,_utf16 X'042104220423')); hex(RPAD(_utf16 X'0420',10,_utf16 X'042104220423')) 0420042104220423042104220423042104220423 SELECT hex(RPAD(_utf16 X'0420042104220423042404250426042704280429042A042B',10,_utf16 X'042104220423')); hex(RPAD(_utf16 X'0420042104220423042404250426042704280429042A042B',10,_utf16 X'042104220423')) 0420042104220423042404250426042704280429 SELECT hex(RPAD(_utf16 X'D800DC00', 10, _utf16 X'0421')); hex(RPAD(_utf16 X'D800DC00', 10, _utf16 X'0421')) D800DC00042104210421042104210421042104210421 SELECT hex(RPAD(_utf16 X'0421', 10, _utf16 X'D800DC00')); hex(RPAD(_utf16 X'0421', 10, _utf16 X'D800DC00')) 0421D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00 CREATE TABLE t1 SELECT LPAD(_utf16 X'0420',10,_utf16 X'0421') l, RPAD(_utf16 X'0420',10,_utf16 X'0421') r; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `l` varchar(10) CHARACTER SET utf16 NOT NULL DEFAULT '', `r` varchar(10) CHARACTER SET utf16 NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 select hex(l), hex(r) from t1; hex(l) hex(r) 0421042104210421042104210421042104210420 0420042104210421042104210421042104210421 DROP TABLE t1; create table t1 (f1 char(30)); insert into t1 values ("103000"), ("22720000"), ("3401200"), ("78000"); select lpad(f1, 12, "-o-/") from t1; lpad(f1, 12, "-o-/") -o-/-o103000 -o-/22720000 -o-/-3401200 -o-/-o-78000 drop table t1; SET NAMES latin1; SET character_set_connection=utf16; select @@collation_connection; @@collation_connection utf16_general_ci create table t1 as select repeat(' ',10) as a union select null; alter table t1 add key(a); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) CHARACTER SET utf16 DEFAULT NULL, KEY `a` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); explain select * from t1 where a like 'abc%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 43 NULL 1 Using where; Using index explain select * from t1 where a like concat('abc','%'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 43 NULL 1 Using where; Using index select * from t1 where a like "abc%"; a abc abcd select * from t1 where a like concat("abc","%"); a abc abcd select * from t1 where a like "ABC%"; a abc abcd select * from t1 where a like "test%"; a test select * from t1 where a like "te_t"; a test select * from t1 where a like "%a%"; a a abc abcd select * from t1 where a like "%abcd%"; a abcd select * from t1 where a like "%abc\d%"; a abcd drop table t1; select 'AA' like 'AA'; 'AA' like 'AA' 1 select 'AA' like 'A%A'; 'AA' like 'A%A' 1 select 'AA' like 'A%%A'; 'AA' like 'A%%A' 1 select 'AA' like 'AA%'; 'AA' like 'AA%' 1 select 'AA' like '%AA%'; 'AA' like '%AA%' 1 select 'AA' like '%A'; 'AA' like '%A' 1 select 'AA' like '%AA'; 'AA' like '%AA' 1 select 'AA' like 'A%A%'; 'AA' like 'A%A%' 1 select 'AA' like '_%_%'; 'AA' like '_%_%' 1 select 'AA' like '%A%A'; 'AA' like '%A%A' 1 select 'AAA'like 'A%A%A'; 'AAA'like 'A%A%A' 1 select 'AZ' like 'AZ'; 'AZ' like 'AZ' 1 select 'AZ' like 'A%Z'; 'AZ' like 'A%Z' 1 select 'AZ' like 'A%%Z'; 'AZ' like 'A%%Z' 1 select 'AZ' like 'AZ%'; 'AZ' like 'AZ%' 1 select 'AZ' like '%AZ%'; 'AZ' like '%AZ%' 1 select 'AZ' like '%Z'; 'AZ' like '%Z' 1 select 'AZ' like '%AZ'; 'AZ' like '%AZ' 1 select 'AZ' like 'A%Z%'; 'AZ' like 'A%Z%' 1 select 'AZ' like '_%_%'; 'AZ' like '_%_%' 1 select 'AZ' like '%A%Z'; 'AZ' like '%A%Z' 1 select 'AZ' like 'A_'; 'AZ' like 'A_' 1 select 'AZ' like '_Z'; 'AZ' like '_Z' 1 select 'AMZ'like 'A%M%Z'; 'AMZ'like 'A%M%Z' 1 SET NAMES utf8; SET character_set_connection=utf16; CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf16); INSERT INTO t1 VALUES ('фыва'),('Фыва'),('фЫва'),('фыВа'),('фывА'),('ФЫВА'); INSERT INTO t1 VALUES ('фывапролдж'),('Фывапролдж'),('фЫвапролдж'),('фыВапролдж'); INSERT INTO t1 VALUES ('фывАпролдж'),('фываПролдж'),('фывапРолдж'),('фывапрОлдж'); INSERT INTO t1 VALUES ('фывапроЛдж'),('фывапролДж'),('фывапролдЖ'),('ФЫВАПРОЛДЖ'); SELECT * FROM t1 WHERE a LIKE '%фЫва%' ORDER BY BINARY a; a ФЫВА ФЫВАПРОЛДЖ Фыва Фывапролдж фЫва фЫвапролдж фыВа фыВапролдж фывА фывАпролдж фыва фываПролдж фывапРолдж фывапрОлдж фывапроЛдж фывапролДж фывапролдЖ фывапролдж SELECT * FROM t1 WHERE a LIKE '%фЫв%' ORDER BY BINARY a; a ФЫВА ФЫВАПРОЛДЖ Фыва Фывапролдж фЫва фЫвапролдж фыВа фыВапролдж фывА фывАпролдж фыва фываПролдж фывапРолдж фывапрОлдж фывапроЛдж фывапролДж фывапролдЖ фывапролдж SELECT * FROM t1 WHERE a LIKE 'фЫва%' ORDER BY BINARY a; a ФЫВА ФЫВАПРОЛДЖ Фыва Фывапролдж фЫва фЫвапролдж фыВа фыВапролдж фывА фывАпролдж фыва фываПролдж фывапРолдж фывапрОлдж фывапроЛдж фывапролДж фывапролдЖ фывапролдж SELECT * FROM t1 WHERE a LIKE 'фЫва%' COLLATE utf16_bin ORDER BY BINARY a; a фЫва фЫвапролдж DROP TABLE t1; CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word)) ENGINE=MyISAM CHARACTER SET utf16; INSERT INTO t1 (word) VALUES ("cat"); SELECT * FROM t1 WHERE word LIKE "c%"; word cat SELECT * FROM t1 WHERE word LIKE "ca_"; word cat SELECT * FROM t1 WHERE word LIKE "cat"; word cat SELECT * FROM t1 WHERE word LIKE _utf16 x'00630025'; word cat SELECT * FROM t1 WHERE word LIKE _utf16 x'00630061005F'; word cat DROP TABLE t1; select insert(_utf16 0x006100620063,10,2,_utf16 0x006400650066); insert(_utf16 0x006100620063,10,2,_utf16 0x006400650066) abc select insert(_utf16 0x006100620063,1,2,_utf16 0x006400650066); insert(_utf16 0x006100620063,1,2,_utf16 0x006400650066) defc SET NAMES latin1; CREATE TABLE t1 ( word VARCHAR(64), bar INT(11) default 0, PRIMARY KEY (word)) ENGINE=MyISAM CHARSET utf16 COLLATE utf16_general_ci ; INSERT INTO t1 (word) VALUES ("aar"); INSERT INTO t1 (word) VALUES ("a"); INSERT INTO t1 (word) VALUES ("aardvar"); INSERT INTO t1 (word) VALUES ("aardvark"); INSERT INTO t1 (word) VALUES ("aardvara"); INSERT INTO t1 (word) VALUES ("aardvarz"); EXPLAIN SELECT * FROM t1 ORDER BY word; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort SELECT * FROM t1 ORDER BY word; word bar a 0 aar 0 aardvar 0 aardvara 0 aardvark 0 aardvarz 0 EXPLAIN SELECT word FROM t1 ORDER BY word; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 258 NULL 6 Using index SELECT word FROM t1 ORDER by word; word a aar aardvar aardvara aardvark aardvarz DROP TABLE t1; CREATE TABLE t1 ( word VARCHAR(64) , PRIMARY KEY (word)) ENGINE=MyISAM CHARSET utf16 COLLATE utf16_general_ci; INSERT INTO t1 (word) VALUES ("aar"); INSERT INTO t1 (word) VALUES ("a"); INSERT INTO t1 (word) VALUES ("aardvar"); INSERT INTO t1 (word) VALUES ("aardvark"); INSERT INTO t1 (word) VALUES ("aardvara"); INSERT INTO t1 (word) VALUES ("aardvarz"); EXPLAIN SELECT * FROM t1 ORDER BY WORD; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 258 NULL 6 Using index SELECT * FROM t1 ORDER BY word; word a aar aardvar aardvara aardvark aardvarz DROP TABLE t1; CREATE TABLE t1 ( word TEXT, bar INT(11) AUTO_INCREMENT, PRIMARY KEY (bar)) ENGINE=MyISAM CHARSET utf16 COLLATE utf16_general_ci ; INSERT INTO t1 (word) VALUES ("aar"); INSERT INTO t1 (word) VALUES ("a" ); INSERT INTO t1 (word) VALUES ("aardvar"); INSERT INTO t1 (word) VALUES ("aardvark"); INSERT INTO t1 (word) VALUES ("aardvara"); INSERT INTO t1 (word) VALUES ("aardvarz"); EXPLAIN SELECT * FROM t1 ORDER BY word; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort SELECT * FROM t1 ORDER BY word; word bar a 2 aar 1 aardvar 3 aardvara 5 aardvark 4 aardvarz 6 EXPLAIN SELECT word FROM t1 ORDER BY word; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort SELECT word FROM t1 ORDER BY word; word a aar aardvar aardvara aardvark aardvarz DROP TABLE t1; SELECT hex(cast(0xAA as char character set utf16)); hex(cast(0xAA as char character set utf16)) 00AA SELECT hex(convert(0xAA using utf16)); hex(convert(0xAA using utf16)) 00AA CREATE TABLE t1 (a char(10) character set utf16); INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); SELECT HEX(a) FROM t1; HEX(a) 0001 0011 0111 1111 00011111 DROP TABLE t1; CREATE TABLE t1 (a varchar(10) character set utf16); INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); SELECT HEX(a) FROM t1; HEX(a) 0001 0011 0111 1111 00011111 DROP TABLE t1; CREATE TABLE t1 (a text character set utf16); INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); SELECT HEX(a) FROM t1; HEX(a) 0001 0011 0111 1111 00011111 DROP TABLE t1; CREATE TABLE t1 (a mediumtext character set utf16); INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); SELECT HEX(a) FROM t1; HEX(a) 0001 0011 0111 1111 00011111 DROP TABLE t1; CREATE TABLE t1 (a longtext character set utf16); INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); SELECT HEX(a) FROM t1; HEX(a) 0001 0011 0111 1111 00011111 DROP TABLE t1; create table t1(a char(1)) default charset utf16; insert into t1 values ('a'),('b'),('c'); alter table t1 modify a char(5); select a, hex(a) from t1; a hex(a) a 0061 b 0062 c 0063 drop table t1; set @ivar= 1234; set @str1 = 'select ?'; set @str2 = convert(@str1 using utf16); prepare stmt1 from @str2; execute stmt1 using @ivar; ? 1234 set names utf8; create table t1 (a enum('x','y','z') character set utf16); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` enum('x','y','z') CHARACTER SET utf16 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ('x'); insert into t1 values ('y'); insert into t1 values ('z'); select a, hex(a) from t1 order by a; a hex(a) x 0078 y 0079 z 007A alter table t1 change a a enum('x','y','z','d','e','ä','ö','ü') character set utf16; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` enum('x','y','z','d','e','ä','ö','ü') CHARACTER SET utf16 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ('D'); insert into t1 values ('E '); insert into t1 values ('ä'); insert into t1 values ('ö'); insert into t1 values ('ü'); select a, hex(a) from t1 order by a; a hex(a) x 0078 y 0079 z 007A d 0064 e 0065 ä 00E4 ö 00F6 ü 00FC drop table t1; create table t1 (a set ('x','y','z','ä','ö','ü') character set utf16); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` set('x','y','z','ä','ö','ü') CHARACTER SET utf16 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 insert into t1 values ('x'); insert into t1 values ('y'); insert into t1 values ('z'); insert into t1 values ('x,y'); insert into t1 values ('x,y,z,ä,ö,ü'); select a, hex(a) from t1 order by a; a hex(a) x 0078 y 0079 x,y 0078002C0079 z 007A x,y,z,ä,ö,ü 0078002C0079002C007A002C00E4002C00F6002C00FC drop table t1; create table t1(a enum('a','b','c')) default character set utf16; insert into t1 values('a'),('b'),('c'); alter table t1 add b char(1); show warnings; Level Code Message select * from t1 order by a; a b a NULL b NULL c NULL drop table t1; SET NAMES latin1; SET collation_connection='utf16_general_ci'; create table t1 select repeat('a',4000) a; delete from t1; insert into t1 values ('a'), ('a '), ('a\t'); select collation(a),hex(a) from t1 order by a; collation(a) hex(a) utf16_general_ci 00610009 utf16_general_ci 0061 utf16_general_ci 00610020 drop table t1; select @@collation_connection; @@collation_connection utf16_general_ci create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ; insert into t1 values('abcdef'); insert into t1 values('_bcdef'); insert into t1 values('a_cdef'); insert into t1 values('ab_def'); insert into t1 values('abc_ef'); insert into t1 values('abcd_f'); insert into t1 values('abcde_'); select c1 as c1u from t1 where c1 like 'ab\_def'; c1u ab_def select c1 as c2h from t1 where c1 like 'ab#_def' escape '#'; c2h ab_def drop table t1; SET NAMES latin1; SET collation_connection='utf16_bin'; create table t1 select repeat('a',4000) a; delete from t1; insert into t1 values ('a'), ('a '), ('a\t'); select collation(a),hex(a) from t1 order by a; collation(a) hex(a) utf16_bin 00610009 utf16_bin 0061 utf16_bin 00610020 drop table t1; # # Bug#55980 Character sets: supplementary character _bin ordering is wrong # CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a LIMIT 0; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(1) CHARACTER SET utf16 COLLATE utf16_bin NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES (_utf8mb4 0xEFBE9D),(_utf8mb4 0xF0908E84); INSERT INTO t1 VALUES (_utf8mb4 0xCE85),(_utf8mb4 0xF4808080); SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a; HEX(a) HEX(CONVERT(a USING utf8mb4)) 0385 CE85 FF9D EFBE9D D800DF84 F0908E84 DBC0DC00 F4808080 ALTER TABLE t1 ADD KEY(a); SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a; HEX(a) HEX(CONVERT(a USING utf8mb4)) 0385 CE85 FF9D EFBE9D D800DF84 F0908E84 DBC0DC00 F4808080 # Additional test for bug#37244 Character sets: short utf8_bin weight_string value SELECT HEX(a), HEX(WEIGHT_STRING(a)) FROM t1 ORDER BY a; HEX(a) HEX(WEIGHT_STRING(a)) 0385 000385 FF9D 00FF9D D800DF84 010384 DBC0DC00 100000 DROP TABLE IF EXISTS t1; # # BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES # OUT-OF-ORDER RESULTS # CREATE TABLE t1 SELECT ('a a') as n; INSERT INTO t1 VALUES('a b'); SELECT * FROM t1 ORDER BY LOWER(n) ASC; n a a a b SELECT * FROM t1 ORDER BY LOWER(n) DESC; n a b a a DROP TABLE t1; select @@collation_connection; @@collation_connection utf16_bin create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ; insert into t1 values('abcdef'); insert into t1 values('_bcdef'); insert into t1 values('a_cdef'); insert into t1 values('ab_def'); insert into t1 values('abc_ef'); insert into t1 values('abcd_f'); insert into t1 values('abcde_'); select c1 as c1u from t1 where c1 like 'ab\_def'; c1u ab_def select c1 as c2h from t1 where c1 like 'ab#_def' escape '#'; c2h ab_def drop table t1; select hex(substr(_utf16 0x00e400e50068,1)); hex(substr(_utf16 0x00e400e50068,1)) 00E400E50068 select hex(substr(_utf16 0x00e400e50068,2)); hex(substr(_utf16 0x00e400e50068,2)) 00E50068 select hex(substr(_utf16 0x00e400e50068,3)); hex(substr(_utf16 0x00e400e50068,3)) 0068 select hex(substr(_utf16 0x00e400e50068,-1)); hex(substr(_utf16 0x00e400e50068,-1)) 0068 select hex(substr(_utf16 0x00e400e50068,-2)); hex(substr(_utf16 0x00e400e50068,-2)) 00E50068 select hex(substr(_utf16 0x00e400e50068,-3)); hex(substr(_utf16 0x00e400e50068,-3)) 00E400E50068 select hex(substr(_utf16 0x00e400e5D800DC00,1)); hex(substr(_utf16 0x00e400e5D800DC00,1)) 00E400E5D800DC00 select hex(substr(_utf16 0x00e400e5D800DC00,2)); hex(substr(_utf16 0x00e400e5D800DC00,2)) 00E5D800DC00 select hex(substr(_utf16 0x00e400e5D800DC00,3)); hex(substr(_utf16 0x00e400e5D800DC00,3)) D800DC00 select hex(substr(_utf16 0x00e400e5D800DC00,-1)); hex(substr(_utf16 0x00e400e5D800DC00,-1)) D800DC00 select hex(substr(_utf16 0x00e400e5D800DC00,-2)); hex(substr(_utf16 0x00e400e5D800DC00,-2)) 00E5D800DC00 select hex(substr(_utf16 0x00e400e5D800DC00,-3)); hex(substr(_utf16 0x00e400e5D800DC00,-3)) 00E400E5D800DC00 SET NAMES latin1; create table t1 (utext varchar(20) character set utf16); insert into t1 values ("lily"); insert into t1 values ("river"); prepare stmt from 'select utext from t1 where utext like ?'; set @param1='%%'; execute stmt using @param1; utext lily river execute stmt using @param1; utext lily river select utext from t1 where utext like '%%'; utext lily river drop table t1; deallocate prepare stmt; create table t1 ( a char(10) character set utf16 not null, index a (a) ) engine=myisam; insert into t1 values (repeat(0x201f, 10)); insert into t1 values (repeat(0x2020, 10)); insert into t1 values (repeat(0x2021, 10)); explain select hex(a) from t1 order by a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 40 NULL 3 Using index select hex(a) from t1 order by a; hex(a) 201F201F201F201F201F201F201F201F201F201F 2020202020202020202020202020202020202020 2021202120212021202120212021202120212021 alter table t1 drop index a; select hex(a) from t1 order by a; hex(a) 201F201F201F201F201F201F201F201F201F201F 2020202020202020202020202020202020202020 2021202120212021202120212021202120212021 drop table t1; CREATE TABLE t1 ( status enum('active','passive') character set utf16 collate utf16_general_ci NOT NULL default 'passive' ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `status` enum('active','passive') CHARACTER SET utf16 NOT NULL DEFAULT 'passive' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ALTER TABLE t1 ADD a int NOT NULL AFTER status; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `status` enum('active','passive') CHARACTER SET utf16 NOT NULL DEFAULT 'passive', `a` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1; End of 4.1 tests CREATE TABLE t1 (a varchar(64) character set utf16, b decimal(10,3)); INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0); update t1 set b=a; SELECT *, hex(a) FROM t1; a b hex(a) 1.1 1.100 0031002E0031 2.1 2.100 0032002E0031 DROP TABLE t1; create table t1 (utext varchar(20) character set utf16); insert into t1 values ("lily"); insert into t1 values ("river"); prepare stmt from 'select utext from t1 where utext like ?'; set @param1='%%'; execute stmt using @param1; utext lily river execute stmt using @param1; utext lily river select utext from t1 where utext like '%%'; utext lily river drop table t1; deallocate prepare stmt; set names latin1; set character_set_connection=utf16; select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb') H000 H4142 I51231 select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb')); hex(soundex('')) hex(soundex('he')) hex(soundex('hello all folks')) hex(soundex('#3556 in bugdb')) 0048003000300030 00480034003100340032 004900350031003200330031 select 'mood' sounds like 'mud'; 'mood' sounds like 'mud' 1 select hex(soundex(_utf16 0x041004110412)); hex(soundex(_utf16 0x041004110412)) 0410003000300030 select hex(soundex(_utf16 0x00BF00C0)); hex(soundex(_utf16 0x00BF00C0)) 00C0003000300030 set names latin1; create table t1(a blob, b text charset utf16); select data_type, character_octet_length, character_maximum_length from information_schema.columns where table_name='t1'; data_type character_octet_length character_maximum_length blob 65535 65535 text 65535 32767 drop table t1; set names latin1; set collation_connection=utf16_general_ci; select position('bb' in 'abba'); position('bb' in 'abba') 2 create table t1 (a varchar(10) character set utf16) engine=heap; insert into t1 values ('a'),('A'),('b'),('B'); select * from t1 where a='a' order by binary a; a A a select hex(min(binary a)),count(*) from t1 group by a; hex(min(binary a)) count(*) 0041 2 0042 2 drop table t1; select char_length('abcd'), octet_length('abcd'); char_length('abcd') octet_length('abcd') 4 8 select char_length(_utf16 0xD800DC00), octet_length(_utf16 0xD800DC00); char_length(_utf16 0xD800DC00) octet_length(_utf16 0xD800DC00) 1 4 select char_length(_utf16 0xD87FDFFF), octet_length(_utf16 0xD87FDFFF); char_length(_utf16 0xD87FDFFF) octet_length(_utf16 0xD87FDFFF) 1 4 select left('abcd',2); left('abcd',2) ab select hex(left(_utf16 0xD800DC00D87FDFFF, 1)); hex(left(_utf16 0xD800DC00D87FDFFF, 1)) D800DC00 select hex(right(_utf16 0xD800DC00D87FDFFF, 1)); hex(right(_utf16 0xD800DC00D87FDFFF, 1)) D87FDFFF create table t1 (a varchar(10) character set utf16); insert into t1 values (_utf16 0xD800); ERROR HY000: Invalid utf16 character string: 'D800' insert into t1 values (_utf16 0xDC00); ERROR HY000: Invalid utf16 character string: 'DC00' insert into t1 values (_utf16 0xD800D800); ERROR HY000: Invalid utf16 character string: 'D800D8' insert into t1 values (_utf16 0xD800E800); ERROR HY000: Invalid utf16 character string: 'D800E8' insert into t1 values (_utf16 0xD8000800); ERROR HY000: Invalid utf16 character string: 'D80008' insert into t1 values (_utf16 0xD800DC00); insert into t1 values (_utf16 0xD800DCFF); insert into t1 values (_utf16 0xDBFFDC00); insert into t1 values (_utf16 0xDBFFDCFF); select hex(a) from t1; hex(a) D800DC00 D800DCFF DBFFDC00 DBFFDCFF drop table t1; create table t1 (s1 varchar(50) character set ucs2); insert into t1 values (0xdf84); alter table t1 modify column s1 varchar(50) character set utf16; Warnings: Warning 1366 Incorrect string value: '\xDF\x84' for column 's1' at row 1 select hex(s1) from t1; hex(s1) 003F drop table t1; create table t1 (s1 varchar(5) character set ucs2, s2 varchar(5) character set utf16); insert into t1 (s1) values (0xdf84); update t1 set s2 = s1; Warnings: Warning 1366 Incorrect string value: '\xDF\x84' for column 's2' at row 1 select hex(s2) from t1; hex(s2) 003F drop table t1; create table t1 (a char(10)) character set utf16; insert into t1 values ('a '); select hex(a) from t1; hex(a) 0061 drop table t1; select upper('abcd'), lower('ABCD'); upper('abcd') lower('ABCD') ABCD abcd create table t1 (a varchar(10) character set utf16); insert into t1 values (123456); select a, hex(a) from t1; a hex(a) 123456 003100320033003400350036 drop table t1; select hex(soundex('a')); hex(soundex('a')) 0041003000300030 create table t1 (a enum ('a','b','c')) character set utf16; insert into t1 values ('1'); select * from t1; a a drop table t1; set names latin1; select hex(conv(convert('123' using utf16), -10, 16)); hex(conv(convert('123' using utf16), -10, 16)) 3742 select hex(conv(convert('123' using utf16), 10, 16)); hex(conv(convert('123' using utf16), 10, 16)) 3742 set names latin1; set character_set_connection=utf16; select 1.1 + '1.2'; 1.1 + '1.2' 2.3 select 1.1 + '1.2xxx'; 1.1 + '1.2xxx' 2.3 Warnings: Warning 1292 Truncated incorrect DOUBLE value: '1.2xxx' select left('aaa','1'); left('aaa','1') a create table t1 (a int); insert into t1 values ('-1234.1e2'); insert into t1 values ('-1234.1e2xxxx'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 insert into t1 values ('-1234.1e2 '); select * from t1; a -123410 -123410 -123410 drop table t1; create table t1 (a int); insert into t1 values ('1 '); insert into t1 values ('1 x'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 select * from t1; a 1 1 drop table t1; create table t1 (a varchar(17000) character set utf16); Warnings: Note 1246 Converting column 'a' from VARCHAR to TEXT show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` mediumtext CHARACTER SET utf16 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a varchar(250) character set utf16 primary key); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(250) CHARACTER SET utf16 NOT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 drop table t1; create table t1 (a varchar(334) character set utf16 primary key); ERROR 42000: Specified key was too long; max key length is 1000 bytes create table t1 (a char(1) character set utf16); insert into t1 values (0xD800DC00),(0xD800DCFF),(0xDB7FDC00),(0xDB7FDCFF); insert into t1 values (0x00C0), (0x00FF),(0xE000), (0xFFFF); select hex(a), hex(@a:=convert(a using utf8mb4)), hex(convert(@a using utf16)) from t1; hex(a) hex(@a:=convert(a using utf8mb4)) hex(convert(@a using utf16)) D800DC00 F0908080 D800DC00 D800DCFF F09083BF D800DCFF DB7FDC00 F3AFB080 DB7FDC00 DB7FDCFF F3AFB3BF DB7FDCFF 00C0 C380 00C0 00FF C3BF 00FF E000 EE8080 E000 FFFF EFBFBF FFFF drop table t1; set collation_connection=utf16_general_ci; drop table if exists t1; create table t1 as select repeat(' ', 64) as s1, repeat(' ',64) as s2 union select null, null; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `s1` varchar(64) CHARACTER SET utf16 DEFAULT NULL, `s2` varchar(64) CHARACTER SET utf16 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 delete from t1; insert into t1 values('aaa','aaa'); insert into t1 values('aaa|qqq','qqq'); insert into t1 values('gheis','^[^a-dXYZ]+$'); insert into t1 values('aab','^aa?b'); insert into t1 values('Baaan','^Ba*n'); insert into t1 values('aaa','qqq|aaa'); insert into t1 values('qqq','qqq|aaa'); insert into t1 values('bbb','qqq|aaa'); insert into t1 values('bbb','qqq'); insert into t1 values('aaa','aba'); insert into t1 values(null,'abc'); insert into t1 values('def',null); insert into t1 values(null,null); insert into t1 values('ghi','ghi['); select HIGH_PRIORITY s1 regexp s2 from t1; s1 regexp s2 1 1 1 1 1 1 1 0 0 0 NULL NULL NULL NULL drop table t1; set names latin1; SET collation_connection=utf16_general_ci; CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0; SET timestamp=1216359724; INSERT INTO t1 VALUES (current_date); INSERT INTO t1 VALUES (current_time); INSERT INTO t1 VALUES (current_timestamp); SELECT s1, hex(s1) FROM t1; s1 hex(s1) 2008-07-18 0032003000300038002D00300037002D00310038 08:42:04 00300038003A00340032003A00300034 2008-07-18 08:42:04 0032003000300038002D00300037002D00310038002000300038003A00340032003A00300034 DROP TABLE t1; SET timestamp=0; SET NAMES latin1; SET collation_connection=utf16_general_ci; CREATE TABLE t1 AS SELECT repeat('a',2) as s1 LIMIT 0; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `s1` varchar(2) CHARACTER SET utf16 NOT NULL DEFAULT '' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 INSERT INTO t1 VALUES ('ab'),('AE'),('ab'),('AE'); SELECT * FROM t1 ORDER BY s1; s1 ab ab AE AE SET max_sort_length=4; SELECT * FROM t1 ORDER BY s1; s1 ab ab AE AE DROP TABLE t1; SET max_sort_length=DEFAULT; SET NAMES latin1; # # Bug#52520 Difference in tinytext utf column metadata # CREATE TABLE t1 ( s1 TINYTEXT CHARACTER SET utf16, s2 TEXT CHARACTER SET utf16, s3 MEDIUMTEXT CHARACTER SET utf16, s4 LONGTEXT CHARACTER SET utf16 ); SET NAMES utf8, @@character_set_results=NULL; SELECT *, HEX(s1) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 s1 s1 252 255 0 Y 16 0 54 def test t1 t1 s2 s2 252 65535 0 Y 16 0 54 def test t1 t1 s3 s3 252 16777215 0 Y 16 0 54 def test t1 t1 s4 s4 252 4294967295 0 Y 16 0 54 def HEX(s1) 253 6120 0 Y 0 0 33 s1 s2 s3 s4 HEX(s1) SET NAMES latin1; SELECT *, HEX(s1) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 s1 s1 252 127 0 Y 16 0 8 def test t1 t1 s2 s2 252 32767 0 Y 16 0 8 def test t1 t1 s3 s3 252 8388607 0 Y 16 0 8 def test t1 t1 s4 s4 252 2147483647 0 Y 16 0 8 def HEX(s1) 253 2040 0 Y 0 0 8 s1 s2 s3 s4 HEX(s1) SET NAMES utf8; SELECT *, HEX(s1) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 s1 s1 252 381 0 Y 16 0 33 def test t1 t1 s2 s2 252 98301 0 Y 16 0 33 def test t1 t1 s3 s3 252 25165821 0 Y 16 0 33 def test t1 t1 s4 s4 252 4294967295 0 Y 16 0 33 def HEX(s1) 253 6120 0 Y 0 0 33 s1 s2 s3 s4 HEX(s1) CREATE TABLE t2 AS SELECT CONCAT(s1) FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `CONCAT(s1)` varchar(255) CHARACTER SET utf16 DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 DROP TABLE t1, t2; # # Bug#11753363 (Bug#44793) Character sets: case clause, ucs2 or utf32, failure # SELECT CASE _latin1'a' WHEN _utf16'a' THEN 'A' END; CASE _latin1'a' WHEN _utf16'a' THEN 'A' END A SELECT CASE _utf16'a' WHEN _latin1'a' THEN 'A' END; CASE _utf16'a' WHEN _latin1'a' THEN 'A' END A CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET utf16); INSERT INTO t1 VALUES ('a'); SELECT CASE s1 WHEN 'a' THEN 'b' ELSE 'c' END FROM t1; CASE s1 WHEN 'a' THEN 'b' ELSE 'c' END b DROP TABLE t1; # # Bug#12340997 # DATE_ADD/DATE_SUB WITH INTERVAL CRASHES IN GET_INTERVAL_VALUE() # SELECT space(date_add(101, INTERVAL CHAR('1' USING utf16) hour_second)); space(date_add(101, INTERVAL CHAR('1' USING utf16) hour_second)) NULL Warnings: Warning 1301 Result of space() was larger than max_allowed_packet (4194304) - truncated # # Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT # SET NAMES utf8, @@character_set_connection=utf16; SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body UNION ALL SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1 GROUP BY id ORDER BY l DESC; id l a 512 Warnings: Warning 1260 Row 1 was cut by GROUP_CONCAT() # # End of 5.5 tests # # # Start of 5.6 tests # # # WL#3664 WEIGHT_STRING # set collation_connection=utf16_general_ci; select @@collation_connection; @@collation_connection utf16_general_ci select hex(weight_string('a')); hex(weight_string('a')) 0041 select hex(weight_string('A')); hex(weight_string('A')) 0041 select hex(weight_string('abc')); hex(weight_string('abc')) 004100420043 select hex(weight_string('abc' as char(2))); hex(weight_string('abc' as char(2))) 00410042 select hex(weight_string('abc' as char(3))); hex(weight_string('abc' as char(3))) 004100420043 select hex(weight_string('abc' as char(5))); hex(weight_string('abc' as char(5))) 00410042004300200020 select hex(weight_string('abc', 1, 2, 0xC0)); hex(weight_string('abc', 1, 2, 0xC0)) 00 select hex(weight_string('abc', 2, 2, 0xC0)); hex(weight_string('abc', 2, 2, 0xC0)) 0041 select hex(weight_string('abc', 3, 2, 0xC0)); hex(weight_string('abc', 3, 2, 0xC0)) 004100 select hex(weight_string('abc', 4, 2, 0xC0)); hex(weight_string('abc', 4, 2, 0xC0)) 00410042 select hex(weight_string('abc', 5, 2, 0xC0)); hex(weight_string('abc', 5, 2, 0xC0)) 0041004200 select hex(weight_string('abc',25, 2, 0xC0)); hex(weight_string('abc',25, 2, 0xC0)) 00410042002000200020002000200020002000200020002000 select hex(weight_string('abc', 1, 3, 0xC0)); hex(weight_string('abc', 1, 3, 0xC0)) 00 select hex(weight_string('abc', 2, 3, 0xC0)); hex(weight_string('abc', 2, 3, 0xC0)) 0041 select hex(weight_string('abc', 3, 3, 0xC0)); hex(weight_string('abc', 3, 3, 0xC0)) 004100 select hex(weight_string('abc', 4, 3, 0xC0)); hex(weight_string('abc', 4, 3, 0xC0)) 00410042 select hex(weight_string('abc', 5, 3, 0xC0)); hex(weight_string('abc', 5, 3, 0xC0)) 0041004200 select hex(weight_string('abc',25, 3, 0xC0)); hex(weight_string('abc',25, 3, 0xC0)) 00410042004300200020002000200020002000200020002000 select hex(weight_string('abc', 1, 4, 0xC0)); hex(weight_string('abc', 1, 4, 0xC0)) 00 select hex(weight_string('abc', 2, 4, 0xC0)); hex(weight_string('abc', 2, 4, 0xC0)) 0041 select hex(weight_string('abc', 3, 4, 0xC0)); hex(weight_string('abc', 3, 4, 0xC0)) 004100 select hex(weight_string('abc', 4, 4, 0xC0)); hex(weight_string('abc', 4, 4, 0xC0)) 00410042 select hex(weight_string('abc', 5, 4, 0xC0)); hex(weight_string('abc', 5, 4, 0xC0)) 0041004200 select hex(weight_string('abc',25, 4, 0xC0)); hex(weight_string('abc',25, 4, 0xC0)) 00410042004300200020002000200020002000200020002000 select @@collation_connection; @@collation_connection utf16_general_ci select hex(weight_string(cast(_latin1 0x80 as char))); hex(weight_string(cast(_latin1 0x80 as char))) 20AC select hex(weight_string(cast(_latin1 0x808080 as char))); hex(weight_string(cast(_latin1 0x808080 as char))) 20AC20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(2))); hex(weight_string(cast(_latin1 0x808080 as char) as char(2))) 20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(3))); hex(weight_string(cast(_latin1 0x808080 as char) as char(3))) 20AC20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(5))); hex(weight_string(cast(_latin1 0x808080 as char) as char(5))) 20AC20AC20AC00200020 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)) 20 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)) 20AC select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)) 20AC20 select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)) 20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)) 20AC20AC00 select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)) 20AC20AC002000200020002000200020002000200020002000 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)) 20 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)) 20AC select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)) 20AC20 select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)) 20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)) 20AC20AC20 select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)) 20AC20AC20AC00200020002000200020002000200020002000 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)) 20 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)) 20AC select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)) 20AC20 select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)) 20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)) 20AC20AC20 select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)) 20AC20AC20AC00200020002000200020002000200020002000 select hex(weight_string(_utf16 0xD800DC00)); hex(weight_string(_utf16 0xD800DC00)) FFFD select hex(weight_string(_utf16 0xD800DC01)); hex(weight_string(_utf16 0xD800DC01)) FFFD select @@collation_connection; @@collation_connection utf16_general_ci select hex(weight_string('a' LEVEL 1)); hex(weight_string('a' LEVEL 1)) 0041 select hex(weight_string('A' LEVEL 1)); hex(weight_string('A' LEVEL 1)) 0041 select hex(weight_string('abc' LEVEL 1)); hex(weight_string('abc' LEVEL 1)) 004100420043 select hex(weight_string('abc' as char(2) LEVEL 1)); hex(weight_string('abc' as char(2) LEVEL 1)) 00410042 select hex(weight_string('abc' as char(3) LEVEL 1)); hex(weight_string('abc' as char(3) LEVEL 1)) 004100420043 select hex(weight_string('abc' as char(5) LEVEL 1)); hex(weight_string('abc' as char(5) LEVEL 1)) 00410042004300200020 select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)); hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)) 20002000430042004100 select hex(weight_string('abc' as char(5) LEVEL 1 DESC)); hex(weight_string('abc' as char(5) LEVEL 1 DESC)) FFBEFFBDFFBCFFDFFFDF select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)); hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)) DFFFDFFFBCFFBDFFBEFF set collation_connection=utf16_bin; select @@collation_connection; @@collation_connection utf16_bin select hex(weight_string('a')); hex(weight_string('a')) 000061 select hex(weight_string('A')); hex(weight_string('A')) 000041 select hex(weight_string('abc')); hex(weight_string('abc')) 000061000062000063 select hex(weight_string('abc' as char(2))); hex(weight_string('abc' as char(2))) 000061000062 select hex(weight_string('abc' as char(3))); hex(weight_string('abc' as char(3))) 000061000062000063 select hex(weight_string('abc' as char(5))); hex(weight_string('abc' as char(5))) 000061000062000063000020000020 select hex(weight_string('abc', 1, 2, 0xC0)); hex(weight_string('abc', 1, 2, 0xC0)) 00 select hex(weight_string('abc', 2, 2, 0xC0)); hex(weight_string('abc', 2, 2, 0xC0)) 0000 select hex(weight_string('abc', 3, 2, 0xC0)); hex(weight_string('abc', 3, 2, 0xC0)) 000061 select hex(weight_string('abc', 4, 2, 0xC0)); hex(weight_string('abc', 4, 2, 0xC0)) 00006100 select hex(weight_string('abc', 5, 2, 0xC0)); hex(weight_string('abc', 5, 2, 0xC0)) 0000610000 select hex(weight_string('abc',25, 2, 0xC0)); hex(weight_string('abc',25, 2, 0xC0)) 00006100006200002000002000002000002000002000002000 select hex(weight_string('abc', 1, 3, 0xC0)); hex(weight_string('abc', 1, 3, 0xC0)) 00 select hex(weight_string('abc', 2, 3, 0xC0)); hex(weight_string('abc', 2, 3, 0xC0)) 0000 select hex(weight_string('abc', 3, 3, 0xC0)); hex(weight_string('abc', 3, 3, 0xC0)) 000061 select hex(weight_string('abc', 4, 3, 0xC0)); hex(weight_string('abc', 4, 3, 0xC0)) 00006100 select hex(weight_string('abc', 5, 3, 0xC0)); hex(weight_string('abc', 5, 3, 0xC0)) 0000610000 select hex(weight_string('abc',25, 3, 0xC0)); hex(weight_string('abc',25, 3, 0xC0)) 00006100006200006300002000002000002000002000002000 select hex(weight_string('abc', 1, 4, 0xC0)); hex(weight_string('abc', 1, 4, 0xC0)) 00 select hex(weight_string('abc', 2, 4, 0xC0)); hex(weight_string('abc', 2, 4, 0xC0)) 0000 select hex(weight_string('abc', 3, 4, 0xC0)); hex(weight_string('abc', 3, 4, 0xC0)) 000061 select hex(weight_string('abc', 4, 4, 0xC0)); hex(weight_string('abc', 4, 4, 0xC0)) 00006100 select hex(weight_string('abc', 5, 4, 0xC0)); hex(weight_string('abc', 5, 4, 0xC0)) 0000610000 select hex(weight_string('abc',25, 4, 0xC0)); hex(weight_string('abc',25, 4, 0xC0)) 00006100006200006300002000002000002000002000002000 select @@collation_connection; @@collation_connection utf16_bin select hex(weight_string(cast(_latin1 0x80 as char))); hex(weight_string(cast(_latin1 0x80 as char))) 0020AC select hex(weight_string(cast(_latin1 0x808080 as char))); hex(weight_string(cast(_latin1 0x808080 as char))) 0020AC0020AC0020AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(2))); hex(weight_string(cast(_latin1 0x808080 as char) as char(2))) 0020AC0020AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(3))); hex(weight_string(cast(_latin1 0x808080 as char) as char(3))) 0020AC0020AC0020AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(5))); hex(weight_string(cast(_latin1 0x808080 as char) as char(5))) 0020AC0020AC0020AC000020000020 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)) 00 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)) 0020 select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)) 0020AC select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)) 0020AC00 select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)) 0020AC0020 select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)) 0020AC0020AC00002000002000002000002000002000002000 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)) 00 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)) 0020 select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)) 0020AC select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)) 0020AC00 select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)) 0020AC0020 select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)) 0020AC0020AC0020AC00002000002000002000002000002000 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)) 00 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)) 0020 select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)) 0020AC select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)) 0020AC00 select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)) 0020AC0020 select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)) 0020AC0020AC0020AC00002000002000002000002000002000 select @@collation_connection; @@collation_connection utf16_bin select hex(weight_string('a' LEVEL 1)); hex(weight_string('a' LEVEL 1)) 000061 select hex(weight_string('A' LEVEL 1)); hex(weight_string('A' LEVEL 1)) 000041 select hex(weight_string('abc' LEVEL 1)); hex(weight_string('abc' LEVEL 1)) 000061000062000063 select hex(weight_string('abc' as char(2) LEVEL 1)); hex(weight_string('abc' as char(2) LEVEL 1)) 000061000062 select hex(weight_string('abc' as char(3) LEVEL 1)); hex(weight_string('abc' as char(3) LEVEL 1)) 000061000062000063 select hex(weight_string('abc' as char(5) LEVEL 1)); hex(weight_string('abc' as char(5) LEVEL 1)) 000061000062000063000020000020 select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)); hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)) 200000200000630000620000610000 select hex(weight_string('abc' as char(5) LEVEL 1 DESC)); hex(weight_string('abc' as char(5) LEVEL 1 DESC)) FFFF9EFFFF9DFFFF9CFFFFDFFFFFDF select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)); hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)) DFFFFFDFFFFF9CFFFF9DFFFF9EFFFF # # End of 5.6 tests #