2
# Tests with the utf8 character set
5
--source include/have_innodb.inc
8
drop table if exists t1,t2;
12
select left(_utf8 0xD0B0D0B1D0B2,1);
13
select right(_utf8 0xD0B0D0B2D0B2,1);
15
select locate('he','hello');
16
select locate('he','hello',2);
17
select locate('lo','hello',2);
18
select locate('HE','hello');
19
select locate('HE','hello',2);
20
select locate('LO','hello',2);
21
select locate('HE','hello' collate utf8_bin);
22
select locate('HE','hello' collate utf8_bin,2);
23
select locate('LO','hello' collate utf8_bin,2);
25
select locate(_utf8 0xD0B1, _utf8 0xD0B0D0B1D0B2);
26
select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2);
27
select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2);
28
select locate(_utf8 0xD091, _utf8 0xD0B0D0B1D0B2 collate utf8_bin);
29
select locate(_utf8 0xD0B1, _utf8 0xD0B0D091D0B2 collate utf8_bin);
31
select length(_utf8 0xD0B1), bit_length(_utf8 0xD0B1), char_length(_utf8 0xD0B1);
35
select 'A' like 'a' collate utf8_bin;
36
select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD0B1,_utf8 '%');
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:
42
select convert(_latin1'G�nter Andr�' using utf8) like CONVERT(_latin1'G�NTER%' USING utf8);
43
select CONVERT(_koi8r'����' USING utf8) LIKE CONVERT(_koi8r'����' USING utf8);
44
select CONVERT(_koi8r'����' USING utf8) LIKE CONVERT(_koi8r'����' USING utf8);
47
# Check the following:
59
# The same for binary collation
61
SELECT 'a' = 'a ' collate utf8_bin;
62
SELECT 'a\0' < 'a' collate utf8_bin;
63
SELECT 'a\0' < 'a ' collate utf8_bin;
64
SELECT 'a\t' < 'a' collate utf8_bin;
65
SELECT 'a\t' < 'a ' collate utf8_bin;
67
CREATE TABLE t1 (a char(10) character set utf8 not null);
68
INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a ');
69
SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1;
73
# Fix this, it should return 1:
75
#select _utf8 0xD0B0D0B1D0B2 like concat(_utf8'%',_utf8 0xD091,_utf8 '%');
79
# Bug 2367: INSERT() behaviour is different for different charsets.
81
select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es');
82
select insert("aa",100,1,"b"),insert("aa",1,3,"b");
85
# LELF() didn't work well with utf8 in some cases too.
87
select char_length(left(@a:='тест',5)), length(@a), @a;
93
create table t1 select date_format("2004-01-19 10:10:10", "%Y-%m-%d");
99
# Bug#22646 LC_TIME_NAMES: Assignment to non-UTF8 target fails
102
set LC_TIME_NAMES='fr_FR';
103
create table t1 (s1 char(20) character set latin1);
104
insert into t1 values (date_format('2004-02-02','%M'));
105
select hex(s1) from t1;
107
create table t1 (s1 char(20) character set koi8r);
108
set LC_TIME_NAMES='ru_RU';
109
insert into t1 values (date_format('2004-02-02','%M'));
110
insert into t1 values (date_format('2004-02-02','%b'));
111
insert into t1 values (date_format('2004-02-02','%W'));
112
insert into t1 values (date_format('2004-02-02','%a'));
113
select hex(s1), s1 from t1;
115
set LC_TIME_NAMES='en_US';
119
# Bug #2366 Wrong utf8 behaviour when data is truncated
122
create table t1 (s1 char(1) character set utf8);
123
insert into t1 values (_koi8r'��');
124
select s1,hex(s1),char_length(s1),octet_length(s1) from t1;
127
create table t1 (s1 tinytext character set utf8);
128
insert into t1 select repeat('a',300);
129
insert into t1 select repeat('�',300);
130
insert into t1 select repeat('a�',300);
131
insert into t1 select repeat('�a',300);
132
insert into t1 select repeat('��',300);
133
select hex(s1) from t1;
134
select length(s1),char_length(s1) from t1;
137
create table t1 (s1 text character set utf8);
138
insert into t1 select repeat('a',66000);
139
insert into t1 select repeat('�',66000);
140
insert into t1 select repeat('a�',66000);
141
insert into t1 select repeat('�a',66000);
142
insert into t1 select repeat('��',66000);
143
select length(s1),char_length(s1) from t1;
147
# Bug #2368 Multibyte charsets do not check that incoming data is well-formed
149
create table t1 (s1 char(10) character set utf8);
150
insert into t1 values (0x41FF);
151
select hex(s1) from t1;
154
create table t1 (s1 varchar(10) character set utf8);
155
insert into t1 values (0x41FF);
156
select hex(s1) from t1;
159
create table t1 (s1 text character set utf8);
160
insert into t1 values (0x41FF);
161
select hex(s1) from t1;
166
# UTF8 breaks primary keys for cols > 333 characters
169
create table t1 (a text character set utf8, primary key(a(360)));
174
# UTF8 charset breaks joins with mixed column/string constant
176
CREATE TABLE t1 ( a varchar(10) ) CHARACTER SET utf8;
177
INSERT INTO t1 VALUES ( 'test' );
178
SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a;
179
SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = 'test' and b.a = 'test';
180
SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a and a.a = 'test';
183
create table t1 (a char(255) character set utf8);
184
insert into t1 values('b'),('b');
185
select * from t1 where a = 'b';
186
select * from t1 where a = 'b' and a = 'b';
187
select * from t1 where a = 'b' and a != 'b';
193
set collation_connection=utf8_general_ci;
194
--source include/ctype_regex.inc
198
# Bug #3928 regexp [[:>:]] and UTF-8
202
# This should return TRUE
203
select 'вася' rlike '[[:<:]]вася[[:>:]]';
204
select 'вася ' rlike '[[:<:]]вася[[:>:]]';
205
select ' вася' rlike '[[:<:]]вася[[:>:]]';
206
select ' вася ' rlike '[[:<:]]вася[[:>:]]';
208
# This should return FALSE
209
select 'васяz' rlike '[[:<:]]вася[[:>:]]';
210
select 'zвася' rlike '[[:<:]]вася[[:>:]]';
211
select 'zвасяz' rlike '[[:<:]]вася[[:>:]]';
215
# ALTER TABLE crashes mysqld with enum column collated utf8_unicode_ci
217
CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8_unicode_ci);
218
ALTER TABLE t1 ADD COLUMN b CHAR(20);
221
# Customer Support Center issue # 3299
222
# ENUM and SET multibyte fields computed their length wronly
223
# when converted into a char field
225
create table t1 (a enum('aaaa','проба') character set utf8);
226
show create table t1;
227
insert into t1 values ('проба');
229
create table t2 select ifnull(a,a) from t1;
230
show create table t2;
236
# Bug 4521: unique key prefix interacts poorly with utf8
237
# MYISAM: keys with prefix compression, case insensitive collation.
239
create table t1 (c varchar(30) character set utf8, unique(c(10)));
240
insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');
241
insert into t1 values ('aaaaaaaaaa');
243
insert into t1 values ('aaaaaaaaaaa');
245
insert into t1 values ('aaaaaaaaaaaa');
246
insert into t1 values (repeat('b',20));
247
select c c1 from t1 where c='1';
248
select c c2 from t1 where c='2';
249
select c c3 from t1 where c='3';
250
select c cx from t1 where c='x';
251
select c cy from t1 where c='y';
252
select c cz from t1 where c='z';
253
select c ca10 from t1 where c='aaaaaaaaaa';
254
select c cb20 from t1 where c=repeat('b',20);
258
# Bug 4521: unique key prefix interacts poorly with utf8
259
# InnoDB: keys with prefix compression, case insensitive collation.
261
create table t1 (c varchar(30) character set utf8, unique(c(10))) engine=innodb;
262
insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');
263
insert into t1 values ('aaaaaaaaaa');
265
insert into t1 values ('aaaaaaaaaaa');
267
insert into t1 values ('aaaaaaaaaaaa');
268
insert into t1 values (repeat('b',20));
269
select c c1 from t1 where c='1';
270
select c c2 from t1 where c='2';
271
select c c3 from t1 where c='3';
272
select c cx from t1 where c='x';
273
select c cy from t1 where c='y';
274
select c cz from t1 where c='z';
275
select c ca10 from t1 where c='aaaaaaaaaa';
276
select c cb20 from t1 where c=repeat('b',20);
279
# Bug 4521: unique key prefix interacts poorly with utf8
280
# MYISAM: fixed length keys, case insensitive collation
282
create table t1 (c char(3) character set utf8, unique (c(2)));
283
insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');
284
insert into t1 values ('a');
285
insert into t1 values ('aa');
287
insert into t1 values ('aaa');
288
insert into t1 values ('b');
289
insert into t1 values ('bb');
291
insert into t1 values ('bbb');
292
insert into t1 values ('а');
293
insert into t1 values ('аа');
295
insert into t1 values ('ааа');
296
insert into t1 values ('б');
297
insert into t1 values ('бб');
299
insert into t1 values ('ббб');
300
insert into t1 values ('ꪪ');
301
insert into t1 values ('ꪪꪪ');
303
insert into t1 values ('ꪪꪪꪪ');
306
# Bug 4521: unique key prefix interacts poorly with utf8
307
# InnoDB: fixed length keys, case insensitive collation
309
create table t1 (c char(3) character set utf8, unique (c(2))) engine=innodb;
310
insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');
311
insert into t1 values ('a');
312
insert into t1 values ('aa');
314
insert into t1 values ('aaa');
315
insert into t1 values ('b');
316
insert into t1 values ('bb');
318
insert into t1 values ('bbb');
319
insert into t1 values ('а');
320
insert into t1 values ('аа');
322
insert into t1 values ('ааа');
323
insert into t1 values ('б');
324
insert into t1 values ('бб');
326
insert into t1 values ('ббб');
327
insert into t1 values ('ꪪ');
328
insert into t1 values ('ꪪꪪ');
330
insert into t1 values ('ꪪꪪꪪ');
333
# Bug 4531: unique key prefix interacts poorly with utf8
334
# Check HEAP+HASH, case insensitive collation
337
c char(10) character set utf8,
338
unique key a using hash (c(1))
340
show create table t1;
341
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
343
insert into t1 values ('aa');
345
insert into t1 values ('aaa');
346
insert into t1 values ('б');
348
insert into t1 values ('бб');
350
insert into t1 values ('ббб');
351
select c as c_all from t1 order by c;
352
select c as c_a from t1 where c='a';
353
select c as c_a from t1 where c='б';
357
# Bug 4531: unique key prefix interacts poorly with utf8
358
# Check HEAP+BTREE, case insensitive collation
361
c char(10) character set utf8,
362
unique key a using btree (c(1))
364
show create table t1;
365
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
367
insert into t1 values ('aa');
369
insert into t1 values ('aaa');
370
insert into t1 values ('б');
372
insert into t1 values ('бб');
374
insert into t1 values ('ббб');
375
select c as c_all from t1 order by c;
376
select c as c_a from t1 where c='a';
377
select c as c_a from t1 where c='б';
381
# Bug 4531: unique key prefix interacts poorly with utf8
382
# Check BDB, case insensitive collation
385
c char(10) character set utf8,
388
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
390
insert into t1 values ('aa');
392
insert into t1 values ('aaa');
393
insert into t1 values ('б');
395
insert into t1 values ('бб');
397
insert into t1 values ('ббб');
398
select c as c_all from t1 order by c;
399
select c as c_a from t1 where c='a';
400
select c as c_a from t1 where c='б';
404
# Bug 4521: unique key prefix interacts poorly with utf8
405
# MYISAM: keys with prefix compression, binary collation.
407
create table t1 (c varchar(30) character set utf8 collate utf8_bin, unique(c(10)));
408
insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z');
409
insert into t1 values ('aaaaaaaaaa');
411
insert into t1 values ('aaaaaaaaaaa');
413
insert into t1 values ('aaaaaaaaaaaa');
414
insert into t1 values (repeat('b',20));
415
select c c1 from t1 where c='1';
416
select c c2 from t1 where c='2';
417
select c c3 from t1 where c='3';
418
select c cx from t1 where c='x';
419
select c cy from t1 where c='y';
420
select c cz from t1 where c='z';
421
select c ca10 from t1 where c='aaaaaaaaaa';
422
select c cb20 from t1 where c=repeat('b',20);
426
# Bug 4521: unique key prefix interacts poorly with utf8
427
# MYISAM: fixed length keys, binary collation
429
create table t1 (c char(3) character set utf8 collate utf8_bin, unique (c(2)));
430
insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z');
431
insert into t1 values ('a');
432
insert into t1 values ('aa');
434
insert into t1 values ('aaa');
435
insert into t1 values ('b');
436
insert into t1 values ('bb');
438
insert into t1 values ('bbb');
439
insert into t1 values ('а');
440
insert into t1 values ('аа');
442
insert into t1 values ('ааа');
443
insert into t1 values ('б');
444
insert into t1 values ('бб');
446
insert into t1 values ('ббб');
447
insert into t1 values ('ꪪ');
448
insert into t1 values ('ꪪꪪ');
450
insert into t1 values ('ꪪꪪꪪ');
454
# Bug 4531: unique key prefix interacts poorly with utf8
455
# Check HEAP+HASH, binary collation
458
c char(10) character set utf8 collate utf8_bin,
459
unique key a using hash (c(1))
461
show create table t1;
462
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
464
insert into t1 values ('aa');
466
insert into t1 values ('aaa');
467
insert into t1 values ('б');
469
insert into t1 values ('бб');
471
insert into t1 values ('ббб');
472
select c as c_all from t1 order by c;
473
select c as c_a from t1 where c='a';
474
select c as c_a from t1 where c='б';
478
# Bug 4531: unique key prefix interacts poorly with utf8
479
# Check HEAP+BTREE, binary collation
482
c char(10) character set utf8 collate utf8_bin,
483
unique key a using btree (c(1))
485
show create table t1;
486
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
488
insert into t1 values ('aa');
490
insert into t1 values ('aaa');
491
insert into t1 values ('б');
493
insert into t1 values ('бб');
495
insert into t1 values ('ббб');
496
select c as c_all from t1 order by c;
497
select c as c_a from t1 where c='a';
498
select c as c_a from t1 where c='б';
502
# Bug 4531: unique key prefix interacts poorly with utf8
503
# Check BDB, binary collation
506
c char(10) character set utf8 collate utf8_bin,
509
insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f');
511
insert into t1 values ('aa');
513
insert into t1 values ('aaa');
514
insert into t1 values ('б');
516
insert into t1 values ('бб');
518
insert into t1 values ('ббб');
519
select c as c_all from t1 order by c;
520
select c as c_a from t1 where c='a';
521
select c as c_a from t1 where c='б';
525
# Bug#4594: column index make = failed for gbk, but like works
529
str varchar(255) character set utf8 not null,
532
INSERT INTO t1 VALUES ('str');
533
INSERT INTO t1 VALUES ('str2');
534
select * from t1 where str='str';
537
# Bug#4594: column index make = failed for gbk, but like works
541
str varchar(255) character set utf8 not null,
544
INSERT INTO t1 VALUES ('str');
545
INSERT INTO t1 VALUES ('str2');
546
select * from t1 where str='str';
549
# the same for HEAP+BTREE
553
str varchar(255) character set utf8 not null,
554
key str using btree (str(2))
556
INSERT INTO t1 VALUES ('str');
557
INSERT INTO t1 VALUES ('str2');
558
select * from t1 where str='str';
561
# the same for HEAP+HASH
565
str varchar(255) character set utf8 not null,
566
key str using hash (str(2))
568
INSERT INTO t1 VALUES ('str');
569
INSERT INTO t1 VALUES ('str2');
570
select * from t1 where str='str';
577
str varchar(255) character set utf8 not null,
580
INSERT INTO t1 VALUES ('str');
581
INSERT INTO t1 VALUES ('str2');
582
select * from t1 where str='str';
586
# Bug #5397: Crash with varchar binary and LIKE
588
CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8;
589
INSERT INTO t1 VALUES ('test');
590
SELECT a FROM t1 WHERE a LIKE '%te';
594
# Bug #5723: length(<varchar utf8 field>) returns varying results
598
subject varchar(255) character set utf8 collate utf8_unicode_ci,
599
p varchar(15) character set utf8
600
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
601
INSERT INTO t1 VALUES ('谷川俊二と申しますが、インターネット予約の会員登録をしましたところ、メールアドレスを間違えてしまい会員IDが受け取ることが出来ませんでした。間違えアドレスはtani-shun@n.vodafone.ne.jpを書き込みました。どうすればよいですか? その他、住所等は間違えありません。連絡ください。よろしくお願いします。m(__)m','040312-000057');
602
INSERT INTO t1 VALUES ('aaa','bbb');
603
SELECT length(subject) FROM t1;
604
SELECT length(subject) FROM t1 ORDER BY 1;
608
# Bug #5832 SELECT doesn't return records in some cases
611
id int unsigned NOT NULL auto_increment,
612
list_id smallint unsigned NOT NULL,
615
INDEX(list_id, term(4))
616
) ENGINE=MYISAM CHARSET=utf8;
617
INSERT INTO t1 SET list_id = 1, term = "letterc";
618
INSERT INTO t1 SET list_id = 1, term = "letterb";
619
INSERT INTO t1 SET list_id = 1, term = "lettera";
620
INSERT INTO t1 SET list_id = 1, term = "letterd";
621
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc");
622
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb");
623
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera");
624
SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd");
629
# Bug #6043 erratic searching for diacriticals in indexed MyISAM UTF-8 table
633
id int unsigned NOT NULL auto_increment,
634
list_id smallint unsigned NOT NULL,
637
INDEX(list_id, term(19))
638
) ENGINE=MyISAM CHARSET=utf8;
639
INSERT INTO t1 set list_id = 1, term = "test�test";
640
INSERT INTO t1 set list_id = 1, term = "testetest";
641
INSERT INTO t1 set list_id = 1, term = "test�test";
642
SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test");
643
SELECT id, term FROM t1 where (list_id = 1) AND (term = "testetest");
644
SELECT id, term FROM t1 where (list_id = 1) AND (term = "test�test");
648
# Bug #6019 SELECT tries to use too short prefix index on utf8 data
655
) engine=innodb character set=utf8;
656
insert into t1 values(1,'foo'),(2,'foobar');
657
select * from t1 where b like 'foob%';
658
alter table t1 engine=innodb;
659
select * from t1 where b like 'foob%';
663
# Test for calculate_interval_lengths() function
666
a enum('петя','вася','анюта') character set utf8 not null default 'анюта',
667
b set('петя','вася','анюта') character set utf8 not null default 'анюта'
669
create table t2 select concat(a,_utf8'') as a, concat(b,_utf8'')as b from t1;
670
show create table t2;
675
# Bug #6787 LIKE not working properly with _ and utf8 data
677
select 'c' like '\_' as want0;
680
# SUBSTR with negative offset didn't work with multi-byte strings
682
SELECT SUBSTR('вася',-2);
686
# Bug #7730 Server crash using soundex on an utf8 table
688
create table t1 (id integer, a varchar(100) character set utf8 collate utf8_unicode_ci);
689
insert into t1 values (1, 'Test');
690
select * from t1 where soundex(a) = soundex('Test');
691
select * from t1 where soundex(a) = soundex('TEST');
692
select * from t1 where soundex(a) = soundex('test');
696
# Bug#22638 SOUNDEX broken for international characters
698
select soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB);
699
select hex(soundex(_utf8 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB));
700
select soundex(_utf8 0xD091D092D093);
701
select hex(soundex(_utf8 0xD091D092D093));
704
SET collation_connection='utf8_general_ci';
705
-- source include/ctype_filesort.inc
706
-- source include/ctype_like_escape.inc
707
-- source include/ctype_german.inc
708
SET collation_connection='utf8_bin';
709
-- source include/ctype_filesort.inc
710
-- source include/ctype_like_escape.inc
713
# Bug #7874 CONCAT() gives wrong results mixing
714
# latin1 field and utf8 string literals
717
user varchar(255) NOT NULL default ''
718
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
719
INSERT INTO t1 VALUES ('one'),('two');
721
SELECT user, CONCAT('<', user, '>') AS c FROM t1;
726
# the same problem with the above, but with nested CONCATs
728
create table t1 (f1 varchar(1) not null) default charset utf8;
729
insert into t1 values (''), ('');
730
select concat(concat(_latin1'->',f1),_latin1'<-') from t1;
734
# Bug#8385: utf8_general_ci treats Cyrillic letters I and SHORT I as the same
736
select convert(_koi8r'�' using utf8) < convert(_koi8r'�' using utf8);
739
# Bugs#5980: NULL requires a characterset in a union
742
create table t1 (a varchar(10)) character set utf8;
743
insert into t1 values ('test');
744
select ifnull(a,'') from t1;
746
select repeat(_utf8'+',3) as h union select NULL;
747
select ifnull(NULL, _utf8'string');
750
# Bug#9509 Optimizer: wrong result after AND with comparisons
753
create table t1 (s1 char(5) character set utf8 collate utf8_lithuanian_ci);
754
insert into t1 values ('I'),('K'),('Y');
755
select * from t1 where s1 < 'K' and s1 = 'Y';
756
select * from t1 where 'K' > s1 and s1 = 'Y';
759
create table t1 (s1 char(5) character set utf8 collate utf8_czech_ci);
760
insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i');
761
select * from t1 where s1 > 'd' and s1 = 'CH';
762
select * from t1 where 'd' < s1 and s1 = 'CH';
763
select * from t1 where s1 = 'cH' and s1 <> 'ch';
764
select * from t1 where 'cH' = s1 and s1 <> 'ch';
768
# Bug#10714: Inserting double value into utf8 column crashes server
770
create table t1 (a varchar(255)) default character set utf8;
771
insert into t1 values (1.0);
775
# Bug#10253 compound index length and utf8 char set
776
# produces invalid query results
780
city varchar(20) not null,
782
) character set=utf8;
783
insert into t1 values (1,'Durban North');
784
insert into t1 values (2,'Durban');
785
select * from t1 where city = 'Durban';
786
select * from t1 where city = 'Durban ';
790
# Bug #11819 CREATE TABLE with a SET DEFAULT 0 and UTF8 crashes server.
793
create table t1 (x set('A', 'B') default 0) character set utf8;
795
create table t1 (x enum('A', 'B') default 0) character set utf8;
799
# Test for bug #11167: join for utf8 varchar value longer than 255 bytes
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 '',
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;
823
INSERT 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');
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;
834
INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25');
836
SELECT content, t2.msisdn FROM t1, t2 WHERE t1.msisdn = '1234567890';
841
# Bug#11591: CHAR column with utf8 does not work properly
842
# (more chars than expected)
844
create table t1 (a char(20) character set utf8);
845
insert into t1 values ('123456'),('андрей');
846
alter table t1 modify a char(2) character set utf8;
847
select char_length(a), length(a), a from t1 order by a;
852
# ESCAPE + LIKE do not work when the escape char is a multibyte one
855
select 'andre%' like 'andreñ%' escape 'ñ';
858
# Bugs#11754: SET NAMES utf8 followed by SELECT "A\\" LIKE "A\\" returns 0
861
select 'a\\' like 'a\\';
862
select 'aa\\' like 'a%\\';
864
create table t1 (a char(10), key(a)) character set utf8;
865
insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test");
866
select * from t1 where a like "abc%";
867
select * from t1 where a like concat("abc","%");
868
select * from t1 where a like "ABC%";
869
select * from t1 where a like "test%";
870
select * from t1 where a like "te_t";
871
select * from t1 where a like "%a%";
872
select * from t1 where a like "%abcd%";
873
select * from t1 where a like "%abc\d%";
878
# Bug#9557 MyISAM utf8 table crash
881
a varchar(255) NOT NULL default '',
883
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE utf8_general_ci;
884
insert into t1 values (_utf8 0xe880bd);
885
insert into t1 values (_utf8 0x5b);
886
select hex(a) from t1;
890
# Bug#13751 find_in_set: Illegal mix of collations
893
create table t1 (a varchar(255)) default charset=utf8;
894
select * from t1 where find_in_set('-1', a);
898
# Bug#13233: select distinct char(column) fails with utf8
900
create table t1 (a int);
901
insert into t1 values (48),(49),(50);
903
select distinct char(a) from t1;
907
# Bug#15581: COALESCE function truncates mutli-byte TINYTEXT values
909
CREATE TABLE t1 (t TINYTEXT CHARACTER SET utf8);
910
INSERT INTO t1 VALUES(REPEAT('a', 100));
911
CREATE TEMPORARY TABLE t2 SELECT COALESCE(t) AS bug FROM t1;
912
SELECT LENGTH(bug) FROM t2;
917
# Bug#17313: N'xxx' and _utf8'xxx' are not equivalent
919
CREATE TABLE t1 (item varchar(255)) default character set utf8;
920
INSERT INTO t1 VALUES (N'\\');
921
INSERT INTO t1 VALUES (_utf8'\\');
922
INSERT INTO t1 VALUES (N'Cote d\'Ivoire');
923
INSERT INTO t1 VALUES (_utf8'Cote d\'Ivoire');
924
SELECT item FROM t1 ORDER BY item;
928
# Bug#17705: Corruption of compressed index when index length changes between
933
DROP TABLE IF EXISTS t1;
934
CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8;
935
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb');
936
INSERT INTO t1 VALUES('uu');
938
INSERT INTO t1 VALUES('uU');
940
INSERT INTO t1 VALUES('uu');
942
INSERT INTO t1 VALUES('uuABC');
944
INSERT INTO t1 VALUES('UuABC');
946
INSERT INTO t1 VALUES('uuABC');
948
alter table t1 add b int;
949
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);
950
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',2);
951
delete from t1 where b=1;
952
INSERT INTO t1 VALUES('UUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1);
954
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);
955
INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',4);
956
delete from t1 where b=3;
957
INSERT INTO t1 VALUES('uUABCDEFGHIGKLMNOPRSTUVWXYZ̈bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3);
962
# Bug#20471 LIKE search fails with indexed utf8 char column
965
create table t1 (s1 char(5) character set utf8);
966
insert into t1 values
967
('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
968
create index it1 on t1 (s1);
969
select s1 as before_delete_general_ci from t1 where s1 like 'ペテ%';
970
delete from t1 where s1 = 'Y';
971
select s1 as after_delete_general_ci from t1 where s1 like 'ペテ%';
975
create table t1 (s1 char(5) character set utf8 collate utf8_unicode_ci);
976
insert into t1 values
977
('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
978
create index it1 on t1 (s1);
979
select s1 as before_delete_unicode_ci from t1 where s1 like 'ペテ%';
980
delete from t1 where s1 = 'Y';
981
select s1 as after_delete_unicode_ci from t1 where s1 like 'ペテ%';
985
create table t1 (s1 char(5) character set utf8 collate utf8_bin);
986
insert into t1 values
987
('a'),('b'),(null),('ペテルグル'),('ü'),('Y');
988
create index it1 on t1 (s1);
989
select s1 as before_delete_bin from t1 where s1 like 'ペテ%';
990
delete from t1 where s1 = 'Y';
991
select s1 as after_delete_bin from t1 where s1 like 'ペテ%';
994
# additional tests from duplicate bug#20744 MySQL return no result
997
create table t1 (a varchar(30) not null primary key)
998
engine=innodb default character set utf8 collate utf8_general_ci;
999
insert into t1 values ('あいうえおかきくけこさしすせそ');
1000
insert into t1 values ('さしすせそかきくけこあいうえお');
1001
select a as gci1 from t1 where a like 'さしすせそかきくけこあいうえお%';
1002
select a as gci2 from t1 where a like 'あいうえおかきくけこさしすせそ';
1006
create table t1 (a varchar(30) not null primary key)
1007
engine=innodb default character set utf8 collate utf8_unicode_ci;
1008
insert into t1 values ('あいうえおかきくけこさしすせそ');
1009
insert into t1 values ('さしすせそかきくけこあいうえお');
1010
select a as uci1 from t1 where a like 'さしすせそかきくけこあいうえお%';
1011
select a as uci2 from t1 where a like 'あいうえおかきくけこさしすせそ';
1015
create table t1 (a varchar(30) not null primary key)
1016
engine=innodb default character set utf8 collate utf8_bin;
1017
insert into t1 values ('あいうえおかきくけこさしすせそ');
1018
insert into t1 values ('さしすせそかきくけこあいうえお');
1019
select a as bin1 from t1 where a like 'さしすせそかきくけこあいうえお%';
1020
select a as bin2 from t1 where a like 'あいうえおかきくけこさしすせそ';
1026
# Bug#14896: Comparison with a key in a partial index over mb chararacter field
1030
CREATE TABLE t1 (id int PRIMARY KEY,
1031
a varchar(16) collate utf8_unicode_ci NOT NULL default '',
1033
f varchar(128) default 'XXX',
1035
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
1036
INSERT 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);
1042
SELECT id, a, b FROM t1;
1044
SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb';
1046
SELECT id, a FROM t1 WHERE a='bbbbbb';
1047
SELECT id, a FROM t1 WHERE a='bbbbbb' ORDER BY b;
1052
# Bug#16674: LIKE predicate for a utf8 character set column
1058
a CHAR(13) DEFAULT '',
1060
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
1061
INSERT 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');
1065
INSERT INTO t1 SELECT * FROM t1;
1068
a CHAR(13) DEFAULT '',
1070
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;
1072
INSERT 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');
1076
INSERT INTO t2 SELECT * FROM t2;
1078
SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4';
1079
SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4';
1081
EXPLAIN SELECT a FROM t1 WHERE a LIKE 'Käli Käli 2+4';
1082
EXPLAIN SELECT a FROM t1 WHERE a = 'Käli Käli 2+4';
1083
EXPLAIN SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4';
1084
EXPLAIN SELECT a FROM t2 WHERE a = 'Kali Kali 2+4';
1089
a char(255) DEFAULT '',
1091
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
1092
INSERT INTO t1 VALUES ('Käli Käli 2-4');
1093
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
1094
INSERT INTO t1 VALUES ('Käli Käli 2-4');
1095
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
1099
a char(255) DEFAULT ''
1100
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;
1101
INSERT INTO t1 VALUES ('Käli Käli 2-4');
1102
INSERT INTO t1 VALUES ('Käli Käli 2-4');
1103
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
1104
ALTER TABLE t1 ADD KEY (a(10));
1105
SELECT * FROM t1 WHERE a LIKE 'Käli Käli 2%';
1109
# Bug#18359: LIKE predicate for a 'utf8' text column with a partial index
1110
# (see bug #16674 as well)
1116
id int(11) NOT NULL default '0',
1117
tid int(11) NOT NULL default '0',
1119
INDEX idx(tid, val(10))
1120
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
1122
INSERT 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');
1129
SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNY ADSL';
1130
SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL';
1131
SELECT * FROM t1 WHERE tid=72 and val LIKE '%VOLN� ADSL';
1133
ALTER TABLE t1 DROP KEY idx;
1134
ALTER TABLE t1 ADD KEY idx (tid,val(11));
1136
SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLN� ADSL';
1141
# Bug 20709: problem with utf8 fields in temporary tables
1144
create table t1(a char(200) collate utf8_unicode_ci NOT NULL default '')
1145
default charset=utf8 collate=utf8_unicode_ci;
1146
insert into t1 values (unhex('65')), (unhex('C3A9')), (unhex('65'));
1147
-- disable_query_log
1148
-- disable_result_log
1150
-- enable_result_log
1152
explain select distinct a from t1;
1153
select distinct a from t1;
1154
explain select a from t1 group by a;
1155
select a from t1 group by a;
1159
# Bug #20204: "order by" changes the results returned
1162
create table t1(a char(10)) default charset utf8;
1163
insert into t1 values ('123'), ('456');
1164
-- disable_query_log
1165
-- disable_result_log
1167
-- enable_result_log
1170
select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
1171
select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1;
1175
# Bug #34349: Passing invalid parameter to CHAR() in an ORDER BY causes
1179
SET CHARACTER SET utf8;
1180
SHOW VARIABLES LIKE 'character\_set\_%';
1181
CREATE DATABASE crashtest DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
1183
CREATE TABLE crashtest (crash char(10)) DEFAULT CHARSET=utf8;
1184
INSERT INTO crashtest VALUES ('35'), ('36'), ('37');
1185
SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8);
1186
INSERT INTO crashtest VALUES ('-1000');
1187
-- disable_query_log
1188
-- disable_result_log
1189
ANALYZE TABLE crashtest;
1190
-- enable_result_log
1192
EXPLAIN SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8);
1193
SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8);
1194
DROP TABLE crashtest;
1195
DROP DATABASE crashtest;
1197
SET CHARACTER SET default;
1202
# Test for bug #11484: wrong results for a DISTINCT varchar column in uft8.
1205
CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8;
1206
INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa');
1209
SELECT DISTINCT id FROM t1;
1210
SELECT DISTINCT id FROM t1 ORDER BY id;
1215
# Bug#20095 Changing length of VARCHAR field with UTF8
1216
# collation does not truncate values
1219
a varchar(26) not null
1220
) default character set utf8;
1221
insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz');
1223
# varchar to varchar
1224
alter table t1 change a a varchar(20) character set utf8 not null;
1227
alter table t1 change a a char(15) character set utf8 not null;
1230
alter table t1 change a a char(10) character set utf8 not null;
1233
alter table t1 change a a varchar(5) character set utf8 not null;
1238
# Check that do_varstring2_mb produces a warning
1241
a varchar(4000) not null
1242
) default character set utf8;
1243
insert into t1 values (repeat('a',4000));
1244
alter table t1 change a a varchar(3000) character set utf8 not null;
1245
select length(a) from t1;
1250
# Bug#10504: Character set does not support traditional mode
1251
# Bug#14146: CHAR(...USING ...) and CONVERT(CHAR(...) USING...)
1252
# produce different results
1256
select hex(char(1 using utf8));
1257
select char(0xd1,0x8f using utf8);
1258
select char(0xd18f using utf8);
1259
select char(53647 using utf8);
1260
# incorrect value: return with warning
1261
select char(0xff,0x8f using utf8);
1262
select convert(char(0xff,0x8f) using utf8);
1263
# incorrect value in strict mode: return NULL with "Error" level warning
1264
set sql_mode=traditional;
1265
select char(0xff,0x8f using utf8);
1266
select char(195 using utf8);
1267
select char(196 using utf8);
1268
select char(2557 using utf8);
1269
select convert(char(0xff,0x8f) using utf8);
1272
# Check convert + char + using
1274
select hex(convert(char(2557 using latin1) using utf8));
1277
# char() without USING returns "binary" by default, any argument is ok
1279
select hex(char(195));
1280
select hex(char(196));
1281
select hex(char(2557));
1286
# Bug#12891: UNION doesn't return DISTINCT result for multi-byte characters
1289
create table t1 (a char(1)) default character set utf8;
1290
create table t2 (a char(1)) default character set utf8;
1291
insert into t1 values('a'),('a'),(0xE38182),(0xE38182);
1292
insert into t1 values('i'),('i'),(0xE38184),(0xE38184);
1293
select * from t1 union distinct select * from t2;
1298
# Bug#12371: executing prepared statement fails (illegal mix of collations)
1301
create table t1 (a char(10), b varchar(10));
1302
insert into t1 values ('bar','kostja');
1303
insert into t1 values ('kostja','bar');
1304
prepare my_stmt from "select * from t1 where a=?";
1306
execute my_stmt using @a;
1308
execute my_stmt using @a;
1310
execute my_stmt using @a;
1311
drop table if exists t1;
1315
# Bug#21505 Create view - illegal mix of collation for operation 'UNION'
1318
drop table if exists t1;
1319
drop view if exists v1, v2;
1322
create table t1(col1 varchar(12) character set utf8 collate utf8_unicode_ci);
1323
insert into t1 values('t1_val');
1324
create view v1 as select 'v1_val' as col1;
1325
select coercibility(col1), collation(col1) from v1;
1326
create view v2 as select col1 from v1 union select col1 from t1;
1327
select coercibility(col1), collation(col1)from v2;
1329
create view v1 as select 'v1_val' collate utf8_swedish_ci as col1;
1330
select coercibility(col1), collation(col1) from v1;
1331
create view v2 as select col1 from v1 union select col1 from t1;
1332
select coercibility(col1), collation(col1) from v2;
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
1342
create table t1 (a varchar(10) character set latin1, b int);
1343
insert into t1 values ('a',1);
1344
select concat(a, if(b>10, N'x', N'y')) from t1;
1346
select concat(a, if(b>10, N'æ', N'ß')) from t1;
1349
# Conversion tests for character set introducers
1351
create table t1 (a varchar(10) character set latin1, b int);
1352
insert into t1 values ('a',1);
1353
select concat(a, if(b>10, _utf8'x', _utf8'y')) from t1;
1355
select concat(a, if(b>10, _utf8'æ', _utf8'ß')) from t1;
1358
# Conversion tests for introducer + HEX string
1360
create table t1 (a varchar(10) character set latin1, b int);
1361
insert into t1 values ('a',1);
1362
select concat(a, if(b>10, _utf8 0x78, _utf8 0x79)) from t1;
1364
select concat(a, if(b>10, _utf8 0xC3A6, _utf8 0xC3AF)) from t1;
1367
# Conversion tests for "text_literal TEXT_STRING_literal" syntax structure
1369
create table t1 (a varchar(10) character set latin1, b int);
1370
insert into t1 values ('a',1);
1371
select concat(a, if(b>10, 'x' 'x', 'y' 'y')) from t1;
1373
select concat(a, if(b>10, 'x' 'æ', 'y' 'ß')) from t1;
1377
# Bug#19960: Inconsistent results when joining
1378
# InnoDB tables using partial UTF8 indexes
1382
colA int(11) NOT NULL,
1383
colB varchar(255) character set utf8 NOT NULL,
1385
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
1386
INSERT INTO t1 (colA, colB) VALUES (1, 'foo'), (2, 'foo bar');
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;
1392
INSERT INTO t2 (colA, colB) VALUES (1, 'foo'),(2, 'foo bar');
1393
SELECT * FROM t1 JOIN t2 ON t1.colA=t2.colA AND t1.colB=t2.colB
1398
# Bug#29205: truncation of UTF8 values when the UNION statement
1399
# forces collation to the binary charset
1402
SELECT 'н1234567890' UNION SELECT _binary '1';
1403
SELECT 'н1234567890' UNION SELECT 1;
1405
SELECT '1' UNION SELECT 'н1234567890';
1406
SELECT 1 UNION SELECT 'н1234567890';
1408
CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8;
1409
CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT);
1411
INSERT INTO t1 (c) VALUES ('н1234567890');
1412
INSERT INTO t2 (b, i) VALUES ('1', 1);
1414
SELECT c FROM t1 UNION SELECT b FROM t2;
1415
SELECT c FROM t1 UNION SELECT i FROM t2;
1417
SELECT b FROM t2 UNION SELECT c FROM t1;
1418
SELECT i FROM t2 UNION SELECT c FROM t1;
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
1426
set sql_mode=traditional;
1427
select hex(char(0xFF using utf8));
1428
select hex(convert(0xFF using utf8));
1429
--error ER_INVALID_CHARACTER_STRING
1430
select hex(_utf8 0x616263FF);
1431
--error ER_INVALID_CHARACTER_STRING
1432
select hex(_utf8 X'616263FF');
1433
--error ER_INVALID_CHARACTER_STRING
1434
select hex(_utf8 B'001111111111');
1435
--error ER_INVALID_CHARACTER_STRING
1436
select (_utf8 X'616263FF');
1437
set sql_mode=default;
1438
select hex(char(0xFF using utf8));
1439
select hex(convert(0xFF using utf8));
1440
--error ER_INVALID_CHARACTER_STRING
1441
select hex(_utf8 0x616263FF);
1442
--error ER_INVALID_CHARACTER_STRING
1443
select hex(_utf8 X'616263FF');
1444
--error ER_INVALID_CHARACTER_STRING
1445
select hex(_utf8 B'001111111111');
1446
--error ER_INVALID_CHARACTER_STRING
1447
select (_utf8 X'616263FF');
1450
--echo # Bug#44131 Binary-mode "order by" returns records in incorrect order for UTF-8 strings
1452
CREATE TABLE t1 (id int not null primary key, name varchar(10)) character set utf8;
1453
INSERT INTO t1 VALUES
1454
(2,'一二三01'),(3,'一二三09'),(4,'一二三02'),(5,'一二三08'),
1455
(6,'一二三11'),(7,'一二三91'),(8,'一二三21'),(9,'一二三81');
1456
SELECT * FROM t1 ORDER BY BINARY(name);
1460
# Bug #36772: When using UTF8, CONVERT with GROUP BY returns truncated results
1462
CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL);
1463
INSERT INTO t1 VALUES (70000, 1092), (70001, 1085), (70002, 1065);
1464
SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;
1465
SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1;
1466
ALTER TABLE t1 ADD UNIQUE (b);
1467
SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;
1469
SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b;
1470
ALTER TABLE t1 ADD INDEX (b);
1471
SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) from t1 GROUP BY b;
1474
--echo End of 5.0 tests
1478
# Bug #57272: crash in rpad() when using utf8
1480
SELECT LENGTH(RPAD(0.0115E88, 61297, _utf8'яэюя'));
1481
SELECT LENGTH(RPAD(0.0115E88, 61297, _utf8'йцуя'));
1482
SELECT HEX(RPAD(0x20, 2, _utf8 0xD18F));
1483
SELECT HEX(RPAD(0x20, 4, _utf8 0xD18F));
1484
SELECT HEX(LPAD(0x20, 2, _utf8 0xD18F));
1485
SELECT HEX(LPAD(0x20, 4, _utf8 0xD18F));
1487
SELECT HEX(RPAD(_utf8 0xD18F, 3, 0x20));
1488
SELECT HEX(LPAD(_utf8 0xD18F, 3, 0x20));
1490
SELECT HEX(INSERT(_utf8 0xD18F, 2, 1, 0x20));
1491
SELECT HEX(INSERT(_utf8 0xD18FD18E, 2, 1, 0x20));
1494
--echo # Bug#11752408 - 43593: DUMP/BACKUP/RESTORE/UPGRADE TOOLS FAILS BECAUSE OF UTF8_GENERAL_CI
1496
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_general_mysql500_ci);
1497
INSERT INTO t1 VALUES ('a'),('r'),('s'),(_latin1 0xDF),(_latin1 0xF7),('t'),('z');
1498
SELECT * FROM t1 ORDER BY a;
1499
SELECT a, COUNT(*) FROM t1 GROUP BY a;
1502
--echo End of 5.1 tests
1505
--echo Start of 5.4 tests
1508
# WL#1213: utf8mb3 is an alias for utf8
1511
SHOW VARIABLES LIKE 'character_set_results%';
1512
CREATE TABLE t1 (a CHAR CHARACTER SET utf8mb3 COLLATE utf8mb3_bin);
1513
SHOW CREATE TABLE t1;
1515
SELECT _utf8mb3'test';
1518
# Bug#26180: Can't add columns to tables created with utf8 text indexes
1521
clipid INT NOT NULL,
1523
PRIMARY KEY (clipid),
1525
) CHARACTER SET=utf8;
1526
ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid;
1527
SHOW CREATE TABLE t1;
1531
# Bug#26474: Add Sinhala script (Sri Lanka) collation to MySQL
1534
DROP TABLE IF EXISTS t1;
1537
predicted_order int NOT NULL,
1538
utf8_encoding VARCHAR(10) NOT NULL
1539
) CHARACTER SET utf8;
1540
INSERT 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');
1541
SELECT predicted_order, hex(utf8_encoding) FROM t1 ORDER BY utf8_encoding COLLATE utf8_sinhala_ci;
1544
# Postfix for Bug#26474
1546
SET NAMES utf8 COLLATE utf8_sinhala_ci;
1547
CREATE TABLE t1 (s1 VARCHAR(10) COLLATE utf8_sinhala_ci);
1548
INSERT INTO t1 VALUES ('a'),('ae'),('af');
1549
SELECT s1,hex(s1) FROM t1 ORDER BY s1;
1550
SELECT * FROM t1 ORDER BY s1;
1553
--echo End of 5.4 tests
1556
--echo # Start of 5.5 tests
1560
--echo # Bug#52520 Difference in tinytext utf column metadata
1563
s1 TINYTEXT CHARACTER SET utf8,
1564
s2 TEXT CHARACTER SET utf8,
1565
s3 MEDIUMTEXT CHARACTER SET utf8,
1566
s4 LONGTEXT CHARACTER SET utf8
1569
SET NAMES utf8, @@character_set_results=NULL;
1570
SELECT *, HEX(s1) FROM t1;
1572
SELECT *, HEX(s1) FROM t1;
1574
SELECT *, HEX(s1) FROM t1;
1576
CREATE TABLE t2 AS SELECT CONCAT(s1) FROM t1;
1577
SHOW CREATE TABLE t2;
1582
--source include/ctype_numconv.inc
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.
1589
CREATE TABLE t1 (a INT);
1590
INSERT INTO t1 VALUES (0), (0), (1), (0), (0);
1591
--error ER_DUP_ENTRY
1592
SELECT COUNT(*) FROM t1, t1 t2
1593
GROUP BY INSERT('', t2.a, t1.a, (@@global.max_binlog_size));
1597
--echo # Bug#11764503 (Bug#57341) Query in EXPLAIN EXTENDED shows wrong characters
1599
# Emulate utf8 client erroneously started with --default-character-set=latin1,
1600
# as in the bug report. EXPLAIN output should still be pretty readable
1602
EXPLAIN EXTENDED SELECT 'abcdÁÂÃÄÅ', _latin1'abcdÁÂÃÄÅ', _utf8'abcdÁÂÃÄÅ' AS u;
1605
EXPLAIN EXTENDED SELECT 'abcdÁÂÃÄÅ', _latin1'abcdÁÂÃÄÅ', _utf8'abcdÁÂÃÄÅ';
1608
--echo # Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT
1612
SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l
1613
FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body
1615
SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1
1620
--echo # End of 5.5 tests
1624
--echo # Start of 5.6 tests
1628
--echo # WL#3664 WEIGHT_STRING
1632
--source include/weight_string.inc
1633
--source include/weight_string_euro.inc
1634
--source include/weight_string_l1.inc
1636
set @@collation_connection=utf8_bin;
1637
--source include/weight_string.inc
1638
--source include/weight_string_euro.inc
1639
--source include/weight_string_l1.inc
1642
--echo # Checking strnxfrm() with odd length
1644
set max_sort_length=5;
1645
select @@max_sort_length;
1646
create table t1 (a varchar(128) character set utf8 collate utf8_general_ci);
1647
insert into t1 values ('a'),('b'),('c');
1648
select * from t1 order by a;
1649
alter table t1 modify a varchar(128) character set utf8 collate utf8_bin;
1650
select * from t1 order by a;
1652
set max_sort_length=default;
1655
--echo # Bugs#12635232: VALGRIND WARNINGS: IS_IPV6, IS_IPV4, INET6_ATON,
1656
--echo # INET6_NTOA + MULTIBYTE CHARSET.
1660
--source include/ctype_inet.inc
1663
--echo # End of 5.6 tests