-- source include/have_ucs2.inc

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

SET NAMES latin1;
SET character_set_connection=ucs2;
-- source include/endspace.inc

SET CHARACTER SET koi8r;

#
# 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;

#
# 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;

create table t1 (a varchar(10) character set ucs2, key(a));
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
explain select * from t1 where a like 'abc%';
explain select * from t1 where a like concat('abc','%');
select * from t1 where a like "abc%";
select * from t1 where a like concat("abc","%");
select * from t1 where a like "ABC%";
select * from t1 where a like "test%";
select * from t1 where a like "te_t";
select * from t1 where a like "%a%";
select * from t1 where a like "%abcd%";
select * from t1 where a like "%abc\d%";
drop table t1;

#
# More LIKE test: bug#2619
#
select 'AA' like 'AA'; 
select 'AA' like 'A%A';
select 'AA' like 'A%%A';
select 'AA' like 'AA%';
select 'AA' like '%AA%';
select 'AA' like '%A';
select 'AA' like '%AA';
select 'AA' like 'A%A%';
select 'AA' like '_%_%';
select 'AA' like '%A%A';
select 'AAA'like 'A%A%A';

select 'AZ' like 'AZ'; 
select 'AZ' like 'A%Z';
select 'AZ' like 'A%%Z';
select 'AZ' like 'AZ%';
select 'AZ' like '%AZ%';
select 'AZ' like '%Z';
select 'AZ' like '%AZ';
select 'AZ' like 'A%Z%';
select 'AZ' like '_%_%';
select 'AZ' like '%A%Z';
select 'AZ' like 'A_';
select 'AZ' like '_Z';
select 'AMZ'like 'A%M%Z';

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
SET NAMES latin1;
SET collation_connection='ucs2_bin';
-- source include/ctype_filesort.inc
-- source include/ctype_like_escape.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 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
#
--disable_warnings
create table t1(f1 varchar(5) CHARACTER SET ucs2 COLLATE ucs2_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 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;

--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;

--echo End of 5.0 tests