1# 2# Tests with the utf8mb4 character set 3# 4--disable_warnings 5drop table if exists t1,t2; 6--enable_warnings 7 8--echo # 9--echo # Start of 5.5 tests 10--echo # 11 12set names utf8mb4; 13 14select left(_utf8mb4 0xD0B0D0B1D0B2,1); 15select right(_utf8mb4 0xD0B0D0B2D0B2,1); 16 17select locate('he','hello'); 18select locate('he','hello',2); 19select locate('lo','hello',2); 20select locate('HE','hello'); 21select locate('HE','hello',2); 22select locate('LO','hello',2); 23select locate('HE','hello' collate utf8mb4_bin); 24select locate('HE','hello' collate utf8mb4_bin,2); 25select locate('LO','hello' collate utf8mb4_bin,2); 26 27select locate(_utf8mb4 0xD0B1, _utf8mb4 0xD0B0D0B1D0B2); 28select locate(_utf8mb4 0xD091, _utf8mb4 0xD0B0D0B1D0B2); 29select locate(_utf8mb4 0xD0B1, _utf8mb4 0xD0B0D091D0B2); 30select locate(_utf8mb4 0xD091, _utf8mb4 0xD0B0D0B1D0B2 collate utf8mb4_bin); 31select locate(_utf8mb4 0xD0B1, _utf8mb4 0xD0B0D091D0B2 collate utf8mb4_bin); 32 33select length(_utf8mb4 0xD0B1), bit_length(_utf8mb4 0xD0B1), char_length(_utf8mb4 0xD0B1); 34 35select 'a' like 'a'; 36select 'A' like 'a'; 37select 'A' like 'a' collate utf8mb4_bin; 38select _utf8mb4 0xD0B0D0B1D0B2 like concat(_utf8mb4'%',_utf8mb4 0xD0B1,_utf8mb4 '%'); 39 40# Bug #6040: can't retrieve records with umlaut 41# characters in case insensitive manner. 42# Case insensitive search LIKE comparison 43# was broken for multibyte characters: 44select convert(_latin1'G�nter Andr�' using utf8mb4) like CONVERT(_latin1'G�NTER%' USING utf8mb4); 45select CONVERT(_koi8r'����' USING utf8mb4) LIKE CONVERT(_koi8r'����' USING utf8mb4); 46select CONVERT(_koi8r'����' USING utf8mb4) LIKE CONVERT(_koi8r'����' USING utf8mb4); 47 48# 49# Check the following: 50# "a" == "a " 51# "a\0" < "a" 52# "a\0" < "a " 53 54SELECT 'a' = 'a '; 55SELECT 'a\0' < 'a'; 56SELECT 'a\0' < 'a '; 57SELECT 'a\t' < 'a'; 58SELECT 'a\t' < 'a '; 59 60# 61# The same for binary collation 62# 63SELECT 'a' = 'a ' collate utf8mb4_bin; 64SELECT 'a\0' < 'a' collate utf8mb4_bin; 65SELECT 'a\0' < 'a ' collate utf8mb4_bin; 66SELECT 'a\t' < 'a' collate utf8mb4_bin; 67SELECT 'a\t' < 'a ' collate utf8mb4_bin; 68 69eval CREATE TABLE t1 (a char(10) character set utf8mb4 not null) ENGINE $engine; 70INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a '); 71--sorted_result 72SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1; 73DROP TABLE t1; 74 75# 76# Fix this, it should return 1: 77# 78#select _utf8mb4 0xD0B0D0B1D0B2 like concat(_utf8mb4'%',_utf8mb4 0xD091,_utf8mb4 '%'); 79# 80 81# 82# Bug 2367: INSERT() behaviour is different for different charsets. 83# 84select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); 85select insert("aa",100,1,"b"),insert("aa",1,3,"b"); 86 87# 88# LELF() didn't work well with utf8mb4 in some cases too. 89# 90select char_length(left(@a:='тест',5)), length(@a), @a; 91 92 93# 94# CREATE ... SELECT 95# 96eval create table t1 ENGINE $engine select date_format("2004-01-19 10:10:10", "%Y-%m-%d"); 97show create table t1; 98select * from t1; 99drop table t1; 100 101# 102# Bug#22646 LC_TIME_NAMES: Assignment to non-UTF8 target fails 103# 104set names utf8mb4; 105set LC_TIME_NAMES='fr_FR'; 106eval create table t1 (s1 char(20) character set latin1) engine $engine; 107insert into t1 values (date_format('2004-02-02','%M')); 108select hex(s1) from t1; 109drop table t1; 110eval create table t1 (s1 char(20) character set koi8r) engine $engine; 111set LC_TIME_NAMES='ru_RU'; 112insert into t1 values (date_format('2004-02-02','%M')); 113insert into t1 values (date_format('2004-02-02','%b')); 114insert into t1 values (date_format('2004-02-02','%W')); 115insert into t1 values (date_format('2004-02-02','%a')); 116--sorted_result 117select hex(s1), s1 from t1; 118drop table t1; 119set LC_TIME_NAMES='en_US'; 120 121 122# 123# Bug #2366 Wrong utf8mb4 behaviour when data is truncated 124# 125set names koi8r; 126eval create table t1 (s1 char(1) character set utf8mb4) engine $engine; 127insert into t1 values (_koi8r'��'); 128select s1,hex(s1),char_length(s1),octet_length(s1) from t1; 129drop table t1; 130 131if (!$is_heap) 132{ 133eval create table t1 (s1 tinytext character set utf8mb4) engine $engine; 134} 135if ($is_heap) 136{ 137eval create table t1 (s1 varchar(255) character set utf8mb4) engine $engine; 138} 139insert into t1 select repeat('a',300); 140insert into t1 select repeat('�',300); 141insert into t1 select repeat('a�',300); 142insert into t1 select repeat('�a',300); 143insert into t1 select repeat('��',300); 144--sorted_result 145select hex(s1) from t1; 146--sorted_result 147select length(s1),char_length(s1) from t1; 148drop table t1; 149 150if (!$is_heap) 151{ 152eval create table t1 (s1 text character set utf8mb4) engine $engine; 153} 154if ($is_heap) 155{ 156eval create table t1 (s1 varchar(255) character set utf8mb4) engine $engine; 157} 158insert into t1 select repeat('a',66000); 159insert into t1 select repeat('�',66000); 160insert into t1 select repeat('a�',66000); 161insert into t1 select repeat('�a',66000); 162insert into t1 select repeat('��',66000); 163--sorted_result 164select length(s1),char_length(s1) from t1; 165drop table t1; 166 167# 168# Bug #2368 Multibyte charsets do not check that incoming data is well-formed 169# 170eval create table t1 (s1 char(10) character set utf8mb4) engine $engine; 171insert into t1 values (0x41FF); 172select hex(s1) from t1; 173drop table t1; 174 175eval create table t1 (s1 varchar(10) character set utf8mb4) engine $engine; 176insert into t1 values (0x41FF); 177select hex(s1) from t1; 178drop table t1; 179 180if (!$is_heap) 181{ 182eval create table t1 (s1 text character set utf8mb4) engine $engine; 183insert into t1 values (0x41FF); 184select hex(s1) from t1; 185drop table t1; 186} 187 188# 189# Bug 2699 190# UTF8 breaks primary keys for cols > 333 characters 191# 192if(!$is_heap) 193{ 194if(!$is_ndb) 195{ 196--error ER_TOO_LONG_KEY 197eval create table t1 (a text character set utf8mb4, primary key(a(371))) engine $engine; 198} 199if($is_ndb) 200{ 201--error ER_BLOB_USED_AS_KEY 202eval create table t1 (a text character set utf8mb4, primary key(a(371))) engine $engine; 203} 204} 205 206# 207# Bug 2959 208# UTF8 charset breaks joins with mixed column/string constant 209# 210eval CREATE TABLE t1 ( a varchar(10) ) CHARACTER SET utf8mb4 ENGINE $engine; 211INSERT INTO t1 VALUES ( 'test' ); 212SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a; 213SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = 'test' and b.a = 'test'; 214SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a and a.a = 'test'; 215DROP TABLE t1; 216 217eval create table t1 (a char(255) character set utf8mb4) engine $engine; 218insert into t1 values('b'),('b'); 219select * from t1 where a = 'b'; 220select * from t1 where a = 'b' and a = 'b'; 221select * from t1 where a = 'b' and a != 'b'; 222drop table t1; 223 224# 225# Testing regexp 226# 227set collation_connection=utf8mb4_general_ci; 228--source include/ctype_regex.inc 229set names utf8mb4; 230 231# 232# Bug #3928 regexp [[:>:]] and UTF-8 233# 234set names utf8mb4; 235 236# This should return TRUE 237select 'вася' rlike '[[:<:]]вася[[:>:]]'; 238select 'вася ' rlike '[[:<:]]вася[[:>:]]'; 239select ' вася' rlike '[[:<:]]вася[[:>:]]'; 240select ' вася ' rlike '[[:<:]]вася[[:>:]]'; 241 242# This should return FALSE 243select 'васяz' rlike '[[:<:]]вася[[:>:]]'; 244select 'zвася' rlike '[[:<:]]вася[[:>:]]'; 245select 'zвасяz' rlike '[[:<:]]вася[[:>:]]'; 246 247# 248# Bug #4555 249# ALTER TABLE crashes mysqld with enum column collated utf8mb4_unicode_ci 250# 251eval CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8mb4_unicode_ci) ENGINE $engine; 252ALTER TABLE t1 ADD COLUMN b CHAR(20); 253DROP TABLE t1; 254 255# Customer Support Center issue # 3299 256# ENUM and SET multibyte fields computed their length wronly 257# when converted into a char field 258set names utf8mb4; 259eval create table t1 (a enum('aaaa','проба') character set utf8mb4) engine $engine; 260show create table t1; 261insert into t1 values ('проба'); 262select * from t1; 263eval create table t2 engine $engine select ifnull(a,a) from t1; 264show create table t2; 265select * from t2; 266drop table t1; 267drop table t2; 268 269# 270# Bug 4521: unique key prefix interacts poorly with utf8mb4 271# MYISAM: keys with prefix compression, case insensitive collation. 272# 273if (!$is_ndb) 274{ 275eval create table t1 (c varchar(30) character set utf8mb4, unique(c(10))) engine $engine; 276insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); 277insert into t1 values ('aaaaaaaaaa'); 278--error ER_DUP_ENTRY 279insert into t1 values ('aaaaaaaaaaa'); 280--error ER_DUP_ENTRY 281insert into t1 values ('aaaaaaaaaaaa'); 282insert into t1 values (repeat('b',20)); 283select c c1 from t1 where c='1'; 284select c c2 from t1 where c='2'; 285select c c3 from t1 where c='3'; 286select c cx from t1 where c='x'; 287select c cy from t1 where c='y'; 288select c cz from t1 where c='z'; 289select c ca10 from t1 where c='aaaaaaaaaa'; 290select c cb20 from t1 where c=repeat('b',20); 291drop table t1; 292 293# 294# Bug 4521: unique key prefix interacts poorly with utf8mb4 295# InnoDB: keys with prefix compression, case insensitive collation. 296# 297--disable_warnings 298eval create table t1 (c varchar(30) character set utf8mb4, unique(c(10))) engine=$engine; 299--enable_warnings 300insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); 301insert into t1 values ('aaaaaaaaaa'); 302--error ER_DUP_ENTRY 303insert into t1 values ('aaaaaaaaaaa'); 304--error ER_DUP_ENTRY 305insert into t1 values ('aaaaaaaaaaaa'); 306insert into t1 values (repeat('b',20)); 307select c c1 from t1 where c='1'; 308select c c2 from t1 where c='2'; 309select c c3 from t1 where c='3'; 310select c cx from t1 where c='x'; 311select c cy from t1 where c='y'; 312select c cz from t1 where c='z'; 313select c ca10 from t1 where c='aaaaaaaaaa'; 314select c cb20 from t1 where c=repeat('b',20); 315drop table t1; 316 317# 318# Bug 4521: unique key prefix interacts poorly with utf8mb4 319# MYISAM: fixed length keys, case insensitive collation 320# 321eval create table t1 (c char(3) character set utf8mb4, unique (c(2))) engine $engine; 322insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); 323insert into t1 values ('a'); 324insert into t1 values ('aa'); 325--error ER_DUP_ENTRY 326insert into t1 values ('aaa'); 327insert into t1 values ('b'); 328insert into t1 values ('bb'); 329--error ER_DUP_ENTRY 330insert into t1 values ('bbb'); 331insert into t1 values ('а'); 332insert into t1 values ('аа'); 333--error ER_DUP_ENTRY 334insert into t1 values ('ааа'); 335insert into t1 values ('б'); 336insert into t1 values ('бб'); 337--error ER_DUP_ENTRY 338insert into t1 values ('ббб'); 339insert into t1 values ('ꪪ'); 340insert into t1 values ('ꪪꪪ'); 341--error ER_DUP_ENTRY 342insert into t1 values ('ꪪꪪꪪ'); 343drop table t1; 344# 345# Bug 4521: unique key prefix interacts poorly with utf8mb4 346# InnoDB: fixed length keys, case insensitive collation 347# 348--disable_warnings 349eval create table t1 (c char(3) character set utf8mb4, unique (c(2))) engine=$engine; 350--enable_warnings 351insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); 352insert into t1 values ('a'); 353insert into t1 values ('aa'); 354--error ER_DUP_ENTRY 355insert into t1 values ('aaa'); 356insert into t1 values ('b'); 357insert into t1 values ('bb'); 358--error ER_DUP_ENTRY 359insert into t1 values ('bbb'); 360insert into t1 values ('а'); 361insert into t1 values ('аа'); 362--error ER_DUP_ENTRY 363insert into t1 values ('ааа'); 364insert into t1 values ('б'); 365insert into t1 values ('бб'); 366--error ER_DUP_ENTRY 367insert into t1 values ('ббб'); 368insert into t1 values ('ꪪ'); 369insert into t1 values ('ꪪꪪ'); 370--error ER_DUP_ENTRY 371insert into t1 values ('ꪪꪪꪪ'); 372drop table t1; 373# 374# Bug 4531: unique key prefix interacts poorly with utf8mb4 375# Check HEAP+HASH, case insensitive collation 376# 377eval create table t1 ( 378c char(10) character set utf8mb4, 379unique key a using hash (c(1)) 380) engine=$engine; 381show create table t1; 382insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 383--error ER_DUP_ENTRY 384insert into t1 values ('aa'); 385--error ER_DUP_ENTRY 386insert into t1 values ('aaa'); 387insert into t1 values ('б'); 388--error ER_DUP_ENTRY 389insert into t1 values ('бб'); 390--error ER_DUP_ENTRY 391insert into t1 values ('ббб'); 392select c as c_all from t1 order by c; 393select c as c_a from t1 where c='a'; 394select c as c_a from t1 where c='б'; 395drop table t1; 396 397# 398# Bug 4531: unique key prefix interacts poorly with utf8mb4 399# Check HEAP+BTREE, case insensitive collation 400# 401eval create table t1 ( 402c char(10) character set utf8mb4, 403unique key a using btree (c(1)) 404) engine=$engine; 405show create table t1; 406insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 407--error ER_DUP_ENTRY 408insert into t1 values ('aa'); 409--error ER_DUP_ENTRY 410insert into t1 values ('aaa'); 411insert into t1 values ('б'); 412--error ER_DUP_ENTRY 413insert into t1 values ('бб'); 414--error ER_DUP_ENTRY 415insert into t1 values ('ббб'); 416select c as c_all from t1 order by c; 417select c as c_a from t1 where c='a'; 418select c as c_a from t1 where c='б'; 419drop table t1; 420 421# 422# Bug 4531: unique key prefix interacts poorly with utf8mb4 423# Check BDB, case insensitive collation 424# 425--disable_warnings 426eval create table t1 ( 427c char(10) character set utf8mb4, 428unique key a (c(1)) 429) engine=$engine; 430--enable_warnings 431insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 432--error ER_DUP_ENTRY 433insert into t1 values ('aa'); 434--error ER_DUP_ENTRY 435insert into t1 values ('aaa'); 436insert into t1 values ('б'); 437--error ER_DUP_ENTRY 438insert into t1 values ('бб'); 439--error ER_DUP_ENTRY 440insert into t1 values ('ббб'); 441select c as c_all from t1 order by c; 442select c as c_a from t1 where c='a'; 443select c as c_a from t1 where c='б'; 444drop table t1; 445 446# 447# Bug 4521: unique key prefix interacts poorly with utf8mb4 448# MYISAM: keys with prefix compression, binary collation. 449# 450eval create table t1 (c varchar(30) character set utf8mb4 collate utf8mb4_bin, unique(c(10))) engine $engine; 451insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); 452insert into t1 values ('aaaaaaaaaa'); 453--error ER_DUP_ENTRY 454insert into t1 values ('aaaaaaaaaaa'); 455--error ER_DUP_ENTRY 456insert into t1 values ('aaaaaaaaaaaa'); 457insert into t1 values (repeat('b',20)); 458select c c1 from t1 where c='1'; 459select c c2 from t1 where c='2'; 460select c c3 from t1 where c='3'; 461select c cx from t1 where c='x'; 462select c cy from t1 where c='y'; 463select c cz from t1 where c='z'; 464select c ca10 from t1 where c='aaaaaaaaaa'; 465select c cb20 from t1 where c=repeat('b',20); 466drop table t1; 467 468# 469# Bug 4521: unique key prefix interacts poorly with utf8mb4 470# MYISAM: fixed length keys, binary collation 471# 472eval create table t1 (c char(3) character set utf8mb4 collate utf8mb4_bin, unique (c(2))) engine $engine; 473insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); 474insert into t1 values ('a'); 475insert into t1 values ('aa'); 476--error ER_DUP_ENTRY 477insert into t1 values ('aaa'); 478insert into t1 values ('b'); 479insert into t1 values ('bb'); 480--error ER_DUP_ENTRY 481insert into t1 values ('bbb'); 482insert into t1 values ('а'); 483insert into t1 values ('аа'); 484--error ER_DUP_ENTRY 485insert into t1 values ('ааа'); 486insert into t1 values ('б'); 487insert into t1 values ('бб'); 488--error ER_DUP_ENTRY 489insert into t1 values ('ббб'); 490insert into t1 values ('ꪪ'); 491insert into t1 values ('ꪪꪪ'); 492--error ER_DUP_ENTRY 493insert into t1 values ('ꪪꪪꪪ'); 494drop table t1; 495 496# 497# Bug 4531: unique key prefix interacts poorly with utf8mb4 498# Check HEAP+HASH, binary collation 499# 500eval create table t1 ( 501c char(10) character set utf8mb4 collate utf8mb4_bin, 502unique key a using hash (c(1)) 503) engine=$engine; 504show create table t1; 505insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 506--error ER_DUP_ENTRY 507insert into t1 values ('aa'); 508--error ER_DUP_ENTRY 509insert into t1 values ('aaa'); 510insert into t1 values ('б'); 511--error ER_DUP_ENTRY 512insert into t1 values ('бб'); 513--error ER_DUP_ENTRY 514insert into t1 values ('ббб'); 515select c as c_all from t1 order by c; 516select c as c_a from t1 where c='a'; 517select c as c_a from t1 where c='б'; 518drop table t1; 519 520# 521# Bug 4531: unique key prefix interacts poorly with utf8mb4 522# Check HEAP+BTREE, binary collation 523# 524eval create table t1 ( 525c char(10) character set utf8mb4 collate utf8mb4_bin, 526unique key a using btree (c(1)) 527) engine=$engine; 528show create table t1; 529insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 530--error ER_DUP_ENTRY 531insert into t1 values ('aa'); 532--error ER_DUP_ENTRY 533insert into t1 values ('aaa'); 534insert into t1 values ('б'); 535--error ER_DUP_ENTRY 536insert into t1 values ('бб'); 537--error ER_DUP_ENTRY 538insert into t1 values ('ббб'); 539select c as c_all from t1 order by c; 540select c as c_a from t1 where c='a'; 541select c as c_a from t1 where c='б'; 542drop table t1; 543 544# 545# Bug 4531: unique key prefix interacts poorly with utf8mb4 546# Check BDB, binary collation 547# 548--disable_warnings 549eval create table t1 ( 550c char(10) character set utf8mb4 collate utf8mb4_bin, 551unique key a (c(1)) 552) engine=$engine; 553--enable_warnings 554insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 555--error ER_DUP_ENTRY 556insert into t1 values ('aa'); 557--error ER_DUP_ENTRY 558insert into t1 values ('aaa'); 559insert into t1 values ('б'); 560--error ER_DUP_ENTRY 561insert into t1 values ('бб'); 562--error ER_DUP_ENTRY 563insert into t1 values ('ббб'); 564select c as c_all from t1 order by c; 565select c as c_a from t1 where c='a'; 566select c as c_a from t1 where c='б'; 567drop table t1; 568} 569 570 571# Bug#4594: column index make = failed for gbk, but like works 572# Check MYISAM 573# 574eval create table t1 ( 575 str varchar(255) character set utf8mb4 not null, 576 key str (str(2)) 577) engine=$engine; 578INSERT INTO t1 VALUES ('str'); 579INSERT INTO t1 VALUES ('str2'); 580select * from t1 where str='str'; 581drop table t1; 582 583# Bug#4594: column index make = failed for gbk, but like works 584# Check InnoDB 585# 586--disable_warnings 587eval create table t1 ( 588 str varchar(255) character set utf8mb4 not null, 589 key str (str(2)) 590) engine=$engine; 591--enable_warnings 592INSERT INTO t1 VALUES ('str'); 593INSERT INTO t1 VALUES ('str2'); 594select * from t1 where str='str'; 595drop table t1; 596 597# the same for HEAP+BTREE 598# 599 600eval create table t1 ( 601 str varchar(255) character set utf8mb4 not null, 602 key str using btree (str(2)) 603) engine=$engine; 604INSERT INTO t1 VALUES ('str'); 605INSERT INTO t1 VALUES ('str2'); 606select * from t1 where str='str'; 607drop table t1; 608 609# the same for HEAP+HASH 610# 611 612if (!$is_ndb) 613{ 614eval create table t1 ( 615 str varchar(255) character set utf8mb4 not null, 616 key str using hash (str(2)) 617) engine=$engine; 618INSERT INTO t1 VALUES ('str'); 619INSERT INTO t1 VALUES ('str2'); 620select * from t1 where str='str'; 621drop table t1; 622 623# the same for BDB 624# 625 626#hh 627--disable_warnings 628eval create table t1 ( 629 str varchar(255) character set utf8mb4 not null, 630 key str (str(2)) 631) engine= $engine; 632--enable_warnings 633INSERT INTO t1 VALUES ('str'); 634INSERT INTO t1 VALUES ('str2'); 635select * from t1 where str='str'; 636drop table t1; 637} 638 639# 640# Bug #5397: Crash with varchar binary and LIKE 641# 642eval CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8mb4 ENGINE $engine; 643INSERT INTO t1 VALUES ('test'); 644SELECT a FROM t1 WHERE a LIKE '%te'; 645DROP TABLE t1; 646 647# 648# Bug #5723: length(<varchar utf8mb4 field>) returns varying results 649# 650--disable_warnings 651SET NAMES utf8mb4; 652--disable_warnings 653eval CREATE TABLE t1 ( 654 subject varchar(255) character set utf8mb4 collate utf8mb4_unicode_ci, 655 p varchar(15) character set utf8mb4 656) ENGINE= $engine DEFAULT CHARSET=latin1; 657--enable_warnings 658INSERT INTO t1 VALUES ('谷川俊二と申しますが、インターネット予約の会員登録をしましたところ、メールアドレスを間違えてしまい会員ＩＤが受け取ることが出来ませんでした。間違えアドレスはtani-shun@n.vodafone.ne.jpを書き込みました。どうすればよいですか？ その他、住所等は間違えありません。連絡ください。よろしくお願いします。m(__)m','040312-000057'); 659INSERT INTO t1 VALUES ('aaa','bbb'); 660--sorted_result 661SELECT length(subject) FROM t1; 662SELECT length(subject) FROM t1 ORDER BY 1; 663DROP TABLE t1; 664 665# 666# Bug #5832 SELECT doesn't return records in some cases 667# 668if (!$is_heap) 669{ 670if (!$is_ndb) 671{ 672eval CREATE TABLE t1 ( 673 id int unsigned NOT NULL auto_increment, 674 list_id smallint unsigned NOT NULL, 675 term TEXT NOT NULL, 676 PRIMARY KEY(id), 677 INDEX(list_id, term(4)) 678) ENGINE=$engine CHARSET=utf8mb4; 679INSERT INTO t1 SET list_id = 1, term = "letterc"; 680INSERT INTO t1 SET list_id = 1, term = "letterb"; 681INSERT INTO t1 SET list_id = 1, term = "lettera"; 682INSERT INTO t1 SET list_id = 1, term = "letterd"; 683SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc"); 684SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb"); 685SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera"); 686SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd"); 687DROP TABLE t1; 688 689# 690# Bug #6043 erratic searching for diacriticals in indexed MyISAM UTF-8 table 691# 692SET NAMES latin1; 693eval CREATE TABLE t1 ( 694 id int unsigned NOT NULL auto_increment, 695 list_id smallint unsigned NOT NULL, 696 term text NOT NULL, 697 PRIMARY KEY(id), 698 INDEX(list_id, term(19)) 699) ENGINE=$engine CHARSET=utf8mb4; 700INSERT INTO t1 set list_id = 1, term = "test�test"; 701INSERT INTO t1 set list_id = 1, term = "testetest"; 702INSERT INTO t1 set list_id = 1, term = "test�test"; 703SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test"); 704SELECT id, term FROM t1 where (list_id = 1) AND (term = "testetest"); 705SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test"); 706DROP TABLE t1; 707} 708} 709 710# 711# Bug #6019 SELECT tries to use too short prefix index on utf8mb4 data 712# 713set names utf8mb4; 714--disable_warnings 715eval create table t1 ( 716 a int primary key, 717 b varchar(6), 718 index b3(b(3)) 719) engine=$engine character set=utf8mb4; 720--enable_warnings 721insert into t1 values(1,'foo'),(2,'foobar'); 722--sorted_result 723select * from t1 where b like 'foob%'; 724--disable_warnings 725alter table t1 engine=innodb; 726--enable_warnings 727--sorted_result 728select * from t1 where b like 'foob%'; 729drop table t1; 730 731# 732# Test for calculate_interval_lengths() function 733# 734eval create table t1 ( 735 a enum('петя','вася','анюта') character set utf8mb4 not null default 'анюта', 736 b set('петя','вася','анюта') character set utf8mb4 not null default 'анюта' 737) engine $engine; 738eval create table t2 engine $engine select concat(a,_utf8mb4'') as a, concat(b,_utf8mb4'')as b from t1; 739show create table t2; 740drop table t2; 741drop table t1; 742 743# 744# Bug #6787 LIKE not working properly with _ and utf8mb4 data 745# 746select 'c' like '\_' as want0; 747 748# 749# SUBSTR with negative offset didn't work with multi-byte strings 750# 751SELECT SUBSTR('вася',-2); 752 753 754# 755# Bug #7730 Server crash using soundex on an utf8mb4 table 756# 757eval create table t1 (id integer, a varchar(100) character set utf8mb4 collate utf8mb4_unicode_ci) engine $engine; 758insert into t1 values (1, 'Test'); 759select * from t1 where soundex(a) = soundex('Test'); 760select * from t1 where soundex(a) = soundex('TEST'); 761select * from t1 where soundex(a) = soundex('test'); 762drop table t1; 763 764# 765# Bug#22638 SOUNDEX broken for international characters 766# 767select soundex(_utf8mb4 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB); 768select hex(soundex(_utf8mb4 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB)); 769select soundex(_utf8mb4 0xD091D092D093); 770select hex(soundex(_utf8mb4 0xD091D092D093)); 771 772 773SET collation_connection='utf8mb4_general_ci'; 774-- source include/ctype_filesort.inc 775-- source include/ctype_like_escape.inc 776-- source include/ctype_german.inc 777SET collation_connection='utf8mb4_bin'; 778-- source include/ctype_filesort.inc 779-- source include/ctype_like_escape.inc 780 781# 782# Bug #7874 CONCAT() gives wrong results mixing 783# latin1 field and utf8mb4 string literals 784# 785eval CREATE TABLE t1 ( 786 user varchar(255) NOT NULL default '' 787) ENGINE=$engine DEFAULT CHARSET=latin1; 788INSERT INTO t1 VALUES ('one'),('two'); 789SELECT CHARSET('a'); 790--sorted_result 791SELECT user, CONCAT('<', user, '>') AS c FROM t1; 792DROP TABLE t1; 793 794# 795# Bug#8785 796# the same problem with the above, but with nested CONCATs 797# 798eval create table t1 (f1 varchar(1) not null) default charset utf8mb4 engine $engine; 799insert into t1 values (''), (''); 800select concat(concat(_latin1'->',f1),_latin1'<-') from t1; 801drop table t1; 802 803# 804# Bug#8385: utf8mb4_general_ci treats Cyrillic letters I and SHORT I as the same 805# 806select convert(_koi8r'�' using utf8mb4) < convert(_koi8r'�' using utf8mb4); 807 808# 809# Bugs#5980: NULL requires a characterset in a union 810# 811set names latin1; 812eval create table t1 (a varchar(10)) character set utf8mb4 engine $engine; 813insert into t1 values ('test'); 814select ifnull(a,'') from t1; 815drop table t1; 816select repeat(_utf8mb4'+',3) as h union select NULL; 817select ifnull(NULL, _utf8mb4'string'); 818 819# 820# Bug#9509 Optimizer: wrong result after AND with comparisons 821# 822set names utf8mb4; 823eval create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_lithuanian_ci) engine $engine; 824insert into t1 values ('I'),('K'),('Y'); 825--sorted_result 826select * from t1 where s1 < 'K' and s1 = 'Y'; 827--sorted_result 828select * from t1 where 'K' > s1 and s1 = 'Y'; 829drop table t1; 830 831eval create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_czech_ci) engine $engine; 832insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i'); 833--sorted_result 834select * from t1 where s1 > 'd' and s1 = 'CH'; 835--sorted_result 836select * from t1 where 'd' < s1 and s1 = 'CH'; 837--sorted_result 838select * from t1 where s1 = 'cH' and s1 <> 'ch'; 839--sorted_result 840select * from t1 where 'cH' = s1 and s1 <> 'ch'; 841drop table t1; 842 843# 844# Bug#10714: Inserting double value into utf8mb4 column crashes server 845# 846eval create table t1 (a varchar(255)) default character set utf8mb4 engine $engine; 847insert into t1 values (1.0); 848drop table t1; 849 850# 851# Bug#10253 compound index length and utf8mb4 char set 852# produces invalid query results 853# 854eval create table t1 ( 855 id int not null, 856 city varchar(20) not null, 857 key (city(7),id) 858) character set=utf8mb4 engine $engine; 859insert into t1 values (1,'Durban North'); 860insert into t1 values (2,'Durban'); 861select * from t1 where city = 'Durban'; 862select * from t1 where city = 'Durban '; 863drop table t1; 864 865# 866# Bug #11819 CREATE TABLE with a SET DEFAULT 0 and UTF8 crashes server. 867# 868--error 1067 869eval create table t1 (x set('A', 'B') default 0) character set utf8mb4 engine $engine; 870--error 1067 871eval create table t1 (x enum('A', 'B') default 0) character set utf8mb4 engine $engine; 872 873 874# 875# Test for bug #11167: join for utf8mb4 varchar value longer than 255 bytes 876# 877 878SET NAMES UTF8; 879 880eval CREATE TABLE t1 ( 881 `id` int(20) NOT NULL auto_increment, 882 `country` varchar(100) NOT NULL default '', 883 `shortcode` varchar(100) NOT NULL default '', 884 `operator` varchar(100) NOT NULL default '', 885 `momid` varchar(30) NOT NULL default '', 886 `keyword` varchar(160) NOT NULL default '', 887 `content` varchar(160) NOT NULL default '', 888 `second_token` varchar(160) default NULL, 889 `gateway_id` int(11) NOT NULL default '0', 890 `created` datetime NOT NULL default '0000-00-00 00:00:00', 891 `msisdn` varchar(15) NOT NULL default '', 892 PRIMARY KEY (`id`), 893 UNIQUE KEY `MSCCSPK_20030521130957121` (`momid`), 894 KEY `IX_mobile_originated_message_keyword` (`keyword`), 895 KEY `IX_mobile_originated_message_created` (`created`), 896 KEY `IX_mobile_originated_message_support` (`msisdn`,`momid`,`keyword`,`gateway_id`,`created`) 897) ENGINE=$engine DEFAULT CHARSET=utf8mb4; 898 899INSERT INTO t1 VALUES 900(1,'blah','464','aaa','fkc1c9ilc20x0hgae7lx6j09','ERR','ERR Имри.Афимим.Аеимимримдмримрмрирор имримримримр имридм ирбднримрфмририримрфмфмим.Ад.Д имдимримрад.Адимримримрмдиримримримр м.Дадимфшьмримд им.Адимимрн имадми','ИМРИ.АФИМИМ.АЕИМИМРИМДМРИМРМРИРОР',3,'2005-06-01 17:30:43','1234567890'), 901(2,'blah','464','aaa','haxpl2ilc20x00bj4tt2m5ti','11','11 g','G',3,'2005-06-02 22:43:10','1234567890'); 902 903--disable_warnings 904eval CREATE TABLE t2 ( 905 `msisdn` varchar(15) NOT NULL default '', 906 `operator_id` int(11) NOT NULL default '0', 907 `created` datetime NOT NULL default '0000-00-00 00:00:00', 908 UNIQUE KEY `PK_user` (`msisdn`) 909) ENGINE=$engine DEFAULT CHARSET=utf8mb4; 910--enable_warnings 911 912INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25'); 913 914SELECT content, t2.msisdn FROM t1, t2 WHERE t1.msisdn = '1234567890'; 915 916DROP TABLE t1,t2; 917 918# 919# Bug#11591: CHAR column with utf8mb4 does not work properly 920# (more chars than expected) 921# 922eval create table t1 (a char(20) character set utf8mb4) engine $engine; 923insert into t1 values ('123456'),('андрей'); 924alter table t1 modify a char(2) character set utf8mb4; 925select char_length(a), length(a), a from t1 order by a; 926drop table t1; 927 928# 929# Bugs#12611 930# ESCAPE + LIKE do not work when the escape char is a multibyte one 931# 932set names utf8mb4; 933select 'andre%' like 'andreñ%' escape 'ñ'; 934 935# 936# Bugs#11754: SET NAMES utf8mb4 followed by SELECT "A\\" LIKE "A\\" returns 0 937# 938set names utf8mb4; 939select 'a\\' like 'a\\'; 940select 'aa\\' like 'a%\\'; 941 942eval create table t1 (a char(10), key(a)) character set utf8mb4 engine $engine; 943insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); 944--sorted_result 945select * from t1 where a like "abc%"; 946--sorted_result 947select * from t1 where a like concat("abc","%"); 948--sorted_result 949select * from t1 where a like "ABC%"; 950select * from t1 where a like "test%"; 951select * from t1 where a like "te_t"; 952--sorted_result 953select * from t1 where a like "%a%"; 954--sorted_result 955select * from t1 where a like "%abcd%"; 956select * from t1 where a like "%abc\d%"; 957drop table t1; 958 959 960# 961# Bug#9557 MyISAM utf8mb4 table crash 962# 963eval CREATE TABLE t1 ( 964 a varchar(255) NOT NULL default '', 965 KEY a (a) 966) ENGINE=$engine DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci; 967insert into t1 values (_utf8mb4 0xe880bd); 968insert into t1 values (_utf8mb4 0x5b); 969--sorted_result 970select hex(a) from t1; 971drop table t1; 972 973# 974# Bug#13751 find_in_set: Illegal mix of collations 975# 976set names 'latin1'; 977eval create table t1 (a varchar(255)) default charset=utf8mb4 engine $engine; 978select * from t1 where find_in_set('-1', a); 979drop table t1; 980 981# 982# Bug#13233: select distinct char(column) fails with utf8mb4 983# 984eval create table t1 (a int) engine $engine; 985insert into t1 values (48),(49),(50); 986set names utf8mb4; 987--sorted_result 988select distinct char(a) from t1; 989drop table t1; 990 991if (!$is_heap) 992{ 993# 994# Bug#15581: COALESCE function truncates mutli-byte TINYTEXT values 995# 996eval CREATE TABLE t1 (t TINYTEXT CHARACTER SET utf8mb4) ENGINE $engine; 997INSERT INTO t1 VALUES(REPEAT('a', 100)); 998if (!$is_ndb) 999{ 1000eval CREATE TEMPORARY TABLE t2 ENGINE $engine SELECT COALESCE(t) AS bug FROM t1; 1001} 1002if ($is_ndb) 1003{ 1004eval CREATE TABLE t2 ENGINE $engine SELECT COALESCE(t) AS bug FROM t1; 1005} 1006SELECT LENGTH(bug) FROM t2; 1007DROP TABLE t2; 1008DROP TABLE t1; 1009} 1010 1011# 1012# Bug#17313: N'xxx' and _utf8mb4'xxx' are not equivalent 1013# 1014eval CREATE TABLE t1 (item varchar(255)) default character set utf8mb4 ENGINE $engine; 1015INSERT INTO t1 VALUES (N'\\'); 1016INSERT INTO t1 VALUES (_utf8mb4'\\'); 1017INSERT INTO t1 VALUES (N'Cote d\'Ivoire'); 1018INSERT INTO t1 VALUES (_utf8mb4'Cote d\'Ivoire'); 1019SELECT item FROM t1 ORDER BY item; 1020DROP TABLE t1; 1021 1022# 1023# Bug#17705: Corruption of compressed index when index length changes between 1024# 254 and 256 1025# 1026 1027SET NAMES utf8mb4; 1028DROP TABLE IF EXISTS t1; 1029eval CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=$engine DEFAULT CHARSET=utf8mb4; 1030INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 1031INSERT INTO t1 VALUES('uu'); 1032check table t1; 1033INSERT INTO t1 VALUES('uU'); 1034check table t1; 1035INSERT INTO t1 VALUES('uu'); 1036check table t1; 1037INSERT INTO t1 VALUES('uuABC'); 1038check table t1; 1039INSERT INTO t1 VALUES('UuABC'); 1040check table t1; 1041INSERT INTO t1 VALUES('uuABC'); 1042check table t1; 1043alter table t1 add b int; 1044INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1); 1045INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',2); 1046delete from t1 where b=1; 1047INSERT INTO t1 VALUES('UUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1); 1048check table t1; 1049INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3); 1050INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',4); 1051delete from t1 where b=3; 1052INSERT INTO t1 VALUES('uUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3); 1053check table t1; 1054drop table t1; 1055 1056# 1057# Bug#20471 LIKE search fails with indexed utf8mb4 char column 1058# 1059set names utf8mb4; 1060eval create table t1 (s1 char(5) character set utf8mb4) engine $engine; 1061insert into t1 values 1062('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); 1063create index it1 on t1 (s1); 1064select s1 as before_delete_general_ci from t1 where s1 like 'ペテ%'; 1065delete from t1 where s1 = 'Y'; 1066select s1 as after_delete_general_ci from t1 where s1 like 'ペテ%'; 1067drop table t1; 1068 1069set names utf8mb4; 1070eval create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_unicode_ci) engine $engine; 1071insert into t1 values 1072('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); 1073create index it1 on t1 (s1); 1074select s1 as before_delete_unicode_ci from t1 where s1 like 'ペテ%'; 1075delete from t1 where s1 = 'Y'; 1076select s1 as after_delete_unicode_ci from t1 where s1 like 'ペテ%'; 1077drop table t1; 1078 1079set names utf8mb4; 1080eval create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_bin) engine $engine; 1081insert into t1 values 1082('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); 1083create index it1 on t1 (s1); 1084select s1 as before_delete_bin from t1 where s1 like 'ペテ%'; 1085delete from t1 where s1 = 'Y'; 1086select s1 as after_delete_bin from t1 where s1 like 'ペテ%'; 1087drop table t1; 1088 1089# additional tests from duplicate bug#20744 MySQL return no result 1090 1091set names utf8mb4; 1092--disable_warnings 1093eval create table t1 (a varchar(30) not null primary key) 1094engine=$engine default character set utf8mb4 collate utf8mb4_general_ci; 1095--enable_warnings 1096insert into t1 values ('あいうえおかきくけこさしすせそ'); 1097insert into t1 values ('さしすせそかきくけこあいうえお'); 1098select a as gci1 from t1 where a like 'さしすせそかきくけこあいうえお%'; 1099select a as gci2 from t1 where a like 'あいうえおかきくけこさしすせそ'; 1100drop table t1; 1101 1102set names utf8mb4; 1103--disable_warnings 1104eval create table t1 (a varchar(30) not null primary key) 1105engine=$engine default character set utf8mb4 collate utf8mb4_unicode_ci; 1106--enable_warnings 1107insert into t1 values ('あいうえおかきくけこさしすせそ'); 1108insert into t1 values ('さしすせそかきくけこあいうえお'); 1109select a as uci1 from t1 where a like 'さしすせそかきくけこあいうえお%'; 1110select a as uci2 from t1 where a like 'あいうえおかきくけこさしすせそ'; 1111drop table t1; 1112 1113set names utf8mb4; 1114--disable_warnings 1115eval create table t1 (a varchar(30) not null primary key) 1116engine=$engine default character set utf8mb4 collate utf8mb4_bin; 1117--enable_warnings 1118insert into t1 values ('あいうえおかきくけこさしすせそ'); 1119insert into t1 values ('さしすせそかきくけこあいうえお'); 1120select a as bin1 from t1 where a like 'さしすせそかきくけこあいうえお%'; 1121select a as bin2 from t1 where a like 'あいうえおかきくけこさしすせそ'; 1122drop table t1; 1123 1124 1125 1126# 1127# Bug#14896: Comparison with a key in a partial index over mb chararacter field 1128# 1129 1130SET NAMES utf8mb4; 1131eval CREATE TABLE t1 (id int PRIMARY KEY, 1132 a varchar(16) collate utf8mb4_unicode_ci NOT NULL default '', 1133 b int, 1134 f varchar(128) default 'XXX', 1135 INDEX (a(4)) 1136) ENGINE=$engine DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; 1137INSERT INTO t1(id, a, b) VALUES 1138 (1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30), 1139 (4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40), 1140 (7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50), 1141 (10, 'eeeee', 40), (11, 'bbbbbb', 60); 1142 1143--sorted_result 1144SELECT id, a, b FROM t1; 1145 1146--sorted_result 1147SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb'; 1148 1149--sorted_result 1150SELECT id, a FROM t1 WHERE a='bbbbbb'; 1151SELECT id, a FROM t1 WHERE a='bbbbbb' ORDER BY b; 1152 1153DROP TABLE t1; 1154 1155# 1156# Bug#16674: LIKE predicate for a utf8mb4 character set column 1157# 1158 1159SET NAMES utf8mb4; 1160 1161eval CREATE TABLE t1 ( 1162 a CHAR(13) DEFAULT '', 1163 INDEX(a) 1164) ENGINE=$engine DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 1165INSERT INTO t1 VALUES 1166 ('Käli Käli 2-4'), ('Käli Käli 2-4'), 1167 ('Käli Käli 2+4'), ('Käli Käli 2+4'), 1168 ('Käli Käli 2-6'), ('Käli Käli 2-6'); 1169INSERT INTO t1 SELECT * FROM t1; 1170 1171eval CREATE TABLE t2 ( 1172 a CHAR(13) DEFAULT '', 1173 INDEX(a) 1174) ENGINE=$engine DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 1175 1176INSERT INTO t2 VALUES 1177 ('Kali Kali 2-4'), ('Kali Kali 2-4'), 1178 ('Kali Kali 2+4'), ('Kali Kali 2+4'), 1179 ('Kali Kali 2-6'), ('Kali Kali 2-6'); 1180INSERT INTO t2 SELECT * FROM t2; 1181 1182-- disable_query_log 1183-- disable_result_log 1184analyze table t1; 1185analyze table t2; 1186-- enable_result_log 1187-- enable_query_log 1188 1189SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4'; 1190SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4'; 1191 1192EXPLAIN SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4'; 1193EXPLAIN SELECT a FROM t1 WHERE a = 'Käli Käli 2+4'; 1194EXPLAIN SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4'; 1195EXPLAIN SELECT a FROM t2 WHERE a = 'Kali Kali 2+4'; 1196 1197DROP TABLE t1,t2; 1198 1199eval CREATE TABLE t1 ( 1200 a char(255) DEFAULT '', 1201 KEY(a(10)) 1202) ENGINE=$engine DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 1203INSERT INTO t1 VALUES ('Käli Käli 2-4'); 1204SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%'; 1205INSERT INTO t1 VALUES ('Käli Käli 2-4'); 1206SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%'; 1207DROP TABLE t1; 1208 1209eval CREATE TABLE t1 ( 1210 a char(255) DEFAULT '' 1211) ENGINE=$engine DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; 1212INSERT INTO t1 VALUES ('Käli Käli 2-4'); 1213INSERT INTO t1 VALUES ('Käli Käli 2-4'); 1214SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%'; 1215ALTER TABLE t1 ADD KEY (a(10)); 1216SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%'; 1217DROP TABLE t1; 1218 1219# 1220# Bug#18359: LIKE predicate for a 'utf8mb4' text column with a partial index 1221# (see bug #16674 as well) 1222# 1223 1224SET NAMES latin2; 1225 1226if (!$is_heap) 1227{ 1228if (!$is_ndb) 1229{ 1230eval CREATE TABLE t1 ( 1231 id int(11) NOT NULL default '0', 1232 tid int(11) NOT NULL default '0', 1233 val text NOT NULL, 1234 INDEX idx(tid, val(10)) 1235) ENGINE=$engine DEFAULT CHARSET=utf8mb4; 1236 1237INSERT INTO t1 VALUES 1238 (40988,72,'VOLN� ADSL'),(41009,72,'VOLN� ADSL'), 1239 (41032,72,'VOLN� ADSL'),(41038,72,'VOLN� ADSL'), 1240 (41063,72,'VOLN� ADSL'),(41537,72,'VOLN� ADSL Office'), 1241 (42141,72,'VOLN� ADSL'),(42565,72,'VOLN� ADSL Combi'), 1242 (42749,72,'VOLN� ADSL'),(44205,72,'VOLN� ADSL'); 1243 1244SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNY ADSL'; 1245SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL'; 1246SELECT * FROM t1 WHERE tid=72 and val LIKE '%VOLN� ADSL'; 1247 1248ALTER TABLE t1 DROP KEY idx; 1249ALTER TABLE t1 ADD KEY idx (tid,val(11)); 1250 1251SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL'; 1252 1253DROP TABLE t1; 1254} 1255} 1256 1257# 1258# Bug 20709: problem with utf8mb4 fields in temporary tables 1259# 1260 1261eval create table t1(a char(200) collate utf8mb4_unicode_ci NOT NULL default '') 1262 default charset=utf8mb4 collate=utf8mb4_unicode_ci engine $engine; 1263insert into t1 values (unhex('65')), (unhex('C3A9')), (unhex('65')); 1264-- disable_query_log 1265-- disable_result_log 1266analyze table t1; 1267-- enable_result_log 1268-- enable_query_log 1269explain select distinct a from t1; 1270SELECT COUNT(*) FROM (SELECT DISTINCT a FROM t1) AS t2; 1271explain select a from t1 group by a; 1272SELECT COUNT(*) FROM (SELECT a FROM t1 GROUP BY a) AS t2; 1273drop table t1; 1274 1275# 1276# Bug #20204: "order by" changes the results returned 1277# 1278 1279eval create table t1(a char(10)) default charset utf8mb4 engine $engine; 1280insert into t1 values ('123'), ('456'); 1281-- disable_query_log 1282-- disable_result_log 1283analyze table t1; 1284-- enable_result_log 1285-- enable_query_log 1286explain 1287 select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; 1288select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; 1289drop table t1; 1290 1291# 1292# Bug #34349: Passing invalid parameter to CHAR() in an ORDER BY causes 1293# MySQL to hang 1294# 1295 1296SET CHARACTER SET utf8mb4; 1297SHOW VARIABLES LIKE 'character\_set\_%'; 1298CREATE DATABASE crashtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; 1299USE crashtest; 1300eval CREATE TABLE crashtest (crash char(10)) DEFAULT CHARSET=utf8mb4 ENGINE $engine; 1301INSERT INTO crashtest VALUES ('35'), ('36'), ('37'); 1302SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8mb4); 1303INSERT INTO crashtest VALUES ('-1000'); 1304-- disable_query_log 1305-- disable_result_log 1306ANALYZE TABLE crashtest; 1307-- enable_result_log 1308-- enable_query_log 1309EXPLAIN SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8mb4); 1310SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8mb4); 1311DROP TABLE crashtest; 1312DROP DATABASE crashtest; 1313USE test; 1314SET CHARACTER SET default; 1315 1316# End of 4.1 tests 1317 1318# 1319# Test for bug #11484: wrong results for a DISTINCT varchar column in uft8. 1320# 1321 1322eval CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8mb4 ENGINE $engine; 1323INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa'); 1324 1325--sorted_result 1326SELECT id FROM t1; 1327--sorted_result 1328SELECT DISTINCT id FROM t1; 1329SELECT DISTINCT id FROM t1 ORDER BY id; 1330 1331DROP TABLE t1; 1332 1333# 1334# Bug#20095 Changing length of VARCHAR field with UTF8 1335# collation does not truncate values 1336# 1337eval create table t1 ( 1338 a varchar(26) not null 1339) default character set utf8mb4 ENGINE $engine; 1340insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz'); 1341select * from t1; 1342# varchar to varchar 1343alter table t1 change a a varchar(20) character set utf8mb4 not null; 1344select * from t1; 1345# varchar to char 1346alter table t1 change a a char(15) character set utf8mb4 not null; 1347select * from t1; 1348# char to char 1349alter table t1 change a a char(10) character set utf8mb4 not null; 1350select * from t1; 1351# char to varchar 1352alter table t1 change a a varchar(5) character set utf8mb4 not null; 1353select * from t1; 1354drop table t1; 1355 1356# 1357# Check that do_varstring2_mb produces a warning 1358# 1359if (!$is_ndb) 1360{ 1361eval create table t1 ( 1362 a varchar(4000) not null 1363) default character set utf8mb4 engine $engine; 1364insert into t1 values (repeat('a',4000)); 1365alter table t1 change a a varchar(3000) character set utf8mb4 not null; 1366select length(a) from t1; 1367drop table t1; 1368} 1369 1370# 1371# Bug#10504: Character set does not support traditional mode 1372# Bug#14146: CHAR(...USING ...) and CONVERT(CHAR(...) USING...) 1373# produce different results 1374# 1375set names utf8mb4; 1376# correct value 1377select hex(char(1 using utf8mb4)); 1378select char(0xd1,0x8f using utf8mb4); 1379select char(0xd18f using utf8mb4); 1380select char(53647 using utf8mb4); 1381# incorrect value: return with warning 1382select char(0xff,0x8f using utf8mb4); 1383select convert(char(0xff,0x8f) using utf8mb4); 1384# incorrect value in strict mode: return NULL with "Error" level warning 1385set sql_mode=traditional; 1386select char(0xff,0x8f using utf8mb4); 1387select char(195 using utf8mb4); 1388select char(196 using utf8mb4); 1389select char(2557 using utf8mb4); 1390select convert(char(0xff,0x8f) using utf8mb4); 1391 1392# 1393# Check convert + char + using 1394# 1395select hex(convert(char(2557 using latin1) using utf8mb4)); 1396 1397# 1398# char() without USING returns "binary" by default, any argument is ok 1399# 1400select hex(char(195)); 1401select hex(char(196)); 1402select hex(char(2557)); 1403 1404 1405 1406# 1407# Bug#12891: UNION doesn't return DISTINCT result for multi-byte characters 1408# 1409set names utf8mb4; 1410eval create table t1 (a char(1)) default character set utf8mb4 engine $engine; 1411eval create table t2 (a char(1)) default character set utf8mb4 engine $engine; 1412insert into t1 values('a'),('a'),(0xE38182),(0xE38182); 1413insert into t1 values('i'),('i'),(0xE38184),(0xE38184); 1414--sorted_result 1415select * from t1 union distinct select * from t2; 1416drop table t1,t2; 1417 1418 1419# 1420# Bug#12371: executing prepared statement fails (illegal mix of collations) 1421# 1422set names utf8mb4; 1423eval create table t1 (a char(10), b varchar(10)) engine $engine; 1424insert into t1 values ('bar','kostja'); 1425insert into t1 values ('kostja','bar'); 1426prepare my_stmt from "select * from t1 where a=?"; 1427set @a:='bar'; 1428execute my_stmt using @a; 1429set @a:='kostja'; 1430execute my_stmt using @a; 1431set @a:=null; 1432execute my_stmt using @a; 1433drop table if exists t1; 1434 1435 1436# 1437# Bug#21505 Create view - illegal mix of collation for operation 'UNION' 1438# 1439--disable_warnings 1440drop table if exists t1; 1441drop view if exists v1, v2; 1442--enable_warnings 1443set names utf8mb4; 1444eval create table t1(col1 varchar(12) character set utf8mb4 collate utf8mb4_unicode_ci) engine $engine; 1445insert into t1 values('t1_val'); 1446create view v1 as select 'v1_val' as col1; 1447select coercibility(col1), collation(col1) from v1; 1448create view v2 as select col1 from v1 union select col1 from t1; 1449select coercibility(col1), collation(col1)from v2; 1450drop view v1, v2; 1451create view v1 as select 'v1_val' collate utf8mb4_swedish_ci as col1; 1452select coercibility(col1), collation(col1) from v1; 1453create view v2 as select col1 from v1 union select col1 from t1; 1454select coercibility(col1), collation(col1) from v2; 1455drop view v1, v2; 1456drop table t1; 1457 1458# 1459# Check conversion of NCHAR strings to subset (e.g. latin1). 1460# Conversion is possible if string repertoire is ASCII. 1461# Conversion is not possible if the string have extended characters 1462# 1463set names utf8mb4; 1464eval create table t1 (a varchar(10) character set latin1, b int) engine $engine; 1465insert into t1 values ('a',1); 1466select concat(a, if(b>10, N'x', N'y')) from t1; 1467--error 1267 1468select concat(a, if(b>10, N'æ', N'ß')) from t1; 1469drop table t1; 1470 1471# Conversion tests for character set introducers 1472set names utf8mb4; 1473eval create table t1 (a varchar(10) character set latin1, b int) engine $engine; 1474insert into t1 values ('a',1); 1475select concat(a, if(b>10, _utf8mb4'x', _utf8mb4'y')) from t1; 1476--error 1267 1477select concat(a, if(b>10, _utf8mb4'æ', _utf8mb4'ß')) from t1; 1478drop table t1; 1479 1480# Conversion tests for introducer + HEX string 1481set names utf8mb4; 1482eval create table t1 (a varchar(10) character set latin1, b int) engine $engine; 1483insert into t1 values ('a',1); 1484select concat(a, if(b>10, _utf8mb4 0x78, _utf8mb4 0x79)) from t1; 1485--error 1267 1486select concat(a, if(b>10, _utf8mb4 0xC3A6, _utf8mb4 0xC3AF)) from t1; 1487drop table t1; 1488 1489# Conversion tests for "text_literal TEXT_STRING_literal" syntax structure 1490set names utf8mb4; 1491eval create table t1 (a varchar(10) character set latin1, b int) engine $engine; 1492insert into t1 values ('a',1); 1493select concat(a, if(b>10, 'x' 'x', 'y' 'y')) from t1; 1494--error 1267 1495select concat(a, if(b>10, 'x' 'æ', 'y' 'ß')) from t1; 1496drop table t1; 1497 1498 1499# 1500# Bug#19960: Inconsistent results when joining 1501# InnoDB tables using partial UTF8 indexes 1502# 1503--disable_warnings 1504eval CREATE TABLE t1 ( 1505 colA int(11) NOT NULL, 1506 colB varchar(255) character set utf8mb4 NOT NULL, 1507 PRIMARY KEY (colA) 1508) ENGINE=$engine DEFAULT CHARSET=latin1; 1509--enable_warnings 1510INSERT INTO t1 (colA, colB) VALUES (1, 'foo'), (2, 'foo bar'); 1511--disable_warnings 1512eval CREATE TABLE t2 ( 1513 colA int(11) NOT NULL, 1514 colB varchar(255) character set utf8mb4 NOT NULL, 1515 KEY bad (colA,colB(3)) 1516) ENGINE=$engine DEFAULT CHARSET=latin1; 1517--enable_warnings 1518INSERT INTO t2 (colA, colB) VALUES (1, 'foo'),(2, 'foo bar'); 1519SELECT * FROM t1 JOIN t2 ON t1.colA=t2.colA AND t1.colB=t2.colB 1520WHERE t1.colA < 3; 1521DROP TABLE t1, t2; 1522 1523# 1524# Bug#29205: truncation of UTF8 values when the UNION statement 1525# forces collation to the binary charset 1526# 1527 1528SELECT 'н1234567890' UNION SELECT _binary '1'; 1529SELECT 'н1234567890' UNION SELECT 1; 1530 1531SELECT '1' UNION SELECT 'н1234567890'; 1532SELECT 1 UNION SELECT 'н1234567890'; 1533 1534eval CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8mb4 ENGINE $engine; 1535eval CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT) ENGINE $engine; 1536 1537INSERT INTO t1 (c) VALUES ('н1234567890'); 1538INSERT INTO t2 (b, i) VALUES ('1', 1); 1539 1540SELECT c FROM t1 UNION SELECT b FROM t2; 1541SELECT c FROM t1 UNION SELECT i FROM t2; 1542 1543SELECT b FROM t2 UNION SELECT c FROM t1; 1544SELECT i FROM t2 UNION SELECT c FROM t1; 1545 1546DROP TABLE t1, t2; 1547 1548# 1549# Bug#30982: CHAR(..USING..) can return a not-well-formed string 1550# Bug #30986: Character set introducer followed by a HEX string can return bad result 1551# 1552set sql_mode=traditional; 1553select hex(char(0xFF using utf8mb4)); 1554select hex(convert(0xFF using utf8mb4)); 1555--error ER_INVALID_CHARACTER_STRING 1556select hex(_utf8mb4 0x616263FF); 1557--error ER_INVALID_CHARACTER_STRING 1558select hex(_utf8mb4 X'616263FF'); 1559--error ER_INVALID_CHARACTER_STRING 1560select hex(_utf8mb4 B'001111111111'); 1561--error ER_INVALID_CHARACTER_STRING 1562select (_utf8mb4 X'616263FF'); 1563set sql_mode=default; 1564select hex(char(0xFF using utf8mb4)); 1565select hex(convert(0xFF using utf8mb4)); 1566--error ER_INVALID_CHARACTER_STRING 1567select hex(_utf8mb4 0x616263FF); 1568--error ER_INVALID_CHARACTER_STRING 1569select hex(_utf8mb4 X'616263FF'); 1570--error ER_INVALID_CHARACTER_STRING 1571select hex(_utf8mb4 B'001111111111'); 1572--error ER_INVALID_CHARACTER_STRING 1573select (_utf8mb4 X'616263FF'); 1574 1575# 1576# Bug #36772: When using UTF8, CONVERT with GROUP BY returns truncated results 1577# 1578eval CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL) ENGINE $engine; 1579INSERT INTO t1 VALUES (70000, 1092), (70001, 1085), (70002, 1065); 1580--sorted_result 1581SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; 1582--sorted_result 1583SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1; 1584ALTER TABLE t1 ADD UNIQUE (b); 1585--sorted_result 1586SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; 1587DROP INDEX b ON t1; 1588--sorted_result 1589SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; 1590ALTER TABLE t1 ADD INDEX (b); 1591--sorted_result 1592SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) from t1 GROUP BY b; 1593DROP TABLE t1; 1594 1595--echo # 1596--echo # Bug#26474: Add Sinhala script (Sri Lanka) collation to MySQL 1597--echo # 1598--disable_warnings 1599DROP TABLE IF EXISTS t1; 1600--enable_warnings 1601eval CREATE TABLE t1 ( 1602 predicted_order int NOT NULL, 1603 utf8mb4_encoding VARCHAR(10) NOT NULL 1604) CHARACTER SET utf8mb4 ENGINE $engine; 1605INSERT INTO t1 VALUES (19, x'E0B696'), (30, x'E0B69AE0B798'), (61, x'E0B6AF'), (93, x'E0B799'), (52, x'E0B6A6'), (73, x'E0B6BBE0B78AE2808D'), (3, x'E0B686'), (56, x'E0B6AA'), (55, x'E0B6A9'), (70, x'E0B6B9'), (94, x'E0B79A'), (80, x'E0B785'), (25, x'E0B69AE0B791'), (48, x'E0B6A2'), (13, x'E0B690'), (86, x'E0B793'), (91, x'E0B79F'), (81, x'E0B786'), (79, x'E0B784'), (14, x'E0B691'), (99, x'E0B78A'), (8, x'E0B68B'), (68, x'E0B6B7'), (22, x'E0B69A'), (16, x'E0B693'), (33, x'E0B69AE0B7B3'), (38, x'E0B69AE0B79D'), (21, x'E0B683'), (11, x'E0B68E'), (77, x'E0B782'), (40, x'E0B69AE0B78A'), (101, x'E0B78AE2808DE0B6BB'), (35, x'E0B69AE0B79A'), (1, x'E0B7B4'), (9, x'E0B68C'), (96, x'E0B79C'), (6, x'E0B689'), (95, x'E0B79B'), (88, x'E0B796'), (64, x'E0B6B3'), (26, x'E0B69AE0B792'), (82, x'E0B78F'), (28, x'E0B69AE0B794'), (39, x'E0B69AE0B79E'), (97, x'E0B79D'), (2, x'E0B685'), (75, x'E0B780'), (34, x'E0B69AE0B799'), (69, x'E0B6B8'), (83, x'E0B790'), (18, x'E0B695'), (90, x'E0B7B2'), (17, x'E0B694'), (72, x'E0B6BB'), (66, x'E0B6B5'), (59, x'E0B6AD'), (44, x'E0B69E'), (15, x'E0B692'), (23, x'E0B69AE0B78F'), (65, x'E0B6B4'), (42, x'E0B69C'), (63, x'E0B6B1'), (85, x'E0B792'), (47, x'E0B6A1'), (49, x'E0B6A3'), (92, x'E0B7B3'), (78, x'E0B783'), (36, x'E0B69AE0B79B'), (4, x'E0B687'), (24, x'E0B69AE0B790'), (87, x'E0B794'), (37, x'E0B69AE0B79C'), (32, x'E0B69AE0B79F'), (29, x'E0B69AE0B796'), (43, x'E0B69D'), (62, x'E0B6B0'), (100, x'E0B78AE2808DE0B6BA'), (60, x'E0B6AE'), (45, x'E0B69F'), (12, x'E0B68F'), (46, x'E0B6A0'), (50, x'E0B6A5'), (51, x'E0B6A4'), (5, x'E0B688'), (76, x'E0B781'), (89, x'E0B798'), (74, x'E0B6BD'), (10, x'E0B68D'), (57, x'E0B6AB'), (71, x'E0B6BA'), (58, x'E0B6AC'), (27, x'E0B69AE0B793'), (54, x'E0B6A8'), (84, x'E0B791'), (31, x'E0B69AE0B7B2'), (98, x'E0B79E'), (53, x'E0B6A7'), (41, x'E0B69B'), (67, x'E0B6B6'), (7, x'E0B68A'), (20, x'E0B682'); 1606SELECT predicted_order, hex(utf8mb4_encoding) FROM t1 ORDER BY utf8mb4_encoding COLLATE utf8mb4_sinhala_ci; 1607DROP TABLE t1; 1608 1609--echo # 1610--echo # Bug#32914 Character sets: illegal characters in utf8mb4 and utf32 columns 1611--echo # 1612eval create table t1 (utf8mb4 char(1) character set utf8mb4) engine $engine; 1613--echo Testing [F0][90..BF][80..BF][80..BF] 1614insert into t1 values (0xF0908080); 1615insert into t1 values (0xF0BFBFBF); 1616insert into t1 values (0xF08F8080); 1617--sorted_result 1618select hex(utf8mb4) from t1; 1619delete from t1; 1620 1621--echo Testing [F2..F3][80..BF][80..BF][80..BF] 1622insert into t1 values (0xF2808080); 1623insert into t1 values (0xF2BFBFBF); 1624--sorted_result 1625select hex(utf8mb4) from t1; 1626delete from t1; 1627 1628--echo Testing [F4][80..8F][80..BF][80..BF] 1629insert into t1 values (0xF4808080); 1630insert into t1 values (0xF48F8080); 1631insert into t1 values (0xF4908080); 1632--sorted_result 1633select hex(utf8mb4) from t1; 1634drop table t1; 1635 1636 1637--echo # 1638--echo # Check strnxfrm() with odd length 1639--echo # 1640set max_sort_length=5; 1641select @@max_sort_length; 1642eval create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci) engine $engine; 1643insert into t1 values ('a'),('b'),('c'); 1644select * from t1 order by a; 1645alter table t1 modify a varchar(128) character set utf8mb4 collate utf8mb4_bin; 1646select * from t1 order by a; 1647drop table t1; 1648set max_sort_length=default; 1649 1650--echo # 1651--echo # Bug#26180: Can't add columns to tables created with utf8mb4 text indexes 1652--echo # 1653if (!$is_heap) 1654{ 1655if (!$is_ndb) 1656{ 1657eval CREATE TABLE t1 ( 1658 clipid INT NOT NULL, 1659 Tape TINYTEXT, 1660 PRIMARY KEY (clipid), 1661 KEY tape(Tape(255)) 1662) CHARACTER SET=utf8mb4 ENGINE $engine; 1663ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid; 1664SHOW CREATE TABLE t1; 1665DROP TABLE t1; 1666} 1667} 1668 1669#--echo # 1670#--echo # Check that supplementary characters are not allowed in identifiers 1671#--echo # 1672# TODO: activate this when system_charset_info is changed to utf8mb4 1673#--error 1300 1674#CREATE DATABASE `��`; 1675#--error 1300 1676#CREATE TABLE `��` (a int); 1677#--error 1166 1678#CREATE TABLE test.t1 SELECT '��'; 1679#--error 1300 1680#CREATE USER `��`; 1681 1682--echo # 1683--echo # Testing 4-byte values. 1684--echo # 1685 1686--disable_warnings 1687DROP TABLE IF EXISTS t1; 1688--enable_warnings 1689eval CREATE TABLE t1 ( 1690 u_decimal int NOT NULL, 1691 utf8mb4_encoding VARCHAR(10) NOT NULL 1692) CHARACTER SET utf8mb4 ENGINE $engine; 1693# Source of the following values: http://www.fileformat.info/info/unicode/block/index.htm 1694# SINGLE BARLINE 1695INSERT INTO t1 VALUES (119040, x'f09d8480'), 1696# G CLEF 1697 (119070, x'f09d849e'), 1698# HALF NOTE 1699 (119134, x'f09d859e'), 1700# MUSICAL SYMBOL CROIX 1701 (119247, x'f09d878f'), 1702# MATHEMATICAL BOLD ITALIC CAPITAL DELTA 1703 (120607, x'f09d9c9f'), 1704# SANS-SERIF BOLD ITALIC CAPITAL PI 1705 (120735, x'f09d9e9f'), 1706# <Plane 16 Private Use, Last> (last 4 byte character) 1707 (1114111, x'f48fbfbf'), 1708# VARIATION SELECTOR-256 1709 (917999, x'f3a087af'); 1710# All from musical chars 1711INSERT INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859ff09d859ff09d85a0f09d85a0f09d8480'); 1712# Mix of 3-byte and 4-byte chars 1713INSERT INTO t1 VALUES (65131, x'efb9abf09d849ef09d859ef09d859ef09d8480f09d859fefb9abefb9abf09d85a0efb9ab'); 1714# All from musical chars, but 11 instead of 10 chars. truncated 1715INSERT INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859ff09d859ff09d85a0f09d85a0f09d8480f09d85a0'); 1716 1717--sorted_result 1718SELECT u_decimal, hex(utf8mb4_encoding) FROM t1 ORDER BY utf8mb4_encoding COLLATE utf8mb4_general_ci, BINARY utf8mb4_encoding; 1719 1720# First invalid 4 byte value 1721INSERT INTO t1 VALUES (1114111, x'f5808080'); 1722 1723--sorted_result 1724SELECT character_maximum_length, character_octet_length FROM information_schema.columns WHERE 1725 table_name= 't1' AND column_name= 'utf8mb4_encoding'; 1726 1727--disable_warnings 1728DROP TABLE IF EXISTS t2; 1729--enable_warnings 1730eval CREATE TABLE t2 ( 1731 u_decimal int NOT NULL, 1732 utf8mb3_encoding VARCHAR(10) NOT NULL 1733) CHARACTER SET utf8mb3 ENGINE $engine; 1734# LATIN CAPITAL LETTER VEND 1735INSERT INTO t2 VALUES (42856, x'ea9da8'); 1736# SMALL COMMERCIAL AT 1737INSERT INTO t2 VALUES (65131, x'efb9ab'); 1738# <Plane 16 Private Use, Last> (last 4 byte character) 1739INSERT INTO t2 VALUES (1114111, x'f48fbfbf'); 1740 1741--sorted_result 1742SELECT character_maximum_length, character_octet_length FROM information_schema.columns WHERE 1743 table_name= 't2' AND column_name= 'utf8mb3_encoding'; 1744 1745# Update a 3-byte char col with a 4-byte char, error 1746UPDATE t2 SET utf8mb3_encoding= x'f48fbfbd' where u_decimal= 42856; 1747 1748# Update to a 3-byte char casted to 4-byte, error? 1749UPDATE t2 SET utf8mb3_encoding= _utf8mb4 x'ea9da8' where u_decimal= 42856; 1750 1751# Returns utfmb4 1752--sorted_result 1753SELECT HEX(CONCAT(utf8mb4_encoding, _utf8 x'ea9da8')) FROM t1; 1754--sorted_result 1755SELECT HEX(CONCAT(utf8mb4_encoding, utf8mb3_encoding)) FROM t1,t2; 1756 1757#--error ER_INVALID_CHARACTER_STRING 1758#SELECT CONCAT(_utf8 utf8mb4_encoding, _utf8 '|') FROM t1; 1759#--error ER_INVALID_CHARACTER_STRING 1760#SELECT CONCAT(_utf8mb3 utf8mb4_encoding, _utf8 '|') FROM t1; 1761 1762SELECT count(*) FROM t1, t2 1763 WHERE t1.utf8mb4_encoding > t2.utf8mb3_encoding; 1764 1765# Alter from 4-byte charset to 3-byte charset, error 1766--disable_warnings 1767ALTER TABLE t1 CONVERT TO CHARACTER SET utf8; 1768--enable_warnings 1769SHOW CREATE TABLE t1; 1770--sorted_result 1771SELECT u_decimal,hex(utf8mb4_encoding),utf8mb4_encoding FROM t1; 1772 1773# Alter table from utf8 to utf8mb4 1774ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4; 1775SHOW CREATE TABLE t2; 1776--sorted_result 1777SELECT u_decimal,hex(utf8mb3_encoding) FROM t2; 1778 1779# Alter table back from utf8mb4 to utf8 1780ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb3; 1781SHOW CREATE TABLE t2; 1782--sorted_result 1783SELECT u_decimal,hex(utf8mb3_encoding) FROM t2; 1784 1785# ALter of utf8mb4 column to utf8 1786--disable_warnings 1787ALTER TABLE t1 MODIFY utf8mb4_encoding VARCHAR(10) CHARACTER SET utf8mb3; 1788--enable_warnings 1789SHOW CREATE TABLE t1; 1790--sorted_result 1791SELECT u_decimal,hex(utf8mb4_encoding) FROM t1; 1792 1793# ALter of utf8 column to utf8mb4 1794ALTER TABLE t1 MODIFY utf8mb4_encoding VARCHAR(10) CHARACTER SET utf8mb4; 1795SHOW CREATE TABLE t1; 1796--sorted_result 1797SELECT u_decimal,hex(utf8mb4_encoding) FROM t1; 1798 1799# ALter of utf8 column to utf8mb4 1800ALTER TABLE t2 MODIFY utf8mb3_encoding VARCHAR(10) CHARACTER SET utf8mb4; 1801SHOW CREATE TABLE t2; 1802--sorted_result 1803SELECT u_decimal,hex(utf8mb3_encoding) FROM t2; 1804 1805--disable_warnings 1806DROP TABLE IF EXISTS t3; 1807--enable_warnings 1808eval CREATE TABLE t3 ( 1809 u_decimal int NOT NULL, 1810 utf8mb3_encoding VARCHAR(10) NOT NULL 1811) CHARACTER SET utf8 ENGINE $engine; 1812 1813# Insert select utf8mb4 (4-byte) into utf8 (3-byte), error 1814#--error ER_INVALID_CHARACTER_STRING 1815INSERT INTO t3 SELECT * FROM t1; 1816 1817--disable_warnings 1818DROP TABLE IF EXISTS t4; 1819--enable_warnings 1820eval CREATE TABLE t4 ( 1821 u_decimal int NOT NULL, 1822 utf8mb4_encoding VARCHAR(10) NOT NULL 1823) CHARACTER SET utf8mb4 ENGINE $engine; 1824 1825# Insert select utf8 (3-byte) into utf8mb4 (4-byte) 1826INSERT INTO t3 SELECT * FROM t2; 1827 1828DROP TABLE t1; 1829DROP TABLE t2; 1830DROP TABLE t3; 1831DROP TABLE t4; 1832 1833--echo # 1834--echo # Testing that mixing utf8 and utf8mb4 collations returns utf8mb4 1835--echo # 1836SELECT CHARSET(CONCAT(_utf8mb4'a',_utf8'b')); 1837 1838eval CREATE TABLE t1 (utf8mb4 VARCHAR(10) CHARACTER SET utf8mb4 NOT NULL) ENGINE $engine; 1839INSERT INTO t1 VALUES (x'ea9da8'),(x'f48fbfbf'); 1840SELECT CONCAT(utf8mb4, _utf8 x'ea9da8') FROM t1 LIMIT 0; 1841 1842eval CREATE TABLE t2 (utf8mb3 VARCHAR(10) CHARACTER SET utf8mb3 NOT NULL) ENGINE $engine; 1843INSERT INTO t2 VALUES (x'ea9da8'); 1844 1845SELECT HEX(CONCAT(utf8mb4, utf8mb3)) FROM t1,t2 ORDER BY 1; 1846SELECT CHARSET(CONCAT(utf8mb4, utf8mb3)) FROM t1, t2 LIMIT 1; 1847 1848if (!$is_ndb) 1849{ 1850eval CREATE TEMPORARY TABLE t3 ENGINE $engine AS SELECT *, concat(utf8mb4,utf8mb3) FROM t1, t2; 1851SHOW CREATE TABLE t3; 1852DROP TEMPORARY TABLE t3; 1853} 1854 1855SELECT * FROM t1, t2 WHERE t1.utf8mb4 > t2.utf8mb3; 1856SELECT * FROM t1, t2 WHERE t1.utf8mb4 = t2.utf8mb3; 1857SELECT * FROM t1, t2 WHERE t1.utf8mb4 < t2.utf8mb3; 1858 1859DROP TABLE t1; 1860DROP TABLE t2; 1861 1862--echo # 1863--echo # Check that mixing utf8mb4 with an invalid utf8 constant returns error 1864--echo # 1865# This should perhaps be changed to return ER_INVALID_CHARACTER_STRING 1866eval CREATE TABLE t1 (utf8mb4 VARCHAR(10) CHARACTER SET utf8mb4) ENGINE $engine; 1867INSERT INTO t1 VALUES (x'f48fbfbf'); 1868--error ER_CANT_AGGREGATE_2COLLATIONS 1869SELECT CONCAT(utf8mb4, _utf8 '�') FROM t1; 1870--error ER_CANT_AGGREGATE_2COLLATIONS 1871SELECT CONCAT('a', _utf8 '�') FROM t1; 1872DROP TABLE t1; 1873 1874--echo # 1875--echo # End of 5.5 tests 1876--echo # 1877 1878--echo # 1879--echo # End of tests 1880--echo # 1881