Create 'main' test directory and move 't' and 'r' there

1 files changed, 1069 insertions, 0 deletions

diff --git a/mysql-test/main/ctype_ucs.test b/mysql-test/main/ctype_ucs.test
new file mode 100644
index 00000000000..d7a4fd48ccd
--- /dev/null
+++ b/mysql-test/main/ctype_ucs.test
@@ -0,0 +1,1069 @@
+-- source include/have_ucs2.inc
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+SET @test_character_set= 'ucs2';
+SET @test_collation= 'ucs2_general_ci';
+-- source include/ctype_common.inc
+
+SET NAMES latin1;
+SET character_set_connection=ucs2;
+-- source include/endspace.inc
+
+SET CHARACTER SET koi8r;
+
+#
+# BUG#49028, error in LIKE with ucs2
+#
+create table t1 (a varchar(2) character set ucs2 collate ucs2_bin, key(a));
+insert into t1 values ('A'),('A'),('B'),('C'),('D'),('A\t');
+insert into t1 values ('A\0'),('A\0'),('A\0'),('A\0'),('AZ');
+select hex(a) from t1 where a like 'A_' order by a;
+select hex(a) from t1 ignore key(a) where a like 'A_' order by a;
+drop table t1;
+
+#
+# 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 UCS2, 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) CHARACTER SET ucs2, word2 CHAR(64) CHARACTER SET ucs2);
+INSERT INTO t1 VALUES (_koi8r'ò',_koi8r'ò'), (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
+#
+SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'0421');
+SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'04210422');
+SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'042104220423');
+SELECT LPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423');
+
+SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'0421');
+SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'04210422');
+SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'042104220423');
+SELECT RPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423');
+
+CREATE TABLE t1 SELECT 
+LPAD(_ucs2 X'0420',10,_ucs2 X'0421') l,
+RPAD(_ucs2 X'0420',10,_ucs2 X'0421') r;
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug #51876 : crash/memory underrun when loading data with ucs2 
+--echo #   and reverse() function
+--echo #
+
+--echo # Problem # 1 (original report): wrong parsing of ucs2 data
+SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp.txt';
+CREATE TABLE t1(a INT);
+LOAD DATA INFILE 'tmpp.txt' INTO TABLE t1 CHARACTER SET ucs2
+(@b) SET a=REVERSE(@b);
+--echo # should return 2 zeroes (as the value is truncated)
+SELECT * FROM t1;
+
+DROP TABLE t1;
+let $MYSQLD_DATADIR= `select @@datadir`;
+remove_file $MYSQLD_DATADIR/test/tmpp.txt;
+
+
+--echo # Problem # 2 : if you write and read ucs2 data to a file they're lost
+SELECT '00' UNION SELECT '10' INTO OUTFILE 'tmpp2.txt' CHARACTER SET ucs2;
+CREATE TABLE t1(a INT);
+LOAD DATA INFILE 'tmpp2.txt' INTO TABLE t1 CHARACTER SET ucs2
+(@b) SET a=REVERSE(@b);
+--echo # should return 0 and 1 (10 reversed)
+SELECT * FROM t1;
+
+DROP TABLE t1;
+let $MYSQLD_DATADIR= `select @@datadir`;
+remove_file $MYSQLD_DATADIR/test/tmpp2.txt;
+
+
+
+#
+# BUG3946
+#
+
+create table t2(f1 Char(30));
+insert into t2 values ("103000"), ("22720000"), ("3401200"), ("78000");
+select lpad(f1, 12, "-o-/") from t2;
+drop table t2;
+
+######################################################
+#
+# Test of like
+#
+
+SET NAMES koi8r;
+SET character_set_connection=ucs2;
+--source include/ctype_like.inc
+
+CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ucs2);
+INSERT INTO t1 VALUES ('ÆÙ×Á'),('æÙ×Á'),('Æù×Á'),('ÆÙ÷Á'),('ÆÙ×á'),('æù÷á');
+INSERT INTO t1 VALUES ('ÆÙ×ÁÐÒÏÌÄÖ'),('æÙ×ÁÐÒÏÌÄÖ'),('Æù×ÁÐÒÏÌÄÖ'),('ÆÙ÷ÁÐÒÏÌÄÖ');
+INSERT INTO t1 VALUES ('ÆÙ×áÐÒÏÌÄÖ'),('ÆÙ×ÁðÒÏÌÄÖ'),('ÆÙ×ÁÐòÏÌÄÖ'),('ÆÙ×ÁÐÒïÌÄÖ');
+INSERT INTO t1 VALUES ('ÆÙ×ÁÐÒÏìÄÖ'),('ÆÙ×ÁÐÒÏÌäÖ'),('ÆÙ×ÁÐÒÏÌÄö'),('æù÷áðòïìäö');
+SELECT * FROM t1 WHERE a LIKE '%Æù×Á%';
+SELECT * FROM t1 WHERE a LIKE '%Æù×%';
+SELECT * FROM t1 WHERE a LIKE 'Æù×Á%';
+SELECT * FROM t1 WHERE a LIKE 'Æù×Á%' COLLATE ucs2_bin;
+DROP TABLE t1;
+
+#
+# Bug 1181
+#
+CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word))
+ENGINE=MyISAM CHARACTER SET ucs2 COLLATE ucs2_general_ci;
+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 _ucs2 x'00630025';
+SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630061005F';
+DROP TABLE t1;
+
+#
+# Check that INSERT works fine. 
+# This invokes charpos() function.
+select insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066);
+select insert(_ucs2 0x006100620063,1,2,_ucs2 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 ucs2
+   COLLATE ucs2_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 ucs2
+   COLLATE ucs2_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 ucs2
+   COLLATE ucs2_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
+#
+########################################################
+
+
+# Bug #2390
+# Check alignment for constants
+#
+SELECT HEX(_ucs2 0x0);
+SELECT HEX(_ucs2 0x01);
+SELECT HEX(_ucs2 0x012);
+SELECT HEX(_ucs2 0x0123);
+SELECT HEX(_ucs2 0x01234);
+SELECT HEX(_ucs2 0x012345);
+SELECT HEX(_ucs2 0x0123456);
+SELECT HEX(_ucs2 0x01234567);
+SELECT HEX(_ucs2 0x012345678);
+SELECT HEX(_ucs2 0x0123456789);
+SELECT HEX(_ucs2 0x0123456789A);
+SELECT HEX(_ucs2 0x0123456789AB);
+SELECT HEX(_ucs2 0x0123456789ABC);
+SELECT HEX(_ucs2 0x0123456789ABCD);
+SELECT HEX(_ucs2 0x0123456789ABCDE);
+SELECT HEX(_ucs2 0x0123456789ABCDEF);
+
+#
+# Check alignment for from-binary-conversion with CAST and CONVERT
+#
+SELECT hex(cast(0xAA as char character set ucs2));
+SELECT hex(convert(0xAA using ucs2));
+
+#
+# Check alignment for string types
+#
+CREATE TABLE t1 (a char(10) character set ucs2);
+INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a varchar(10) character set ucs2);
+INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a text character set ucs2);
+INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a mediumtext character set ucs2);
+INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a longtext character set ucs2);
+INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA);
+SELECT HEX(a) FROM t1;
+DROP TABLE t1;
+
+# the same should be also done with enum and set
+
+
+#
+# Bug #5024 Server crashes with queries on fields
+#  with certain charset/collation settings
+#
+
+create table t1 (s1 char character set `ucs2` collate `ucs2_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 = ucs2;
+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 UCS2 string
+#
+set @ivar= 1234;
+set @str1 = 'select ?';
+set @str2 = convert(@str1 using ucs2);
+prepare stmt1 from @str2;
+execute stmt1 using @ivar;
+
+#
+# Check that ucs2 works with ENUM and SET type
+#
+set names latin1;
+create table t1 (a enum('x','y','z') character set ucs2);
+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 ucs2;
+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 ucs2);
+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 ucs2;
+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 collation_connection='ucs2_general_ci';
+-- source include/ctype_filesort.inc
+-- source include/ctype_like_escape.inc
+-- source include/ctype_german.inc
+-- source include/ctype_like_range_f1f2.inc
+-- source include/ctype_str_to_date.inc
+SET NAMES latin1;
+SET collation_connection='ucs2_bin';
+-- source include/ctype_filesort.inc
+-- source include/ctype_like_escape.inc
+-- source include/ctype_like_range_f1f2.inc
+
+#
+# Bug#10344 Some string functions fail for UCS2
+#
+select hex(substr(_ucs2 0x00e400e50068,1));
+select hex(substr(_ucs2 0x00e400e50068,2));
+select hex(substr(_ucs2 0x00e400e50068,3));
+select hex(substr(_ucs2 0x00e400e50068,-1));
+select hex(substr(_ucs2 0x00e400e50068,-2));
+select hex(substr(_ucs2 0x00e400e50068,-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 IGNORE INTO t1 VALUES ('-1');
+DROP TABLE t1;
+SET NAMES latin1;
+
+#
+# Bug#18691 Converting number to UNICODE string returns invalid result
+#
+SELECT CONVERT(103, CHAR(50) UNICODE);
+SELECT CONVERT(103.0, CHAR(50) UNICODE);
+SELECT CONVERT(-103, CHAR(50) UNICODE);
+SELECT CONVERT(-103.0, CHAR(50) UNICODE);
+
+#
+# Bug#9557 MyISAM utf8 table crash
+#
+CREATE TABLE t1 (
+  a varchar(255) NOT NULL default '',
+  KEY a (a)
+) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE ucs2_general_ci;
+insert into t1 values (0x803d);
+insert into t1 values (0x005b);
+select hex(a) from t1;
+drop table t1;
+
+#
+# Bug #14583 Bug on query using a LIKE on indexed field with ucs2_bin collation
+#
+set sql_mode="";
+--disable_warnings
+create table t1(f1 varchar(5) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL) engine=InnoDB;
+--enable_warnings
+set sql_mode=default;
+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 ucs2);
+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) unicode 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') collate latin1_general_ci 
+    NOT NULL default 'passive'
+);
+SHOW CREATE TABLE t1;
+ALTER TABLE t1 ADD a int NOT NULL AFTER status; 
+
+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 t1,t2;
+
+
+# Some broken functions:  add these tests just to document current behavior.
+
+# PASSWORD and OLD_PASSWORD don't work with UCS2 strings, but to fix it would
+# not be backwards compatible in all cases, so it's best to leave it alone
+select password(name) from bug20536;
+select old_password(name) from bug20536;
+
+# Disable test case as encrypt relies on 'crypt' function.
+# "decrypt" is noramlly tested in func_crypt.test which have a
+# "have_crypt.inc" test
+--disable_parsing
+# ENCRYPT relies on OS function crypt() which takes a NUL-terminated string; it
+# doesn't return good results for strings with embedded 0 bytes.  It won't be
+# fixed unless we choose to re-implement the crypt() function ourselves to take
+# an extra size_t string_length argument.
+select encrypt(name, 'SALT') from bug20536;
+--enable_parsing
+
+# QUOTE doesn't work with UCS2 data.  It would require a total rewrite
+# of Item_func_quote::val_str(), which isn't worthwhile until UCS2 is
+# supported fully as a client character set.
+select quote(name) from bug20536;
+
+drop table bug20536;
+
+#
+# Bug #31615: crash after set names ucs2 collate xxx
+#
+--error 1231
+set names ucs2;
+--error 1231
+set names ucs2 collate ucs2_bin;
+--error 1231
+set character_set_client= ucs2;
+--error 1231
+set character_set_client= concat('ucs', substr('2', 1));
+
+#
+# BUG#31159 - fulltext search on ucs2 column crashes server
+#
+CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci);
+INSERT INTO t1 VALUES('abcd');
+SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE);
+DROP TABLE t1;
+
+--echo End of 4.1 tests
+
+#
+# Conversion from an UCS2 string to a decimal column
+#
+CREATE TABLE t1 (a varchar(64) character set ucs2, b decimal(10,3));
+INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0);
+update t1 set b=a;
+SELECT * 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 ucs2);
+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=ucs2;
+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(_ucs2 0x041004110412));
+# Make sure that "U+00BF INVERTED QUESTION MARK" is not considered as letter
+select hex(soundex(_ucs2 0x00BF00C0));
+set names latin1;
+
+#
+# Bug #14290: character_maximum_length for text fields
+#
+create table t1(a blob, b text charset utf8, c text charset ucs2);
+select data_type, character_octet_length, character_maximum_length
+  from information_schema.columns where table_name='t1';
+drop table t1;
+
+#
+# Bug#28925 GROUP_CONCAT inserts wrong separators for a ucs2 column
+#
+create table t1 (a char(1) character set ucs2);
+insert into t1 values ('a'),('b'),('c');
+select hex(group_concat(a)) from t1;
+select collation(group_concat(a)) from t1;
+drop table t1;
+
+set names latin1;
+create table t1 (a char(1) character set latin1);
+insert into t1 values ('a'),('b'),('c');
+set character_set_connection=ucs2;
+select hex(group_concat(a separator ',')) from t1;
+select collation(group_concat(a separator ',')) from t1;
+drop table t1;
+set names latin1;
+
+#
+# Bug#29499 Converting 'del' from ascii to Unicode results in 'question mark'
+#
+create table t1 (s1 char(1) character set ascii, s2 char(1) character set ucs2);
+insert into t1 (s1) values (0x7f);
+update t1 set s2 = s1;
+select hex(s2) from t1;
+select hex(convert(s1 using latin1)) from t1;
+drop table t1;
+
+#
+# Conversion from UCS2 to ASCII is possible
+# if the UCS2 string consists of only ASCII characters
+#
+create table t1 (a varchar(15) character set ascii not null, b int);
+insert into t1 values ('a',1);
+select concat(a,if(b<10,_ucs2 0x0061,_ucs2 0x0062)) from t1;
+select concat(a,if(b>10,_ucs2 0x0061,_ucs2 0x0062)) from t1;
+select * from t1 where a=if(b<10,_ucs2 0x0061,_ucs2 0x0062);
+select * from t1 where a=if(b>10,_ucs2 0x0061,_ucs2 0x0062);
+
+#
+# Conversion from UCS2 to ASCII is not possible if 
+# the UCS2 string has non-ASCII characters
+#
+--error 1267
+select concat(a,if(b<10,_ucs2 0x00C0,_ucs2 0x0062)) from t1;
+--error 1267
+select concat(a,if(b>10,_ucs2 0x00C0,_ucs2 0x0062)) from t1;
+--error 1267
+select concat(a,if(b<10,_ucs2 0x0062,_ucs2 0x00C0)) from t1;
+--error 1267
+select concat(a,if(b>10,_ucs2 0x0062,_ucs2 0x00C0)) from t1;
+--error 1267
+select * from t1 where a=if(b<10,_ucs2 0x00C0,_ucs2 0x0062);
+--error 1267
+select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0);
+drop table t1;
+
+#
+# Bug#35720 ucs2 + pad_char_to_full_length = failure
+#
+CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET UCS2);
+INSERT INTO t1 VALUES ('a');
+SET @@sql_mode=pad_char_to_full_length;
+SELECT HEX(s1) FROM t1;
+SET @@sql_mode=default;
+SELECT HEX(s1) FROM t1;
+DROP TABLE t1;
+
+set collation_connection=ucs2_general_ci;
+--source include/ctype_regex.inc
+set names latin1;
+#
+# Bug#30981 CHAR(0x41 USING ucs2) doesn't add leading zero
+#
+select hex(char(0x41 using ucs2));
+
+#
+# Bug#37575: UCASE fails on monthname
+#
+SET character_set_connection=ucs2;
+SELECT CHARSET(DAYNAME(19700101));
+SELECT CHARSET(MONTHNAME(19700101));
+SELECT LOWER(DAYNAME(19700101));
+SELECT LOWER(MONTHNAME(19700101));
+SELECT UPPER(DAYNAME(19700101));
+SELECT UPPER(MONTHNAME(19700101));
+SELECT HEX(MONTHNAME(19700101));
+SELECT HEX(DAYNAME(19700101));
+SET LC_TIME_NAMES=ru_RU;
+SET NAMES utf8;
+SET character_set_connection=ucs2;
+SELECT CHARSET(DAYNAME(19700101));
+SELECT CHARSET(MONTHNAME(19700101));
+SELECT LOWER(DAYNAME(19700101));
+SELECT LOWER(MONTHNAME(19700101));
+SELECT UPPER(DAYNAME(19700101));
+SELECT UPPER(MONTHNAME(19700101));
+SELECT HEX(MONTHNAME(19700101));
+SELECT HEX(DAYNAME(19700101));
+SET character_set_connection=latin1;
+
+--echo #
+--echo # Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817
+--echo #
+CREATE TABLE t1 (a CHAR(1) CHARSET ascii, b CHAR(1) CHARSET latin1);
+CREATE VIEW v1 AS SELECT 1 from t1
+WHERE t1.b <=> (SELECT a FROM t1 WHERE a < SOME(SELECT '1'));
+DROP VIEW v1;
+DROP TABLE t1;
+
+--echo #
+--echo # Bug#59648 my_strtoll10_mb2: Assertion `(*endptr - s) % 2 == 0' failed.
+--echo #
+--disable_warnings
+# Enable warnings when "MDEV-8844 Unreadable control characters printed as is in warnings" is fixed
+SELECT HEX(CHAR(COALESCE(NULL, CHAR(COUNT('%s') USING ucs2), 1, @@global.license, NULL) USING cp850));
+--enable_warnings
+SELECT CONVERT(QUOTE(CHAR(0xf5 using ucs2)), SIGNED);
+
+--echo End of 5.0 tests
+
+--echo #
+--echo # Start of 5.1 tests
+--echo #
+
+#
+# Checking my_like_range_ucs2
+#
+SET NAMES utf8;
+CREATE TABLE t1 (
+  a varchar(10) CHARACTER SET ucs2 COLLATE ucs2_czech_ci,
+  key(a)
+);
+INSERT INTO t1 VALUES
+('aa'),('bb'),('cc'),('dd'),('ee'),('ff'),('gg'),('hh'),('ii'),
+('jj'),('kk'),('ll'),('mm'),('nn'),('oo'),('pp'),('rr'),('ss'),
+('tt'),('uu'),('vv'),('ww'),('xx'),('yy'),('zz');
+INSERT INTO t1 VALUES ('ca'),('cz'),('ch');
+INSERT INTO t1 VALUES ('da'),('dz'), (X'0064017E');
+# This one should scan only one row
+EXPLAIN SELECT * FROM t1 WHERE a LIKE 'b%';
+# This one should scan many rows: 'c' is a contraction head
+EXPLAIN SELECT * FROM t1 WHERE a LIKE 'c%';
+SELECT * FROM t1 WHERE a LIKE 'c%';
+EXPLAIN SELECT * FROM t1 WHERE a LIKE 'ch%';
+SELECT * FROM t1 WHERE a LIKE 'ch%';
+ALTER TABLE t1 MODIFY a VARCHAR(10) CHARACTER SET ucs2 COLLATE ucs2_croatian_ci;
+EXPLAIN SELECT * FROM t1 WHERE a LIKE 'd%';
+SELECT hex(concat('d',_ucs2 0x017E,'%'));
+EXPLAIN SELECT * FROM t1 WHERE a LIKE concat('d',_ucs2 0x017E,'%');
+SELECT hex(a) FROM t1 WHERE a LIKE concat('D',_ucs2 0x017E,'%');
+
+DROP TABLE t1;
+
+--echo #
+--echo # End of 5.1 tests
+--echo #
+
+--echo #
+--echo # Start of 5.5 tests
+--echo #
+
+SET NAMES latin1;
+SET collation_connection=ucs2_general_ci;
+--source include/ctype_numconv.inc
+SET NAMES latin1;
+
+--echo #
+--echo # Bug #13832953 	MY_STRNXFRM_UNICODE: ASSERTION `SRC' FAILED
+--echo #
+SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR
+CREATE TABLE t1 (c1 SET('','') CHARACTER SET ucs2);
+INSERT INTO t1 VALUES ('');
+SELECT COALESCE(c1) FROM t1 ORDER BY 1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-5745 analyze MySQL fix for bug#12368495
+--echo #
+SELECT CHAR_LENGTH(TRIM(LEADING 0x000000 FROM _ucs2 0x0061));
+SELECT CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _ucs2 0x0061));
+SELECT CHAR_LENGTH(TRIM(LEADING 0x00 FROM _ucs2 0x0061));
+
+SELECT CHAR_LENGTH(TRIM(TRAILING 0x000000 FROM _ucs2 0x0061));
+SELECT CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _ucs2 0x0061));
+SELECT CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _ucs2 0x0061));
+
+SELECT CHAR_LENGTH(TRIM(BOTH 0x000000 FROM _ucs2 0x0061));
+SELECT CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _ucs2 0x0061));
+SELECT CHAR_LENGTH(TRIM(BOTH 0x61 FROM _ucs2 0x0061));
+SELECT CHAR_LENGTH(TRIM(BOTH 0x00 FROM _ucs2 0x0061));
+
+--echo #
+--echo # MDEV-11685: sql_mode can't be set with non-ascii connection charset
+--echo #
+SET character_set_connection=ucs2;
+SET sql_mode='NO_ENGINE_SUBSTITUTION';
+SELECT @@sql_mode;
+SET sql_mode=DEFAULT;
+SET NAMES utf8;
+
+--echo #
+--echo # MDEV-13972 crash in Item_func_sec_to_time::get_date
+--echo #
+
+SELECT SEC_TO_TIME(CONVERT(900*24*60*60 USING ucs2));
+
+--echo #
+--echo # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535
+--echo #
+
+set sql_mode="";
+CREATE TABLE t1 (c1 VARCHAR(32766) CHARACTER SET ucs2);
+DESCRIBE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 VARCHAR(32767) CHARACTER SET ucs2);
+DESCRIBE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (c1 VARCHAR(32768) CHARACTER SET ucs2);
+DESCRIBE t1;
+DROP TABLE t1;
+set sql_mode=default;
+
+
+--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=ucs2_general_ci;
+--source include/weight_string.inc
+--source include/weight_string_euro.inc
+--source include/weight_string_l1.inc
+
+set collation_connection=ucs2_bin;
+--source include/weight_string.inc
+--source include/weight_string_euro.inc
+--source include/weight_string_l1.inc
+
+--echo #
+--echo # Bug #36418 Character sets: crash if char(256 using utf32)
+--echo #
+select hex(char(0x01 using ucs2));
+select hex(char(0x0102 using ucs2));
+select hex(char(0x010203 using ucs2));
+select hex(char(0x01020304 using ucs2));
+
+--echo #
+--echo # Bug#10094 Displays wrong error message for UNIQUE key index on CHAR(255) Unicode datatype
+--echo #
+CREATE TABLE t1 (f1 CHAR(255) unicode);
+INSERT INTO t1 values ('abc'),('bcd'),('abc');
+--error ER_DUP_ENTRY
+ALTER TABLE t1 ADD UNIQUE Index_1 (f1);
+DROP TABLE t1;
+
+--echo #
+--echo # Test how character set works with date/time
+--echo #
+SET collation_connection=ucs2_general_ci;
+--source include/ctype_datetime.inc
+SET NAMES latin1;
+
+--echo #
+--echo # WL#4013 Unicode german2 collation
+--echo #
+SET collation_connection=ucs2_german2_ci;
+--source include/ctype_german.inc
+
+--echo #
+--echo # Bug#59145 valgrind warnings for uninitialized values in my_strtoll10_mb2
+--echo #
+SET NAMES latin1;
+SELECT CONVERT(CHAR(NULL USING ucs2), UNSIGNED);
+DO IFNULL(CHAR(NULL USING ucs2), '');
+DO CAST(CONVERT('' USING ucs2) AS UNSIGNED);
+
+--echo #
+--echo # Test error message for conversion using different charset
+--echo # 
+
+CREATE TABLE t1 (a DECIMAL(2,0));
+
+SET sql_mode='strict_all_tables';
+--error ER_WARN_DATA_OUT_OF_RANGE
+INSERT INTO t1 VALUES (CONVERT('9e99999999' USING ucs2));
+SET sql_mode=DEFAULT;
+
+INSERT IGNORE INTO t1 VALUES (CONVERT('aaa' USING ucs2)); 
+
+DROP TABLE t1;
+
+--echo #
+--echo # End of 5.6 tests
+--echo #
+
+
+--echo #
+--echo # Start of 10.0 tests
+--echo #
+
+SET NAMES latin1, collation_connection=ucs2_bin;
+--source include/ctype_like_cond_propagation.inc
+SET NAMES latin1, collation_connection=ucs2_general_ci;
+--source include/ctype_like_cond_propagation.inc
+SET NAMES latin1;
+
+--echo #
+--echo # MDEV-6661 PI() does not work well in UCS2/UTF16/UTF32 context
+--echo #
+SELECT CONCAT(CONVERT('pi=' USING ucs2),PI()) AS PI;
+
+--echo #
+--echo # MDEV-6695 Bad column name for UCS2 string literals
+--echo #
+SET NAMES utf8, character_set_connection=ucs2;
+SELECT 'a','aa';
+
+--echo #
+--echo # MDEV-10306 Wrong results with combination of CONCAT, SUBSTR and CONVERT in subquery
+--echo #
+
+SET NAMES utf8, character_set_connection=ucs2;
+SET @save_optimizer_switch=@@optimizer_switch;
+SET optimizer_switch=_utf8'derived_merge=on';
+CREATE TABLE t1 (t VARCHAR(10) CHARSET latin1);
+INSERT INTO t1 VALUES('abcdefghi');
+SET NAMES utf8, character_set_connection=ucs2;
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT HEX(t) t2 FROM t1) sub;
+SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT TO_BASE64(t) t2 FROM t1) sub;
+DROP TABLE t1;
+SET optimizer_switch=@save_optimizer_switch;
+
+
+--echo #
+--echo # End of 10.0 tests
+--echo #
+
+#
+# CAST (... BINARY)
+#
+select collation(cast("a" as char(10) unicode binary));
+select collation(cast("a" as char(10) binary unicode));
+
+--echo #
+--echo # MDEV-8222 "string_field LIKE int_const" returns a wrong result in case of UCS2
+--echo #
+CREATE TABLE t1 (a VARCHAR(10) CHARSET ucs2);
+INSERT INTO t1 VALUES ('1');
+SELECT * FROM t1 WHERE a LIKE 1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # MDEV-8253 EXPLAIN SELECT prints unexpected characters
+--echo #
+SET NAMES latin1, character_set_connection=ucs2;
+CREATE TABLE t1 (a DECIMAL(10,1),b DECIMAL(10,1),c VARCHAR(10),d VARCHAR(10));
+INSERT INTO t1 VALUES (1.5,1.5,'1','1'),(3.5,3.5,'3','3');
+EXPLAIN EXTENDED
+SELECT * FROM t1 WHERE COALESCE(c,0)='3 ' AND COALESCE(d,0)=COALESCE(c,0);
+DROP TABLE t1;
+
+--echo #
+--echo # MDEV-9178 Wrong result for CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED)
+--echo #
+SET NAMES utf8;
+SELECT CAST(CONVERT('1IJ3' USING ucs2) 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=ucs2;
+let $coll='ucs2_general_nopad_ci';
+let $coll_pad='ucs2_general_ci';
+--source include/ctype_pad_all_engines.inc
+
+let $coll='ucs2_nopad_bin';
+let $coll_pad='ucs2_bin';
+--source include/ctype_pad_all_engines.inc
+
+--echo #
+--echo # MDEV-10585 EXECUTE IMMEDIATE statement
+--echo #
+SET character_set_connection=ucs2;
+EXECUTE IMMEDIATE 'SELECT COLLATION("a")';
+
+SET @stmt='SELECT COLLATION("a")';
+EXECUTE IMMEDIATE @stmt;
+
+--echo #
+--echo # MDEV-10866 Extend PREPARE and EXECUTE IMMEDIATE to understand expressions
+--echo #
+SET NAMES utf8, collation_connection=ucs2_bin;
+SET @stmt='SELECT COLLATION(''a'')';
+EXECUTE IMMEDIATE @stmt;
+
+SET NAMES utf8, character_set_connection=ucs2;
+SET @stmt='SELECT COLLATION(''a'')';
+EXECUTE IMMEDIATE @stmt;
+
+EXECUTE IMMEDIATE CONCAT('SELECT ''a'' FROM DUAL');
+
+SELECT HEX('aä') FROM DUAL;
+EXECUTE IMMEDIATE 'SELECT HEX(''aä'') FROM DUAL';
+EXECUTE IMMEDIATE CONCAT('SELECT HEX(''aä'') FROM DUAL');
+EXECUTE IMMEDIATE CONCAT('SELECT HEX(''aä'') FROM ', 'DUAL');
+PREPARE stmt FROM 'SELECT HEX(''aä'') FROM DUAL';
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+SET @table='DUAL';
+SELECT HEX(@table);
+EXECUTE IMMEDIATE CONCAT('SELECT HEX(''aä'') FROM ', @table);
+EXECUTE IMMEDIATE CONCAT('SELECT HEX(''aä'') FROM ', CONVERT(@table USING utf8));
+SET @stmt='SELECT HEX(''aä'') FROM DUAL';
+EXECUTE IMMEDIATE @stmt;
+PREPARE stmt FROM @stmt;
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #