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