1# 2# Tests with the utf8 character set 3# 4 5let $MYSQLD_DATADIR= `select @@datadir`; 6 7let collation=utf8_unicode_ci; 8--source include/have_collation.inc 9SET TIME_ZONE='+03:00'; 10 11--source include/have_innodb.inc 12--source include/default_optimizer_switch.inc 13--source include/default_charset.inc 14 15--disable_warnings 16drop table if exists t1,t2,t3,t4; 17drop database if exists mysqltest; 18--enable_warnings 19 20--disable_warnings 21drop table if exists t1,t2; 22--enable_warnings 23set names utf8; 24 25select left(_utf8 0xD0B0D0B1D0B2,1); 26select right(_utf8 0xD0B0D0B2D0B2,1); 27 28select locate('he','hello'); 29select locate('he','hello',2); 30select locate('lo','hello',2); 31select locate('HE','hello'); 32select locate('HE','hello',2); 33select locate('LO','hello',2); 34select locate('HE','hello' collate utf8_bin); 35select locate('HE','hello' collate utf8_bin,2); 36select locate('LO','hello' collate utf8_bin,2); 37 38select locate(_utf8 0xD0B1, _utf8 0xD0B0D0B1D0B2); 39select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2); 40select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2); 41select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2 collate utf8_bin); 42select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2 collate utf8_bin); 43 44select length(_utf8 0xD0B1), bit_length(_utf8 0xD0B1), char_length(_utf8 0xD0B1); 45 46select 'a' like 'a'; 47select 'A' like 'a'; 48select 'A' like 'a' collate utf8_bin; 49select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%'); 50 51# Bug #6040: can't retrieve records with umlaut 52# characters in case insensitive manner. 53# Case insensitive search LIKE comparison 54# was broken for multibyte characters: 55select convert(_latin1'G�nter Andr�' using utf8) like CONVERT(_latin1'G�NTER%' USING utf8); 56select CONVERT(_koi8r'����' USING utf8) LIKE CONVERT(_koi8r'����' USING utf8); 57select CONVERT(_koi8r'����' USING utf8) LIKE CONVERT(_koi8r'����' USING utf8); 58 59# 60# Check the following: 61# "a" == "a " 62# "a\0" < "a" 63# "a\0" < "a " 64 65SELECT 'a' = 'a '; 66SELECT 'a\0' < 'a'; 67SELECT 'a\0' < 'a '; 68SELECT 'a\t' < 'a'; 69SELECT 'a\t' < 'a '; 70 71# 72# The same for binary collation 73# 74SELECT 'a' = 'a ' collate utf8_bin; 75SELECT 'a\0' < 'a' collate utf8_bin; 76SELECT 'a\0' < 'a ' collate utf8_bin; 77SELECT 'a\t' < 'a' collate utf8_bin; 78SELECT 'a\t' < 'a ' collate utf8_bin; 79 80CREATE TABLE t1 (a char(10) character set utf8 not null); 81INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a '); 82SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1; 83DROP TABLE t1; 84 85# 86# Fix this, it should return 1: 87# 88#select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD091,_utf8 '%'); 89# 90 91# 92# Bug 2367: INSERT() behaviour is different for different charsets. 93# 94select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); 95select insert("aa",100,1,"b"),insert("aa",1,3,"b"); 96 97# 98# LELF() didn't work well with utf8 in some cases too. 99# 100select char_length(left(@a:='тест',5)), length(@a), @a; 101 102 103# 104# CREATE ... SELECT 105# 106create table t1 select date_format("2004-01-19 10:10:10", "%Y-%m-%d"); 107show create table t1; 108select * from t1; 109drop table t1; 110 111# 112# Bug#22646 LC_TIME_NAMES: Assignment to non-UTF8 target fails 113# 114set names utf8; 115set LC_TIME_NAMES='fr_FR'; 116create table t1 (s1 char(20) character set latin1); 117insert into t1 values (date_format('2004-02-02','%M')); 118select hex(s1) from t1; 119drop table t1; 120create table t1 (s1 char(20) character set koi8r); 121set LC_TIME_NAMES='ru_RU'; 122insert into t1 values (date_format('2004-02-02','%M')); 123insert into t1 values (date_format('2004-02-02','%b')); 124insert into t1 values (date_format('2004-02-02','%W')); 125insert into t1 values (date_format('2004-02-02','%a')); 126select hex(s1), s1 from t1; 127drop table t1; 128set LC_TIME_NAMES='en_US'; 129 130 131# 132# Bug #2366 Wrong utf8 behaviour when data is truncated 133# 134set names koi8r; 135create table t1 (s1 char(1) character set utf8); 136insert ignore into t1 values (_koi8r'��'); 137select s1,hex(s1),char_length(s1),octet_length(s1) from t1; 138drop table t1; 139 140create table t1 (s1 tinytext character set utf8); 141insert ignore into t1 select repeat('a',300); 142insert ignore into t1 select repeat('�',300); 143insert ignore into t1 select repeat('a�',300); 144insert ignore into t1 select repeat('�a',300); 145insert ignore into t1 select repeat('��',300); 146select hex(s1) from t1; 147select length(s1),char_length(s1) from t1; 148drop table t1; 149 150create table t1 (s1 text character set utf8); 151insert ignore into t1 select repeat('a',66000); 152insert ignore into t1 select repeat('�',66000); 153insert ignore into t1 select repeat('a�',66000); 154insert ignore into t1 select repeat('�a',66000); 155insert ignore into t1 select repeat('��',66000); 156select length(s1),char_length(s1) from t1; 157drop table t1; 158 159# 160# Bug #2368 Multibyte charsets do not check that incoming data is well-formed 161# 162create table t1 (s1 char(10) character set utf8); 163insert ignore into t1 values (0x41FF); 164select hex(s1) from t1; 165drop table t1; 166 167create table t1 (s1 varchar(10) character set utf8); 168insert ignore into t1 values (0x41FF); 169select hex(s1) from t1; 170drop table t1; 171 172create table t1 (s1 text character set utf8); 173insert ignore into t1 values (0x41FF); 174select hex(s1) from t1; 175drop table t1; 176 177# 178# Bug 2699 179# UTF8 breaks primary keys for cols > 333 characters 180# 181--error 1071 182create table t1 (a text character set utf8, primary key(a(371))); 183 184 185# 186# Bug 2959 187# UTF8 charset breaks joins with mixed column/string constant 188# 189CREATE TABLE t1 ( a varchar(10) ) CHARACTER SET utf8; 190INSERT INTO t1 VALUES ( 'test' ); 191SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a; 192SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = 'test' and b.a = 'test'; 193SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a and a.a = 'test'; 194DROP TABLE t1; 195 196create table t1 (a char(255) character set utf8); 197insert into t1 values('b'),('b'); 198select * from t1 where a = 'b'; 199select * from t1 where a = 'b' and a = 'b'; 200select * from t1 where a = 'b' and a != 'b'; 201drop table t1; 202 203# 204# Testing regexp 205# 206set names utf8; 207--source include/ctype_regex.inc 208--source include/ctype_regex_utf8.inc 209 210# 211# Bug #4555 212# ALTER TABLE crashes mysqld with enum column collated utf8_unicode_ci 213# 214CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8_unicode_ci); 215ALTER TABLE t1 ADD COLUMN b CHAR(20); 216DROP TABLE t1; 217 218# Customer Support Center issue # 3299 219# ENUM and SET multibyte fields computed their length wronly 220# when converted into a char field 221set names utf8; 222create table t1 (a enum('aaaa','проба') character set utf8); 223show create table t1; 224insert into t1 values ('проба'); 225select * from t1; 226create table t2 select ifnull(a,a) from t1; 227show create table t2; 228select * from t2; 229drop table t1; 230drop table t2; 231 232# 233# Bug 4521: unique key prefix interacts poorly with utf8 234# MYISAM: keys with prefix compression, case insensitive collation. 235# 236create table t1 (c varchar(30) character set utf8, unique(c(10))); 237insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); 238insert into t1 values ('aaaaaaaaaa'); 239--error ER_DUP_ENTRY 240insert into t1 values ('aaaaaaaaaaa'); 241--error ER_DUP_ENTRY 242insert into t1 values ('aaaaaaaaaaaa'); 243insert into t1 values (repeat('b',20)); 244select c c1 from t1 where c='1'; 245select c c2 from t1 where c='2'; 246select c c3 from t1 where c='3'; 247select c cx from t1 where c='x'; 248select c cy from t1 where c='y'; 249select c cz from t1 where c='z'; 250select c ca10 from t1 where c='aaaaaaaaaa'; 251select c cb20 from t1 where c=repeat('b',20); 252drop table t1; 253 254# 255# Bug 4521: unique key prefix interacts poorly with utf8 256# InnoDB: keys with prefix compression, case insensitive collation. 257# 258create table t1 (c varchar(30) character set utf8, unique(c(10))) engine=innodb; 259insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); 260insert into t1 values ('aaaaaaaaaa'); 261--error ER_DUP_ENTRY 262insert into t1 values ('aaaaaaaaaaa'); 263--error ER_DUP_ENTRY 264insert into t1 values ('aaaaaaaaaaaa'); 265insert into t1 values (repeat('b',20)); 266select c c1 from t1 where c='1'; 267select c c2 from t1 where c='2'; 268select c c3 from t1 where c='3'; 269select c cx from t1 where c='x'; 270select c cy from t1 where c='y'; 271select c cz from t1 where c='z'; 272select c ca10 from t1 where c='aaaaaaaaaa'; 273select c cb20 from t1 where c=repeat('b',20); 274drop table t1; 275# 276# Bug 4521: unique key prefix interacts poorly with utf8 277# MYISAM: fixed length keys, case insensitive collation 278# 279create table t1 (c char(3) character set utf8, unique (c(2))); 280insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); 281insert into t1 values ('a'); 282insert into t1 values ('aa'); 283--error ER_DUP_ENTRY 284insert into t1 values ('aaa'); 285insert into t1 values ('b'); 286insert into t1 values ('bb'); 287--error ER_DUP_ENTRY 288insert into t1 values ('bbb'); 289insert into t1 values ('а'); 290insert into t1 values ('аа'); 291--error ER_DUP_ENTRY 292insert into t1 values ('ааа'); 293insert into t1 values ('б'); 294insert into t1 values ('бб'); 295--error ER_DUP_ENTRY 296insert into t1 values ('ббб'); 297insert into t1 values ('ꪪ'); 298insert into t1 values ('ꪪꪪ'); 299--error ER_DUP_ENTRY 300insert into t1 values ('ꪪꪪꪪ'); 301drop table t1; 302# 303# Bug 4521: unique key prefix interacts poorly with utf8 304# InnoDB: fixed length keys, case insensitive collation 305# 306create table t1 (c char(3) character set utf8, unique (c(2))) engine=innodb; 307insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); 308insert into t1 values ('a'); 309insert into t1 values ('aa'); 310--error ER_DUP_ENTRY 311insert into t1 values ('aaa'); 312insert into t1 values ('b'); 313insert into t1 values ('bb'); 314--error ER_DUP_ENTRY 315insert into t1 values ('bbb'); 316insert into t1 values ('а'); 317insert into t1 values ('аа'); 318--error ER_DUP_ENTRY 319insert into t1 values ('ааа'); 320insert into t1 values ('б'); 321insert into t1 values ('бб'); 322--error ER_DUP_ENTRY 323insert into t1 values ('ббб'); 324insert into t1 values ('ꪪ'); 325insert into t1 values ('ꪪꪪ'); 326--error ER_DUP_ENTRY 327insert into t1 values ('ꪪꪪꪪ'); 328drop table t1; 329# 330# Bug 4531: unique key prefix interacts poorly with utf8 331# Check HEAP+HASH, case insensitive collation 332# 333create table t1 ( 334c char(10) character set utf8, 335unique key a using hash (c(1)) 336) engine=heap; 337show create table t1; 338insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 339--error ER_DUP_ENTRY 340insert into t1 values ('aa'); 341--error ER_DUP_ENTRY 342insert into t1 values ('aaa'); 343insert into t1 values ('б'); 344--error ER_DUP_ENTRY 345insert into t1 values ('бб'); 346--error ER_DUP_ENTRY 347insert into t1 values ('ббб'); 348select c as c_all from t1 order by c; 349select c as c_a from t1 where c='a'; 350select c as c_a from t1 where c='б'; 351drop table t1; 352 353# 354# Bug 4531: unique key prefix interacts poorly with utf8 355# Check HEAP+BTREE, case insensitive collation 356# 357create table t1 ( 358c char(10) character set utf8, 359unique key a using btree (c(1)) 360) engine=heap; 361show create table t1; 362insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 363--error ER_DUP_ENTRY 364insert into t1 values ('aa'); 365--error ER_DUP_ENTRY 366insert into t1 values ('aaa'); 367insert into t1 values ('б'); 368--error ER_DUP_ENTRY 369insert into t1 values ('бб'); 370--error ER_DUP_ENTRY 371insert into t1 values ('ббб'); 372select c as c_all from t1 order by c; 373select c as c_a from t1 where c='a'; 374select c as c_a from t1 where c='б'; 375drop table t1; 376 377# 378# Bug 4531: unique key prefix interacts poorly with utf8 379# Check BDB, case insensitive collation 380# 381create table t1 ( 382c char(10) character set utf8, 383unique key a (c(1)) 384) engine=innodb; 385insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 386--error ER_DUP_ENTRY 387insert into t1 values ('aa'); 388--error ER_DUP_ENTRY 389insert into t1 values ('aaa'); 390insert into t1 values ('б'); 391--error ER_DUP_ENTRY 392insert into t1 values ('бб'); 393--error ER_DUP_ENTRY 394insert into t1 values ('ббб'); 395select c as c_all from t1 order by c; 396select c as c_a from t1 where c='a'; 397select c as c_a from t1 where c='б'; 398drop table t1; 399 400# 401# Bug 4521: unique key prefix interacts poorly with utf8 402# MYISAM: keys with prefix compression, binary collation. 403# 404create table t1 (c varchar(30) character set utf8 collate utf8_bin, unique(c(10))); 405insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); 406insert into t1 values ('aaaaaaaaaa'); 407--error ER_DUP_ENTRY 408insert into t1 values ('aaaaaaaaaaa'); 409--error ER_DUP_ENTRY 410insert into t1 values ('aaaaaaaaaaaa'); 411insert into t1 values (repeat('b',20)); 412select c c1 from t1 where c='1'; 413select c c2 from t1 where c='2'; 414select c c3 from t1 where c='3'; 415select c cx from t1 where c='x'; 416select c cy from t1 where c='y'; 417select c cz from t1 where c='z'; 418select c ca10 from t1 where c='aaaaaaaaaa'; 419select c cb20 from t1 where c=repeat('b',20); 420drop table t1; 421 422# 423# Bug 4521: unique key prefix interacts poorly with utf8 424# MYISAM: fixed length keys, binary collation 425# 426create table t1 (c char(3) character set utf8 collate utf8_bin, unique (c(2))); 427insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); 428insert into t1 values ('a'); 429insert into t1 values ('aa'); 430--error ER_DUP_ENTRY 431insert into t1 values ('aaa'); 432insert into t1 values ('b'); 433insert into t1 values ('bb'); 434--error ER_DUP_ENTRY 435insert into t1 values ('bbb'); 436insert into t1 values ('а'); 437insert into t1 values ('аа'); 438--error ER_DUP_ENTRY 439insert into t1 values ('ааа'); 440insert into t1 values ('б'); 441insert into t1 values ('бб'); 442--error ER_DUP_ENTRY 443insert into t1 values ('ббб'); 444insert into t1 values ('ꪪ'); 445insert into t1 values ('ꪪꪪ'); 446--error ER_DUP_ENTRY 447insert into t1 values ('ꪪꪪꪪ'); 448drop table t1; 449 450# 451# Bug 4531: unique key prefix interacts poorly with utf8 452# Check HEAP+HASH, binary collation 453# 454create table t1 ( 455c char(10) character set utf8 collate utf8_bin, 456unique key a using hash (c(1)) 457) engine=heap; 458show create table t1; 459insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 460--error ER_DUP_ENTRY 461insert into t1 values ('aa'); 462--error ER_DUP_ENTRY 463insert into t1 values ('aaa'); 464insert into t1 values ('б'); 465--error ER_DUP_ENTRY 466insert into t1 values ('бб'); 467--error ER_DUP_ENTRY 468insert into t1 values ('ббб'); 469select c as c_all from t1 order by c; 470select c as c_a from t1 where c='a'; 471select c as c_a from t1 where c='б'; 472drop table t1; 473 474# 475# Bug 4531: unique key prefix interacts poorly with utf8 476# Check HEAP+BTREE, binary collation 477# 478create table t1 ( 479c char(10) character set utf8 collate utf8_bin, 480unique key a using btree (c(1)) 481) engine=heap; 482show create table t1; 483insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 484--error ER_DUP_ENTRY 485insert into t1 values ('aa'); 486--error ER_DUP_ENTRY 487insert into t1 values ('aaa'); 488insert into t1 values ('б'); 489--error ER_DUP_ENTRY 490insert into t1 values ('бб'); 491--error ER_DUP_ENTRY 492insert into t1 values ('ббб'); 493select c as c_all from t1 order by c; 494select c as c_a from t1 where c='a'; 495select c as c_a from t1 where c='б'; 496drop table t1; 497 498# 499# Bug 4531: unique key prefix interacts poorly with utf8 500# Check BDB, binary collation 501# 502create table t1 ( 503c char(10) character set utf8 collate utf8_bin, 504unique key a (c(1)) 505) engine=innodb; 506insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); 507--error ER_DUP_ENTRY 508insert into t1 values ('aa'); 509--error ER_DUP_ENTRY 510insert into t1 values ('aaa'); 511insert into t1 values ('б'); 512--error ER_DUP_ENTRY 513insert into t1 values ('бб'); 514--error ER_DUP_ENTRY 515insert into t1 values ('ббб'); 516select c as c_all from t1 order by c; 517select c as c_a from t1 where c='a'; 518select c as c_a from t1 where c='б'; 519drop table t1; 520 521 522# Bug#4594: column index make = failed for gbk, but like works 523# Check MYISAM 524# 525create table t1 ( 526 str varchar(255) character set utf8 not null, 527 key str (str(2)) 528) engine=myisam; 529INSERT INTO t1 VALUES ('str'); 530INSERT INTO t1 VALUES ('str2'); 531select * from t1 where str='str'; 532drop table t1; 533 534# Bug#4594: column index make = failed for gbk, but like works 535# Check InnoDB 536# 537create table t1 ( 538 str varchar(255) character set utf8 not null, 539 key str (str(2)) 540) engine=innodb; 541INSERT INTO t1 VALUES ('str'); 542INSERT INTO t1 VALUES ('str2'); 543select * from t1 where str='str'; 544drop table t1; 545 546# the same for HEAP+BTREE 547# 548 549create table t1 ( 550 str varchar(255) character set utf8 not null, 551 key str using btree (str(2)) 552) engine=heap; 553INSERT INTO t1 VALUES ('str'); 554INSERT INTO t1 VALUES ('str2'); 555select * from t1 where str='str'; 556drop table t1; 557 558# the same for HEAP+HASH 559# 560 561create table t1 ( 562 str varchar(255) character set utf8 not null, 563 key str using hash (str(2)) 564) engine=heap; 565INSERT INTO t1 VALUES ('str'); 566INSERT INTO t1 VALUES ('str2'); 567select * from t1 where str='str'; 568drop table t1; 569 570# the same for BDB 571# 572 573create table t1 ( 574 str varchar(255) character set utf8 not null, 575 key str (str(2)) 576) engine=innodb; 577INSERT INTO t1 VALUES ('str'); 578INSERT INTO t1 VALUES ('str2'); 579select * from t1 where str='str'; 580drop table t1; 581 582# 583# Bug #5397: Crash with varchar binary and LIKE 584# 585CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8; 586INSERT INTO t1 VALUES ('test'); 587SELECT a FROM t1 WHERE a LIKE '%te'; 588DROP TABLE t1; 589 590# 591# Bug #5723: length(<varchar utf8 field>) returns varying results 592# 593SET NAMES utf8; 594CREATE TABLE t1 ( 595 subject varchar(255) character set utf8 collate utf8_unicode_ci, 596 p varchar(15) character set utf8 597) ENGINE=InnoDB DEFAULT CHARSET=latin1; 598INSERT INTO t1 VALUES ('谷川俊二と申しますが、インターネット予約の会員登録をしましたところ、メールアドレスを間違えてしまい会員ＩＤが受け取ることが出来ませんでした。間違えアドレスはtani-shun@n.vodafone.ne.jpを書き込みました。どうすればよいですか？ その他、住所等は間違えありません。連絡ください。よろしくお願いします。m(__)m','040312-000057'); 599INSERT INTO t1 VALUES ('aaa','bbb'); 600SELECT length(subject) FROM t1; 601SELECT length(subject) FROM t1 ORDER BY 1; 602DROP TABLE t1; 603 604# 605# Bug #5832 SELECT doesn't return records in some cases 606# 607CREATE TABLE t1 ( 608 id int unsigned NOT NULL auto_increment, 609 list_id smallint unsigned NOT NULL, 610 term TEXT NOT NULL, 611 PRIMARY KEY(id), 612 INDEX(list_id, term(4)) 613) ENGINE=MYISAM CHARSET=utf8; 614INSERT INTO t1 SET list_id = 1, term = "letterc"; 615INSERT INTO t1 SET list_id = 1, term = "letterb"; 616INSERT INTO t1 SET list_id = 1, term = "lettera"; 617INSERT INTO t1 SET list_id = 1, term = "letterd"; 618SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc"); 619SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb"); 620SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera"); 621SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd"); 622DROP TABLE t1; 623 624 625# 626# Bug #6043 erratic searching for diacriticals in indexed MyISAM UTF-8 table 627# 628SET NAMES latin1; 629CREATE TABLE t1 ( 630 id int unsigned NOT NULL auto_increment, 631 list_id smallint unsigned NOT NULL, 632 term text NOT NULL, 633 PRIMARY KEY(id), 634 INDEX(list_id, term(19)) 635) ENGINE=MyISAM CHARSET=utf8; 636INSERT INTO t1 set list_id = 1, term = "test�test"; 637INSERT INTO t1 set list_id = 1, term = "testetest"; 638INSERT INTO t1 set list_id = 1, term = "test�test"; 639SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test"); 640SELECT id, term FROM t1 where (list_id = 1) AND (term = "testetest"); 641SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test"); 642DROP TABLE t1; 643 644# 645# Bug #6019 SELECT tries to use too short prefix index on utf8 data 646# 647set names utf8; 648create table t1 ( 649 a int primary key, 650 b varchar(6), 651 index b3(b(3)) 652) engine=innodb character set=utf8; 653insert into t1 values(1,'foo'),(2,'foobar'); 654select * from t1 where b like 'foob%'; 655alter table t1 engine=innodb; 656select * from t1 where b like 'foob%'; 657drop table t1; 658 659# 660# Test for calculate_interval_lengths() function 661# 662create table t1 ( 663 a enum('петя','вася','анюта') character set utf8 not null default 'анюта', 664 b set('петя','вася','анюта') character set utf8 not null default 'анюта' 665); 666create table t2 select concat(a,_utf8'') as a, concat(b,_utf8'')as b from t1; 667show create table t2; 668drop table t2; 669drop table t1; 670 671# 672# Bug #6787 LIKE not working properly with _ and utf8 data 673# 674select 'c' like '\_' as want0; 675 676# 677# SUBSTR with negative offset didn't work with multi-byte strings 678# 679SELECT SUBSTR('вася',-2); 680 681 682# 683# Bug #7730 Server crash using soundex on an utf8 table 684# 685create table t1 (id integer, a varchar(100) character set utf8 collate utf8_unicode_ci); 686insert into t1 values (1, 'Test'); 687select * from t1 where soundex(a) = soundex('Test'); 688select * from t1 where soundex(a) = soundex('TEST'); 689select * from t1 where soundex(a) = soundex('test'); 690drop table t1; 691 692# 693# Bug#22638 SOUNDEX broken for international characters 694# 695select soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB); 696select hex(soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB)); 697select soundex(_utf8 0xD091D092D093); 698select hex(soundex(_utf8 0xD091D092D093)); 699 700 701SET collation_connection='utf8_general_ci'; 702-- source include/ctype_filesort.inc 703-- source include/ctype_like_escape.inc 704-- source include/ctype_german.inc 705-- source include/ctype_str_to_date.inc 706SET collation_connection='utf8_bin'; 707-- source include/ctype_filesort.inc 708-- source include/ctype_like_escape.inc 709 710# 711# Bug #7874 CONCAT() gives wrong results mixing 712# latin1 field and utf8 string literals 713# 714CREATE TABLE t1 ( 715 user varchar(255) NOT NULL default '' 716) ENGINE=MyISAM DEFAULT CHARSET=latin1; 717INSERT INTO t1 VALUES ('one'),('two'); 718SELECT CHARSET('a'); 719SELECT user, CONCAT('<', user, '>') AS c FROM t1; 720DROP TABLE t1; 721 722# 723# Bug#8785 724# the same problem with the above, but with nested CONCATs 725# 726create table t1 (f1 varchar(1) not null) default charset utf8; 727insert into t1 values (''), (''); 728select concat(concat(_latin1'->',f1),_latin1'<-') from t1; 729drop table t1; 730 731# 732# Bug#8385: utf8_general_ci treats Cyrillic letters I and SHORT I as the same 733# 734select convert(_koi8r'�' using utf8) < convert(_koi8r'�' using utf8); 735 736# 737# Bugs#5980: NULL requires a characterset in a union 738# 739set names latin1; 740create table t1 (a varchar(10)) character set utf8; 741insert into t1 values ('test'); 742select ifnull(a,'') from t1; 743drop table t1; 744select repeat(_utf8'+',3) as h union select NULL; 745select ifnull(NULL, _utf8'string'); 746 747# 748# Bug#9509 Optimizer: wrong result after AND with comparisons 749# 750set names utf8; 751create table t1 (s1 char(5) character set utf8 collate utf8_lithuanian_ci); 752insert into t1 values ('I'),('K'),('Y'); 753select * from t1 where s1 < 'K' and s1 = 'Y'; 754select * from t1 where 'K' > s1 and s1 = 'Y'; 755drop table t1; 756 757create table t1 (s1 char(5) character set utf8 collate utf8_czech_ci); 758insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i'); 759select * from t1 where s1 > 'd' and s1 = 'CH'; 760select * from t1 where 'd' < s1 and s1 = 'CH'; 761select * from t1 where s1 = 'cH' and s1 <> 'ch'; 762select * from t1 where 'cH' = s1 and s1 <> 'ch'; 763drop table t1; 764 765# 766# Bug#10714: Inserting double value into utf8 column crashes server 767# 768create table t1 (a varchar(255)) default character set utf8; 769insert into t1 values (1.0); 770drop table t1; 771 772# 773# Bug#10253 compound index length and utf8 char set 774# produces invalid query results 775# 776create table t1 ( 777 id int not null, 778 city varchar(20) not null, 779 key (city(7),id) 780) character set=utf8; 781insert into t1 values (1,'Durban North'); 782insert into t1 values (2,'Durban'); 783select * from t1 where city = 'Durban'; 784select * from t1 where city = 'Durban '; 785drop table t1; 786 787# 788# Bug #11819 CREATE TABLE with a SET DEFAULT 0 and UTF8 crashes server. 789# 790--error 1067 791create table t1 (x set('A', 'B') default 0) character set utf8; 792--error 1067 793create table t1 (x enum('A', 'B') default 0) character set utf8; 794 795 796# 797# Test for bug #11167: join for utf8 varchar value longer than 255 bytes 798# 799 800SET NAMES UTF8; 801 802CREATE TABLE t1 ( 803 `id` int(20) NOT NULL auto_increment, 804 `country` varchar(100) NOT NULL default '', 805 `shortcode` varchar(100) NOT NULL default '', 806 `operator` varchar(100) NOT NULL default '', 807 `momid` varchar(30) NOT NULL default '', 808 `keyword` varchar(160) NOT NULL default '', 809 `content` varchar(160) NOT NULL default '', 810 `second_token` varchar(160) default NULL, 811 `gateway_id` int(11) NOT NULL default '0', 812 `created` datetime NOT NULL default '0000-00-00 00:00:00', 813 `msisdn` varchar(15) NOT NULL default '', 814 PRIMARY KEY (`id`), 815 UNIQUE KEY `MSCCSPK_20030521130957121` (`momid`), 816 KEY `IX_mobile_originated_message_keyword` (`keyword`), 817 KEY `IX_mobile_originated_message_created` (`created`), 818 KEY `IX_mobile_originated_message_support` (`msisdn`,`momid`,`keyword`,`gateway_id`,`created`) 819) ENGINE=MyISAM DEFAULT CHARSET=utf8; 820 821INSERT INTO t1 VALUES 822(1,'blah','464','aaa','fkc1c9ilc20x0hgae7lx6j09','ERR','ERR Имри.Афимим.Аеимимримдмримрмрирор имримримримр имридм ирбднримрфмририримрфмфмим.Ад.Д имдимримрад.Адимримримрмдиримримримр м.Дадимфшьмримд им.Адимимрн имадми','ИМРИ.АФИМИМ.АЕИМИМРИМДМРИМРМРИРОР',3,'2005-06-01 17:30:43','1234567890'), 823(2,'blah','464','aaa','haxpl2ilc20x00bj4tt2m5ti','11','11 g','G',3,'2005-06-02 22:43:10','1234567890'); 824 825CREATE TABLE t2 ( 826 `msisdn` varchar(15) NOT NULL default '', 827 `operator_id` int(11) NOT NULL default '0', 828 `created` datetime NOT NULL default '0000-00-00 00:00:00', 829 UNIQUE KEY `PK_user` (`msisdn`) 830) ENGINE=InnoDB DEFAULT CHARSET=utf8; 831 832INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25'); 833 834SELECT content, t2.msisdn FROM t1, t2 WHERE t1.msisdn = '1234567890'; 835 836DROP TABLE t1,t2; 837 838# 839# Bug#11591: CHAR column with utf8 does not work properly 840# (more chars than expected) 841# 842create table t1 (a char(20) character set utf8); 843insert into t1 values ('123456'),('андрей'); 844SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 845alter table t1 modify a char(2) character set utf8; 846select char_length(a), length(a), a from t1 order by a; 847drop table t1; 848 849# 850# Bugs#12611 851# ESCAPE + LIKE do not work when the escape char is a multibyte one 852# 853set names utf8; 854select 'andre%' like 'andreñ%' escape 'ñ'; 855 856# 857# Bugs#11754: SET NAMES utf8 followed by SELECT "A\\" LIKE "A\\" returns 0 858# 859set names utf8; 860select 'a\\' like 'a\\'; 861select 'aa\\' like 'a%\\'; 862 863create table t1 (a char(10), key(a)) character set utf8; 864insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); 865select * from t1 where a like "abc%"; 866select * from t1 where a like concat("abc","%"); 867select * from t1 where a like "ABC%"; 868select * from t1 where a like "test%"; 869select * from t1 where a like "te_t"; 870select * from t1 where a like "%a%"; 871select * from t1 where a like "%abcd%"; 872select * from t1 where a like "%abc\d%"; 873drop table t1; 874 875 876# 877# Bug#9557 MyISAM utf8 table crash 878# 879CREATE TABLE t1 ( 880 a varchar(255) NOT NULL default '', 881 KEY a (a) 882) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci; 883insert into t1 values (_utf8 0xe880bd); 884insert into t1 values (_utf8 0x5b); 885select hex(a) from t1; 886drop table t1; 887 888# 889# Bug#13751 find_in_set: Illegal mix of collations 890# 891set names 'latin1'; 892create table t1 (a varchar(255)) default charset=utf8; 893select * from t1 where find_in_set('-1', a); 894drop table t1; 895 896# 897# Bug#13233: select distinct char(column) fails with utf8 898# 899create table t1 (a int); 900insert into t1 values (48),(49),(50); 901set names utf8; 902select distinct char(a) from t1; 903drop table t1; 904 905# 906# Bug#15581: COALESCE function truncates mutli-byte TINYTEXT values 907# 908CREATE TABLE t1 (t TINYTEXT CHARACTER SET utf8); 909INSERT INTO t1 VALUES(REPEAT('a', 100)); 910CREATE TEMPORARY TABLE t2 SELECT COALESCE(t) AS bug FROM t1; 911SELECT LENGTH(bug) FROM t2; 912DROP TABLE t2; 913DROP TABLE t1; 914 915# 916# Bug#17313: N'xxx' and _utf8'xxx' are not equivalent 917# 918CREATE TABLE t1 (item varchar(255)) default character set utf8; 919INSERT INTO t1 VALUES (N'\\'); 920INSERT INTO t1 VALUES (_utf8'\\'); 921INSERT INTO t1 VALUES (N'Cote d\'Ivoire'); 922INSERT INTO t1 VALUES (_utf8'Cote d\'Ivoire'); 923SELECT item FROM t1 ORDER BY item; 924DROP TABLE t1; 925 926# 927# Bug#17705: Corruption of compressed index when index length changes between 928# 254 and 256 929# 930 931SET NAMES utf8; 932DROP TABLE IF EXISTS t1; 933CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8; 934INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); 935INSERT INTO t1 VALUES('uu'); 936check table t1; 937INSERT INTO t1 VALUES('uU'); 938check table t1; 939INSERT INTO t1 VALUES('uu'); 940check table t1; 941INSERT INTO t1 VALUES('uuABC'); 942check table t1; 943INSERT INTO t1 VALUES('UuABC'); 944check table t1; 945INSERT INTO t1 VALUES('uuABC'); 946check table t1; 947alter table t1 add b int; 948INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1); 949INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',2); 950delete from t1 where b=1; 951INSERT INTO t1 VALUES('UUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1); 952check table t1; 953INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3); 954INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',4); 955delete from t1 where b=3; 956INSERT INTO t1 VALUES('uUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3); 957check table t1; 958drop table t1; 959 960# 961# Bug#20471 LIKE search fails with indexed utf8 char column 962# 963set names utf8; 964create table t1 (s1 char(5) character set utf8); 965insert into t1 values 966('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); 967create index it1 on t1 (s1); 968select s1 as before_delete_general_ci from t1 where s1 like 'ペテ%'; 969delete from t1 where s1 = 'Y'; 970select s1 as after_delete_general_ci from t1 where s1 like 'ペテ%'; 971drop table t1; 972 973set names utf8; 974create table t1 (s1 char(5) character set utf8 collate utf8_unicode_ci); 975insert into t1 values 976('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); 977create index it1 on t1 (s1); 978select s1 as before_delete_unicode_ci from t1 where s1 like 'ペテ%'; 979delete from t1 where s1 = 'Y'; 980select s1 as after_delete_unicode_ci from t1 where s1 like 'ペテ%'; 981drop table t1; 982 983set names utf8; 984create table t1 (s1 char(5) character set utf8 collate utf8_bin); 985insert into t1 values 986('a'),('b'),(null),('ペテルグル'),('ü'),('Y'); 987create index it1 on t1 (s1); 988select s1 as before_delete_bin from t1 where s1 like 'ペテ%'; 989delete from t1 where s1 = 'Y'; 990select s1 as after_delete_bin from t1 where s1 like 'ペテ%'; 991drop table t1; 992 993# additional tests from duplicate bug#20744 MySQL return no result 994 995set names utf8; 996create table t1 (a varchar(30) not null primary key) 997engine=innodb default character set utf8 collate utf8_general_ci; 998insert into t1 values ('あいうえおかきくけこさしすせそ'); 999insert into t1 values ('さしすせそかきくけこあいうえお'); 1000select a as gci1 from t1 where a like 'さしすせそかきくけこあいうえお%'; 1001select a as gci2 from t1 where a like 'あいうえおかきくけこさしすせそ'; 1002drop table t1; 1003 1004set names utf8; 1005create table t1 (a varchar(30) not null primary key) 1006engine=innodb default character set utf8 collate utf8_unicode_ci; 1007insert into t1 values ('あいうえおかきくけこさしすせそ'); 1008insert into t1 values ('さしすせそかきくけこあいうえお'); 1009select a as uci1 from t1 where a like 'さしすせそかきくけこあいうえお%'; 1010select a as uci2 from t1 where a like 'あいうえおかきくけこさしすせそ'; 1011drop table t1; 1012 1013set names utf8; 1014create table t1 (a varchar(30) not null primary key) 1015engine=innodb default character set utf8 collate utf8_bin; 1016insert into t1 values ('あいうえおかきくけこさしすせそ'); 1017insert into t1 values ('さしすせそかきくけこあいうえお'); 1018select a as bin1 from t1 where a like 'さしすせそかきくけこあいうえお%'; 1019select a as bin2 from t1 where a like 'あいうえおかきくけこさしすせそ'; 1020drop table t1; 1021 1022 1023 1024# 1025# Bug#14896: Comparison with a key in a partial index over mb chararacter field 1026# 1027 1028SET NAMES utf8; 1029CREATE TABLE t1 (id int PRIMARY KEY, 1030 a varchar(16) collate utf8_unicode_ci NOT NULL default '', 1031 b int, 1032 f varchar(128) default 'XXX', 1033 INDEX (a(4)) 1034) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; 1035INSERT INTO t1(id, a, b) VALUES 1036 (1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30), 1037 (4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40), 1038 (7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50), 1039 (10, 'eeeee', 40), (11, 'bbbbbb', 60); 1040 1041SELECT id, a, b FROM t1; 1042 1043SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb'; 1044 1045SELECT id, a FROM t1 WHERE a='bbbbbb'; 1046SELECT id, a FROM t1 WHERE a='bbbbbb' ORDER BY b; 1047 1048DROP TABLE t1; 1049 1050# 1051# Bug#16674: LIKE predicate for a utf8 character set column 1052# 1053 1054SET NAMES utf8; 1055 1056CREATE TABLE t1 ( 1057 a CHAR(13) DEFAULT '', 1058 INDEX(a) 1059) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; 1060INSERT INTO t1 VALUES 1061 ('Käli Käli 2-4'), ('Käli Käli 2-4'), 1062 ('Käli Käli 2+4'), ('Käli Käli 2+4'), 1063 ('Käli Käli 2-6'), ('Käli Käli 2-6'); 1064INSERT INTO t1 SELECT * FROM t1; 1065 1066CREATE TABLE t2 ( 1067 a CHAR(13) DEFAULT '', 1068 INDEX(a) 1069) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 1070 1071INSERT INTO t2 VALUES 1072 ('Kali Kali 2-4'), ('Kali Kali 2-4'), 1073 ('Kali Kali 2+4'), ('Kali Kali 2+4'), 1074 ('Kali Kali 2-6'), ('Kali Kali 2-6'); 1075INSERT INTO t2 SELECT * FROM t2; 1076 1077SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4'; 1078SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4'; 1079 1080EXPLAIN SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4'; 1081EXPLAIN SELECT a FROM t1 WHERE a = 'Käli Käli 2+4'; 1082EXPLAIN SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4'; 1083EXPLAIN SELECT a FROM t2 WHERE a = 'Kali Kali 2+4'; 1084 1085DROP TABLE t1,t2; 1086 1087CREATE TABLE t1 ( 1088 a char(255) DEFAULT '', 1089 KEY(a(10)) 1090) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; 1091INSERT INTO t1 VALUES ('Käli Käli 2-4'); 1092SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%'; 1093INSERT INTO t1 VALUES ('Käli Käli 2-4'); 1094SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%'; 1095DROP TABLE t1; 1096 1097CREATE TABLE t1 ( 1098 a char(255) DEFAULT '' 1099) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci; 1100INSERT INTO t1 VALUES ('Käli Käli 2-4'); 1101INSERT INTO t1 VALUES ('Käli Käli 2-4'); 1102SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%'; 1103ALTER TABLE t1 ADD KEY (a(10)); 1104SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%'; 1105DROP TABLE t1; 1106 1107# 1108# Bug#18359: LIKE predicate for a 'utf8' text column with a partial index 1109# (see bug #16674 as well) 1110# 1111 1112SET NAMES latin2; 1113 1114CREATE TABLE t1 ( 1115 id int(11) NOT NULL default '0', 1116 tid int(11) NOT NULL default '0', 1117 val text NOT NULL, 1118 INDEX idx(tid, val(10)) 1119) ENGINE=MyISAM DEFAULT CHARSET=utf8; 1120 1121INSERT INTO t1 VALUES 1122 (40988,72,'VOLN� ADSL'),(41009,72,'VOLN� ADSL'), 1123 (41032,72,'VOLN� ADSL'),(41038,72,'VOLN� ADSL'), 1124 (41063,72,'VOLN� ADSL'),(41537,72,'VOLN� ADSL Office'), 1125 (42141,72,'VOLN� ADSL'),(42565,72,'VOLN� ADSL Combi'), 1126 (42749,72,'VOLN� ADSL'),(44205,72,'VOLN� ADSL'); 1127 1128SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNY ADSL'; 1129SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL'; 1130SELECT * FROM t1 WHERE tid=72 and val LIKE '%VOLN� ADSL'; 1131 1132ALTER TABLE t1 DROP KEY idx; 1133ALTER TABLE t1 ADD KEY idx (tid,val(11)); 1134 1135SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL'; 1136 1137DROP TABLE t1; 1138 1139# 1140# Bug 20709: problem with utf8 fields in temporary tables 1141# 1142 1143create table t1(a char(200) collate utf8_unicode_ci NOT NULL default '') 1144 default charset=utf8 collate=utf8_unicode_ci; 1145insert into t1 values (unhex('65')), (unhex('C3A9')), (unhex('65')); 1146-- disable_query_log 1147-- disable_result_log 1148analyze table t1; 1149-- enable_result_log 1150-- enable_query_log 1151explain select distinct a from t1; 1152select distinct a from t1; 1153explain select a from t1 group by a; 1154select a from t1 group by a; 1155drop table t1; 1156 1157# 1158# Bug #20204: "order by" changes the results returned 1159# 1160 1161create table t1(a char(10)) default charset utf8; 1162insert into t1 values ('123'), ('456'); 1163-- disable_query_log 1164-- disable_result_log 1165analyze table t1; 1166-- enable_result_log 1167-- enable_query_log 1168explain 1169 select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; 1170select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; 1171drop table t1; 1172 1173# 1174# Bug #34349: Passing invalid parameter to CHAR() in an ORDER BY causes 1175# MySQL to hang 1176# 1177 1178SET CHARACTER SET utf8; 1179SHOW VARIABLES LIKE 'character\_set\_%'; 1180CREATE DATABASE crashtest DEFAULT CHARACTER SET utf8 COLLATE utf8_bin; 1181USE crashtest; 1182CREATE TABLE crashtest (crash char(10)) DEFAULT CHARSET=utf8; 1183INSERT INTO crashtest VALUES ('35'), ('36'), ('37'); 1184SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8); 1185INSERT INTO crashtest VALUES ('-1000'); 1186-- disable_query_log 1187-- disable_result_log 1188ANALYZE TABLE crashtest; 1189-- enable_result_log 1190-- enable_query_log 1191EXPLAIN SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8); 1192SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8); 1193DROP TABLE crashtest; 1194DROP DATABASE crashtest; 1195USE test; 1196SET CHARACTER SET default; 1197 1198# End of 4.1 tests 1199 1200# 1201# Test for bug #11484: wrong results for a DISTINCT varchar column in uft8. 1202# 1203 1204CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8; 1205INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa'); 1206 1207SELECT id FROM t1; 1208SELECT DISTINCT id FROM t1; 1209SELECT DISTINCT id FROM t1 ORDER BY id; 1210 1211DROP TABLE t1; 1212 1213# 1214# Bug#20095 Changing length of VARCHAR field with UTF8 1215# collation does not truncate values 1216# 1217create table t1 ( 1218 a varchar(26) not null 1219) default character set utf8; 1220insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz'); 1221select * from t1; 1222# varchar to varchar 1223SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1224alter table t1 change a a varchar(20) character set utf8 not null; 1225select * from t1; 1226# varchar to char 1227SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1228alter table t1 change a a char(15) character set utf8 not null; 1229select * from t1; 1230# char to char 1231SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1232alter table t1 change a a char(10) character set utf8 not null; 1233select * from t1; 1234# char to varchar 1235SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1236alter table t1 change a a varchar(5) character set utf8 not null; 1237select * from t1; 1238drop table t1; 1239 1240# 1241# Check that do_varstring2_mb produces a warning 1242# 1243create table t1 ( 1244 a varchar(4000) not null 1245) default character set utf8; 1246insert into t1 values (repeat('a',4000)); 1247SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1248alter table t1 change a a varchar(3000) character set utf8 not null; 1249select length(a) from t1; 1250drop table t1; 1251 1252 1253# 1254# Bug#10504: Character set does not support traditional mode 1255# Bug#14146: CHAR(...USING ...) and CONVERT(CHAR(...) USING...) 1256# produce different results 1257# 1258set names utf8; 1259# correct value 1260select hex(char(1 using utf8)); 1261select char(0xd1,0x8f using utf8); 1262select char(0xd18f using utf8); 1263select char(53647 using utf8); 1264# incorrect value: return with warning 1265select char(0xff,0x8f using utf8); 1266select convert(char(0xff,0x8f) using utf8); 1267# incorrect value in strict mode: return NULL with "Error" level warning 1268set sql_mode=traditional; 1269select char(0xff,0x8f using utf8); 1270select char(195 using utf8); 1271select char(196 using utf8); 1272select char(2557 using utf8); 1273select convert(char(0xff,0x8f) using utf8); 1274 1275# 1276# Check convert + char + using 1277# 1278select hex(convert(char(2557 using latin1) using utf8)); 1279 1280# 1281# char() without USING returns "binary" by default, any argument is ok 1282# 1283select hex(char(195)); 1284select hex(char(196)); 1285select hex(char(2557)); 1286 1287 1288 1289# 1290# Bug#12891: UNION doesn't return DISTINCT result for multi-byte characters 1291# 1292set names utf8; 1293create table t1 (a char(1)) default character set utf8; 1294create table t2 (a char(1)) default character set utf8; 1295insert into t1 values('a'),('a'),(0xE38182),(0xE38182); 1296insert into t1 values('i'),('i'),(0xE38184),(0xE38184); 1297select * from t1 union distinct select * from t2; 1298drop table t1,t2; 1299 1300 1301# 1302# Bug#12371: executing prepared statement fails (illegal mix of collations) 1303# 1304set names utf8; 1305create table t1 (a char(10), b varchar(10)); 1306insert into t1 values ('bar','kostja'); 1307insert into t1 values ('kostja','bar'); 1308prepare my_stmt from "select * from t1 where a=?"; 1309set @a:='bar'; 1310execute my_stmt using @a; 1311set @a:='kostja'; 1312execute my_stmt using @a; 1313set @a:=null; 1314execute my_stmt using @a; 1315drop table if exists t1; 1316 1317 1318# 1319# Bug#21505 Create view - illegal mix of collation for operation 'UNION' 1320# 1321--disable_warnings 1322drop table if exists t1; 1323drop view if exists v1, v2; 1324--enable_warnings 1325set names utf8; 1326create table t1(col1 varchar(12) character set utf8 collate utf8_unicode_ci); 1327insert into t1 values('t1_val'); 1328create view v1 as select 'v1_val' as col1; 1329select coercibility(col1), collation(col1) from v1; 1330create view v2 as select col1 from v1 union select col1 from t1; 1331select coercibility(col1), collation(col1)from v2; 1332drop view v1, v2; 1333create view v1 as select 'v1_val' collate utf8_swedish_ci as col1; 1334select coercibility(col1), collation(col1) from v1; 1335create view v2 as select col1 from v1 union select col1 from t1; 1336select coercibility(col1), collation(col1) from v2; 1337drop view v1, v2; 1338drop table t1; 1339 1340# 1341# Check conversion of NCHAR strings to subset (e.g. latin1). 1342# Conversion is possible if string repertoire is ASCII. 1343# Conversion is not possible if the string have extended characters 1344# 1345set names utf8; 1346create table t1 (a varchar(10) character set latin1, b int); 1347insert into t1 values ('a',1); 1348select concat(a, if(b>10, N'x', N'y')) from t1; 1349--error 1267 1350select concat(a, if(b>10, N'æ', N'ß')) from t1; 1351drop table t1; 1352 1353# Conversion tests for character set introducers 1354set names utf8; 1355create table t1 (a varchar(10) character set latin1, b int); 1356insert into t1 values ('a',1); 1357select concat(a, if(b>10, _utf8'x', _utf8'y')) from t1; 1358--error 1267 1359select concat(a, if(b>10, _utf8'æ', _utf8'ß')) from t1; 1360drop table t1; 1361 1362# Conversion tests for introducer + HEX string 1363set names utf8; 1364create table t1 (a varchar(10) character set latin1, b int); 1365insert into t1 values ('a',1); 1366select concat(a, if(b>10, _utf8 0x78, _utf8 0x79)) from t1; 1367--error 1267 1368select concat(a, if(b>10, _utf8 0xC3A6, _utf8 0xC3AF)) from t1; 1369drop table t1; 1370 1371# Conversion tests for "text_literal TEXT_STRING_literal" syntax structure 1372set names utf8; 1373create table t1 (a varchar(10) character set latin1, b int); 1374insert into t1 values ('a',1); 1375select concat(a, if(b>10, 'x' 'x', 'y' 'y')) from t1; 1376--error 1267 1377select concat(a, if(b>10, 'x' 'æ', 'y' 'ß')) from t1; 1378drop table t1; 1379 1380# 1381# Bug#19960: Inconsistent results when joining 1382# InnoDB tables using partial UTF8 indexes 1383# 1384 1385CREATE TABLE t1 ( 1386 colA int(11) NOT NULL, 1387 colB varchar(255) character set utf8 NOT NULL, 1388 PRIMARY KEY (colA) 1389) ENGINE=InnoDB DEFAULT CHARSET=latin1; 1390INSERT INTO t1 (colA, colB) VALUES (1, 'foo'), (2, 'foo bar'); 1391CREATE TABLE t2 ( 1392 colA int(11) NOT NULL, 1393 colB varchar(255) character set utf8 NOT NULL, 1394 KEY bad (colA,colB(3)) 1395) ENGINE=InnoDB DEFAULT CHARSET=latin1; 1396INSERT INTO t2 (colA, colB) VALUES (1, 'foo'),(2, 'foo bar'); 1397SELECT * FROM t1 JOIN t2 ON t1.colA=t2.colA AND t1.colB=t2.colB 1398WHERE t1.colA < 3; 1399DROP TABLE t1, t2; 1400 1401# 1402# Bug#29205: truncation of UTF8 values when the UNION statement 1403# forces collation to the binary charset 1404# 1405 1406SELECT 'н1234567890' UNION SELECT _binary '1'; 1407SELECT 'н1234567890' UNION SELECT 1; 1408 1409SELECT '1' UNION SELECT 'н1234567890'; 1410SELECT 1 UNION SELECT 'н1234567890'; 1411 1412CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8; 1413CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT); 1414 1415INSERT INTO t1 (c) VALUES ('н1234567890'); 1416INSERT INTO t2 (b, i) VALUES ('1', 1); 1417 1418SELECT c FROM t1 UNION SELECT b FROM t2; 1419SELECT c FROM t1 UNION SELECT i FROM t2; 1420 1421SELECT b FROM t2 UNION SELECT c FROM t1; 1422SELECT i FROM t2 UNION SELECT c FROM t1; 1423 1424DROP TABLE t1, t2; 1425 1426# 1427# Bug#30982: CHAR(..USING..) can return a not-well-formed string 1428# Bug #30986: Character set introducer followed by a HEX string can return bad result 1429# 1430set sql_mode=traditional; 1431select hex(char(0xFF using utf8)); 1432select hex(convert(0xFF using utf8)); 1433--error ER_INVALID_CHARACTER_STRING 1434select hex(_utf8 0x616263FF); 1435--error ER_INVALID_CHARACTER_STRING 1436select hex(_utf8 X'616263FF'); 1437--error ER_INVALID_CHARACTER_STRING 1438select hex(_utf8 B'001111111111'); 1439--error ER_INVALID_CHARACTER_STRING 1440select (_utf8 X'616263FF'); 1441set sql_mode=default; 1442select hex(char(0xFF using utf8)); 1443select hex(convert(0xFF using utf8)); 1444--error ER_INVALID_CHARACTER_STRING 1445select hex(_utf8 0x616263FF); 1446--error ER_INVALID_CHARACTER_STRING 1447select hex(_utf8 X'616263FF'); 1448--error ER_INVALID_CHARACTER_STRING 1449select hex(_utf8 B'001111111111'); 1450--error ER_INVALID_CHARACTER_STRING 1451select (_utf8 X'616263FF'); 1452 1453--echo # 1454--echo # Bug#44131 Binary-mode "order by" returns records in incorrect order for UTF-8 strings 1455--echo # 1456CREATE TABLE t1 (id int not null primary key, name varchar(10)) character set utf8; 1457INSERT INTO t1 VALUES 1458(2,'一二三01'),(3,'一二三09'),(4,'一二三02'),(5,'一二三08'), 1459(6,'一二三11'),(7,'一二三91'),(8,'一二三21'),(9,'一二三81'); 1460SELECT * FROM t1 ORDER BY BINARY(name); 1461DROP TABLE t1; 1462 1463# 1464# Bug #36772: When using UTF8, CONVERT with GROUP BY returns truncated results 1465# 1466CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL); 1467INSERT INTO t1 VALUES (70000, 1092), (70001, 1085), (70002, 1065); 1468SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; 1469SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1; 1470ALTER TABLE t1 ADD UNIQUE (b); 1471SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; 1472DROP INDEX b ON t1; 1473SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; 1474ALTER TABLE t1 ADD INDEX (b); 1475SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) from t1 GROUP BY b; 1476DROP TABLE t1; 1477 1478--echo End of 5.0 tests 1479 1480 1481# 1482# Bug #57272: crash in rpad() when using utf8 1483# 1484SELECT LENGTH(RPAD(0.0115E88, 61297, _utf8'яэюя')); 1485SELECT LENGTH(RPAD(0.0115E88, 61297, _utf8'йцуя')); 1486SELECT HEX(RPAD(0x20, 2, _utf8 0xD18F)); 1487SELECT HEX(RPAD(0x20, 4, _utf8 0xD18F)); 1488SELECT HEX(LPAD(0x20, 2, _utf8 0xD18F)); 1489SELECT HEX(LPAD(0x20, 4, _utf8 0xD18F)); 1490 1491SELECT HEX(RPAD(_utf8 0xD18F, 3, 0x20)); 1492SELECT HEX(LPAD(_utf8 0xD18F, 3, 0x20)); 1493 1494SELECT HEX(INSERT(_utf8 0xD18F, 2, 1, 0x20)); 1495SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20)); 1496 1497--echo # 1498--echo # Bug#11752408 - 43593: DUMP/BACKUP/RESTORE/UPGRADE TOOLS FAILS BECAUSE OF UTF8_GENERAL_CI 1499--echo # 1500CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci); 1501INSERT INTO t1 VALUES ('a'),('r'),('s'),(_latin1 0xDF),(_latin1 0xF7),('t'),('z'); 1502SELECT * FROM t1 ORDER BY a; 1503SELECT a, COUNT(*) FROM t1 GROUP BY a; 1504DROP TABLE t1; 1505 1506--echo End of 5.1 tests 1507 1508 1509--echo Start of 5.4 tests 1510 1511# 1512# WL#1213: utf8mb3 is an alias for utf8 1513# 1514SET NAMES utf8mb3; 1515SHOW VARIABLES LIKE 'character_set_results%'; 1516CREATE TABLE t1 (a CHAR CHARACTER SET utf8mb3 COLLATE utf8mb3_bin); 1517SHOW CREATE TABLE t1; 1518DROP TABLE t1; 1519SELECT _utf8mb3'test'; 1520 1521# 1522# Bug#26180: Can't add columns to tables created with utf8 text indexes 1523# 1524CREATE TABLE t1 ( 1525 clipid INT NOT NULL, 1526 Tape TINYTEXT, 1527 PRIMARY KEY (clipid), 1528 KEY tape(Tape(255)) 1529) CHARACTER SET=utf8; 1530ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid; 1531SHOW CREATE TABLE t1; 1532DROP TABLE t1; 1533 1534# 1535# Bug#26474: Add Sinhala script (Sri Lanka) collation to MySQL 1536# 1537--disable_warnings 1538DROP TABLE IF EXISTS t1; 1539--enable_warnings 1540CREATE TABLE t1 ( 1541 predicted_order int NOT NULL, 1542 utf8_encoding VARCHAR(10) NOT NULL 1543) CHARACTER SET utf8; 1544INSERT 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'); 1545SELECT predicted_order, hex(utf8_encoding) FROM t1 ORDER BY utf8_encoding COLLATE utf8_sinhala_ci; 1546DROP TABLE t1; 1547# 1548# Postfix for Bug#26474 1549# 1550SET NAMES utf8 COLLATE utf8_sinhala_ci; 1551CREATE TABLE t1 (s1 VARCHAR(10) COLLATE utf8_sinhala_ci); 1552INSERT INTO t1 VALUES ('a'),('ae'),('af'); 1553SELECT s1,hex(s1) FROM t1 ORDER BY s1; 1554SELECT * FROM t1 ORDER BY s1; 1555DROP TABLE t1; 1556 1557--echo End of 5.4 tests 1558 1559--echo # 1560--echo # Start of 5.5 tests 1561--echo # 1562 1563--echo # 1564--echo # Bug#52520 Difference in tinytext utf column metadata 1565--echo # 1566CREATE TABLE t1 ( 1567 s1 TINYTEXT CHARACTER SET utf8, 1568 s2 TEXT CHARACTER SET utf8, 1569 s3 MEDIUMTEXT CHARACTER SET utf8, 1570 s4 LONGTEXT CHARACTER SET utf8 1571); 1572--enable_metadata 1573SET NAMES utf8, @@character_set_results=NULL; 1574SELECT *, HEX(s1) FROM t1; 1575SET NAMES latin1; 1576SELECT *, HEX(s1) FROM t1; 1577SET NAMES utf8; 1578SELECT *, HEX(s1) FROM t1; 1579--disable_metadata 1580CREATE TABLE t2 AS SELECT CONCAT(s1) FROM t1; 1581SHOW CREATE TABLE t2; 1582DROP TABLE t1, t2; 1583 1584 1585SET NAMES utf8; 1586--source include/ctype_numconv.inc 1587 1588--echo # 1589--echo # Bug#57687 crash when reporting duplicate group_key error and utf8 1590--echo # Bug#58081 Duplicate entry error when doing GROUP BY 1591--echo # MDEV-9332 Bug after upgrade to 10.1.10 1592--echo # 1593SET NAMES utf8; 1594CREATE TABLE t1 (a INT); 1595INSERT INTO t1 VALUES (0), (0), (1), (0), (0); 1596SELECT COUNT(*) FROM t1, t1 t2 1597GROUP BY INSERT('', t2.a, t1.a, (@@global.max_binlog_size)); 1598DROP TABLE t1; 1599 1600--echo # 1601--echo # Bug#11764503 (Bug#57341) Query in EXPLAIN EXTENDED shows wrong characters 1602--echo # 1603 1604--echo # Emulate utf8 client erroneously started with --default-character-set=latin1, 1605--echo # # as in the bug report. EXPLAIN output should still be pretty readable 1606SET NAMES latin1; 1607EXPLAIN EXTENDED SELECT 'abcdÁÂÃÄÅ', _latin1'abcdÁÂÃÄÅ', _utf8'abcdÁÂÃÄÅ' AS u; 1608--echo # Test normal utf8 1609SET NAMES utf8; 1610EXPLAIN EXTENDED SELECT 'abcdÁÂÃÄÅ', _latin1'abcdÁÂÃÄÅ', _utf8'abcdÁÂÃÄÅ'; 1611 1612--echo # 1613--echo # Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT 1614--echo # 1615 1616SET NAMES utf8; 1617SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l 1618FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body 1619UNION ALL 1620SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1 1621GROUP BY id 1622ORDER BY l DESC; 1623 1624SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l 1625FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body 1626UNION ALL 1627SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1; 1628 1629--echo # 1630--echo # MDEV-7814 Assertion `args[0]->fixed' fails in Item_func_conv_charset::Item_func_conv_charset 1631--echo # 1632CREATE TABLE t1(a CHAR(1) CHARACTER SET latin1, b INT NOT NULL); 1633CREATE TABLE t2(a CHAR(1) CHARACTER SET utf8 COLLATE utf8_general_ci, b INT NOT NULL); 1634SELECT (SELECT t2.a FROM t2 WHERE t2.a=t1.a) AS aa, b, COUNT(b) FROM t1 GROUP BY aa; 1635DROP TABLE t1,t2; 1636 1637--echo # 1638--echo # MDEV-7649 wrong result when comparing utf8 column with an invalid literal 1639--echo # 1640 1641SET NAMES utf8 COLLATE utf8_general_ci; 1642--let ENGINE=InnoDB 1643--source include/ctype_utf8_ilseq.inc 1644--let ENGINE=MyISAM 1645--source include/ctype_utf8_ilseq.inc 1646--let ENGINE=HEAP 1647--source include/ctype_utf8_ilseq.inc 1648 1649--echo # 1650--echo # MDEV-8067 correct fix for MySQL Bug # 19699237: UNINITIALIZED VARIABLE IN ITEM_FIELD::STR_RESULT 1651--echo # 1652CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); 1653CREATE TABLE t2 (a VARCHAR(10) CHARACTER SET latin1); 1654INSERT INTO t1 VALUES ('aaa'); 1655INSERT INTO t2 VALUES ('aaa'); 1656SELECT (SELECT CONCAT(a),1 FROM t1) <=> (SELECT CONCAT(a),1 FROM t2); 1657INSERT INTO t1 VALUES ('aaa'); 1658INSERT INTO t2 VALUES ('aaa'); 1659# Running the below query crashed with two rows 1660--error ER_SUBQUERY_NO_1_ROW 1661SELECT (SELECT CONCAT(a),1 FROM t1) <=> (SELECT CONCAT(a),1 FROM t2); 1662DROP TABLE t1, t2; 1663 1664--echo # 1665--echo # MDEV-8630 Datetime value dropped in "INSERT ... SELECT ... ON DUPLICATE KEY" 1666--echo # 1667SET NAMES utf8; 1668CREATE TABLE t1 (id2 int, ts timestamp); 1669INSERT INTO t1 VALUES (1,'2012-06-11 15:17:34'),(2,'2012-06-11 15:18:24'); 1670CREATE TABLE t2 AS SELECT 1671 COALESCE(ts, 0) AS c0, 1672 GREATEST(COALESCE(ts, 0), COALESCE(ts, 0)) AS c1, 1673 GREATEST(CASE WHEN 1 THEN ts ELSE 0 END, CASE WHEN 1 THEN ts ELSE 0 END) AS c2, 1674 GREATEST(IFNULL(ts,0), IFNULL(ts,0)) AS c3, 1675 GREATEST(IF(1,ts,0), IF(1,ts,0)) AS c4 1676FROM t1; 1677SHOW CREATE TABLE t2; 1678SELECT * FROM t2; 1679DROP TABLE t2, t1; 1680 1681--echo # 1682--echo # MDEV-9319 ALTER from a bigger to a smaller blob type truncates too much data 1683--echo # 1684SET NAMES utf8; 1685CREATE TABLE t1 (a TEXT CHARACTER SET utf8); 1686INSERT INTO t1 VALUES (REPEAT('Ａ',100)); 1687SELECT OCTET_LENGTH(a) FROM t1; 1688SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR 1689ALTER TABLE t1 MODIFY a TINYTEXT CHARACTER SET utf8; 1690SELECT OCTET_LENGTH(a),a FROM t1; 1691DROP TABLE t1; 1692 1693--echo # 1694--echo # MDEV-8402 Bug#77473 Bug#21317406 TRUNCATED DATA WITH SUBQUERY & UTF8 1695--echo # 1696--echo # 1697 1698SET NAMES utf8; 1699SELECT length(rpad(_utf8 0xD0B1, 65536, _utf8 0xD0B2)) AS data; 1700SELECT length(data) AS len FROM ( 1701 SELECT rpad(_utf8 0xD0B1, 65536, _utf8 0xD0B2) AS data 1702) AS sub; 1703 1704SELECT length(rpad(_utf8 0xD0B1, 65535, _utf8 0xD0B2)) AS data; 1705SELECT length(data) AS len FROM ( 1706 SELECT rpad(_utf8 0xD0B1, 65535, _utf8 0xD0B2) AS data 1707) AS sub; 1708 1709SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36766) AS data) AS sub; 1710SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36767) AS data) AS sub; 1711SELECT length(data) AS len FROM (SELECT REPEAT('ä', 36778) AS data) AS sub; 1712SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65535) AS data) AS sub; 1713SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65536) AS data) AS sub; 1714SELECT length(data) AS len FROM (SELECT REPEAT('ä', 65537) AS data) AS sub; 1715 1716--echo # 1717--echo # MDEV-10717 Assertion `!null_value' failed in virtual bool Item::send(Protocol*, String*) 1718--echo # 1719CREATE TABLE t1 (i INT, KEY(i)); 1720INSERT INTO t1 VALUES (20081205),(20050327); 1721SELECT HEX(i), HEX(CHAR(i USING utf8)) FROM t1; 1722SET sql_mode='STRICT_ALL_TABLES'; 1723SELECT HEX(i), HEX(CHAR(i USING utf8)) FROM t1; 1724# Avoid garbage in the output 1725--replace_column 1 ### 1726SELECT CHAR(i USING utf8) FROM t1; 1727SET sql_mode=DEFAULT; 1728DROP TABLE t1; 1729 1730--echo # 1731--echo # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 1732--echo # 1733 1734SET sql_mode=''; 1735CREATE TABLE t1 (c1 VARCHAR(21844) CHARACTER SET utf8); 1736DESCRIBE t1; 1737DROP TABLE t1; 1738 1739CREATE TABLE t1 (c1 VARCHAR(21845) CHARACTER SET utf8); 1740DESCRIBE t1; 1741DROP TABLE t1; 1742 1743CREATE TABLE t1 (c1 VARCHAR(21846) CHARACTER SET utf8); 1744DESCRIBE t1; 1745DROP TABLE t1; 1746SET sql_mode=default; 1747 1748--echo # 1749--echo # End of 5.5 tests 1750--echo # 1751 1752--echo # 1753--echo # Start of 5.6 tests 1754--echo # 1755 1756--echo # 1757--echo # WL#3664 WEIGHT_STRING 1758--echo # 1759 1760set names utf8; 1761--source include/weight_string.inc 1762--source include/weight_string_euro.inc 1763--source include/weight_string_l1.inc 1764 1765set @@collation_connection=utf8_bin; 1766--source include/weight_string.inc 1767--source include/weight_string_euro.inc 1768--source include/weight_string_l1.inc 1769 1770--echo # 1771--echo # Checking strnxfrm() with odd length 1772--echo # 1773set max_sort_length=9; 1774select @@max_sort_length; 1775create table t1 (a varchar(128) character set utf8 collate utf8_general_ci); 1776insert into t1 values ('a'),('b'),('c'); 1777select * from t1 order by a; 1778alter table t1 modify a varchar(128) character set utf8 collate utf8_bin; 1779select * from t1 order by a; 1780drop table t1; 1781set max_sort_length=default; 1782 1783--echo # 1784--echo # End of 5.6 tests 1785--echo # 1786 1787--echo # 1788--echo # Start of 10.0 tests 1789--echo # 1790 1791SET NAMES utf8 COLLATE utf8_bin; 1792--source include/ctype_like_cond_propagation.inc 1793SET NAMES utf8; 1794--source include/ctype_like_cond_propagation.inc 1795--source include/ctype_like_cond_propagation_utf8_german.inc 1796 1797 1798--echo # 1799--echo # MDEV-6666 Malformed result for CONCAT(utf8_column, binary_string) 1800--echo # 1801 1802CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); 1803INSERT INTO t1 VALUES ('a'); 1804--error ER_INVALID_CHARACTER_STRING 1805SELECT CONCAT(a,0xFF) FROM t1; 1806SELECT CONCAT(a,0xC3BF) FROM t1; 1807DROP TABLE t1; 1808--error ER_INVALID_CHARACTER_STRING 1809SELECT CONCAT('a' COLLATE utf8_unicode_ci, _binary 0xFF); 1810PREPARE stmt FROM "SELECT CONCAT('a' COLLATE utf8_unicode_ci, ?)"; 1811SET @arg00=_binary 0xFF; 1812--error ER_INVALID_CHARACTER_STRING 1813EXECUTE stmt USING @arg00; 1814DEALLOCATE PREPARE stmt; 1815SET NAMES latin1; 1816PREPARE stmt FROM "SELECT CONCAT(_utf8'a' COLLATE utf8_unicode_ci, ?)"; 1817EXECUTE stmt USING @no_such_var; 1818DEALLOCATE PREPARE stmt; 1819SET NAMES utf8; 1820 1821--echo # 1822--echo # MDEV-6679 Different optimizer plan for "a BETWEEN 'string' AND ?" and "a BETWEEN ? AND 'string'" 1823--echo # 1824SET NAMES utf8, collation_connection=utf8_swedish_ci; 1825CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8, b INT NOT NULL DEFAULT 0, key(a)); 1826INSERT INTO t1 (a) VALUES ('a'),('b'),('c'),('d'),('¢'); 1827SET @arg='¢'; 1828PREPARE stmt FROM "EXPLAIN SELECT * FROM t1 WHERE a BETWEEN _utf8'¢' and ?"; 1829EXECUTE stmt USING @arg; 1830PREPARE stmt FROM "EXPLAIN SELECT * FROM t1 WHERE a between ? and _utf8'¢'"; 1831EXECUTE stmt USING @arg; 1832DEALLOCATE PREPARE stmt; 1833DROP TABLE t1; 1834 1835-- echo # 1836-- echo # MDEV-6683 A parameter and a string literal with the same values are not recognized as equal by the optimizer 1837-- echo # 1838 1839SET NAMES utf8, collation_connection=utf8_swedish_ci; 1840CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, b INT NOT NULL DEFAULT 0, key(a)); 1841INSERT INTO t1 (a) VALUES ('a'),('b'),('c'),('d'),('¢'); 1842SET @arg='¢'; 1843PREPARE stmt FROM "EXPLAIN SELECT * FROM t1 WHERE a BETWEEN _utf8'¢' and ?"; 1844EXECUTE stmt USING @arg; 1845PREPARE stmt FROM "EXPLAIN SELECT * FROM t1 WHERE a between ? and _utf8'¢'"; 1846EXECUTE stmt USING @arg; 1847DEALLOCATE PREPARE stmt; 1848DROP TABLE t1; 1849 1850--echo # 1851--echo # MDEV-6688 Illegal mix of collation with bit string B'01100001' 1852--echo # 1853CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1, b INT); 1854INSERT INTO t1 VALUES ('a',1); 1855SELECT CONCAT(a, IF(b>10, _utf8 X'61', _utf8 X'61')) FROM t1; 1856SELECT CONCAT(a, IF(b>10, _utf8 X'61', _utf8 B'01100001')) FROM t1; 1857DROP TABLE t1; 1858 1859--echo # 1860--echo # MDEV-6694 Illegal mix of collation with a PS parameter 1861--echo # 1862SET NAMES utf8; 1863CREATE TABLE t1 (a INT, b VARCHAR(10) CHARACTER SET latin1); 1864INSERT INTO t1 VALUES (1,'a'); 1865SELECT CONCAT(b,IF(a,'b','b')) FROM t1; 1866PREPARE stmt FROM "SELECT CONCAT(b,IF(a,?,?)) FROM t1"; 1867SET @b='b'; 1868EXECUTE stmt USING @b,@b; 1869SET @b=''; 1870EXECUTE stmt USING @b,@b; 1871SET @b='я'; 1872--error ER_CANT_AGGREGATE_2COLLATIONS 1873EXECUTE stmt USING @b,@b; 1874DEALLOCATE PREPARE stmt; 1875DROP TABLE t1; 1876 1877--echo # 1878--echo # MDEV-7629 Regression: Bit and hex string literals changed column names in 10.0.14 1879--echo # 1880SELECT _utf8 0x7E, _utf8 X'7E', _utf8 B'01111110'; 1881 1882 1883let $ctype_unescape_combinations=selected; 1884--source include/ctype_unescape.inc 1885 1886--echo # 1887--echo # MDEV-12681 Wrong VIEW results for CHAR(0xDF USING latin1) 1888--echo # 1889 1890SET NAMES utf8; 1891SELECT CHAR(0xDF USING latin1); 1892CREATE OR REPLACE VIEW v1 AS SELECT CHAR(0xDF USING latin1) AS c; 1893SHOW CREATE VIEW v1; 1894SELECT * FROM v1; 1895DROP VIEW v1; 1896 1897 1898# 1899# MDEV-13118 Wrong results with LOWER and UPPER and subquery 1900# 1901SET NAMES utf8; 1902--source include/ctype_mdev13118.inc 1903 1904 1905--echo # 1906--echo # End of 10.0 tests 1907--echo # 1908 1909 1910--echo # 1911--echo # Start of 10.1 tests 1912--echo # 1913 1914--echo # 1915--echo # MDEV-6572 "USE dbname" with a bad sequence erroneously connects to a wrong database 1916--echo # 1917SET NAMES utf8; 1918--error ER_INVALID_CHARACTER_STRING 1919SELECT * FROM `test����test`; 1920 1921--echo # 1922--echo #MDEV-8256 A part of a ROW comparison is erroneously optimized away 1923--echo # 1924SET NAMES utf8; 1925CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8); 1926INSERT INTO t1 VALUES ('1e1'),('1é1'); 1927SELECT * FROM t1 WHERE a=10; 1928SELECT * FROM t1 WHERE a='1e1'; 1929SELECT * FROM t1 WHERE a=10 AND a='1e1'; 1930SELECT * FROM t1 WHERE (a,a)=(10,'1e1'); 1931EXPLAIN EXTENDED SELECT * FROM t1 WHERE (a,a)=(10,'1e1'); 1932DROP TABLE t1; 1933 1934--echo # 1935--echo # MDEV-8688 Wrong result for SELECT..WHERE varchar_column IN (1,2,3) AND varchar_column=' 1'; 1936--echo # 1937SET NAMES utf8; 1938CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET latin1); 1939INSERT INTO t1 VALUES ('1e1'),('1ë1'); 1940SELECT * FROM t1 WHERE a IN (1,2); 1941SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; 1942SELECT * FROM t1 WHERE a IN (1,2,'x') AND a='1ë1'; 1943# Equality should not propagate '1ë1' to IN: incompatible comparison context 1944EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2) AND a='1ë1'; 1945EXPLAIN EXTENDED SELECT * FROM t1 WHERE a IN (1,2,'x') AND a='1ë1'; 1946DROP TABLE IF EXISTS t1; 1947 1948 1949--echo # 1950--echo # MDEV-8816 Equal field propagation is not applied for WHERE varbinary_column>=_utf8'a' COLLATE utf8_swedish_ci AND varbinary_column='A'; 1951--echo # 1952CREATE TABLE t1 (c VARBINARY(10)); 1953INSERT INTO t1 VALUES ('a'),('A'); 1954SELECT * FROM t1 WHERE c>=_utf8'a' COLLATE utf8_general_ci AND c='A'; 1955EXPLAIN EXTENDED 1956SELECT * FROM t1 WHERE c>=_utf8'a' COLLATE utf8_general_ci AND c='A'; 1957DROP TABLE t1; 1958 1959 1960--echo # 1961--echo # MDEV-7231 Field ROUTINE_DEFINITION in INFORMATION_SCHEMA.`ROUTINES` contains broken procedure body when used shielding quotes inside. 1962--echo # 1963DELIMITER $$; 1964CREATE PROCEDURE p1() 1965BEGIN 1966SELECT CONCAT('ABC = ''',1,''''), CONCAT('ABC = ',2); 1967SELECT '''', """", '\'', "\""; 1968SELECT '<tab> <tab>\t<tab>'; 1969SELECT '<nl> 1970<nl>\n<nl>'; 1971SELECT 'test'; 1972SELECT 'tëst'; 1973SELECT 'test\0'; 1974SELECT 'tëst\0'; 1975SELECT _binary'test'; 1976SELECT _binary'test\0'; 1977SELECT N'''', N"""", N'\'', N"\""; 1978SELECT N'<tab> <tab>\t<tab>'; 1979SELECT N'<nl> 1980<nl>\n<nl>'; 1981SELECT N'test'; 1982SELECT N'tëst'; 1983SELECT N'test\0'; 1984SELECT N'tëst\0'; 1985END$$ 1986DELIMITER ;$$ 1987SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES 1988WHERE ROUTINE_SCHEMA='test' AND SPECIFIC_NAME ='p1'; 1989SELECT body_utf8 FROM mysql.proc WHERE name='p1'; 1990DROP PROCEDURE p1; 1991 1992SET @@SQL_MODE='NO_BACKSLASH_ESCAPES'; 1993DELIMITER $$; 1994CREATE PROCEDURE p1() 1995BEGIN 1996SELECT CONCAT('ABC = ''',1,''''), CONCAT('ABC = ',2); 1997SELECT '''', """"; 1998SELECT '<tab> <tab>\t<tab>'; 1999SELECT '<nl> 2000<nl>\n<nl>'; 2001SELECT 'test'; 2002SELECT 'tëst'; 2003SELECT 'test\0'; 2004SELECT 'tëst\0'; 2005SELECT _binary'test'; 2006SELECT _binary'test\0'; 2007SELECT N'''', N""""; 2008SELECT N'<tab> <tab>\t<tab>'; 2009SELECT N'<nl> 2010<nl>\n<nl>'; 2011SELECT N'test'; 2012SELECT N'tëst'; 2013SELECT N'test\0'; 2014SELECT N'tëst\0'; 2015END$$ 2016DELIMITER ;$$ 2017SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES 2018WHERE ROUTINE_SCHEMA='test' AND SPECIFIC_NAME ='p1'; 2019SELECT body_utf8 FROM mysql.proc WHERE name='p1'; 2020DROP PROCEDURE p1; 2021SET @@SQL_MODE=default; 2022 2023 2024# TODO: Uncomment the below test whe we fix: 2025# MDEV-9623INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION does not handle binary literals well 2026# 2027#SET NAMES binary; 2028#CREATE FUNCTION f1() RETURNS TEXT RETURN CONCAT('i','й'); 2029#SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES 2030#WHERE ROUTINE_SCHEMA='test' AND SPECIFIC_NAME ='f1'; 2031#SELECT body_utf8 FROM mysql.proc WHERE name='f1'; 2032#DROP FUNCTION f1; 2033#SET NAMES utf8; 2034 2035--echo # 2036--echo # MDEV-10191 non convertible chars convert() resulted in Null instead "?" on Windows 2037--echo # 2038 2039SET sql_mode='STRICT_TRANS_TABLES'; 2040SELECT CONVERT(_utf8 0xC499 USING latin1); 2041SELECT CAST(_utf8 0xC499 AS CHAR CHARACTER SET latin1); 2042 2043SET sql_mode=default; 2044SELECT CONVERT(_utf8 0xC499 USING latin1); 2045SELECT CAST(_utf8 0xC499 AS CHAR CHARACTER SET latin1); 2046 2047 2048--echo # 2049--echo # MDEV-15005 ASAN: stack-buffer-overflow in my_strnncollsp_simple 2050--echo # 2051 2052SET NAMES utf8; 2053SELECT CONVERT(1, CHAR) IN ('100', 10, '101'); 2054SELECT CONVERT(1, CHAR) IN ('100', 10, '1'); 2055SELECT CONVERT(1, CHAR) IN ('100', '10', '1'); 2056 2057--echo # 2058--echo # MDEV-23535 SIGSEGV, SIGABRT and SIGILL in typeinfo for Item_func_set_collation (on optimized builds) 2059--echo # 2060 2061SET NAMES utf8; 2062CREATE OR REPLACE TABLE t1(a DATETIME) ENGINE=MYISAM; 2063INSERT INTO t1 VALUES ('2019-03-10 02:55:05'); 2064CREATE OR REPLACE TABLE t2(a VARCHAR(50) CHARACTER SET latin1) ENGINE=MYISAM; 2065INSERT INTO t2 VALUES ('2019-03-10 02:55:05'); 2066SELECT * FROM t1 WHERE (SELECT 1,CONCAT(a) FROM t1) = (SELECT 1,CONCAT(a) FROM t2); 2067DROP TABLE t1, t2; 2068 2069 2070--echo # 2071--echo # End of 10.1 tests 2072--echo # 2073 2074--echo # 2075--echo # Start of 10.2 tests 2076--echo # 2077 2078--echo # 2079--echo # MDEV-9824 LOAD DATA does not work with multi-byte strings in LINES TERMINATED BY when IGNORE is specified 2080--echo # 2081CREATE TABLE t1 (c1 VARCHAR(10) CHARACTER SET utf8); 2082LOAD DATA INFILE '../../std_data/loaddata/mdev9824.txt' INTO TABLE t1 CHARACTER SET utf8 LINES TERMINATED BY 'ёё'; 2083SELECT c1 FROM t1; 2084DELETE FROM t1; 2085LOAD DATA INFILE '../../std_data/loaddata/mdev9824.txt' INTO TABLE t1 CHARACTER SET utf8 LINES TERMINATED BY 'ёё' IGNORE 1 LINES; 2086SELECT c1 FROM t1 ORDER BY c1; 2087DROP TABLE t1; 2088 2089--echo # 2090--echo # MDEV-9842 LOAD DATA INFILE does not work well with a TEXT column when using sjis 2091--echo # 2092CREATE TABLE t1 (a TEXT CHARACTER SET utf8); 2093LOAD DATA INFILE '../../std_data/loaddata/mdev9823.utf8mb4.txt' IGNORE INTO TABLE t1 CHARACTER SET utf8 IGNORE 4 LINES; 2094SELECT HEX(a) FROM t1; 2095DROP TABLE t1; 2096 2097--echo # 2098--echo # MDEV-9874 LOAD XML INFILE does not handle well broken multi-byte characters 2099--echo # 2100CREATE TABLE t1 (a TEXT CHARACTER SET utf8); 2101LOAD XML INFILE '../../std_data/loaddata/mdev9874.xml' IGNORE INTO TABLE t1 CHARACTER SET utf8 ROWS IDENTIFIED BY '<row>'; 2102SELECT HEX(a) FROM t1; 2103DROP TABLE t1; 2104 2105--echo # 2106--echo # MDEV-10134 Add full support for DEFAULT 2107--echo # 2108 2109# This test uses some magic codes: 2110# _latin1 0xC39F is "A WITH TILDE + Y WITH DIAERESIS" 2111# _utf8 0xC39F is "SHARP S" 2112 2113# "A WITH TILDE + Y WITH DIAERESIS" in DEFAULT. 2114SET NAMES latin1; 2115CREATE TABLE t1 (a VARCHAR(30) CHARACTER SET latin1 DEFAULT CONCAT('ß')); 2116SET NAMES utf8; 2117SHOW CREATE TABLE t1; 2118INSERT INTO t1 VALUES (DEFAULT); 2119SELECT HEX(a),a FROM t1; 2120SET NAMES latin1; 2121ALTER TABLE t1 ADD b VARCHAR(30) CHARACTER SET latin1 DEFAULT CONCAT('ß'); 2122SET NAMES utf8; 2123ALTER TABLE t1 ADD c VARCHAR(30) CHARACTER SET latin1 DEFAULT CONCAT('ß'); 2124SHOW CREATE TABLE t1; 2125# Testing that DEFAULT is independent on the current "SET NAMES". 2126DELETE FROM t1; 2127INSERT INTO t1 VALUES(); 2128SELECT * FROM t1; 2129SET NAMES latin1; 2130DELETE FROM t1; 2131INSERT INTO t1 VALUES(); 2132SET NAMES utf8; 2133SELECT * FROM t1; 2134DROP TABLE t1; 2135 2136SET NAMES latin1; 2137CREATE TABLE t1 (a VARCHAR(30) CHARACTER SET utf8 DEFAULT CONCAT('ß')); 2138SET NAMES utf8; 2139SHOW CREATE TABLE t1; 2140INSERT INTO t1 VALUES (DEFAULT); 2141SELECT HEX(a), a FROM t1; 2142DROP TABLE t1; 2143 2144# "SHARP S" in DEFAULT 2145SET NAMES utf8; 2146CREATE TABLE t1 (a VARCHAR(30) CHARACTER SET latin1 DEFAULT CONCAT('ß')); 2147SHOW CREATE TABLE t1; 2148INSERT INTO t1 VALUES (DEFAULT); 2149SELECT HEX(a) FROM t1; 2150DROP TABLE t1; 2151 2152SET NAMES utf8; 2153CREATE TABLE t1 (a VARCHAR(30) CHARACTER SET utf8 DEFAULT CONCAT('ß')); 2154SHOW CREATE TABLE t1; 2155INSERT INTO t1 VALUES (DEFAULT); 2156SELECT HEX(a) FROM t1; 2157DROP TABLE t1; 2158 2159 2160--echo # 2161--echo # MDEV-9711 NO PAD Collatons 2162--echo # 2163let $coll='utf8_general_nopad_ci'; 2164let $coll_pad='utf8_general_ci'; 2165--source include/ctype_pad_all_engines.inc 2166 2167let $coll='utf8_nopad_bin'; 2168let $coll_pad='utf8_bin'; 2169--source include/ctype_pad_all_engines.inc 2170 2171--echo # 2172--echo # MDEV-23408 Wrong result upon query from I_S and further Assertion `!alias_arg || strlen(alias_arg->str) == alias_arg->length' failed with certain connection charset 2173--echo # 2174 2175SET NAMES utf8; 2176SET SESSION character_set_connection=latin1; 2177CREATE VIEW v1 AS SELECT 'ä' AS c1; 2178SELECT c1, HEX(c1) FROM v1; 2179CREATE TABLE kv (v BLOB); 2180--replace_result $MYSQLD_DATADIR MYSQLD_DATADIR 2181eval LOAD DATA INFILE '$MYSQLD_DATADIR/test/v1.frm' REPLACE INTO TABLE kv; 2182SELECT * FROM kv WHERE v LIKE _binary'query=%'; 2183DROP TABLE kv; 2184DROP VIEW v1; 2185SET NAMES utf8; 2186 2187--echo # 2188--echo # MDEV-24335 Unexpected question mark in the end of a TINYTEXT column 2189--echo # 2190 2191CREATE TABLE t1 (a TINYTEXT character set utf8); 2192INSERT IGNORE INTO t1 VALUES (REPEAT(_utf8 0xD184, 250)); 2193SELECT LENGTH(a), CHAR_LENGTH(a), RIGHT(a,3) FROM t1; 2194DROP TABLE t1; 2195 2196--echo # 2197--echo # End of 10.2 tests 2198--echo # 2199 2200--echo # 2201--echo # Start of 10.3 tests 2202--echo # 2203 2204 2205--echo # 2206--echo # MDEV-11155 Bad error message when creating a SET column with comma and non-ASCII characters 2207--echo # 2208 2209SET NAMES utf8; 2210--error ER_ILLEGAL_VALUE_FOR_TYPE 2211CREATE TABLE t1 (a SET('a,bü')); 2212 2213--echo # 2214--echo # MDEV-12607 Hybrid functions create wrong VARBINARY length when mixing character and binary data 2215--echo # 2216SET sql_mode=''; 2217SET NAMES utf8; 2218CREATE OR REPLACE TABLE t1 AS SELECT COALESCE('ßa',_binary 'a'); 2219SELECT * FROM t1; 2220SHOW CREATE TABLE t1; 2221DROP TABLE t1; 2222SET sql_mode=DEFAULT; 2223 2224 2225--echo # 2226--echo # MDEV-19239 ERROR 1300 (HY000): Invalid utf8 character string in 10.3.13-MariaDB 2227--echo # 2228 2229# 2230# Test that the following query does not fail on "Invalid utf8 character string" 2231# 2232 2233SET NAMES utf8; 2234--error ER_NO_SUCH_TABLE 2235SELECT 2236 x.消息ID, 2237 x.消息TITLE, 2238 x.消息类型, 2239 x.发送时间, 2240 x.阅读时间,x.老师ID, 2241 IF(x.四天内最近一次登录时间='2100-01-01 00:00:00','',x.四天内最近一次登录时间) 四天内最近一次登录时间 2242FROM ( 2243 SELECT 2244 msg.*, 2245 CASE 2246 WHEN login.login_time BETWEEN msg.发送时间 AND DATE_ADD(msg.发送时间,INTERVAL 4 DAY) 2247 THEN login.login_time 2248 WHEN (login.login_time NOT BETWEEN msg.发送时间 AND DATE_ADD(msg.发送时间,INTERVAL 4 DAY)) AND login.login_time>0 2249 THEN '2100-01-01 00:00:00' ELSE '' 2250 END 四天内最近一次登录时间 2251 FROM ( 2252 SELECT 2253 me.id 消息ID, 2254 me.title 消息TITLE, 2255 CASE 2256 WHEN me.type=1 2257 THEN 'Interview Message' 2258 WHEN me.type=2 2259 THEN 'Orientation Message' 2260 WHEN me.type=3 2261 THEN 'Warning Message' 2262 WHEN me.type=4 2263 THEN 'Fail Message' 2264 WHEN me.type=5 2265 THEN 'FM Message' 2266 WHEN me.type=6 2267 THEN 'Training Message' 2268 WHEN me.type=7 2269 THEN 'TUrgent Message' 2270 END 消息类型, 2271 FROM_UNIXTIME(me.sending_time) 发送时间, 2272 IF(tar.is_read=1,FROM_UNIXTIME(tar.read_time),'') 阅读时间, 2273 tar.tid 老师ID 2274 FROM ebk_message me 2275 LEFT JOIN ebk_message_target tar 2276 ON me.id=tar.msg_id 2277 WHERE 2278 FROM_UNIXTIME(me.sending_time,'%Y-%m-%d') BETWEEN 'start' AND 'end' AND me.status=1 AND tar.tid>0 2279 GROUP BY 2280 tar.tid, 2281 me.sending_time,me.id) msg 2282 LEFT JOIN ( 2283 SELECT tid,FROM_UNIXTIME(login_time) login_time 2284 FROM ebk_teacher_login_log 2285 WHERE FROM_UNIXTIME(login_time,'%Y-%m-%d') BETWEEN 'start' AND DATE_ADD('end',INTERVAL 4 DAY) 2286 ORDER BY tid,FROM_UNIXTIME(login_time)) login 2287 ON 2288 msg.老师ID=login.tid 2289 ORDER BY msg.消息ID,msg.发送时间,msg.老师ID,login_time) x 2290 GROUP BY x.消息ID,x.发送时间,x.老师ID; 2291 2292 2293SET NAMES utf8; 2294CREATE TABLE t1 (x INT); 2295INSERT INTO t1 VALUES (1); 2296SELECT x AS 5天内最近一次登录时间 FROM t1; 2297DROP TABLE t1; 2298 2299--echo # 2300--echo # MDEV-22391 Assertion `0' failed in Item_type_holder::val_str on utf16 charset table query 2301--echo # 2302 2303SET NAMES utf8; 2304VALUES (_latin1 0xDF) UNION SELECT _utf8'a' COLLATE utf8_bin; 2305VALUES (_latin1 0xDF) UNION VALUES(_utf8'a' COLLATE utf8_bin); 2306 2307--echo # 2308--echo # End of 10.3 tests 2309--echo # 2310 2311 2312--echo # 2313--echo # Start of 10.5 tests 2314--echo # 2315 2316--echo # 2317--echo # MDEV-20712 Wrong data type for CAST(@a AS BINARY) for a numeric variable 2318--echo # 2319 2320SET NAMES utf8; 2321SET @a=2; 2322CREATE OR REPLACE TABLE t1 AS SELECT CAST(1 AS BINARY), CAST(@a AS BINARY), CAST(@b:=3 AS BINARY); 2323SHOW CREATE TABLE t1; 2324DROP TABLE t1; 2325 2326--echo # 2327--echo # MDEV-20890 Illegal mix of collations with UUID() 2328--echo # 2329 2330SET NAMES utf8 COLLATE utf8_unicode_ci; 2331SELECT uuid()>''; 2332 2333 2334--echo # 2335--echo # MDEV-8844 Unreadable control characters printed as is in warnings 2336--echo # 2337SET NAMES utf8; 2338--echo # control, part1 2339SELECT CAST(_utf8 0x610062 AS INT); 2340SELECT CAST(_utf8 0x610162 AS INT); 2341SELECT CAST(_utf8 0x611F62 AS INT); 2342 2343--echo # control, part2: U+0080..U+009F 2344SELECT CAST(_utf8 0x617F62 AS INT); 2345SELECT CAST(_utf8 0x61C28062 AS INT); 2346SELECT CAST(_utf8 0x61C29F62 AS INT); 2347 2348--echo # normal characters 2349SELECT CAST(_utf8 0x612062 AS INT); 2350SELECT CAST(_utf8 0x617E62 AS INT); 2351SELECT CAST(_utf8 0x61C2BF62 AS INT); 2352SELECT CAST(_utf8 'ëëë' AS INT); 2353SELECT CAST(_utf8 'œœœ' AS INT); 2354SELECT CAST(_utf8 'яяя' AS INT); 2355 2356 2357--echo # 2358--echo # End of 10.5 tests 2359--echo # 2360