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
|
-- source include/have_ucs2.inc
delimiter |;
#
# BUG#17615: problem with character set
#
--disable_warnings
drop function if exists bug17615|
--enable_warnings
create table t3 (a varchar(256) unicode)|
create function bug17615() returns varchar(256) unicode
begin
declare tmp_res varchar(256) unicode;
set tmp_res= 'foo string';
return tmp_res;
end|
insert into t3 values(bug17615())|
select * from t3|
drop function bug17615|
drop table t3|
#
# Testing COLLATE clause in
# - IN parameter
# - RETURNS
# - DELCARE
#
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|
SELECT f('a')|
SELECT collation(f('a'))|
DROP FUNCTION f|
#
# Testing keywords UNICODE + BINARY
#
CREATE FUNCTION f()
RETURNS VARCHAR(64) UNICODE BINARY
BEGIN
RETURN '';
END|
SHOW CREATE FUNCTION f|
DROP FUNCTION f|
CREATE FUNCTION f()
RETURNS VARCHAR(64) BINARY UNICODE
BEGIN
RETURN '';
END|
SHOW CREATE FUNCTION f|
DROP FUNCTION f|
#
# Testing keywords ASCII + BINARY
#
CREATE FUNCTION f()
RETURNS VARCHAR(64) ASCII BINARY
BEGIN
RETURN '';
END|
SHOW CREATE FUNCTION f|
DROP FUNCTION f|
CREATE FUNCTION f()
RETURNS VARCHAR(64) BINARY ASCII
BEGIN
RETURN '';
END|
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|
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|
SELECT f1()|
DROP PROCEDURE p1|
DROP FUNCTION f1|
#
# COLLATE with no CHARACTER SET in IN param
#
--error ER_COLLATION_CHARSET_MISMATCH
CREATE FUNCTION f(f1 VARCHAR(64) COLLATE ucs2_unicode_ci)
RETURNS VARCHAR(64) CHARACTER SET ucs2
BEGIN
RETURN 'str';
END|
#
# COLLATE with no CHARACTER SET in RETURNS
#
--error ER_COLLATION_CHARSET_MISMATCH
CREATE FUNCTION f(f1 VARCHAR(64) CHARACTER SET ucs2)
RETURNS VARCHAR(64) COLLATE ucs2_unicode_ci
BEGIN
RETURN 'str';
END|
#
# COLLATE with no CHARACTER SET in DECLARE
#
--error ER_COLLATION_CHARSET_MISMATCH
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|
delimiter ;|
#
# Bug#48766 SHOW CREATE FUNCTION returns extra data in return clause
#
SET NAMES utf8;
#
# Test that Latin letters are not prepended with extra '\0'.
#
CREATE FUNCTION bug48766 ()
RETURNS ENUM( 'w' ) CHARACTER SET ucs2
RETURN 0;
SHOW CREATE FUNCTION bug48766;
SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='bug48766';
DROP FUNCTION bug48766;
#
# Test non-Latin characters
#
CREATE FUNCTION bug48766 ()
RETURNS ENUM('а','б','в','г') CHARACTER SET ucs2
RETURN 0;
SHOW CREATE FUNCTION bug48766;
SELECT DTD_IDENTIFIER FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME='bug48766';
DROP FUNCTION bug48766;
#
#
#
call mtr.add_suppression('invalid value in column mysql.proc.');
set collation_connection=ucs2_general_ci;
insert into mysql.proc (db, name, type, specific_name, language, sql_data_access, is_deterministic, security_type, param_list, returns, body, definer, created, modified, sql_mode, comment, character_set_client, collation_connection, db_collation, body_utf8 ) values ( 'a', 'a', 'function', 'bug14233_1', 'sql', 'reads_sql_data', 'no', 'definer', '', 'int(10)', 'select * from mysql.user', 'root@localhost', now(), '0000-00-00 00:00:00', '', '', '', '', '', 'select * from mysql.user' );
select routine_name from information_schema.routines where routine_name='a';
set collation_connection=default;
delete from mysql.proc where name='a';
|
