DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1 ( latin1_f CHAR(32) CHARACTER SET latin1 NOT NULL ); CREATE TABLE t2 ( latin1_f CHAR(32) CHARACTER SET latin1 COLLATE koi8r_general_ci NOT NULL ); ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1' CREATE TABLE t2 ( latin1_f CHAR(32) CHARACTER SET latin1 COLLATE some_non_existing_col NOT NULL ); ERROR HY000: Unknown collation: 'some_non_existing_col' INSERT INTO t1 (latin1_f) VALUES (_latin1'A'); INSERT INTO t1 (latin1_f) VALUES (_latin1'a'); INSERT INTO t1 (latin1_f) VALUES (_latin1'AD'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ad'); INSERT INTO t1 (latin1_f) VALUES (_latin1'AE'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ae'); INSERT INTO t1 (latin1_f) VALUES (_latin1'AF'); INSERT INTO t1 (latin1_f) VALUES (_latin1'af'); INSERT INTO t1 (latin1_f) VALUES (_latin1'Ä'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ä'); INSERT INTO t1 (latin1_f) VALUES (_latin1'Å'); INSERT INTO t1 (latin1_f) VALUES (_latin1'å'); INSERT INTO t1 (latin1_f) VALUES (_latin1'B'); INSERT INTO t1 (latin1_f) VALUES (_latin1'b'); INSERT INTO t1 (latin1_f) VALUES (_latin1'U'); INSERT INTO t1 (latin1_f) VALUES (_latin1'u'); INSERT INTO t1 (latin1_f) VALUES (_latin1'UE'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ue'); INSERT INTO t1 (latin1_f) VALUES (_latin1'Ü'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ü'); INSERT INTO t1 (latin1_f) VALUES (_latin1'SS'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ss'); INSERT INTO t1 (latin1_f) VALUES (_latin1'ß'); INSERT INTO t1 (latin1_f) VALUES (_latin1'Y'); INSERT INTO t1 (latin1_f) VALUES (_latin1'y'); INSERT INTO t1 (latin1_f) VALUES (_latin1'Z'); INSERT INTO t1 (latin1_f) VALUES (_latin1'z'); SELECT latin1_f FROM t1 ORDER BY latin1_f; latin1_f A a AD ad ae AE AF af b B SS ss u U UE ue Y y ü Ü Z z å Å Ä ä ß SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_swedish_ci; latin1_f A a AD ad ae AE AF af b B SS ss u U UE ue Y y ü Ü Z z å Å Ä ä ß SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_german2_ci; latin1_f A a Å å AD ad Ä ae AE ä af AF b B ß ss SS U u ue UE ü Ü Y y Z z SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_general_ci; latin1_f A a AD ad AE ae af AF Ä ä Å å b B ss SS ß U u UE ue ü Ü Y y Z z SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_bin; latin1_f A AD AE AF B SS U UE Y Z a ad ae af b ss u ue y z Ä Å Ü ß ä å ü SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE koi8r_general_ci; ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1' SELECT latin1_f COLLATE latin1_swedish_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; latin1_f_as A a AD ad ae AE AF af b B SS ss u U UE ue Y y ü Ü Z z å Å Ä ä ß SELECT latin1_f COLLATE latin1_german2_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; latin1_f_as A a Å å AD ad Ä ae AE ä af AF b B ß ss SS U u ue UE ü Ü Y y Z z SELECT latin1_f COLLATE latin1_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; latin1_f_as A a AD ad AE ae af AF Ä ä Å å b B ss SS ß U u UE ue ü Ü Y y Z z SELECT latin1_f COLLATE latin1_bin AS latin1_f_as FROM t1 ORDER BY latin1_f_as; latin1_f_as A AD AE AF B SS U UE Y Z a ad ae af b ss u ue y z Ä Å Ü ß ä å ü SELECT latin1_f COLLATE koi8r_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as; ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1' SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f; latin1_f count(*) A 2 AD 2 AE 2 AF 2 B 2 SS 2 U 2 UE 2 Ü 4 Z 2 Å 2 Ä 2 ß 1 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_swedish_ci; latin1_f count(*) A 2 AD 2 AE 2 AF 2 B 2 SS 2 U 2 UE 2 Ü 4 Z 2 Å 2 Ä 2 ß 1 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_german2_ci; latin1_f count(*) A 4 AD 2 AE 4 AF 2 B 2 SS 3 U 2 UE 4 Y 2 Z 2 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_general_ci; latin1_f count(*) A 2 AD 2 AE 2 AF 2 Ä 2 Å 2 B 2 SS 2 ß 1 U 2 UE 2 Ü 2 Y 2 Z 2 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_bin; latin1_f count(*) A 1 AD 1 AE 1 AF 1 B 1 SS 1 U 1 UE 1 Y 1 Z 1 a 1 ad 1 ae 1 af 1 b 1 ss 1 u 1 ue 1 y 1 z 1 Ä 1 Å 1 Ü 1 ß 1 ä 1 å 1 ü 1 SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE koi8r_general_ci; ERROR 42000: COLLATION 'koi8r_general_ci' is not valid for CHARACTER SET 'latin1' SELECT DISTINCT latin1_f FROM t1; latin1_f A AD AE AF Ä Å B U UE Ü SS ß Z SELECT DISTINCT latin1_f COLLATE latin1_swedish_ci FROM t1; latin1_f COLLATE latin1_swedish_ci A AD AE AF Ä Å B U UE Ü SS ß Z SELECT DISTINCT latin1_f COLLATE latin1_german2_ci FROM t1; latin1_f COLLATE latin1_german2_ci A AD AE AF B U UE SS Y Z SELECT DISTINCT latin1_f COLLATE latin1_general_ci FROM t1; latin1_f COLLATE latin1_general_ci A AD AE AF Ä Å B U UE Ü SS ß Y Z SELECT DISTINCT latin1_f COLLATE latin1_bin FROM t1; latin1_f COLLATE latin1_bin A a AD ad AE ae AF af Ä ä Å å B b U u UE ue Ü ü SS ss ß Y y Z z SELECT DISTINCT latin1_f COLLATE koi8r FROM t1; ERROR HY000: Unknown collation: 'koi8r' SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `latin1_f` char(32) NOT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SHOW FIELDS FROM t1; Field Type Null Key Default Extra latin1_f char(32) NO NULL ALTER TABLE t1 CHANGE latin1_f latin1_f CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `latin1_f` char(32) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci SHOW FIELDS FROM t1; Field Type Null Key Default Extra latin1_f char(32) YES NULL ALTER TABLE t1 CHARACTER SET latin1 COLLATE latin1_bin; SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `latin1_f` char(32) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_bin SHOW FIELDS FROM t1; Field Type Null Key Default Extra latin1_f char(32) YES NULL SET CHARACTER SET 'latin1'; SHOW VARIABLES LIKE 'character_set_client'; Variable_name Value character_set_client latin1 SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; charset('a') collation('a') coercibility('a') 'a'='A' latin1 latin1_swedish_ci 4 1 explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select charset('a') AS `charset('a')`,collation('a') AS `collation('a')`,coercibility('a') AS `coercibility('a')`,'a' = 'A' AS `'a'='A'` SET CHARACTER SET koi8r; SHOW VARIABLES LIKE 'collation_client'; Variable_name Value SELECT charset('a'),collation('a'),coercibility('a'),'a'='A'; charset('a') collation('a') coercibility('a') 'a'='A' latin1 latin1_swedish_ci 4 1 SET CHARACTER SET 'DEFAULT'; ERROR 42000: Unknown character set: 'DEFAULT' DROP TABLE t1; CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci, s2 CHAR(5) COLLATE latin1_swedish_ci); SELECT * FROM t1 WHERE s1 = s2; ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' DROP TABLE t1; CREATE TABLE t1 (s1 CHAR(5) COLLATE latin1_german1_ci, s2 CHAR(5) COLLATE latin1_swedish_ci, s3 CHAR(5) COLLATE latin1_bin); INSERT INTO t1 VALUES ('a','A','A'); SELECT * FROM t1 WHERE s1 = s2; ERROR HY000: Illegal mix of collations (latin1_german1_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '=' SELECT * FROM t1 WHERE s1 = s3; s1 s2 s3 SELECT * FROM t1 WHERE s2 = s3; s1 s2 s3 a A A DROP TABLE t1; create table t1 (a varchar(1) character set latin1 collate latin1_general_ci); insert into t1 values ('A'),('a'),('B'),('b'),('C'),('c'); select * from t1 where a > 'B' collate latin1_bin; a a b C c select * from t1 where a <> 'B' collate latin1_bin; a A a b C c create index i on t1 (a); select * from t1 where a > 'B' collate latin1_bin; a a b C c select * from t1 where a <> 'B' collate latin1_bin; a A a b C c drop table t1; SET NAMES latin1; CREATE TABLE t1 (s1 char(10) COLLATE latin1_german1_ci, s2 char(10) COLLATE latin1_swedish_ci, KEY(s1), KEY(s2)); INSERT INTO t1 VALUES ('a','a'); INSERT INTO t1 VALUES ('b','b'); INSERT INTO t1 VALUES ('c','c'); INSERT INTO t1 VALUES ('d','d'); INSERT INTO t1 VALUES ('e','e'); INSERT INTO t1 VALUES ('f','f'); INSERT INTO t1 VALUES ('g','g'); INSERT INTO t1 VALUES ('h','h'); INSERT INTO t1 VALUES ('i','i'); INSERT INTO t1 VALUES ('j','j'); EXPLAIN SELECT * FROM t1 WHERE s1='a'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref s1 s1 11 const 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2='a'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref s2 s2 11 const 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ref s1 s1 11 const 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range s1 s1 11 NULL 2 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 IN ('a','b' COLLATE latin1_german1_ci); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range s1 s1 11 NULL 2 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2 IN ('a','b' COLLATE latin1_german1_ci); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range s1 s1 11 NULL 1 Using index condition EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL s2 NULL NULL NULL 10 Using where DROP TABLE t1; create table t1(f1 varchar(10) character set latin2 collate latin2_hungarian_ci, key(f1)); insert into t1 set f1=0x3F3F9DC73F; insert into t1 set f1=0x3F3F1E563F; insert into t1 set f1=0x3F3F; check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; create table t1 (a varchar(2) character set latin7 collate latin7_general_ci,key(a)); insert into t1 set a=0x4c20; insert into t1 set a=0x6c; insert into t1 set a=0x4c98; check table t1 extended; Table Op Msg_type Msg_text test.t1 check status OK drop table t1; select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci); least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) a create table t1 select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) as f1; show create table t1; Table Create Table t1 CREATE TABLE `t1` ( `f1` varchar(1) CHARACTER SET latin5 COLLATE latin5_turkish_ci DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop table t1; select case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate latin5_turkish_ci then 2 else 3 end; case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate latin5_turkish_ci then 2 else 3 end 3 select concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci); concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) abc # # Bug#11765016 57926: ILLEGAL MIX OF COLLATIONS FOR OPERATION 'UNION' .. USING CONCAT/FUNCTION/ # Not a bug: only adding coverage tests # SET NAMES latin1 COLLATE latin1_german2_ci; CREATE DATABASE test1 DEFAULT CHARACTER SET latin1 COLLATE latin1_german2_ci; USE test1; # # Using "COLLATE latin1_swedish_ci" as the default collation for latin1 # CREATE FUNCTION `getText`() RETURNS varchar(20) CHARSET latin1 BEGIN RETURN "Testtext"; END;// SELECT getText(), CHARSET(getText()), COLLATION(getText()), COERCIBILITY(getText()); getText() CHARSET(getText()) COLLATION(getText()) COERCIBILITY(getText()) Testtext latin1 latin1_swedish_ci 4 CREATE TABLE t1 AS SELECT ' - ' AS a UNION SELECT getText(); ERROR HY000: Illegal mix of collations for operation 'UNION' DROP FUNCTION getText; # # Using "CHARACTER SET latin1 COLLATE latin1_german2_ci" as the database defaults # CREATE FUNCTION `getText`() RETURNS varchar(20) BEGIN RETURN "Testtext"; END;// SELECT getText(), CHARSET(getText()), COLLATION(getText()), COERCIBILITY(getText()); getText() CHARSET(getText()) COLLATION(getText()) COERCIBILITY(getText()) Testtext latin1 latin1_german2_ci 4 CREATE TABLE t1 AS SELECT ' - ' AS a UNION SELECT getText(); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci DROP TABLE t1; DROP FUNCTION getText; # # Using explicit "CHARACTER SET latin1 COLLATE latin1_german2_ci" # CREATE FUNCTION `getText`() RETURNS varchar(20) CHARACTER SET latin1 COLLATE latin1_german2_ci BEGIN RETURN "Testtext"; END;// SELECT getText(), CHARSET(getText()), COLLATION(getText()), COERCIBILITY(getText()); getText() CHARSET(getText()) COLLATION(getText()) COERCIBILITY(getText()) Testtext latin1 latin1_german2_ci 4 CREATE TABLE t1 AS SELECT ' - ' AS a UNION SELECT getText(); SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `a` varchar(20) DEFAULT NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_german2_ci DROP TABLE t1; DROP FUNCTION getText; DROP DATABASE test1; USE test; SET NAMES latin1; # # MDEV-11320, MySQL BUG#81810: Inconsistent sort order for blob/text between InnoDB and filesort # CREATE TABLE t1 ( b LONGTEXT CHARACTER SET "latin1" COLLATE "latin1_bin", KEY b (b(32)) ); INSERT INTO t1 (b) VALUES ('a'), (_binary 0x1), (_binary 0x0), (''); drop table t1; CREATE TABLE t1 ( b LONGTEXT CHARACTER SET "latin1" COLLATE "latin1_bin", PRIMARY KEY b (b(32)) ); Warnings: Warning 1280 Name 'b' ignored for PRIMARY key. INSERT INTO t1 (b) VALUES ('a'), (_binary 0x1), (_binary 0x0), (''); explain select hex(b) from t1 force index (PRIMARY) where b<'zzz'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 34 NULL 4 Using where select hex(b) from t1 force index (PRIMARY) where b<'zzz'; hex(b) 00 01 61 explain select hex(b) from t1 where b<'zzz' order by b; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 34 NULL 4 Using where; Using filesort select hex(b) from t1 where b<'zzz' order by b; hex(b) 00 01 61 drop table t1; # # Start of 10.2 tests # # # MDEV-27690 Crash on `CHARACTER SET csname COLLATE DEFAULT` in column definition # CREATE TABLE t1 (a CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT); DROP TABLE t1; SELECT CAST('a' AS CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT); CAST('a' AS CHAR(10) CHARACTER SET latin1 COLLATE DEFAULT) a SELECT COLUMN_GET(COLUMN_CREATE(0, 'string'),0 AS CHAR CHARACTER SET latin1 COLLATE DEFAULT) AS c1; c1 string # # End of 10.2 tests #