diff options
Diffstat (limited to 'mysql-test/main/ctype_uca.test')
-rw-r--r-- | mysql-test/main/ctype_uca.test | 679 |
1 files changed, 679 insertions, 0 deletions
diff --git a/mysql-test/main/ctype_uca.test b/mysql-test/main/ctype_uca.test new file mode 100644 index 00000000000..15a945fde6d --- /dev/null +++ b/mysql-test/main/ctype_uca.test @@ -0,0 +1,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 # |