SET TIME_ZONE = '+03:00'; DROP TABLE IF EXISTS t1; # # Start of 5.5 tests # SET NAMES latin1; SET character_set_connection=utf32; select hex('a'), hex('a '); hex('a') hex('a ') 00000061 0000006100000020 select 'a' = 'a', 'a' = 'a ', 'a ' = 'a'; 'a' = 'a' 'a' = 'a ' 'a ' = 'a' 1 1 1 select 'a\0' = 'a', 'a\0' < 'a', 'a\0' > 'a'; 'a\0' = 'a' 'a\0' < 'a' 'a\0' > 'a' 0 1 0 select 'a' = 'a\0', 'a' < 'a\0', 'a' > 'a\0'; 'a' = 'a\0' 'a' < 'a\0' 'a' > 'a\0' 0 0 1 select 'a\0' = 'a ', 'a\0' < 'a ', 'a\0' > 'a '; 'a\0' = 'a ' 'a\0' < 'a ' 'a\0' > 'a ' 0 1 0 select 'a ' = 'a\0', 'a ' < 'a\0', 'a ' > 'a\0'; 'a ' = 'a\0' 'a ' < 'a\0' 'a ' > 'a\0' 0 0 1 select 'a a' > 'a', 'a \0' < 'a'; 'a a' > 'a' 'a \0' < 'a' 1 1 select binary 'a a' > 'a', binary 'a \0' > 'a', binary 'a\0' > 'a'; binary 'a a' > 'a' binary 'a \0' > 'a' binary 'a\0' > 'a' 1 1 1 select hex(_utf32 0x44); hex(_utf32 0x44) 00000044 select hex(_utf32 0x3344); hex(_utf32 0x3344) 00003344 select hex(_utf32 0x103344); hex(_utf32 0x103344) 00103344 select hex(_utf32 X'44'); hex(_utf32 X'44') 00000044 select hex(_utf32 X'3344'); hex(_utf32 X'3344') 00003344 select hex(_utf32 X'103344'); hex(_utf32 X'103344') 00103344 CREATE TABLE t1 (word VARCHAR(64), word2 CHAR(64)) CHARACTER SET utf32; INSERT INTO t1 VALUES (_koi8r 0xF2, _koi8r 0xF2), (X'2004',X'2004'); SELECT hex(word) FROM t1 ORDER BY word; hex(word) 00000420 00002004 SELECT hex(word2) FROM t1 ORDER BY word2; hex(word2) 00000420 00002004 DELETE FROM t1; INSERT INTO t1 VALUES (X'000004200000002000000020',X'000004200000002000000020'), (X'000020040000002000000020',X'000020040000002000000020'); SELECT hex(word) FROM t1 ORDER BY word; hex(word) 000004200000002000000020 000020040000002000000020 SELECT hex(word2) FROM t1 ORDER BY word2; hex(word2) 00000420 00002004 DROP TABLE t1; SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'0421')); hex(LPAD(_utf32 X'0420',10,_utf32 X'0421')) 00000421000004210000042100000421000004210000042100000421000004210000042100000420 SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'0000042100000422')); hex(LPAD(_utf32 X'0420',10,_utf32 X'0000042100000422')) 00000421000004220000042100000422000004210000042200000421000004220000042100000420 SELECT hex(LPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423')); hex(LPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423')) 00000421000004220000042300000421000004220000042300000421000004220000042300000420 SELECT hex(LPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423')); hex(LPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423')) 00000420000004210000042200000423000004240000042500000426000004270000042800000429 SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'0421')); hex(RPAD(_utf32 X'0420',10,_utf32 X'0421')) 00000420000004210000042100000421000004210000042100000421000004210000042100000421 SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'0000042100000422')); hex(RPAD(_utf32 X'0420',10,_utf32 X'0000042100000422')) 00000420000004210000042200000421000004220000042100000422000004210000042200000421 SELECT hex(RPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423')); hex(RPAD(_utf32 X'0420',10,_utf32 X'000004210000042200000423')) 00000420000004210000042200000423000004210000042200000423000004210000042200000423 SELECT hex(RPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423')); hex(RPAD(_utf32 X'000004200000042100000422000004230000042400000425000004260000042700000428000004290000042A0000042B',10,_utf32 X'000004210000042200000423')) 00000420000004210000042200000423000004240000042500000426000004270000042800000429 CREATE TABLE t1 SELECT LPAD(_utf32 X'0420',10,_utf32 X'0421') l, RPAD(_utf32 X'0420',10,_utf32 X'0421') r; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `l` varchar(10) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL, `r` varchar(10) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci select hex(l), hex(r) from t1; hex(l) hex(r) 00000421000004210000042100000421000004210000042100000421000004210000042100000420 00000420000004210000042100000421000004210000042100000421000004210000042100000421 DROP TABLE t1; create table t1 (f1 char(30)); insert into t1 values ("103000"), ("22720000"), ("3401200"), ("78000"); select lpad(f1, 12, "-o-/") from t1; lpad(f1, 12, "-o-/") -o-/-o103000 -o-/22720000 -o-/-3401200 -o-/-o-78000 drop table t1; SET NAMES latin1; SET character_set_connection=utf32; select @@collation_connection; @@collation_connection utf32_general_ci create table t1 as select repeat(' ',10) as a union select null; alter table t1 add key(a); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL, KEY `a` (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); explain select * from t1 where a like 'abc%'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 43 NULL 2 Using where; Using index explain select * from t1 where a like concat('abc','%'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range a a 43 NULL 2 Using where; Using index select * from t1 where a like "abc%"; a abc abcd select * from t1 where a like concat("abc","%"); a abc abcd select * from t1 where a like "ABC%"; a abc abcd select * from t1 where a like "test%"; a test select * from t1 where a like "te_t"; a test select * from t1 where a like "%a%"; a a abc abcd select * from t1 where a like "%abcd%"; a abcd select * from t1 where a like "%abc\d%"; a abcd drop table t1; select 'AA' like 'AA'; 'AA' like 'AA' 1 select 'AA' like 'A%A'; 'AA' like 'A%A' 1 select 'AA' like 'A%%A'; 'AA' like 'A%%A' 1 select 'AA' like 'AA%'; 'AA' like 'AA%' 1 select 'AA' like '%AA%'; 'AA' like '%AA%' 1 select 'AA' like '%A'; 'AA' like '%A' 1 select 'AA' like '%AA'; 'AA' like '%AA' 1 select 'AA' like 'A%A%'; 'AA' like 'A%A%' 1 select 'AA' like '_%_%'; 'AA' like '_%_%' 1 select 'AA' like '%A%A'; 'AA' like '%A%A' 1 select 'AAA'like 'A%A%A'; 'AAA'like 'A%A%A' 1 select 'AZ' like 'AZ'; 'AZ' like 'AZ' 1 select 'AZ' like 'A%Z'; 'AZ' like 'A%Z' 1 select 'AZ' like 'A%%Z'; 'AZ' like 'A%%Z' 1 select 'AZ' like 'AZ%'; 'AZ' like 'AZ%' 1 select 'AZ' like '%AZ%'; 'AZ' like '%AZ%' 1 select 'AZ' like '%Z'; 'AZ' like '%Z' 1 select 'AZ' like '%AZ'; 'AZ' like '%AZ' 1 select 'AZ' like 'A%Z%'; 'AZ' like 'A%Z%' 1 select 'AZ' like '_%_%'; 'AZ' like '_%_%' 1 select 'AZ' like '%A%Z'; 'AZ' like '%A%Z' 1 select 'AZ' like 'A_'; 'AZ' like 'A_' 1 select 'AZ' like '_Z'; 'AZ' like '_Z' 1 select 'AMZ'like 'A%M%Z'; 'AMZ'like 'A%M%Z' 1 SET NAMES utf8; SET character_set_connection=utf32; CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32); INSERT INTO t1 VALUES ('фыва'),('Фыва'),('фЫва'),('фыВа'),('фывА'),('ФЫВА'); INSERT INTO t1 VALUES ('фывапролдж'),('Фывапролдж'),('фЫвапролдж'),('фыВапролдж'); INSERT INTO t1 VALUES ('фывАпролдж'),('фываПролдж'),('фывапРолдж'),('фывапрОлдж'); INSERT INTO t1 VALUES ('фывапроЛдж'),('фывапролДж'),('фывапролдЖ'),('ФЫВАПРОЛДЖ'); SELECT * FROM t1 WHERE a LIKE '%фЫва%' ORDER BY BINARY a; a ФЫВА ФЫВАПРОЛДЖ Фыва Фывапролдж фЫва фЫвапролдж фыВа фыВапролдж фывА фывАпролдж фыва фываПролдж фывапРолдж фывапрОлдж фывапроЛдж фывапролДж фывапролдЖ фывапролдж SELECT * FROM t1 WHERE a LIKE '%фЫв%' ORDER BY BINARY a; a ФЫВА ФЫВАПРОЛДЖ Фыва Фывапролдж фЫва фЫвапролдж фыВа фыВапролдж фывА фывАпролдж фыва фываПролдж фывапРолдж фывапрОлдж фывапроЛдж фывапролДж фывапролдЖ фывапролдж SELECT * FROM t1 WHERE a LIKE 'фЫва%' ORDER BY BINARY a; a ФЫВА ФЫВАПРОЛДЖ Фыва Фывапролдж фЫва фЫвапролдж фыВа фыВапролдж фывА фывАпролдж фыва фываПролдж фывапРолдж фывапрОлдж фывапроЛдж фывапролДж фывапролдЖ фывапролдж SELECT * FROM t1 WHERE a LIKE 'фЫва%' COLLATE utf32_bin ORDER BY BINARY a; a фЫва фЫвапролдж DROP TABLE t1; CREATE TABLE t1 (word varchar(64) NOT NULL, PRIMARY KEY (word)) ENGINE=MyISAM CHARACTER SET utf32; INSERT INTO t1 (word) VALUES ("cat"); SELECT * FROM t1 WHERE word LIKE "c%"; word cat SELECT * FROM t1 WHERE word LIKE "ca_"; word cat SELECT * FROM t1 WHERE word LIKE "cat"; word cat SELECT * FROM t1 WHERE word LIKE _utf32 x'0000006300000025'; word cat SELECT * FROM t1 WHERE word LIKE _utf32 x'00000063000000610000005F'; word cat DROP TABLE t1; select insert(_utf32 0x000000610000006200000063,10,2,_utf32 0x000000640000006500000066); insert(_utf32 0x000000610000006200000063,10,2,_utf32 0x000000640000006500000066) abc select insert(_utf32 0x000000610000006200000063,1,2,_utf32 0x000000640000006500000066); insert(_utf32 0x000000610000006200000063,1,2,_utf32 0x000000640000006500000066) defc SET NAMES latin1; CREATE TABLE t1 ( word VARCHAR(64), bar INT(11) default 0, PRIMARY KEY (word)) ENGINE=MyISAM CHARSET utf32 COLLATE utf32_general_ci ; INSERT INTO t1 (word) VALUES ("aar"); INSERT INTO t1 (word) VALUES ("a"); INSERT INTO t1 (word) VALUES ("aardvar"); INSERT INTO t1 (word) VALUES ("aardvark"); INSERT INTO t1 (word) VALUES ("aardvara"); INSERT INTO t1 (word) VALUES ("aardvarz"); EXPLAIN SELECT * FROM t1 ORDER BY word; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort SELECT * FROM t1 ORDER BY word; word bar a 0 aar 0 aardvar 0 aardvara 0 aardvark 0 aardvarz 0 EXPLAIN SELECT word FROM t1 ORDER BY word; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 258 NULL 6 Using index SELECT word FROM t1 ORDER by word; word a aar aardvar aardvara aardvark aardvarz DROP TABLE t1; CREATE TABLE t1 ( word VARCHAR(64) , PRIMARY KEY (word)) ENGINE=MyISAM CHARSET utf32 COLLATE utf32_general_ci; INSERT INTO t1 (word) VALUES ("aar"); INSERT INTO t1 (word) VALUES ("a"); INSERT INTO t1 (word) VALUES ("aardvar"); INSERT INTO t1 (word) VALUES ("aardvark"); INSERT INTO t1 (word) VALUES ("aardvara"); INSERT INTO t1 (word) VALUES ("aardvarz"); EXPLAIN SELECT * FROM t1 ORDER BY WORD; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL PRIMARY 258 NULL 6 Using index SELECT * FROM t1 ORDER BY word; word a aar aardvar aardvara aardvark aardvarz DROP TABLE t1; CREATE TABLE t1 ( word TEXT, bar INT(11) AUTO_INCREMENT, PRIMARY KEY (bar)) ENGINE=MyISAM CHARSET utf32 COLLATE utf32_general_ci ; INSERT INTO t1 (word) VALUES ("aar"); INSERT INTO t1 (word) VALUES ("a" ); INSERT INTO t1 (word) VALUES ("aardvar"); INSERT INTO t1 (word) VALUES ("aardvark"); INSERT INTO t1 (word) VALUES ("aardvara"); INSERT INTO t1 (word) VALUES ("aardvarz"); EXPLAIN SELECT * FROM t1 ORDER BY word; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort SELECT * FROM t1 ORDER BY word; word bar a 2 aar 1 aardvar 3 aardvara 5 aardvark 4 aardvarz 6 EXPLAIN SELECT word FROM t1 ORDER BY word; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 6 Using filesort SELECT word FROM t1 ORDER BY word; word a aar aardvar aardvara aardvark aardvarz DROP TABLE t1; SELECT hex(cast(0xAA as char character set utf32)); hex(cast(0xAA as char character set utf32)) 000000AA SELECT hex(convert(0xAA using utf32)); hex(convert(0xAA using utf32)) 000000AA CREATE TABLE t1 (a char(10) character set utf32); INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); SELECT HEX(a) FROM t1; HEX(a) 00000001 00000011 00000111 00001111 00011111 DROP TABLE t1; CREATE TABLE t1 (a varchar(10) character set utf32); INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); SELECT HEX(a) FROM t1; HEX(a) 00000001 00000011 00000111 00001111 00011111 DROP TABLE t1; CREATE TABLE t1 (a text character set utf32); INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); SELECT HEX(a) FROM t1; HEX(a) 00000001 00000011 00000111 00001111 00011111 DROP TABLE t1; CREATE TABLE t1 (a mediumtext character set utf32); INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); SELECT HEX(a) FROM t1; HEX(a) 00000001 00000011 00000111 00001111 00011111 DROP TABLE t1; CREATE TABLE t1 (a longtext character set utf32); INSERT INTO t1 VALUES (0x1),(0x11),(0x111),(0x1111),(0x11111); SELECT HEX(a) FROM t1; HEX(a) 00000001 00000011 00000111 00001111 00011111 DROP TABLE t1; create table t1(a char(1)) default charset utf32; insert into t1 values ('a'),('b'),('c'); alter table t1 modify a char(5); select a, hex(a) from t1; a hex(a) a 00000061 b 00000062 c 00000063 drop table t1; set @ivar= 1234; set @str1 = 'select ?'; set @str2 = convert(@str1 using utf32); prepare stmt1 from @str2; execute stmt1 using @ivar; ? 1234 set names utf8; create table t1 (a enum('x','y','z') character set utf32); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` enum('x','y','z') CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert into t1 values ('x'); insert into t1 values ('y'); insert into t1 values ('z'); select a, hex(a) from t1 order by a; a hex(a) x 00000078 y 00000079 z 0000007A alter table t1 change a a enum('x','y','z','d','e','ä','ö','ü') character set utf32; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` enum('x','y','z','d','e','ä','ö','ü') CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert into t1 values ('D'); insert into t1 values ('E '); insert into t1 values ('ä'); insert into t1 values ('ö'); insert into t1 values ('ü'); select a, hex(a) from t1 order by a; a hex(a) x 00000078 y 00000079 z 0000007A d 00000064 e 00000065 ä 000000E4 ö 000000F6 ü 000000FC drop table t1; create table t1 (a set ('x','y','z','ä','ö','ü') character set utf32); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` set('x','y','z','ä','ö','ü') CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci insert into t1 values ('x'); insert into t1 values ('y'); insert into t1 values ('z'); insert into t1 values ('x,y'); insert into t1 values ('x,y,z,ä,ö,ü'); select a, hex(a) from t1 order by a; a hex(a) x 00000078 y 00000079 x,y 000000780000002C00000079 z 0000007A x,y,z,ä,ö,ü 000000780000002C000000790000002C0000007A0000002C000000E40000002C000000F60000002C000000FC drop table t1; create table t1(a enum('a','b','c')) default character set utf32; insert into t1 values('a'),('b'),('c'); alter table t1 add b char(1); show warnings; Level Code Message select * from t1 order by a; a b a NULL b NULL c NULL drop table t1; SET NAMES latin1; SET collation_connection='utf32_general_ci'; create table t1 select repeat('a',4000) a; delete from t1; insert into t1 values ('a'), ('a '), ('a\t'); select collation(a),hex(a) from t1 order by a; collation(a) hex(a) utf32_general_ci 0000006100000009 utf32_general_ci 00000061 utf32_general_ci 0000006100000020 drop table t1; # # MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP # SELECT @@collation_connection; @@collation_connection utf32_general_ci CREATE TABLE t1 (i INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); SELECT * FROM t1 GROUP BY MID(CURRENT_USER,0) WITH ROLLUP; i 1 1 SELECT * FROM t1 GROUP BY MID('test',0) WITH ROLLUP; i 1 1 DROP TABLE t1; # # MDEV-6170 Incorrect ordering with utf8_bin and utf8mb4_bin collations # SELECT @@collation_connection; @@collation_connection utf32_general_ci CREATE TABLE t1 ENGINE=MEMORY AS SELECT REPEAT('a',5) AS a LIMIT 0; INSERT INTO t1 (a) VALUES ("a"); INSERT INTO t1 (a) VALUES ("b"); INSERT INTO t1 (a) VALUES ("c"); INSERT INTO t1 (a) VALUES ("d"); INSERT INTO t1 (a) VALUES ("e"); INSERT INTO t1 (a) VALUES ("f"); INSERT INTO t1 (a) VALUES ("g"); INSERT INTO t1 (a) VALUES ("h"); INSERT INTO t1 (a) VALUES ("i"); INSERT INTO t1 (a) VALUES ("j"); INSERT INTO t1 (a) VALUES ("k"); INSERT INTO t1 (a) VALUES ("l"); INSERT INTO t1 (a) VALUES ("m"); SELECT * FROM t1 ORDER BY LOWER(a); a a b c d e f g h i j k l m SELECT * FROM t1 ORDER BY LOWER(a) DESC; a m l k j i h g f e d c b a DROP TABLE t1; select @@collation_connection; @@collation_connection utf32_general_ci create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ; insert into t1 values('abcdef'); insert into t1 values('_bcdef'); insert into t1 values('a_cdef'); insert into t1 values('ab_def'); insert into t1 values('abc_ef'); insert into t1 values('abcd_f'); insert into t1 values('abcde_'); select c1 as c1u from t1 where c1 like 'ab\_def'; c1u ab_def select c1 as c2h from t1 where c1 like 'ab#_def' escape '#'; c2h ab_def drop table t1; SELECT @@collation_connection; @@collation_connection utf32_general_ci SELECT '\%b' LIKE '%\%'; '\%b' LIKE '%\%' 0 SET NAMES latin1; SET collation_connection='utf32_bin'; create table t1 select repeat('a',4000) a; delete from t1; insert into t1 values ('a'), ('a '), ('a\t'); select collation(a),hex(a) from t1 order by a; collation(a) hex(a) utf32_bin 0000006100000009 utf32_bin 00000061 utf32_bin 0000006100000020 drop table t1; # # MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP # SELECT @@collation_connection; @@collation_connection utf32_bin CREATE TABLE t1 (i INT) ENGINE=MyISAM; INSERT INTO t1 VALUES (1),(2); SELECT * FROM t1 GROUP BY MID(CURRENT_USER,0) WITH ROLLUP; i 1 1 SELECT * FROM t1 GROUP BY MID('test',0) WITH ROLLUP; i 1 1 DROP TABLE t1; # # MDEV-6170 Incorrect ordering with utf8_bin and utf8mb4_bin collations # SELECT @@collation_connection; @@collation_connection utf32_bin CREATE TABLE t1 ENGINE=MEMORY AS SELECT REPEAT('a',5) AS a LIMIT 0; INSERT INTO t1 (a) VALUES ("a"); INSERT INTO t1 (a) VALUES ("b"); INSERT INTO t1 (a) VALUES ("c"); INSERT INTO t1 (a) VALUES ("d"); INSERT INTO t1 (a) VALUES ("e"); INSERT INTO t1 (a) VALUES ("f"); INSERT INTO t1 (a) VALUES ("g"); INSERT INTO t1 (a) VALUES ("h"); INSERT INTO t1 (a) VALUES ("i"); INSERT INTO t1 (a) VALUES ("j"); INSERT INTO t1 (a) VALUES ("k"); INSERT INTO t1 (a) VALUES ("l"); INSERT INTO t1 (a) VALUES ("m"); SELECT * FROM t1 ORDER BY LOWER(a); a a b c d e f g h i j k l m SELECT * FROM t1 ORDER BY LOWER(a) DESC; a m l k j i h g f e d c b a DROP TABLE t1; # # Bug#55980 Character sets: supplementary character _bin ordering is wrong # CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a LIMIT 0; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(1) CHARACTER SET utf32 COLLATE utf32_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (_utf8mb4 0xEFBE9D),(_utf8mb4 0xF0908E84); INSERT INTO t1 VALUES (_utf8mb4 0xCE85),(_utf8mb4 0xF4808080); SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a; HEX(a) HEX(CONVERT(a USING utf8mb4)) 00000385 CE85 0000FF9D EFBE9D 00010384 F0908E84 00100000 F4808080 ALTER TABLE t1 ADD KEY(a); SELECT HEX(a), HEX(CONVERT(a USING utf8mb4)) FROM t1 ORDER BY a; HEX(a) HEX(CONVERT(a USING utf8mb4)) 00000385 CE85 0000FF9D EFBE9D 00010384 F0908E84 00100000 F4808080 DROP TABLE IF EXISTS t1; # # BUG#16691598 - ORDER BY LOWER(COLUMN) PRODUCES # OUT-OF-ORDER RESULTS # CREATE TABLE t1 SELECT ('a a') as n; INSERT INTO t1 VALUES('a b'); SELECT * FROM t1 ORDER BY LOWER(n) ASC; n a a a b SELECT * FROM t1 ORDER BY LOWER(n) DESC; n a b a a DROP TABLE t1; select @@collation_connection; @@collation_connection utf32_bin create table t1 ROW_FORMAT=DYNAMIC select repeat('a',50) as c1 ; insert into t1 values('abcdef'); insert into t1 values('_bcdef'); insert into t1 values('a_cdef'); insert into t1 values('ab_def'); insert into t1 values('abc_ef'); insert into t1 values('abcd_f'); insert into t1 values('abcde_'); select c1 as c1u from t1 where c1 like 'ab\_def'; c1u ab_def select c1 as c2h from t1 where c1 like 'ab#_def' escape '#'; c2h ab_def drop table t1; SELECT @@collation_connection; @@collation_connection utf32_bin SELECT '\%b' LIKE '%\%'; '\%b' LIKE '%\%' 0 select hex(substr(_utf32 0x000000e4000000e500000068,1)); hex(substr(_utf32 0x000000e4000000e500000068,1)) 000000E4000000E500000068 select hex(substr(_utf32 0x000000e4000000e500000068,2)); hex(substr(_utf32 0x000000e4000000e500000068,2)) 000000E500000068 select hex(substr(_utf32 0x000000e4000000e500000068,3)); hex(substr(_utf32 0x000000e4000000e500000068,3)) 00000068 select hex(substr(_utf32 0x000000e4000000e500000068,-1)); hex(substr(_utf32 0x000000e4000000e500000068,-1)) 00000068 select hex(substr(_utf32 0x000000e4000000e500000068,-2)); hex(substr(_utf32 0x000000e4000000e500000068,-2)) 000000E500000068 select hex(substr(_utf32 0x000000e4000000e500000068,-3)); hex(substr(_utf32 0x000000e4000000e500000068,-3)) 000000E4000000E500000068 CREATE TABLE t1 ( a varchar(250) NOT NULL default '', KEY a (a) ) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE utf32_general_ci; insert into t1 values (0x803d); insert into t1 values (0x005b); select hex(a) from t1; hex(a) 0000005B 0000803D drop table t1; create table t1 (utext varchar(20) character set utf32); insert into t1 values ("lily"); insert into t1 values ("river"); prepare stmt from 'select utext from t1 where utext like ?'; set @param1='%%'; execute stmt using @param1; utext lily river execute stmt using @param1; utext lily river select utext from t1 where utext like '%%'; utext lily river drop table t1; deallocate prepare stmt; create table t1 ( a char(10) character set utf32 not null, index a (a) ) engine=myisam; insert into t1 values (repeat(0x0000201f, 10)); insert into t1 values (repeat(0x00002020, 10)); insert into t1 values (repeat(0x00002021, 10)); explain select hex(a) from t1 order by a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index NULL a 40 NULL 3 Using index select hex(a) from t1 order by a; hex(a) 0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F 00002020000020200000202000002020000020200000202000002020000020200000202000002020 00002021000020210000202100002021000020210000202100002021000020210000202100002021 alter table t1 drop index a; select hex(a) from t1 order by a; hex(a) 0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F0000201F 00002020000020200000202000002020000020200000202000002020000020200000202000002020 00002021000020210000202100002021000020210000202100002021000020210000202100002021 drop table t1; CREATE TABLE t1 ( status enum('active','passive') character set utf32 collate utf32_general_ci NOT NULL default 'passive' ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `status` enum('active','passive') CHARACTER SET utf32 COLLATE utf32_general_ci NOT NULL DEFAULT 'passive' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ALTER TABLE t1 ADD a int NOT NULL AFTER status; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `status` enum('active','passive') CHARACTER SET utf32 COLLATE utf32_general_ci NOT NULL DEFAULT 'passive', `a` int(11) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; End of 4.1 tests CREATE TABLE t1 (a varchar(64) character set utf32, b decimal(10,3)); INSERT INTO t1 VALUES ("1.1", 0), ("2.1", 0); update t1 set b=a; SELECT *, hex(a) FROM t1; a b hex(a) 1.1 1.100 000000310000002E00000031 2.1 2.100 000000320000002E00000031 DROP TABLE t1; create table t1 (utext varchar(20) character set utf32); insert into t1 values ("lily"); insert into t1 values ("river"); prepare stmt from 'select utext from t1 where utext like ?'; set @param1='%%'; execute stmt using @param1; utext lily river execute stmt using @param1; utext lily river select utext from t1 where utext like '%%'; utext lily river drop table t1; deallocate prepare stmt; set names latin1; set character_set_connection=utf32; select soundex(''),soundex('he'),soundex('hello all folks'),soundex('#3556 in bugdb'); soundex('') soundex('he') soundex('hello all folks') soundex('#3556 in bugdb') H000 H4142 I51231 select hex(soundex('')),hex(soundex('he')),hex(soundex('hello all folks')),hex(soundex('#3556 in bugdb')); hex(soundex('')) hex(soundex('he')) hex(soundex('hello all folks')) hex(soundex('#3556 in bugdb')) 00000048000000300000003000000030 0000004800000034000000310000003400000032 000000490000003500000031000000320000003300000031 select 'mood' sounds like 'mud'; 'mood' sounds like 'mud' 1 select hex(soundex(_utf32 0x000004100000041100000412)); hex(soundex(_utf32 0x000004100000041100000412)) 00000410000000300000003000000030 select hex(soundex(_utf32 0x000000BF000000C0)); hex(soundex(_utf32 0x000000BF000000C0)) 000000C0000000300000003000000030 set names latin1; create table t1(a blob, b text charset utf32); select data_type, character_octet_length, character_maximum_length from information_schema.columns where table_name='t1'; data_type character_octet_length character_maximum_length blob 65535 65535 text 65535 16383 drop table t1; set names latin1; set collation_connection=utf32_general_ci; select position('bb' in 'abba'); position('bb' in 'abba') 2 create table t1 (a varchar(10) character set utf32) engine=heap; insert into t1 values ('a'),('A'),('b'),('B'); select * from t1 where a='a' order by binary a; a A a select hex(min(binary a)),count(*) from t1 group by a; hex(min(binary a)) count(*) 00000041 2 00000042 2 drop table t1; select char_length('abcd'), octet_length('abcd'); char_length('abcd') octet_length('abcd') 4 16 select left('abcd',2); left('abcd',2) ab create table t1 (a varchar(10) character set utf32); insert into t1 values (_utf32 0x0010FFFF); insert into t1 values (_utf32 0x00110000); ERROR HY000: Invalid utf32 character string: '001100' insert into t1 values (_utf32 0x00110101); ERROR HY000: Invalid utf32 character string: '001101' insert into t1 values (_utf32 0x01000101); ERROR HY000: Invalid utf32 character string: '010001' insert into t1 values (_utf32 0x11000101); ERROR HY000: Invalid utf32 character string: '110001' select hex(a) from t1; hex(a) 0010FFFF drop table t1; create table t1 (utf32 varchar(2) character set utf32); Wrong character with pad insert ignore into t1 values (0x110000); Warnings: Warning 1366 Incorrect string value: '\x11\x00\x00' for column `test`.`t1`.`utf32` at row 1 Wrong chsaracter without pad insert ignore into t1 values (0x00110000); Warnings: Warning 1366 Incorrect string value: '\x00\x11\x00\x00' for column `test`.`t1`.`utf32` at row 1 Wrong character with pad followed by another wrong character insert ignore into t1 values (0x11000000110000); Warnings: Warning 1366 Incorrect string value: '\x11\x00\x00\x00\x11\x00...' for column `test`.`t1`.`utf32` at row 1 Good character with pad followed by bad character insert ignore into t1 values (0x10000000110000); Warnings: Warning 1366 Incorrect string value: '\x00\x11\x00\x00' for column `test`.`t1`.`utf32` at row 1 Good character without pad followed by bad character insert ignore into t1 values (0x0010000000110000); Warnings: Warning 1366 Incorrect string value: '\x00\x11\x00\x00' for column `test`.`t1`.`utf32` at row 1 Wrong character with the second byte higher than 0x10 insert ignore into t1 values (0x00800037); Warnings: Warning 1366 Incorrect string value: '\x00\x80\x007' for column `test`.`t1`.`utf32` at row 1 Wrong character with pad with the second byte higher than 0x10 insert ignore into t1 values (0x00800037); Warnings: Warning 1366 Incorrect string value: '\x00\x80\x007' for column `test`.`t1`.`utf32` at row 1 drop table t1; select _utf32'a' collate utf32_general_ci = 0xfffd; _utf32'a' collate utf32_general_ci = 0xfffd 0 select hex(concat(_utf32 0x0410 collate utf32_general_ci, 0x61)); hex(concat(_utf32 0x0410 collate utf32_general_ci, 0x61)) 0000041000000061 create table t1 (s1 varchar(5) character set utf32); insert into t1 values (0xfffd); select case when s1 = 0xfffd then 1 else 0 end from t1; case when s1 = 0xfffd then 1 else 0 end 1 select hex(s1) from t1 where s1 = 0xfffd; hex(s1) 0000FFFD drop table t1; create table t1 (a char(10)) character set utf32; insert into t1 values ('a '); select hex(a) from t1; hex(a) 00000061 drop table t1; select upper('abcd'), lower('ABCD'); upper('abcd') lower('ABCD') ABCD abcd create table t1 (a varchar(10) character set utf32); insert into t1 values (123456); select a, hex(a) from t1; a hex(a) 123456 000000310000003200000033000000340000003500000036 drop table t1; select hex(soundex('a')); hex(soundex('a')) 00000041000000300000003000000030 create table t1 (a enum ('a','b','c')) character set utf32; insert into t1 values ('1'); select * from t1; a a drop table t1; set names latin1; select hex(conv(convert('123' using utf32), -10, 16)); hex(conv(convert('123' using utf32), -10, 16)) 3742 select hex(conv(convert('123' using utf32), 10, 16)); hex(conv(convert('123' using utf32), 10, 16)) 3742 set names latin1; set character_set_connection=utf32; select 1.1 + '1.2'; 1.1 + '1.2' 2.3 select 1.1 + '1.2xxx'; 1.1 + '1.2xxx' 2.3 Warnings: Warning 1292 Truncated incorrect DOUBLE value: '1.2xxx' select left('aaa','1'); left('aaa','1') a # # Testing strtoll10() # SELECT @@character_set_connection; @@character_set_connection utf32 SELECT CAST('1' AS UNSIGNED); CAST('1' AS UNSIGNED) 1 SELECT CAST('12' AS UNSIGNED); CAST('12' AS UNSIGNED) 12 SELECT CAST('123' AS UNSIGNED); CAST('123' AS UNSIGNED) 123 SELECT CAST('1234' AS UNSIGNED); CAST('1234' AS UNSIGNED) 1234 SELECT CAST('12345' AS UNSIGNED); CAST('12345' AS UNSIGNED) 12345 SELECT CAST('123456' AS UNSIGNED); CAST('123456' AS UNSIGNED) 123456 SELECT CAST('1234567' AS UNSIGNED); CAST('1234567' AS UNSIGNED) 1234567 SELECT CAST('12345678' AS UNSIGNED); CAST('12345678' AS UNSIGNED) 12345678 SELECT CAST('123456789' AS UNSIGNED); CAST('123456789' AS UNSIGNED) 123456789 SELECT CAST('1234567891' AS UNSIGNED); CAST('1234567891' AS UNSIGNED) 1234567891 SELECT CAST('12345678912' AS UNSIGNED); CAST('12345678912' AS UNSIGNED) 12345678912 SELECT CAST('123456789123' AS UNSIGNED); CAST('123456789123' AS UNSIGNED) 123456789123 SELECT CAST('1234567891234' AS UNSIGNED); CAST('1234567891234' AS UNSIGNED) 1234567891234 SELECT CAST('12345678912345' AS UNSIGNED); CAST('12345678912345' AS UNSIGNED) 12345678912345 SELECT CAST('123456789123456' AS UNSIGNED); CAST('123456789123456' AS UNSIGNED) 123456789123456 SELECT CAST('1234567891234567' AS UNSIGNED); CAST('1234567891234567' AS UNSIGNED) 1234567891234567 SELECT CAST('12345678912345678' AS UNSIGNED); CAST('12345678912345678' AS UNSIGNED) 12345678912345678 SELECT CAST('123456789123456789' AS UNSIGNED); CAST('123456789123456789' AS UNSIGNED) 123456789123456789 SELECT CAST('1234567891234567891' AS UNSIGNED); CAST('1234567891234567891' AS UNSIGNED) 1234567891234567891 SELECT CAST('12345678912345678912' AS UNSIGNED); CAST('12345678912345678912' AS UNSIGNED) 12345678912345678912 SELECT CAST('123456789123456789123' AS UNSIGNED); CAST('123456789123456789123' AS UNSIGNED) 18446744073709551615 Warnings: Warning 1292 Truncated incorrect INTEGER value: '123456789123456789123' SELECT CAST('1x' AS UNSIGNED); CAST('1x' AS UNSIGNED) 1 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1x' SELECT CAST('12x' AS UNSIGNED); CAST('12x' AS UNSIGNED) 12 Warnings: Warning 1292 Truncated incorrect INTEGER value: '12x' SELECT CAST('123x' AS UNSIGNED); CAST('123x' AS UNSIGNED) 123 Warnings: Warning 1292 Truncated incorrect INTEGER value: '123x' SELECT CAST('1234x' AS UNSIGNED); CAST('1234x' AS UNSIGNED) 1234 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1234x' SELECT CAST('12345x' AS UNSIGNED); CAST('12345x' AS UNSIGNED) 12345 Warnings: Warning 1292 Truncated incorrect INTEGER value: '12345x' SELECT CAST('123456x' AS UNSIGNED); CAST('123456x' AS UNSIGNED) 123456 Warnings: Warning 1292 Truncated incorrect INTEGER value: '123456x' SELECT CAST('1234567x' AS UNSIGNED); CAST('1234567x' AS UNSIGNED) 1234567 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1234567x' SELECT CAST('12345678x' AS UNSIGNED); CAST('12345678x' AS UNSIGNED) 12345678 Warnings: Warning 1292 Truncated incorrect INTEGER value: '12345678x' SELECT CAST('123456789x' AS UNSIGNED); CAST('123456789x' AS UNSIGNED) 123456789 Warnings: Warning 1292 Truncated incorrect INTEGER value: '123456789x' SELECT CAST('1234567891x' AS UNSIGNED); CAST('1234567891x' AS UNSIGNED) 1234567891 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1234567891x' SELECT CAST('12345678912x' AS UNSIGNED); CAST('12345678912x' AS UNSIGNED) 12345678912 Warnings: Warning 1292 Truncated incorrect INTEGER value: '12345678912x' SELECT CAST('123456789123x' AS UNSIGNED); CAST('123456789123x' AS UNSIGNED) 123456789123 Warnings: Warning 1292 Truncated incorrect INTEGER value: '123456789123x' SELECT CAST('1234567891234x' AS UNSIGNED); CAST('1234567891234x' AS UNSIGNED) 1234567891234 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1234567891234x' SELECT CAST('12345678912345x' AS UNSIGNED); CAST('12345678912345x' AS UNSIGNED) 12345678912345 Warnings: Warning 1292 Truncated incorrect INTEGER value: '12345678912345x' SELECT CAST('123456789123456x' AS UNSIGNED); CAST('123456789123456x' AS UNSIGNED) 123456789123456 Warnings: Warning 1292 Truncated incorrect INTEGER value: '123456789123456x' SELECT CAST('1234567891234567x' AS UNSIGNED); CAST('1234567891234567x' AS UNSIGNED) 1234567891234567 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1234567891234567x' SELECT CAST('12345678912345678x' AS UNSIGNED); CAST('12345678912345678x' AS UNSIGNED) 12345678912345678 Warnings: Warning 1292 Truncated incorrect INTEGER value: '12345678912345678x' SELECT CAST('123456789123456789x' AS UNSIGNED); CAST('123456789123456789x' AS UNSIGNED) 123456789123456789 Warnings: Warning 1292 Truncated incorrect INTEGER value: '123456789123456789x' SELECT CAST('1234567891234567891x' AS UNSIGNED); CAST('1234567891234567891x' AS UNSIGNED) 1234567891234567891 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1234567891234567891x' SELECT CAST('12345678912345678912x' AS UNSIGNED); CAST('12345678912345678912x' AS UNSIGNED) 12345678912345678912 Warnings: Warning 1292 Truncated incorrect INTEGER value: '12345678912345678912x' SELECT CAST('123456789123456789123x' AS UNSIGNED); CAST('123456789123456789123x' AS UNSIGNED) 18446744073709551615 Warnings: Warning 1292 Truncated incorrect INTEGER value: '123456789123456789123x' create table t1 (a int); insert into t1 values ('-1234.1e2'); insert ignore into t1 values ('-1234.1e2xxxx'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 insert into t1 values ('-1234.1e2 '); Warnings: Note 1265 Data truncated for column 'a' at row 1 select * from t1; a -123410 -123410 -123410 drop table t1; create table t1 (a int); insert into t1 values ('1 '); Warnings: Note 1265 Data truncated for column 'a' at row 1 insert ignore into t1 values ('1 x'); Warnings: Warning 1265 Data truncated for column 'a' at row 1 select * from t1; a 1 1 drop table t1; SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR create table t1 (a varchar(17000) character set utf32); Warnings: Note 1246 Converting column 'a' from VARCHAR to TEXT show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` mediumtext CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; create table t1 (a varchar(250) character set utf32 primary key); show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(250) CHARACTER SET utf32 COLLATE utf32_general_ci NOT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; create table t1 (a varchar(334) character set utf32 primary key); ERROR 42000: Specified key was too long; max key length is 1000 bytes create table t1 (a varchar(333) character set utf32, key(a)); Warnings: Note 1071 Specified key was too long; max key length is 1000 bytes insert into t1 values (repeat('a',333)), (repeat('b',333)); flush tables; check table t1; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; SET collation_connection=utf32_general_ci; # # Bug#32390 Character sets: casting utf32 to/from date doesn't work # CREATE TABLE t1 AS SELECT repeat('a',20) AS s1 LIMIT 0; SET time_zone=_latin1'+03:00'; SET timestamp=1216359724; INSERT INTO t1 VALUES (current_date); INSERT INTO t1 VALUES (current_time); INSERT INTO t1 VALUES (current_timestamp); SELECT s1, hex(s1) FROM t1; s1 hex(s1) 2008-07-18 000000320000003000000030000000380000002D00000030000000370000002D0000003100000038 08:42:04 00000030000000380000003A00000034000000320000003A0000003000000034 2008-07-18 08:42:04 000000320000003000000030000000380000002D00000030000000370000002D00000031000000380000002000000030000000380000003A00000034000000320000003A0000003000000034 DROP TABLE t1; SET timestamp=0; SET time_zone=default; # # MDEV-5298 Illegal mix of collations on timestamp # SELECT CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY); CHARSET('2013-11-15 00:41:28' - INTERVAL 7 DAY) utf32 SELECT COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY); COERCIBILITY('2013-11-15 00:41:28' - INTERVAL 7 DAY) 4 SELECT CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY); CHARSET(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY) binary SELECT COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY); COERCIBILITY(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY) 5 SELECT CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)); CHARSET(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)) utf32 SELECT COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)); COERCIBILITY(CONCAT('2013-11-15 00:41:28' - INTERVAL 7 DAY)) 4 SELECT CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)); CHARSET(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)) utf32 SELECT COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)); COERCIBILITY(CONCAT(TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY)) 4 SELECT CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)); CHARSET(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)) utf32 SELECT COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)); COERCIBILITY(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)) 4 SELECT HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)); HEX(CONCAT('','2001-01-08 00:00:00' - INTERVAL 7 DAY)) 000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030 SELECT CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)); CHARSET(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) utf32 SELECT COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)); COERCIBILITY(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 4 SELECT HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)); HEX(CONCAT('',TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030 CREATE TABLE t1 AS SELECT REPEAT('a', 64) AS a LIMIT 0; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(64) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES (''); SELECT CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; CHARSET(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) utf32 SELECT COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; COERCIBILITY(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 2 SELECT HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; HEX(CONCAT(a,'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030 SELECT CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; CHARSET(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) utf32 SELECT COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; COERCIBILITY(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 2 SELECT HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) FROM t1; HEX(CONCAT(a,TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY)) 000000320000003000000030000000310000002D00000030000000310000002D00000030000000310000002000000030000000300000003A00000030000000300000003A0000003000000030 DROP TABLE t1; CREATE TABLE t1 (t TIMESTAMP NOT NULL); INSERT INTO t1 VALUES ('2001-01-01 00:00:00'); SELECT * FROM t1 WHERE t < '2013-11-15 00:41:28' - INTERVAL 7 DAY; t 2001-01-01 00:00:00 SELECT * FROM t1 WHERE t = '2001-01-08 00:00:00' - INTERVAL 7 DAY; t 2001-01-01 00:00:00 SELECT * FROM t1 WHERE t < CONCAT('2013-11-15 00:41:28',LEFT(RAND(),0)) - INTERVAL 7 DAY; t 2001-01-01 00:00:00 SELECT * FROM t1 WHERE t = CONCAT('2001-01-08 00:00:00',LEFT(RAND(),0)) - INTERVAL 7 DAY; t 2001-01-01 00:00:00 SELECT * FROM t1 WHERE t < TIMESTAMP'2013-11-15 00:41:28' - INTERVAL 7 DAY; t 2001-01-01 00:00:00 SELECT * FROM t1 WHERE t = TIMESTAMP'2001-01-08 00:00:00' - INTERVAL 7 DAY; t 2001-01-01 00:00:00 DROP TABLE t1; SET NAMES latin1; set collation_connection=utf32_general_ci; drop table if exists t1; create table t1 as select repeat(' ', 64) as s1, repeat(' ',64) as s2 union select null, null; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `s1` varchar(64) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL, `s2` varchar(64) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci delete from t1; insert into t1 values('aaa','aaa'); insert into t1 values('aaa|qqq','qqq'); insert into t1 values('gheis','^[^a-dXYZ]+$'); insert into t1 values('aab','^aa?b'); insert into t1 values('Baaan','^Ba*n'); insert into t1 values('aaa','qqq|aaa'); insert into t1 values('qqq','qqq|aaa'); insert into t1 values('bbb','qqq|aaa'); insert into t1 values('bbb','qqq'); insert into t1 values('aaa','aba'); insert into t1 values(null,'abc'); insert into t1 values('def',null); insert into t1 values(null,null); insert into t1 values('ghi','ghi['); select HIGH_PRIORITY s1 regexp s2 from t1; s1 regexp s2 0 0 0 1 1 1 1 1 1 1 NULL NULL NULL NULL drop table t1; set names latin1; select hex(char(0x01 using utf32)); hex(char(0x01 using utf32)) 00000001 select hex(char(0x0102 using utf32)); hex(char(0x0102 using utf32)) 00000102 select hex(char(0x010203 using utf32)); hex(char(0x010203 using utf32)) 00010203 select hex(char(0x01020304 using utf32)); hex(char(0x01020304 using utf32)) NULL Warnings: Warning 1300 Invalid utf32 character string: '010203' create table t1 (s1 varchar(1) character set utf32, s2 text character set utf32); create index i on t1 (s1); insert into t1 values (char(256 using utf32), char(256 using utf32)); select hex(s1), hex(s2) from t1; hex(s1) hex(s2) 00000100 00000100 drop table t1; SET collation_connection=utf32_general_ci; CREATE TABLE t1 AS SELECT repeat('a',2) as s1 LIMIT 0; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `s1` varchar(2) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES ('ab'),('AE'),('ab'),('AE'); SELECT * FROM t1 ORDER BY s1; s1 ab ab AE AE SET max_sort_length=8; Warnings: Warning 1292 Truncated incorrect max_sort_length value: '8' SELECT * FROM t1 ORDER BY s1; s1 ab ab AE AE DROP TABLE t1; SET max_sort_length=DEFAULT; SET NAMES latin1; # # Bug#52520 Difference in tinytext utf column metadata # CREATE TABLE t1 ( s1 TINYTEXT CHARACTER SET utf32, s2 TEXT CHARACTER SET utf32, s3 MEDIUMTEXT CHARACTER SET utf32, s4 LONGTEXT CHARACTER SET utf32 ); SET NAMES utf8mb4, @@character_set_results=NULL; SELECT *, HEX(s1) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 s1 s1 252 255 0 Y 16 0 60 def test t1 t1 s2 s2 252 65535 0 Y 16 0 60 def test t1 t1 s3 s3 252 16777215 0 Y 16 0 60 def test t1 t1 s4 s4 252 4294967295 0 Y 16 0 60 def HEX(s1) 253 8160 0 Y 0 0 45 s1 s2 s3 s4 HEX(s1) SET NAMES latin1; SELECT *, HEX(s1) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 s1 s1 252 63 0 Y 16 0 8 def test t1 t1 s2 s2 252 16383 0 Y 16 0 8 def test t1 t1 s3 s3 252 4194303 0 Y 16 0 8 def test t1 t1 s4 s4 252 1073741823 0 Y 16 0 8 def HEX(s1) 253 2040 0 Y 0 0 8 s1 s2 s3 s4 HEX(s1) SET NAMES utf8mb4; SELECT *, HEX(s1) FROM t1; Catalog Database Table Table_alias Column Column_alias Type Length Max length Is_null Flags Decimals Charsetnr def test t1 t1 s1 s1 252 252 0 Y 16 0 45 def test t1 t1 s2 s2 252 65532 0 Y 16 0 45 def test t1 t1 s3 s3 252 16777212 0 Y 16 0 45 def test t1 t1 s4 s4 252 4294967292 0 Y 16 0 45 def HEX(s1) 253 8160 0 Y 0 0 45 s1 s2 s3 s4 HEX(s1) CREATE TABLE t2 AS SELECT CONCAT(s1) FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `CONCAT(s1)` varchar(255) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1, t2; # # Bug#45263 utf32_general_ci, bad effects around CREATE TABLE AS SELECT # SET collation_connection=utf32_general_ci; CREATE TABLE t1 AS SELECT HEX(0x00) AS my_col; SELECT * FROM t1; my_col 00 DROP TABLE t1; # # Bug#32859 Character sets: no warning with non-fitting chariot wheel # CREATE TABLE t1 (utf32 CHAR(5) CHARACTER SET utf32, latin1 CHAR(5) CHARACTER SET latin1); INSERT INTO t1 (utf32) VALUES (0xc581); UPDATE IGNORE t1 SET latin1 = utf32; Warnings: Warning 1366 Incorrect string value: '\x00\x00\xC5\x81' for column `test`.`t1`.`latin1` at row 1 DELETE FROM t1; INSERT INTO t1 (utf32) VALUES (0x100cc); UPDATE IGNORE t1 SET latin1 = utf32; Warnings: Warning 1366 Incorrect string value: '\x00\x01\x00\xCC' for column `test`.`t1`.`latin1` at row 1 DROP TABLE t1; # # Bug#55912 FORMAT with locale set fails for numbers < 1000 # SET collation_connection=utf32_general_ci; CREATE TABLE t1 AS SELECT format(123,2,'no_NO'); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `format(123,2,'no_NO')` varchar(17) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT * FROM t1; format(123,2,'no_NO') 123,00 DROP TABLE t1; # # Bug#42511 mysqld: ctype-ucs2.c:2044: my_strnncollsp_utf32: Assertion (tlen % 4) == 0' faied # CREATE TABLE t1 ( b char(250) CHARACTER SET utf32, key (b) ) ENGINE=MYISAM; INSERT INTO t1 VALUES ('d'),('f'); SELECT * FROM t1 WHERE b BETWEEN 'a' AND 'z'; b d f DROP TABLE t1; # # Bug#11753363 (Bug#44793) Character sets: case clause, ucs2 or utf32, failure # SELECT CASE _latin1'a' WHEN _utf32'a' THEN 'A' END; CASE _latin1'a' WHEN _utf32'a' THEN 'A' END A SELECT CASE _utf32'a' WHEN _latin1'a' THEN 'A' END; CASE _utf32'a' WHEN _latin1'a' THEN 'A' END A CREATE TABLE t1 (s1 CHAR(5) CHARACTER SET utf32); INSERT INTO t1 VALUES ('a'); SELECT CASE s1 WHEN 'a' THEN 'b' ELSE 'c' END FROM t1; CASE s1 WHEN 'a' THEN 'b' ELSE 'c' END b DROP TABLE t1; # # Bug#11750518 41090: ORDER BY TRUNCATES GROUP_CONCAT RESULT # SET NAMES utf8, @@character_set_connection=utf32; SET STATEMENT group_concat_max_len=1024 FOR SELECT id, CHAR_LENGTH(GROUP_CONCAT(body)) AS l FROM (SELECT 'a' AS id, REPEAT('foo bar', 100) AS body UNION ALL SELECT 'a' AS id, REPEAT('bla bla', 100) AS body) t1 GROUP BY id ORDER BY l DESC; id l a 256 Warnings: Warning 1260 Row 1 was cut by GROUP_CONCAT() # # incorrect charset for val_str_ascii # SELECT '2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second; '2010-10-10 10:10:10' + INTERVAL GeometryType(GeomFromText('POINT(1 1)')) hour_second 2010-10-10 10:10:10 # # MDEV-5745 analyze MySQL fix for bug#12368495 # SELECT CHAR_LENGTH(TRIM(LEADING 0x0000000000 FROM _utf32 0x00000061)); CHAR_LENGTH(TRIM(LEADING 0x0000000000 FROM _utf32 0x00000061)) 4 SELECT CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _utf32 0x00000061)); CHAR_LENGTH(TRIM(LEADING 0x0001 FROM _utf32 0x00000061)) 4 SELECT CHAR_LENGTH(TRIM(LEADING 0x00 FROM _utf32 0x00000061)); CHAR_LENGTH(TRIM(LEADING 0x00 FROM _utf32 0x00000061)) 1 SELECT CHAR_LENGTH(TRIM(TRAILING 0x0000000000 FROM _utf32 0x00000061)); CHAR_LENGTH(TRIM(TRAILING 0x0000000000 FROM _utf32 0x00000061)) 4 SELECT CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _utf32 0x00000061)); CHAR_LENGTH(TRIM(TRAILING 0x0001 FROM _utf32 0x00000061)) 4 SELECT CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _utf32 0x00000061)); CHAR_LENGTH(TRIM(TRAILING 0x61 FROM _utf32 0x00000061)) 3 SELECT CHAR_LENGTH(TRIM(BOTH 0x0000000000 FROM _utf32 0x00000061)); CHAR_LENGTH(TRIM(BOTH 0x0000000000 FROM _utf32 0x00000061)) 4 SELECT CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _utf32 0x00000061)); CHAR_LENGTH(TRIM(BOTH 0x0001 FROM _utf32 0x00000061)) 4 SELECT CHAR_LENGTH(TRIM(BOTH 0x61 FROM _utf32 0x00000061)); CHAR_LENGTH(TRIM(BOTH 0x61 FROM _utf32 0x00000061)) 3 SELECT CHAR_LENGTH(TRIM(BOTH 0x00 FROM _utf32 0x00000061)); CHAR_LENGTH(TRIM(BOTH 0x00 FROM _utf32 0x00000061)) 1 select hex(lower(cast(0xffff0000 as char character set utf32))) as c; c 0000003F0000003F0000003F0000003F Warnings: Warning 1300 Invalid utf32 character string: '\xFF\xFF\x00\x00' # # MDEV-11685: sql_mode can't be set with non-ascii connection charset # SET character_set_connection=utf32; SET sql_mode='NO_ENGINE_SUBSTITUTION'; SELECT @@sql_mode; @@sql_mode NO_ENGINE_SUBSTITUTION SET sql_mode=DEFAULT; SET NAMES utf8; # # MDEV-13530 VARBINARY doesn't convert to to BLOB for sizes 65533, 65534 and 65535 # set sql_mode=''; CREATE TABLE t1 (c1 VARCHAR(16383) CHARACTER SET utf32); DESCRIBE t1; Field Type Null Key Default Extra c1 varchar(16383) YES NULL DROP TABLE t1; CREATE TABLE t1 (c1 VARCHAR(16384) CHARACTER SET utf32); Warnings: Note 1246 Converting column 'c1' from VARCHAR to TEXT DESCRIBE t1; Field Type Null Key Default Extra c1 mediumtext YES NULL DROP TABLE t1; set sql_mode=default; # # End of 5.5 tests # # # Start of 5.6 tests # # # WL#3664 WEIGHT_STRING # set collation_connection=utf32_general_ci; select @@collation_connection; @@collation_connection utf32_general_ci CREATE TABLE t1 AS SELECT 'a' AS a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(1) CHARACTER SET utf32 COLLATE utf32_general_ci NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `ws` varbinary(2) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT HEX(WEIGHT_STRING(a)) FROM t1; HEX(WEIGHT_STRING(a)) 0041 SELECT HEX(ws) FROM t2; HEX(ws) 0041 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 AS SELECT REPEAT('a',5) AS a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(5) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `ws` varbinary(10) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT HEX(WEIGHT_STRING(a)) FROM t1; HEX(WEIGHT_STRING(a)) 00410041004100410041 SELECT HEX(ws) FROM t2; HEX(ws) 00410041004100410041 DROP TABLE t2; CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(3)) AS ws FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `ws` varbinary(6) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1; HEX(WEIGHT_STRING(a AS CHAR(3))) 004100410041 SELECT HEX(ws) FROM t2; HEX(ws) 004100410041 DROP TABLE t2; CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(10)) AS ws FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `ws` varbinary(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; HEX(WEIGHT_STRING(a AS CHAR(10))) 0041004100410041004100200020002000200020 SELECT HEX(ws) FROM t2; HEX(ws) 0041004100410041004100200020002000200020 DROP TABLE t2; DROP TABLE t1; select hex(weight_string('a')); hex(weight_string('a')) 0041 select hex(weight_string('A')); hex(weight_string('A')) 0041 select hex(weight_string('abc')); hex(weight_string('abc')) 004100420043 select hex(weight_string('abc' as char(2))); hex(weight_string('abc' as char(2))) 00410042 select hex(weight_string('abc' as char(3))); hex(weight_string('abc' as char(3))) 004100420043 select hex(weight_string('abc' as char(5))); hex(weight_string('abc' as char(5))) 00410042004300200020 select hex(weight_string('abc', 1, 2, 0xC0)); hex(weight_string('abc', 1, 2, 0xC0)) 00 select hex(weight_string('abc', 2, 2, 0xC0)); hex(weight_string('abc', 2, 2, 0xC0)) 0041 select hex(weight_string('abc', 3, 2, 0xC0)); hex(weight_string('abc', 3, 2, 0xC0)) 004100 select hex(weight_string('abc', 4, 2, 0xC0)); hex(weight_string('abc', 4, 2, 0xC0)) 00410042 select hex(weight_string('abc', 5, 2, 0xC0)); hex(weight_string('abc', 5, 2, 0xC0)) 0041004200 select hex(weight_string('abc',25, 2, 0xC0)); hex(weight_string('abc',25, 2, 0xC0)) 00410042002000200020002000200020002000200020002000 select hex(weight_string('abc', 1, 3, 0xC0)); hex(weight_string('abc', 1, 3, 0xC0)) 00 select hex(weight_string('abc', 2, 3, 0xC0)); hex(weight_string('abc', 2, 3, 0xC0)) 0041 select hex(weight_string('abc', 3, 3, 0xC0)); hex(weight_string('abc', 3, 3, 0xC0)) 004100 select hex(weight_string('abc', 4, 3, 0xC0)); hex(weight_string('abc', 4, 3, 0xC0)) 00410042 select hex(weight_string('abc', 5, 3, 0xC0)); hex(weight_string('abc', 5, 3, 0xC0)) 0041004200 select hex(weight_string('abc',25, 3, 0xC0)); hex(weight_string('abc',25, 3, 0xC0)) 00410042004300200020002000200020002000200020002000 select hex(weight_string('abc', 1, 4, 0xC0)); hex(weight_string('abc', 1, 4, 0xC0)) 00 select hex(weight_string('abc', 2, 4, 0xC0)); hex(weight_string('abc', 2, 4, 0xC0)) 0041 select hex(weight_string('abc', 3, 4, 0xC0)); hex(weight_string('abc', 3, 4, 0xC0)) 004100 select hex(weight_string('abc', 4, 4, 0xC0)); hex(weight_string('abc', 4, 4, 0xC0)) 00410042 select hex(weight_string('abc', 5, 4, 0xC0)); hex(weight_string('abc', 5, 4, 0xC0)) 0041004200 select hex(weight_string('abc',25, 4, 0xC0)); hex(weight_string('abc',25, 4, 0xC0)) 00410042004300200020002000200020002000200020002000 select @@collation_connection; @@collation_connection utf32_general_ci select hex(weight_string(cast(_latin1 0x80 as char))); hex(weight_string(cast(_latin1 0x80 as char))) 20AC select hex(weight_string(cast(_latin1 0x808080 as char))); hex(weight_string(cast(_latin1 0x808080 as char))) 20AC20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(2))); hex(weight_string(cast(_latin1 0x808080 as char) as char(2))) 20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(3))); hex(weight_string(cast(_latin1 0x808080 as char) as char(3))) 20AC20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(5))); hex(weight_string(cast(_latin1 0x808080 as char) as char(5))) 20AC20AC20AC00200020 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)) 20 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)) 20AC select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)) 20AC20 select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)) 20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)) 20AC20AC00 select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)) 20AC20AC002000200020002000200020002000200020002000 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)) 20 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)) 20AC select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)) 20AC20 select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)) 20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)) 20AC20AC20 select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)) 20AC20AC20AC00200020002000200020002000200020002000 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)) 20 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)) 20AC select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)) 20AC20 select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)) 20AC20AC select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)) 20AC20AC20 select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)) 20AC20AC20AC00200020002000200020002000200020002000 select hex(weight_string(_utf32 0x10000)); hex(weight_string(_utf32 0x10000)) FFFD select hex(weight_string(_utf32 0x10001)); hex(weight_string(_utf32 0x10001)) FFFD select @@collation_connection; @@collation_connection utf32_general_ci select hex(weight_string('a' LEVEL 1)); hex(weight_string('a' LEVEL 1)) 0041 select hex(weight_string('A' LEVEL 1)); hex(weight_string('A' LEVEL 1)) 0041 select hex(weight_string('abc' LEVEL 1)); hex(weight_string('abc' LEVEL 1)) 004100420043 select hex(weight_string('abc' as char(2) LEVEL 1)); hex(weight_string('abc' as char(2) LEVEL 1)) 00410042 select hex(weight_string('abc' as char(3) LEVEL 1)); hex(weight_string('abc' as char(3) LEVEL 1)) 004100420043 select hex(weight_string('abc' as char(5) LEVEL 1)); hex(weight_string('abc' as char(5) LEVEL 1)) 00410042004300200020 select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)); hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)) 20002000430042004100 select hex(weight_string('abc' as char(5) LEVEL 1 DESC)); hex(weight_string('abc' as char(5) LEVEL 1 DESC)) FFBEFFBDFFBCFFDFFFDF select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)); hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)) DFFFDFFFBCFFBDFFBEFF set collation_connection=utf32_bin; select @@collation_connection; @@collation_connection utf32_bin CREATE TABLE t1 AS SELECT 'a' AS a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(1) CHARACTER SET utf32 COLLATE utf32_bin NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `ws` varbinary(3) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT HEX(WEIGHT_STRING(a)) FROM t1; HEX(WEIGHT_STRING(a)) 000061 SELECT HEX(ws) FROM t2; HEX(ws) 000061 DROP TABLE t2; DROP TABLE t1; CREATE TABLE t1 AS SELECT REPEAT('a',5) AS a; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(5) CHARACTER SET utf32 COLLATE utf32_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci CREATE TABLE t2 AS SELECT WEIGHT_STRING(a) AS ws FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `ws` varbinary(15) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT HEX(WEIGHT_STRING(a)) FROM t1; HEX(WEIGHT_STRING(a)) 000061000061000061000061000061 SELECT HEX(ws) FROM t2; HEX(ws) 000061000061000061000061000061 DROP TABLE t2; CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(3)) AS ws FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `ws` varbinary(9) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT HEX(WEIGHT_STRING(a AS CHAR(3))) FROM t1; HEX(WEIGHT_STRING(a AS CHAR(3))) 000061000061000061 SELECT HEX(ws) FROM t2; HEX(ws) 000061000061000061 DROP TABLE t2; CREATE TABLE t2 AS SELECT WEIGHT_STRING(a AS CHAR(10)) AS ws FROM t1; SHOW CREATE TABLE t2; Table Create Table t2 CREATE TABLE `t2` ( `ws` varbinary(30) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; HEX(WEIGHT_STRING(a AS CHAR(10))) 000061000061000061000061000061000020000020000020000020000020 SELECT HEX(ws) FROM t2; HEX(ws) 000061000061000061000061000061000020000020000020000020000020 DROP TABLE t2; DROP TABLE t1; select hex(weight_string('a')); hex(weight_string('a')) 000061 select hex(weight_string('A')); hex(weight_string('A')) 000041 select hex(weight_string('abc')); hex(weight_string('abc')) 000061000062000063 select hex(weight_string('abc' as char(2))); hex(weight_string('abc' as char(2))) 000061000062 select hex(weight_string('abc' as char(3))); hex(weight_string('abc' as char(3))) 000061000062000063 select hex(weight_string('abc' as char(5))); hex(weight_string('abc' as char(5))) 000061000062000063000020000020 select hex(weight_string('abc', 1, 2, 0xC0)); hex(weight_string('abc', 1, 2, 0xC0)) 00 select hex(weight_string('abc', 2, 2, 0xC0)); hex(weight_string('abc', 2, 2, 0xC0)) 0000 select hex(weight_string('abc', 3, 2, 0xC0)); hex(weight_string('abc', 3, 2, 0xC0)) 000061 select hex(weight_string('abc', 4, 2, 0xC0)); hex(weight_string('abc', 4, 2, 0xC0)) 00006100 select hex(weight_string('abc', 5, 2, 0xC0)); hex(weight_string('abc', 5, 2, 0xC0)) 0000610000 select hex(weight_string('abc',25, 2, 0xC0)); hex(weight_string('abc',25, 2, 0xC0)) 00006100006200002000002000002000002000002000002000 select hex(weight_string('abc', 1, 3, 0xC0)); hex(weight_string('abc', 1, 3, 0xC0)) 00 select hex(weight_string('abc', 2, 3, 0xC0)); hex(weight_string('abc', 2, 3, 0xC0)) 0000 select hex(weight_string('abc', 3, 3, 0xC0)); hex(weight_string('abc', 3, 3, 0xC0)) 000061 select hex(weight_string('abc', 4, 3, 0xC0)); hex(weight_string('abc', 4, 3, 0xC0)) 00006100 select hex(weight_string('abc', 5, 3, 0xC0)); hex(weight_string('abc', 5, 3, 0xC0)) 0000610000 select hex(weight_string('abc',25, 3, 0xC0)); hex(weight_string('abc',25, 3, 0xC0)) 00006100006200006300002000002000002000002000002000 select hex(weight_string('abc', 1, 4, 0xC0)); hex(weight_string('abc', 1, 4, 0xC0)) 00 select hex(weight_string('abc', 2, 4, 0xC0)); hex(weight_string('abc', 2, 4, 0xC0)) 0000 select hex(weight_string('abc', 3, 4, 0xC0)); hex(weight_string('abc', 3, 4, 0xC0)) 000061 select hex(weight_string('abc', 4, 4, 0xC0)); hex(weight_string('abc', 4, 4, 0xC0)) 00006100 select hex(weight_string('abc', 5, 4, 0xC0)); hex(weight_string('abc', 5, 4, 0xC0)) 0000610000 select hex(weight_string('abc',25, 4, 0xC0)); hex(weight_string('abc',25, 4, 0xC0)) 00006100006200006300002000002000002000002000002000 select @@collation_connection; @@collation_connection utf32_bin select hex(weight_string(cast(_latin1 0x80 as char))); hex(weight_string(cast(_latin1 0x80 as char))) 0020AC select hex(weight_string(cast(_latin1 0x808080 as char))); hex(weight_string(cast(_latin1 0x808080 as char))) 0020AC0020AC0020AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(2))); hex(weight_string(cast(_latin1 0x808080 as char) as char(2))) 0020AC0020AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(3))); hex(weight_string(cast(_latin1 0x808080 as char) as char(3))) 0020AC0020AC0020AC select hex(weight_string(cast(_latin1 0x808080 as char) as char(5))); hex(weight_string(cast(_latin1 0x808080 as char) as char(5))) 0020AC0020AC0020AC000020000020 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 2, 0xC0)) 00 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 2, 0xC0)) 0020 select hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 2, 0xC0)) 0020AC select hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 2, 0xC0)) 0020AC00 select hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 2, 0xC0)) 0020AC0020 select hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 2, 0xC0)) 0020AC0020AC00002000002000002000002000002000002000 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 3, 0xC0)) 00 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 3, 0xC0)) 0020 select hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 3, 0xC0)) 0020AC select hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 3, 0xC0)) 0020AC00 select hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 3, 0xC0)) 0020AC0020 select hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 3, 0xC0)) 0020AC0020AC0020AC00002000002000002000002000002000 select hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 1, 4, 0xC0)) 00 select hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 2, 4, 0xC0)) 0020 select hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 3, 4, 0xC0)) 0020AC select hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 4, 4, 0xC0)) 0020AC00 select hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char), 5, 4, 0xC0)) 0020AC0020 select hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)); hex(weight_string(cast(_latin1 0x808080 as char),25, 4, 0xC0)) 0020AC0020AC0020AC00002000002000002000002000002000 select @@collation_connection; @@collation_connection utf32_bin select hex(weight_string('a' LEVEL 1)); hex(weight_string('a' LEVEL 1)) 000061 select hex(weight_string('A' LEVEL 1)); hex(weight_string('A' LEVEL 1)) 000041 select hex(weight_string('abc' LEVEL 1)); hex(weight_string('abc' LEVEL 1)) 000061000062000063 select hex(weight_string('abc' as char(2) LEVEL 1)); hex(weight_string('abc' as char(2) LEVEL 1)) 000061000062 select hex(weight_string('abc' as char(3) LEVEL 1)); hex(weight_string('abc' as char(3) LEVEL 1)) 000061000062000063 select hex(weight_string('abc' as char(5) LEVEL 1)); hex(weight_string('abc' as char(5) LEVEL 1)) 000061000062000063000020000020 select hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)); hex(weight_string('abc' as char(5) LEVEL 1 REVERSE)) 200000200000630000620000610000 select hex(weight_string('abc' as char(5) LEVEL 1 DESC)); hex(weight_string('abc' as char(5) LEVEL 1 DESC)) FFFF9EFFFF9DFFFF9CFFFFDFFFFFDF select hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)); hex(weight_string('abc' as char(5) LEVEL 1 DESC REVERSE)) DFFFFFDFFFFF9CFFFF9DFFFF9EFFFF # # End of 5.6 tests # # # Start of 10.0 tests # # # MDEV-6661 PI() does not work well in UCS2/UTF16/UTF32 context # SELECT CONCAT(CONVERT('pi=' USING utf32),PI()) AS PI; PI pi=3.141593 # # MDEV-6666 Malformed result for CONCAT(utf8_column, binary_string) # SET NAMES utf8mb4; CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32); INSERT INTO t1 VALUES ('a'); SELECT CONCAT(a,0x20FFFF) FROM t1; ERROR HY000: Invalid utf32 character string: '0020FF' SELECT CONCAT(a,0x010000) FROM t1; CONCAT(a,0x010000) a𐀀 SELECT CONCAT(a,0x00FF) FROM t1; CONCAT(a,0x00FF) aÿ DROP TABLE t1; SELECT CONCAT(_utf32'a' COLLATE utf32_unicode_ci, _binary 0x20FFFF); ERROR HY000: Invalid utf32 character string: '0020FF' PREPARE stmt FROM "SELECT CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?)"; SET @arg00=_binary 0x20FFFF; EXECUTE stmt USING @arg00; ERROR HY000: Invalid utf32 character string: '0020FF' SET @arg00=_binary 0x010000; EXECUTE stmt USING @arg00; CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?) a𐀀 SET @arg00=_binary 0x00FF; EXECUTE stmt USING @arg00; CONCAT(_utf32'a' COLLATE utf32_unicode_ci, ?) aÿ DEALLOCATE PREPARE stmt; SET NAMEs utf8, character_set_connection=utf32; # # MDEV-13118 Wrong results with LOWER and UPPER and subquery # SET @save_optimizer_switch=@@optimizer_switch; SET optimizer_switch=_latin1'derived_merge=on'; CREATE TABLE t1 AS SELECT REPEAT('a', 10) AS t LIMIT 0; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `t` varchar(10) CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 VALUES ('abcdefghi'),('ABCDEFGHI'); SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT LOWER(t) t2 FROM t1) sub; c2 abcdefghi-abcdefghi abcdefghi-abcdefghi SELECT CONCAT(t2,'-',t2) c2 FROM (SELECT UPPER(t) t2 FROM t1) sub; c2 ABCDEFGHI-ABCDEFGHI ABCDEFGHI-ABCDEFGHI DROP TABLE t1; SET optimizer_switch=@save_optimizer_switch; # # End of 10.0 tests # # # Start of 10.1 tests # # # MDEV-8419 utf32: compare broken bytes as "greater than any non-broken character" # CREATE TABLE t1 (a VARCHAR(10) CHARACTER SET utf32, KEY(a)); INSERT INTO t1 VALUES (0x10000),(0x10001),(0x10002); SELECT COUNT(DISTINCT a) FROM t1; COUNT(DISTINCT a) 1 DROP TABLE t1; SELECT _utf32 0x10001=_utf32 0x10002; _utf32 0x10001=_utf32 0x10002 1 # # MDEV-9178 Wrong result for CAST(CONVERT('1IJ3' USING ucs2) AS SIGNED) # SET NAMES utf8; SELECT CAST(CONVERT('1IJ3' USING utf32) AS SIGNED); CAST(CONVERT('1IJ3' USING utf32) AS SIGNED) 1 Warnings: Warning 1292 Truncated incorrect INTEGER value: '1IJ3' # # End of 10.1 tests # # # Start of 10.2 tests # # # MDEV-9711 NO PAD Collatons # SET character_set_connection=utf32; SET DEFAULT_STORAGE_ENGINE=MyISAM; # # Start of ctype_pad.inc # # # Unique indexes # CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf32_general_nopad_ci'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_general_nopad_ci INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); SELECT HEX(a), a FROM t1 ORDER BY a; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 0000006100000062000000630000002000000020 abc SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 0000006100000062000000630000002000000020 abc SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; HEX(a) a 0000006100000062000000630000002000000020 abc 000000610000006200000063 abc 00000061000000200000002000000020 a 000000200000006100000020 a 0000002000000061 a # # UNION # CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf32_general_nopad_ci'; INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; HEX(a) a 0000002000000061 a 000000200000006100000020 a 0000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 00000061000000620000006300000020 abc 0000006100000062000000630000002000000020 abc DROP TABLE t1; DROP TABLE t2; # # DISTINCT, COUNT, MAX # CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_general_nopad_ci'; INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061 a 000000610000002000000020 a 00000061000000200000002000000020 a SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; COUNT(DISTINCT a) 5 SELECT HEX(MAX(a)), MAX(a) FROM t1; HEX(MAX(a)) MAX(a) 00000061000000200000002000000020 a # # GROUP BY # CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf32_general_nopad_ci'; INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; HEX(a) cnt 0000006100000061 1 00000061000000610000002000000020 1 0000006100000062 2 DROP TABLE t2; # # Weights # SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; HEX(WEIGHT_STRING(a AS CHAR(10))) 0041000000000000000000000000000000000000 0041002000200000000000000000000000000000 0020004100000000000000000000000000000000 0020004100200000000000000000000000000000 0041002000200020000000000000000000000000 DROP TABLE t1; # # IF, CASE, LEAST # SELECT IF('abc' COLLATE 'utf32_general_nopad_ci' = 'abc ', 'pad', 'nopad'); IF('abc' COLLATE 'utf32_general_nopad_ci' = 'abc ', 'pad', 'nopad') nopad SELECT CASE 'abc' COLLATE 'utf32_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; CASE 'abc' COLLATE 'utf32_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END nopad SELECT CASE WHEN 'abc' COLLATE 'utf32_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; CASE WHEN 'abc' COLLATE 'utf32_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END nopad SELECT HEX(LEAST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')); HEX(LEAST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')) 00000061000000620000006300000020 SELECT HEX(GREATEST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')); HEX(GREATEST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')) 0000006100000062000000630000002000000020 # # Collation mix # CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_general_ci'; INSERT INTO t1 VALUES ('a'),('a '); SELECT COUNT(*) FROM t1 WHERE a='a'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_ci'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_nopad_ci'; COUNT(*) 1 ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf32_general_nopad_ci'; SELECT COUNT(*) FROM t1 WHERE a='a'; COUNT(*) 1 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_ci'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_nopad_ci'; COUNT(*) 1 DROP TABLE t1; # # End of ctype_pad.inc # SET DEFAULT_STORAGE_ENGINE=HEAP; # # Start of ctype_pad.inc # # # Unique indexes # CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf32_general_nopad_ci'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=MEMORY DEFAULT CHARSET=utf32 COLLATE=utf32_general_nopad_ci INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); SELECT HEX(a), a FROM t1 ORDER BY a; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 0000006100000062000000630000002000000020 abc SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 0000006100000062000000630000002000000020 abc SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; HEX(a) a 0000006100000062000000630000002000000020 abc 000000610000006200000063 abc 00000061000000200000002000000020 a 000000200000006100000020 a 0000002000000061 a # # UNION # CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf32_general_nopad_ci'; INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; HEX(a) a 0000002000000061 a 000000200000006100000020 a 0000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 00000061000000620000006300000020 abc 0000006100000062000000630000002000000020 abc DROP TABLE t1; DROP TABLE t2; # # DISTINCT, COUNT, MAX # CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_general_nopad_ci'; INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061 a 000000610000002000000020 a 00000061000000200000002000000020 a SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; COUNT(DISTINCT a) 5 SELECT HEX(MAX(a)), MAX(a) FROM t1; HEX(MAX(a)) MAX(a) 00000061000000200000002000000020 a # # GROUP BY # CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf32_general_nopad_ci'; INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; HEX(a) cnt 0000006100000061 1 00000061000000610000002000000020 1 0000006100000062 2 DROP TABLE t2; # # Weights # SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; HEX(WEIGHT_STRING(a AS CHAR(10))) 0041000000000000000000000000000000000000 0041002000200000000000000000000000000000 0020004100000000000000000000000000000000 0020004100200000000000000000000000000000 0041002000200020000000000000000000000000 DROP TABLE t1; # # IF, CASE, LEAST # SELECT IF('abc' COLLATE 'utf32_general_nopad_ci' = 'abc ', 'pad', 'nopad'); IF('abc' COLLATE 'utf32_general_nopad_ci' = 'abc ', 'pad', 'nopad') nopad SELECT CASE 'abc' COLLATE 'utf32_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; CASE 'abc' COLLATE 'utf32_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END nopad SELECT CASE WHEN 'abc' COLLATE 'utf32_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; CASE WHEN 'abc' COLLATE 'utf32_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END nopad SELECT HEX(LEAST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')); HEX(LEAST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')) 00000061000000620000006300000020 SELECT HEX(GREATEST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')); HEX(GREATEST('abc ' COLLATE 'utf32_general_nopad_ci', 'abc ')) 0000006100000062000000630000002000000020 # # Collation mix # CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_general_ci'; INSERT INTO t1 VALUES ('a'),('a '); SELECT COUNT(*) FROM t1 WHERE a='a'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_ci'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_nopad_ci'; COUNT(*) 1 ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf32_general_nopad_ci'; SELECT COUNT(*) FROM t1 WHERE a='a'; COUNT(*) 1 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_ci'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_general_nopad_ci'; COUNT(*) 1 DROP TABLE t1; # # End of ctype_pad.inc # SET DEFAULT_STORAGE_ENGINE=Default; SET DEFAULT_STORAGE_ENGINE=MyISAM; # # Start of ctype_pad.inc # # # Unique indexes # CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf32_nopad_bin'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=MyISAM DEFAULT CHARSET=utf32 COLLATE=utf32_nopad_bin INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); SELECT HEX(a), a FROM t1 ORDER BY a; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 0000006100000062000000630000002000000020 abc SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 0000006100000062000000630000002000000020 abc SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; HEX(a) a 0000006100000062000000630000002000000020 abc 000000610000006200000063 abc 00000061000000200000002000000020 a 000000200000006100000020 a 0000002000000061 a # # UNION # CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf32_nopad_bin'; INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; HEX(a) a 0000002000000061 a 000000200000006100000020 a 0000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 00000061000000620000006300000020 abc 0000006100000062000000630000002000000020 abc DROP TABLE t1; DROP TABLE t2; # # DISTINCT, COUNT, MAX # CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_nopad_bin'; INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061 a 000000610000002000000020 a 00000061000000200000002000000020 a SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; COUNT(DISTINCT a) 5 SELECT HEX(MAX(a)), MAX(a) FROM t1; HEX(MAX(a)) MAX(a) 00000061000000200000002000000020 a # # GROUP BY # CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf32_nopad_bin'; INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; HEX(a) cnt 0000006100000061 1 00000061000000610000002000000020 1 0000006100000062 2 DROP TABLE t2; # # Weights # SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; HEX(WEIGHT_STRING(a AS CHAR(10))) 000061000000000000000000000000000000000000000000000000000000 000061000020000020000000000000000000000000000000000000000000 000020000061000000000000000000000000000000000000000000000000 000020000061000020000000000000000000000000000000000000000000 000061000020000020000020000000000000000000000000000000000000 DROP TABLE t1; # # IF, CASE, LEAST # SELECT IF('abc' COLLATE 'utf32_nopad_bin' = 'abc ', 'pad', 'nopad'); IF('abc' COLLATE 'utf32_nopad_bin' = 'abc ', 'pad', 'nopad') nopad SELECT CASE 'abc' COLLATE 'utf32_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; CASE 'abc' COLLATE 'utf32_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END nopad SELECT CASE WHEN 'abc' COLLATE 'utf32_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; CASE WHEN 'abc' COLLATE 'utf32_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END nopad SELECT HEX(LEAST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')); HEX(LEAST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')) 00000061000000620000006300000020 SELECT HEX(GREATEST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')); HEX(GREATEST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')) 0000006100000062000000630000002000000020 # # Collation mix # CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_bin'; INSERT INTO t1 VALUES ('a'),('a '); SELECT COUNT(*) FROM t1 WHERE a='a'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_bin'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_nopad_bin'; COUNT(*) 1 ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf32_nopad_bin'; SELECT COUNT(*) FROM t1 WHERE a='a'; COUNT(*) 1 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_bin'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_nopad_bin'; COUNT(*) 1 DROP TABLE t1; # # End of ctype_pad.inc # SET DEFAULT_STORAGE_ENGINE=HEAP; # # Start of ctype_pad.inc # # # Unique indexes # CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf32_nopad_bin'; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(10) NOT NULL, PRIMARY KEY (`a`) ) ENGINE=MEMORY DEFAULT CHARSET=utf32 COLLATE=utf32_nopad_bin INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); SELECT HEX(a), a FROM t1 ORDER BY a; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 0000006100000062000000630000002000000020 abc SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 0000006100000062000000630000002000000020 abc SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; HEX(a) a 0000006100000062000000630000002000000020 abc 000000610000006200000063 abc 00000061000000200000002000000020 a 000000200000006100000020 a 0000002000000061 a # # UNION # CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf32_nopad_bin'; INSERT INTO t2 VALUES ('abc '),('abc '),(' a'),('a '); SELECT HEX(a),a FROM (SELECT * FROM t1 UNION SELECT * FROM t2 ORDER BY a) td; HEX(a) a 0000002000000061 a 000000200000006100000020 a 0000006100000020 a 00000061000000200000002000000020 a 000000610000006200000063 abc 00000061000000620000006300000020 abc 0000006100000062000000630000002000000020 abc DROP TABLE t1; DROP TABLE t2; # # DISTINCT, COUNT, MAX # CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_nopad_bin'; INSERT INTO t1 VALUES ('a'),('a '),(' a'),(' a '),('a '); SELECT HEX(a), a FROM (SELECT DISTINCT a FROM t1 ORDER BY a) td; HEX(a) a 0000002000000061 a 000000200000006100000020 a 00000061 a 000000610000002000000020 a 00000061000000200000002000000020 a SELECT COUNT(DISTINCT a) FROM t1 ORDER BY a; COUNT(DISTINCT a) 5 SELECT HEX(MAX(a)), MAX(a) FROM t1; HEX(MAX(a)) MAX(a) 00000061000000200000002000000020 a # # GROUP BY # CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf32_nopad_bin'; INSERT t2 values('ab', 12, 'cd'), ('ab', 2, 'ed'), ('aa', 20, 'er'), ('aa ', 0, 'er '); SELECT HEX(a), cnt FROM (SELECT a, COUNT(a) AS cnt FROM t2 GROUP BY a ORDER BY a) AS td; HEX(a) cnt 0000006100000061 1 00000061000000610000002000000020 1 0000006100000062 2 DROP TABLE t2; # # Weights # SELECT HEX(WEIGHT_STRING(a AS CHAR(10))) FROM t1; HEX(WEIGHT_STRING(a AS CHAR(10))) 000061000000000000000000000000000000000000000000000000000000 000061000020000020000000000000000000000000000000000000000000 000020000061000000000000000000000000000000000000000000000000 000020000061000020000000000000000000000000000000000000000000 000061000020000020000020000000000000000000000000000000000000 DROP TABLE t1; # # IF, CASE, LEAST # SELECT IF('abc' COLLATE 'utf32_nopad_bin' = 'abc ', 'pad', 'nopad'); IF('abc' COLLATE 'utf32_nopad_bin' = 'abc ', 'pad', 'nopad') nopad SELECT CASE 'abc' COLLATE 'utf32_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; CASE 'abc' COLLATE 'utf32_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END nopad SELECT CASE WHEN 'abc' COLLATE 'utf32_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; CASE WHEN 'abc' COLLATE 'utf32_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END nopad SELECT HEX(LEAST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')); HEX(LEAST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')) 00000061000000620000006300000020 SELECT HEX(GREATEST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')); HEX(GREATEST('abc ' COLLATE 'utf32_nopad_bin', 'abc ')) 0000006100000062000000630000002000000020 # # Collation mix # CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf32_bin'; INSERT INTO t1 VALUES ('a'),('a '); SELECT COUNT(*) FROM t1 WHERE a='a'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_bin'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_nopad_bin'; COUNT(*) 1 ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf32_nopad_bin'; SELECT COUNT(*) FROM t1 WHERE a='a'; COUNT(*) 1 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_bin'; COUNT(*) 2 SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf32_nopad_bin'; COUNT(*) 1 DROP TABLE t1; # # End of ctype_pad.inc # SET DEFAULT_STORAGE_ENGINE=Default; # # MDEV-22111 ERROR 1064 & 1033 and SIGSEGV on CREATE TABLE w/ various charsets on 10.4/5 optimized builds | Assertion `(uint) (table_check_constraints - share->check_constraints) == (uint) (share->table_check_constraints - share->field_check_constraints)' failed # 10.2 tests # SET NAMES utf8, COLLATION_CONNECTION=utf32_bin; CREATE TABLE t1(c1 ENUM('a','b','ac') CHARACTER SET 'Binary',c2 JSON,c3 INT); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` enum('\0\0\0a','\0\0\0b','\0\0\0a\0\0\0c') CHARACTER SET binary DEFAULT NULL, `c2` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`c2`)), `c3` int(11) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci INSERT INTO t1 (c1) VALUES (1),(2),(3); SELECT HEX(c1) FROM t1 ORDER BY c1; HEX(c1) 00000061 00000062 0000006100000063 DROP TABLE t1; # # MDEV-23210 Assertion `(length % 4) == 0' failed in my_lengthsp_utf32 on ALTER TABLE, SELECT and INSERT # CREATE TABLE t1 (a CHAR(1)); SET COLLATION_CONNECTION=utf32_general_ci, CHARACTER_SET_CLIENT=binary; ALTER TABLE t1 CHANGE a a ENUM('a','a') CHARACTER SET utf32; ERROR HY000: Column 'a' has duplicated value 'a' in ENUM ALTER TABLE t1 CHANGE a a ENUM('aaa') CHARACTER SET utf32; ERROR HY000: Invalid utf32 character string: '\x00aaa' ALTER TABLE t1 CHANGE a a ENUM('aa') CHARACTER SET utf32; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` enum('慡') CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci ALTER TABLE t1 CHANGE a a ENUM('a','b') CHARACTER SET utf32; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` enum('a','b') CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; SET NAMES utf8; # # MDEV-28078 Garbage on multiple equal ENUMs with tricky character sets # CREATE TABLE t1 ( c1 ENUM ('a','b') CHARACTER SET utf32 DEFAULT 'a', c2 ENUM ('a','b') CHARACTER SET utf32 DEFAULT 'a' ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` enum('a','b') CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT 'a', `c2` enum('a','b') CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT 'a' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; CREATE TABLE t1 ( c1 ENUM ('00000061','00000062') DEFAULT '00000061' COLLATE latin1_bin, c2 ENUM ('a','b') DEFAULT 'a' COLLATE utf32_general_ci ); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `c1` enum('00000061','00000062') CHARACTER SET latin1 COLLATE latin1_bin DEFAULT '00000061', `c2` enum('a','b') CHARACTER SET utf32 COLLATE utf32_general_ci DEFAULT 'a' ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci DROP TABLE t1; # # End of 10.2 tests #