1-- source include/have_utf32.inc 2-- source include/have_utf8mb4.inc 3 4SET TIME_ZONE = '+03:00'; 5 6--disable_warnings 7DROP TABLE IF EXISTS t1; 8--enable_warnings 9 10--echo # 11--echo # Start of 5.5 tests 12--echo # 13 14SET NAMES latin1; 15SET character_set_connection=utf32; 16select hex('a'), hex('a '); 17-- source include/endspace.inc 18 19# 20# Check that incomplete utf32 characters in HEX notation 21# are left-padded with zeros 22# 23select hex(_utf32 0x44); 24select hex(_utf32 0x3344); 25select hex(_utf32 0x103344); 26 27select hex(_utf32 X'44'); 28select hex(_utf32 X'3344'); 29select hex(_utf32 X'103344'); 30 31 32# 33# Check that 0x20 is only trimmed when it is 34# a part of real SPACE character, not just a part 35# of a multibyte sequence. 36# Note, CYRILLIC LETTER ER is used as an example, which 37# is stored as 0x0420 in UCS2, thus contains 0x20 in the 38# low byte. The second character is THREE-PER-M, U+2004, 39# which contains 0x20 in the high byte. 40# 41 42CREATE TABLE t1 (word VARCHAR(64), word2 CHAR(64)) CHARACTER SET utf32; 43INSERT INTO t1 VALUES (_koi8r 0xF2, _koi8r 0xF2), (X'2004',X'2004'); 44SELECT hex(word) FROM t1 ORDER BY word; 45SELECT hex(word2) FROM t1 ORDER BY word2; 46DELETE FROM t1; 47 48# 49# Check that real spaces are correctly trimmed. 50# 51 52INSERT INTO t1 VALUES 53 (X'000004200000002000000020',X'000004200000002000000020'), 54 (X'000020040000002000000020',X'000020040000002000000020'); 55SELECT hex(word) FROM t1 ORDER BY word; 56SELECT hex(word2) FROM t1 ORDER BY word2; 57DROP TABLE t1; 58 59# 60# Check LPAD/RPAD 61# 62SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'0421')); 63SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'0000042100000422')); 64SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423')); 65SELECT hex(LPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423')); 66 67SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'0421')); 68SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'0000042100000422')); 69SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423')); 70SELECT hex(RPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423')); 71 72CREATE TABLE t1 SELECT 73LPAD(_utf32 X'0420',10,_utf32 X'0421') l, 74RPAD(_utf32 X'0420',10,_utf32 X'0421') r; 75SHOW CREATE TABLE t1; 76select hex(l), hex(r) from t1; 77DROP TABLE t1; 78 79create table t1 (f1 char(30)); 80insert into t1 values ("103000"), ("22720000"), ("3401200"), ("78000"); 81select lpad(f1, 12, "-o-/") from t1; 82drop table t1; 83 84###################################################### 85# 86# Test of like 87# 88 89SET NAMES latin1; 90SET character_set_connection=utf32; 91--source include/ctype_like.inc 92 93SET NAMES utf8; 94SET character_set_connection=utf32; 95CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32); 96INSERT INTO t1 VALUES ('фыва'),('Фыва'),('фЫва'),('фыВа'),('фывА'),('ФЫВА'); 97INSERT INTO t1 VALUES ('фывапролдж'),('Фывапролдж'),('фЫвапролдж'),('фыВапролдж'); 98INSERT INTO t1 VALUES ('фывАпролдж'),('фываПролдж'),('фывапРолдж'),('фывапрОлдж'); 99INSERT INTO t1 VALUES ('фывапроЛдж'),('фывапролДж'),('фывапролдЖ'),('ФЫВАПРОЛДЖ'); 100SELECT * FROM t1 WHERE a LIKE '%фЫва%' ORDER BY BINARY a; 101SELECT * FROM t1 WHERE a LIKE '%фЫв%' ORDER BY BINARY a; 102SELECT * FROM t1 WHERE a LIKE 'фЫва%' ORDER BY BINARY a; 103SELECT * FROM t1 WHERE a LIKE 'фЫва%' COLLATE utf32_bin ORDER BY BINARY a; 104DROP TABLE t1; 105 106CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word)) 107ENGINE=MyISAM CHARACTER SET utf32; 108INSERT INTO t1 (word) VALUES ("cat"); 109SELECT * FROM t1 WHERE word LIKE "c%"; 110SELECT * FROM t1 WHERE word LIKE "ca_"; 111SELECT * FROM t1 WHERE word LIKE "cat"; 112SELECT * FROM t1 WHERE word LIKE _utf32 x'0000006300000025'; # "c%" 113SELECT * FROM t1 WHERE word LIKE _utf32 x'00000063000000610000005F'; # "ca_" 114DROP TABLE t1; 115 116 117# 118# Check that INSERT() works fine. 119# This invokes charpos() function. 120select insert(_utf32 0x000000610000006200000063,10,2,_utf32 0x000000640000006500000066); 121select insert(_utf32 0x000000610000006200000063,1,2,_utf32 0x000000640000006500000066); 122 123####################################################### 124 125# 126# Bug 1264 127# 128# Description: 129# 130# When using a ucs2 table in MySQL, 131# either with ucs2_general_ci or ucs2_bin collation, 132# words are returned in an incorrect order when using ORDER BY 133# on an _indexed_ CHAR or VARCHAR column. They are sorted with 134# the longest word *first* instead of last. I.E. The word "aardvark" 135# is in the results before the word "a". 136# 137# If there is no index for the column, the problem does not occur. 138# 139# Interestingly, if there is no second column, the words are returned 140# in the correct order. 141# 142# According to EXPLAIN, it looks like when the output includes columns that 143# are not part of the index sorted on, it does a filesort, which fails. 144# Using a straight index yields correct results. 145 146SET NAMES latin1; 147 148# 149# Two fields, index 150# 151 152CREATE TABLE t1 ( 153 word VARCHAR(64), 154 bar INT(11) default 0, 155 PRIMARY KEY (word)) 156 ENGINE=MyISAM 157 CHARSET utf32 158 COLLATE utf32_general_ci ; 159 160INSERT INTO t1 (word) VALUES ("aar"); 161INSERT INTO t1 (word) VALUES ("a"); 162INSERT INTO t1 (word) VALUES ("aardvar"); 163INSERT INTO t1 (word) VALUES ("aardvark"); 164INSERT INTO t1 (word) VALUES ("aardvara"); 165INSERT INTO t1 (word) VALUES ("aardvarz"); 166EXPLAIN SELECT * FROM t1 ORDER BY word; 167SELECT * FROM t1 ORDER BY word; 168EXPLAIN SELECT word FROM t1 ORDER BY word; 169SELECT word FROM t1 ORDER by word; 170DROP TABLE t1; 171 172 173# 174# One field, index 175# 176 177CREATE TABLE t1 ( 178 word VARCHAR(64) , 179 PRIMARY KEY (word)) 180 ENGINE=MyISAM 181 CHARSET utf32 182 COLLATE utf32_general_ci; 183 184INSERT INTO t1 (word) VALUES ("aar"); 185INSERT INTO t1 (word) VALUES ("a"); 186INSERT INTO t1 (word) VALUES ("aardvar"); 187INSERT INTO t1 (word) VALUES ("aardvark"); 188INSERT INTO t1 (word) VALUES ("aardvara"); 189INSERT INTO t1 (word) VALUES ("aardvarz"); 190EXPLAIN SELECT * FROM t1 ORDER BY WORD; 191SELECT * FROM t1 ORDER BY word; 192DROP TABLE t1; 193 194 195# 196# Two fields, no index 197# 198 199CREATE TABLE t1 ( 200 word TEXT, 201 bar INT(11) AUTO_INCREMENT, 202 PRIMARY KEY (bar)) 203 ENGINE=MyISAM 204 CHARSET utf32 205 COLLATE utf32_general_ci ; 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# END OF Bug 1264 test 220# 221######################################################## 222 223 224# 225# Check alignment for from-binary-conversion with CAST and CONVERT 226# 227SELECT hex(cast(0xAA as char character set utf32)); 228SELECT hex(convert(0xAA using utf32)); 229 230# 231# Check alignment for string types 232# 233CREATE TABLE t1 (a char(10) character set utf32); 234INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); 235SELECT HEX(a) FROM t1; 236DROP TABLE t1; 237 238CREATE TABLE t1 (a varchar(10) character set utf32); 239INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); 240SELECT HEX(a) FROM t1; 241DROP TABLE t1; 242 243CREATE TABLE t1 (a text character set utf32); 244INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); 245SELECT HEX(a) FROM t1; 246DROP TABLE t1; 247 248CREATE TABLE t1 (a mediumtext character set utf32); 249INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); 250SELECT HEX(a) FROM t1; 251DROP TABLE t1; 252 253CREATE TABLE t1 (a longtext character set utf32); 254INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); 255SELECT HEX(a) FROM t1; 256DROP TABLE t1; 257 258## 259## Bug #5024 Server crashes with queries on fields 260## with certain charset/collation settings 261## 262# 263#create table t1 (s1 char character set `ucs2` collate `ucs2_czech_ci`); 264#insert into t1 values ('0'),('1'),('2'),('a'),('b'),('c'); 265#select s1 from t1 where s1 > 'a' order by s1; 266#drop table t1; 267 268# 269# Bug #5081 : UCS2 fields are filled with '0x2020' 270# after extending field length 271# 272create table t1(a char(1)) default charset utf32; 273insert into t1 values ('a'),('b'),('c'); 274alter table t1 modify a char(5); 275select a, hex(a) from t1; 276drop table t1; 277 278# 279# Check prepare statement from an UTF32 string 280# 281set @ivar= 1234; 282set @str1 = 'select ?'; 283set @str2 = convert(@str1 using utf32); 284prepare stmt1 from @str2; 285execute stmt1 using @ivar; 286 287# 288# Check that utf32 works with ENUM and SET type 289# 290set names utf8; 291create table t1 (a enum('x','y','z') character set utf32); 292show create table t1; 293insert into t1 values ('x'); 294insert into t1 values ('y'); 295insert into t1 values ('z'); 296select a, hex(a) from t1 order by a; 297alter table t1 change a a enum('x','y','z','d','e','ä','ö','ü') character set utf32; 298show create table t1; 299insert into t1 values ('D'); 300insert into t1 values ('E '); 301insert into t1 values ('ä'); 302insert into t1 values ('ö'); 303insert into t1 values ('ü'); 304select a, hex(a) from t1 order by a; 305drop table t1; 306 307create table t1 (a set ('x','y','z','ä','ö','ü') character set utf32); 308show create table t1; 309insert into t1 values ('x'); 310insert into t1 values ('y'); 311insert into t1 values ('z'); 312insert into t1 values ('x,y'); 313insert into t1 values ('x,y,z,ä,ö,ü'); 314select a, hex(a) from t1 order by a; 315drop table t1; 316 317# 318# Bug#7302 UCS2 data in ENUM fields get truncated when new column is added 319# 320create table t1(a enum('a','b','c')) default character set utf32; 321insert into t1 values('a'),('b'),('c'); 322alter table t1 add b char(1); 323show warnings; 324select * from t1 order by a; 325drop table t1; 326 327SET NAMES latin1; 328SET collation_connection='utf32_general_ci'; 329-- source include/ctype_filesort.inc 330-- source include/ctype_like_escape.inc 331SET NAMES latin1; 332SET collation_connection='utf32_bin'; 333-- source include/ctype_filesort.inc 334-- source include/ctype_filesort2.inc 335-- source include/ctype_like_escape.inc 336 337# 338# Bug#10344 Some string functions fail for UCS2 339# 340select hex(substr(_utf32 0x000000e4000000e500000068,1)); 341select hex(substr(_utf32 0x000000e4000000e500000068,2)); 342select hex(substr(_utf32 0x000000e4000000e500000068,3)); 343select hex(substr(_utf32 0x000000e4000000e500000068,-1)); 344select hex(substr(_utf32 0x000000e4000000e500000068,-2)); 345select hex(substr(_utf32 0x000000e4000000e500000068,-3)); 346 347#SET NAMES latin1; 348# 349# Bug#8235 350# 351# This bug also helped to find another problem that 352# INSERT of a UCS2 string containing a negative number 353# into a unsigned int column didn't produce warnings. 354# This test covers both problems. 355# 356#SET collation_connection='ucs2_swedish_ci'; 357#CREATE TABLE t1 (Field1 int(10) default '0'); 358## no warnings, negative numbers are allowed 359#INSERT INTO t1 VALUES ('-1'); 360#SELECT * FROM t1; 361#DROP TABLE t1; 362#CREATE TABLE t1 (Field1 int(10) unsigned default '0'); 363## this should generate a "Data truncated" warning 364#INSERT INTO t1 VALUES ('-1'); 365#DROP TABLE t1; 366#SET NAMES latin1; 367 368# 369## 370## Bug#18691 Converting number to UNICODE string returns invalid result 371## 372#SELECT CONVERT(103, CHAR(50) UNICODE); 373#SELECT CONVERT(103.0, CHAR(50) UNICODE); 374#SELECT CONVERT(-103, CHAR(50) UNICODE); 375#SELECT CONVERT(-103.0, CHAR(50) UNICODE); 376 377# 378# Bug#9557 MyISAM utf8 table crash 379# 380CREATE TABLE t1 ( 381 a varchar(250) NOT NULL default '', 382 KEY a (a) 383) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE utf32_general_ci; 384insert into t1 values (0x803d); 385insert into t1 values (0x005b); 386select hex(a) from t1; 387drop table t1; 388 389## 390## Bug #14583 Bug on query using a LIKE on indexed field with ucs2_bin collation 391## 392#--disable_warnings 393#create table t1(f1 varchar(5) CHARACTER SET utf32 COLLATE utf32_bin NOT NULL) engine=InnoDB; 394#--enable_warnings 395#insert into t1 values('a'); 396#create index t1f1 on t1(f1); 397#select f1 from t1 where f1 like 'a%'; 398#drop table t1; 399 400# 401# Bug#9442 Set parameter make query fail if column character set is UCS2 402# 403create table t1 (utext varchar(20) character set utf32); 404insert into t1 values ("lily"); 405insert into t1 values ("river"); 406prepare stmt from 'select utext from t1 where utext like ?'; 407set @param1='%%'; 408execute stmt using @param1; 409execute stmt using @param1; 410select utext from t1 where utext like '%%'; 411drop table t1; 412deallocate prepare stmt; 413 414# 415# Bug#22052 Trailing spaces are not removed from UNICODE fields in an index 416# 417create table t1 ( 418 a char(10) character set utf32 not null, 419 index a (a) 420) engine=myisam; 421insert into t1 values (repeat(0x0000201f, 10)); 422insert into t1 values (repeat(0x00002020, 10)); 423insert into t1 values (repeat(0x00002021, 10)); 424# make sure "index read" is used 425explain select hex(a) from t1 order by a; 426select hex(a) from t1 order by a; 427alter table t1 drop index a; 428select hex(a) from t1 order by a; 429drop table t1; 430 431# 432# Bug #20076: server crashes for a query with GROUP BY if MIN/MAX aggregation 433# over a 'ucs2' field uses a temporary table 434# 435#CREATE TABLE t1 (id int, s char(5) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci); 436#INSERT INTO t1 VALUES (1, 'ZZZZZ'), (1, 'ZZZ'), (2, 'ZZZ'), (2, 'ZZZZZ'); 437#SELECT id, MIN(s) FROM t1 GROUP BY id; 438#DROP TABLE t1; 439 440## 441## Bug #20536: md5() with GROUP BY and UCS2 return different results on myisam/innodb 442## 443# 444#--disable_warnings 445#drop table if exists bug20536; 446#--enable_warnings 447# 448#set names latin1; 449#create table bug20536 (id bigint not null auto_increment primary key, name 450#varchar(255) character set ucs2 not null); 451#insert into `bug20536` (`id`,`name`) values (1, _latin1 x'7465737431'), (2, "'test\\_2'"); 452#select md5(name) from bug20536; 453#select sha1(name) from bug20536; 454#select make_set(3, name, upper(name)) from bug20536; 455#select export_set(5, name, upper(name)) from bug20536; 456#select export_set(5, name, upper(name), ",", 5) from bug20536; 457 458# 459# Bug #20108: corrupted default enum value for a ucs2 field 460# 461 462CREATE TABLE t1 ( 463 status enum('active','passive') character set utf32 collate utf32_general_ci 464 NOT NULL default 'passive' 465); 466SHOW CREATE TABLE t1; 467ALTER TABLE t1 ADD a int NOT NULL AFTER status; 468SHOW CREATE TABLE t1; 469DROP TABLE t1; 470 471#CREATE TABLE t2 ( 472# status enum('active','passive') collate ucs2_turkish_ci 473# NOT NULL default 'passive' 474#); 475#SHOW CREATE TABLE t2; 476#ALTER TABLE t2 ADD a int NOT NULL AFTER status; 477#DROP TABLE t2; 478 479 480## Some broken functions: add these tests just to document current behavior. 481# 482## PASSWORD and OLD_PASSWORD don't work with UCS2 strings, but to fix it would 483## not be backwards compatible in all cases, so it's best to leave it alone 484#select password(name) from bug20536; 485#select old_password(name) from bug20536; 486# 487## Disable test case as encrypt relies on 'crypt' function. 488## "decrypt" is noramlly tested in func_crypt.test which have a 489## "have_crypt.inc" test 490#--disable_parsing 491## ENCRYPT relies on OS function crypt() which takes a NUL-terminated string; it 492## doesn't return good results for strings with embedded 0 bytes. It won't be 493## fixed unless we choose to re-implement the crypt() function ourselves to take 494## an extra size_t string_length argument. 495#select encrypt(name, 'SALT') from bug20536; 496#--enable_parsing 497# 498## QUOTE doesn't work with UCS2 data. It would require a total rewrite 499## of Item_func_quote::val_str(), which isn't worthwhile until UCS2 is 500## supported fully as a client character set. 501#select quote(name) from bug20536; 502# 503#drop table bug20536; 504# 505--echo End of 4.1 tests 506 507 508# 509# Conversion from an UTF32 string to a decimal column 510# 511CREATE TABLE t1 (a varchar(64) character set utf32, b decimal(10,3)); 512INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0); 513update t1 set b=a; 514SELECT *, hex(a) FROM t1; 515DROP TABLE t1; 516 517# 518# Bug#9442 Set parameter make query fail if column character set is UCS2 519# 520create table t1 (utext varchar(20) character set utf32); 521insert into t1 values ("lily"); 522insert into t1 values ("river"); 523prepare stmt from 'select utext from t1 where utext like ?'; 524set @param1='%%'; 525execute stmt using @param1; 526execute stmt using @param1; 527select utext from t1 where utext like '%%'; 528drop table t1; 529deallocate prepare stmt; 530 531# 532# Bug#22638 SOUNDEX broken for international characters 533# 534set names latin1; 535set character_set_connection=utf32; 536select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); 537select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb')); 538select 'mood' sounds like 'mud'; 539# Cyrillic A, BE, VE 540select hex(soundex(_utf32 0x000004100000041100000412)); 541# Make sure that "U+00BF INVERTED QUESTION MARK" is not considered as letter 542select hex(soundex(_utf32 0x000000BF000000C0)); 543set names latin1; 544 545# 546# Bug #14290: character_maximum_length for text fields 547# 548create table t1(a blob, b text charset utf32); 549select data_type, character_octet_length, character_maximum_length 550 from information_schema.columns where table_name='t1'; 551drop table t1; 552 553 554set names latin1; 555set collation_connection=utf32_general_ci; 556# 557# Testing instr() 558# 559select position('bb' in 'abba'); 560 561# 562# Testing hash_sort() 563# 564create table t1 (a varchar(10) character set utf32) engine=heap; 565insert into t1 values ('a'),('A'),('b'),('B'); 566select * from t1 where a='a' order by binary a; 567select hex(min(binary a)),count(*) from t1 group by a; 568drop table t1; 569 570# 571# Testing numchars() 572# 573select char_length('abcd'), octet_length('abcd'); 574 575# 576# Testing charpos() 577# 578select left('abcd',2); 579 580# 581# Testing well_formed_length() 582# 583create table t1 (a varchar(10) character set utf32); 584insert into t1 values (_utf32 0x0010FFFF); 585--error ER_INVALID_CHARACTER_STRING 586insert into t1 values (_utf32 0x00110000); 587--error ER_INVALID_CHARACTER_STRING 588insert into t1 values (_utf32 0x00110101); 589--error ER_INVALID_CHARACTER_STRING 590insert into t1 values (_utf32 0x01000101); 591--error ER_INVALID_CHARACTER_STRING 592insert into t1 values (_utf32 0x11000101); 593select hex(a) from t1; 594drop table t1; 595 596# 597# Bug#32914 Character sets: illegal characters in utf8 and utf32 columns 598# 599create table t1 (utf32 varchar(2) character set utf32); 600--echo Wrong character with pad 601insert ignore into t1 values (0x110000); 602--echo Wrong chsaracter without pad 603insert ignore into t1 values (0x00110000); 604--echo Wrong character with pad followed by another wrong character 605insert ignore into t1 values (0x11000000110000); 606--echo Good character with pad followed by bad character 607insert ignore into t1 values (0x10000000110000); 608--echo Good character without pad followed by bad character 609insert ignore into t1 values (0x0010000000110000); 610--echo Wrong character with the second byte higher than 0x10 611insert ignore into t1 values (0x00800037); 612--echo Wrong character with pad with the second byte higher than 0x10 613insert ignore into t1 values (0x00800037); 614drop table t1; 615 616# 617# Bug#32394 Character sets: crash if comparison with 0xfffd 618# 619select _utf32'a' collate utf32_general_ci = 0xfffd; 620select hex(concat(_utf32 0x0410 collate utf32_general_ci, 0x61)); 621create table t1 (s1 varchar(5) character set utf32); 622insert into t1 values (0xfffd); 623select case when s1 = 0xfffd then 1 else 0 end from t1; 624select hex(s1) from t1 where s1 = 0xfffd; 625drop table t1; 626 627# 628# Testing lengthsp() 629# 630create table t1 (a char(10)) character set utf32; 631insert into t1 values ('a '); 632select hex(a) from t1; 633drop table t1; 634 635# 636# Testing caseup() and casedn() 637# 638select upper('abcd'), lower('ABCD'); 639 640# 641# TODO: str_to_datetime() is broken and doesn't work with ucs2 and utf32 642# Testing snprintf() 643# 644#create table t1 (a date); 645#insert into t1 values ('2007-09-16'); 646#select * from t1; 647#drop table t1; 648 649# 650# Testing l10tostr 651# !!! Not used in the code 652 653# 654# Testing ll10tostr 655# 656create table t1 (a varchar(10) character set utf32); 657insert into t1 values (123456); 658select a, hex(a) from t1; 659drop table t1; 660 661# 662# Testing fill 663# SOUNDEX fills strings with DIGIT ZERO up to four characters 664select hex(soundex('a')); 665 666# 667# Testing strntol 668# !!! Not used in the code 669 670# 671# Testing strntoul 672# 673create table t1 (a enum ('a','b','c')) character set utf32; 674insert into t1 values ('1'); 675select * from t1; 676drop table t1; 677 678# 679# Testing strntoll and strntoull 680# 681set names latin1; 682select hex(conv(convert('123' using utf32), -10, 16)); 683select hex(conv(convert('123' using utf32), 10, 16)); 684 685# 686# Testing strntod 687# 688set names latin1; 689set character_set_connection=utf32; 690select 1.1 + '1.2'; 691select 1.1 + '1.2xxx'; 692 693# Testing strntoll10_utf32 694# Testing strtoll10 695select left('aaa','1'); 696--source include/ctype_strtoll10.inc 697 698# 699# Testing strntoull10rnd 700# 701create table t1 (a int); 702insert into t1 values ('-1234.1e2'); 703insert ignore into t1 values ('-1234.1e2xxxx'); 704insert into t1 values ('-1234.1e2 '); 705select * from t1; 706drop table t1; 707 708# 709# Testing scan 710# 711create table t1 (a int); 712insert into t1 values ('1 '); 713insert ignore into t1 values ('1 x'); 714select * from t1; 715drop table t1; 716 717# 718# Testing auto-conversion to TEXT 719# 720SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 721create table t1 (a varchar(17000) character set utf32); 722show create table t1; 723drop table t1; 724 725# 726# Testing that maximim possible key length is 1332 bytes 727# 728create table t1 (a varchar(250) character set utf32 primary key); 729show create table t1; 730drop table t1; 731--error ER_TOO_LONG_KEY 732create table t1 (a varchar(334) character set utf32 primary key); 733 734# 735# Testing mi_check with long key values 736# 737create table t1 (a varchar(333) character set utf32, key(a)); 738insert into t1 values (repeat('a',333)), (repeat('b',333)); 739flush tables; 740check table t1; 741drop table t1; 742 743# 744# Test how character set works with date/time 745# 746SET collation_connection=utf32_general_ci; 747--source include/ctype_datetime.inc 748SET NAMES latin1; 749 750# 751# Test basic regex functionality 752# 753set collation_connection=utf32_general_ci; 754--source include/ctype_regex.inc 755set names latin1; 756 757 758# TODO: add tests for all engines 759 760# 761# Bug #36418 Character sets: crash if char(256 using utf32) 762# 763select hex(char(0x01 using utf32)); 764select hex(char(0x0102 using utf32)); 765select hex(char(0x010203 using utf32)); 766select hex(char(0x01020304 using utf32)); 767create table t1 (s1 varchar(1) character set utf32, s2 text character set utf32); 768create index i on t1 (s1); 769insert into t1 values (char(256 using utf32), char(256 using utf32)); 770select hex(s1), hex(s2) from t1; 771drop table t1; 772 773 774# 775# Bug#33073 Character sets: ordering fails with utf32 776# 777SET collation_connection=utf32_general_ci; 778CREATE TABLE t1 AS SELECT repeat('a',2) as s1 LIMIT 0; 779SHOW CREATE TABLE t1; 780INSERT INTO t1 VALUES ('ab'),('AE'),('ab'),('AE'); 781SELECT * FROM t1 ORDER BY s1; 782SET max_sort_length=8; 783SELECT * FROM t1 ORDER BY s1; 784DROP TABLE t1; 785SET max_sort_length=DEFAULT; 786SET NAMES latin1; 787 788--echo # 789--echo # Bug#52520 Difference in tinytext utf column metadata 790--echo # 791CREATE TABLE t1 ( 792 s1 TINYTEXT CHARACTER SET utf32, 793 s2 TEXT CHARACTER SET utf32, 794 s3 MEDIUMTEXT CHARACTER SET utf32, 795 s4 LONGTEXT CHARACTER SET utf32 796); 797--enable_metadata 798SET NAMES utf8mb4, @@character_set_results=NULL; 799SELECT *, HEX(s1) FROM t1; 800SET NAMES latin1; 801SELECT *, HEX(s1) FROM t1; 802SET NAMES utf8mb4; 803SELECT *, HEX(s1) FROM t1; 804--disable_metadata 805CREATE TABLE t2 AS SELECT CONCAT(s1) FROM t1; 806SHOW CREATE TABLE t2; 807DROP TABLE t1, t2; 808 809 810--echo # 811--echo # Bug#45263 utf32_general_ci, bad effects around CREATE TABLE AS SELECT 812--echo # 813SET collation_connection=utf32_general_ci; 814CREATE TABLE t1 AS SELECT HEX(0x00) AS my_col; 815SELECT * FROM t1; 816DROP TABLE t1; 817 818--echo # 819--echo # Bug#32859 Character sets: no warning with non-fitting chariot wheel 820--echo # 821CREATE TABLE t1 (utf32 CHAR(5) CHARACTER SET utf32, latin1 CHAR(5) CHARACTER SET latin1); 822INSERT INTO t1 (utf32) VALUES (0xc581); 823UPDATE IGNORE t1 SET latin1 = utf32; 824DELETE FROM t1; 825INSERT INTO t1 (utf32) VALUES (0x100cc); 826UPDATE IGNORE t1 SET latin1 = utf32; 827DROP TABLE t1; 828 829--echo # 830--echo # Bug#55912 FORMAT with locale set fails for numbers < 1000 831--echo # 832SET collation_connection=utf32_general_ci; 833CREATE TABLE t1 AS SELECT format(123,2,'no_NO'); 834SHOW CREATE TABLE t1; 835SELECT * FROM t1; 836DROP TABLE t1; 837 838--echo # 839--echo # Bug#42511 mysqld: ctype-ucs2.c:2044: my_strnncollsp_utf32: Assertion (tlen % 4) == 0' faied 840--echo # 841CREATE TABLE t1 ( 842 b char(250) CHARACTER SET utf32, 843 key (b) 844) ENGINE=MYISAM; 845INSERT INTO t1 VALUES ('d'),('f'); 846SELECT * FROM t1 WHERE b BETWEEN 'a' AND 'z'; 847DROP TABLE t1; 848 849--echo # 850--echo # Bug#11753363 (Bug#44793) Character sets: case clause, ucs2 or utf32, failure 851--echo # 852SELECT CASE _latin1'a' WHEN _utf32'a' THEN 'A' END; 853SELECT CASE _utf32'a' WHEN _latin1'a' THEN 'A' END; 854CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET utf32); 855INSERT INTO t1 VALUES ('a'); 856SELECT CASE s1 WHEN 'a' THEN 'b' ELSE 'c' END FROM t1; 857DROP TABLE t1; 858 859--echo # 860--echo # Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT 861--echo # 862 863SET NAMES utf8, @@character_set_connection=utf32; 864SET STATEMENT group_concat_max_len=1024 FOR 865SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l 866FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body 867UNION ALL 868SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1 869GROUP BY id 870ORDER BY l DESC; 871 872--echo # 873--echo # incorrect charset for val_str_ascii 874--echo # 875 876SELECT '2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second; 877 878--echo # 879--echo # MDEV-5745 analyze MySQL fix for bug#12368495 880--echo # 881SELECT CHAR_LENGTH(TRIM(LEADING 0x0000000000 FROM _utf32 0x00000061)); 882SELECT CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _utf32 0x00000061)); 883SELECT CHAR_LENGTH(TRIM(LEADING 0x00 FROM _utf32 0x00000061)); 884 885SELECT CHAR_LENGTH(TRIM(TRAILING 0x0000000000 FROM _utf32 0x00000061)); 886SELECT CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _utf32 0x00000061)); 887SELECT CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _utf32 0x00000061)); 888 889SELECT CHAR_LENGTH(TRIM(BOTH 0x0000000000 FROM _utf32 0x00000061)); 890SELECT CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _utf32 0x00000061)); 891SELECT CHAR_LENGTH(TRIM(BOTH 0x61 FROM _utf32 0x00000061)); 892SELECT CHAR_LENGTH(TRIM(BOTH 0x00 FROM _utf32 0x00000061)); 893 894# 895# potential signedness issue 896# 897select hex(lower(cast(0xffff0000 as char character set utf32))) as c; 898 899--echo # 900--echo # MDEV-11685: sql_mode can't be set with non-ascii connection charset 901--echo # 902SET character_set_connection=utf32; 903SET sql_mode='NO_ENGINE_SUBSTITUTION'; 904SELECT @@sql_mode; 905SET sql_mode=DEFAULT; 906SET NAMES utf8; 907 908--echo # 909--echo # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 910--echo # 911 912set sql_mode=''; 913CREATE TABLE t1 (c1 VARCHAR(16383) CHARACTER SET utf32); 914DESCRIBE t1; 915DROP TABLE t1; 916 917CREATE TABLE t1 (c1 VARCHAR(16384) CHARACTER SET utf32); 918DESCRIBE t1; 919DROP TABLE t1; 920set sql_mode=default; 921 922 923--echo # 924--echo # End of 5.5 tests 925--echo # 926 927 928--echo # 929--echo # Start of 5.6 tests 930--echo # 931 932--echo # 933--echo # WL#3664 WEIGHT_STRING 934--echo # 935 936set collation_connection=utf32_general_ci; 937--source include/weight_string.inc 938--source include/weight_string_euro.inc 939select hex(weight_string(_utf32 0x10000)); 940select hex(weight_string(_utf32 0x10001)); 941--source include/weight_string_l1.inc 942 943set collation_connection=utf32_bin; 944--source include/weight_string.inc 945--source include/weight_string_euro.inc 946--source include/weight_string_l1.inc 947 948--echo # 949--echo # End of 5.6 tests 950--echo # 951 952 953--echo # 954--echo # Start of 10.0 tests 955--echo # 956 957--echo # 958--echo # MDEV-6661 PI() does not work well in UCS2/UTF16/UTF32 context 959--echo # 960SELECT CONCAT(CONVERT('pi=' USING utf32),PI()) AS PI; 961 962--echo # 963--echo # MDEV-6666 Malformed result for CONCAT(utf8_column, binary_string) 964--echo # 965 966SET NAMES utf8mb4; 967CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32); 968INSERT INTO t1 VALUES ('a'); 969--error ER_INVALID_CHARACTER_STRING 970SELECT CONCAT(a,0x20FFFF) FROM t1; 971SELECT CONCAT(a,0x010000) FROM t1; 972SELECT CONCAT(a,0x00FF) FROM t1; 973DROP TABLE t1; 974--error ER_INVALID_CHARACTER_STRING 975SELECT CONCAT(_utf32'a' COLLATE utf32_unicode_ci, _binary 0x20FFFF); 976PREPARE stmt FROM "SELECT CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?)"; 977SET @arg00=_binary 0x20FFFF; 978--error ER_INVALID_CHARACTER_STRING 979EXECUTE stmt USING @arg00; 980SET @arg00=_binary 0x010000; 981EXECUTE stmt USING @arg00; 982SET @arg00=_binary 0x00FF; 983EXECUTE stmt USING @arg00; 984DEALLOCATE PREPARE stmt; 985 986 987# 988# MDEV-13118 Wrong results with LOWER and UPPER and subquery 989# 990SET NAMEs utf8, character_set_connection=utf32; 991--source include/ctype_mdev13118.inc 992 993 994--echo # 995--echo # End of 10.0 tests 996--echo # 997 998--echo # 999--echo # Start of 10.1 tests 1000--echo # 1001 1002--echo # 1003--echo # MDEV-8419 utf32: compare broken bytes as "greater than any non-broken character" 1004--echo # 1005# Make sure that all non-BMP characters are compared as equal 1006CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32, KEY(a)); 1007INSERT INTO t1 VALUES (0x10000),(0x10001),(0x10002); 1008SELECT COUNT(DISTINCT a) FROM t1; 1009DROP TABLE t1; 1010SELECT _utf32 0x10001=_utf32 0x10002; 1011 1012--echo # 1013--echo # MDEV-9178 Wrong result for CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED) 1014--echo # 1015SET NAMES utf8; 1016SELECT CAST(CONVERT('1IJ3' USING utf32) AS SIGNED); 1017 1018--echo # 1019--echo # End of 10.1 tests 1020--echo # 1021 1022--echo # 1023--echo # Start of 10.2 tests 1024--echo # 1025 1026--echo # 1027--echo # MDEV-9711 NO PAD Collatons 1028--echo # 1029SET character_set_connection=utf32; 1030let $coll='utf32_general_nopad_ci'; 1031let $coll_pad='utf32_general_ci'; 1032--source include/ctype_pad_all_engines.inc 1033 1034let $coll='utf32_nopad_bin'; 1035let $coll_pad='utf32_bin'; 1036--source include/ctype_pad_all_engines.inc 1037 1038 1039--echo # 1040--echo # MDEV-22111 ERROR 1064 & 1033 and SIGSEGV on CREATE TABLE w/ various charsets on 10.4/5 optimized builds | Assertion `(uint) (table_check_constraints - share->check_constraints) == (uint) (share->table_check_constraints - share->field_check_constraints)' failed 1041--echo # 10.2 tests 1042--echo # 1043 1044SET NAMES utf8, COLLATION_CONNECTION=utf32_bin; 1045CREATE TABLE t1(c1 ENUM('a','b','ac') CHARACTER SET 'Binary',c2 JSON,c3 INT); 1046SHOW CREATE TABLE t1; 1047INSERT INTO t1 (c1) VALUES (1),(2),(3); 1048SELECT HEX(c1) FROM t1 ORDER BY c1; 1049DROP TABLE t1; 1050 1051--echo # 1052--echo # End of 10.2 tests 1053--echo # 1054