summaryrefslogtreecommitdiff
path: root/mysql-test/main/ctype_uca.test
blob: 15a945fde6d62f6b2df0f560c66e961236b98781 (plain)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
-- source include/have_ucs2.inc
-- source include/have_utf8mb4.inc

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

#
# Test Unicode collations.
#
set names utf8; 

#
# Check trailing spaces
#
set collation_connection=utf8_unicode_ci;

select 'a' = 'a', 'a' = 'a ', 'a ' = 'a';

select 'a\t' = 'a' , 'a\t' < 'a' , 'a\t' > 'a';
select 'a\t' = 'a ', 'a\t' < 'a ', 'a\t' > 'a ';

select 'a' = 'a\t', 'a' < 'a\t', 'a' > 'a\t';
select 'a ' = 'a\t', 'a ' < 'a\t', 'a ' > 'a\t';

select 'a  a' > 'a', 'a  \t' < 'a';

#
# Bug #6787 LIKE not working properly with _ and utf8 data
#
select 'c' like '\_' as want0; 

#
# Bug #5679 utf8_unicode_ci LIKE--trailing % doesn't equal zero characters
#
CREATE TABLE t (
  c char(20) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
INSERT INTO t VALUES ('a'),('ab'),('aba');
ALTER TABLE t ADD INDEX (c);
SELECT c FROM t WHERE c LIKE 'a%';
#should find 3 rows but only found 2
DROP TABLE t;

create table t1 (c1 char(10) character set utf8 collate utf8_bin);

--source include/ctype_unicode_latin.inc

select group_concat(c1 order by c1) from t1 group by c1 collate utf8_unicode_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_icelandic_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_latvian_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_romanian_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_slovenian_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_polish_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_estonian_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_spanish_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_swedish_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_turkish_ci;

select group_concat(c1 order by c1) from t1 group by c1 collate utf8_czech_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_danish_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_lithuanian_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_slovak_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_spanish2_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_roman_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_esperanto_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_hungarian_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_croatian_mysql561_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_croatian_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_german2_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_unicode_520_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_vietnamese_ci;
select group_concat(c1 order by c1) from t1 group by c1 collate utf8_thai_520_w2;

ALTER TABLE t1 CONVERT TO CHARACTER SET ucs2 COLLATE ucs2_bin;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_unicode_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_icelandic_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_latvian_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_romanian_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_slovenian_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_polish_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_estonian_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_spanish_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_swedish_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_turkish_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_czech_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_danish_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_lithuanian_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_slovak_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_spanish2_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_roman_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_esperanto_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_hungarian_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_croatian_mysql561_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_croatian_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_german2_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_unicode_520_ci;
SELECT GROUP_CONCAT(c1 ORDER BY c1) FROM t1 GROUP BY c1 COLLATE ucs2_vietnamese_ci;

drop table t1;

#
# Bug#5324
#
SET NAMES utf8;
#test1
CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE utf8_general_ci, INDEX (c));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B403B11F770308 USING utf8));
#Check one row
SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
COLLATE utf8_general_ci;
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B4 USING utf8));
#Check two rows
SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
COLLATE utf8_general_ci ORDER BY c;
DROP TABLE t1;
#test2
CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE ucs2_unicode_ci, INDEX (c));
INSERT INTO t1 VALUES (_ucs2 0x039C03C903B403B11F770308);
#Check one row
SELECT * FROM t1 WHERE c LIKE _ucs2 0x039C0025 COLLATE ucs2_unicode_ci;
INSERT INTO t1 VALUES (_ucs2 0x039C03C903B4);
#Check two rows
SELECT * FROM t1 WHERE c LIKE _ucs2 0x039C0025
COLLATE ucs2_unicode_ci ORDER BY c;
DROP TABLE t1;
#test 3
CREATE TABLE t1 (c varchar(255) NOT NULL COLLATE utf8_unicode_ci, INDEX (c));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B403B11F770308 USING utf8));
#Check one row row
SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8) COLLATE utf8_unicode_ci;
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x039C03C903B4 USING utf8));
#Check two rows
SELECT * FROM t1 WHERE c LIKE CONVERT(_ucs2 0x039C0025 USING utf8)
COLLATE utf8_unicode_ci ORDER BY c;
DROP TABLE t1;

CREATE TABLE t1 (
  col1 CHAR(32) CHARACTER SET utf8 NOT NULL
);

INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0041004100410627 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0041004100410628 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0041004100410647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0041004100410648 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0633064A0651062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062D06330646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0642064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06320627062F0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062806310627064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064706450647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F062706460634062C0648064A06270646064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A90647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A06270631064A062E USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062706460642064406270628 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A0631062706460650 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627062F064806270631062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280631062706480646200C06310627 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062E064806270646062F0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0648 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A062D062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0623062B064A0631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06220646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0642063106270631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06AF06310641062A0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270646062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0634062E0635064A0651062A064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628062706310632 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270633062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063906A90633 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270648060C USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F0631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062D062F0648062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628064A0633062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0648067E0646062C USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06330627064406AF064A060C USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063306270644 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606450627064A0646062F0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A06280631064A0632 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645062C06440633 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280648062F060C USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628064A0646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06350641062D0627062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A0646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A9062A06270628 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x068606340645 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062E06480631062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0686064706310647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06420648064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450635064506510645 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06310627 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0646063406270646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645064A200C062F0647062F060C USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0647063106860646062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06390645064400BB USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A9064806340634 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064706500646064A064606AF USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627062D063306270646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064A062706310634062706370631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450646062A06340631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0634062F0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F0633062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A064806270646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0647064506270646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064806510644 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0634062E064A0635 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F0627062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280627 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A064106270648062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062D06270644062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A064106A906510631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063A064406280647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F06270631062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064A06A9064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270632 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063106470628063106270646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606470636062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064506340631064806370647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A063106270646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0646064A0632 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064A06A9 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645062D064206510642 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0637063106270632 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064106310647064606AF USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0645062F06510646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A063106270646064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280648062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A90627063106470627064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270648 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0639063106350647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064506480631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0633064A06270633064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064A063106270646060C USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062D064806320647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063906440645 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F062706460634 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450642062706440627062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F064A06AF0631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0648064A06980647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0646062706450647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064506480631062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628062D062B USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0628063106310633064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606480634062A0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A064606470627 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0622064606860647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F064806310647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064206270645062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x067E0631062F062706320645 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0698062706460648064A0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0648064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F06390648062A0650 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063306500631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F0646064A0633064F0646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063106270633 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0647064A0626062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0639064406480645200C063406310642064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280639062F0627064B USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645062F063106330647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062206410631064A06420627064A064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F06270646063406AF06270647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06440646062F0646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x067E064A06480633062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0647064606AF06270645064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x067E0633 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0622063A06270632 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062C064606AF USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062C064706270646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F064806510645 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063406470631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A9064506280631064A062C USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450646062A06420644 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A90631062F0646062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06470645 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06310641062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06220646062C0627 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064506270646062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0627 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062706A9062A06280631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606380631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F06480644062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F06480628062706310647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606330628062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645063306270639062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0634062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06480632064A0631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0645062E062A06270631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06330641064A0631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627064606AF0644064A0633 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0642064A200C06320627062F0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280627063206AF0634062A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0647064506330631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06220644064506270646064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06270634 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06220645062F0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A906270631064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x067E0631062F0627062E062A0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063906440645064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x0627062F0628064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062D062F0651 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064606280648062F060C USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06480644064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x063906480636060C USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06340627064A062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064506470645 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062A0631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06220646060C USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06470645063306310634 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A90627064606480646 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062E0627064606480627062F06AF064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06AF06310645064A USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280648062C0648062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062206480631062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F0648 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06A90627064506440627064B USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x064A06A9062F064A06AF0631 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06AF USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x062F064406280633062A0647 USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06280648062F0646062F USING utf8));
INSERT INTO t1 VALUES (CONVERT(_ucs2 0x06450647064506270646 USING utf8));
SELECT HEX(CONVERT(col1 USING ucs2)) FROM t1 ORDER BY col1 COLLATE utf8_persian_ci, col1 COLLATE utf8_bin;
DROP TABLE t1;

#
# Test all characters that appear in utf8_persia_ci tailoring
#
CREATE TABLE t1 (
  a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_persian_ci,
  offs INT NOT NULL
);
INSERT INTO t1 VALUES
(_ucs2 0x066D, 1),(_ucs2 0x064E, 2),(_ucs2 0xFE76, 3),(_ucs2 0xFE77, 4),
(_ucs2 0x0650, 5),(_ucs2 0xFE7A, 6),(_ucs2 0xFE7B, 7),(_ucs2 0x064F, 8),
(_ucs2 0xFE78, 9),(_ucs2 0xFE79,10),(_ucs2 0x064B,11),(_ucs2 0xFE70,12),
(_ucs2 0xFE71,13),(_ucs2 0x064D,14),(_ucs2 0xFE74,15),(_ucs2 0x064C,16),
(_ucs2 0xFE72,17),

(_ucs2 0xFE7F, 1),(_ucs2 0x0653, 2),(_ucs2 0x0654, 3),(_ucs2 0x0655, 4),
(_ucs2 0x0670, 5),

(_ucs2 0x0669, 1),(_ucs2 0x0622, 2),(_ucs2 0x0627, 3),(_ucs2 0x0671, 4),
(_ucs2 0x0621, 5),(_ucs2 0x0623, 6),(_ucs2 0x0625, 7),(_ucs2 0x0624, 8),
(_ucs2 0x0626, 9),

(_ucs2 0x0642, 1),(_ucs2 0x06A9, 2),(_ucs2 0x0643, 3),

(_ucs2 0x0648, 1),(_ucs2 0x0647, 2),(_ucs2 0x0629, 3),(_ucs2 0x06C0, 4),
(_ucs2 0x06CC, 5),(_ucs2 0x0649, 6),(_ucs2 0x064A, 7),

(_ucs2 0xFE80, 1),(_ucs2 0xFE81, 2),(_ucs2 0xFE82, 3),(_ucs2 0xFE8D, 4),
(_ucs2 0xFE8E, 5),(_ucs2 0xFB50, 6),(_ucs2 0xFB51, 7),(_ucs2 0xFE80, 8),
(_ucs2 0xFE83, 9),(_ucs2 0xFE84,10),(_ucs2 0xFE87,11),(_ucs2 0xFE88,12),
(_ucs2 0xFE85,13),(_ucs2 0xFE86,14),(_ucs2 0x0689,16),(_ucs2 0x068A,17),

(_ucs2 0xFEAE, 1),(_ucs2 0xFDFC, 2),

(_ucs2 0xFED8, 1),(_ucs2 0xFB8E, 2),(_ucs2 0xFB8F, 3),(_ucs2 0xFB90, 4),
(_ucs2 0xFB91, 5),(_ucs2 0xFED9, 6),(_ucs2 0xFEDA, 7),(_ucs2 0xFEDB, 8),
(_ucs2 0xFEDC, 9),

(_ucs2 0xFEEE, 1),(_ucs2 0xFEE9, 2),(_ucs2 0xFEEA, 3),(_ucs2 0xFEEB, 4),
(_ucs2 0xFEEC, 5),(_ucs2 0xFE93, 6),(_ucs2 0xFE94, 7),(_ucs2 0xFBA4, 8),
(_ucs2 0xFBA5, 9),(_ucs2 0xFBFC,10),(_ucs2 0xFBFD,11),(_ucs2 0xFBFE,12),
(_ucs2 0xFBFF,13),(_ucs2 0xFEEF,14),(_ucs2 0xFEF0,15),(_ucs2 0xFEF1,16),
(_ucs2 0xFEF2,17),(_ucs2 0xFEF3,18),(_ucs2 0xFEF4,19),(_ucs2 0xFEF5,20),
(_ucs2 0xFEF6,21),(_ucs2 0xFEF7,22),(_ucs2 0xFEF8,23),(_ucs2 0xFEF9,24),
(_ucs2 0xFEFA,25),(_ucs2 0xFEFB,26),(_ucs2 0xFEFC,27);

SELECT HEX(CONVERT(a USING ucs2)), offs, hex(weight_string(a)), a
FROM t1 ORDER BY a, offs, BINARY a;
DROP TABLE t1;

SET @test_character_set= 'utf8';
SET @test_collation= 'utf8_swedish_ci';
-- source include/ctype_common.inc

#
# Bug 7111 server crashes when regexp is used
#
create table t1 (a varchar(1)) character set utf8 collate utf8_estonian_ci;
insert into t1 values ('A'),('B'),('C'),('a'),('b'),('c');
select a, a regexp '[a]' from t1 order by binary a;
drop table t1;

SET collation_connection='utf8_unicode_ci';
-- source include/ctype_filesort.inc
-- source include/ctype_like_escape.inc
-- source include/ctype_german.inc

# End of 4.1 tests

#
# Check UPPER/LOWER changeing length
#
# Result shorter than argument
CREATE TABLE t1 (id int, a varchar(30) character set utf8);
INSERT INTO t1 VALUES (1, _ucs2 0x01310069), (2, _ucs2 0x01310131);
INSERT INTO t1 VALUES (3, _ucs2 0x00690069), (4, _ucs2 0x01300049);
INSERT INTO t1 VALUES (5, _ucs2 0x01300130), (6, _ucs2 0x00490049);
SELECT a, length(a) la, @l:=lower(a) l, length(@l) ll, @u:=upper(a) u, length(@u) lu
FROM t1 ORDER BY id;
ALTER TABLE t1 MODIFY a VARCHAR(30) character set utf8 collate utf8_turkish_ci;
SELECT a, length(a) la, @l:=lower(a) l, length(@l) ll, @u:=upper(a) u, length(@u) lu
FROM t1 ORDER BY id;
DROP TABLE t1;

#
# Bug #27079 Crash while grouping empty ucs2 strings
#
CREATE TABLE t1 (
 c1 text character set ucs2 collate ucs2_polish_ci NOT NULL
) ENGINE=MyISAM;
insert into t1 values (''),('a');
SELECT COUNT(*), c1 FROM t1 GROUP BY c1;
DROP TABLE IF EXISTS t1;

#
# Bug#27345 Incorrect data returned when range-read from utf8_danish_ci indexes
#
set names utf8;
create table t1 (
  a varchar(255),
  key a(a)
) character set utf8 collate utf8_danish_ci;
insert into t1 values ('åaaaa'),('ååaaa'),('aaaaa');
select a as like_a from t1 where a like 'a%';
select a as like_aa from t1 where a like 'aa%';
select a as like_aaa from t1 where a like 'aaa%';
select a as like_aaaa from t1 where a like 'aaaa%';
select a as like_aaaaa from t1 where a like 'aaaaa%';
alter table t1 convert to character set ucs2 collate ucs2_danish_ci;
select a as like_a from t1 where a like 'a%';
select a as like_aa from t1 where a like 'aa%';
select a as like_aaa from t1 where a like 'aaa%';
select a as like_aaaa from t1 where a like 'aaaa%';
select a as like_aaaaa from t1 where a like 'aaaaa%';
drop table t1;

create table t1 (
  a varchar(255),
  key(a)
) character set utf8 collate utf8_spanish2_ci;
insert into t1 values ('aaaaa'),('lllll'),('zzzzz');
select a as like_l from t1 where a like 'l%';
select a as like_ll from t1 where a like 'll%';
select a as like_lll from t1 where a like 'lll%';
select a as like_llll from t1 where a like 'llll%';
select a as like_lllll from t1 where a like 'lllll%';
alter table t1 convert to character set ucs2 collate ucs2_spanish2_ci;
select a as like_l from t1 where a like 'l%';
select a as like_ll from t1 where a like 'll%';
select a as like_lll from t1 where a like 'lll%';
select a as like_llll from t1 where a like 'llll%';
select a as like_lllll from t1 where a like 'lllll%';
drop table t1;

create table t1 (
  a varchar(255),
  key a(a)
) character set utf8 collate utf8_czech_ci;
# In Czech 'ch' is a single letter between 'h' and 'i'
insert into t1 values
('b'),('c'),('d'),('e'),('f'),('g'),('h'),('ch'),('i'),('j');
select * from t1 where a like 'c%';
alter table t1 convert to character set ucs2 collate ucs2_czech_ci;
select * from t1 where a like 'c%';
drop table t1;

set collation_connection=ucs2_unicode_ci;
-- source include/ctype_regex.inc
-- source include/ctype_like_range_f1f2.inc
set names utf8;

-- echo End for 5.0 tests
--echo End of 5.1 tests

--echo #
--echo # Start of 5.5 tests
--echo #
#
# Test my_like_range and contractions
#
SET collation_connection=utf8_czech_ci;
--source include/ctype_czech.inc
--source include/ctype_like_ignorable.inc
SET collation_connection=ucs2_czech_ci;
--source include/ctype_czech.inc
--source include/ctype_like_ignorable.inc

create table t1 (a int, c1 varchar(200) collate utf8_croatian_mysql561_ci, key (c1));
insert into t1 values (1,'=> DZ'),(2,'=> Dz'),(3,'=> dz'),(4,'=> dZ');
insert into t1 values (5,'=> DŽ'),(6,'=> Dž'),(7,'=> dž'),(8,'=> dŽ');
insert into t1 values (9,'=> dž'),(10,'=> DŽ');
select c1 from t1;
select concat(c1) from t1 order by c1;
select * from t1 where c1 like '=> d%';
select * from t1 where concat(c1) like '=> d%';
select * from t1 where c1 like '=> dz%';
select * from t1 where concat(c1) like '=> dz%';
select * from t1 where c1 like '=> dž%';
select * from t1 where concat(c1) like '=> dž%';
select * from t1 where c1 = '=> dž';
select * from t1 where concat(c1) = '=> dž';
drop table t1;


--echo #
--echo # MDEV-7649 wrong result when comparing utf8 column with an invalid literal
--echo #

SET NAMES utf8 COLLATE utf8_unicode_ci;
--let ENGINE=MyISAM
--source include/ctype_utf8_ilseq.inc
--let ENGINE=HEAP
--source include/ctype_utf8_ilseq.inc

--echo #
--echo # End of 5.5 tests
--echo #


--echo #
--echo # Start of 5.6 tests
--echo #

--echo #
--echo # WL#3664 WEIGHT_STRING
--echo #

set collation_connection=ucs2_unicode_ci;
--source include/weight_string.inc
--source include/weight_string_euro.inc
--source include/weight_string_l1.inc

set @@collation_connection=utf8_unicode_ci;
--source include/weight_string.inc
--source include/weight_string_euro.inc
--source include/weight_string_l1.inc

set @@collation_connection=utf8_czech_ci;
--source include/weight_string_chde.inc

set @@collation_connection=ucs2_czech_ci;
--source include/weight_string_chde.inc

--echo #
--echo # Bug#33077 weight of supplementary characters is not 0xfffd
--echo #
select hex(weight_string(_utf8mb4 0xF0908080 /* U+10000 */ collate utf8mb4_unicode_ci));

--echo #
--echo # Bug#53064 garbled data when using utf8_german2_ci collation
--echo #
CREATE TABLE t1 (s1 VARCHAR(10) COLLATE utf8_german2_ci); 
INSERT INTO t1 VALUES ('a'),('ae'),('af');                 
SELECT s1,hex(s1),hex(weight_string(s1)) FROM t1 ORDER BY s1; 
DROP TABLE t1;

--echo #
--echo # WL#4013 Unicode german2 collation
--echo #
SET collation_connection=utf8_german2_ci;
--source include/ctype_german.inc

--echo #
--echo # WL#2673 Unicode Collation Algorithm new version
--echo #
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_520_ci;
--source include/ctype_unicode520.inc

--echo #
--echo # End of 5.6 tests
--echo #


--echo #
--echo # Start of MariaDB-10.0 tests
--echo #
--echo

SET NAMES utf8 COLLATE utf8_unicode_ci;
--source include/ctype_like_cond_propagation.inc
--source include/ctype_like_cond_propagation_utf8_german.inc

SET NAMES utf8 COLLATE utf8_german2_ci;
--source include/ctype_like_cond_propagation.inc
--source include/ctype_like_cond_propagation_utf8_german.inc

--echo #
--echo # MDEV-4929 Myanmar collation
--echo #
SET NAMES utf8 COLLATE utf8_myanmar_ci;
--source include/ctype_myanmar.inc
SET collation_connection=ucs2_myanmar_ci;
--source include/ctype_myanmar.inc

--echo #
--echo # MDEV-7366 SELECT 'a' = BINARY 'A' returns 1 (utf8 charset, utf8_unicode_ci collation)
--echo #
SET NAMES utf8 COLLATE utf8_unicode_ci; 
SELECT 'a' = BINARY 'A';
SELECT BINARY 'A' = 'a';

--echo #
--echo # Wrong result set for WHERE a='oe' COLLATE utf8_german2_ci AND a='oe'
--echo #
SET NAMES utf8 COLLATE utf8_german2_ci;
CREATE TABLE t1 (a CHAR(10) CHARACTER SET utf8);
INSERT INTO t1 VALUES ('ö'),('oe');
SELECT * FROM t1 WHERE a='oe' AND a='oe' COLLATE utf8_german2_ci;
SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' AND a='oe' COLLATE utf8_german2_ci;
EXPLAIN EXTENDED SELECT * FROM t1 WHERE a='oe' COLLATE utf8_german2_ci AND a='oe';
DROP TABLE t1;

--echo #
--echo # End of MariaDB-10.0 tests
--echo #

--echo #
--echo # Start of MariaDB-10.1 tests
--echo #

--echo #
--echo # MDEV-6973 XOR aggregates argument collations
--echo #
SELECT '10' COLLATE utf8_general_ci XOR '20' COLLATE utf8_unicode_ci;

--echo #
--echo # MDEV-8705 Wrong result for SELECT..WHERE latin1_bin_column='a' AND latin1_bin_column='A'
--echo #
SET NAMES utf8 COLLATE utf8_german2_ci;
CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf8 COLLATE utf8_bin);
INSERT INTO t1 VALUES ('a'),('A');
SELECT * FROM t1 WHERE a='a';
SELECT * FROM t1 WHERE a=_utf8'a';
# Make sure this does not return "Illegal mix of collations"
SELECT * FROM t1 WHERE a='a' AND a=_utf8'a';
DROP TABLE t1;


SET NAMES utf8 COLLATE utf8_thai_520_w2;
--source include/ctype_uca_w2.inc
--source include/ctype_thai.inc

SET NAMES utf8;
SET collation_connection=ucs2_thai_520_w2;
--source include/ctype_uca_w2.inc
--source include/ctype_thai.inc

--echo #
--echo # End of MariaDB-10.1 tests
--echo #


--echo #
--echo # Start of MariaDB-10.2 tests
--echo #

--echo #
--echo # MDEV-9407 Illegal mix of collation when using GROUP_CONCAT in a VIEW
--echo #
SET NAMES utf8;
CREATE TABLE t1 (col1 VARCHAR(12) CHARACTER SET utf8 COLLATE utf8_unicode_ci);
INSERT INTO t1 VALUES ('a'),('b');
CREATE VIEW v1 AS SELECT group_concat('f') AS col1;
SELECT col1 FROM v1 UNION SELECT col1 FROM t1;
DROP VIEW v1;
DROP TABLE t1;


--echo #
--echo # End of MariaDB-10.2 tests
--echo #