Debian Codegraph @

mysql-test/suite/sys_vars/t/default_collation_for_utf8mb4.test

raw
1 SELECT @@GLOBAL.default_collation_for_utf8mb4;
2 SELECT @@SESSION.default_collation_for_utf8mb4;
3
4 --echo #
5
6 SET @@default_collation_for_utf8mb4 = DEFAULT;
7 SELECT @@default_collation_for_utf8mb4;
8
9 --echo #
10
11 SET @@default_collation_for_utf8mb4 = utf8mb4_0900_ai_ci;
12 SELECT @@default_collation_for_utf8mb4;
13
14 --echo #
15
16 SET @@default_collation_for_utf8mb4 = utf8mb4_general_ci;
17 SELECT @@default_collation_for_utf8mb4;
18
19 --echo #
20
21 --error ER_UNKNOWN_COLLATION
22 SET @@default_collation_for_utf8mb4 = utf8mb4;
23
24 --echo #
25
26 --error ER_INVALID_DEFAULT_UTF8MB4_COLLATION
27 SET @@default_collation_for_utf8mb4 = latin2_general_ci;
28
29 --echo #
30
31 SET @@default_collation_for_utf8mb4 = utf8mb4_general_ci;
32
33 --echo #
34 --echo # String literals
35 --echo #
36
37 SELECT COLLATION(_utf8mb4 'a');
38 SELECT COLLATION(_utf8mb4 'b' COLLATE utf8mb4_0900_ai_ci);
39 SELECT COLLATION(_utf8mb4 0x20);
40 SELECT COLLATION(_utf8mb4 0x20 COLLATE utf8mb4_0900_ai_ci);
41 SELECT COLLATION(_utf8mb4 0b10100);
42 SELECT COLLATION(_utf8mb4 0b10100 COLLATE utf8mb4_0900_ai_ci);
43
44 --echo #
45 --echo # SET NAMES
46 --echo #
47
48 SELECT @@collation_database;
49
50 --echo #
51
52 SET NAMES utf8mb4;
53 SELECT @@collation_connection;
54
55 --echo #
56
57 SET NAMES utf8mb4 COLLATE utf8mb4_0900_ai_ci;
58 SELECT @@collation_connection;
59
60 --echo #
61
62 SET NAMES latin1;
63 SELECT @@collation_connection;
64
65 --echo #
66
67 SET NAMES DEFAULT;
68 SELECT @@collation_connection;
69
70 --echo #
71
72 SET @@default_collation_for_utf8mb4 = DEFAULT;
73 SET NAMES DEFAULT;
74 SET @@default_collation_for_utf8mb4 = utf8mb4_general_ci;
75
76 --echo #
77 --echo # CREATE/ALTER SCHEMA/DATABASE
78 --echo #
79
80 CREATE DATABASE db1;
81 --query_vertical SHOW CREATE DATABASE db1
82 ALTER DATABASE db1 CHARSET utf8mb4;
83 --query_vertical SHOW CREATE DATABASE db1
84 DROP DATABASE db1;
85
86 --echo #
87
88 CREATE DATABASE db2 CHARSET utf8mb4;
89 --query_vertical SHOW CREATE DATABASE db2
90 ALTER DATABASE db2 CHARSET utf8mb4;
91 --query_vertical SHOW CREATE DATABASE db2
92 ALTER DATABASE db2 CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
93 --query_vertical SHOW CREATE DATABASE db2
94 DROP DATABASE db2;
95
96 --echo #
97
98 CREATE DATABASE db3 CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
99 --query_vertical SHOW CREATE DATABASE db3
100 DROP DATABASE db3;
101
102 --echo #
103
104 CREATE DATABASE db4_latin1 CHARSET latin1;
105 --query_vertical SHOW CREATE DATABASE db4_latin1
106
107 --echo #
108
109 CREATE DATABASE db5_utf8mb4_general_ci CHARSET utf8mb4;
110 --query_vertical SHOW CREATE DATABASE db5_utf8mb4_general_ci
111
112 --echo #
113 --echo # CREATE/ALTER TABLE
114 --echo #
115
116 CREATE TABLE db4_latin1.t1 (
117 c1 CHAR(1) CHARSET utf8mb4,
118 c2 CHAR(1) COLLATE utf8mb4_0900_ai_ci,
119 c3 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
120 c4 CHAR(1),
121 c5 CHAR(1) CHARSET latin1
122 );
123 --query_vertical SHOW CREATE TABLE db4_latin1.t1
124 DROP TABLE db4_latin1.t1;
125
126
127 --echo #
128
129 CREATE TABLE t2 (
130 c1 CHAR(1) CHARSET utf8mb4,
131 c2 CHAR(1) COLLATE utf8mb4_0900_ai_ci,
132 c3 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
133 c4 CHAR(1),
134 c5 CHAR(1) CHARSET latin1
135 );
136 --query_vertical SHOW CREATE TABLE t2
137 DROP TABLE t2;
138
139 --echo #
140
141 CREATE TABLE t3 (
142 c1 CHAR(1) CHARSET utf8mb4,
143 c2 CHAR(1) COLLATE utf8mb4_0900_ai_ci,
144 c3 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
145 c4 CHAR(1),
146 c5 CHAR(1) CHARSET latin1
147 ) CHARSET utf8mb4;
148 --query_vertical SHOW CREATE TABLE t3
149 DROP TABLE t3;
150
151 --echo #
152
153 CREATE TABLE t4 (
154 c1 CHAR(1) CHARSET utf8mb4,
155 c2 CHAR(1) COLLATE utf8mb4_0900_ai_ci,
156 c3 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
157 c4 CHAR(1),
158 c5 CHAR(1) CHARSET latin1
159 ) CHARSET latin1;
160 --query_vertical SHOW CREATE TABLE t4
161 DROP TABLE t4;
162
163 --delimiter //
164
165 --echo #
166 --echo # CREATE/ALTER FUNCTION
167 --echo #
168
169 CREATE FUNCTION db5_utf8mb4_general_ci.f1(
170 c1 CHAR(1) CHARSET utf8mb4,
171 c2 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
172 c3 CHAR(1),
173 c4 CHAR(1) CHARSET latin1)
174 RETURNS CHAR(246)
175 BEGIN
176 DECLARE p1 CHAR(1) CHARSET utf8mb4;
177 DECLARE p2 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
178 DECLARE p3 CHAR(1);
179 DECLARE p4 CHAR(1) CHARSET latin1;
180 RETURN CONCAT('c1:', COLLATION(c1),
181 ' c2:', COLLATION(c2),
182 ' c3:', COLLATION(c3),
183 ' c4:', COLLATION(c4),
184 ' p1:', COLLATION(p1),
185 ' p2:', COLLATION(p2),
186 ' p3:', COLLATION(p3),
187 ' p4:', COLLATION(p4));
188 END//
189 --query_vertical SHOW CREATE FUNCTION db5_utf8mb4_general_ci.f1
190 SELECT db5_utf8mb4_general_ci.f1('1', '2', '3', '4'),
191 COLLATION(db5_utf8mb4_general_ci.f1('1', '2', '3', '4'))//
192 DROP FUNCTION db5_utf8mb4_general_ci.f1//
193
194 --echo #
195
196 CREATE FUNCTION f2(
197 c1 CHAR(1) CHARSET utf8mb4,
198 c2 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
199 c3 CHAR(1),
200 c4 CHAR(1) CHARSET latin1)
201 RETURNS CHAR(246)
202 BEGIN
203 DECLARE p1 CHAR(1) CHARSET utf8mb4;
204 DECLARE p2 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
205 DECLARE p3 CHAR(1);
206 DECLARE p4 CHAR(1) CHARSET latin1;
207 RETURN CONCAT('c1:', COLLATION(c1),
208 ' c2:', COLLATION(c2),
209 ' c3:', COLLATION(c3),
210 ' c4:', COLLATION(c4),
211 ' p1:', COLLATION(p1),
212 ' p2:', COLLATION(p2),
213 ' p3:', COLLATION(p3),
214 ' p4:', COLLATION(p4));
215 END//
216 --query_vertical SHOW CREATE FUNCTION f2
217 SELECT f2('1', '2', '3', '4'), COLLATION(f2('1', '2', '3', '4'))//
218 DROP FUNCTION f2//
219
220 --echo #
221
222 CREATE FUNCTION f3(
223 c1 CHAR(1) CHARSET utf8mb4,
224 c2 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
225 c3 CHAR(1),
226 c4 CHAR(1) CHARSET latin1)
227 RETURNS CHAR(246)
228 BEGIN
229 DECLARE p1 CHAR(1) CHARSET utf8mb4;
230 DECLARE p2 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
231 DECLARE p3 CHAR(1);
232 DECLARE p4 CHAR(1) CHARSET latin1;
233 RETURN CONCAT('c1:', COLLATION(c1),
234 ' c2:', COLLATION(c2),
235 ' c3:', COLLATION(c3),
236 ' c4:', COLLATION(c4),
237 ' p1:', COLLATION(p1),
238 ' p2:', COLLATION(p2),
239 ' p3:', COLLATION(p3),
240 ' p4:', COLLATION(p4));
241 END//
242 --query_vertical SHOW CREATE FUNCTION f3
243 SELECT f3('1', '2', '3', '4'), COLLATION(f3('1', '2', '3', '4'))//
244 DROP FUNCTION f3//
245
246 --echo #
247
248 CREATE FUNCTION f4(
249 c1 CHAR(1) CHARSET utf8mb4,
250 c2 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci,
251 c3 CHAR(1),
252 c4 CHAR(1) CHARSET latin1)
253 RETURNS CHAR(246)
254 BEGIN
255 DECLARE p1 CHAR(1) CHARSET utf8mb4;
256 DECLARE p2 CHAR(1) CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
257 DECLARE p3 CHAR(1);
258 DECLARE p4 CHAR(1) CHARSET latin1;
259 RETURN CONCAT('c1:', COLLATION(c1),
260 ' c2:', COLLATION(c2),
261 ' c3:', COLLATION(c3),
262 ' c4:', COLLATION(c4),
263 ' p1:', COLLATION(p1),
264 ' p2:', COLLATION(p2),
265 ' p3:', COLLATION(p3),
266 ' p4:', COLLATION(p4));
267 END//
268 --query_vertical SHOW CREATE FUNCTION f4
269 SELECT f4('1', '2', '3', '4'), COLLATION(f4('1', '2', '3', '4'))//
270 DROP FUNCTION f4//
271
272
273 --echo #
274 --echo # Cleanup
275 --echo #
276
277 --delimiter ;
278 DROP DATABASE db4_latin1;
279 DROP DATABASE db5_utf8mb4_general_ci;
280
281
282 --echo #
283 --echo # Bug#27607720: WRONG UPDATION OF COLLATION AFTER ALTER TABLE COMMAND
284 --echo #
285
286 SET SESSION default_collation_for_utf8mb4 = utf8mb4_general_ci;
287
288 SHOW GLOBAL VARIABLES LIKE '%default_collation_for_utf8mb4%';
289 SHOW SESSION VARIABLES LIKE '%default_collation_for_utf8mb4%';
290
291 CREATE DATABASE db1;
292 USE db1;
293 CREATE TABLE st1 (c1 INT ,c3 varchar(10) UNIQUE KEY);
294 SHOW CREATE TABLE st1;
295 ALTER TABLE st1 DROP COLUMN c1;
296
297 SHOW CREATE TABLE st1;
298
299 DROP DATABASE db1;
300 USE test;
301
302 SET @@default_collation_for_utf8mb4 = DEFAULT;
303
304 --echo #