path: root/mysql-test/r/ctype_collate.result

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
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
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) COLLATE latin1_bin 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 NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=latin1
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) COLLATE latin1_german2_ci 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) COLLATE latin1_german2_ci 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;