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
#