1
drop table if exists t1;
2
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
3
CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
4
CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
5
ERROR HY000: Can't find symbol 'myfunc_nonexist' in library
6
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
7
CREATE FUNCTION sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
8
CREATE FUNCTION lookup RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
9
CREATE FUNCTION reverse_lookup
10
RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
11
CREATE AGGREGATE FUNCTION avgcost
12
RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
13
select myfunc_double();
14
ERROR HY000: Can't initialize function 'myfunc_double'; myfunc_double must have at least one argument
15
select myfunc_double(1);
18
select myfunc_double(78654);
21
select myfunc_nonexist();
22
ERROR 42000: FUNCTION test.myfunc_nonexist does not exist
27
ERROR HY000: Can't initialize function 'lookup'; Wrong arguments to lookup; Use the source
28
select lookup("127.0.0.1");
31
select lookup(127,0,0,1);
32
ERROR HY000: Can't initialize function 'lookup'; Wrong arguments to lookup; Use the source
33
select lookup("localhost");
36
select reverse_lookup();
37
ERROR HY000: Can't initialize function 'reverse_lookup'; Wrong number of arguments to reverse_lookup; Use the source
38
select reverse_lookup("127.0.0.1");
39
select reverse_lookup(127,0,0,1);
40
select reverse_lookup("localhost");
41
reverse_lookup("localhost")
44
ERROR HY000: Can't initialize function 'avgcost'; wrong number of arguments: AVGCOST() requires two arguments
45
select avgcost(100,23.76);
46
ERROR HY000: Can't initialize function 'avgcost'; wrong argument type: AVGCOST() requires an INT and a REAL
47
create table t1(sum int, price float(24));
48
insert into t1 values(100, 50.00), (100, 100.00);
49
select avgcost(sum, price) from t1;
53
insert into t1 values(100, 54.33), (200, 199.99);
54
select avgcost(sum, price) from t1;
58
select metaphon('hello');
61
CREATE PROCEDURE `XXX1`(in testval varchar(10))
63
select metaphon(testval);
69
CREATE PROCEDURE `XXX2`()
71
declare testval varchar(10);
72
set testval = 'hello';
73
select metaphon(testval);
79
CREATE TABLE bug19904(n INT, v varchar(10));
80
INSERT INTO bug19904 VALUES (1,'one'),(2,'two'),(NULL,NULL),(3,'three'),(4,'four');
81
SELECT myfunc_double(n) AS f FROM bug19904;
88
SELECT metaphon(v) AS f FROM bug19904;
96
CREATE DEFINER=CURRENT_USER() FUNCTION should_not_parse
97
RETURNS STRING SONAME "should_not_parse.so";
98
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNS STRING SONAME "should_not_parse.so"' at line 2
99
CREATE DEFINER=someone@somewhere FUNCTION should_not_parse
100
RETURNS STRING SONAME "should_not_parse.so";
101
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RETURNS STRING SONAME "should_not_parse.so"' at line 2
102
create table t1(f1 int);
103
insert into t1 values(1),(2);
104
explain select myfunc_int(f1) from t1 order by 1;
105
id select_type table type possible_keys key key_len ref rows Extra
106
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort
108
CREATE TABLE t1(a INT, b INT);
109
INSERT INTO t1 values (1,1),(2,2);
110
CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC
115
CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a;
116
SELECT myfunc_int(a AS attr_name) FROM t1;
117
myfunc_int(a AS attr_name)
120
EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1;
121
id select_type table type possible_keys key key_len ref rows filtered Extra
122
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
124
Note 1003 select myfunc_int(`test`.`t1`.`a` AS `attr_name`) AS `myfunc_int(a AS attr_name)` from `test`.`t1`
125
EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1;
126
id select_type table type possible_keys key key_len ref rows filtered Extra
127
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00
129
Note 1003 select myfunc_int(`test`.`t1`.`a` AS `a`) AS `myfunc_int(a)` from `test`.`t1`
134
SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a;
135
ERROR 42000: Incorrect parameters in the call to stored function 'fn'
136
SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
137
ERROR 42000: Incorrect parameters in the call to stored function 'fn'
138
SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
139
ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'xx)) as c FROM t1 GROUP BY a' at line 1
140
SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
144
SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
148
EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
149
id select_type table type possible_keys key key_len ref rows filtered Extra
150
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
152
Note 1003 select myfunc_int(min(`test`.`t1`.`b`) AS `xx`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
153
EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
154
id select_type table type possible_keys key key_len ref rows filtered Extra
155
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
157
Note 1003 select `test`.`fn`(min(`test`.`t1`.`b`)) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
158
EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
159
id select_type table type possible_keys key key_len ref rows filtered Extra
160
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
162
Note 1003 select myfunc_int(`fn`(min(`test`.`t1`.`b`)) AS `fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
163
EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
164
id select_type table type possible_keys key key_len ref rows filtered Extra
165
1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using temporary; Using filesort
167
Note 1003 select myfunc_int(`test`.`fn`(min(`test`.`t1`.`b`)) AS `test.fn(MIN(b))`) AS `c` from `test`.`t1` group by `test`.`t1`.`a`
168
SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
172
SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
176
SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
180
SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
188
select myfunc_double(3);
191
select myfunc_double(3 AS three);
192
myfunc_double(3 AS three)
194
select myfunc_double(abs(3));
195
myfunc_double(abs(3))
197
select myfunc_double(abs(3) AS named_param);
198
myfunc_double(abs(3) AS named_param)
200
select abs(myfunc_double(3));
201
abs(myfunc_double(3))
203
select abs(myfunc_double(3 AS three));
204
abs(myfunc_double(3 AS three))
206
select myfunc_double(abs(3 AS wrong));
207
ERROR 42000: Incorrect parameters in the call to native function 'abs'
208
select abs(myfunc_double(3) AS wrong);
209
ERROR 42000: Incorrect parameters in the call to native function 'abs'
210
drop function if exists pi;
211
CREATE FUNCTION pi RETURNS STRING SONAME "should_not_parse.so";
212
ERROR HY000: This function 'pi' has the same name as a native function
213
DROP FUNCTION IF EXISTS metaphon;
214
CREATE FUNCTION metaphon(a int) RETURNS int
216
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
217
DROP FUNCTION metaphon;
218
DROP FUNCTION metaphon;
219
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
220
CREATE FUNCTION metaphon(a int) RETURNS int
222
ERROR HY000: Function 'metaphon' already exists
223
CREATE FUNCTION test.metaphon(a int) RETURNS int
225
ERROR HY000: Function 'metaphon' already exists
226
DROP FUNCTION metaphon;
227
DROP FUNCTION myfunc_double;
228
DROP FUNCTION myfunc_nonexist;
229
ERROR 42000: FUNCTION test.myfunc_nonexist does not exist
230
DROP FUNCTION myfunc_int;
231
DROP FUNCTION sequence;
232
DROP FUNCTION lookup;
233
DROP FUNCTION reverse_lookup;
234
DROP FUNCTION avgcost;
235
select * from mysql.func;
237
CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
241
drop function IS_const;
242
select * from mysql.func;
245
ERROR 42000: FUNCTION test.is_const does not exist
246
CREATE FUNCTION is_const RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
248
is_const(3) as const,
249
is_const(3.14) as const,
250
is_const('fnord') as const,
251
is_const(2+3) as const,
252
is_const(rand()) as 'nc rand()',
253
is_const(sin(3.14)) as const,
254
is_const(upper('test')) as const;
255
const const const const nc rand() const const
256
const const const const not const const const
257
create table bug18761 (n int);
258
insert into bug18761 values (null),(2);
260
is_const(3) as const,
261
is_const(3.14) as const,
262
is_const('fnord') as const,
263
is_const(2+3) as const,
264
is_const(2+n) as 'nc 2+n ',
265
is_const(sin(n)) as 'nc sin(n)',
266
is_const(sin(3.14)) as const,
267
is_const(upper('test')) as const,
268
is_const(rand()) as 'nc rand()',
269
is_const(n) as 'nc n ',
270
is_const(is_const(n)) as 'nc ic?(n)',
271
is_const(is_const('c')) as const
274
const const const const nc 2+n nc sin(n) const const nc rand() nc n nc ic?(n) const
275
const const const const not const not const const const not const not const not const const
276
const const const const not const not const const const not const not const not const const
278
select is_const((1,2,3));
279
ERROR 21000: Operand should contain 1 column(s)
280
drop function if exists is_const;
281
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
282
CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
283
CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
284
create function f1(p1 varchar(255))
289
create function f2(p1 varchar(255))
292
return myfunc_double(p1);
294
create function f3(p1 varchar(255))
297
return myfunc_int(p1);
311
drop function metaphon;
312
drop function myfunc_double;
313
drop function myfunc_int;
314
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
315
create table t1 (a char);
316
set GLOBAL query_cache_size=1355776;
318
select metaphon('MySQL') from t1;
320
show status like "Qcache_hits";
323
show status like "Qcache_queries_in_cache";
325
Qcache_queries_in_cache 0
326
select metaphon('MySQL') from t1;
328
show status like "Qcache_hits";
331
show status like "Qcache_queries_in_cache";
333
Qcache_queries_in_cache 0
335
drop function metaphon;
336
set GLOBAL query_cache_size=default;
337
DROP DATABASE IF EXISTS mysqltest;
338
CREATE DATABASE mysqltest;
340
DROP DATABASE mysqltest;
341
CREATE FUNCTION metaphon RETURNS STRING SONAME "UDF_EXAMPLE_LIB";
342
DROP FUNCTION metaphon;
344
CREATE TABLE const_len_bug (
345
str_const varchar(4000),
346
result1 varchar(4000),
347
result2 varchar(4000)
349
CREATE TRIGGER check_const_len_trigger BEFORE INSERT ON const_len_bug FOR EACH ROW BEGIN
350
set NEW.str_const = 'bar';
351
set NEW.result2 = check_const_len(NEW.str_const);
353
CREATE PROCEDURE check_const_len_sp (IN str_const VARCHAR(4000))
355
DECLARE result VARCHAR(4000);
356
SET result = check_const_len(str_const);
357
insert into const_len_bug values(str_const, result, "");
359
CREATE FUNCTION check_const_len RETURNS string SONAME "UDF_EXAMPLE_LIB";
360
CALL check_const_len_sp("foo");
361
SELECT * from const_len_bug;
362
str_const result1 result2
363
bar Correct length Correct length
364
DROP FUNCTION check_const_len;
365
DROP PROCEDURE check_const_len_sp;
366
DROP TRIGGER check_const_len_trigger;
367
DROP TABLE const_len_bug;
368
CREATE FUNCTION sequence RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
369
CREATE TABLE t1 (a INT);
370
CREATE TABLE t2 (a INT PRIMARY KEY);
371
INSERT INTO t1 VALUES (4),(3),(2),(1);
372
INSERT INTO t2 SELECT * FROM t1;
373
SELECT sequence() AS seq, a FROM t1 ORDER BY seq ASC;
379
SELECT sequence() AS seq, a FROM t1 ORDER BY seq DESC;
385
SELECT * FROM t1 WHERE a = sequence();
387
SELECT * FROM t2 WHERE a = sequence();
393
DROP FUNCTION sequence;
added
removed
mysql-test/r/udf.result
