path: root/mysql-test/main/ctype_utf16.test

-- source include/have_utf16.inc
-- source include/have_utf8mb4.inc

SET TIME_ZONE='+03:00';

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

--echo #
--echo # Start of 5.5 tests
--echo #
--disable_service_connection

SET NAMES latin1;
SET character_set_connection=utf16;
select hex('a'), hex('a ');
-- source include/endspace.inc


# Check that incomplete utf16 characters in HEX notation
# are left-padded with zeros
#
select hex(_utf16 0x44);
select hex(_utf16 0x3344);
select hex(_utf16 0x113344);


# 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 utf16, 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 utf16;
INSERT INTO t1 VALUES (_koi8r 0xF2, _koi8r 0xF2), (X'2004',X'2004');
SELECT hex(word) FROM t1 ORDER BY word;
SELECT hex(word2) FROM t1 ORDER BY word2;
DELETE FROM t1;

#
# Check that real spaces are correctly trimmed.
#
INSERT INTO t1 VALUES (X'042000200020',X'042000200020'), (X'200400200020', X'200400200020');
SELECT hex(word) FROM t1 ORDER BY word;
SELECT hex(word2) FROM t1 ORDER BY word2;
DROP TABLE t1;


#
# Check LPAD/RPAD
#
#enable after fix MDEV-29290
--disable_view_protocol
SELECT hex(LPAD(_utf16 X'0420',10,_utf16 X'0421'));
SELECT hex(LPAD(_utf16 X'0420',10,_utf16 X'04210422'));
SELECT hex(LPAD(_utf16 X'0420',10,_utf16 X'042104220423'));
SELECT hex(LPAD(_utf16 X'0420042104220423042404250426042704280429042A042B',10,_utf16 X'042104220423'));
SELECT hex(LPAD(_utf16 X'D800DC00', 10, _utf16 X'0421'));
SELECT hex(LPAD(_utf16 X'0421', 10, _utf16 X'D800DC00'));

SELECT hex(RPAD(_utf16 X'0420',10,_utf16 X'0421'));
SELECT hex(RPAD(_utf16 X'0420',10,_utf16 X'04210422'));
SELECT hex(RPAD(_utf16 X'0420',10,_utf16 X'042104220423'));
SELECT hex(RPAD(_utf16 X'0420042104220423042404250426042704280429042A042B',10,_utf16 X'042104220423'));
SELECT hex(RPAD(_utf16 X'D800DC00', 10, _utf16 X'0421'));
SELECT hex(RPAD(_utf16 X'0421', 10, _utf16 X'D800DC00'));
--enable_view_protocol

CREATE TABLE t1 SELECT 
LPAD(_utf16 X'0420',10,_utf16 X'0421') l,
RPAD(_utf16 X'0420',10,_utf16 X'0421') r;
SHOW CREATE TABLE t1;
select hex(l), hex(r) from t1;
DROP TABLE t1;

create table t1 (f1 char(30));
insert into t1 values ("103000"), ("22720000"), ("3401200"), ("78000");
select lpad(f1, 12, "-o-/") from t1;
drop table t1;

######################################################
#
# Test of like
#

SET NAMES latin1;
SET character_set_connection=utf16;
--source include/ctype_like.inc

SET NAMES utf8;
SET character_set_connection=utf16;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf16);
INSERT INTO t1 VALUES ('фыва'),('Фыва'),('фЫва'),('фыВа'),('фывА'),('ФЫВА');
INSERT INTO t1 VALUES ('фывапролдж'),('Фывапролдж'),('фЫвапролдж'),('фыВапролдж');
INSERT INTO t1 VALUES ('фывАпролдж'),('фываПролдж'),('фывапРолдж'),('фывапрОлдж');
INSERT INTO t1 VALUES ('фывапроЛдж'),('фывапролДж'),('фывапролдЖ'),('ФЫВАПРОЛДЖ');
SELECT * FROM t1 WHERE a LIKE '%фЫва%' ORDER BY BINARY a;
SELECT * FROM t1 WHERE a LIKE '%фЫв%' ORDER BY BINARY a;
SELECT * FROM t1 WHERE a LIKE 'фЫва%' ORDER BY BINARY a;
SELECT * FROM t1 WHERE a LIKE 'фЫва%' COLLATE utf16_bin ORDER BY BINARY a;
DROP TABLE t1;

CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word))
ENGINE=MyISAM CHARACTER SET utf16;
INSERT INTO t1 (word) VALUES ("cat");
SELECT * FROM t1 WHERE word LIKE "c%";
SELECT * FROM t1 WHERE word LIKE "ca_";
SELECT * FROM t1 WHERE word LIKE "cat";
SELECT * FROM t1 WHERE word LIKE _utf16 x'00630025';     # "c%"
SELECT * FROM t1 WHERE word LIKE _utf16 x'00630061005F'; # "ca_"
DROP TABLE t1;


#
# Check that INSERT() works fine. 
# This invokes charpos() function.
select insert(_utf16 0x006100620063,10,2,_utf16 0x006400650066);
select insert(_utf16 0x006100620063,1,2,_utf16 0x006400650066);

########################################################
#
# Bug 1264
#
# Description: 
#
# When using a ucs2 table in MySQL, 
# either with ucs2_general_ci or ucs2_bin collation,
# words are returned in an incorrect order when using ORDER BY
# on an _indexed_ CHAR or VARCHAR column. They are sorted with
# the longest word *first* instead of last. I.E. The word "aardvark"
# is in the results before the word "a".
#
# If there is no index for the column, the problem does not occur.
#
# Interestingly, if there is no second column, the words are returned
# in the correct order. 
#
# According to EXPLAIN, it looks like when the output includes columns that
# are not part of the index sorted on, it does a filesort, which fails. 
# Using a straight index yields correct results.

SET NAMES latin1;

#
# Two fields, index
#

CREATE TABLE t1 (
   word VARCHAR(64),
   bar INT(11) default 0,
   PRIMARY KEY (word))
   ENGINE=MyISAM
   CHARSET utf16
   COLLATE utf16_general_ci ;

INSERT INTO t1 (word) VALUES ("aar");
INSERT INTO t1 (word) VALUES ("a");
INSERT INTO t1 (word) VALUES ("aardvar");
INSERT INTO t1 (word) VALUES ("aardvark");
INSERT INTO t1 (word) VALUES ("aardvara");
INSERT INTO t1 (word) VALUES ("aardvarz");
EXPLAIN SELECT * FROM t1 ORDER BY word;
SELECT * FROM t1 ORDER BY word;
EXPLAIN SELECT word FROM t1 ORDER BY word;
SELECT word FROM t1 ORDER by word;
DROP TABLE t1;


#
# One field, index
# 

CREATE TABLE t1 (
   word VARCHAR(64) ,
   PRIMARY KEY (word))
   ENGINE=MyISAM
   CHARSET utf16
   COLLATE utf16_general_ci;

INSERT INTO t1 (word) VALUES ("aar");
INSERT INTO t1 (word) VALUES ("a");
INSERT INTO t1 (word) VALUES ("aardvar");
INSERT INTO t1 (word) VALUES ("aardvark");
INSERT INTO t1 (word) VALUES ("aardvara");
INSERT INTO t1 (word) VALUES ("aardvarz");
EXPLAIN SELECT * FROM t1 ORDER BY WORD;
SELECT * FROM t1 ORDER BY word;
DROP TABLE t1;


#
# Two fields, no index
#

CREATE TABLE t1 (
   word TEXT,
   bar INT(11) AUTO_INCREMENT,
   PRIMARY KEY (bar))
   ENGINE=MyISAM
   CHARSET utf16
   COLLATE utf16_general_ci ;
INSERT INTO t1 (word) VALUES ("aar");
INSERT INTO t1 (word) VALUES ("a" );
INSERT INTO t1 (word) VALUES ("aardvar");
INSERT INTO t1 (word) VALUES ("aardvark");
INSERT INTO t1 (word) VALUES ("aardvara");
INSERT INTO t1 (word) VALUES ("aardvarz");
EXPLAIN SELECT * FROM t1 ORDER BY word;
SELECT * FROM t1 ORDER BY word;
EXPLAIN SELECT word FROM t1 ORDER BY word;
SELECT word FROM t1 ORDER BY word;
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 utf16));
SELECT hex(convert(0xAA using utf16));

#
# Check alignment for string types
#
CREATE TABLE t1 (a char(10) character set utf16);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a varchar(10) character set utf16);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a text character set utf16);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a mediumtext character set utf16);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
DROP TABLE t1;

CREATE TABLE t1 (a longtext character set utf16);
INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111);
SELECT HEX(a) FROM t1;
DROP TABLE t1;

##
## Bug #5024 Server crashes with queries on fields
##  with certain charset/collation settings
##
##
#create table t1 (s1 char character set utf16 collate utf16_czech_ci);
#insert into t1 values ('0'),('1'),('2'),('a'),('b'),('c');
#select s1 from t1 where s1 > 'a' order by s1;
#drop table t1;
#

#
# Bug #5081 : UCS2 fields are filled with '0x2020'
# after extending field length
#
create table t1(a char(1)) default charset utf16;
insert into t1 values ('a'),('b'),('c');
alter table t1 modify a char(5);
select a, hex(a) from t1;
drop table t1;

#
# Check prepare statement from an UTF16 string
#
set @ivar= 1234;
set @str1 = 'select ?';
set @str2 = convert(@str1 using utf16);
prepare stmt1 from @str2;
execute stmt1 using @ivar;

#
# Check that utf16 works with ENUM and SET type
#
set names utf8;
create table t1 (a enum('x','y','z') character set utf16);
show create table t1;
insert into t1 values ('x');
insert into t1 values ('y');
insert into t1 values ('z');
select a, hex(a) from t1 order by a;
alter table t1 change a a enum('x','y','z','d','e','ä','ö','ü') character set utf16;
show create table t1;
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;
drop table t1;

create table t1 (a set ('x','y','z','ä','ö','ü') character set utf16);
show create table t1;
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;
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 utf16;
insert into t1 values('a'),('b'),('c');
alter table t1 add b char(1);
show warnings;
select * from t1 order by a;
drop table t1;

SET NAMES latin1;
SET collation_connection='utf16_general_ci';
-- source include/ctype_filesort.inc
-- source include/ctype_like_escape.inc
SET NAMES latin1;
SET collation_connection='utf16_bin';
-- source include/ctype_filesort.inc
-- source include/ctype_filesort2.inc
-- source include/ctype_like_escape.inc

#
# Bug#10344 Some string functions fail for UCS2
#
select hex(substr(_utf16 0x00e400e50068,1));
select hex(substr(_utf16 0x00e400e50068,2));
select hex(substr(_utf16 0x00e400e50068,3));
select hex(substr(_utf16 0x00e400e50068,-1));
select hex(substr(_utf16 0x00e400e50068,-2));
select hex(substr(_utf16 0x00e400e50068,-3));
select hex(substr(_utf16 0x00e400e5D800DC00,1));
select hex(substr(_utf16 0x00e400e5D800DC00,2));
select hex(substr(_utf16 0x00e400e5D800DC00,3));
select hex(substr(_utf16 0x00e400e5D800DC00,-1));
select hex(substr(_utf16 0x00e400e5D800DC00,-2));
select hex(substr(_utf16 0x00e400e5D800DC00,-3));

SET NAMES latin1;

##
## Bug#8235
##
## This bug also helped to find another problem that
## INSERT of a UCS2 string containing a negative number
## into a unsigned int column didn't produce warnings.
## This test covers both problems.
##
##SET collation_connection='ucs2_swedish_ci';
##CREATE TABLE t1 (Field1 int(10) default '0');
### no warnings, negative numbers are allowed
##INSERT INTO t1 VALUES ('-1');
##SELECT * FROM t1;
##DROP TABLE t1;
##CREATE TABLE t1 (Field1 int(10) unsigned default '0');
### this should generate a "Data truncated" warning
##INSERT INTO t1 VALUES ('-1');
##DROP TABLE t1;
##SET NAMES latin1;

###
### Bug #14583 Bug on query using a LIKE on indexed field with ucs2_bin collation
###
##--disable_warnings
##create table t1(f1 varchar(5) CHARACTER SET utf16 COLLATE utf16_bin NOT NULL) engine=InnoDB;
##--enable_warnings
##insert into t1 values('a');
##create index t1f1 on t1(f1);
##select f1 from t1 where f1 like 'a%';
##drop table t1;

#
# Bug#9442 Set parameter make query fail if column character set is UCS2
#
create table t1 (utext varchar(20) character set utf16);
insert into t1 values ("lily");
insert into t1 values ("river");
prepare stmt from 'select utext from t1 where utext like ?';
set @param1='%%';
execute stmt using @param1;
execute stmt using @param1;
select utext from t1 where utext like '%%';
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 utf16 not null, 
  index a (a)
) engine=myisam;
insert into t1 values (repeat(0x201f, 10));
insert into t1 values (repeat(0x2020, 10));
insert into t1 values (repeat(0x2021, 10));
# make sure "index read" is used
explain select hex(a) from t1 order by a;
select hex(a) from t1 order by a;
alter table t1 drop index a;
select hex(a) from t1 order by a;
drop table t1;

##
## Bug #20076: server crashes for a query with GROUP BY if MIN/MAX aggregation
##             over a 'ucs2' field uses a temporary table 
##
##CREATE TABLE t1 (id int, s char(5) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci);
##INSERT INTO t1 VALUES (1, 'ZZZZZ'), (1, 'ZZZ'), (2, 'ZZZ'), (2, 'ZZZZZ');
##SELECT id, MIN(s) FROM t1 GROUP BY id;
##DROP TABLE t1;

###
### Bug #20536: md5() with GROUP BY and UCS2 return different results on myisam/innodb
###
##
##--disable_warnings
##drop table if exists bug20536;
##--enable_warnings
##
##set names latin1;
##create table bug20536 (id bigint not null auto_increment primary key, name
##varchar(255) character set ucs2 not null);
##insert into `bug20536` (`id`,`name`) values (1, _latin1 x'7465737431'), (2, "'test\\_2'");
##select md5(name) from bug20536;
##select sha1(name) from bug20536;
##select make_set(3, name, upper(name)) from bug20536;
##select export_set(5, name, upper(name)) from bug20536;
##select export_set(5, name, upper(name), ",", 5) from bug20536;

#
# Bug #20108: corrupted default enum value for a ucs2 field              
#

CREATE TABLE t1 (
  status enum('active','passive') character set utf16 collate utf16_general_ci 
    NOT NULL default 'passive'
);
SHOW CREATE TABLE t1;
ALTER TABLE t1 ADD a int NOT NULL AFTER status; 
SHOW CREATE TABLE t1;
DROP TABLE t1;

##CREATE TABLE t2 (
##  status enum('active','passive') collate ucs2_turkish_ci 
##    NOT NULL default 'passive'
##);
##SHOW CREATE TABLE t2;
##ALTER TABLE t2 ADD a int NOT NULL AFTER status; 
##DROP TABLE t2;


--echo End of 4.1 tests

#
# Conversion from an UTF16 string to a decimal column
#
CREATE TABLE t1 (a varchar(64) character set utf16, b decimal(10,3));
INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0);
update t1 set b=a;
SELECT *, hex(a) FROM t1;
DROP TABLE t1;

#
# Bug#9442 Set parameter make query fail if column character set is UCS2
#
create table t1 (utext varchar(20) character set utf16);
insert into t1 values ("lily");
insert into t1 values ("river");
prepare stmt from 'select utext from t1 where utext like ?';
set @param1='%%';
execute stmt using @param1;
execute stmt using @param1;
select utext from t1 where utext like '%%';
drop table t1;
deallocate prepare stmt;

#
# Bug#22638 SOUNDEX broken for international characters
#
set names latin1;
set character_set_connection=utf16;
select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb');
select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb'));
select 'mood' sounds like 'mud';
# Cyrillic A, BE, VE
select hex(soundex(_utf16 0x041004110412));
# Make sure that "U+00BF INVERTED QUESTION MARK" is not considered as letter
select hex(soundex(_utf16 0x00BF00C0));
set names latin1;

#
# Bug #14290: character_maximum_length for text fields
#
create table t1(a blob, b text charset utf16);
select data_type, character_octet_length, character_maximum_length
  from information_schema.columns where table_name='t1';
drop table t1;


set names latin1;
set collation_connection=utf16_general_ci;
#
# Testing instr()
#
select position('bb' in 'abba');

#
# Testing hash_sort()
#
create table t1 (a varchar(10) character set utf16) engine=heap;
insert into t1 values ('a'),('A'),('b'),('B');
select * from t1 where a='a' order by binary a;
select hex(min(binary a)),count(*) from t1 group by a;
drop table t1;

#
# Testing numchars()
#
select char_length('abcd'), octet_length('abcd');
select char_length(_utf16 0xD800DC00), octet_length(_utf16 0xD800DC00);
select char_length(_utf16 0xD87FDFFF), octet_length(_utf16 0xD87FDFFF);

#
# Testing charpos()
#
select left('abcd',2);
select hex(left(_utf16 0xD800DC00D87FDFFF, 1));
select hex(right(_utf16 0xD800DC00D87FDFFF, 1));

#
# Testing well_formed_length()
#
create table t1 (a varchar(10) character set utf16);
# Bad sequences
--error ER_INVALID_CHARACTER_STRING
insert into t1 values (_utf16 0xD800);
--error ER_INVALID_CHARACTER_STRING
insert into t1 values (_utf16 0xDC00);
--error ER_INVALID_CHARACTER_STRING
insert into t1 values (_utf16 0xD800D800);
--error ER_INVALID_CHARACTER_STRING
insert into t1 values (_utf16 0xD800E800);
--error ER_INVALID_CHARACTER_STRING
insert into t1 values (_utf16 0xD8000800);
# Good sequences
insert into t1 values (_utf16 0xD800DC00);
insert into t1 values (_utf16 0xD800DCFF);
insert into t1 values (_utf16 0xDBFFDC00);
insert into t1 values (_utf16 0xDBFFDCFF);
select hex(a) from t1;
drop table t1;

#
# Bug#32393 Character sets: illegal characters in utf16 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 utf16;
select hex(s1) from t1;
drop table t1;
#
# via update
#
create table t1 (s1 varchar(5) character set ucs2, s2 varchar(5) character set utf16);
insert into t1 (s1) values (0xdf84);
update ignore t1 set s2 = s1;
select hex(s2) from t1;
drop table t1;



#
# Testing lengthsp()
#
create table t1 (a char(10)) character set utf16;
insert into t1 values ('a   ');
select hex(a) from t1;
drop table t1;

#
# Testing caseup() and casedn()
#
select upper('abcd'), lower('ABCD');

#
# TODO: str_to_datetime() is broken and doesn't work with ucs2 and utf16
# Testing snprintf()
#
#create table t1 (a date);
#insert into t1 values ('2007-09-16');
#select * from t1;
#drop table t1;

#
# Testing l10tostr
# !!! Not used in the code

#
# Testing ll10tostr
#
create table t1 (a varchar(10) character set utf16);
insert into t1 values (123456);
select a, hex(a) from t1;
drop table t1;


# Testing fill
# SOUNDEX fills strings with DIGIT ZERO up to four characters
select hex(soundex('a'));

#
# Testing strntol
# !!! Not used in the code

#
# Testing strntoul
#
create table t1 (a enum ('a','b','c')) character set utf16;
insert into t1 values ('1');
select * from t1;
drop table t1;

#
# Testing strntoll and strntoull
#
set names latin1;
select hex(conv(convert('123' using utf16), -10, 16));
select hex(conv(convert('123' using utf16), 10, 16));

#
# Testing strntod
#
set names latin1;
set character_set_connection=utf16;
select 1.1 + '1.2';
select 1.1 + '1.2xxx';

# Testing strntoll10_utf16
# Testing strtoll10
select left('aaa','1');
--source include/ctype_strtoll10.inc

#
# Testing strntoull10rnd
#
create table t1 (a int);
insert into t1 values ('-1234.1e2');
insert ignore into t1 values ('-1234.1e2xxxx');
insert into t1 values ('-1234.1e2    ');
select * from t1;
drop table t1;

#
# Testing scan
#
create table t1 (a int);
insert into t1 values ('1 ');
insert ignore into t1 values ('1 x');
select * from t1;
drop table t1;

#
# Testing auto-conversion to TEXT
#
SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
create table t1 (a varchar(17000) character set utf16);
show create table t1;
drop table t1;

#
# Testing that maximim possible key length is 1000 bytes
#
create table t1 (a varchar(250) character set utf16 primary key);
show create table t1;
drop table t1;
--error ER_TOO_LONG_KEY
create table t1 (a varchar(334) character set utf16 primary key);

#
# Conversion to utf8
#
create table t1 (a char(1) character set utf16);
insert into t1 values (0xD800DC00),(0xD800DCFF),(0xDB7FDC00),(0xDB7FDCFF);
insert into t1 values (0x00C0), (0x00FF),(0xE000), (0xFFFF);
select hex(a), hex(@a:=convert(a using utf8mb4)), hex(convert(@a using utf16)) from t1;
drop table t1;

#
# Test basic regex functionality
#
set collation_connection=utf16_general_ci;
--source include/ctype_regex.inc
set names latin1;

#
# Test how character set works with date/time
#
SET collation_connection=utf16_general_ci;
--source include/ctype_datetime.inc
SET NAMES latin1;

#
# Bug#33073 Character sets: ordering fails with utf32
#
SET collation_connection=utf16_general_ci;
CREATE TABLE t1 AS SELECT repeat('a',2) as s1 LIMIT 0;
SHOW CREATE TABLE t1;
INSERT INTO t1 VALUES ('ab'),('AE'),('ab'),('AE');
SELECT * FROM t1 ORDER BY s1;
SET max_sort_length=8;
SELECT * FROM t1 ORDER BY s1;
DROP TABLE t1;
SET max_sort_length=DEFAULT;
SET NAMES latin1;

--echo #
--echo # Bug#52520 Difference in tinytext utf column metadata
--echo #
CREATE TABLE t1 (
  s1 TINYTEXT CHARACTER SET utf16,
  s2 TEXT CHARACTER SET utf16,
  s3 MEDIUMTEXT CHARACTER SET utf16,
  s4 LONGTEXT CHARACTER SET utf16
);
--disable_view_protocol
--enable_metadata
SET NAMES utf8, @@character_set_results=NULL;
SELECT *, HEX(s1) FROM t1;
SET NAMES latin1;
SELECT *, HEX(s1) FROM t1;
SET NAMES utf8;
SELECT *, HEX(s1) FROM t1;
--disable_metadata
--enable_view_protocol
CREATE TABLE t2 AS SELECT CONCAT(s1) FROM t1;
SHOW CREATE TABLE t2;
DROP TABLE t1, t2;

--echo #
--echo # Bug#11753363 (Bug#44793) Character sets: case clause, ucs2 or utf32, failure
--echo #
SELECT CASE _latin1'a' WHEN _utf16'a' THEN 'A' END;
SELECT CASE _utf16'a' WHEN _latin1'a' THEN 'A' END;
CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET utf16);
INSERT INTO t1 VALUES ('a');
SELECT CASE s1 WHEN 'a' THEN 'b' ELSE 'c' END FROM t1;
DROP TABLE t1;

--echo #
--echo # Bug#12340997
--echo # DATE_ADD/DATE_SUB WITH INTERVAL CRASHES IN GET_INTERVAL_VALUE()
--echo #

let $max_packet=`select @@max_allowed_packet`;
--replace_result $max_packet XXX
SELECT space(date_add(101, INTERVAL CHAR('1' USING utf16) hour_second)); 

--echo #
--echo # Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT
--echo #

SET NAMES utf8, @@character_set_connection=utf16;
SET STATEMENT group_concat_max_len=1024 FOR
SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l
FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body
UNION ALL
SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1
GROUP BY id
ORDER BY l DESC;

SET STATEMENT group_concat_max_len=1024 FOR
SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l
FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body
UNION ALL
SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1;

#
## TODO: add tests for all engines
#

--echo #
--echo # MDEV-6865 Merge Bug#18935421 RPAD DIES WITH CERTAIN PADSTR INTPUTS..
--echo #
--error ER_INVALID_CHARACTER_STRING
DO RPAD(_utf16 0x0061 COLLATE utf16_unicode_ci, 10000, 0x0061DE989999);
--error ER_INVALID_CHARACTER_STRING
DO LPAD(_utf16 0x0061 COLLATE utf16_unicode_ci, 10000, 0x0061DE989999);

--echo #
--echo # MDEV-11685: sql_mode can't be set with non-ascii connection charset
--echo #
SET character_set_connection=utf16;
SET sql_mode='NO_ENGINE_SUBSTITUTION';
SELECT @@sql_mode;
SET sql_mode=DEFAULT;
SET NAMES utf8;

--echo #
--echo # End of 5.5 tests
--echo #


--echo #
--echo # Start of 5.6 tests
--echo #

--echo #
--echo # WL#3664 WEIGHT_STRING
--echo #

set collation_connection=utf16_general_ci;
--source include/weight_string.inc
--source include/weight_string_euro.inc
select hex(weight_string(_utf16 0xD800DC00));
select hex(weight_string(_utf16 0xD800DC01));
--source include/weight_string_l1.inc

set collation_connection=utf16_bin;
--source include/weight_string.inc
--source include/weight_string_euro.inc
--source include/weight_string_l1.inc

--echo #
--echo # End of 5.6 tests
--echo #

--echo #
--echo # Start of 10.0 tests
--echo #

--echo #
--echo # MDEV-6661 PI() does not work well in UCS2/UTF16/UTF32 context
--echo #
SELECT CONCAT(CONVERT('pi=' USING utf16),PI()) AS PI;

--echo #
--echo # MDEV-6666 Malformed result for CONCAT(utf8_column, binary_string)
--echo #

SET NAMES utf8mb4;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf16);
INSERT INTO t1 VALUES ('a');
--error ER_INVALID_CHARACTER_STRING
SELECT CONCAT(a,0xD800) FROM t1;
SELECT CONCAT(a,0xD800DC00) FROM t1;
SELECT CONCAT(a,0x00FF) FROM t1;
DROP TABLE t1;
--error ER_INVALID_CHARACTER_STRING
SELECT CONCAT(_utf16'a' COLLATE utf16_unicode_ci, _binary 0xD800);
PREPARE stmt FROM "SELECT CONCAT(_utf16'a' COLLATE utf16_unicode_ci, ?)";
SET @arg00=_binary 0xD800;
--error ER_INVALID_CHARACTER_STRING
EXECUTE stmt USING @arg00;
SET @arg00=_binary 0xD800DC00;
EXECUTE stmt USING @arg00;
SET @arg00=_binary 0x00FF;
EXECUTE stmt USING @arg00;
DEALLOCATE PREPARE stmt;


SET NAMES utf8, character_set_connection=utf16;
--source include/ctype_mdev13118.inc


--echo #
--echo # End of 10.0 tests
--echo #

--echo #
--echo # Start of 10.1 tests
--echo #

--echo #
--echo # MDEV-8417 utf8mb4: compare broken bytes as "greater than any non-broken character"
--echo #
CREATE TABLE t1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  a VARCHAR(10) CHARACTER SET utf16, 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;
SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
SELECT COUNT(DISTINCT a) FROM t1;
ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET utf16 COLLATE utf16_bin;
SELECT id,HEX(a) FROM t1 ORDER BY a;
SELECT id,HEX(a) FROM t1 ORDER BY a DESC,id DESC;
SELECT COUNT(DISTINCT a) FROM t1;
DROP TABLE t1;

--echo #
--echo # MDEV-9178 Wrong result for CAST(CONVERT('1IJ3' USING utf16) AS SIGNED)
--echo #
SET NAMES utf8;
SELECT CAST(CONVERT('1IJ3' USING utf16) AS SIGNED);


--echo #
--echo # End of 10.1 tests
--echo #

--echo #
--echo # Start of 10.2 tests
--echo #

--echo #
--echo # MDEV-9711 NO PAD Collatons
--echo #
SET character_set_connection=utf16;
let $coll='utf16_general_nopad_ci';
let $coll_pad='utf16_general_ci';
--source include/ctype_pad_all_engines.inc

let $coll='utf16_nopad_bin';
let $coll_pad='utf16_bin';
--source include/ctype_pad_all_engines.inc

--echo #
--echo # End of 10.2 tests
--echo #

--echo #
--echo # Start of 10.3 tests
--echo #

--echo #
--echo # MDEV-22391 Assertion `0' failed in Item_type_holder::val_str on utf16 charset table query
--echo #

SET NAMES utf8;
CREATE TABLE t1 (a TEXT CHARACTER SET utf16);
SELECT * FROM (VALUES (1) UNION SELECT * FROM t1) AS t;
DROP TABLE t1;
VALUES (1) UNION SELECT _utf16 0x0020;
VALUES ('') UNION SELECT _utf16 0x0020 COLLATE utf16_bin;
VALUES ('') UNION VALUES ( _utf16 0x0020 COLLATE utf16_bin);
--enable_service_connection

--echo #
--echo # End of 10.3 tests
--echo #


--echo #
--echo # Start of 10.5 tests
--echo #

--echo #
--echo # MDEV-8844 Unreadable control characters printed as is in warnings
--echo #

SET NAMES utf8;
# Make sure surrogate halfs (when a part of a full utf16 character)
# are not escaped and the entire utf16 character consisting of two
# surrogate pairs is replaced to a single question mark.
SELECT CAST(_utf16 0x0061D83DDE0E0062 AS INT);


--echo #
--echo # End of 10.5 tests
--echo #