1--source include/have_myisam.inc 2--source include/no_valgrind_without_big.inc 3# Some bugs have MyISAM specific tests 4 5-- source include/have_ucs2.inc 6 7--disable_warnings 8DROP TABLE IF EXISTS t1; 9--enable_warnings 10 11SET @test_character_set= 'ucs2'; 12SET @test_collation= 'ucs2_general_ci'; 13-- source include/ctype_common.inc 14 15SET NAMES latin1; 16SET character_set_connection=ucs2; 17-- source include/endspace.inc 18 19SET CHARACTER SET koi8r; 20 21# 22# BUG#49028, error in LIKE with ucs2 23# 24create table t1 (a varchar(2) character set ucs2 collate ucs2_bin, key(a)); 25insert into t1 values ('A'),('A'),('B'),('C'),('D'),('A\t'); 26insert into t1 values ('A\0'),('A\0'),('A\0'),('A\0'),('AZ'); 27select hex(a) from t1 where a like 'A_' order by a; 28select hex(a) from t1 ignore key(a) where a like 'A_' order by a; 29drop table t1; 30 31# 32# Check that 0x20 is only trimmed when it is 33# a part of real SPACE character, not just a part 34# of a multibyte sequence. 35# Note, CYRILLIC LETTER ER is used as an example, which 36# is stored as 0x0420 in UCS2, thus contains 0x20 in the 37# low byte. The second character is THREE-PER-M, U+2004, 38# which contains 0x20 in the high byte. 39# 40 41CREATE TABLE t1 (word VARCHAR(64) CHARACTER SET ucs2, word2 CHAR(64) CHARACTER SET ucs2); 42INSERT INTO t1 VALUES (_koi8r'�',_koi8r'�'), (X'2004',X'2004'); 43SELECT hex(word) FROM t1 ORDER BY word; 44SELECT hex(word2) FROM t1 ORDER BY word2; 45DELETE FROM t1; 46 47# 48# Check that real spaces are correctly trimmed. 49# 50 51INSERT INTO t1 VALUES (X'042000200020',X'042000200020'), (X'200400200020', X'200400200020'); 52SELECT hex(word) FROM t1 ORDER BY word; 53SELECT hex(word2) FROM t1 ORDER BY word2; 54DROP TABLE t1; 55 56# 57# Check LPAD/RPAD 58# 59SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'0421'); 60SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'04210422'); 61SELECT LPAD(_ucs2 X'0420',10,_ucs2 X'042104220423'); 62SELECT LPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423'); 63 64SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'0421'); 65SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'04210422'); 66SELECT RPAD(_ucs2 X'0420',10,_ucs2 X'042104220423'); 67SELECT RPAD(_ucs2 X'0420042104220423042404250426042704280429042A042B',10,_ucs2 X'042104220423'); 68 69CREATE TABLE t1 SELECT 70LPAD(_ucs2 X'0420',10,_ucs2 X'0421') l, 71RPAD(_ucs2 X'0420',10,_ucs2 X'0421') r; 72SHOW CREATE TABLE t1; 73DROP TABLE t1; 74 75--echo # 76--echo # Bug #51876 : crash/memory underrun when loading data with ucs2 77--echo # and reverse() function 78--echo # 79 80# In 5.7 default 'sql_mode' now also includes 'STRICT_TRANS_TABLES' which will prevent 81# "LOAD DATA INFILE" from loading invalid data. For the following test we need to 82# disable it. 83SET @sql_mode_save = @@sql_mode; 84SET sql_mode = _latin1 'ONLY_FULL_GROUP_BY,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION'; 85 86# Because of the Oralce Bug #79989, there is no guarantee that after 87# including "ctype_common.inc" current value of the @@character_set_database 88# session variable will be 'latin1' and string literals without prefixes in the 89# following "SELECT" statement ('00' and '01') will have this charset. 90# As a workaround, we add "_latin1" prefix here. 91 92--echo # Problem # 1 (original report): wrong parsing of ucs2 data 93SELECT _latin1 '00' UNION SELECT _latin1 '10' INTO OUTFILE 'tmpp.txt'; 94CREATE TABLE t1(a INT); 95LOAD DATA INFILE 'tmpp.txt' INTO TABLE t1 CHARACTER SET ucs2 96(@b) SET a=REVERSE(@b); 97--echo # should return 2 zeroes (as the value is truncated) 98SELECT * FROM t1; 99 100DROP TABLE t1; 101let $MYSQLD_DATADIR= `select @@datadir`; 102remove_file $MYSQLD_DATADIR/test/tmpp.txt; 103 104 105--echo # Problem # 2 : if you write and read ucs2 data to a file they're lost 106SELECT _latin1 '00' UNION SELECT _latin1 '10' INTO OUTFILE 'tmpp2.txt' CHARACTER SET ucs2; 107CREATE TABLE t1(a INT); 108LOAD DATA INFILE 'tmpp2.txt' INTO TABLE t1 CHARACTER SET ucs2 109(@b) SET a=REVERSE(@b); 110--echo # should return 0 and 1 (10 reversed) 111SELECT * FROM t1; 112 113DROP TABLE t1; 114let $MYSQLD_DATADIR= `select @@datadir`; 115remove_file $MYSQLD_DATADIR/test/tmpp2.txt; 116 117# restoring 'sql_mode' 118SET sql_mode = @sql_mode_save; 119 120 121# 122# BUG3946 123# 124 125create table t2(f1 Char(30)); 126insert into t2 values ("103000"), ("22720000"), ("3401200"), ("78000"); 127select lpad(f1, 12, "-o-/") from t2; 128drop table t2; 129 130###################################################### 131# 132# Test of like 133# 134 135SET NAMES koi8r; 136SET character_set_connection=ucs2; 137--source include/ctype_like.inc 138 139CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET ucs2); 140INSERT INTO t1 VALUES ('����'),('����'),('����'),('����'),('����'),('����'); 141INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 142INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 143INSERT INTO t1 VALUES ('����������'),('����������'),('����������'),('����������'); 144SELECT * FROM t1 WHERE a LIKE '%����%'; 145SELECT * FROM t1 WHERE a LIKE '%���%'; 146SELECT * FROM t1 WHERE a LIKE '����%'; 147SELECT * FROM t1 WHERE a LIKE '����%' COLLATE ucs2_bin; 148DROP TABLE t1; 149 150# 151# Bug 1181 152# 153CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word)) 154ENGINE=MyISAM CHARACTER SET ucs2 COLLATE ucs2_general_ci; 155INSERT INTO t1 (word) VALUES ("cat"); 156SELECT * FROM t1 WHERE word LIKE "c%"; 157SELECT * FROM t1 WHERE word LIKE "ca_"; 158SELECT * FROM t1 WHERE word LIKE "cat"; 159SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630025'; 160SELECT * FROM t1 WHERE word LIKE _ucs2 x'00630061005F'; 161DROP TABLE t1; 162 163# 164# Check that INSERT works fine. 165# This invokes charpos() function. 166select insert(_ucs2 0x006100620063,10,2,_ucs2 0x006400650066); 167select insert(_ucs2 0x006100620063,1,2,_ucs2 0x006400650066); 168 169###################################################### 170 171# 172# Bug 1264 173# 174# Description: 175# 176# When using a ucs2 table in MySQL, 177# either with ucs2_general_ci or ucs2_bin collation, 178# words are returned in an incorrect order when using ORDER BY 179# on an _indexed_ CHAR or VARCHAR column. They are sorted with 180# the longest word *first* instead of last. I.E. The word "aardvark" 181# is in the results before the word "a". 182# 183# If there is no index for the column, the problem does not occur. 184# 185# Interestingly, if there is no second column, the words are returned 186# in the correct order. 187# 188# According to EXPLAIN, it looks like when the output includes columns that 189# are not part of the index sorted on, it does a filesort, which fails. 190# Using a straight index yields correct results. 191 192SET NAMES latin1; 193 194# 195# Two fields, index 196# 197 198CREATE TABLE t1 ( 199 word VARCHAR(64), 200 bar INT(11) default 0, 201 PRIMARY KEY (word)) 202 ENGINE=MyISAM 203 CHARSET ucs2 204 COLLATE ucs2_general_ci ; 205 206INSERT INTO t1 (word) VALUES ("aar"); 207INSERT INTO t1 (word) VALUES ("a"); 208INSERT INTO t1 (word) VALUES ("aardvar"); 209INSERT INTO t1 (word) VALUES ("aardvark"); 210INSERT INTO t1 (word) VALUES ("aardvara"); 211INSERT INTO t1 (word) VALUES ("aardvarz"); 212EXPLAIN SELECT * FROM t1 ORDER BY word; 213SELECT * FROM t1 ORDER BY word; 214EXPLAIN SELECT word FROM t1 ORDER BY word; 215SELECT word FROM t1 ORDER by word; 216DROP TABLE t1; 217 218# 219# One field, index 220# 221 222CREATE TABLE t1 ( 223 word VARCHAR(64) , 224 PRIMARY KEY (word)) 225 ENGINE=MyISAM 226 CHARSET ucs2 227 COLLATE ucs2_general_ci; 228 229INSERT INTO t1 (word) VALUES ("aar"); 230INSERT INTO t1 (word) VALUES ("a"); 231INSERT INTO t1 (word) VALUES ("aardvar"); 232INSERT INTO t1 (word) VALUES ("aardvark"); 233INSERT INTO t1 (word) VALUES ("aardvara"); 234INSERT INTO t1 (word) VALUES ("aardvarz"); 235EXPLAIN SELECT * FROM t1 ORDER BY WORD; 236SELECT * FROM t1 ORDER BY word; 237DROP TABLE t1; 238 239 240# 241# Two fields, no index 242# 243 244CREATE TABLE t1 ( 245 word TEXT, 246 bar INT(11) AUTO_INCREMENT, 247 PRIMARY KEY (bar)) 248 ENGINE=MyISAM 249 CHARSET ucs2 250 COLLATE ucs2_general_ci ; 251INSERT INTO t1 (word) VALUES ("aar"); 252INSERT INTO t1 (word) VALUES ("a" ); 253INSERT INTO t1 (word) VALUES ("aardvar"); 254INSERT INTO t1 (word) VALUES ("aardvark"); 255INSERT INTO t1 (word) VALUES ("aardvara"); 256INSERT INTO t1 (word) VALUES ("aardvarz"); 257EXPLAIN SELECT * FROM t1 ORDER BY word; 258SELECT * FROM t1 ORDER BY word; 259EXPLAIN SELECT word FROM t1 ORDER BY word; 260SELECT word FROM t1 ORDER BY word; 261DROP TABLE t1; 262 263# 264# END OF Bug 1264 test 265# 266######################################################## 267 268 269# Bug #2390 270# Check alignment for constants 271# 272SELECT HEX(_ucs2 0x0); 273SELECT HEX(_ucs2 0x01); 274SELECT HEX(_ucs2 0x012); 275SELECT HEX(_ucs2 0x0123); 276SELECT HEX(_ucs2 0x01234); 277SELECT HEX(_ucs2 0x012345); 278SELECT HEX(_ucs2 0x0123456); 279SELECT HEX(_ucs2 0x01234567); 280SELECT HEX(_ucs2 0x012345678); 281SELECT HEX(_ucs2 0x0123456789); 282SELECT HEX(_ucs2 0x0123456789A); 283SELECT HEX(_ucs2 0x0123456789AB); 284SELECT HEX(_ucs2 0x0123456789ABC); 285SELECT HEX(_ucs2 0x0123456789ABCD); 286SELECT HEX(_ucs2 0x0123456789ABCDE); 287SELECT HEX(_ucs2 0x0123456789ABCDEF); 288 289# 290# Check alignment for from-binary-conversion with CAST and CONVERT 291# 292SELECT hex(cast(0xAA as char character set ucs2)); 293SELECT hex(convert(0xAA using ucs2)); 294 295# 296# Check alignment for string types 297# 298CREATE TABLE t1 (a char(10) character set ucs2); 299INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); 300SELECT HEX(a) FROM t1; 301DROP TABLE t1; 302 303CREATE TABLE t1 (a varchar(10) character set ucs2); 304INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); 305SELECT HEX(a) FROM t1; 306DROP TABLE t1; 307 308CREATE TABLE t1 (a text character set ucs2); 309INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); 310SELECT HEX(a) FROM t1; 311DROP TABLE t1; 312 313CREATE TABLE t1 (a mediumtext character set ucs2); 314INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); 315SELECT HEX(a) FROM t1; 316DROP TABLE t1; 317 318CREATE TABLE t1 (a longtext character set ucs2); 319INSERT INTO t1 VALUES (0xA),(0xAA),(0xAAA),(0xAAAA),(0xAAAAA); 320SELECT HEX(a) FROM t1; 321DROP TABLE t1; 322 323# the same should be also done with enum and set 324 325 326# 327# Bug #5024 Server crashes with queries on fields 328# with certain charset/collation settings 329# 330 331create table t1 (s1 char character set `ucs2` collate `ucs2_czech_ci`); 332insert into t1 values ('0'),('1'),('2'),('a'),('b'),('c'); 333select s1 from t1 where s1 > 'a' order by s1; 334drop table t1; 335 336# 337# Bug #5081 : UCS2 fields are filled with '0x2020' 338# after extending field length 339# 340create table t1(a char(1)) default charset = ucs2; 341insert into t1 values ('a'),('b'),('c'); 342alter table t1 modify a char(5); 343select a, hex(a) from t1; 344drop table t1; 345 346# 347# Check prepare statement from an UCS2 string 348# 349set @ivar= 1234; 350set @str1 = 'select ?'; 351set @str2 = convert(@str1 using ucs2); 352prepare stmt1 from @str2; 353execute stmt1 using @ivar; 354 355# 356# Check that ucs2 works with ENUM and SET type 357# 358set names latin1; 359create table t1 (a enum('x','y','z') character set ucs2); 360show create table t1; 361insert into t1 values ('x'); 362insert into t1 values ('y'); 363insert into t1 values ('z'); 364select a, hex(a) from t1 order by a; 365alter table t1 change a a enum('x','y','z','d','e','�','�','�') character set ucs2; 366show create table t1; 367insert into t1 values ('D'); 368insert into t1 values ('E '); 369insert into t1 values ('�'); 370insert into t1 values ('�'); 371insert into t1 values ('�'); 372select a, hex(a) from t1 order by a; 373drop table t1; 374 375create table t1 (a set ('x','y','z','�','�','�') character set ucs2); 376show create table t1; 377insert into t1 values ('x'); 378insert into t1 values ('y'); 379insert into t1 values ('z'); 380insert into t1 values ('x,y'); 381insert into t1 values ('x,y,z,�,�,�'); 382select a, hex(a) from t1 order by a; 383drop table t1; 384 385# 386# Bug#7302 UCS2 data in ENUM fields get truncated when new column is added 387# 388create table t1(a enum('a','b','c')) default character set ucs2; 389insert into t1 values('a'),('b'),('c'); 390alter table t1 add b char(1); 391show warnings; 392select * from t1 order by a; 393drop table t1; 394 395SET collation_connection='ucs2_general_ci'; 396-- source include/ctype_filesort.inc 397-- source include/ctype_like_escape.inc 398-- source include/ctype_german.inc 399-- source include/ctype_like_range_f1f2.inc 400SET NAMES latin1; 401SET collation_connection='ucs2_bin'; 402-- source include/ctype_filesort.inc 403-- source include/ctype_like_escape.inc 404-- source include/ctype_like_range_f1f2.inc 405 406# 407# Bug#10344 Some string functions fail for UCS2 408# 409select hex(substr(_ucs2 0x00e400e50068,1)); 410select hex(substr(_ucs2 0x00e400e50068,2)); 411select hex(substr(_ucs2 0x00e400e50068,3)); 412select hex(substr(_ucs2 0x00e400e50068,-1)); 413select hex(substr(_ucs2 0x00e400e50068,-2)); 414select hex(substr(_ucs2 0x00e400e50068,-3)); 415 416SET NAMES latin1; 417# 418# Bug#8235 419# 420# This bug also helped to find another problem that 421# INSERT of a UCS2 string containing a negative number 422# into a unsigned int column didn't produce warnings. 423# This test covers both problems. 424# 425SET collation_connection='ucs2_swedish_ci'; 426CREATE TABLE t1 (Field1 int(10) default '0'); 427# no warnings, negative numbers are allowed 428INSERT INTO t1 VALUES ('-1'); 429SELECT * FROM t1; 430DROP TABLE t1; 431CREATE TABLE t1 (Field1 int(10) unsigned default '0'); 432# this should generate a "Data truncated" warning 433INSERT IGNORE INTO t1 VALUES ('-1'); 434DROP TABLE t1; 435SET NAMES latin1; 436 437# 438# Bug#18691 Converting number to UNICODE string returns invalid result 439# 440SELECT CONVERT(103, CHAR(50) UNICODE); 441SELECT CONVERT(103.0, CHAR(50) UNICODE); 442SELECT CONVERT(-103, CHAR(50) UNICODE); 443SELECT CONVERT(-103.0, CHAR(50) UNICODE); 444 445# 446# Bug#9557 MyISAM utf8 table crash 447# 448CREATE TABLE t1 ( 449 a varchar(255) NOT NULL default '', 450 KEY a (a) 451) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE ucs2_general_ci; 452insert into t1 values (0x803d); 453insert into t1 values (0x005b); 454select hex(a) from t1; 455drop table t1; 456 457# 458# Bug #14583 Bug on query using a LIKE on indexed field with ucs2_bin collation 459# 460--disable_warnings 461create table t1(f1 varchar(5) CHARACTER SET ucs2 COLLATE ucs2_bin NOT NULL) engine=InnoDB; 462--enable_warnings 463insert into t1 values('a'); 464create index t1f1 on t1(f1); 465select f1 from t1 where f1 like 'a%'; 466drop table t1; 467 468# 469# Bug#9442 Set parameter make query fail if column character set is UCS2 470# 471create table t1 (utext varchar(20) character set ucs2); 472insert into t1 values ("lily"); 473insert into t1 values ("river"); 474prepare stmt from 'select utext from t1 where utext like ?'; 475set @param1='%%'; 476execute stmt using @param1; 477execute stmt using @param1; 478select utext from t1 where utext like '%%'; 479drop table t1; 480deallocate prepare stmt; 481 482# 483# Bug#22052 Trailing spaces are not removed from UNICODE fields in an index 484# 485create table t1 ( 486 a char(10) unicode not null, 487 index a (a) 488) engine=myisam; 489insert into t1 values (repeat(0x201f, 10)); 490insert into t1 values (repeat(0x2020, 10)); 491insert into t1 values (repeat(0x2021, 10)); 492# make sure "index read" is used 493explain select hex(a) from t1 order by a; 494select hex(a) from t1 order by a; 495alter table t1 drop index a; 496select hex(a) from t1 order by a; 497drop table t1; 498 499# 500# Bug #20076: server crashes for a query with GROUP BY if MIN/MAX aggregation 501# over a 'ucs2' field uses a temporary table 502# 503 504CREATE TABLE t1 (id int, s char(5) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci); 505INSERT INTO t1 VALUES (1, 'ZZZZZ'), (1, 'ZZZ'), (2, 'ZZZ'), (2, 'ZZZZZ'); 506 507SELECT id, MIN(s) FROM t1 GROUP BY id; 508 509DROP TABLE t1; 510 511# 512# Bug #20536: md5() with GROUP BY and UCS2 return different results on myisam/innodb 513# 514 515--disable_warnings 516drop table if exists bug20536; 517--enable_warnings 518 519set names latin1; 520create table bug20536 (id bigint not null auto_increment primary key, name 521varchar(255) character set ucs2 not null); 522insert into `bug20536` (`id`,`name`) values (1, _latin1 x'7465737431'), (2, "'test\\_2'"); 523select md5(name) from bug20536; 524select sha1(name) from bug20536; 525select make_set(3, name, upper(name)) from bug20536; 526select export_set(5, name, upper(name)) from bug20536; 527select export_set(5, name, upper(name), ",", 5) from bug20536; 528 529# 530# Bug #20108: corrupted default enum value for a ucs2 field 531# 532 533CREATE TABLE t1 ( 534 status enum('active','passive') collate latin1_general_ci 535 NOT NULL default 'passive' 536); 537SHOW CREATE TABLE t1; 538ALTER TABLE t1 ADD a int NOT NULL AFTER status; 539 540CREATE TABLE t2 ( 541 status enum('active','passive') collate ucs2_turkish_ci 542 NOT NULL default 'passive' 543); 544SHOW CREATE TABLE t2; 545ALTER TABLE t2 ADD a int NOT NULL AFTER status; 546 547DROP TABLE t1,t2; 548 549 550# Some broken functions: add these tests just to document current behavior. 551 552# PASSWORD and OLD_PASSWORD don't work with UCS2 strings, but to fix it would 553# not be backwards compatible in all cases, so it's best to leave it alone 554# 2011-11-08 update: change in behavior caused by refactoring of 555# Item_func_password() but since the below test doesn't show correct behavior 556# anyway it is removed. 557# select password(name) from bug20536; 558# select old_password(name) from bug20536; 559 560# Disable test case as encrypt relies on 'crypt' function. 561# "decrypt" is noramlly tested in func_crypt.test which have a 562# "have_crypt.inc" test 563--disable_parsing 564# ENCRYPT relies on OS function crypt() which takes a NUL-terminated string; it 565# doesn't return good results for strings with embedded 0 bytes. It won't be 566# fixed unless we choose to re-implement the crypt() function ourselves to take 567# an extra size_t string_length argument. 568select encrypt(name, 'SALT') from bug20536; 569--enable_parsing 570 571# QUOTE doesn't work with UCS2 data. It would require a total rewrite 572# of Item_func_quote::val_str(), which isn't worthwhile until UCS2 is 573# supported fully as a client character set. 574select quote(name) from bug20536; 575 576drop table bug20536; 577 578# 579# Bug #31615: crash after set names ucs2 collate xxx 580# 581--error 1231 582set names ucs2; 583--error 1231 584set names ucs2 collate ucs2_bin; 585--error 1231 586set character_set_client= ucs2; 587--error 1231 588set character_set_client= concat('ucs', substr('2', 1)); 589 590# 591# BUG#31159 - fulltext search on ucs2 column crashes server 592# 593CREATE TABLE t1(a TEXT CHARSET ucs2 COLLATE ucs2_unicode_ci) engine=MyISAM; 594INSERT INTO t1 VALUES('abcd'); 595SELECT * FROM t1 WHERE MATCH(a) AGAINST ('+abcd' IN BOOLEAN MODE); 596DROP TABLE t1; 597 598--echo End of 4.1 tests 599 600# 601# Conversion from an UCS2 string to a decimal column 602# 603CREATE TABLE t1 (a varchar(64) character set ucs2, b decimal(10,3)); 604INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0); 605update t1 set b=a; 606SELECT * FROM t1; 607DROP TABLE t1; 608 609# 610# Bug#9442 Set parameter make query fail if column character set is UCS2 611# 612create table t1 (utext varchar(20) character set ucs2); 613insert into t1 values ("lily"); 614insert into t1 values ("river"); 615prepare stmt from 'select utext from t1 where utext like ?'; 616set @param1='%%'; 617execute stmt using @param1; 618execute stmt using @param1; 619select utext from t1 where utext like '%%'; 620drop table t1; 621deallocate prepare stmt; 622 623# 624# Bug#22638 SOUNDEX broken for international characters 625# 626set names latin1; 627set character_set_connection=ucs2; 628select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); 629select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb')); 630select 'mood' sounds like 'mud'; 631# Cyrillic A, BE, VE 632select hex(soundex(_ucs2 0x041004110412)); 633# Make sure that "U+00BF INVERTED QUESTION MARK" is not considered as letter 634select hex(soundex(_ucs2 0x00BF00C0)); 635set names latin1; 636 637# 638# Bug #14290: character_maximum_length for text fields 639# 640create table t1(a blob, b text charset utf8, c text charset ucs2); 641select data_type, character_octet_length, character_maximum_length 642 from information_schema.columns where table_name='t1'; 643drop table t1; 644 645# 646# Bug#28925 GROUP_CONCAT inserts wrong separators for a ucs2 column 647# 648create table t1 (a char(1) character set ucs2); 649insert into t1 values ('a'),('b'),('c'); 650select hex(group_concat(a)) from t1; 651select collation(group_concat(a)) from t1; 652drop table t1; 653 654set names latin1; 655create table t1 (a char(1) character set latin1); 656insert into t1 values ('a'),('b'),('c'); 657set character_set_connection=ucs2; 658select hex(group_concat(a separator ',')) from t1; 659select collation(group_concat(a separator ',')) from t1; 660drop table t1; 661set names latin1; 662 663# 664# Bug#29499 Converting 'del' from ascii to Unicode results in 'question mark' 665# 666create table t1 (s1 char(1) character set ascii, s2 char(1) character set ucs2); 667insert into t1 (s1) values (0x7f); 668update t1 set s2 = s1; 669select hex(s2) from t1; 670select hex(convert(s1 using latin1)) from t1; 671drop table t1; 672 673# 674# Conversion from UCS2 to ASCII is possible 675# if the UCS2 string consists of only ASCII characters 676# 677create table t1 (a varchar(15) character set ascii not null, b int); 678insert into t1 values ('a',1); 679select concat(a,if(b<10,_ucs2 0x0061,_ucs2 0x0062)) from t1; 680select concat(a,if(b>10,_ucs2 0x0061,_ucs2 0x0062)) from t1; 681select * from t1 where a=if(b<10,_ucs2 0x0061,_ucs2 0x0062); 682select * from t1 where a=if(b>10,_ucs2 0x0061,_ucs2 0x0062); 683 684# 685# Conversion from UCS2 to ASCII is not possible if 686# the UCS2 string has non-ASCII characters 687# 688--error 1267 689select concat(a,if(b<10,_ucs2 0x00C0,_ucs2 0x0062)) from t1; 690--error 1267 691select concat(a,if(b>10,_ucs2 0x00C0,_ucs2 0x0062)) from t1; 692--error 1267 693select concat(a,if(b<10,_ucs2 0x0062,_ucs2 0x00C0)) from t1; 694--error 1267 695select concat(a,if(b>10,_ucs2 0x0062,_ucs2 0x00C0)) from t1; 696--error 1267 697select * from t1 where a=if(b<10,_ucs2 0x00C0,_ucs2 0x0062); 698--error 1267 699select * from t1 where a=if(b<10,_ucs2 0x0062,_ucs2 0x00C0); 700drop table t1; 701 702# 703# Bug#35720 ucs2 + pad_char_to_full_length = failure 704# 705CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET UCS2); 706INSERT INTO t1 VALUES ('a'); 707SET @@sql_mode=pad_char_to_full_length; 708SELECT HEX(s1) FROM t1; 709SET @@sql_mode=default; 710SELECT HEX(s1) FROM t1; 711DROP TABLE t1; 712 713set collation_connection=ucs2_general_ci; 714--source include/ctype_regex.inc 715set names latin1; 716# 717# Bug#30981 CHAR(0x41 USING ucs2) doesn't add leading zero 718# 719select hex(char(0x41 using ucs2)); 720 721# 722# Bug#37575: UCASE fails on monthname 723# 724SET character_set_connection=ucs2; 725SELECT CHARSET(DAYNAME(19700101)); 726SELECT CHARSET(MONTHNAME(19700101)); 727SELECT LOWER(DAYNAME(19700101)); 728SELECT LOWER(MONTHNAME(19700101)); 729SELECT UPPER(DAYNAME(19700101)); 730SELECT UPPER(MONTHNAME(19700101)); 731SELECT HEX(MONTHNAME(19700101)); 732SELECT HEX(DAYNAME(19700101)); 733SET LC_TIME_NAMES=ru_RU; 734SET NAMES utf8; 735SET character_set_connection=ucs2; 736SELECT CHARSET(DAYNAME(19700101)); 737SELECT CHARSET(MONTHNAME(19700101)); 738SELECT LOWER(DAYNAME(19700101)); 739SELECT LOWER(MONTHNAME(19700101)); 740SELECT UPPER(DAYNAME(19700101)); 741SELECT UPPER(MONTHNAME(19700101)); 742SELECT HEX(MONTHNAME(19700101)); 743SELECT HEX(DAYNAME(19700101)); 744SET character_set_connection=latin1; 745 746--echo # 747--echo # Bug#52120 create view cause Assertion failed: 0, file .\item_subselect.cc, line 817 748--echo # 749CREATE TABLE t1 (a CHAR(1) CHARSET ascii, b CHAR(1) CHARSET latin1); 750CREATE VIEW v1 AS SELECT 1 from t1 751WHERE t1.b <=> (SELECT a FROM t1 WHERE a < SOME(SELECT '1')); 752DROP VIEW v1; 753DROP TABLE t1; 754 755--echo # 756--echo # Bug#59648 my_strtoll10_mb2: Assertion `(*endptr - s) % 2 == 0' failed. 757--echo # 758SELECT HEX(CHAR(COALESCE(NULL, CHAR(COUNT('%s') USING ucs2), 1, @@global.license, NULL) USING cp850)); 759SELECT CONVERT(QUOTE(CHAR(0xf5 using ucs2)), SIGNED); 760 761--echo End of 5.0 tests 762 763 764--echo # 765--echo # Start of 5.5 tests 766--echo # 767 768SET NAMES latin1; 769SET collation_connection=ucs2_general_ci; 770--source include/ctype_numconv.inc 771SET NAMES latin1; 772 773--echo # 774--echo # Bug #13832953 MY_STRNXFRM_UNICODE: ASSERTION `SRC' FAILED 775--echo # 776SET sql_mode = 'NO_ENGINE_SUBSTITUTION'; 777CREATE TABLE t1 (c1 SET('','') CHARACTER SET ucs2); 778INSERT INTO t1 VALUES (''); 779SELECT COALESCE(c1) FROM t1 ORDER BY 1; 780DROP TABLE t1; 781SET sql_mode = default; 782 783--echo # 784--echo # End of 5.5 tests 785--echo # 786 787 788--echo # 789--echo # Start of 5.6 tests 790--echo # 791 792--echo # 793--echo # WL#3664 WEIGHT_STRING 794--echo # 795 796set collation_connection=ucs2_general_ci; 797--source include/weight_string.inc 798--source include/weight_string_euro.inc 799--source include/weight_string_l1.inc 800 801set collation_connection=ucs2_bin; 802--source include/weight_string.inc 803--source include/weight_string_euro.inc 804--source include/weight_string_l1.inc 805 806--echo # 807--echo # Bug #36418 Character sets: crash if char(256 using utf32) 808--echo # 809select hex(char(0x01 using ucs2)); 810select hex(char(0x0102 using ucs2)); 811select hex(char(0x010203 using ucs2)); 812select hex(char(0x01020304 using ucs2)); 813 814--echo # 815--echo # Bug#10094 Displays wrong error message for UNIQUE key index on CHAR(255) Unicode datatype 816--echo # 817CREATE TABLE t1 (f1 CHAR(255) unicode); 818INSERT INTO t1 values ('abc'),('bcd'),('abc'); 819--error ER_DUP_ENTRY 820ALTER TABLE t1 ADD UNIQUE Index_1 (f1); 821DROP TABLE t1; 822 823--echo # 824--echo # Test how character set works with date/time 825--echo # 826SET collation_connection=ucs2_general_ci; 827--source include/ctype_datetime.inc 828SET NAMES latin1; 829 830--echo # 831--echo # WL#4013 Unicode german2 collation 832--echo # 833SET collation_connection=ucs2_german2_ci; 834--source include/ctype_german.inc 835 836--echo # 837--echo # Bug#59145 valgrind warnings for uninitialized values in my_strtoll10_mb2 838--echo # 839SET NAMES latin1; 840SELECT CONVERT(CHAR(NULL USING ucs2), UNSIGNED); 841DO IFNULL(CHAR(NULL USING ucs2), ''); 842DO CAST(CONVERT('' USING ucs2) AS UNSIGNED); 843 844--echo # 845--echo # Test error message for conversion using different charset 846--echo # 847 848CREATE TABLE t1 (a DECIMAL(2,0)); 849 850SET sql_mode=default; 851--error ER_TRUNCATED_WRONG_VALUE_FOR_FIELD 852INSERT INTO t1 VALUES (CONVERT('9e99999999' USING ucs2)); 853 854INSERT IGNORE INTO t1 VALUES (CONVERT('aaa' USING ucs2)); 855 856DROP TABLE t1; 857 858--echo # 859--echo # End of 5.6 tests 860--echo # 861