summaryrefslogtreecommitdiff
path: root/mysql-test/main/udf.test
blob: c3a25c6bcced50f7c8bd3f51f4c67adfed6c8b62 (plain)
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
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
--source include/have_udf.inc
#
# To run this tests the "sql/udf_example.c" need to be compiled into
# udf_example.so and LD_LIBRARY_PATH should be setup to point out where
# the library are.
#


--disable_warnings
drop table if exists t1;
--enable_warnings

#
# Create the example functions from udf_example
#

--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO";
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_SO";

--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
--error ER_CANT_FIND_DL_ENTRY
eval CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION udf_sequence RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION lookup RETURNS STRING SONAME "$UDF_EXAMPLE_SO";
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION reverse_lookup
        RETURNS STRING SONAME "$UDF_EXAMPLE_SO";
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE AGGREGATE FUNCTION avgcost
        RETURNS REAL SONAME "$UDF_EXAMPLE_SO";

--error ER_CANT_INITIALIZE_UDF
select myfunc_double();
select myfunc_double(1);
select myfunc_double(78654);
--error 1305
select myfunc_nonexist();
select myfunc_int();
--error ER_CANT_INITIALIZE_UDF
select lookup();
select lookup("127.0.0.1");
--error ER_CANT_INITIALIZE_UDF
select lookup(127,0,0,1);
select lookup("localhost");
--error ER_CANT_INITIALIZE_UDF
select reverse_lookup();

# These two function calls should return "localhost", but it's
# depending on configuration, so just call them and don't log the result
--disable_result_log
select reverse_lookup("127.0.0.1");
select reverse_lookup(127,0,0,1);

# This function call may return different results depending on platform,
# so ignore results (see Bug#52060).
select reverse_lookup("localhost");
--enable_result_log

--error ER_CANT_INITIALIZE_UDF
select avgcost();
--error ER_CANT_INITIALIZE_UDF
select avgcost(100,23.76);
create table t1(sum int, price float(24));
insert into t1 values(100, 50.00), (100, 100.00);
select avgcost(sum, price) from t1;
delete from t1;
insert into t1 values(100, 54.33), (200, 199.99);
select avgcost(sum, price) from t1;
drop table t1;

#------------------------------------------------------------------------
# BUG#17261 Passing a variable from a stored procedure to UDF crashes mysqld
#------------------------------------------------------------------------

select metaphon('hello');

delimiter //;
CREATE PROCEDURE `XXX1`(in testval varchar(10))
begin
select metaphon(testval);
end//
delimiter ;//

call XXX1('hello');
drop procedure xxx1;

delimiter //;
CREATE PROCEDURE `XXX2`()
begin
declare testval varchar(10);
set testval = 'hello';
select metaphon(testval);
end//
delimiter ;//

call XXX2();
drop procedure xxx2;

#
# Bug#19904: UDF: not initialized *is_null per row
#

CREATE TABLE bug19904(n INT, v varchar(10));
INSERT INTO bug19904 VALUES (1,'one'),(2,'two'),(NULL,NULL),(3,'three'),(4,'four');
SELECT myfunc_double(n) AS f FROM bug19904;
SELECT metaphon(v) AS f FROM bug19904;
DROP TABLE bug19904;

#
# Bug#21269: DEFINER-clause is allowed for UDF-functions
#

--error ER_PARSE_ERROR
CREATE DEFINER=CURRENT_USER() FUNCTION should_not_parse
RETURNS STRING SONAME "should_not_parse.so";

--error ER_PARSE_ERROR
CREATE DEFINER=someone@somewhere FUNCTION should_not_parse
RETURNS STRING SONAME "should_not_parse.so";
#
# Bug#19862: Sort with filesort by function evaluates function twice
#
create table t1(f1 int);
insert into t1 values(1),(2);
explain select myfunc_int(f1) from t1 order by 1;
drop table t1;

# 
# Bug #21809: Error 1356 while selecting from view with grouping though 
#              underlying select OK.
#
CREATE TABLE t1(a INT, b INT); INSERT INTO t1 values (1,1),(2,2);

DELIMITER ||;
CREATE FUNCTION fn(a int) RETURNS int DETERMINISTIC
BEGIN
    RETURN a;
END
||
DELIMITER ;||

CREATE VIEW v1 AS SELECT a, fn(MIN(b)) as c FROM t1 GROUP BY a;

SELECT myfunc_int(a AS attr_name) FROM t1;
EXPLAIN EXTENDED SELECT myfunc_int(a AS attr_name) FROM t1;
EXPLAIN EXTENDED SELECT myfunc_int(a) FROM t1;
SELECT a,c FROM v1;

--error ER_WRONG_PARAMETERS_TO_STORED_FCT
SELECT a, fn(MIN(b) xx) as c FROM t1 GROUP BY a;
--error ER_WRONG_PARAMETERS_TO_STORED_FCT
SELECT myfunc_int(fn(MIN(b) xx)) as c FROM t1 GROUP BY a;
--error ER_PARSE_ERROR
SELECT myfunc_int(test.fn(MIN(b) xx)) as c FROM t1 GROUP BY a;

SELECT myfunc_int(fn(MIN(b)) xx) as c FROM t1 GROUP BY a;
SELECT myfunc_int(test.fn(MIN(b)) xx) as c FROM t1 GROUP BY a;

EXPLAIN EXTENDED SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
EXPLAIN EXTENDED SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
EXPLAIN EXTENDED SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
EXPLAIN EXTENDED SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
SELECT myfunc_int(MIN(b) xx) as c FROM t1 GROUP BY a;
SELECT test.fn(MIN(b)) as c FROM t1 GROUP BY a;
SELECT myfunc_int(fn(MIN(b))) as c FROM t1 GROUP BY a;
SELECT myfunc_int(test.fn(MIN(b))) as c FROM t1 GROUP BY a;
DROP VIEW v1;
DROP TABLE t1;
DROP FUNCTION fn;

--echo End of 5.0 tests.

#
# Bug#24736: UDF functions parsed as Stored Functions
#

select myfunc_double(3);
select myfunc_double(3 AS three);
select myfunc_double(abs(3));
select myfunc_double(abs(3) AS named_param);
select abs(myfunc_double(3));
select abs(myfunc_double(3 AS three));

-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select myfunc_double(abs(3 AS wrong));
-- error ER_WRONG_PARAMETERS_TO_NATIVE_FCT
select abs(myfunc_double(3) AS wrong);

#
# BUG#18239: Possible to overload internal functions with stored functions
#

--disable_warnings
drop function if exists pi;
--enable_warnings

--error ER_NATIVE_FCT_NAME_COLLISION
CREATE FUNCTION pi RETURNS STRING SONAME "should_not_parse.so";

# Verify that Stored Functions and UDF are mutually exclusive
DROP FUNCTION IF EXISTS metaphon;

CREATE FUNCTION metaphon(a int) RETURNS int
return 0;

# this currently passes, and eclipse the stored function
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO";

DROP FUNCTION metaphon;
DROP FUNCTION metaphon;

--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO";

--error ER_UDF_EXISTS
CREATE FUNCTION metaphon(a int) RETURNS int
return 0;

--error ER_UDF_EXISTS
CREATE FUNCTION test.metaphon(a int) RETURNS int
return 0;

# End of Bug#18239

#
# Drop the example functions from udf_example
#

DROP FUNCTION metaphon;
DROP FUNCTION myfunc_double;
--error ER_SP_DOES_NOT_EXIST
DROP FUNCTION myfunc_nonexist;
DROP FUNCTION myfunc_int;
DROP FUNCTION udf_sequence;
DROP FUNCTION lookup;
DROP FUNCTION reverse_lookup;
DROP FUNCTION avgcost;

#
# Bug #15439: UDF name case handling forces DELETE FROM mysql.func to remove 
#             the UDF
# 
select * from mysql.func;
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_SO";

select IS_const(3);

drop function IS_const;

select * from mysql.func;

--error 1305
select is_const(3);

#
# Bug#18761: constant expression as UDF parameters not passed in as constant
#
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_SO";

select
  is_const(3) as const,
  is_const(3.14) as const,
  is_const('fnord') as const,
  is_const(2+3) as const,
  is_const(rand()) as 'nc rand()',
  is_const(sin(3.14)) as const,
  is_const(upper('test')) as const;

create table bug18761 (n int);
insert into bug18761 values (null),(2);
select
  is_const(3) as const,
  is_const(3.14) as const,
  is_const('fnord') as const,
  is_const(2+3) as const,
  is_const(2+n) as 'nc  2+n  ',
  is_const(sin(n)) as 'nc sin(n)',
  is_const(sin(3.14)) as const,
  is_const(upper('test')) as const,
  is_const(rand()) as 'nc rand()',
  is_const(n) as 'nc   n   ',
  is_const(is_const(n)) as 'nc ic?(n)',
  is_const(is_const('c')) as const
from
  bug18761;
drop table bug18761;

--error 1241
select is_const((1,2,3));

drop function if exists is_const;

#
# Bug #25382: Passing NULL to an UDF called from stored procedures 
# crashes server
#
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO";

--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_SO";

--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";

delimiter //;
create function f1(p1 varchar(255))
returns varchar(255)
begin
  return metaphon(p1);
end//

create function f2(p1 varchar(255))
returns double
begin
  return myfunc_double(p1);
end//

create function f3(p1 varchar(255))
returns double
begin
  return myfunc_int(p1);
end//

delimiter ;//

select f3(NULL);
select f2(NULL);
select f1(NULL);

drop function f1;
drop function f2;
drop function f3;
drop function metaphon;
drop function myfunc_double;
drop function myfunc_int;

#
# Bug#28318  CREATE FUNCTION (UDF) requires a schema
#

--disable_warnings
DROP DATABASE IF EXISTS mysqltest;
--enable_warnings
CREATE DATABASE mysqltest;
USE mysqltest;
DROP DATABASE mysqltest;
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO";
DROP FUNCTION metaphon;
USE test;

#
# Bug #29804  UDF parameters don't contain correct string length
#

CREATE TABLE const_len_bug (
  str_const varchar(4000),
  result1 varchar(4000),
  result2 varchar(4000)
);

DELIMITER |;
CREATE TRIGGER check_const_len_trigger BEFORE INSERT ON const_len_bug FOR EACH ROW BEGIN
   set NEW.str_const = 'bar';
   set NEW.result2 = check_const_len(NEW.str_const);
END |

CREATE PROCEDURE check_const_len_sp (IN str_const VARCHAR(4000))
BEGIN
DECLARE result VARCHAR(4000);
SET result = check_const_len(str_const);
insert into const_len_bug values(str_const, result, "");
END |
DELIMITER ;|

--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION check_const_len RETURNS string SONAME "$UDF_EXAMPLE_SO";

CALL check_const_len_sp("foo");

SELECT * from const_len_bug;

DROP FUNCTION check_const_len;
DROP PROCEDURE check_const_len_sp;
DROP TRIGGER check_const_len_trigger;
DROP TABLE const_len_bug;


#
# Bug #30355: Incorrect ordering of UDF results
#

--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION udf_sequence RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
CREATE TABLE t1 (a INT);
CREATE TABLE t2 (a INT PRIMARY KEY);
INSERT INTO t1 VALUES (4),(3),(2),(1);
INSERT INTO t2 SELECT * FROM t1;

SELECT udf_sequence() AS seq, a FROM t1 ORDER BY seq ASC;
SELECT udf_sequence() AS seq, a FROM t1 ORDER BY seq DESC;

SELECT * FROM t1 WHERE a = udf_sequence();
SELECT * FROM t2 WHERE a = udf_sequence();

DROP FUNCTION udf_sequence;
DROP TABLE t1,t2;

#
# Bug#31767 (DROP FUNCTION name resolution)
#

--disable_warnings
drop function if exists test.metaphon;
drop function if exists metaphon;
--enable_warnings

--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO";

select metaphon("Hello");

# The UDF should not be dropped
drop function if exists test.metaphon;

select metaphon("Hello");

drop function metaphon;

CREATE FUNCTION test.metaphon(a TEXT) RETURNS TEXT return "This is a SF";

create database db_31767;
use db_31767;

--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO";

use test;

# Uses the UDF
select metaphon("Hello");

# Uses the SF
select test.metaphon("Hello");

# Should drop the UDF, resolving the name the same way select does.
drop function metaphon;

# Should call the SF
select metaphon("Hello");

# Drop the SF
drop function metaphon;

# Change the current database to none.
use db_31767;
drop database db_31767;

drop function if exists no_such_func;

--error ER_SP_DOES_NOT_EXIST
drop function no_such_func;

drop function if exists test.no_such_func;

--error ER_SP_DOES_NOT_EXIST
drop function test.no_such_func;

--error ER_NO_DB_ERROR
drop procedure if exists no_such_proc;

--error ER_NO_DB_ERROR
drop procedure no_such_proc;

use test;


--echo #
--echo # Bug#46259: 5.0.83 -> 5.1.36, query doesn't work
--echo #
CREATE TABLE t1 ( a INT );

INSERT INTO t1 VALUES (1), (2), (3);

SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b` + 1, 1 );
SELECT IF( a = 1, a, a ) AS `b` FROM t1 ORDER BY field( `b`, 1 );

DROP TABLE t1;

--echo End of 5.0 tests.

--echo #
--echo # Bug#33546: Slowdown on re-evaluation of constant expressions.
--echo #
CREATE TABLE t1 (f1 INT);
INSERT INTO t1 VALUES(1),(50);
--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION myfunc_double RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO";
EXPLAIN EXTENDED SELECT 1 FROM t1 WHERE f1=1 + myfunc_double(1);
DROP FUNCTION myfunc_double;
DROP TABLE t1;
--echo #
--echo End of 5.1 tests.

--echo #
--echo # MDEV-10134 Add full support for DEFAULT
--echo #

--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB
eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO";
SELECT METAPHON('Hello');
CREATE TABLE t1 (a VARCHAR(10), b VARCHAR(10) DEFAULT METAPHON(a));
SHOW CREATE TABLE t1;
INSERT INTO t1 (a) VALUES ('Hello');
SELECT * FROM t1;
DROP FUNCTION METAPHON;
# QQ: this should return an error
#INSERT INTO t1 (a) VALUES ('Hello');
#SELECT * FROM t1;
DROP TABLE t1;