diff options
Diffstat (limited to 'mysql-test/main/ctype_utf8mb4.result')
| -rw-r--r-- | mysql-test/main/ctype_utf8mb4.result | 4092 |
1 files changed, 4092 insertions, 0 deletions
diff --git a/mysql-test/main/ctype_utf8mb4.result b/mysql-test/main/ctype_utf8mb4.result new file mode 100644 index 00000000000..fbe95d9f44b --- /dev/null +++ b/mysql-test/main/ctype_utf8mb4.result @@ -0,0 +1,4092 @@ +drop table if exists t1,t2; +# +# Start of 5.5 tests +# +set names utf8mb4; +select left(_utf8mb4 0xD0B0D0B1D0B2,1); +left(_utf8mb4 0xD0B0D0B1D0B2,1) +Π° +select right(_utf8mb4 0xD0B0D0B2D0B2,1); +right(_utf8mb4 0xD0B0D0B2D0B2,1) +Π² +select locate('he','hello'); +locate('he','hello') +1 +select locate('he','hello',2); +locate('he','hello',2) +0 +select locate('lo','hello',2); +locate('lo','hello',2) +4 +select locate('HE','hello'); +locate('HE','hello') +1 +select locate('HE','hello',2); +locate('HE','hello',2) +0 +select locate('LO','hello',2); +locate('LO','hello',2) +4 +select locate('HE','hello' collate utf8mb4_bin); +locate('HE','hello' collate utf8mb4_bin) +0 +select locate('HE','hello' collate utf8mb4_bin,2); +locate('HE','hello' collate utf8mb4_bin,2) +0 +select locate('LO','hello' collate utf8mb4_bin,2); +locate('LO','hello' collate utf8mb4_bin,2) +0 +select locate(_utf8mb4 0xD0B1, _utf8mb4 0xD0B0D0B1D0B2); +locate(_utf8mb4 0xD0B1, _utf8mb4 0xD0B0D0B1D0B2) +2 +select locate(_utf8mb4 0xD091, _utf8mb4 0xD0B0D0B1D0B2); +locate(_utf8mb4 0xD091, _utf8mb4 0xD0B0D0B1D0B2) +2 +select locate(_utf8mb4 0xD0B1, _utf8mb4 0xD0B0D091D0B2); +locate(_utf8mb4 0xD0B1, _utf8mb4 0xD0B0D091D0B2) +2 +select locate(_utf8mb4 0xD091, _utf8mb4 0xD0B0D0B1D0B2 collate utf8mb4_bin); +locate(_utf8mb4 0xD091, _utf8mb4 0xD0B0D0B1D0B2 collate utf8mb4_bin) +0 +select locate(_utf8mb4 0xD0B1, _utf8mb4 0xD0B0D091D0B2 collate utf8mb4_bin); +locate(_utf8mb4 0xD0B1, _utf8mb4 0xD0B0D091D0B2 collate utf8mb4_bin) +0 +select length(_utf8mb4 0xD0B1), bit_length(_utf8mb4 0xD0B1), char_length(_utf8mb4 0xD0B1); +length(_utf8mb4 0xD0B1) bit_length(_utf8mb4 0xD0B1) char_length(_utf8mb4 0xD0B1) +2 16 1 +select 'a' like 'a'; +'a' like 'a' +1 +select 'A' like 'a'; +'A' like 'a' +1 +select 'A' like 'a' collate utf8mb4_bin; +'A' like 'a' collate utf8mb4_bin +0 +select _utf8mb4 0xD0B0D0B1D0B2 like concat(_utf8mb4'%',_utf8mb4 0xD0B1,_utf8mb4 '%'); +_utf8mb4 0xD0B0D0B1D0B2 like concat(_utf8mb4'%',_utf8mb4 0xD0B1,_utf8mb4 '%') +1 +select convert(_latin1'Gόnter Andrι' using utf8mb4) like CONVERT(_latin1'GάNTER%' USING utf8mb4); +convert(_latin1'G?nter Andr?' using utf8mb4) like CONVERT(_latin1'G?NTER%' USING utf8mb4) +1 +select CONVERT(_koi8r'ΧΑΣΡ' USING utf8mb4) LIKE CONVERT(_koi8r'χασρ' USING utf8mb4); +CONVERT(_koi8r'????' USING utf8mb4) LIKE CONVERT(_koi8r'????' USING utf8mb4) +1 +select CONVERT(_koi8r'χασρ' USING utf8mb4) LIKE CONVERT(_koi8r'ΧΑΣΡ' USING utf8mb4); +CONVERT(_koi8r'????' USING utf8mb4) LIKE CONVERT(_koi8r'????' USING utf8mb4) +1 +SELECT 'a' = 'a '; +'a' = 'a ' +1 +SELECT 'a\0' < 'a'; +'a\0' < 'a' +1 +SELECT 'a\0' < 'a '; +'a\0' < 'a ' +1 +SELECT 'a\t' < 'a'; +'a\t' < 'a' +1 +SELECT 'a\t' < 'a '; +'a\t' < 'a ' +1 +SELECT 'a' = 'a ' collate utf8mb4_bin; +'a' = 'a ' collate utf8mb4_bin +1 +SELECT 'a\0' < 'a' collate utf8mb4_bin; +'a\0' < 'a' collate utf8mb4_bin +1 +SELECT 'a\0' < 'a ' collate utf8mb4_bin; +'a\0' < 'a ' collate utf8mb4_bin +1 +SELECT 'a\t' < 'a' collate utf8mb4_bin; +'a\t' < 'a' collate utf8mb4_bin +1 +SELECT 'a\t' < 'a ' collate utf8mb4_bin; +'a\t' < 'a ' collate utf8mb4_bin +1 +CREATE TABLE t1 (a char(10) character set utf8mb4 not null); +INSERT INTO t1 VALUES ('a'),('a\0'),('a\t'),('a '); +SELECT hex(a),STRCMP(a,'a'), STRCMP(a,'a ') FROM t1; +hex(a) STRCMP(a,'a') STRCMP(a,'a ') +61 0 0 +6100 -1 -1 +6109 -1 -1 +61 0 0 +DROP TABLE t1; +select insert('txs',2,1,'hi'),insert('is ',4,0,'a'),insert('txxxxt',2,4,'es'); +insert('txs',2,1,'hi') insert('is ',4,0,'a') insert('txxxxt',2,4,'es') +this is test +select insert("aa",100,1,"b"),insert("aa",1,3,"b"); +insert("aa",100,1,"b") insert("aa",1,3,"b") +aa b +select char_length(left(@a:='ΡΠ΅ΡΡ',5)), length(@a), @a; +char_length(left(@a:='ΡΠ΅ΡΡ',5)) length(@a) @a +4 8 ΡΠ΅ΡΡ +create table t1 select date_format("2004-01-19 10:10:10", "%Y-%m-%d"); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `date_format("2004-01-19 10:10:10", "%Y-%m-%d")` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t1; +date_format("2004-01-19 10:10:10", "%Y-%m-%d") +2004-01-19 +drop table t1; +set names utf8mb4; +set LC_TIME_NAMES='fr_FR'; +create table t1 (s1 char(20) character set latin1); +insert into t1 values (date_format('2004-02-02','%M')); +select hex(s1) from t1; +hex(s1) +66E97672696572 +drop table t1; +create table t1 (s1 char(20) character set koi8r); +set LC_TIME_NAMES='ru_RU'; +insert into t1 values (date_format('2004-02-02','%M')); +insert into t1 values (date_format('2004-02-02','%b')); +insert into t1 values (date_format('2004-02-02','%W')); +insert into t1 values (date_format('2004-02-02','%a')); +select hex(s1), s1 from t1; +hex(s1) s1 +E6C5D7D2C1CCD1 Π€Π΅Π²ΡΠ°Π»Ρ +E6C5D7 Π€Π΅Π² +F0CFCEC5C4C5CCD8CEC9CB ΠΠΎΠ½Π΅Π΄Π΅Π»ΡΠ½ΠΈΠΊ +F0CEC4 ΠΠ½Π΄ +drop table t1; +set LC_TIME_NAMES='en_US'; +set names koi8r; +create table t1 (s1 char(1) character set utf8mb4); +insert ignore into t1 values (_koi8r'ΑΒ'); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +select s1,hex(s1),char_length(s1),octet_length(s1) from t1; +s1 hex(s1) char_length(s1) octet_length(s1) +Α D0B0 1 2 +drop table t1; +create table t1 (s1 tinytext character set utf8mb4); +insert ignore into t1 select repeat('a',300); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +insert ignore into t1 select repeat('Ρ',300); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +insert ignore into t1 select repeat('aΡ',300); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +insert ignore into t1 select repeat('Ρa',300); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +insert ignore into t1 select repeat('ΡΡ',300); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +select hex(s1) from t1; +hex(s1) +616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161616161 +D18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18F +61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F +D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61D18F61 +D18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18FD18F +select length(s1),char_length(s1) from t1; +length(s1) char_length(s1) +255 255 +254 127 +255 170 +255 170 +254 127 +drop table t1; +create table t1 (s1 text character set utf8mb4); +insert ignore into t1 select repeat('a',66000); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +insert ignore into t1 select repeat('Ρ',66000); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +insert ignore into t1 select repeat('aΡ',66000); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +insert ignore into t1 select repeat('Ρa',66000); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +insert ignore into t1 select repeat('ΡΡ',66000); +Warnings: +Warning 1265 Data truncated for column 's1' at row 1 +select length(s1),char_length(s1) from t1; +length(s1) char_length(s1) +65535 65535 +65534 32767 +65535 43690 +65535 43690 +65534 32767 +drop table t1; +create table t1 (s1 char(10) character set utf8mb4); +insert ignore into t1 values (0x41FF); +Warnings: +Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1 +select hex(s1) from t1; +hex(s1) +413F +drop table t1; +create table t1 (s1 varchar(10) character set utf8mb4); +insert ignore into t1 values (0x41FF); +Warnings: +Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1 +select hex(s1) from t1; +hex(s1) +413F +drop table t1; +create table t1 (s1 text character set utf8mb4); +insert ignore into t1 values (0x41FF); +Warnings: +Warning 1366 Incorrect string value: '\xFF' for column 's1' at row 1 +select hex(s1) from t1; +hex(s1) +413F +drop table t1; +create table t1 (a text character set utf8mb4, primary key(a(371))); +ERROR 42000: Specified key was too long; max key length is 1000 bytes +CREATE TABLE t1 ( a varchar(10) ) CHARACTER SET utf8mb4; +INSERT INTO t1 VALUES ( 'test' ); +SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a; +a a +test test +SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = 'test' and b.a = 'test'; +a a +test test +SELECT a.a, b.a FROM t1 a, t1 b WHERE a.a = b.a and a.a = 'test'; +a a +test test +DROP TABLE t1; +create table t1 (a char(255) character set utf8mb4); +insert into t1 values('b'),('b'); +select * from t1 where a = 'b'; +a +b +b +select * from t1 where a = 'b' and a = 'b'; +a +b +b +select * from t1 where a = 'b' and a != 'b'; +a +drop table t1; +set names utf8mb4; +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 utf8mb4 DEFAULT NULL, + `s2` varchar(64) CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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; +SELECT @@character_set_client, @@collation_connection; +@@character_set_client @@collation_connection +utf8mb4 utf8mb4_general_ci +select 'Π²Π°ΡΡ' rlike '\\bΠ²Π°ΡΡ\\b'; +'Π²Π°ΡΡ' rlike '\\bΠ²Π°ΡΡ\\b' +1 +select 'Π²Π°ΡΡ ' rlike '\\bΠ²Π°ΡΡ\\b'; +'Π²Π°ΡΡ ' rlike '\\bΠ²Π°ΡΡ\\b' +1 +select ' Π²Π°ΡΡ' rlike '\\bΠ²Π°ΡΡ\\b'; +' Π²Π°ΡΡ' rlike '\\bΠ²Π°ΡΡ\\b' +1 +select ' Π²Π°ΡΡ ' rlike '\\bΠ²Π°ΡΡ\\b'; +' Π²Π°ΡΡ ' rlike '\\bΠ²Π°ΡΡ\\b' +1 +select 'Π²Π°ΡΡ' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]'; +'Π²Π°ΡΡ' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]' +1 +select 'Π²Π°ΡΡ ' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]'; +'Π²Π°ΡΡ ' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]' +1 +select ' Π²Π°ΡΡ' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]'; +' Π²Π°ΡΡ' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]' +1 +select ' Π²Π°ΡΡ ' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]'; +' Π²Π°ΡΡ ' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]' +1 +select 'Π²Π°ΡΡz' rlike '\\bΠ²Π°ΡΡ\\b'; +'Π²Π°ΡΡz' rlike '\\bΠ²Π°ΡΡ\\b' +0 +select 'zΠ²Π°ΡΡ' rlike '\\bΠ²Π°ΡΡ\\b'; +'zΠ²Π°ΡΡ' rlike '\\bΠ²Π°ΡΡ\\b' +0 +select 'zΠ²Π°ΡΡz' rlike '\\bΠ²Π°ΡΡ\\b'; +'zΠ²Π°ΡΡz' rlike '\\bΠ²Π°ΡΡ\\b' +0 +select 'Π²Π°ΡΡz' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]'; +'Π²Π°ΡΡz' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]' +0 +select 'zΠ²Π°ΡΡ' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]'; +'zΠ²Π°ΡΡ' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]' +0 +select 'zΠ²Π°ΡΡz' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]'; +'zΠ²Π°ΡΡz' rlike '[[:<:]]Π²Π°ΡΡ[[:>:]]' +0 +CREATE TABLE t1 (a enum ('Y', 'N') DEFAULT 'N' COLLATE utf8mb4_unicode_ci); +ALTER TABLE t1 ADD COLUMN b CHAR(20); +DROP TABLE t1; +set names utf8mb4; +create table t1 (a enum('aaaa','ΠΏΡΠΎΠ±Π°') character set utf8mb4); +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` enum('aaaa','ΠΏΡΠΎΠ±Π°') CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +insert into t1 values ('ΠΏΡΠΎΠ±Π°'); +select * from t1; +a +ΠΏΡΠΎΠ±Π° +create table t2 select ifnull(a,a) from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `ifnull(a,a)` varchar(5) CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +select * from t2; +ifnull(a,a) +ΠΏΡΠΎΠ±Π° +drop table t1; +drop table t2; +create table t1 (c varchar(30) character set utf8mb4, unique(c(10))); +insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); +insert into t1 values ('aaaaaaaaaa'); +insert into t1 values ('aaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c' +insert into t1 values ('aaaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c' +insert into t1 values (repeat('b',20)); +select c c1 from t1 where c='1'; +c1 +1 +select c c2 from t1 where c='2'; +c2 +2 +select c c3 from t1 where c='3'; +c3 +3 +select c cx from t1 where c='x'; +cx +x +select c cy from t1 where c='y'; +cy +y +select c cz from t1 where c='z'; +cz +z +select c ca10 from t1 where c='aaaaaaaaaa'; +ca10 +aaaaaaaaaa +select c cb20 from t1 where c=repeat('b',20); +cb20 +bbbbbbbbbbbbbbbbbbbb +drop table t1; +create table t1 (c varchar(30) character set utf8mb4, unique(c(10))) engine=innodb; +insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); +insert into t1 values ('aaaaaaaaaa'); +insert into t1 values ('aaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c' +insert into t1 values ('aaaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c' +insert into t1 values (repeat('b',20)); +select c c1 from t1 where c='1'; +c1 +1 +select c c2 from t1 where c='2'; +c2 +2 +select c c3 from t1 where c='3'; +c3 +3 +select c cx from t1 where c='x'; +cx +x +select c cy from t1 where c='y'; +cy +y +select c cz from t1 where c='z'; +cz +z +select c ca10 from t1 where c='aaaaaaaaaa'; +ca10 +aaaaaaaaaa +select c cb20 from t1 where c=repeat('b',20); +cb20 +bbbbbbbbbbbbbbbbbbbb +drop table t1; +create table t1 (c char(3) character set utf8mb4, unique (c(2))); +insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); +insert into t1 values ('a'); +insert into t1 values ('aa'); +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aa' for key 'c' +insert into t1 values ('b'); +insert into t1 values ('bb'); +insert into t1 values ('bbb'); +ERROR 23000: Duplicate entry 'bb' for key 'c' +insert into t1 values ('Π°'); +insert into t1 values ('Π°Π°'); +insert into t1 values ('Π°Π°Π°'); +ERROR 23000: Duplicate entry 'Π°Π°' for key 'c' +insert into t1 values ('Π±'); +insert into t1 values ('Π±Π±'); +insert into t1 values ('Π±Π±Π±'); +ERROR 23000: Duplicate entry 'Π±Π±' for key 'c' +insert into t1 values ('κͺͺ'); +insert into t1 values ('κͺͺκͺͺ'); +insert into t1 values ('κͺͺκͺͺκͺͺ'); +ERROR 23000: Duplicate entry 'κͺͺκͺͺ' for key 'c' +drop table t1; +create table t1 (c char(3) character set utf8mb4, unique (c(2))) engine=innodb; +insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); +insert into t1 values ('a'); +insert into t1 values ('aa'); +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aa' for key 'c' +insert into t1 values ('b'); +insert into t1 values ('bb'); +insert into t1 values ('bbb'); +ERROR 23000: Duplicate entry 'bb' for key 'c' +insert into t1 values ('Π°'); +insert into t1 values ('Π°Π°'); +insert into t1 values ('Π°Π°Π°'); +ERROR 23000: Duplicate entry 'Π°Π°' for key 'c' +insert into t1 values ('Π±'); +insert into t1 values ('Π±Π±'); +insert into t1 values ('Π±Π±Π±'); +ERROR 23000: Duplicate entry 'Π±Π±' for key 'c' +insert into t1 values ('κͺͺ'); +insert into t1 values ('κͺͺκͺͺ'); +insert into t1 values ('κͺͺκͺͺκͺͺ'); +ERROR 23000: Duplicate entry 'κͺͺκͺͺ' for key 'c' +drop table t1; +create table t1 ( +c char(10) character set utf8mb4, +unique key a using hash (c(1)) +) engine=heap; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, + UNIQUE KEY `a` (`c`(1)) USING HASH +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('Π±'); +insert into t1 values ('Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +insert into t1 values ('Π±Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +Π± +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='Π±'; +c_a +Π± +drop table t1; +create table t1 ( +c char(10) character set utf8mb4, +unique key a using btree (c(1)) +) engine=heap; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) CHARACTER SET utf8mb4 DEFAULT NULL, + UNIQUE KEY `a` (`c`(1)) USING BTREE +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('Π±'); +insert into t1 values ('Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +insert into t1 values ('Π±Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +Π± +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='Π±'; +c_a +Π± +drop table t1; +create table t1 ( +c char(10) character set utf8mb4, +unique key a (c(1)) +) engine=innodb; +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('Π±'); +insert into t1 values ('Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +insert into t1 values ('Π±Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +Π± +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='Π±'; +c_a +Π± +drop table t1; +create table t1 (c varchar(30) character set utf8mb4 collate utf8mb4_bin, unique(c(10))); +insert into t1 values ('1'),('2'),('3'),('x'),('y'),('z'); +insert into t1 values ('aaaaaaaaaa'); +insert into t1 values ('aaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c' +insert into t1 values ('aaaaaaaaaaaa'); +ERROR 23000: Duplicate entry 'aaaaaaaaaa' for key 'c' +insert into t1 values (repeat('b',20)); +select c c1 from t1 where c='1'; +c1 +1 +select c c2 from t1 where c='2'; +c2 +2 +select c c3 from t1 where c='3'; +c3 +3 +select c cx from t1 where c='x'; +cx +x +select c cy from t1 where c='y'; +cy +y +select c cz from t1 where c='z'; +cz +z +select c ca10 from t1 where c='aaaaaaaaaa'; +ca10 +aaaaaaaaaa +select c cb20 from t1 where c=repeat('b',20); +cb20 +bbbbbbbbbbbbbbbbbbbb +drop table t1; +create table t1 (c char(3) character set utf8mb4 collate utf8mb4_bin, unique (c(2))); +insert into t1 values ('1'),('2'),('3'),('4'),('x'),('y'),('z'); +insert into t1 values ('a'); +insert into t1 values ('aa'); +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'aa' for key 'c' +insert into t1 values ('b'); +insert into t1 values ('bb'); +insert into t1 values ('bbb'); +ERROR 23000: Duplicate entry 'bb' for key 'c' +insert into t1 values ('Π°'); +insert into t1 values ('Π°Π°'); +insert into t1 values ('Π°Π°Π°'); +ERROR 23000: Duplicate entry 'Π°Π°' for key 'c' +insert into t1 values ('Π±'); +insert into t1 values ('Π±Π±'); +insert into t1 values ('Π±Π±Π±'); +ERROR 23000: Duplicate entry 'Π±Π±' for key 'c' +insert into t1 values ('κͺͺ'); +insert into t1 values ('κͺͺκͺͺ'); +insert into t1 values ('κͺͺκͺͺκͺͺ'); +ERROR 23000: Duplicate entry 'κͺͺκͺͺ' for key 'c' +drop table t1; +create table t1 ( +c char(10) character set utf8mb4 collate utf8mb4_bin, +unique key a using hash (c(1)) +) engine=heap; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, + UNIQUE KEY `a` (`c`(1)) USING HASH +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('Π±'); +insert into t1 values ('Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +insert into t1 values ('Π±Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +Π± +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='Π±'; +c_a +Π± +drop table t1; +create table t1 ( +c char(10) character set utf8mb4 collate utf8mb4_bin, +unique key a using btree (c(1)) +) engine=heap; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `c` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL, + UNIQUE KEY `a` (`c`(1)) USING BTREE +) ENGINE=MEMORY DEFAULT CHARSET=latin1 +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('Π±'); +insert into t1 values ('Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +insert into t1 values ('Π±Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +Π± +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='Π±'; +c_a +Π± +drop table t1; +create table t1 ( +c char(10) character set utf8mb4 collate utf8mb4_bin, +unique key a (c(1)) +) engine=innodb; +insert into t1 values ('a'),('b'),('c'),('d'),('e'),('f'); +insert into t1 values ('aa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('aaa'); +ERROR 23000: Duplicate entry 'a' for key 'a' +insert into t1 values ('Π±'); +insert into t1 values ('Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +insert into t1 values ('Π±Π±Π±'); +ERROR 23000: Duplicate entry 'Π±' for key 'a' +select c as c_all from t1 order by c; +c_all +a +b +c +d +e +f +Π± +select c as c_a from t1 where c='a'; +c_a +a +select c as c_a from t1 where c='Π±'; +c_a +Π± +drop table t1; +create table t1 ( +str varchar(255) character set utf8mb4 not null, +key str (str(2)) +) engine=myisam; +INSERT INTO t1 VALUES ('str'); +INSERT INTO t1 VALUES ('str2'); +select * from t1 where str='str'; +str +str +drop table t1; +create table t1 ( +str varchar(255) character set utf8mb4 not null, +key str (str(2)) +) engine=innodb; +INSERT INTO t1 VALUES ('str'); +INSERT INTO t1 VALUES ('str2'); +select * from t1 where str='str'; +str +str +drop table t1; +create table t1 ( +str varchar(255) character set utf8mb4 not null, +key str using btree (str(2)) +) engine=heap; +INSERT INTO t1 VALUES ('str'); +INSERT INTO t1 VALUES ('str2'); +select * from t1 where str='str'; +str +str +drop table t1; +create table t1 ( +str varchar(255) character set utf8mb4 not null, +key str using hash (str(2)) +) engine=heap; +INSERT INTO t1 VALUES ('str'); +INSERT INTO t1 VALUES ('str2'); +select * from t1 where str='str'; +str +str +drop table t1; +create table t1 ( +str varchar(255) character set utf8mb4 not null, +key str (str(2)) +) engine=innodb; +INSERT INTO t1 VALUES ('str'); +INSERT INTO t1 VALUES ('str2'); +select * from t1 where str='str'; +str +str +drop table t1; +CREATE TABLE t1 (a varchar(32) BINARY) CHARACTER SET utf8mb4; +INSERT INTO t1 VALUES ('test'); +SELECT a FROM t1 WHERE a LIKE '%te'; +a +DROP TABLE t1; +SET NAMES utf8mb4; +CREATE TABLE t1 ( +subject varchar(255) character set utf8mb4 collate utf8mb4_unicode_ci, +p varchar(15) character set utf8mb4 +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES ('θ°·ε·δΏδΊγ¨η³γγΎγγγγ€γ³γΏγΌγγγδΊη΄γδΌε‘η»ι²γγγΎγγγ¨γγγγ‘γΌγ«γ’γγ¬γΉγιιγγ¦γγΎγδΌε‘οΌ©οΌ€γεγεγγγ¨γεΊζ₯γΎγγγ§γγγιιγγ’γγ¬γΉγ―tani-shun@n.vodafone.ne.jpγζΈγθΎΌγΏγΎγγγγ©γγγγ°γγγ§γγοΌ γγδ»γδ½ζηγ―ιιγγγγΎγγγι£η΅‘γγ γγγγγγγγι‘γγγΎγγm(__)m','040312-000057'); +INSERT INTO t1 VALUES ('aaa','bbb'); +SELECT length(subject) FROM t1; +length(subject) +432 +3 +SELECT length(subject) FROM t1 ORDER BY 1; +length(subject) +3 +432 +DROP TABLE t1; +CREATE TABLE t1 ( +id int unsigned NOT NULL auto_increment, +list_id smallint unsigned NOT NULL, +term TEXT NOT NULL, +PRIMARY KEY(id), +INDEX(list_id, term(4)) +) ENGINE=MYISAM CHARSET=utf8mb4; +INSERT INTO t1 SET list_id = 1, term = "letterc"; +INSERT INTO t1 SET list_id = 1, term = "letterb"; +INSERT INTO t1 SET list_id = 1, term = "lettera"; +INSERT INTO t1 SET list_id = 1, term = "letterd"; +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterc"); +id +1 +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterb"); +id +2 +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "lettera"); +id +3 +SELECT id FROM t1 WHERE (list_id = 1) AND (term = "letterd"); +id +4 +DROP TABLE t1; +SET NAMES latin1; +CREATE TABLE t1 ( +id int unsigned NOT NULL auto_increment, +list_id smallint unsigned NOT NULL, +term text NOT NULL, +PRIMARY KEY(id), +INDEX(list_id, term(19)) +) ENGINE=MyISAM CHARSET=utf8mb4; +INSERT INTO t1 set list_id = 1, term = "testιtest"; +INSERT INTO t1 set list_id = 1, term = "testetest"; +INSERT INTO t1 set list_id = 1, term = "testθtest"; +SELECT id, term FROM t1 where (list_id = 1) AND (term = "testιtest"); +id term +1 testιtest +2 testetest +3 testθtest +SELECT id, term FROM t1 where (list_id = 1) AND (term = "testetest"); +id term +1 testιtest +2 testetest +3 testθtest +SELECT id, term FROM t1 where (list_id = 1) AND (term = "testθtest"); +id term +1 testιtest +2 testetest +3 testθtest +DROP TABLE t1; +set names utf8mb4; +create table t1 ( +a int primary key, +b varchar(6), +index b3(b(3)) +) engine=innodb character set=utf8mb4; +insert into t1 values(1,'foo'),(2,'foobar'); +select * from t1 where b like 'foob%'; +a b +2 foobar +alter table t1 engine=innodb; +select * from t1 where b like 'foob%'; +a b +2 foobar +drop table t1; +create table t1 ( +a enum('ΠΏΠ΅ΡΡ','Π²Π°ΡΡ','Π°Π½ΡΡΠ°') character set utf8mb4 not null default 'Π°Π½ΡΡΠ°', +b set('ΠΏΠ΅ΡΡ','Π²Π°ΡΡ','Π°Π½ΡΡΠ°') character set utf8mb4 not null default 'Π°Π½ΡΡΠ°' +); +create table t2 select concat(a,_utf8mb4'') as a, concat(b,_utf8mb4'')as b from t1; +show create table t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `a` varchar(5) CHARACTER SET utf8mb4 DEFAULT NULL, + `b` varchar(15) CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +drop table t2; +drop table t1; +select 'c' like '\_' as want0; +want0 +0 +SELECT SUBSTR('Π²Π°ΡΡ',-2); +SUBSTR('Π²Π°ΡΡ',-2) +ΡΡ +create table t1 (id integer, a varchar(100) character set utf8mb4 collate utf8mb4_unicode_ci); +insert into t1 values (1, 'Test'); +select * from t1 where soundex(a) = soundex('Test'); +id a +1 Test +select * from t1 where soundex(a) = soundex('TEST'); +id a +1 Test +select * from t1 where soundex(a) = soundex('test'); +id a +1 Test +drop table t1; +select soundex(_utf8mb4 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB); +soundex(_utf8mb4 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB) +ι
000 +select hex(soundex(_utf8mb4 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB)); +hex(soundex(_utf8mb4 0xE99885E8A788E99A8FE697B6E69BB4E696B0E79A84E696B0E997BB)) +E99885303030 +select soundex(_utf8mb4 0xD091D092D093); +soundex(_utf8mb4 0xD091D092D093) +Π000 +select hex(soundex(_utf8mb4 0xD091D092D093)); +hex(soundex(_utf8mb4 0xD091D092D093)) +D091303030 +SET collation_connection='utf8mb4_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) +utf8mb4_general_ci 6109 +utf8mb4_general_ci 61 +utf8mb4_general_ci 6120 +drop table t1; +# +# MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP +# +SELECT @@collation_connection; +@@collation_connection +utf8mb4_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 +utf8mb4_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 +utf8mb4_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; +"BEGIN ctype_german.inc" +drop table if exists t1; +create table t1 as select repeat(' ', 64) as s1; +select collation(s1) from t1; +collation(s1) +utf8mb4_general_ci +delete from t1; +INSERT INTO t1 VALUES ('ud'),('uf'); +INSERT INTO t1 VALUES ('od'),('of'); +INSERT INTO t1 VALUES ('e'); +INSERT INTO t1 VALUES ('ad'),('af'); +insert into t1 values ('a'),('ae'),(_latin1 0xE4); +insert into t1 values ('o'),('oe'),(_latin1 0xF6); +insert into t1 values ('s'),('ss'),(_latin1 0xDF); +insert into t1 values ('u'),('ue'),(_latin1 0xFC); +INSERT INTO t1 VALUES (_latin1 0xE6), (_latin1 0xC6); +INSERT INTO t1 VALUES (_latin1 0x9C), (_latin1 0x8C); +select s1, hex(s1) from t1 order by s1, binary s1; +s1 hex(s1) +a 61 +Γ€ C3A4 +ad 6164 +ae 6165 +af 6166 +e 65 +o 6F +ΓΆ C3B6 +od 6F64 +oe 6F65 +of 6F66 +s 73 +Γ C39F +ss 7373 +u 75 +ΓΌ C3BC +ud 7564 +ue 7565 +uf 7566 +Γ C386 +Γ¦ C3A6 +Ε C592 +Ε C593 +select group_concat(s1 order by binary s1) from t1 group by s1; +group_concat(s1 order by binary s1) +a,Γ€ +ad +ae +af +e +o,ΓΆ +od +oe +of +s,Γ +ss +u,ΓΌ +ud +ue +uf +Γ,Γ¦ +Ε,Ε +SELECT s1, hex(s1), hex(weight_string(s1)) FROM t1 ORDER BY s1, BINARY(s1); +s1 hex(s1) hex(weight_string(s1)) +a 61 0041 +Γ€ C3A4 0041 +ad 6164 00410044 +ae 6165 00410045 +af 6166 00410046 +e 65 0045 +o 6F 004F +ΓΆ C3B6 004F +od 6F64 004F0044 +oe 6F65 004F0045 +of 6F66 004F0046 +s 73 0053 +Γ C39F 0053 +ss 7373 00530053 +u 75 0055 +ΓΌ C3BC 0055 +ud 7564 00550044 +ue 7565 00550045 +uf 7566 00550046 +Γ C386 00C6 +Γ¦ C3A6 00C6 +Ε C592 0152 +Ε C593 0152 +SELECT s1, hex(s1) FROM t1 WHERE s1='ae' ORDER BY s1, BINARY(s1); +s1 hex(s1) +ae 6165 +drop table t1; +CREATE TABLE t1 AS SELECT REPEAT('a',1) AS a, 1 AS b LIMIT 0; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(1) CHARACTER SET utf8mb4 DEFAULT NULL, + `b` int(1) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +INSERT INTO t1 VALUES ('s',0),(_latin1 0xDF,1); +SELECT * FROM t1 ORDER BY a, b; +a b +s 0 +Γ 1 +SELECT * FROM t1 ORDER BY a DESC, b; +a b +s 0 +Γ 1 +SELECT * FROM t1 ORDER BY CONCAT(a), b; +a b +s 0 +Γ 1 +SELECT * FROM t1 ORDER BY CONCAT(a) DESC, b; +a b +s 0 +Γ 1 +DROP TABLE t1; +"END ctype_german.inc" +SET collation_connection='utf8mb4_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) +utf8mb4_bin 6109 +utf8mb4_bin 61 +utf8mb4_bin 6120 +drop table t1; +# +# MDEV-5453 Assertion `src' fails in my_strnxfrm_unicode on GROUP BY MID(..) WITH ROLLUP +# +SELECT @@collation_connection; +@@collation_connection +utf8mb4_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 +utf8mb4_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 utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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)) +CE85 CE85 +EFBE9D EFBE9D +F0908E84 F0908E84 +F4808080 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)) +CE85 CE85 +EFBE9D EFBE9D +F0908E84 F0908E84 +F4808080 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 +utf8mb4_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; +CREATE TABLE t1 ( +user varchar(255) NOT NULL default '' +) ENGINE=MyISAM DEFAULT CHARSET=latin1; +INSERT INTO t1 VALUES ('one'),('two'); +SELECT CHARSET('a'); +CHARSET('a') +utf8mb4 +SELECT user, CONCAT('<', user, '>') AS c FROM t1; +user c +one <one> +two <two> +DROP TABLE t1; +create table t1 (f1 varchar(1) not null) default charset utf8mb4; +insert into t1 values (''), (''); +select concat(concat(_latin1'->',f1),_latin1'<-') from t1; +concat(concat(_latin1'->',f1),_latin1'<-') +-><- +-><- +drop table t1; +select convert(_koi8r'Ι' using utf8mb4) < convert(_koi8r'Κ' using utf8mb4); +convert(_koi8r'?' using utf8mb4) < convert(_koi8r'?' using utf8mb4) +1 +set names latin1; +create table t1 (a varchar(10)) character set utf8mb4; +insert into t1 values ('test'); +select ifnull(a,'') from t1; +ifnull(a,'') +test +drop table t1; +select repeat(_utf8mb4'+',3) as h union select NULL; +h ++++ +NULL +select ifnull(NULL, _utf8mb4'string'); +ifnull(NULL, _utf8mb4'string') +string +set names utf8mb4; +create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_lithuanian_ci); +insert into t1 values ('I'),('K'),('Y'); +select * from t1 where s1 < 'K' and s1 = 'Y'; +s1 +I +Y +select * from t1 where 'K' > s1 and s1 = 'Y'; +s1 +I +Y +drop table t1; +create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_czech_ci); +insert into t1 values ('c'),('d'),('h'),('ch'),('CH'),('cH'),('Ch'),('i'); +select * from t1 where s1 > 'd' and s1 = 'CH'; +s1 +ch +CH +Ch +select * from t1 where 'd' < s1 and s1 = 'CH'; +s1 +ch +CH +Ch +select * from t1 where s1 = 'cH' and s1 <> 'ch'; +s1 +cH +select * from t1 where 'cH' = s1 and s1 <> 'ch'; +s1 +cH +drop table t1; +create table t1 (a varchar(255)) default character set utf8mb4; +insert into t1 values (1.0); +drop table t1; +create table t1 ( +id int not null, +city varchar(20) not null, +key (city(7),id) +) character set=utf8mb4; +insert into t1 values (1,'Durban North'); +insert into t1 values (2,'Durban'); +select * from t1 where city = 'Durban'; +id city +2 Durban +select * from t1 where city = 'Durban '; +id city +2 Durban +drop table t1; +create table t1 (x set('A', 'B') default 0) character set utf8mb4; +ERROR 42000: Invalid default value for 'x' +create table t1 (x enum('A', 'B') default 0) character set utf8mb4; +ERROR 42000: Invalid default value for 'x' +SET NAMES UTF8; +CREATE TABLE t1 ( +`id` int(20) NOT NULL auto_increment, +`country` varchar(100) NOT NULL default '', +`shortcode` varchar(100) NOT NULL default '', +`operator` varchar(100) NOT NULL default '', +`momid` varchar(30) NOT NULL default '', +`keyword` varchar(160) NOT NULL default '', +`content` varchar(160) NOT NULL default '', +`second_token` varchar(160) default NULL, +`gateway_id` int(11) NOT NULL default '0', +`created` datetime NOT NULL default '0000-00-00 00:00:00', +`msisdn` varchar(15) NOT NULL default '', +PRIMARY KEY (`id`), +UNIQUE KEY `MSCCSPK_20030521130957121` (`momid`), +KEY `IX_mobile_originated_message_keyword` (`keyword`), +KEY `IX_mobile_originated_message_created` (`created`), +KEY `IX_mobile_originated_message_support` (`msisdn`,`momid`,`keyword`,`gateway_id`,`created`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; +INSERT INTO t1 VALUES +(1,'blah','464','aaa','fkc1c9ilc20x0hgae7lx6j09','ERR','ERR ΠΠΌΡΠΈ.ΠΡΠΈΠΌΠΈΠΌ.ΠΠ΅ΠΈΠΌΠΈΠΌΡΠΈΠΌΠ΄ΠΌΡΠΈΠΌΡΠΌΡΠΈΡΠΎΡ ΠΈΠΌΡΠΈΠΌΡΠΈΠΌΡΠΈΠΌΡ ΠΈΠΌΡΠΈΠ΄ΠΌ ΠΈΡΠ±Π΄Π½ΡΠΈΠΌΡΡΠΌΡΠΈΡΠΈΡΠΈΠΌΡΡΠΌΡΠΌΠΈΠΌ.ΠΠ΄.Π ΠΈΠΌΠ΄ΠΈΠΌΡΠΈΠΌΡΠ°Π΄.ΠΠ΄ΠΈΠΌΡΠΈΠΌΡΠΈΠΌΡΠΌΠ΄ΠΈΡΠΈΠΌΡΠΈΠΌΡΠΈΠΌΡ ΠΌ.ΠΠ°Π΄ΠΈΠΌΡΡΡΠΌΡΠΈΠΌΠ΄ ΠΈΠΌ.ΠΠ΄ΠΈΠΌΠΈΠΌΡΠ½ ΠΈΠΌΠ°Π΄ΠΌΠΈ','ΠΠΠ Π.ΠΠ€ΠΠΠΠ.ΠΠΠΠΠΠΠ ΠΠΠΠΠ ΠΠΠ ΠΠ ΠΠ ΠΠ ',3,'2005-06-01 17:30:43','1234567890'), +(2,'blah','464','aaa','haxpl2ilc20x00bj4tt2m5ti','11','11 g','G',3,'2005-06-02 22:43:10','1234567890'); +CREATE TABLE t2 ( +`msisdn` varchar(15) NOT NULL default '', +`operator_id` int(11) NOT NULL default '0', +`created` datetime NOT NULL default '0000-00-00 00:00:00', +UNIQUE KEY `PK_user` (`msisdn`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +INSERT INTO t2 VALUES ('1234567890',2,'2005-05-24 13:53:25'); +SELECT content, t2.msisdn FROM t1, t2 WHERE t1.msisdn = '1234567890'; +content msisdn +ERR ΠΠΌΡΠΈ.ΠΡΠΈΠΌΠΈΠΌ.ΠΠ΅ΠΈΠΌΠΈΠΌΡΠΈΠΌΠ΄ΠΌΡΠΈΠΌΡΠΌΡΠΈΡΠΎΡ ΠΈΠΌΡΠΈΠΌΡΠΈΠΌΡΠΈΠΌΡ ΠΈΠΌΡΠΈΠ΄ΠΌ ΠΈΡΠ±Π΄Π½ΡΠΈΠΌΡΡΠΌΡΠΈΡΠΈΡΠΈΠΌΡΡΠΌΡΠΌΠΈΠΌ.ΠΠ΄.Π ΠΈΠΌΠ΄ΠΈΠΌΡΠΈΠΌΡΠ°Π΄.ΠΠ΄ΠΈΠΌΡΠΈΠΌΡΠΈΠΌΡΠΌΠ΄ΠΈΡΠΈΠΌΡΠΈΠΌΡΠΈΠΌΡ ΠΌ.ΠΠ°Π΄ΠΈΠΌΡΡΡΠΌΡΠΈΠΌΠ΄ ΠΈΠΌ.ΠΠ΄ΠΈΠΌΠΈΠΌΡΠ½ ΠΈΠΌΠ°Π΄ΠΌΠΈ 1234567890 +11 g 1234567890 +DROP TABLE t1,t2; +create table t1 (a char(20) character set utf8mb4); +insert into t1 values ('123456'),('Π°Π½Π΄ΡΠ΅ΠΉ'); +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +alter table t1 modify a char(2) character set utf8mb4; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +Warning 1265 Data truncated for column 'a' at row 2 +select char_length(a), length(a), a from t1 order by a; +char_length(a) length(a) a +2 2 12 +2 4 Π°Π½ +drop table t1; +set names utf8mb4; +select 'andre%' like 'andreΓ±%' escape 'Γ±'; +'andre%' like 'andreΓ±%' escape 'Γ±' +1 +set names utf8mb4; +select 'a\\' like 'a\\'; +'a\\' like 'a\\' +1 +select 'aa\\' like 'a%\\'; +'aa\\' like 'a%\\' +1 +create table t1 (a char(10), key(a)) character set utf8mb4; +insert into t1 values ("a"),("abc"),("abcd"),("hello"),("test"); +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; +CREATE TABLE t1 ( +a varchar(255) NOT NULL default '', +KEY a (a) +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_general_ci; +Warnings: +Warning 1071 Specified key was too long; max key length is 1000 bytes +insert into t1 values (_utf8mb4 0xe880bd); +insert into t1 values (_utf8mb4 0x5b); +select hex(a) from t1; +hex(a) +E880BD +5B +drop table t1; +set names 'latin1'; +create table t1 (a varchar(255)) default charset=utf8mb4; +select * from t1 where find_in_set('-1', a); +a +drop table t1; +create table t1 (a int); +insert into t1 values (48),(49),(50); +set names utf8mb4; +select distinct char(a) from t1; +char(a) +0 +1 +2 +drop table t1; +CREATE TABLE t1 (t TINYTEXT CHARACTER SET utf8mb4); +INSERT INTO t1 VALUES(REPEAT('a', 100)); +CREATE TEMPORARY TABLE t2 SELECT COALESCE(t) AS bug FROM t1; +SELECT LENGTH(bug) FROM t2; +LENGTH(bug) +100 +DROP TABLE t2; +DROP TABLE t1; +CREATE TABLE t1 (item varchar(255)) default character set utf8mb4; +INSERT INTO t1 VALUES (N'\\'); +INSERT INTO t1 VALUES (_utf8mb4'\\'); +INSERT INTO t1 VALUES (N'Cote d\'Ivoire'); +INSERT INTO t1 VALUES (_utf8mb4'Cote d\'Ivoire'); +SELECT item FROM t1 ORDER BY item; +item +Cote d'Ivoire +Cote d'Ivoire +\ +\ +DROP TABLE t1; +SET NAMES utf8mb4; +DROP TABLE IF EXISTS t1; +Warnings: +Note 1051 Unknown table 'test.t1' +CREATE TABLE t1(a VARCHAR(255), KEY(a)) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; +Warnings: +Warning 1071 Specified key was too long; max key length is 1000 bytes +INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZΜbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'); +INSERT INTO t1 VALUES('uu'); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 VALUES('uU'); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 VALUES('uu'); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 VALUES('uuABC'); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 VALUES('UuABC'); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 VALUES('uuABC'); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +alter table t1 add b int; +INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZΜbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1); +INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZΜbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',2); +delete from t1 where b=1; +INSERT INTO t1 VALUES('UUABCDEFGHIGKLMNOPRSTUVWXYZΜbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',1); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZΜbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3); +INSERT INTO t1 VALUES('uuABCDEFGHIGKLMNOPRSTUVWXYZΜbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',4); +delete from t1 where b=3; +INSERT INTO t1 VALUES('uUABCDEFGHIGKLMNOPRSTUVWXYZΜbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb',3); +check table t1; +Table Op Msg_type Msg_text +test.t1 check status OK +drop table t1; +set names utf8mb4; +create table t1 (s1 char(5) character set utf8mb4); +insert into t1 values +('a'),('b'),(null),('γγγ«γ°γ«'),('ΓΌ'),('Y'); +create index it1 on t1 (s1); +select s1 as before_delete_general_ci from t1 where s1 like 'γγ%'; +before_delete_general_ci +γγγ«γ°γ« +delete from t1 where s1 = 'Y'; +select s1 as after_delete_general_ci from t1 where s1 like 'γγ%'; +after_delete_general_ci +γγγ«γ°γ« +drop table t1; +set names utf8mb4; +create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_unicode_ci); +insert into t1 values +('a'),('b'),(null),('γγγ«γ°γ«'),('ΓΌ'),('Y'); +create index it1 on t1 (s1); +select s1 as before_delete_unicode_ci from t1 where s1 like 'γγ%'; +before_delete_unicode_ci +γγγ«γ°γ« +delete from t1 where s1 = 'Y'; +select s1 as after_delete_unicode_ci from t1 where s1 like 'γγ%'; +after_delete_unicode_ci +γγγ«γ°γ« +drop table t1; +set names utf8mb4; +create table t1 (s1 char(5) character set utf8mb4 collate utf8mb4_bin); +insert into t1 values +('a'),('b'),(null),('γγγ«γ°γ«'),('ΓΌ'),('Y'); +create index it1 on t1 (s1); +select s1 as before_delete_bin from t1 where s1 like 'γγ%'; +before_delete_bin +γγγ«γ°γ« +delete from t1 where s1 = 'Y'; +select s1 as after_delete_bin from t1 where s1 like 'γγ%'; +after_delete_bin +γγγ«γ°γ« +drop table t1; +set names utf8mb4; +create table t1 (a varchar(30) not null primary key) +engine=innodb default character set utf8mb4 collate utf8mb4_general_ci; +insert into t1 values ('γγγγγγγγγγγγγγγ'); +insert into t1 values ('γγγγγγγγγγγγγγγ'); +select a as gci1 from t1 where a like 'γγγγγγγγγγγγγγγ%'; +gci1 +γγγγγγγγγγγγγγγ +select a as gci2 from t1 where a like 'γγγγγγγγγγγγγγγ'; +gci2 +γγγγγγγγγγγγγγγ +drop table t1; +set names utf8mb4; +create table t1 (a varchar(30) not null primary key) +engine=innodb default character set utf8mb4 collate utf8mb4_unicode_ci; +insert into t1 values ('γγγγγγγγγγγγγγγ'); +insert into t1 values ('γγγγγγγγγγγγγγγ'); +select a as uci1 from t1 where a like 'γγγγγγγγγγγγγγγ%'; +uci1 +γγγγγγγγγγγγγγγ +select a as uci2 from t1 where a like 'γγγγγγγγγγγγγγγ'; +uci2 +γγγγγγγγγγγγγγγ +drop table t1; +set names utf8mb4; +create table t1 (a varchar(30) not null primary key) +engine=innodb default character set utf8mb4 collate utf8mb4_bin; +insert into t1 values ('γγγγγγγγγγγγγγγ'); +insert into t1 values ('γγγγγγγγγγγγγγγ'); +select a as bin1 from t1 where a like 'γγγγγγγγγγγγγγγ%'; +bin1 +γγγγγγγγγγγγγγγ +select a as bin2 from t1 where a like 'γγγγγγγγγγγγγγγ'; +bin2 +γγγγγγγγγγγγγγγ +drop table t1; +SET NAMES utf8mb4; +CREATE TABLE t1 (id int PRIMARY KEY, +a varchar(16) collate utf8mb4_unicode_ci NOT NULL default '', +b int, +f varchar(128) default 'XXX', +INDEX (a(4)) +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; +INSERT INTO t1(id, a, b) VALUES +(1, 'cccc', 50), (2, 'cccc', 70), (3, 'cccc', 30), +(4, 'cccc', 30), (5, 'cccc', 20), (6, 'bbbbbb', 40), +(7, 'dddd', 30), (8, 'aaaa', 10), (9, 'aaaa', 50), +(10, 'eeeee', 40), (11, 'bbbbbb', 60); +SELECT id, a, b FROM t1; +id a b +1 cccc 50 +2 cccc 70 +3 cccc 30 +4 cccc 30 +5 cccc 20 +6 bbbbbb 40 +7 dddd 30 +8 aaaa 10 +9 aaaa 50 +10 eeeee 40 +11 bbbbbb 60 +SELECT id, a, b FROM t1 WHERE a BETWEEN 'aaaa' AND 'bbbbbb'; +id a b +8 aaaa 10 +9 aaaa 50 +6 bbbbbb 40 +11 bbbbbb 60 +SELECT id, a FROM t1 WHERE a='bbbbbb'; +id a +6 bbbbbb +11 bbbbbb +SELECT id, a FROM t1 WHERE a='bbbbbb' ORDER BY b; +id a +6 bbbbbb +11 bbbbbb +DROP TABLE t1; +SET NAMES utf8mb4; +CREATE TABLE t1 ( +a CHAR(13) DEFAULT '', +INDEX(a) +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +INSERT INTO t1 VALUES +('KΓ€li KΓ€li 2-4'), ('KΓ€li KΓ€li 2-4'), +('KΓ€li KΓ€li 2+4'), ('KΓ€li KΓ€li 2+4'), +('KΓ€li KΓ€li 2-6'), ('KΓ€li KΓ€li 2-6'); +INSERT INTO t1 SELECT * FROM t1; +CREATE TABLE t2 ( +a CHAR(13) DEFAULT '', +INDEX(a) +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; +INSERT INTO t2 VALUES +('Kali Kali 2-4'), ('Kali Kali 2-4'), +('Kali Kali 2+4'), ('Kali Kali 2+4'), +('Kali Kali 2-6'), ('Kali Kali 2-6'); +INSERT INTO t2 SELECT * FROM t2; +SELECT a FROM t1 WHERE a LIKE 'KΓ€li KΓ€li 2+4'; +a +KΓ€li KΓ€li 2+4 +KΓ€li KΓ€li 2+4 +KΓ€li KΓ€li 2+4 +KΓ€li KΓ€li 2+4 +SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4'; +a +Kali Kali 2+4 +Kali Kali 2+4 +Kali Kali 2+4 +Kali Kali 2+4 +EXPLAIN SELECT a FROM t1 WHERE a LIKE 'KΓ€li KΓ€li 2+4'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 range a a 53 NULL 4 Using where; Using index +EXPLAIN SELECT a FROM t1 WHERE a = 'KΓ€li KΓ€li 2+4'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ref a a 53 const 4 Using where; Using index +EXPLAIN SELECT a FROM t2 WHERE a LIKE 'Kali Kali 2+4'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 range a a 14 NULL 4 Using where; Using index +EXPLAIN SELECT a FROM t2 WHERE a = 'Kali Kali 2+4'; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t2 ref a a 14 const 4 Using where; Using index +DROP TABLE t1,t2; +CREATE TABLE t1 ( +a char(255) DEFAULT '', +KEY(a(10)) +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +INSERT INTO t1 VALUES ('KΓ€li KΓ€li 2-4'); +SELECT * FROM t1 WHERE a LIKE 'KΓ€li KΓ€li 2%'; +a +KΓ€li KΓ€li 2-4 +INSERT INTO t1 VALUES ('KΓ€li KΓ€li 2-4'); +SELECT * FROM t1 WHERE a LIKE 'KΓ€li KΓ€li 2%'; +a +KΓ€li KΓ€li 2-4 +KΓ€li KΓ€li 2-4 +DROP TABLE t1; +CREATE TABLE t1 ( +a char(255) DEFAULT '' +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; +INSERT INTO t1 VALUES ('KΓ€li KΓ€li 2-4'); +INSERT INTO t1 VALUES ('KΓ€li KΓ€li 2-4'); +SELECT * FROM t1 WHERE a LIKE 'KΓ€li KΓ€li 2%'; +a +KΓ€li KΓ€li 2-4 +KΓ€li KΓ€li 2-4 +ALTER TABLE t1 ADD KEY (a(10)); +SELECT * FROM t1 WHERE a LIKE 'KΓ€li KΓ€li 2%'; +a +KΓ€li KΓ€li 2-4 +KΓ€li KΓ€li 2-4 +DROP TABLE t1; +SET NAMES latin2; +CREATE TABLE t1 ( +id int(11) NOT NULL default '0', +tid int(11) NOT NULL default '0', +val text NOT NULL, +INDEX idx(tid, val(10)) +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4; +INSERT INTO t1 VALUES +(40988,72,'VOLNέ ADSL'),(41009,72,'VOLNέ ADSL'), +(41032,72,'VOLNέ ADSL'),(41038,72,'VOLNέ ADSL'), +(41063,72,'VOLNέ ADSL'),(41537,72,'VOLNέ ADSL Office'), +(42141,72,'VOLNέ ADSL'),(42565,72,'VOLNέ ADSL Combi'), +(42749,72,'VOLNέ ADSL'),(44205,72,'VOLNέ ADSL'); +SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNY ADSL'; +id tid val +40988 72 VOLNέ ADSL +41009 72 VOLNέ ADSL +41032 72 VOLNέ ADSL +41038 72 VOLNέ ADSL +41063 72 VOLNέ ADSL +42141 72 VOLNέ ADSL +42749 72 VOLNέ ADSL +44205 72 VOLNέ ADSL +SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNέ ADSL'; +id tid val +40988 72 VOLNέ ADSL +41009 72 VOLNέ ADSL +41032 72 VOLNέ ADSL +41038 72 VOLNέ ADSL +41063 72 VOLNέ ADSL +42141 72 VOLNέ ADSL +42749 72 VOLNέ ADSL +44205 72 VOLNέ ADSL +SELECT * FROM t1 WHERE tid=72 and val LIKE '%VOLNέ ADSL'; +id tid val +40988 72 VOLNέ ADSL +41009 72 VOLNέ ADSL +41032 72 VOLNέ ADSL +41038 72 VOLNέ ADSL +41063 72 VOLNέ ADSL +42141 72 VOLNέ ADSL +42749 72 VOLNέ ADSL +44205 72 VOLNέ ADSL +ALTER TABLE t1 DROP KEY idx; +ALTER TABLE t1 ADD KEY idx (tid,val(11)); +SELECT * FROM t1 WHERE tid=72 and val LIKE 'VOLNέ ADSL'; +id tid val +40988 72 VOLNέ ADSL +41009 72 VOLNέ ADSL +41032 72 VOLNέ ADSL +41038 72 VOLNέ ADSL +41063 72 VOLNέ ADSL +42141 72 VOLNέ ADSL +42749 72 VOLNέ ADSL +44205 72 VOLNέ ADSL +DROP TABLE t1; +create table t1(a char(200) collate utf8mb4_unicode_ci NOT NULL default '') +default charset=utf8mb4 collate=utf8mb4_unicode_ci; +insert into t1 values (unhex('65')), (unhex('C3A9')), (unhex('65')); +explain select distinct a from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary +select distinct a from t1; +a +e +explain select a from t1 group by a; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 3 Using temporary; Using filesort +select a from t1 group by a; +a +e +drop table t1; +create table t1(a char(10)) default charset utf8mb4; +insert into t1 values ('123'), ('456'); +explain +select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE Y ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE Z ALL NULL NULL NULL NULL 2 Using where; Using join buffer (flat, BNL join) +select substr(Z.a,-1), Z.a from t1 as Y join t1 as Z on Y.a=Z.a order by 1; +substr(Z.a,-1) a +3 123 +6 456 +drop table t1; +SET CHARACTER SET utf8mb4; +SHOW VARIABLES LIKE 'character\_set\_%'; +Variable_name Value +character_set_client utf8mb4 +character_set_connection latin1 +character_set_database latin1 +character_set_filesystem binary +character_set_results utf8mb4 +character_set_server latin1 +character_set_system utf8 +CREATE DATABASE crashtest DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin; +USE crashtest; +CREATE TABLE crashtest (crash char(10)) DEFAULT CHARSET=utf8mb4; +INSERT INTO crashtest VALUES ('35'), ('36'), ('37'); +SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8mb4); +crash +35 +36 +37 +INSERT INTO crashtest VALUES ('-1000'); +EXPLAIN SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8mb4); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE crashtest ALL NULL NULL NULL NULL 4 Using filesort +SELECT * FROM crashtest ORDER BY CHAR(crash USING utf8mb4); +crash +-1000 +35 +36 +37 +Warnings: +Warning 1300 Invalid utf8mb4 character string: 'FFFFFC' +DROP TABLE crashtest; +DROP DATABASE crashtest; +USE test; +SET CHARACTER SET default; +CREATE TABLE t1(id varchar(20) NOT NULL) DEFAULT CHARSET=utf8mb4; +INSERT INTO t1 VALUES ('xxx'), ('aa'), ('yyy'), ('aa'); +SELECT id FROM t1; +id +xxx +aa +yyy +aa +SELECT DISTINCT id FROM t1; +id +xxx +aa +yyy +SELECT DISTINCT id FROM t1 ORDER BY id; +id +aa +xxx +yyy +DROP TABLE t1; +create table t1 ( +a varchar(26) not null +) default character set utf8mb4; +insert into t1 (a) values ('abcdefghijklmnopqrstuvwxyz'); +select * from t1; +a +abcdefghijklmnopqrstuvwxyz +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +alter table t1 change a a varchar(20) character set utf8mb4 not null; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +select * from t1; +a +abcdefghijklmnopqrst +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +alter table t1 change a a char(15) character set utf8mb4 not null; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +select * from t1; +a +abcdefghijklmno +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +alter table t1 change a a char(10) character set utf8mb4 not null; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +select * from t1; +a +abcdefghij +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +alter table t1 change a a varchar(5) character set utf8mb4 not null; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +select * from t1; +a +abcde +drop table t1; +create table t1 ( +a varchar(4000) not null +) default character set utf8mb4; +insert into t1 values (repeat('a',4000)); +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +alter table t1 change a a varchar(3000) character set utf8mb4 not null; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +select length(a) from t1; +length(a) +3000 +drop table t1; +set names utf8mb4; +select hex(char(1 using utf8mb4)); +hex(char(1 using utf8mb4)) +01 +select char(0xd1,0x8f using utf8mb4); +char(0xd1,0x8f using utf8mb4) +Ρ +select char(0xd18f using utf8mb4); +char(0xd18f using utf8mb4) +Ρ +select char(53647 using utf8mb4); +char(53647 using utf8mb4) +Ρ +select char(0xff,0x8f using utf8mb4); +char(0xff,0x8f using utf8mb4) +NULL +Warnings: +Warning 1300 Invalid utf8mb4 character string: 'FF8F' +select convert(char(0xff,0x8f) using utf8mb4); +convert(char(0xff,0x8f) using utf8mb4) +?? +Warnings: +Warning 1300 Invalid utf8mb4 character string: '\xFF\x8F' +set sql_mode=traditional; +select char(0xff,0x8f using utf8mb4); +char(0xff,0x8f using utf8mb4) +NULL +Warnings: +Warning 1300 Invalid utf8mb4 character string: 'FF8F' +select char(195 using utf8mb4); +char(195 using utf8mb4) +NULL +Warnings: +Warning 1300 Invalid utf8mb4 character string: 'C3' +select char(196 using utf8mb4); +char(196 using utf8mb4) +NULL +Warnings: +Warning 1300 Invalid utf8mb4 character string: 'C4' +select char(2557 using utf8mb4); +char(2557 using utf8mb4) +NULL +Warnings: +Warning 1300 Invalid utf8mb4 character string: 'FD' +select convert(char(0xff,0x8f) using utf8mb4); +convert(char(0xff,0x8f) using utf8mb4) +?? +Warnings: +Warning 1300 Invalid utf8mb4 character string: '\xFF\x8F' +select hex(convert(char(2557 using latin1) using utf8mb4)); +hex(convert(char(2557 using latin1) using utf8mb4)) +09C3BD +select hex(char(195)); +hex(char(195)) +C3 +select hex(char(196)); +hex(char(196)) +C4 +select hex(char(2557)); +hex(char(2557)) +09FD +set names utf8mb4; +create table t1 (a char(1)) default character set utf8mb4; +create table t2 (a char(1)) default character set utf8mb4; +insert into t1 values('a'),('a'),(0xE38182),(0xE38182); +insert into t1 values('i'),('i'),(0xE38184),(0xE38184); +select * from t1 union distinct select * from t2; +a +a +γ +i +γ +drop table t1,t2; +set names utf8mb4; +create table t1 (a char(10), b varchar(10)); +insert into t1 values ('bar','kostja'); +insert into t1 values ('kostja','bar'); +prepare my_stmt from "select * from t1 where a=?"; +set @a:='bar'; +execute my_stmt using @a; +a b +bar kostja +set @a:='kostja'; +execute my_stmt using @a; +a b +kostja bar +set @a:=null; +execute my_stmt using @a; +a b +drop table if exists t1; +drop table if exists t1; +drop view if exists v1, v2; +set names utf8mb4; +create table t1(col1 varchar(12) character set utf8mb4 collate utf8mb4_unicode_ci); +insert into t1 values('t1_val'); +create view v1 as select 'v1_val' as col1; +select coercibility(col1), collation(col1) from v1; +coercibility(col1) collation(col1) +4 utf8mb4_general_ci +create view v2 as select col1 from v1 union select col1 from t1; +select coercibility(col1), collation(col1)from v2; +coercibility(col1) collation(col1) +2 utf8mb4_unicode_ci +2 utf8mb4_unicode_ci +drop view v1, v2; +create view v1 as select 'v1_val' collate utf8mb4_swedish_ci as col1; +select coercibility(col1), collation(col1) from v1; +coercibility(col1) collation(col1) +0 utf8mb4_swedish_ci +create view v2 as select col1 from v1 union select col1 from t1; +select coercibility(col1), collation(col1) from v2; +coercibility(col1) collation(col1) +0 utf8mb4_swedish_ci +0 utf8mb4_swedish_ci +drop view v1, v2; +drop table t1; +set names utf8mb4; +create table t1 (a varchar(10) character set latin1, b int); +insert into t1 values ('a',1); +select concat(a, if(b>10, N'x', N'y')) from t1; +concat(a, if(b>10, N'x', N'y')) +ay +select concat(a, if(b>10, N'Γ¦', N'Γ')) from t1; +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat' +drop table t1; +set names utf8mb4; +create table t1 (a varchar(10) character set latin1, b int); +insert into t1 values ('a',1); +select concat(a, if(b>10, _utf8mb4'x', _utf8mb4'y')) from t1; +concat(a, if(b>10, _utf8mb4'x', _utf8mb4'y')) +ay +select concat(a, if(b>10, _utf8mb4'Γ¦', _utf8mb4'Γ')) from t1; +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' +drop table t1; +set names utf8mb4; +create table t1 (a varchar(10) character set latin1, b int); +insert into t1 values ('a',1); +select concat(a, if(b>10, _utf8mb4 0x78, _utf8mb4 0x79)) from t1; +concat(a, if(b>10, _utf8mb4 0x78, _utf8mb4 0x79)) +ay +select concat(a, if(b>10, _utf8mb4 0xC3A6, _utf8mb4 0xC3AF)) from t1; +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' +drop table t1; +set names utf8mb4; +create table t1 (a varchar(10) character set latin1, b int); +insert into t1 values ('a',1); +select concat(a, if(b>10, 'x' 'x', 'y' 'y')) from t1; +concat(a, if(b>10, 'x' 'x', 'y' 'y')) +ayy +select concat(a, if(b>10, 'x' 'Γ¦', 'y' 'Γ')) from t1; +ERROR HY000: Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE) for operation 'concat' +drop table t1; +CREATE TABLE t1 ( +colA int(11) NOT NULL, +colB varchar(255) character set utf8mb4 NOT NULL, +PRIMARY KEY (colA) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t1 (colA, colB) VALUES (1, 'foo'), (2, 'foo bar'); +CREATE TABLE t2 ( +colA int(11) NOT NULL, +colB varchar(255) character set utf8mb4 NOT NULL, +KEY bad (colA,colB(3)) +) ENGINE=InnoDB DEFAULT CHARSET=latin1; +INSERT INTO t2 (colA, colB) VALUES (1, 'foo'),(2, 'foo bar'); +SELECT * FROM t1 JOIN t2 ON t1.colA=t2.colA AND t1.colB=t2.colB +WHERE t1.colA < 3; +colA colB colA colB +1 foo 1 foo +2 foo bar 2 foo bar +DROP TABLE t1, t2; +SELECT 'Π½1234567890' UNION SELECT _binary '1'; +Π½1234567890 +Π½1234567890 +1 +SELECT 'Π½1234567890' UNION SELECT 1; +Π½1234567890 +Π½1234567890 +1 +SELECT '1' UNION SELECT 'Π½1234567890'; +1 +1 +Π½1234567890 +SELECT 1 UNION SELECT 'Π½1234567890'; +1 +1 +Π½1234567890 +CREATE TABLE t1 (c VARCHAR(11)) CHARACTER SET utf8mb4; +CREATE TABLE t2 (b CHAR(1) CHARACTER SET binary, i INT); +INSERT INTO t1 (c) VALUES ('Π½1234567890'); +INSERT INTO t2 (b, i) VALUES ('1', 1); +SELECT c FROM t1 UNION SELECT b FROM t2; +c +Π½1234567890 +1 +SELECT c FROM t1 UNION SELECT i FROM t2; +c +Π½1234567890 +1 +SELECT b FROM t2 UNION SELECT c FROM t1; +b +1 +Π½1234567890 +SELECT i FROM t2 UNION SELECT c FROM t1; +i +1 +Π½1234567890 +DROP TABLE t1, t2; +set sql_mode=traditional; +select hex(char(0xFF using utf8mb4)); +hex(char(0xFF using utf8mb4)) +NULL +Warnings: +Warning 1300 Invalid utf8mb4 character string: 'FF' +select hex(convert(0xFF using utf8mb4)); +hex(convert(0xFF using utf8mb4)) +3F +Warnings: +Warning 1300 Invalid utf8mb4 character string: '\xFF' +select hex(_utf8mb4 0x616263FF); +ERROR HY000: Invalid utf8mb4 character string: 'FF' +select hex(_utf8mb4 X'616263FF'); +ERROR HY000: Invalid utf8mb4 character string: 'FF' +select hex(_utf8mb4 B'001111111111'); +ERROR HY000: Invalid utf8mb4 character string: 'FF' +select (_utf8mb4 X'616263FF'); +ERROR HY000: Invalid utf8mb4 character string: 'FF' +set sql_mode=default; +select hex(char(0xFF using utf8mb4)); +hex(char(0xFF using utf8mb4)) +NULL +Warnings: +Warning 1300 Invalid utf8mb4 character string: 'FF' +select hex(convert(0xFF using utf8mb4)); +hex(convert(0xFF using utf8mb4)) +3F +Warnings: +Warning 1300 Invalid utf8mb4 character string: '\xFF' +select hex(_utf8mb4 0x616263FF); +ERROR HY000: Invalid utf8mb4 character string: 'FF' +select hex(_utf8mb4 X'616263FF'); +ERROR HY000: Invalid utf8mb4 character string: 'FF' +select hex(_utf8mb4 B'001111111111'); +ERROR HY000: Invalid utf8mb4 character string: 'FF' +select (_utf8mb4 X'616263FF'); +ERROR HY000: Invalid utf8mb4 character string: 'FF' +CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL); +INSERT INTO t1 VALUES (70000, 1092), (70001, 1085), (70002, 1065); +SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; +CONVERT(a, CHAR) CONVERT(b, CHAR) +70002 1065 +70001 1085 +70000 1092 +SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1; +CONVERT(a, CHAR) CONVERT(b, CHAR) +70000 1092 +70001 1085 +70002 1065 +ALTER TABLE t1 ADD UNIQUE (b); +SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; +CONVERT(a, CHAR) CONVERT(b, CHAR) +70002 1065 +70001 1085 +70000 1092 +DROP INDEX b ON t1; +SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) FROM t1 GROUP BY b; +CONVERT(a, CHAR) CONVERT(b, CHAR) +70002 1065 +70001 1085 +70000 1092 +ALTER TABLE t1 ADD INDEX (b); +SELECT CONVERT(a, CHAR), CONVERT(b, CHAR) from t1 GROUP BY b; +CONVERT(a, CHAR) CONVERT(b, CHAR) +70002 1065 +70001 1085 +70000 1092 +DROP TABLE t1; +# +# Bug#26474: Add Sinhala script (Sri Lanka) collation to MySQL +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +predicted_order int NOT NULL, +utf8mb4_encoding VARCHAR(10) NOT NULL +) CHARACTER SET utf8mb4; +INSERT INTO t1 VALUES (19, x'E0B696'), (30, x'E0B69AE0B798'), (61, x'E0B6AF'), (93, x'E0B799'), (52, x'E0B6A6'), (73, x'E0B6BBE0B78AE2808D'), (3, x'E0B686'), (56, x'E0B6AA'), (55, x'E0B6A9'), (70, x'E0B6B9'), (94, x'E0B79A'), (80, x'E0B785'), (25, x'E0B69AE0B791'), (48, x'E0B6A2'), (13, x'E0B690'), (86, x'E0B793'), (91, x'E0B79F'), (81, x'E0B786'), (79, x'E0B784'), (14, x'E0B691'), (99, x'E0B78A'), (8, x'E0B68B'), (68, x'E0B6B7'), (22, x'E0B69A'), (16, x'E0B693'), (33, x'E0B69AE0B7B3'), (38, x'E0B69AE0B79D'), (21, x'E0B683'), (11, x'E0B68E'), (77, x'E0B782'), (40, x'E0B69AE0B78A'), (101, x'E0B78AE2808DE0B6BB'), (35, x'E0B69AE0B79A'), (1, x'E0B7B4'), (9, x'E0B68C'), (96, x'E0B79C'), (6, x'E0B689'), (95, x'E0B79B'), (88, x'E0B796'), (64, x'E0B6B3'), (26, x'E0B69AE0B792'), (82, x'E0B78F'), (28, x'E0B69AE0B794'), (39, x'E0B69AE0B79E'), (97, x'E0B79D'), (2, x'E0B685'), (75, x'E0B780'), (34, x'E0B69AE0B799'), (69, x'E0B6B8'), (83, x'E0B790'), (18, x'E0B695'), (90, x'E0B7B2'), (17, x'E0B694'), (72, x'E0B6BB'), (66, x'E0B6B5'), (59, x'E0B6AD'), (44, x'E0B69E'), (15, x'E0B692'), (23, x'E0B69AE0B78F'), (65, x'E0B6B4'), (42, x'E0B69C'), (63, x'E0B6B1'), (85, x'E0B792'), (47, x'E0B6A1'), (49, x'E0B6A3'), (92, x'E0B7B3'), (78, x'E0B783'), (36, x'E0B69AE0B79B'), (4, x'E0B687'), (24, x'E0B69AE0B790'), (87, x'E0B794'), (37, x'E0B69AE0B79C'), (32, x'E0B69AE0B79F'), (29, x'E0B69AE0B796'), (43, x'E0B69D'), (62, x'E0B6B0'), (100, x'E0B78AE2808DE0B6BA'), (60, x'E0B6AE'), (45, x'E0B69F'), (12, x'E0B68F'), (46, x'E0B6A0'), (50, x'E0B6A5'), (51, x'E0B6A4'), (5, x'E0B688'), (76, x'E0B781'), (89, x'E0B798'), (74, x'E0B6BD'), (10, x'E0B68D'), (57, x'E0B6AB'), (71, x'E0B6BA'), (58, x'E0B6AC'), (27, x'E0B69AE0B793'), (54, x'E0B6A8'), (84, x'E0B791'), (31, x'E0B69AE0B7B2'), (98, x'E0B79E'), (53, x'E0B6A7'), (41, x'E0B69B'), (67, x'E0B6B6'), (7, x'E0B68A'), (20, x'E0B682'); +SELECT predicted_order, hex(utf8mb4_encoding) FROM t1 ORDER BY utf8mb4_encoding COLLATE utf8mb4_sinhala_ci; +predicted_order hex(utf8mb4_encoding) +1 E0B7B4 +2 E0B685 +3 E0B686 +4 E0B687 +5 E0B688 +6 E0B689 +7 E0B68A +8 E0B68B +9 E0B68C +10 E0B68D +11 E0B68E +12 E0B68F +13 E0B690 +14 E0B691 +15 E0B692 +16 E0B693 +17 E0B694 +18 E0B695 +19 E0B696 +20 E0B682 +21 E0B683 +22 E0B69A +23 E0B69AE0B78F +24 E0B69AE0B790 +25 E0B69AE0B791 +26 E0B69AE0B792 +27 E0B69AE0B793 +28 E0B69AE0B794 +29 E0B69AE0B796 +30 E0B69AE0B798 +31 E0B69AE0B7B2 +32 E0B69AE0B79F +33 E0B69AE0B7B3 +34 E0B69AE0B799 +35 E0B69AE0B79A +36 E0B69AE0B79B +37 E0B69AE0B79C +38 E0B69AE0B79D +39 E0B69AE0B79E +40 E0B69AE0B78A +41 E0B69B +42 E0B69C +43 E0B69D +44 E0B69E +45 E0B69F +46 E0B6A0 +47 E0B6A1 +48 E0B6A2 +49 E0B6A3 +50 E0B6A5 +51 E0B6A4 +52 E0B6A6 +53 E0B6A7 +54 E0B6A8 +55 E0B6A9 +56 E0B6AA +57 E0B6AB +58 E0B6AC +59 E0B6AD +60 E0B6AE +61 E0B6AF +62 E0B6B0 +63 E0B6B1 +64 E0B6B3 +65 E0B6B4 +66 E0B6B5 +67 E0B6B6 +68 E0B6B7 +69 E0B6B8 +70 E0B6B9 +71 E0B6BA +72 E0B6BB +73 E0B6BBE0B78AE2808D +74 E0B6BD +75 E0B780 +76 E0B781 +77 E0B782 +78 E0B783 +79 E0B784 +80 E0B785 +81 E0B786 +82 E0B78F +83 E0B790 +84 E0B791 +85 E0B792 +86 E0B793 +87 E0B794 +88 E0B796 +89 E0B798 +90 E0B7B2 +91 E0B79F +92 E0B7B3 +93 E0B799 +94 E0B79A +95 E0B79B +96 E0B79C +97 E0B79D +98 E0B79E +99 E0B78A +100 E0B78AE2808DE0B6BA +101 E0B78AE2808DE0B6BB +DROP TABLE t1; +# +# Bug#32914 Character sets: illegal characters in utf8mb4 and utf32 columns +# +create table t1 (utf8mb4 char(1) character set utf8mb4); +Testing [F0][90..BF][80..BF][80..BF] +insert into t1 values (0xF0908080); +insert into t1 values (0xF0BFBFBF); +insert ignore into t1 values (0xF08F8080); +Warnings: +Warning 1366 Incorrect string value: '\xF0\x8F\x80\x80' for column 'utf8mb4' at row 1 +select hex(utf8mb4) from t1 order by binary utf8mb4; +hex(utf8mb4) +3F +F0908080 +F0BFBFBF +delete from t1; +Testing [F2..F3][80..BF][80..BF][80..BF] +insert into t1 values (0xF2808080); +insert into t1 values (0xF2BFBFBF); +select hex(utf8mb4) from t1 order by binary utf8mb4; +hex(utf8mb4) +F2808080 +F2BFBFBF +delete from t1; +Testing [F4][80..8F][80..BF][80..BF] +insert into t1 values (0xF4808080); +insert into t1 values (0xF48F8080); +insert ignore into t1 values (0xF4908080); +Warnings: +Warning 1366 Incorrect string value: '\xF4\x90\x80\x80' for column 'utf8mb4' at row 1 +select hex(utf8mb4) from t1 order by binary utf8mb4; +hex(utf8mb4) +3F +F4808080 +F48F8080 +drop table t1; +# +# Check strnxfrm() with odd length +# +set max_sort_length=5; +select @@max_sort_length; +@@max_sort_length +5 +create table t1 (a varchar(128) character set utf8mb4 collate utf8mb4_general_ci); +insert into t1 values ('a'),('b'),('c'); +select * from t1 order by a; +a +a +b +c +alter table t1 modify a varchar(128) character set utf8mb4 collate utf8mb4_bin; +select * from t1 order by a; +a +a +b +c +drop table t1; +set max_sort_length=default; +# +# Bug#26180: Can't add columns to tables created with utf8mb4 text indexes +# +CREATE TABLE t1 ( +clipid INT NOT NULL, +Tape TINYTEXT, +PRIMARY KEY (clipid), +KEY tape(Tape(255)) +) CHARACTER SET=utf8mb4; +Warnings: +Note 1071 Specified key was too long; max key length is 1000 bytes +ALTER TABLE t1 ADD mos TINYINT DEFAULT 0 AFTER clipid; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `clipid` int(11) NOT NULL, + `mos` tinyint(4) DEFAULT 0, + `Tape` tinytext DEFAULT NULL, + PRIMARY KEY (`clipid`), + KEY `tape` (`Tape`(250)) +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 +DROP TABLE t1; +# +# Testing 4-byte values. +# +DROP TABLE IF EXISTS t1; +CREATE TABLE t1 ( +u_decimal int NOT NULL, +utf8mb4_encoding VARCHAR(10) NOT NULL +) CHARACTER SET utf8mb4; +INSERT INTO t1 VALUES (119040, x'f09d8480'), +# G CLEF +(119070, x'f09d849e'), +# HALF NOTE +(119134, x'f09d859e'), +# MUSICAL SYMBOL CROIX +(119247, x'f09d878f'), +# MATHEMATICAL BOLD ITALIC CAPITAL DELTA +(120607, x'f09d9c9f'), +# SANS-SERIF BOLD ITALIC CAPITAL PI +(120735, x'f09d9e9f'), +# <Plane 16 Private Use, Last> (last 4 byte character) +(1114111, x'f48fbfbf'), +# VARIATION SELECTOR-256 +(917999, x'f3a087af'); +INSERT INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859ff09d859ff09d85a0f09d85a0f09d8480'); +INSERT INTO t1 VALUES (65131, x'efb9abf09d849ef09d859ef09d859ef09d8480f09d859fefb9abefb9abf09d85a0efb9ab'); +INSERT IGNORE INTO t1 VALUES (119070, x'f09d849ef09d859ef09d859ef09d8480f09d859ff09d859ff09d859ff09d85a0f09d85a0f09d8480f09d85a0'); +Warnings: +Warning 1265 Data truncated for column 'utf8mb4_encoding' at row 1 +SELECT u_decimal, hex(utf8mb4_encoding) FROM t1 ORDER BY utf8mb4_encoding COLLATE utf8mb4_general_ci, BINARY utf8mb4_encoding; +u_decimal hex(utf8mb4_encoding) +65131 EFB9ABF09D849EF09D859EF09D859EF09D8480F09D859FEFB9ABEFB9ABF09D85A0EFB9AB +119040 F09D8480 +119070 F09D849E +119134 F09D859E +119247 F09D878F +120607 F09D9C9F +120735 F09D9E9F +917999 F3A087AF +1114111 F48FBFBF +119070 F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480 +119070 F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480 +INSERT IGNORE INTO t1 VALUES (1114111, x'f5808080'); +Warnings: +Warning 1366 Incorrect string value: '\xF5\x80\x80\x80' for column 'utf8mb4_encoding' at row 1 +SELECT character_maximum_length, character_octet_length FROM information_schema.columns WHERE +table_name= 't1' AND column_name= 'utf8mb4_encoding'; +character_maximum_length character_octet_length +10 40 +DROP TABLE IF EXISTS t2; +CREATE TABLE t2 ( +u_decimal int NOT NULL, +utf8mb3_encoding VARCHAR(10) NOT NULL +) CHARACTER SET utf8mb3; +INSERT INTO t2 VALUES (42856, x'ea9da8'); +INSERT INTO t2 VALUES (65131, x'efb9ab'); +INSERT IGNORE INTO t2 VALUES (1114111, x'f48fbfbf'); +Warnings: +Warning 1366 Incorrect string value: '\xF4\x8F\xBF\xBF' for column 'utf8mb3_encoding' at row 1 +SELECT character_maximum_length, character_octet_length FROM information_schema.columns WHERE +table_name= 't2' AND column_name= 'utf8mb3_encoding'; +character_maximum_length character_octet_length +10 30 +UPDATE IGNORE t2 SET utf8mb3_encoding= x'f48fbfbd' where u_decimal= 42856; +Warnings: +Warning 1366 Incorrect string value: '\xF4\x8F\xBF\xBD' for column 'utf8mb3_encoding' at row 1 +UPDATE t2 SET utf8mb3_encoding= _utf8mb4 x'ea9da8' where u_decimal= 42856; +SELECT HEX(CONCAT(utf8mb4_encoding, _utf8 x'ea9da8')) FROM t1; +HEX(CONCAT(utf8mb4_encoding, _utf8 x'ea9da8')) +F09D8480EA9DA8 +F09D849EEA9DA8 +F09D859EEA9DA8 +F09D878FEA9DA8 +F09D9C9FEA9DA8 +F09D9E9FEA9DA8 +F48FBFBFEA9DA8 +F3A087AFEA9DA8 +F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EA9DA8 +EFB9ABF09D849EF09D859EF09D859EF09D8480F09D859FEFB9ABEFB9ABF09D85A0EFB9ABEA9DA8 +F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EA9DA8 +3F3F3F3FEA9DA8 +SELECT HEX(CONCAT(utf8mb4_encoding, utf8mb3_encoding)) FROM t1,t2; +HEX(CONCAT(utf8mb4_encoding, utf8mb3_encoding)) +F09D8480EA9DA8 +F09D8480EFB9AB +F09D84803F3F3F3F +F09D849EEA9DA8 +F09D849EEFB9AB +F09D849E3F3F3F3F +F09D859EEA9DA8 +F09D859EEFB9AB +F09D859E3F3F3F3F +F09D878FEA9DA8 +F09D878FEFB9AB +F09D878F3F3F3F3F +F09D9C9FEA9DA8 +F09D9C9FEFB9AB +F09D9C9F3F3F3F3F +F09D9E9FEA9DA8 +F09D9E9FEFB9AB +F09D9E9F3F3F3F3F +F48FBFBFEA9DA8 +F48FBFBFEFB9AB +F48FBFBF3F3F3F3F +F3A087AFEA9DA8 +F3A087AFEFB9AB +F3A087AF3F3F3F3F +F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EA9DA8 +F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EFB9AB +F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D84803F3F3F3F +EFB9ABF09D849EF09D859EF09D859EF09D8480F09D859FEFB9ABEFB9ABF09D85A0EFB9ABEA9DA8 +EFB9ABF09D849EF09D859EF09D859EF09D8480F09D859FEFB9ABEFB9ABF09D85A0EFB9ABEFB9AB +EFB9ABF09D849EF09D859EF09D859EF09D8480F09D859FEFB9ABEFB9ABF09D85A0EFB9AB3F3F3F3F +F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EA9DA8 +F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D8480EFB9AB +F09D849EF09D859EF09D859EF09D8480F09D859FF09D859FF09D859FF09D85A0F09D85A0F09D84803F3F3F3F +3F3F3F3FEA9DA8 +3F3F3F3FEFB9AB +3F3F3F3F3F3F3F3F +SELECT count(*) FROM t1, t2 +WHERE t1.utf8mb4_encoding > t2.utf8mb3_encoding; +count(*) +33 +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +ALTER TABLE t1 CONVERT TO CHARACTER SET utf8; +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9D\x84\x80' for column 'utf8mb4_encoding' at row 1 +Warning 1366 Incorrect string value: '\xF0\x9D\x84\x9E' for column 'utf8mb4_encoding' at row 2 +Warning 1366 Incorrect string value: '\xF0\x9D\x85\x9E' for column 'utf8mb4_encoding' at row 3 +Warning 1366 Incorrect string value: '\xF0\x9D\x87\x8F' for column 'utf8mb4_encoding' at row 4 +Warning 1366 Incorrect string value: '\xF0\x9D\x9C\x9F' for column 'utf8mb4_encoding' at row 5 +Warning 1366 Incorrect string value: '\xF0\x9D\x9E\x9F' for column 'utf8mb4_encoding' at row 6 +Warning 1366 Incorrect string value: '\xF4\x8F\xBF\xBF' for column 'utf8mb4_encoding' at row 7 +Warning 1366 Incorrect string value: '\xF3\xA0\x87\xAF' for column 'utf8mb4_encoding' at row 8 +Warning 1366 Incorrect string value: '\xF0\x9D\x84\x9E\xF0\x9D...' for column 'utf8mb4_encoding' at row 9 +Warning 1366 Incorrect string value: '\xF0\x9D\x84\x9E\xF0\x9D...' for column 'utf8mb4_encoding' at row 10 +Warning 1366 Incorrect string value: '\xF0\x9D\x84\x9E\xF0\x9D...' for column 'utf8mb4_encoding' at row 11 +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `u_decimal` int(11) NOT NULL, + `utf8mb4_encoding` varchar(10) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +SELECT u_decimal,hex(utf8mb4_encoding),utf8mb4_encoding FROM t1; +u_decimal hex(utf8mb4_encoding) utf8mb4_encoding +119040 3F ? +119070 3F ? +119134 3F ? +119247 3F ? +120607 3F ? +120735 3F ? +1114111 3F ? +917999 3F ? +119070 3F3F3F3F3F3F3F3F3F3F ?????????? +65131 EFB9AB3F3F3F3F3FEFB9ABEFB9AB3FEFB9AB οΉ«?????οΉ«οΉ«?οΉ« +119070 3F3F3F3F3F3F3F3F3F3F ?????????? +1114111 3F3F3F3F ???? +ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb4; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `u_decimal` int(11) NOT NULL, + `utf8mb3_encoding` varchar(10) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 +SELECT u_decimal,hex(utf8mb3_encoding) FROM t2; +u_decimal hex(utf8mb3_encoding) +42856 EA9DA8 +65131 EFB9AB +1114111 3F3F3F3F +ALTER TABLE t2 CONVERT TO CHARACTER SET utf8mb3; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `u_decimal` int(11) NOT NULL, + `utf8mb3_encoding` varchar(10) NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +SELECT u_decimal,hex(utf8mb3_encoding) FROM t2; +u_decimal hex(utf8mb3_encoding) +42856 EA9DA8 +65131 EFB9AB +1114111 3F3F3F3F +ALTER TABLE t1 MODIFY utf8mb4_encoding VARCHAR(10) CHARACTER SET utf8mb3; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `u_decimal` int(11) NOT NULL, + `utf8mb4_encoding` varchar(10) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +SELECT u_decimal,hex(utf8mb4_encoding) FROM t1; +u_decimal hex(utf8mb4_encoding) +119040 3F +119070 3F +119134 3F +119247 3F +120607 3F +120735 3F +1114111 3F +917999 3F +119070 3F3F3F3F3F3F3F3F3F3F +65131 EFB9AB3F3F3F3F3FEFB9ABEFB9AB3FEFB9AB +119070 3F3F3F3F3F3F3F3F3F3F +1114111 3F3F3F3F +ALTER TABLE t1 MODIFY utf8mb4_encoding VARCHAR(10) CHARACTER SET utf8mb4; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `u_decimal` int(11) NOT NULL, + `utf8mb4_encoding` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +SELECT u_decimal,hex(utf8mb4_encoding) FROM t1; +u_decimal hex(utf8mb4_encoding) +119040 3F +119070 3F +119134 3F +119247 3F +120607 3F +120735 3F +1114111 3F +917999 3F +119070 3F3F3F3F3F3F3F3F3F3F +65131 EFB9AB3F3F3F3F3FEFB9ABEFB9AB3FEFB9AB +119070 3F3F3F3F3F3F3F3F3F3F +1114111 3F3F3F3F +ALTER TABLE t2 MODIFY utf8mb3_encoding VARCHAR(10) CHARACTER SET utf8mb4; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TABLE `t2` ( + `u_decimal` int(11) NOT NULL, + `utf8mb3_encoding` varchar(10) CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +SELECT u_decimal,hex(utf8mb3_encoding) FROM t2; +u_decimal hex(utf8mb3_encoding) +42856 EA9DA8 +65131 EFB9AB +1114111 3F3F3F3F +DROP TABLE IF EXISTS t3; +CREATE TABLE t3 ( +u_decimal int NOT NULL, +utf8mb3_encoding VARCHAR(10) NOT NULL +) CHARACTER SET utf8; +INSERT INTO t3 SELECT * FROM t1; +DROP TABLE IF EXISTS t4; +CREATE TABLE t4 ( +u_decimal int NOT NULL, +utf8mb4_encoding VARCHAR(10) NOT NULL +) CHARACTER SET utf8mb4; +INSERT INTO t3 SELECT * FROM t2; +DROP TABLE t1; +DROP TABLE t2; +DROP TABLE t3; +DROP TABLE t4; +# +# Testing that mixing utf8 and utf8mb4 collations returns utf8mb4 +# +SELECT CHARSET(CONCAT(_utf8mb4'a',_utf8'b')); +CHARSET(CONCAT(_utf8mb4'a',_utf8'b')) +utf8mb4 +CREATE TABLE t1 (utf8mb4 VARCHAR(10) CHARACTER SET utf8mb4 NOT NULL); +INSERT INTO t1 VALUES (x'ea9da8'),(x'f48fbfbf'); +SELECT CONCAT(utf8mb4, _utf8 x'ea9da8') FROM t1 LIMIT 0; +CONCAT(utf8mb4, _utf8 x'ea9da8') +CREATE TABLE t2 (utf8mb3 VARCHAR(10) CHARACTER SET utf8mb3 NOT NULL); +INSERT INTO t2 VALUES (x'ea9da8'); +SELECT HEX(CONCAT(utf8mb4, utf8mb3)) FROM t1,t2 ORDER BY 1; +HEX(CONCAT(utf8mb4, utf8mb3)) +EA9DA8EA9DA8 +F48FBFBFEA9DA8 +SELECT CHARSET(CONCAT(utf8mb4, utf8mb3)) FROM t1, t2 LIMIT 1; +CHARSET(CONCAT(utf8mb4, utf8mb3)) +utf8mb4 +CREATE TEMPORARY TABLE t3 AS SELECT *, concat(utf8mb4,utf8mb3) FROM t1, t2; +SHOW CREATE TABLE t3; +Table Create Table +t3 CREATE TEMPORARY TABLE `t3` ( + `utf8mb4` varchar(10) CHARACTER SET utf8mb4 NOT NULL, + `utf8mb3` varchar(10) CHARACTER SET utf8 NOT NULL, + `concat(utf8mb4,utf8mb3)` varchar(20) CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TEMPORARY TABLE t3; +SELECT * FROM t1, t2 WHERE t1.utf8mb4 > t2.utf8mb3; +utf8mb4 utf8mb3 +τΏΏ κ¨ +SELECT * FROM t1, t2 WHERE t1.utf8mb4 = t2.utf8mb3; +utf8mb4 utf8mb3 +κ¨ κ¨ +SELECT * FROM t1, t2 WHERE t1.utf8mb4 < t2.utf8mb3; +utf8mb4 utf8mb3 +DROP TABLE t1; +DROP TABLE t2; +# +# Check that mixing utf8mb4 with an invalid utf8 constant returns error +# +CREATE TABLE t1 (utf8mb4 VARCHAR(10) CHARACTER SET utf8mb4); +INSERT INTO t1 VALUES (x'f48fbfbf'); +SELECT CONCAT(utf8mb4, _utf8 'Ώ') FROM t1; +ERROR HY000: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation 'concat' +SELECT CONCAT('a', _utf8 'Ώ') FROM t1; +ERROR HY000: Illegal mix of collations (utf8mb4_general_ci,COERCIBLE) and (utf8_general_ci,COERCIBLE) for operation 'concat' +DROP TABLE t1; +# +# Bug#51675 Server crashes on inserting 4 byte char. +# after ALTER TABLE to 'utf8mb4' +# +SET NAMES utf8; +CREATE TABLE t1 ( +subject varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci, +p VARCHAR(15) CHARACTER SET utf8 +) DEFAULT CHARSET=latin1; +ALTER TABLE t1 ADD INDEX (subject); +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +ALTER TABLE t1 +DEFAULT CHARACTER SET utf8, +MODIFY subject varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, +MODIFY p varchar(255) CHARACTER SET utf8; +Warnings: +Warning 1071 Specified key was too long; max key length is 1000 bytes +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `p` varchar(255) DEFAULT NULL, + KEY `subject` (`subject`(250)) +) ENGINE=MyISAM DEFAULT CHARSET=utf8 +INSERT INTO t1(subject) VALUES ('abcd'); +INSERT INTO t1(subject) VALUES(x'f0909080'); +DROP TABLE t1; +CREATE TABLE t1 (a TEXT CHARACTER SET utf8mb4, FULLTEXT INDEX(a)); +INSERT INTO t1 VALUES (0xF0A08080 /* U+20000 */ ); +DROP TABLE t1; +# +# Bug #51676 Server crashes on SELECT, ORDER BY on 'utf8mb4' column +# +SET NAMES utf8mb4; +CREATE TABLE t1 ( +subject varchar(255) character set utf8mb4 collate utf8mb4_unicode_ci, +p varchar(15) character set utf8mb4 +) DEFAULT CHARSET=latin1; +INSERT INTO t1(subject) VALUES(0xF0909080); +INSERT INTO t1(subject) VALUES(0x616263F0909080646566); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL, + `p` varchar(15) CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +SELECT * FROM t1 ORDER BY 1; +subject p +abcπdef NULL +π NULL +SELECT hex(subject), length(subject), char_length(subject), octet_length(subject) FROM t1 ORDER BY 1; +hex(subject) length(subject) char_length(subject) octet_length(subject) +616263F0909080646566 10 7 10 +F0909080 4 1 4 +SELECT subject FROM t1 ORDER BY 1; +subject +abcπdef +π +DROP TABLE t1; +# +# Bug#52520 Difference in tinytext utf column metadata +# +CREATE TABLE t1 ( +s1 TINYTEXT CHARACTER SET utf8mb4, +s2 TEXT CHARACTER SET utf8mb4, +s3 MEDIUMTEXT CHARACTER SET utf8mb4, +s4 LONGTEXT CHARACTER SET utf8mb4 +); +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 45 +def test t1 t1 s2 s2 252 65535 0 Y 16 0 45 +def test t1 t1 s3 s3 252 16777215 0 Y 16 0 45 +def test t1 t1 s4 s4 252 4294967295 0 Y 16 0 45 +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 255 0 Y 16 0 8 +def test t1 t1 s2 s2 252 65535 0 Y 16 0 8 +def test t1 t1 s3 s3 252 16777215 0 Y 16 0 8 +def test t1 t1 s4 s4 252 4294967295 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 1020 0 Y 16 0 45 +def test t1 t1 s2 s2 252 262140 0 Y 16 0 45 +def test t1 t1 s3 s3 252 67108860 0 Y 16 0 45 +def test t1 t1 s4 s4 252 4294967295 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 utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1, t2; +# +# Bug#13581962 HIGH MEMORY USAGE ATTEMPT, THEN CRASH WITH LONGTEXT, UNION, USER VARIABLE +# Bug#14096619 UNABLE TO RESTORE DATABASE DUMP +# +CREATE TABLE t1(f1 LONGTEXT CHARACTER SET utf8mb4); +INSERT INTO t1 VALUES ('a'); +SELECT @a:= CAST(f1 AS SIGNED) FROM t1 +UNION ALL +SELECT CAST(f1 AS SIGNED) FROM t1; +@a:= CAST(f1 AS SIGNED) +0 +0 +Warnings: +Warning 1292 Truncated incorrect INTEGER value: 'a' +Warning 1292 Truncated incorrect INTEGER value: 'a' +DROP TABLE t1; +# End of test for Bug#13581962,Bug#14096619 +# +# MDEV-9319 ALTER from a bigger to a smaller blob type truncates too much data +# +SET NAMES utf8mb4; +CREATE TABLE t1 (a TEXT CHARACTER SET utf8mb4); +INSERT INTO t1 VALUES (REPEAT('π',100)); +SELECT OCTET_LENGTH(a) FROM t1; +OCTET_LENGTH(a) +400 +SET STATEMENT sql_mode = 'NO_ENGINE_SUBSTITUTION' FOR +ALTER TABLE t1 MODIFY a TINYTEXT CHARACTER SET utf8mb4; +Warnings: +Warning 1265 Data truncated for column 'a' at row 1 +SELECT OCTET_LENGTH(a),a FROM t1; +OCTET_LENGTH(a) a +252 πππππππππππππππππππππππππππππππππππππππππππππππππππππππππππππππ +DROP TABLE t1; +# +# MDEV-8402 Bug#77473 Bug#21317406 TRUNCATED DATA WITH SUBQUERY & UTF8 +# +# +SET NAMES utf8mb4; +SELECT length(repeat(_utf8mb4 0xE29883, 21844)) AS data; +data +65532 +SELECT length(data) AS len +FROM ( SELECT repeat(_utf8mb4 0xE29883, 21844) AS data ) AS sub; +len +65532 +SELECT length(repeat(_utf8mb4 0xE29883, 21846)) AS data; +data +65538 +SELECT length(data) AS len +FROM ( SELECT repeat(_utf8mb4 0xE29883, 21846) AS data ) AS sub; +len +65538 +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('β', 21844) AS data ) AS sub; +len +65532 +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('β', 21845) AS data ) AS sub; +len +65535 +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('β', 21846) AS data ) AS sub; +len +65538 +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('β', 65535) AS data ) AS sub; +len +196605 +SELECT LENGTH(data) AS len FROM (SELECT REPEAT('β', 65536) AS data ) AS sub; +len +196608 +# +# End of 5.5 tests +# +# +# WL#3664 WEIGHT_STRING +# +set names utf8mb4; +select @@collation_connection; +@@collation_connection +utf8mb4_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 utf8mb4 NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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 +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 utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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 +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 +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 +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 +utf8mb4_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 @@collation_connection; +@@collation_connection +utf8mb4_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=utf8mb4_bin; +select @@collation_connection; +@@collation_connection +utf8mb4_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 utf8mb4 COLLATE utf8mb4_bin NOT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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 +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 utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +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 +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 +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 +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 +utf8mb4_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 +utf8mb4_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-11343 LOAD DATA INFILE fails to load data with an escape character followed by a multi-byte character +# +CREATE TABLE t1 (a TEXT CHARACTER SET utf8mb4); +LOAD DATA INFILE '../../std_data/loaddata/mdev-11343.txt' INTO TABLE t1 CHARACTER SET utf8mb4; +SELECT HEX(a) FROM t1; +HEX(a) +C3A4 +C3A478 +78C3A4 +78C3A478 +EA99A0 +EA99A078 +78EA99A0 +78EA99A078 +F09F988E +F09F988E78 +78F09F988E +78F09F988E78 +DROP TABLE t1; +# +# MDEV-6566 Different INSERT behaviour on bad bytes with and without character set conversion +# +# +# This test sets session character set to 3-byte utf8, +# but then sends a 4-byte sequence (which is wrong for 3-byte utf8). +# It should be replaced to four question marks: '????' in both columns +# (i.e. four unknown bytes are replaced to four question marks), +# then the rest of the string should be stored, so we get 'a ???? b'. +# +SET NAMES utf8; +CREATE TABLE t1 ( +a VARCHAR(32) CHARACTER SET utf8mb4, +b VARCHAR(32) CHARACTER SET utf8 +); +INSERT IGNORE INTO t1 SELECT 'a π b', 'a π b'; +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81 b' for column 'a' at row 1 +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81 b' for column 'b' at row 1 +SELECT * FROM t1; +a b +a ???? b a ???? b +DROP TABLE t1; +# +# This test sets session character set to 4-byte utf8, +# then normally sends a 4-byte sequence. +# It should be stored AS IS into the utf8mb4 column (a), +# and should be replaced to a single question mark in the utf8 column (b) +# (i.e. one character that cannot be converted is replaced to one question mark). +# +SET NAMES utf8mb4; +CREATE TABLE t1 ( +a VARCHAR(32) CHARACTER SET utf8mb4, +b VARCHAR(32) CHARACTER SET utf8 +); +INSERT IGNORE INTO t1 SELECT 'a π b', 'a π b'; +Warnings: +Warning 1366 Incorrect string value: '\xF0\x9F\x98\x81 b' for column 'b' at row 1 +SELECT * FROM t1; +a b +a π b a ? b +DROP TABLE t1; +# +# MDEV-8949: COLUMN_CREATE unicode name breakage +# +SET NAMES utf8mb4; +SELECT COLUMN_JSON(COLUMN_CREATE(_utf8mb4 0xF09F988E, 1)); +COLUMN_JSON(COLUMN_CREATE(_utf8mb4 0xF09F988E, 1)) +{"π":1} +SELECT COLUMN_LIST(COLUMN_CREATE(_utf8mb4 0xF09F988E, 1)); +COLUMN_LIST(COLUMN_CREATE(_utf8mb4 0xF09F988E, 1)) +`π` +SELECT COLUMN_GET(COLUMN_CREATE(_utf8mb4 0xF09F988E, 1), _utf8mb4 0xF09F988E +as int); +COLUMN_GET(COLUMN_CREATE(_utf8mb4 0xF09F988E, 1), _utf8mb4 0xF09F988E +as int) +1 +CREATE TABLE t1 AS SELECT +COLUMN_LIST(COLUMN_CREATE('a',1)), +COLUMN_JSON(COLUMN_CREATE('b',1)); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `COLUMN_LIST(COLUMN_CREATE('a',1))` longtext CHARACTER SET utf8mb4 DEFAULT NULL, + `COLUMN_JSON(COLUMN_CREATE('b',1))` longtext CHARACTER SET utf8mb4 DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +DROP TABLE t1; +SET NAMES default; +# +# End of 10.0 tests +# +# +# Start of 10.1 tests +# +# +# MDEV-6572 "USE dbname" with a bad sequence erroneously connects to a wrong database +# +SET NAMES utf8mb4; +SELECT * FROM `testππtest`; +ERROR HY000: Invalid utf8mb4 character string: 'test\xF0\x9F\x98\x81\xF0\x9F\x98\x81test' +# +# MDEV-7231 Field ROUTINE_DEFINITION in INFORMATION_SCHEMA.`ROUTINES` contains broken procedure body when used shielding quotes inside. +# +SET NAMES utf8mb4; +CREATE FUNCTION f1() RETURNS TEXT CHARACTER SET utf8mb4 +RETURN CONCAT('π','xπ','πy','xπy'); +SELECT ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES +WHERE ROUTINE_SCHEMA='test' AND SPECIFIC_NAME ='f1'; +ROUTINE_DEFINITION +RETURN CONCAT('?','x?','?y','x?y') +SELECT body_utf8 FROM mysql.proc WHERE name='f1'; +body_utf8 +RETURN CONCAT('?','x?','?y','x?y') +DROP FUNCTION f1; +# +# End of 10.1 tests +# +# +# End of 10.2 tests +# +# +# MDEV-9842 LOAD DATA INFILE does not work well with a TEXT column when using sjis +# +CREATE TABLE t1 (a TEXT CHARACTER SET utf8mb4); +LOAD DATA INFILE '../../std_data/loaddata/mdev9823.utf8mb4.txt' IGNORE INTO TABLE t1 CHARACTER SET utf8mb4 IGNORE 4 LINES; +Warnings: +Warning 1366 Incorrect string value: '\xD0' for column 'a' at row 1 +Warning 1366 Incorrect string value: '\xE1\x80' for column 'a' at row 3 +Warning 1366 Incorrect string value: '\xF0\x9F\x98' for column 'a' at row 5 +Warning 1366 Incorrect string value: '\xF0\x9F\x98' for column 'a' at row 8 +SELECT HEX(a) FROM t1; +HEX(a) +3F +78787831 +3F3F +78787832 +3F3F3F +78787833 +F09F988E +3F3F3F +DROP TABLE t1; +# +# MDEV-9711 NO PAD Collatons +# +SET STORAGE_ENGINE=MyISAM; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf8mb4_general_nopad_ci'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE utf8mb4_general_nopad_ci NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_nopad_ci +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf8mb4_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 +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf8mb4_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 +2061 a +206120 a +61 a +612020 a +61202020 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) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf8mb4_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 +6161 1 +61612020 1 +6162 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 'utf8mb4_general_nopad_ci' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'utf8mb4_general_nopad_ci' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'utf8mb4_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'utf8mb4_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'utf8mb4_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'utf8mb4_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'utf8mb4_general_nopad_ci', 'abc ')); +HEX(LEAST('abc ' COLLATE 'utf8mb4_general_nopad_ci', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'utf8mb4_general_nopad_ci', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'utf8mb4_general_nopad_ci', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf8mb4_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 'utf8mb4_general_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_general_nopad_ci'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf8mb4_general_nopad_ci'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_general_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_general_nopad_ci'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=HEAP; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf8mb4_general_nopad_ci'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE utf8mb4_general_nopad_ci NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_nopad_ci +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf8mb4_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 +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf8mb4_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 +2061 a +206120 a +61 a +612020 a +61202020 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) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf8mb4_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 +6161 1 +61612020 1 +6162 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 'utf8mb4_general_nopad_ci' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'utf8mb4_general_nopad_ci' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'utf8mb4_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'utf8mb4_general_nopad_ci' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'utf8mb4_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'utf8mb4_general_nopad_ci' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'utf8mb4_general_nopad_ci', 'abc ')); +HEX(LEAST('abc ' COLLATE 'utf8mb4_general_nopad_ci', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'utf8mb4_general_nopad_ci', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'utf8mb4_general_nopad_ci', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf8mb4_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 'utf8mb4_general_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_general_nopad_ci'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf8mb4_general_nopad_ci'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_general_ci'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_general_nopad_ci'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=Default; +SET STORAGE_ENGINE=MyISAM; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf8mb4_nopad_bin'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE utf8mb4_nopad_bin NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_nopad_bin +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf8mb4_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 +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf8mb4_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 +2061 a +206120 a +61 a +612020 a +61202020 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) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf8mb4_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 +6161 1 +61612020 1 +6162 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 'utf8mb4_nopad_bin' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'utf8mb4_nopad_bin' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'utf8mb4_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'utf8mb4_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'utf8mb4_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'utf8mb4_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'utf8mb4_nopad_bin', 'abc ')); +HEX(LEAST('abc ' COLLATE 'utf8mb4_nopad_bin', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'utf8mb4_nopad_bin', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'utf8mb4_nopad_bin', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf8mb4_bin'; +INSERT INTO t1 VALUES ('a'),('a '); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_nopad_bin'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf8mb4_nopad_bin'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_nopad_bin'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=HEAP; +# +# Start of ctype_pad.inc +# +# +# Unique indexes +# +CREATE TABLE t1 (a VARCHAR(10) PRIMARY KEY) COLLATE 'utf8mb4_nopad_bin'; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(10) COLLATE utf8mb4_nopad_bin NOT NULL, + PRIMARY KEY (`a`) +) ENGINE=MEMORY DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_nopad_bin +INSERT INTO t1 VALUES ('abc'),('abc '),(' a'),(' a '),('a '); +SELECT HEX(a), a FROM t1 ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a; +HEX(a) a +2061 a +206120 a +61202020 a +616263 abc +6162632020 abc +SELECT HEX(a), a FROM t1 IGNORE INDEX(PRIMARY) ORDER BY a DESC; +HEX(a) a +6162632020 abc +616263 abc +61202020 a +206120 a +2061 a +# +# UNION +# +CREATE TABLE t2 (a VARCHAR(10)) COLLATE 'utf8mb4_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 +2061 a +206120 a +6120 a +61202020 a +616263 abc +61626320 abc +6162632020 abc +DROP TABLE t1; +DROP TABLE t2; +# +# DISTINCT, COUNT, MAX +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf8mb4_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 +2061 a +206120 a +61 a +612020 a +61202020 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) +61202020 a +# +# GROUP BY +# +CREATE TABLE t2 (a VARCHAR(10), b int, c varchar(10)) COLLATE 'utf8mb4_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 +6161 1 +61612020 1 +6162 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 'utf8mb4_nopad_bin' = 'abc ', 'pad', 'nopad'); +IF('abc' COLLATE 'utf8mb4_nopad_bin' = 'abc ', 'pad', 'nopad') +nopad +SELECT CASE 'abc' COLLATE 'utf8mb4_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE 'abc' COLLATE 'utf8mb4_nopad_bin' WHEN 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT CASE WHEN 'abc' COLLATE 'utf8mb4_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END; +CASE WHEN 'abc' COLLATE 'utf8mb4_nopad_bin' = 'abc ' THEN 'pad' ELSE 'nopad' END +nopad +SELECT HEX(LEAST('abc ' COLLATE 'utf8mb4_nopad_bin', 'abc ')); +HEX(LEAST('abc ' COLLATE 'utf8mb4_nopad_bin', 'abc ')) +61626320 +SELECT HEX(GREATEST('abc ' COLLATE 'utf8mb4_nopad_bin', 'abc ')); +HEX(GREATEST('abc ' COLLATE 'utf8mb4_nopad_bin', 'abc ')) +6162632020 +# +# Collation mix +# +CREATE TABLE t1 (a VARCHAR(10)) COLLATE 'utf8mb4_bin'; +INSERT INTO t1 VALUES ('a'),('a '); +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_nopad_bin'; +COUNT(*) +1 +ALTER TABLE t1 MODIFY a VARCHAR(10) COLLATE 'utf8mb4_nopad_bin'; +SELECT COUNT(*) FROM t1 WHERE a='a'; +COUNT(*) +1 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_bin'; +COUNT(*) +2 +SELECT COUNT(*) FROM t1 WHERE a='a' COLLATE 'utf8mb4_nopad_bin'; +COUNT(*) +1 +DROP TABLE t1; +# +# End of ctype_pad.inc +# +SET STORAGE_ENGINE=Default; +# +# MDEV-10867 PREPARE..EXECUTE is not consistent about non-ASCII characters +# +SET NAMES utf8mb4; +SELECT 'π' AS c; +c +π +SET @src='SELECT ''π'' AS c'; +PREPARE stmt FROM @src; +EXECUTE stmt; +c +π +EXECUTE IMMEDIATE @src; +c +π +PREPARE stmt FROM 'SELECT ''π'' AS c'; +EXECUTE stmt; +c +π +EXECUTE IMMEDIATE 'SELECT ''π'' AS c'; +c +π +# +# End of 10.2 tests +# |