1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
|
drop function if exists bug17615|
create table t3 (a varchar(256) unicode)|
Warnings:
Warning 1287 'UNICODE' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
create function bug17615() returns varchar(256) unicode
begin
declare tmp_res varchar(256) unicode;
set tmp_res= 'foo string';
return tmp_res;
end|
Warnings:
Warning 1287 'UNICODE' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
Warning 1287 'UNICODE' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
insert into t3 values(bug17615())|
select * from t3|
a
foo string
drop function bug17615|
drop table t3|
CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_unicode_ci)
RETURNS VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_danish_ci
BEGIN
DECLARE f2 VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_swedish_ci;
DECLARE f3 VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_bin;
SET f1= concat(collation(f1), ' ', collation(f2), ' ', collation(f3));
RETURN f1;
END|
Warnings:
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning 4079 'ucs2_unicode_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning 4079 'ucs2_danish_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning 4079 'ucs2_swedish_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning 4079 'ucs2_bin' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
SELECT f('a')|
f('a')
ucs2_unicode_ci ucs2_swedish_ci ucs2_bin
Warnings:
Warning 4079 'ucs2_unicode_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
Warning 4079 'ucs2_danish_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
Warning 4079 'ucs2_swedish_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
Warning 4079 'ucs2_bin' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
SELECT collation(f('a'))|
collation(f('a'))
ucs2_danish_ci
DROP FUNCTION f|
CREATE FUNCTION f()
RETURNS VARCHAR(64) UNICODE BINARY
BEGIN
RETURN '';
END|
Warnings:
Warning 1287 'UNICODE' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
SHOW CREATE FUNCTION f;
DROP FUNCTION f;
CREATE FUNCTION f()
RETURNS VARCHAR(64) BINARY UNICODE
BEGIN
RETURN '';
END|
Function sql_mode Create Function character_set_client collation_connection Database Collation
f ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET ucs2 COLLATE ucs2_bin
BEGIN
RETURN '';
END utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
Warnings:
Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
Warning 1287 'UNICODE' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
SHOW CREATE FUNCTION f;
DROP FUNCTION f;
#
# Testing keywords ASCII + BINARY
#
CREATE FUNCTION f()
RETURNS VARCHAR(64) ASCII BINARY
BEGIN
RETURN '';
END|
Function sql_mode Create Function character_set_client collation_connection Database Collation
f ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET ucs2 COLLATE ucs2_bin
BEGIN
RETURN '';
END utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
Warnings:
Warning 1287 'ASCII' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
SHOW CREATE FUNCTION f;
DROP FUNCTION f;
CREATE FUNCTION f()
RETURNS VARCHAR(64) BINARY ASCII
BEGIN
RETURN '';
END|
Function sql_mode Create Function character_set_client collation_connection Database Collation
f ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET latin1 COLLATE latin1_bin
BEGIN
RETURN '';
END utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
Warnings:
Warning 1287 'BINARY as attribute of a type' is deprecated and will be removed in a future release. Please use a CHARACTER SET clause with _bin collation instead
Warning 1287 'ASCII' is deprecated and will be removed in a future release. Please use CHARACTER SET charset_name instead
SHOW CREATE FUNCTION f;
DROP FUNCTION f;
#
# Testing COLLATE in OUT parameter
#
CREATE PROCEDURE p1(IN f1 VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_czech_ci,
OUT f2 VARCHAR(64) CHARACTER SET ucs2 COLLATE ucs2_polish_ci)
BEGIN
SET f2= f1;
SET f2= concat(collation(f1), ' ', collation(f2));
END|
Function sql_mode Create Function character_set_client collation_connection Database Collation
f ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `f`() RETURNS varchar(64) CHARSET latin1 COLLATE latin1_bin
BEGIN
RETURN '';
END utf8mb4 utf8mb4_0900_ai_ci utf8mb4_0900_ai_ci
Warnings:
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning 4079 'ucs2_czech_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning 4079 'ucs2_polish_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
CREATE FUNCTION f1()
RETURNS VARCHAR(64) CHARACTER SET ucs2
BEGIN
DECLARE f1 VARCHAR(64) CHARACTER SET ucs2;
DECLARE f2 VARCHAR(64) CHARACTER SET ucs2;
SET f1='str';
CALL p1(f1, f2);
RETURN f2;
END|
Warnings:
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
SELECT f1()|
f1()
ucs2_czech_ci ucs2_polish_ci
Warnings:
Warning 4079 'ucs2_czech_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
Warning 4079 'ucs2_polish_ci' is a collation of the deprecated character set ucs2. Please consider using utf8mb4 with an appropriate collation instead.
DROP PROCEDURE p1|
DROP FUNCTION f1|
CREATE FUNCTION f(f1 VARCHAR(64) COLLATE ucs2_unicode_ci)
RETURNS VARCHAR(64) CHARACTER SET ucs2
BEGIN
RETURN 'str';
END|
ERROR 42000: This version of MySQL doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE'
CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2)
RETURNS VARCHAR(64) COLLATE ucs2_unicode_ci
BEGIN
RETURN 'str';
END|
ERROR 42000: This version of MySQL doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE'
CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2)
RETURNS VARCHAR(64) CHARACTER SET ucs2
BEGIN
DECLARE f2 VARCHAR(64) COLLATE ucs2_unicode_ci;
RETURN 'str';
END|
ERROR 42000: This version of MySQL doesn't yet support 'COLLATE with no CHARACTER SET in SP parameters, RETURNS, DECLARE'
SET NAMES utf8;
Warnings:
Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
DROP FUNCTION IF EXISTS bug48766;
CREATE FUNCTION bug48766 ()
RETURNS ENUM( 'w' ) CHARACTER SET ucs2
RETURN 0;
Warnings:
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
SHOW CREATE FUNCTION bug48766;
Function sql_mode Create Function character_set_client collation_connection Database Collation
bug48766 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `bug48766`() RETURNS enum('w') CHARSET ucs2
RETURN 0 utf8mb3 utf8mb3_general_ci utf8mb4_0900_ai_ci
SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='bug48766';
DTD_IDENTIFIER
enum('w')
DROP FUNCTION bug48766;
CREATE FUNCTION bug48766 ()
RETURNS ENUM('а','б','в','г') CHARACTER SET ucs2
RETURN 0;
Warnings:
Warning 1287 'ucs2' is deprecated and will be removed in a future release. Please use utf8mb4 instead
SHOW CREATE FUNCTION bug48766;
Function sql_mode Create Function character_set_client collation_connection Database Collation
bug48766 ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` FUNCTION `bug48766`() RETURNS enum('а','б','в','г') CHARSET ucs2
RETURN 0 utf8mb3 utf8mb3_general_ci utf8mb4_0900_ai_ci
SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='bug48766';
DTD_IDENTIFIER
enum('а','б','в','г')
DROP FUNCTION bug48766;
|
