SET TIME_ZONE='+03:00';
DROP TABLE IF EXISTS t1;
#
# Start of 5.6 tests
#
SET NAMES utf8mb4, collation_connection=utf16le_general_ci;
SELECT HEX('a'), HEX('a ');
HEX('a')	HEX('a ')
6100	61002000
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
#
# Check that incomplete utf16le characters in HEX notation
# are left-padded with zeros
#
SELECT HEX(_utf16le 0x44);
HEX(_utf16le 0x44)
0044
SELECT HEX(_utf16le 0x3344);
HEX(_utf16le 0x3344)
3344
SELECT HEX(_utf16le 0x113344);
HEX(_utf16le 0x113344)
00113344
#
# Check that 0x20 is only trimmed when it is
# a part of real SPACE character, not just a part
# of a multibyte sequence.
# Note, CYRILLIC LETTER ER is used as an example, which
# is stored as 0x0420 in utf16le, thus contains 0x20 in the
# low byte. The second character is THREE-PER-M, U+2004,
# which contains 0x20 in the high byte.
#
CREATE TABLE t1 (word VARCHAR(64), word2 CHAR(64)) CHARACTER SET utf16le;
INSERT INTO t1 VALUES (_koi8r 0xF2, _koi8r 0xF2), (_ucs2 X'2004',_ucs2 X'2004');
SELECT HEX(word) FROM t1 ORDER BY word;
HEX(word)
2004
0420
SELECT HEX(word2) FROM t1 ORDER BY word2;
HEX(word2)
2004
0420
DELETE FROM t1;
#
# Check that real spaces are correctly trimmed.
#
INSERT INTO t1 VALUES (_ucs2 X'042000200020', _ucs2 X'042000200020');
INSERT INTO t1 VALUES (_ucs2 X'200400200020', _ucs2 X'200400200020');
SELECT HEX(word) FROM t1 ORDER BY word;
HEX(word)
200420002000
042020002000
SELECT HEX(word2) FROM t1 ORDER BY word2;
HEX(word2)
2004
0420
DROP TABLE t1;
#
# Check LPAD/RPAD
#
CREATE TABLE t1 (a VARCHAR(10), pad INT, b VARCHAR(10)) CHARACTER SET utf16le;
INSERT INTO t1 VALUES (_ucs2 X'0420', 10, _ucs2 X'0421');
INSERT INTO t1 VALUES (_ucs2 X'0420', 10, _ucs2 X'04210422');
INSERT INTO t1 VALUES (_ucs2 X'0420', 10, _ucs2 X'042104220423');
INSERT IGNORE INTO t1 VALUES (_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423');
Warnings:
Warning	1265	Data truncated for column 'a' at row 1
INSERT INTO t1 VALUES (_utf32 X'010000', 10, _ucs2 X'0421');
INSERT INTO t1 VALUES (_ucs2 X'0421', 10, _utf32 X'010000');
SELECT a, pad, b, LPAD(a, pad, b), HEX(LPAD(a, pad, b)) FROM t1;
a	pad	b	LPAD(a, pad, b)	HEX(LPAD(a, pad, b))
Р	10	С	СССССССССР	2104210421042104210421042104210421042004
Р	10	СТ	СТСТСТСТСР	2104220421042204210422042104220421042004
Р	10	СТУ	СТУСТУСТУР	2104220423042104220423042104220423042004
РСТУФХЦЧШЩ	10	СТУ	РСТУФХЦЧШЩ	2004210422042304240425042604270428042904
𐀀	10	С	ССССССССС𐀀	21042104210421042104210421042104210400D800DC
С	10	𐀀	𐀀𐀀𐀀𐀀𐀀𐀀𐀀𐀀𐀀С	00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC00D800DC2104
DROP TABLE t1;
CREATE TABLE t1 SELECT
LPAD(_utf16le X'2004',10,_utf16le X'2104') l,
RPAD(_utf16le X'2004',10,_utf16le X'2104') r;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `l` varchar(10) CHARACTER SET utf16le COLLATE utf16le_general_ci DEFAULT NULL,
  `r` varchar(10) CHARACTER SET utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(l), HEX(r) FROM t1;
HEX(l)	HEX(r)
2104210421042104210421042104210421042004	2004210421042104210421042104210421042104
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;
#
# Testing LIKE
#
SET NAMES utf8, collation_connection=utf16le_general_ci;
select @@collation_connection;
@@collation_connection
utf16le_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 utf16le COLLATE utf16le_general_ci DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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	2	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	2	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
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf16le);
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 utf16le_bin ORDER BY BINARY a;
a
фЫва
фЫвапролдж
DROP TABLE t1;
CREATE TABLE t1 (word VARCHAR(64) NOT NULL, PRIMARY KEY (word))
ENGINE=MyISAM CHARACTER SET utf16le;
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 _ucs2 x'00630025';
word
cat
SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630061005F';
word
cat
DROP TABLE t1;
#
# Check that INSERT() works fine.
# This invokes charpos() function.
#
CREATE TABLE t1 (
a VARCHAR(10) CHARACTER SET utf16le,
b VARCHAR(10) CHARACTER SET utf16le);
INSERT INTO t1 VALUES ('abc', 'def');
SELECT INSERT(a, 10, 2, b) FROM t1;
INSERT(a, 10, 2, b)
abc
SELECT INSERT(a, 1, 2, b) FROM t1;
INSERT(a, 1, 2, b)
defc
DROP TABLE t1;
#
# Bug#1264
#
SET NAMES utf8, collation_connection=utf16le_general_ci;
#
# Two fields, index
#
CREATE TABLE t1 (
word VARCHAR(64),
bar INT(11) DEFAULT 0,
PRIMARY KEY (word))
ENGINE=MyISAM
CHARSET utf16le
COLLATE utf16le_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;
#
# One field, index
#
CREATE TABLE t1 (
word VARCHAR(64) ,
PRIMARY KEY (word))
ENGINE=MyISAM
CHARSET utf16le
COLLATE utf16le_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;
#
# Two fields, no index
#
CREATE TABLE t1 (
word TEXT,
bar INT(11) AUTO_INCREMENT,
PRIMARY KEY (bar))
ENGINE=MyISAM
CHARSET utf16le
COLLATE utf16le_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;
#
# END OF Bug 1264 test
#
#
# Check alignment for from-binary-conversion with CAST and CONVERT
#
SELECT HEX(CAST(0xAA as char CHARACTER SET utf16le));
HEX(CAST(0xAA as char CHARACTER SET utf16le))
00AA
SELECT HEX(CONVERT(0xAA USING utf16le));
HEX(CONVERT(0xAA USING utf16le))
00AA
#
# Check alignment for string types
#
CREATE TABLE t1 (a CHAR(10) CHARACTER SET utf16le);
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 utf16le);
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 utf16le);
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 utf16le);
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 utf16le);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
HEX(a)
0001
0011
0111
1111
00011111
DROP TABLE t1;
#
# Bug#5081 : UCS2 fields are filled with '0x2020'
# after extending field length
#
CREATE TABLE t1(a CHAR(1)) DEFAULT CHARSET utf16le;
INSERT INTO t1 VALUES ('a'),('b'),('c');
ALTER TABLE t1 MODIFY a CHAR(5);
SELECT a, HEX(a) FROM t1;
a	HEX(a)
a	6100
b	6200
c	6300
DROP TABLE t1;
#
# Check prepare statement from an UTF16 string
#
SET NAMES latin1;
SET @ivar= 1234;
SET @str1 = 'SELECT ?';
SET @str2 = CONVERT(@str1 USING utf16le);
PREPARE stmt1 FROM @str2;
EXECUTE stmt1 USING @ivar;
?
1234
#
# Check that utf16le works with ENUM and SET type
#
SET NAMES utf8, collation_connection=utf16le_general_ci;
CREATE TABLE t1 (a ENUM('x','y','z') CHARACTER SET utf16le);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` enum('x','y','z') CHARACTER SET utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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	7800
y	7900
z	7A00
ALTER TABLE t1 CHANGE a a ENUM('x','y','z','d','e','ä','ö','ü') CHARACTER SET utf16le;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` enum('x','y','z','d','e','ä','ö','ü') CHARACTER SET utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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	7800
y	7900
z	7A00
d	6400
e	6500
ä	E400
ö	F600
ü	FC00
DROP TABLE t1;
CREATE TABLE t1 (a set ('x','y','z','ä','ö','ü') CHARACTER SET utf16le);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` set('x','y','z','ä','ö','ü') CHARACTER SET utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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	7800
y	7900
x,y	78002C007900
z	7A00
x,y,z,ä,ö,ü	78002C0079002C007A002C00E4002C00F6002C00FC00
DROP TABLE t1;
#
# Bug#7302 UCS2 data in ENUM fields get truncated when new column is added
#
CREATE TABLE t1(a ENUM('a','b','c')) DEFAULT CHARACTER SET utf16le;
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 utf8, collation_connection='utf16le_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)
utf16le_general_ci	61000900
utf16le_general_ci	6100
utf16le_general_ci	61002000
drop table t1;
#
# MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP
#
SELECT @@collation_connection;
@@collation_connection
utf16le_general_ci
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM t1 GROUP BY MID(CURRENT_USER,0) WITH ROLLUP;
i
1
1
SELECT * FROM t1 GROUP BY MID('test',0) WITH ROLLUP;
i
1
1
DROP TABLE t1;
#
# MDEV-6170 Incorrect ordering with utf8_bin and utf8mb4_bin collations
#
SELECT @@collation_connection;
@@collation_connection
utf16le_general_ci
CREATE TABLE t1 ENGINE=MEMORY AS SELECT REPEAT('a',5) AS a LIMIT 0;
INSERT INTO t1 (a) VALUES ("a");
INSERT INTO t1 (a) VALUES ("b");
INSERT INTO t1 (a) VALUES ("c");
INSERT INTO t1 (a) VALUES ("d");
INSERT INTO t1 (a) VALUES ("e");
INSERT INTO t1 (a) VALUES ("f");
INSERT INTO t1 (a) VALUES ("g");
INSERT INTO t1 (a) VALUES ("h");
INSERT INTO t1 (a) VALUES ("i");
INSERT INTO t1 (a) VALUES ("j");
INSERT INTO t1 (a) VALUES ("k");
INSERT INTO t1 (a) VALUES ("l");
INSERT INTO t1 (a) VALUES ("m");
SELECT * FROM t1 ORDER BY LOWER(a);
a
a
b
c
d
e
f
g
h
i
j
k
l
m
SELECT * FROM t1 ORDER BY LOWER(a) DESC;
a
m
l
k
j
i
h
g
f
e
d
c
b
a
DROP TABLE t1;
select @@collation_connection;
@@collation_connection
utf16le_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;
SELECT @@collation_connection;
@@collation_connection
utf16le_general_ci
SELECT '\%b' LIKE '%\%';
'\%b' LIKE '%\%'
0
SET NAMES utf8, collation_connection='utf16le_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)
utf16le_bin	61000900
utf16le_bin	6100
utf16le_bin	61002000
drop table t1;
#
# MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP
#
SELECT @@collation_connection;
@@collation_connection
utf16le_bin
CREATE TABLE t1 (i INT) ENGINE=MyISAM;
INSERT INTO t1 VALUES (1),(2);
SELECT * FROM t1 GROUP BY MID(CURRENT_USER,0) WITH ROLLUP;
i
1
1
SELECT * FROM t1 GROUP BY MID('test',0) WITH ROLLUP;
i
1
1
DROP TABLE t1;
#
# MDEV-6170 Incorrect ordering with utf8_bin and utf8mb4_bin collations
#
SELECT @@collation_connection;
@@collation_connection
utf16le_bin
CREATE TABLE t1 ENGINE=MEMORY AS SELECT REPEAT('a',5) AS a LIMIT 0;
INSERT INTO t1 (a) VALUES ("a");
INSERT INTO t1 (a) VALUES ("b");
INSERT INTO t1 (a) VALUES ("c");
INSERT INTO t1 (a) VALUES ("d");
INSERT INTO t1 (a) VALUES ("e");
INSERT INTO t1 (a) VALUES ("f");
INSERT INTO t1 (a) VALUES ("g");
INSERT INTO t1 (a) VALUES ("h");
INSERT INTO t1 (a) VALUES ("i");
INSERT INTO t1 (a) VALUES ("j");
INSERT INTO t1 (a) VALUES ("k");
INSERT INTO t1 (a) VALUES ("l");
INSERT INTO t1 (a) VALUES ("m");
SELECT * FROM t1 ORDER BY LOWER(a);
a
a
b
c
d
e
f
g
h
i
j
k
l
m
SELECT * FROM t1 ORDER BY LOWER(a) DESC;
a
m
l
k
j
i
h
g
f
e
d
c
b
a
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 utf16le COLLATE utf16le_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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))
8503	CE85
9DFF	EFBE9D
00D884DF	F0908E84
C0DB00DC	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))
8503	CE85
9DFF	EFBE9D
00D884DF	F0908E84
C0DB00DC	F4808080
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
utf16le_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 @@collation_connection;
@@collation_connection
utf16le_bin
SELECT '\%b' LIKE '%\%';
'\%b' LIKE '%\%'
0
#
# Bug#10344 Some string functions fail for UCS2
#
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf16le, pos INT);
INSERT INTO t1 VALUES (_ucs2 0x00e400e50068,1);
INSERT INTO t1 VALUES (_ucs2 0x00e400e50068,2);
INSERT INTO t1 VALUES (_ucs2 0x00e400e50068,3);
INSERT INTO t1 VALUES (_ucs2 0x00e400e50068,-1);
INSERT INTO t1 VALUES (_ucs2 0x00e400e50068,-2);
INSERT INTO t1 VALUES (_ucs2 0x00e400e50068,-3);
INSERT INTO t1 VALUES (_utf32 0x000000e4000000e500010000, 1);
INSERT INTO t1 VALUES (_utf32 0x000000e4000000e500010000, 2);
INSERT INTO t1 VALUES (_utf32 0x000000e4000000e500010000, 3);
INSERT INTO t1 VALUES (_utf32 0x000000e4000000e500010000, -1);
INSERT INTO t1 VALUES (_utf32 0x000000e4000000e500010000, -2);
INSERT INTO t1 VALUES (_utf32 0x000000e4000000e500010000, -3);
SELECT HEX(SUBSTR(a, pos)), SUBSTR(a, pos) FROM t1;
HEX(SUBSTR(a, pos))	SUBSTR(a, pos)
E400E5006800	äåh
E5006800	åh
6800	h
6800	h
E5006800	åh
E400E5006800	äåh
E400E50000D800DC	äå?
E50000D800DC	å?
00D800DC	?
00D800DC	?
E50000D800DC	å?
E400E50000D800DC	äå?
DROP TABLE t1;
SET NAMES utf8, collation_connection=utf16le_general_ci;
#
# Bug#9442 Set parameter make query fail if column CHARACTER SET is UCS2
#
CREATE TABLE t1 (utext VARCHAR(20) CHARACTER SET utf16le);
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;
#
# Bug#22052 Trailing spaces are not removed FROM UNICODE fields in an index
#
CREATE TABLE t1 (
a CHAR(10) CHARACTER SET utf16le NOT NULL,
INDEX a (a)
) engine=myisam;
INSERT INTO t1 VALUES (REPEAT(_ucs2 0x201f, 10));
INSERT INTO t1 VALUES (REPEAT(_ucs2 0x2020, 10));
INSERT INTO t1 VALUES (REPEAT(_ucs2 0x2021, 10));
# make sure "index read" is used
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)
1F201F201F201F201F201F201F201F201F201F20
2020202020202020202020202020202020202020
2120212021202120212021202120212021202120
ALTER TABLE t1 DROP INDEX a;
SELECT HEX(a) FROM t1 ORDER BY a;
HEX(a)
1F201F201F201F201F201F201F201F201F201F20
2020202020202020202020202020202020202020
2120212021202120212021202120212021202120
DROP TABLE t1;
#
# Bug #20108: corrupted default enum value for a ucs2 field
#
CREATE TABLE t1 (
status ENUM('active','passive') CHARACTER SET utf16le COLLATE utf16le_general_ci
NOT NULL DEFAULT 'passive'
);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `status` enum('active','passive') CHARACTER SET utf16le COLLATE utf16le_general_ci NOT NULL DEFAULT 'passive'
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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 utf16le COLLATE utf16le_general_ci NOT NULL DEFAULT 'passive',
  `a` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
#
# Conversion FROM an UTF16LE string to a decimal column
#
CREATE TABLE t1 (a VARCHAR(64) CHARACTER SET utf16le, 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	31002E003100
2.1	2.100	32002E003100
DROP TABLE t1;
#
# Bug#9442 Set parameter make query fail if column CHARACTER SET is UCS2
#
CREATE TABLE t1 (utext VARCHAR(20) CHARACTER SET utf16le);
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;
#
# Bug#22638 SOUNDEX broken for international characters
#
SET NAMES utf8, collation_connection=utf16le_general_ci;
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'))
	4800300030003000	48003400310034003200	490035003100320033003100
SELECT 'mood' sounds like 'mud';
'mood' sounds like 'mud'
1
# Cyrillic A, BE, VE
SELECT HEX(SOUNDEX(_utf16le 0x041004110412));
HEX(SOUNDEX(_utf16le 0x041004110412))
0410300030003000
# Make sure that "U+00BF INVERTED QUESTION MARK" is not considered as letter
SELECT HEX(SOUNDEX(_utf16le 0x00BF00C0));
HEX(SOUNDEX(_utf16le 0x00BF00C0))
00BF300030003000
#
# Bug#14290: character_maximum_length for text fields
#
CREATE TABLE t1(a BLOB, b TEXT CHARSET utf16le);
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 utf8, collation_connection=utf16le_general_ci;
#
# Testing instr()
#
SELECT POSITION('bb' IN 'abba');
POSITION('bb' IN 'abba')
2
#
# Testing hash_sort()
#
SET NAMES utf8, collation_connection=utf16le_bin;
#
# Test that hash_sort() ignores trailing spaces
#
SELECT @@collation_connection;
@@collation_connection
utf16le_bin
CREATE TABLE t1 ENGINE=HEAP AS SELECT REPEAT (' ', 10) AS a LIMIT 0;
ALTER TABLE t1 ADD KEY (a);
CREATE TABLE t2 (a VARCHAR(10));
INSERT INTO t2 VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7');
INSERT INTO t2 VALUES ('8'),('9'),('A'),('B'),('C'),('D'),('E'),('F');
INSERT INTO t1 SELECT CONCAT('a',t21.a,t22.a) FROM t2 t21, t2 t22 ORDER BY 1;
DROP TABLE t2;
INSERT INTO t1 VALUES ('a ');
SELECT a, HEX(a) FROM t1 WHERE a='a';
a	HEX(a)
a 	61002000
DROP TABLE t1;
SET NAMES utf8, collation_connection=utf16le_general_ci;
#
# Test that hash_sort() ignores trailing spaces
#
SELECT @@collation_connection;
@@collation_connection
utf16le_general_ci
CREATE TABLE t1 ENGINE=HEAP AS SELECT REPEAT (' ', 10) AS a LIMIT 0;
ALTER TABLE t1 ADD KEY (a);
CREATE TABLE t2 (a VARCHAR(10));
INSERT INTO t2 VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7');
INSERT INTO t2 VALUES ('8'),('9'),('A'),('B'),('C'),('D'),('E'),('F');
INSERT INTO t1 SELECT CONCAT('a',t21.a,t22.a) FROM t2 t21, t2 t22 ORDER BY 1;
DROP TABLE t2;
INSERT INTO t1 VALUES ('a ');
SELECT a, HEX(a) FROM t1 WHERE a='a';
a	HEX(a)
a 	61002000
DROP TABLE t1;
#
# Testing numchars()
#
SELECT CHAR_LENGTH('abcd'), OCTET_LENGTH('abcd');
CHAR_LENGTH('abcd')	OCTET_LENGTH('abcd')
4	8
SELECT CHAR_LENGTH(_utf16le 0x00D800DC), OCTET_LENGTH(_utf16le 0x00D800DC);
CHAR_LENGTH(_utf16le 0x00D800DC)	OCTET_LENGTH(_utf16le 0x00D800DC)
1	4
SELECT CHAR_LENGTH(_utf16le 0x7DD8FFDF), OCTET_LENGTH(_utf16le 0x7FD8DDDF);
CHAR_LENGTH(_utf16le 0x7DD8FFDF)	OCTET_LENGTH(_utf16le 0x7FD8DDDF)
1	4
#
# Testing charpos()
#
SELECT LEFT('abcd',2);
LEFT('abcd',2)
ab
SELECT HEX(LEFT(_utf16le 0x00D800DC7FD8FFDF, 1));
HEX(LEFT(_utf16le 0x00D800DC7FD8FFDF, 1))
00D800DC
SELECT HEX(RIGHT(_utf16le 0x00D800DC7FD8FFDF, 1));
HEX(RIGHT(_utf16le 0x00D800DC7FD8FFDF, 1))
7FD8FFDF
#
# Testing well_formed_length()
#
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf16le);
# Bad sequences
INSERT INTO t1 VALUES (_utf16le 0x00D8);
ERROR HY000: Invalid utf16le character string: '00D8'
INSERT INTO t1 VALUES (_utf16le 0x00DC);
ERROR HY000: Invalid utf16le character string: '00DC'
INSERT INTO t1 VALUES (_utf16le 0x00D800D8);
ERROR HY000: Invalid utf16le character string: '00D800'
INSERT INTO t1 VALUES (_utf16le 0x00D800E8);
ERROR HY000: Invalid utf16le character string: '00D800'
INSERT INTO t1 VALUES (_utf16le 0x00D80008);
ERROR HY000: Invalid utf16le character string: '00D800'
# Good sequences
INSERT INTO t1 VALUES (_utf16le 0x00D800DC);
INSERT INTO t1 VALUES (_utf16le 0x00D8FFDC);
INSERT INTO t1 VALUES (_utf16le 0xFFDB00DC);
INSERT INTO t1 VALUES (_utf16le 0xFFDBFFDC);
SELECT HEX(a) FROM t1;
HEX(a)
00D800DC
00D8FFDC
FFDB00DC
FFDBFFDC
DROP TABLE t1;
#
# Bug#32393 Character sets: illegal characters in utf16le columns
#
# Tests that my_ci_wc_mb() doesn't accept surrogate parts
#
# via ALTER
#
CREATE TABLE t1 (s1 VARCHAR(50) CHARACTER SET ucs2);
INSERT INTO t1 VALUES (0xDF84);
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
ALTER TABLE t1 MODIFY column s1 VARCHAR(50) CHARACTER SET utf16le;
Warnings:
Warning	1366	Incorrect string value: '\xDF\x84' for column `test`.`t1`.`s1` at row 1
SELECT HEX(s1) FROM t1;
HEX(s1)
3F00
DROP TABLE t1;
#
# via UPDATE
#
CREATE TABLE t1 (s1 VARCHAR(5) CHARACTER SET ucs2, s2 VARCHAR(5) CHARACTER SET utf16le);
INSERT INTO t1 (s1) VALUES (0xdf84);
UPDATE IGNORE t1 set s2 = s1;
Warnings:
Warning	1366	Incorrect string value: '\xDF\x84' for column `test`.`t1`.`s2` at row 1
SELECT HEX(s2) FROM t1;
HEX(s2)
3F00
DROP TABLE t1;
#
# Testing lengthsp()
#
CREATE TABLE t1 (a CHAR(10)) CHARACTER SET utf16le;
INSERT INTO t1 VALUES ('a   ');
SELECT HEX(a) FROM t1;
HEX(a)
6100
DROP TABLE t1;
#
# Testing caseup() and casedn()
#
SELECT UPPER('abcd'), LOWER('ABCD');
UPPER('abcd')	LOWER('ABCD')
ABCD	abcd
#
# Checking str_to_datetime()
#
select @@collation_connection;
@@collation_connection
utf16le_general_ci
CREATE TABLE t1 (a date);
INSERT INTO t1 VALUES ('2007-09-16');
SELECT * FROM t1;
a
2007-09-16
DROP TABLE t1;
#
# Testing ll10tostr
#
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf16le);
INSERT INTO t1 VALUES (123456);
SELECT a, HEX(a) FROM t1;
a	HEX(a)
123456	310032003300340035003600
DROP TABLE t1;
#
# Testing fill
# SOUNDEX fills strings with DIGIT ZERO up to four characters
#
SELECT SOUNDEX('a'), HEX(SOUNDEX('a'));
SOUNDEX('a')	HEX(SOUNDEX('a'))
A000	4100300030003000
#
# Testing strntoul
#
CREATE TABLE t1 (a enum ('a','b','c')) CHARACTER SET utf16le;
INSERT INTO t1 VALUES ('1');
SELECT * FROM t1;
a
a
DROP TABLE t1;
#
# Testing strntoll and strntoull
#
SET NAMES latin1;
SELECT HEX(CONV(CONVERT('123' USING utf16le), -10, 16));
HEX(CONV(CONVERT('123' USING utf16le), -10, 16))
3742
SELECT HEX(CONV(CONVERT('123' USING utf16le), 10, 16));
HEX(CONV(CONVERT('123' USING utf16le), 10, 16))
3742
SET NAMES utf8, collation_connection=utf16le_general_ci;
#
# Testing strntod
#
SET NAMES utf8, collation_connection=utf16le_general_ci;
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'
#
# Testing strtoll10
#
SELECT LEFT('aaa','1');
LEFT('aaa','1')
a
CREATE TABLE t1 AS SELECT REPEAT('abcd', 128) AS a;
SELECT LEFT(a, '2') FROM t1;
LEFT(a, '2')
ab
SELECT LEFT(a, ' \t \t 2') FROM t1;
LEFT(a, ' \t \t 2')
ab
SELECT LEFT(a, ' \t \t +2') FROM t1;
LEFT(a, ' \t \t +2')
ab
SELECT SUBSTR(a, '-2') FROM t1;
SUBSTR(a, '-2')
cd
SELECT SUBSTR(a, ' \t \t -2') FROM t1;
SUBSTR(a, ' \t \t -2')
cd
SELECT LEFT(a, '00002') FROM t1;
LEFT(a, '00002')
ab
SELECT LEFT(a, ' \t \t 00002') FROM t1;
LEFT(a, ' \t \t 00002')
ab
SELECT LEFT(a, ' \t \t +00002') FROM t1;
LEFT(a, ' \t \t +00002')
ab
SELECT SUBSTR(a, '-00002') FROM t1;
SUBSTR(a, '-00002')
cd
SELECT SUBSTR(a, ' \t \t -00002') FROM t1;
SUBSTR(a, ' \t \t -00002')
cd
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('abcd', 128) AS a LIMIT 0;
INSERT INTO t1 VALUES ('255'), ('65535'),('16777215'),('4294967295'),
('1099511627775'),('281474976710655'),('72057594037927935'),
('1844674407370955161'),('18446744073709551614'), ('18446744073709551615');
SELECT a, CAST(a AS SIGNED), CAST(a AS UNSIGNED) FROM t1;
a	CAST(a AS SIGNED)	CAST(a AS UNSIGNED)
255	255	255
65535	65535	65535
16777215	16777215	16777215
4294967295	4294967295	4294967295
1099511627775	1099511627775	1099511627775
281474976710655	281474976710655	281474976710655
72057594037927935	72057594037927935	72057594037927935
1844674407370955161	1844674407370955161	1844674407370955161
18446744073709551614	-2	18446744073709551614
18446744073709551615	-1	18446744073709551615
Warnings:
Note	1105	Cast to signed converted positive out-of-range integer to it's negative complement
Note	1105	Cast to signed converted positive out-of-range integer to it's negative complement
UPDATE t1 SET a=CONCAT('-', a);
SELECT a, CAST(a AS SIGNED) FROM t1;
a	CAST(a AS SIGNED)
-255	-255
-65535	-65535
-16777215	-16777215
-4294967295	-4294967295
-1099511627775	-1099511627775
-281474976710655	-281474976710655
-72057594037927935	-72057594037927935
-1844674407370955161	-1844674407370955161
-18446744073709551614	-9223372036854775808
-18446744073709551615	-9223372036854775808
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '-18446744073709551614'
Warning	1292	Truncated incorrect INTEGER value: '-18446744073709551615'
DROP TABLE t1;
#
# Testing strtoll10()
#
SELECT @@character_set_connection;
@@character_set_connection
utf16le
SELECT CAST('1' AS UNSIGNED);
CAST('1' AS UNSIGNED)
1
SELECT CAST('12' AS UNSIGNED);
CAST('12' AS UNSIGNED)
12
SELECT CAST('123' AS UNSIGNED);
CAST('123' AS UNSIGNED)
123
SELECT CAST('1234' AS UNSIGNED);
CAST('1234' AS UNSIGNED)
1234
SELECT CAST('12345' AS UNSIGNED);
CAST('12345' AS UNSIGNED)
12345
SELECT CAST('123456' AS UNSIGNED);
CAST('123456' AS UNSIGNED)
123456
SELECT CAST('1234567' AS UNSIGNED);
CAST('1234567' AS UNSIGNED)
1234567
SELECT CAST('12345678' AS UNSIGNED);
CAST('12345678' AS UNSIGNED)
12345678
SELECT CAST('123456789' AS UNSIGNED);
CAST('123456789' AS UNSIGNED)
123456789
SELECT CAST('1234567891' AS UNSIGNED);
CAST('1234567891' AS UNSIGNED)
1234567891
SELECT CAST('12345678912' AS UNSIGNED);
CAST('12345678912' AS UNSIGNED)
12345678912
SELECT CAST('123456789123' AS UNSIGNED);
CAST('123456789123' AS UNSIGNED)
123456789123
SELECT CAST('1234567891234' AS UNSIGNED);
CAST('1234567891234' AS UNSIGNED)
1234567891234
SELECT CAST('12345678912345' AS UNSIGNED);
CAST('12345678912345' AS UNSIGNED)
12345678912345
SELECT CAST('123456789123456' AS UNSIGNED);
CAST('123456789123456' AS UNSIGNED)
123456789123456
SELECT CAST('1234567891234567' AS UNSIGNED);
CAST('1234567891234567' AS UNSIGNED)
1234567891234567
SELECT CAST('12345678912345678' AS UNSIGNED);
CAST('12345678912345678' AS UNSIGNED)
12345678912345678
SELECT CAST('123456789123456789' AS UNSIGNED);
CAST('123456789123456789' AS UNSIGNED)
123456789123456789
SELECT CAST('1234567891234567891' AS UNSIGNED);
CAST('1234567891234567891' AS UNSIGNED)
1234567891234567891
SELECT CAST('12345678912345678912' AS UNSIGNED);
CAST('12345678912345678912' AS UNSIGNED)
12345678912345678912
SELECT CAST('123456789123456789123' AS UNSIGNED);
CAST('123456789123456789123' AS UNSIGNED)
18446744073709551615
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '123456789123456789123'
SELECT CAST('1x' AS UNSIGNED);
CAST('1x' AS UNSIGNED)
1
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '1x'
SELECT CAST('12x' AS UNSIGNED);
CAST('12x' AS UNSIGNED)
12
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '12x'
SELECT CAST('123x' AS UNSIGNED);
CAST('123x' AS UNSIGNED)
123
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '123x'
SELECT CAST('1234x' AS UNSIGNED);
CAST('1234x' AS UNSIGNED)
1234
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '1234x'
SELECT CAST('12345x' AS UNSIGNED);
CAST('12345x' AS UNSIGNED)
12345
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '12345x'
SELECT CAST('123456x' AS UNSIGNED);
CAST('123456x' AS UNSIGNED)
123456
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '123456x'
SELECT CAST('1234567x' AS UNSIGNED);
CAST('1234567x' AS UNSIGNED)
1234567
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '1234567x'
SELECT CAST('12345678x' AS UNSIGNED);
CAST('12345678x' AS UNSIGNED)
12345678
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '12345678x'
SELECT CAST('123456789x' AS UNSIGNED);
CAST('123456789x' AS UNSIGNED)
123456789
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '123456789x'
SELECT CAST('1234567891x' AS UNSIGNED);
CAST('1234567891x' AS UNSIGNED)
1234567891
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '1234567891x'
SELECT CAST('12345678912x' AS UNSIGNED);
CAST('12345678912x' AS UNSIGNED)
12345678912
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '12345678912x'
SELECT CAST('123456789123x' AS UNSIGNED);
CAST('123456789123x' AS UNSIGNED)
123456789123
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '123456789123x'
SELECT CAST('1234567891234x' AS UNSIGNED);
CAST('1234567891234x' AS UNSIGNED)
1234567891234
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '1234567891234x'
SELECT CAST('12345678912345x' AS UNSIGNED);
CAST('12345678912345x' AS UNSIGNED)
12345678912345
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '12345678912345x'
SELECT CAST('123456789123456x' AS UNSIGNED);
CAST('123456789123456x' AS UNSIGNED)
123456789123456
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '123456789123456x'
SELECT CAST('1234567891234567x' AS UNSIGNED);
CAST('1234567891234567x' AS UNSIGNED)
1234567891234567
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '1234567891234567x'
SELECT CAST('12345678912345678x' AS UNSIGNED);
CAST('12345678912345678x' AS UNSIGNED)
12345678912345678
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '12345678912345678x'
SELECT CAST('123456789123456789x' AS UNSIGNED);
CAST('123456789123456789x' AS UNSIGNED)
123456789123456789
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '123456789123456789x'
SELECT CAST('1234567891234567891x' AS UNSIGNED);
CAST('1234567891234567891x' AS UNSIGNED)
1234567891234567891
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '1234567891234567891x'
SELECT CAST('12345678912345678912x' AS UNSIGNED);
CAST('12345678912345678912x' AS UNSIGNED)
12345678912345678912
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '12345678912345678912x'
SELECT CAST('123456789123456789123x' AS UNSIGNED);
CAST('123456789123456789123x' AS UNSIGNED)
18446744073709551615
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '123456789123456789123x'
#
# Testing strntoull10rnd
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES ('-1234.1e2');
INSERT IGNORE INTO t1 VALUES ('-1234.1e2xxxx');
Warnings:
Warning	1265	Data truncated for column 'a' at row 1
INSERT INTO t1 VALUES ('-1234.1e2    ');
Warnings:
Note	1265	Data truncated for column 'a' at row 1
INSERT INTO t1 VALUES ('123');
INSERT INTO t1 VALUES ('-124');
INSERT INTO t1 VALUES ('+125');
INSERT INTO t1 VALUES (' \t \t 123');
INSERT INTO t1 VALUES (' \t \t -124');
INSERT INTO t1 VALUES (' \t \t +125');
INSERT INTO t1 VALUES (' \t \t 000123');
INSERT INTO t1 VALUES (' \t \t -000124');
INSERT INTO t1 VALUES (' \t \t +000125');
SELECT * FROM t1;
a
-123410
-123410
-123410
123
-124
125
123
-124
125
123
-124
125
DROP TABLE t1;
CREATE TABLE t1 (a BIGINT UNSIGNED);
INSERT INTO t1 VALUES ('255'), ('65535'),('16777215'),('4294967295'),
('1099511627775'),('281474976710655'),('72057594037927935'),
('1844674407370955161'),('18446744073709551614'), ('18446744073709551615');
SELECT * FROM t1;
a
255
65535
16777215
4294967295
1099511627775
281474976710655
72057594037927935
1844674407370955161
18446744073709551614
18446744073709551615
DROP TABLE t1;
#
# Testing scan
#
CREATE TABLE t1 (a int);
INSERT INTO t1 VALUES ('1 ');
Warnings:
Note	1265	Data truncated for column 'a' at row 1
INSERT IGNORE 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;
#
# Testing auto-conversion to TEXT
#
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
CREATE TABLE t1 (a VARCHAR(17000) CHARACTER SET utf16le);
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 utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
#
# Testing that maximim possible key length is 1000 bytes
#
CREATE TABLE t1 (a VARCHAR(250) CHARACTER SET utf16le PRIMARY KEY);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(250) CHARACTER SET utf16le COLLATE utf16le_general_ci NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1;
CREATE TABLE t1 (a VARCHAR(334) CHARACTER SET utf16le PRIMARY KEY);
ERROR 42000: Specified key was too long; max key length is 1000 bytes
#
# Conversion to utf8
#
CREATE TABLE t1 (a CHAR(1) CHARACTER SET utf16le);
INSERT INTO t1 VALUES (0x00D800DC),(0x00D8FFDC),(0x7FDB00DC),(0x7FDBFFDC);
INSERT INTO t1 VALUES (0xC000), (0xFF00),(0x00E0), (0xFFFF);
SELECT HEX(a), HEX(@a:=CONVERT(a USING utf8mb4)), HEX(CONVERT(@a USING utf16le)) FROM t1;
HEX(a)	HEX(@a:=CONVERT(a USING utf8mb4))	HEX(CONVERT(@a USING utf16le))
00D800DC	F0908080	00D800DC
00D8FFDC	F09083BF	00D8FFDC
7FDB00DC	F3AFB080	7FDB00DC
7FDBFFDC	F3AFB3BF	7FDBFFDC
C000	C380	C000
FF00	C3BF	FF00
00E0	EE8080	00E0
FFFF	EFBFBF	FFFF
DROP TABLE t1;
#
# Test basic regex functionality
#
SET NAMES utf8, collation_connection=utf16le_general_ci;
#
# Bug#31081 server crash in regexp function
#
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 utf16le COLLATE utf16le_general_ci DEFAULT NULL,
  `s2` varchar(64) CHARACTER SET utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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
0
0
0
1
1
1
1
1
1
1
NULL
NULL
NULL
NULL
drop table t1;
#
# Test how CHARACTER SET works with date/time
#
#
# Bug#32390 Character sets: casting utf32 to/from date doesn't work
#
CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0;
SET time_zone=_latin1'+03:00';
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	32003000300038002D00300037002D0031003800
08:42:04	300038003A00340032003A0030003400
2008-07-18 08:42:04	32003000300038002D00300037002D00310038002000300038003A00340032003A0030003400
DROP TABLE t1;
SET timestamp=0;
SET time_zone=default;
#
# MDEV-5298 Illegal mix of collations on timestamp
#
SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY);
CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY)
utf16le
SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY);
COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY)
6
SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
binary
SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY);
COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)
7
SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
utf16le
SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY));
COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY))
6
SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
utf16le
SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY));
COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY))
6
SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
utf16le
SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
6
SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY));
HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY))
32003000300031002D00300031002D00300031002000300030003A00300030003A0030003000
SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
utf16le
SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
6
SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY));
HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
32003000300031002D00300031002D00300031002000300030003A00300030003A0030003000
CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(64) CHARACTER SET utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES ('');
SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
utf16le
SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
2
SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY))
32003000300031002D00300031002D00300031002000300030003A00300030003A0030003000
SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
utf16le
SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
2
SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1;
HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY))
32003000300031002D00300031002D00300031002000300030003A00300030003A0030003000
DROP TABLE t1;
CREATE TABLE t1 (t TIMESTAMP NOT NULL);
INSERT INTO t1 VALUES ('2001-01-01 00:00:00');
SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY;
t
2001-01-01 00:00:00
DROP TABLE t1;
#
# Bug#33073 Character sets: ordering fails with utf32
#
SET NAMES utf8, collation_connection=utf16le_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 utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES ('ab'),('AE'),('ab'),('AE');
SELECT * FROM t1 ORDER BY s1;
s1
ab
ab
AE
AE
SET max_sort_length=8;
Warnings:
Warning	1292	Truncated incorrect max_sort_length value: '8'
SELECT * FROM t1 ORDER BY s1;
s1
ab
ab
AE
AE
DROP TABLE t1;
SET max_sort_length=DEFAULT;
#
# Bug#52520 Difference in tinytext utf column metadata
#
CREATE TABLE t1 (
s1 TINYTEXT CHARACTER SET utf16le,
s2 TEXT CHARACTER SET utf16le,
s3 MEDIUMTEXT CHARACTER SET utf16le,
s4 LONGTEXT CHARACTER SET utf16le
);
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	56
def	test	t1	t1	s2	s2	252	65535	0	Y	16	0	56
def	test	t1	t1	s3	s3	252	16777215	0	Y	16	0	56
def	test	t1	t1	s4	s4	252	4294967295	0	Y	16	0	56
def					HEX(s1)	253	6120	0	Y	0	0	192
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	192
def	test	t1	t1	s2	s2	252	98301	0	Y	16	0	192
def	test	t1	t1	s3	s3	252	25165821	0	Y	16	0	192
def	test	t1	t1	s4	s4	252	4294967295	0	Y	16	0	192
def					HEX(s1)	253	6120	0	Y	0	0	192
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 utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
DROP TABLE t1, t2;
#
# Problem found by Roy during review
# MY_CS_BINSORT was not set for utf16le_bin,
# so filesort did not work well
# 
SET NAMES utf8, @@collation_connection=utf16le_bin;
CREATE TABLE t1 AS SELECT REPEAT(' ', 10) as c LIMIT 0;
ALTER TABLE t1 ADD PRIMARY KEY(c);
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `c` varchar(10) CHARACTER SET utf16le COLLATE utf16le_bin NOT NULL,
  PRIMARY KEY (`c`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES ('abc'),('zyx'),('acb');
SELECT UPPER(c) FROM t1 ORDER BY 1 DESC;
UPPER(c)
ZYX
ACB
ABC
DROP TABLE t1;
#
# WL#3664 WEIGHT_STRING
#
SET NAMES utf8, collation_connection=utf16le_general_ci;
select @@collation_connection;
@@collation_connection
utf16le_general_ci
CREATE TABLE t1 AS SELECT 'a' AS a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1) CHARACTER SET utf16le COLLATE utf16le_general_ci NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(2) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
0041
SELECT HEX(ws) FROM t2;
HEX(ws)
0041
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('a',5) AS a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(5) CHARACTER SET utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
00410041004100410041
SELECT HEX(ws) FROM t2;
HEX(ws)
00410041004100410041
DROP TABLE t2;
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(3)) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(6) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(3)))
004100410041
SELECT HEX(ws) FROM t2;
HEX(ws)
004100410041
DROP TABLE t2;
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(10)) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(20) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(10)))
0041004100410041004100200020002000200020
SELECT HEX(ws) FROM t2;
HEX(ws)
0041004100410041004100200020002000200020
DROP TABLE t2;
DROP TABLE t1;
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
utf16le_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(_utf16le 0x00D800DC));
HEX(WEIGHT_STRING(_utf16le 0x00D800DC))
FFFD
SELECT HEX(WEIGHT_STRING(_utf16le 0x00D801DC));
HEX(WEIGHT_STRING(_utf16le 0x00D801DC))
FFFD
select @@collation_connection;
@@collation_connection
utf16le_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 NAMES utf8, collation_connection=utf16le_bin;
select @@collation_connection;
@@collation_connection
utf16le_bin
CREATE TABLE t1 AS SELECT 'a' AS a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(1) CHARACTER SET utf16le COLLATE utf16le_bin NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(3) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
000061
SELECT HEX(ws) FROM t2;
HEX(ws)
000061
DROP TABLE t2;
DROP TABLE t1;
CREATE TABLE t1 AS SELECT REPEAT('a',5) AS a;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(5) CHARACTER SET utf16le COLLATE utf16le_bin DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(15) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a)) FROM t1;
HEX(WEIGHT_STRING(a))
000061000061000061000061000061
SELECT HEX(ws) FROM t2;
HEX(ws)
000061000061000061000061000061
DROP TABLE t2;
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(3)) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(9) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(3)))
000061000061000061
SELECT HEX(ws) FROM t2;
HEX(ws)
000061000061000061
DROP TABLE t2;
CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(10)) AS ws FROM t1;
SHOW CREATE TABLE t2;
Table	Create Table
t2	CREATE TABLE `t2` (
  `ws` varbinary(30) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(10)))
000061000061000061000061000061000020000020000020000020000020
SELECT HEX(ws) FROM t2;
HEX(ws)
000061000061000061000061000061000020000020000020000020000020
DROP TABLE t2;
DROP TABLE t1;
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
utf16le_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
utf16le_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
#
#
# Start of 10.0 tests
#
SET NAMES utf8, character_set_connection=utf16le;
#
# MDEV-13118 Wrong results with LOWER and UPPER and subquery
#
SET @save_optimizer_switch=@@optimizer_switch;
SET optimizer_switch=_latin1'derived_merge=on';
CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0;
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `t` varchar(10) CHARACTER SET utf16le COLLATE utf16le_general_ci DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI');
SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub;
c2
abcdefghi-abcdefghi
abcdefghi-abcdefghi
SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub;
c2
ABCDEFGHI-ABCDEFGHI
ABCDEFGHI-ABCDEFGHI
DROP TABLE t1;
SET optimizer_switch=@save_optimizer_switch;
#
# Start of 10.0 tests
#
#
# Start of 10.1 tests
#
#
# MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character"
#
CREATE TABLE t1 (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
a VARCHAR(10) CHARACTER SET utf16le, KEY(a,id)
);
INSERT INTO t1 (a) VALUES (_utf8mb4 0x61);
INSERT INTO t1 (a) VALUES (_utf8mb4 0xC280),(_utf8mb4 0xDFBF);
INSERT INTO t1 (a) VALUES (_utf8mb4 0xE0A080),(_utf8mb4 0xEFBFBF);
INSERT INTO t1 (a) VALUES (_utf8mb4 0xF0908080),(_utf8mb4 0xF48FBFBF);
SELECT id,HEX(a) FROM t1 ORDER BY a,id;
id	HEX(a)
1	6100
2	8000
3	FF07
4	0008
6	00D800DC
7	FFDBFFDF
5	FFFF
SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
id	HEX(a)
5	FFFF
7	FFDBFFDF
6	00D800DC
4	0008
3	FF07
2	8000
1	6100
SELECT COUNT(DISTINCT a) FROM t1;
COUNT(DISTINCT a)
6
ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf16le COLLATE utf16le_bin;
SELECT id,HEX(a) FROM t1 ORDER BY a;
id	HEX(a)
1	6100
2	8000
3	FF07
4	0008
5	FFFF
6	00D800DC
7	FFDBFFDF
SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
id	HEX(a)
7	FFDBFFDF
6	00D800DC
5	FFFF
4	0008
3	FF07
2	8000
1	6100
SELECT COUNT(DISTINCT a) FROM t1;
COUNT(DISTINCT a)
7
DROP TABLE t1;
#
# MDEV-9178 Wrong result for CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED)
#
SET NAMES utf8;
SELECT CAST(CONVERT('1IJ3' USING utf16le) AS SIGNED);
CAST(CONVERT('1IJ3' USING utf16le) AS SIGNED)
1
Warnings:
Warning	1292	Truncated incorrect INTEGER value: '1IJ3'
#
# End of 10.1 tests
#
#
# Start of 10.2 tests
#
#
# MDEV-9711 NO PAD Collatons
#
SET collation_connection=utf16le_general_nopad_ci;
SET DEFAULT_STORAGE_ENGINE=MyISAM;
#
# Start of ctype_pad.inc
#
#
# Unique indexes
#
CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf16le_general_nopad_ci';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf16le COLLATE=utf16le_general_nopad_ci
INSERT INTO t1 VALUES ('abc'),('abc  '),(' a'),(' a '),('a   ');
SELECT HEX(a), a FROM t1 ORDER BY a;
HEX(a)	a
20006100	 a
200061002000	 a 
6100200020002000	a   
610062006300	abc
61006200630020002000	abc  
SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a;
HEX(a)	a
20006100	 a
200061002000	 a 
6100200020002000	a   
610062006300	abc
61006200630020002000	abc  
SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC;
HEX(a)	a
61006200630020002000	abc  
610062006300	abc
6100200020002000	a   
200061002000	 a 
20006100	 a
#
# UNION
#
CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf16le_general_nopad_ci';
INSERT INTO t2 VALUES ('abc '),('abc  '),(' a'),('a ');
SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td;
HEX(a)	a
20006100	 a
200061002000	 a 
61002000	a 
6100200020002000	a   
610062006300	abc
6100620063002000	abc 
61006200630020002000	abc  
DROP TABLE t1;
DROP TABLE t2;
#
# DISTINCT, COUNT, MAX
#
CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf16le_general_nopad_ci';
INSERT INTO t1 VALUES ('a'),('a  '),(' a'),(' a '),('a   ');
SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td;
HEX(a)	a
20006100	 a
200061002000	 a 
6100	a
610020002000	a  
6100200020002000	a   
SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a;
COUNT(DISTINCT a)
5
SELECT HEX(MAX(a)), MAX(a) FROM t1;
HEX(MAX(a))	MAX(a)
6100200020002000	a   
#
# GROUP BY
#
CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf16le_general_nopad_ci';
INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa  ', 0, 'er ');
SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td;
HEX(a)	cnt
61006100	1
6100610020002000	1
61006200	2
DROP TABLE t2;
#
# Weights
#
SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(10)))
0041000000000000000000000000000000000000
0041002000200000000000000000000000000000
0020004100000000000000000000000000000000
0020004100200000000000000000000000000000
0041002000200020000000000000000000000000
DROP TABLE t1;
#
# IF, CASE, LEAST
#
SELECT IF('abc' COLLATE 'utf16le_general_nopad_ci' = 'abc ', 'pad', 'nopad') as exp;
exp
nopad
SELECT CASE 'abc' COLLATE 'utf16le_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END as exp;
exp
nopad
SELECT CASE WHEN 'abc' COLLATE 'utf16le_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END as exp;
exp
nopad
SELECT HEX(LEAST('abc ' COLLATE 'utf16le_general_nopad_ci', 'abc  ')) as exp;
exp
6100620063002000
SELECT HEX(GREATEST('abc ' COLLATE 'utf16le_general_nopad_ci', 'abc  ')) as exp;
exp
61006200630020002000
#
# Collation mix
#
CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf16le_general_ci';
INSERT INTO t1 VALUES ('a'),('a ');
SELECT COUNT(*) FROM t1 WHERE a='a';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_general_ci';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_general_nopad_ci';
COUNT(*)
1
ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf16le_general_nopad_ci';
SELECT COUNT(*) FROM t1 WHERE a='a';
COUNT(*)
1
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_general_ci';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_general_nopad_ci';
COUNT(*)
1
DROP TABLE t1;
#
# End of ctype_pad.inc
#
SET DEFAULT_STORAGE_ENGINE=HEAP;
#
# Start of ctype_pad.inc
#
#
# Unique indexes
#
CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf16le_general_nopad_ci';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MEMORY DEFAULT CHARSET=utf16le COLLATE=utf16le_general_nopad_ci
INSERT INTO t1 VALUES ('abc'),('abc  '),(' a'),(' a '),('a   ');
SELECT HEX(a), a FROM t1 ORDER BY a;
HEX(a)	a
20006100	 a
200061002000	 a 
6100200020002000	a   
610062006300	abc
61006200630020002000	abc  
SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a;
HEX(a)	a
20006100	 a
200061002000	 a 
6100200020002000	a   
610062006300	abc
61006200630020002000	abc  
SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC;
HEX(a)	a
61006200630020002000	abc  
610062006300	abc
6100200020002000	a   
200061002000	 a 
20006100	 a
#
# UNION
#
CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf16le_general_nopad_ci';
INSERT INTO t2 VALUES ('abc '),('abc  '),(' a'),('a ');
SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td;
HEX(a)	a
20006100	 a
200061002000	 a 
61002000	a 
6100200020002000	a   
610062006300	abc
6100620063002000	abc 
61006200630020002000	abc  
DROP TABLE t1;
DROP TABLE t2;
#
# DISTINCT, COUNT, MAX
#
CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf16le_general_nopad_ci';
INSERT INTO t1 VALUES ('a'),('a  '),(' a'),(' a '),('a   ');
SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td;
HEX(a)	a
20006100	 a
200061002000	 a 
6100	a
610020002000	a  
6100200020002000	a   
SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a;
COUNT(DISTINCT a)
5
SELECT HEX(MAX(a)), MAX(a) FROM t1;
HEX(MAX(a))	MAX(a)
6100200020002000	a   
#
# GROUP BY
#
CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf16le_general_nopad_ci';
INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa  ', 0, 'er ');
SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td;
HEX(a)	cnt
61006100	1
6100610020002000	1
61006200	2
DROP TABLE t2;
#
# Weights
#
SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(10)))
0041000000000000000000000000000000000000
0041002000200000000000000000000000000000
0020004100000000000000000000000000000000
0020004100200000000000000000000000000000
0041002000200020000000000000000000000000
DROP TABLE t1;
#
# IF, CASE, LEAST
#
SELECT IF('abc' COLLATE 'utf16le_general_nopad_ci' = 'abc ', 'pad', 'nopad') as exp;
exp
nopad
SELECT CASE 'abc' COLLATE 'utf16le_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END as exp;
exp
nopad
SELECT CASE WHEN 'abc' COLLATE 'utf16le_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END as exp;
exp
nopad
SELECT HEX(LEAST('abc ' COLLATE 'utf16le_general_nopad_ci', 'abc  ')) as exp;
exp
6100620063002000
SELECT HEX(GREATEST('abc ' COLLATE 'utf16le_general_nopad_ci', 'abc  ')) as exp;
exp
61006200630020002000
#
# Collation mix
#
CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf16le_general_ci';
INSERT INTO t1 VALUES ('a'),('a ');
SELECT COUNT(*) FROM t1 WHERE a='a';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_general_ci';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_general_nopad_ci';
COUNT(*)
1
ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf16le_general_nopad_ci';
SELECT COUNT(*) FROM t1 WHERE a='a';
COUNT(*)
1
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_general_ci';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_general_nopad_ci';
COUNT(*)
1
DROP TABLE t1;
#
# End of ctype_pad.inc
#
SET DEFAULT_STORAGE_ENGINE=Default;
select @@collation_connection;
@@collation_connection
utf16le_general_nopad_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 utf16le COLLATE utf16le_general_nopad_ci DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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	2	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	2	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 collation_connection=utf16le_nopad_bin;
SET DEFAULT_STORAGE_ENGINE=MyISAM;
#
# Start of ctype_pad.inc
#
#
# Unique indexes
#
CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf16le_nopad_bin';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf16le COLLATE=utf16le_nopad_bin
INSERT INTO t1 VALUES ('abc'),('abc  '),(' a'),(' a '),('a   ');
SELECT HEX(a), a FROM t1 ORDER BY a;
HEX(a)	a
20006100	 a
200061002000	 a 
6100200020002000	a   
610062006300	abc
61006200630020002000	abc  
SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a;
HEX(a)	a
20006100	 a
200061002000	 a 
6100200020002000	a   
610062006300	abc
61006200630020002000	abc  
SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC;
HEX(a)	a
61006200630020002000	abc  
610062006300	abc
6100200020002000	a   
200061002000	 a 
20006100	 a
#
# UNION
#
CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf16le_nopad_bin';
INSERT INTO t2 VALUES ('abc '),('abc  '),(' a'),('a ');
SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td;
HEX(a)	a
20006100	 a
200061002000	 a 
61002000	a 
6100200020002000	a   
610062006300	abc
6100620063002000	abc 
61006200630020002000	abc  
DROP TABLE t1;
DROP TABLE t2;
#
# DISTINCT, COUNT, MAX
#
CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf16le_nopad_bin';
INSERT INTO t1 VALUES ('a'),('a  '),(' a'),(' a '),('a   ');
SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td;
HEX(a)	a
20006100	 a
200061002000	 a 
6100	a
610020002000	a  
6100200020002000	a   
SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a;
COUNT(DISTINCT a)
5
SELECT HEX(MAX(a)), MAX(a) FROM t1;
HEX(MAX(a))	MAX(a)
6100200020002000	a   
#
# GROUP BY
#
CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf16le_nopad_bin';
INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa  ', 0, 'er ');
SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td;
HEX(a)	cnt
61006100	1
6100610020002000	1
61006200	2
DROP TABLE t2;
#
# Weights
#
SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(10)))
000061000000000000000000000000000000000000000000000000000000
000061000020000020000000000000000000000000000000000000000000
000020000061000000000000000000000000000000000000000000000000
000020000061000020000000000000000000000000000000000000000000
000061000020000020000020000000000000000000000000000000000000
DROP TABLE t1;
#
# IF, CASE, LEAST
#
SELECT IF('abc' COLLATE 'utf16le_nopad_bin' = 'abc ', 'pad', 'nopad') as exp;
exp
nopad
SELECT CASE 'abc' COLLATE 'utf16le_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END as exp;
exp
nopad
SELECT CASE WHEN 'abc' COLLATE 'utf16le_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END as exp;
exp
nopad
SELECT HEX(LEAST('abc ' COLLATE 'utf16le_nopad_bin', 'abc  ')) as exp;
exp
6100620063002000
SELECT HEX(GREATEST('abc ' COLLATE 'utf16le_nopad_bin', 'abc  ')) as exp;
exp
61006200630020002000
#
# Collation mix
#
CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf16le_bin';
INSERT INTO t1 VALUES ('a'),('a ');
SELECT COUNT(*) FROM t1 WHERE a='a';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_bin';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_nopad_bin';
COUNT(*)
1
ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf16le_nopad_bin';
SELECT COUNT(*) FROM t1 WHERE a='a';
COUNT(*)
1
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_bin';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_nopad_bin';
COUNT(*)
1
DROP TABLE t1;
#
# End of ctype_pad.inc
#
SET DEFAULT_STORAGE_ENGINE=HEAP;
#
# Start of ctype_pad.inc
#
#
# Unique indexes
#
CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf16le_nopad_bin';
SHOW CREATE TABLE t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(10) NOT NULL,
  PRIMARY KEY (`a`)
) ENGINE=MEMORY DEFAULT CHARSET=utf16le COLLATE=utf16le_nopad_bin
INSERT INTO t1 VALUES ('abc'),('abc  '),(' a'),(' a '),('a   ');
SELECT HEX(a), a FROM t1 ORDER BY a;
HEX(a)	a
20006100	 a
200061002000	 a 
6100200020002000	a   
610062006300	abc
61006200630020002000	abc  
SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a;
HEX(a)	a
20006100	 a
200061002000	 a 
6100200020002000	a   
610062006300	abc
61006200630020002000	abc  
SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC;
HEX(a)	a
61006200630020002000	abc  
610062006300	abc
6100200020002000	a   
200061002000	 a 
20006100	 a
#
# UNION
#
CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf16le_nopad_bin';
INSERT INTO t2 VALUES ('abc '),('abc  '),(' a'),('a ');
SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td;
HEX(a)	a
20006100	 a
200061002000	 a 
61002000	a 
6100200020002000	a   
610062006300	abc
6100620063002000	abc 
61006200630020002000	abc  
DROP TABLE t1;
DROP TABLE t2;
#
# DISTINCT, COUNT, MAX
#
CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf16le_nopad_bin';
INSERT INTO t1 VALUES ('a'),('a  '),(' a'),(' a '),('a   ');
SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td;
HEX(a)	a
20006100	 a
200061002000	 a 
6100	a
610020002000	a  
6100200020002000	a   
SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a;
COUNT(DISTINCT a)
5
SELECT HEX(MAX(a)), MAX(a) FROM t1;
HEX(MAX(a))	MAX(a)
6100200020002000	a   
#
# GROUP BY
#
CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf16le_nopad_bin';
INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa  ', 0, 'er ');
SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td;
HEX(a)	cnt
61006100	1
6100610020002000	1
61006200	2
DROP TABLE t2;
#
# Weights
#
SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1;
HEX(WEIGHT_STRING(a AS CHAR(10)))
000061000000000000000000000000000000000000000000000000000000
000061000020000020000000000000000000000000000000000000000000
000020000061000000000000000000000000000000000000000000000000
000020000061000020000000000000000000000000000000000000000000
000061000020000020000020000000000000000000000000000000000000
DROP TABLE t1;
#
# IF, CASE, LEAST
#
SELECT IF('abc' COLLATE 'utf16le_nopad_bin' = 'abc ', 'pad', 'nopad') as exp;
exp
nopad
SELECT CASE 'abc' COLLATE 'utf16le_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END as exp;
exp
nopad
SELECT CASE WHEN 'abc' COLLATE 'utf16le_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END as exp;
exp
nopad
SELECT HEX(LEAST('abc ' COLLATE 'utf16le_nopad_bin', 'abc  ')) as exp;
exp
6100620063002000
SELECT HEX(GREATEST('abc ' COLLATE 'utf16le_nopad_bin', 'abc  ')) as exp;
exp
61006200630020002000
#
# Collation mix
#
CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf16le_bin';
INSERT INTO t1 VALUES ('a'),('a ');
SELECT COUNT(*) FROM t1 WHERE a='a';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_bin';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_nopad_bin';
COUNT(*)
1
ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf16le_nopad_bin';
SELECT COUNT(*) FROM t1 WHERE a='a';
COUNT(*)
1
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_bin';
COUNT(*)
2
SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf16le_nopad_bin';
COUNT(*)
1
DROP TABLE t1;
#
# End of ctype_pad.inc
#
SET DEFAULT_STORAGE_ENGINE=Default;
select @@collation_connection;
@@collation_connection
utf16le_nopad_bin
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 utf16le COLLATE utf16le_nopad_bin DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_uca1400_ai_ci
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	2	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	2	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
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
#
# MDEV-23408 Wrong result upon query from I_S and further Assertion `!alias_arg || strlen(alias_arg->str) == alias_arg->length' failed with certain connection charset
#
SET NAMES utf8;
SET SESSION character_set_connection= utf16le;
CREATE TABLE kv (v TEXT CHARACTER SET latin1);
CREATE TABLE t (a INT);
CREATE VIEW v AS SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 't1';
LOAD DATA INFILE 'MYSQLD_DATADIR/test/v.frm' REPLACE INTO TABLE kv CHARACTER SET latin1;
SELECT LOWER(v) FROM kv WHERE v LIKE _binary'query=%';
LOWER(v)
query=select `information_schema`.`tables`.`table_catalog` as `table_catalog`,`information_schema`.`tables`.`table_schema` as `table_schema`,`information_schema`.`tables`.`table_name` as `table_name`,`information_schema`.`tables`.`table_type` as `table_type`,`information_schema`.`tables`.`engine` as `engine`,`information_schema`.`tables`.`version` as `version`,`information_schema`.`tables`.`row_format` as `row_format`,`information_schema`.`tables`.`table_rows` as `table_rows`,`information_schema`.`tables`.`avg_row_length` as `avg_row_length`,`information_schema`.`tables`.`data_length` as `data_length`,`information_schema`.`tables`.`max_data_length` as `max_data_length`,`information_schema`.`tables`.`index_length` as `index_length`,`information_schema`.`tables`.`data_free` as `data_free`,`information_schema`.`tables`.`auto_increment` as `auto_increment`,`information_schema`.`tables`.`create_time` as `create_time`,`information_schema`.`tables`.`update_time` as `update_time`,`information_schema`.`tables`.`check_time` as `check_time`,`information_schema`.`tables`.`table_collation` as `table_collation`,`information_schema`.`tables`.`checksum` as `checksum`,`information_schema`.`tables`.`create_options` as `create_options`,`information_schema`.`tables`.`table_comment` as `table_comment`,`information_schema`.`tables`.`max_index_length` as `max_index_length`,`information_schema`.`tables`.`temporary` as `temporary` from `information_schema`.`tables` where `information_schema`.`tables`.`table_name` = 't1'
TRUNCATE TABLE kv;
SELECT * FROM v;
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT	MAX_INDEX_LENGTH	TEMPORARY
LOCK TABLE t WRITE;
UNLOCK TABLES;
DROP VIEW v;
DROP TABLE t;
DROP TABLE kv;
CREATE TABLE t (a INT);
SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=CONCAT('t',0x00,'1');
TABLE_CATALOG	TABLE_SCHEMA	TABLE_NAME	TABLE_TYPE	ENGINE	VERSION	ROW_FORMAT	TABLE_ROWS	AVG_ROW_LENGTH	DATA_LENGTH	MAX_DATA_LENGTH	INDEX_LENGTH	DATA_FREE	AUTO_INCREMENT	CREATE_TIME	UPDATE_TIME	CHECK_TIME	TABLE_COLLATION	CHECKSUM	CREATE_OPTIONS	TABLE_COMMENT	MAX_INDEX_LENGTH	TEMPORARY
LOCK TABLE t WRITE;
UNLOCK TABLES;
DROP TABLE t;
CREATE TABLE t (a INT);
SELECT TABLE_NAME, HEX(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME=CONCAT('t',0x00,'1');
TABLE_NAME	HEX(TABLE_NAME)
SELECT TABLE_NAME, TABLE_SCHEMA, HEX(TABLE_NAME) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA=CONCAT('test',0x00,'1');
TABLE_NAME	TABLE_SCHEMA	HEX(TABLE_NAME)
DROP TABLE t;
SET NAMES utf8;
#
# End of 10.2 tests
#