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