2
DROP TABLE IF EXISTS t1;
3
DROP TABLE IF EXISTS t2;
7
latin1_f CHAR(32) CHARACTER SET latin1 NOT NULL
12
latin1_f CHAR(32) CHARACTER SET latin1 COLLATE koi8r_general_ci NOT NULL
17
latin1_f CHAR(32) CHARACTER SET latin1 COLLATE some_non_existing_col NOT NULL
21
INSERT INTO t1 (latin1_f) VALUES (_latin1'A');
22
INSERT INTO t1 (latin1_f) VALUES (_latin1'a');
24
INSERT INTO t1 (latin1_f) VALUES (_latin1'AD');
25
INSERT INTO t1 (latin1_f) VALUES (_latin1'ad');
27
INSERT INTO t1 (latin1_f) VALUES (_latin1'AE');
28
INSERT INTO t1 (latin1_f) VALUES (_latin1'ae');
30
INSERT INTO t1 (latin1_f) VALUES (_latin1'AF');
31
INSERT INTO t1 (latin1_f) VALUES (_latin1'af');
33
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
34
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
36
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
37
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
39
INSERT INTO t1 (latin1_f) VALUES (_latin1'B');
40
INSERT INTO t1 (latin1_f) VALUES (_latin1'b');
42
INSERT INTO t1 (latin1_f) VALUES (_latin1'U');
43
INSERT INTO t1 (latin1_f) VALUES (_latin1'u');
45
INSERT INTO t1 (latin1_f) VALUES (_latin1'UE');
46
INSERT INTO t1 (latin1_f) VALUES (_latin1'ue');
48
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
49
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
51
INSERT INTO t1 (latin1_f) VALUES (_latin1'SS');
52
INSERT INTO t1 (latin1_f) VALUES (_latin1'ss');
53
INSERT INTO t1 (latin1_f) VALUES (_latin1'�');
55
INSERT INTO t1 (latin1_f) VALUES (_latin1'Y');
56
INSERT INTO t1 (latin1_f) VALUES (_latin1'y');
58
INSERT INTO t1 (latin1_f) VALUES (_latin1'Z');
59
INSERT INTO t1 (latin1_f) VALUES (_latin1'z');
64
SELECT latin1_f FROM t1 ORDER BY latin1_f;
65
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_swedish_ci;
66
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_german2_ci;
67
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_general_ci;
68
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE latin1_bin;
70
SELECT latin1_f FROM t1 ORDER BY latin1_f COLLATE koi8r_general_ci;
72
# SELECT latin1_f COLLATE koi8r FROM t1 ;
75
SELECT latin1_f COLLATE latin1_swedish_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
76
SELECT latin1_f COLLATE latin1_german2_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
77
SELECT latin1_f COLLATE latin1_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
78
SELECT latin1_f COLLATE latin1_bin AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
80
SELECT latin1_f COLLATE koi8r_general_ci AS latin1_f_as FROM t1 ORDER BY latin1_f_as;
85
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f;
86
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_swedish_ci;
87
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_german2_ci;
88
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_general_ci;
89
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE latin1_bin;
91
SELECT latin1_f,count(*) FROM t1 GROUP BY latin1_f COLLATE koi8r_general_ci;
96
SELECT DISTINCT latin1_f FROM t1;
97
SELECT DISTINCT latin1_f COLLATE latin1_swedish_ci FROM t1;
98
SELECT DISTINCT latin1_f COLLATE latin1_german2_ci FROM t1;
99
SELECT DISTINCT latin1_f COLLATE latin1_general_ci FROM t1;
100
SELECT DISTINCT latin1_f COLLATE latin1_bin FROM t1;
102
SELECT DISTINCT latin1_f COLLATE koi8r FROM t1;
108
SELECT MAX(k COLLATE latin1_german2_ci)
113
WHERE (_latin1'Mu"ller' COLLATE latin1_german2_ci) = k
117
HAVING (_latin1'Mu"ller' COLLATE latin1_german2_ci) = k;
121
# Check that SHOW displays COLLATE clause
124
SHOW CREATE TABLE t1;
126
ALTER TABLE t1 CHANGE latin1_f
127
latin1_f CHAR(32) CHARACTER SET latin1 COLLATE latin1_bin;
128
SHOW CREATE TABLE t1;
130
ALTER TABLE t1 CHARACTER SET latin1 COLLATE latin1_bin;
131
SHOW CREATE TABLE t1;
135
# Check SET CHARACTER SET
138
SET CHARACTER SET 'latin1';
139
SHOW VARIABLES LIKE 'character_set_client';
140
SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
141
explain extended SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
143
SET CHARACTER SET koi8r;
144
SHOW VARIABLES LIKE 'collation_client';
145
SELECT charset('a'),collation('a'),coercibility('a'),'a'='A';
148
SET CHARACTER SET 'DEFAULT';
153
(s1 CHAR(5) COLLATE latin1_german1_ci,
154
s2 CHAR(5) COLLATE latin1_swedish_ci);
156
SELECT * FROM t1 WHERE s1 = s2;
161
(s1 CHAR(5) COLLATE latin1_german1_ci,
162
s2 CHAR(5) COLLATE latin1_swedish_ci,
163
s3 CHAR(5) COLLATE latin1_bin);
164
INSERT INTO t1 VALUES ('a','A','A');
166
SELECT * FROM t1 WHERE s1 = s2;
167
SELECT * FROM t1 WHERE s1 = s3;
168
SELECT * FROM t1 WHERE s2 = s3;
173
# Test that optimizer doesn't use indexes with wrong collation
177
(s1 char(10) COLLATE latin1_german1_ci,
178
s2 char(10) COLLATE latin1_swedish_ci,
182
INSERT INTO t1 VALUES ('a','a');
183
INSERT INTO t1 VALUES ('b','b');
184
INSERT INTO t1 VALUES ('c','c');
185
INSERT INTO t1 VALUES ('d','d');
186
INSERT INTO t1 VALUES ('e','e');
187
INSERT INTO t1 VALUES ('f','f');
188
INSERT INTO t1 VALUES ('g','g');
189
INSERT INTO t1 VALUES ('h','h');
190
INSERT INTO t1 VALUES ('i','i');
191
INSERT INTO t1 VALUES ('j','j');
193
EXPLAIN SELECT * FROM t1 WHERE s1='a';
194
EXPLAIN SELECT * FROM t1 WHERE s2='a';
195
EXPLAIN SELECT * FROM t1 WHERE s1='a' COLLATE latin1_german1_ci;
196
EXPLAIN SELECT * FROM t1 WHERE s2='a' COLLATE latin1_german1_ci;
198
EXPLAIN SELECT * FROM t1 WHERE s1 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
199
EXPLAIN SELECT * FROM t1 WHERE s2 BETWEEN 'a' AND 'b' COLLATE latin1_german1_ci;
201
EXPLAIN SELECT * FROM t1 WHERE s1 IN ('a','b' COLLATE latin1_german1_ci);
202
EXPLAIN SELECT * FROM t1 WHERE s2 IN ('a','b' COLLATE latin1_german1_ci);
204
EXPLAIN SELECT * FROM t1 WHERE s1 LIKE 'a' COLLATE latin1_german1_ci;
205
EXPLAIN SELECT * FROM t1 WHERE s2 LIKE 'a' COLLATE latin1_german1_ci;
212
# Bug#29261: Sort order of the collation wasn't used when comparing trailing
215
create table t1(f1 varchar(10) character set latin2 collate latin2_hungarian_ci, key(f1));
216
insert into t1 set f1=0x3F3F9DC73F;
217
insert into t1 set f1=0x3F3F1E563F;
218
insert into t1 set f1=0x3F3F;
219
check table t1 extended;
223
# Bug#29461: Sort order of the collation wasn't used when comparing characters
224
# with the space character.
226
create table t1 (a varchar(2) character set latin7 collate latin7_general_ci,key(a));
227
insert into t1 set a=0x4c20;
228
insert into t1 set a=0x6c;
229
insert into t1 set a=0x4c98;
230
check table t1 extended;
234
# Bug#41627 Illegal mix of collations in LEAST / GREATEST / CASE
236
select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci);
238
select least(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci) as f1;
239
show create table t1;
242
select case _latin1'a' when _latin2'b' then 1 when _latin5'c' collate
243
latin5_turkish_ci then 2 else 3 end;
245
select concat(_latin1'a',_latin2'b',_latin5'c' collate latin5_turkish_ci);