diff options
Diffstat (limited to 'mysql-test/main/udf.test')
| -rw-r--r-- | mysql-test/main/udf.test | 650 |
1 files changed, 650 insertions, 0 deletions
diff --git a/mysql-test/main/udf.test b/mysql-test/main/udf.test new file mode 100644 index 00000000..e9823a31 --- /dev/null +++ b/mysql-test/main/udf.test @@ -0,0 +1,650 @@ +--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") rlike '^127\.\\d+\.\\d+.\\d+$'; +--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 +# + +#enable view prtotocol after fix MDEV-28677 +--disable_view_protocol +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); +--enable_view_protocol + +# +# 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 +# + +#enable view protocol after fix MDEV-28678 +--disable_view_protocol +--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; +--enable_view_protocol + +# +# 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; + +--echo # +--echo # MDEV-15424: Unreasonal SQL Error (1356) on select from view +--echo # +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_SO"; +create table t1(a int , b int); +insert into t1 values(100, 54), (200, 199); +create view v1 as select myfunc_int(max(a) over (order by b) , b) from t1; +select * from v1; +drop view v1; +drop function myfunc_int; +drop table t1; + +--echo # +--echo # MDEV-23327: Can't uninstall UDF if the implementation library +--echo # file doesn't exist +--echo # + +# emulate brocken so/ddl udf +insert into mysql.func values ("unexisting_udf", 0, "soname", "function"); + +# check that we have the function "installed" +select * from mysql.func WHERE name='unexisting_udf'; + +# make attempt to drop the function +DROP FUNCTION unexisting_udf; + +# check that we have the plugin uninstalled +select * from mysql.plugin WHERE name='unexisting_udf'; + +--error ER_SP_DOES_NOT_EXIST +DROP FUNCTION unexisting_udf; + +--echo # +--echo # Bug #31674599: THE UDF_INIT() FUNCTION CAUSE SERVER CRASH +--echo # +call mtr.add_suppression('Invalid row in mysql.func table'); +insert mysql.func () values (); +source include/restart_mysqld.inc; +delete from mysql.func where name = ''; + +--echo # +--echo # End of 10.2 tests +--echo # + +--echo # +--echo # MDEV-15073: Generic UDAF parser code in server for window functions +--echo # + +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE AGGREGATE FUNCTION avgcost + RETURNS REAL SONAME "$UDF_EXAMPLE_SO"; +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE AGGREGATE FUNCTION avg2 + RETURNS REAL SONAME "$UDF_EXAMPLE_SO"; +--replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB +eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_SO"; + +create table t1(pk int primary key, + a int, + sum int, + price float(24)); +insert into t1 values + (1, 1, 100, 50.00), + (2, 1, 100, 100.00), + (3, 1, 100, 50.00), + (4, 1, 100, 50.00), + (5, 1, 100, 50.00), + (6, 1, 100, NULL), + (7, 1, NULL, NULL), + (8, 2, 2, 2), + (9, 2, 4, 4); + +--sorted_result +select pk, a, sum, price, avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +from t1; +--sorted_result +select pk, a, sum, price, avgcost(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) +from t1; + +--sorted_result +select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) +from t1; +--sorted_result +select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) +from t1; +--error ER_PARSE_ERROR +select pk, a, sum, price, tttttttt(sprice,sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) +from t1; +--error ER_PARSE_ERROR +select pk, a, sum, price, myfunc_double(sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) +from t1; +--error ER_PARSE_ERROR +select pk, a, sum, price, round(sprice,sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) +from t1; +--error ER_PARSE_ERROR +select pk, a, sum, price, myfunc_double(sum) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) +from t1; + +set @save_sql_mode = @@sql_mode; +set sql_mode="oracle"; +--sorted_result +select pk, a, sum, price, avg2(sum, price) over (partition by a order by pk ROWS BETWEEN 1 PRECEDING AND 0 FOLLOWING) +from t1; +set sql_mode= @save_sql_mode; + +drop table t1; +DROP FUNCTION avgcost; +DROP FUNCTION avg2; +DROP FUNCTION myfunc_double; + +--echo # End of 10.4 tests |