--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_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_LIB"; --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB --error ER_CANT_FIND_DL_ENTRY eval CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION sequence RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION lookup RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION reverse_lookup RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE AGGREGATE FUNCTION avgcost RETURNS REAL SONAME "$UDF_EXAMPLE_LIB"; --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_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; DROP FUNCTION metaphon; DROP FUNCTION metaphon; --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; --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 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_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; 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_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION is_const RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; 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_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION myfunc_double RETURNS REAL SONAME "$UDF_EXAMPLE_LIB"; --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; 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 #28921: Queries containing UDF functions are cached # --replace_result $UDF_EXAMPLE_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; create table t1 (a char); set GLOBAL query_cache_size=1355776; reset query cache; select metaphon('MySQL') from t1; show status like "Qcache_hits"; show status like "Qcache_queries_in_cache"; select metaphon('MySQL') from t1; show status like "Qcache_hits"; show status like "Qcache_queries_in_cache"; drop table t1; drop function metaphon; set GLOBAL query_cache_size=default; # # 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_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_LIB"; 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_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION check_const_len RETURNS string SONAME "$UDF_EXAMPLE_LIB"; 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_LIB UDF_EXAMPLE_LIB eval CREATE FUNCTION sequence RETURNS INTEGER SONAME "$UDF_EXAMPLE_LIB"; 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 sequence() AS seq, a FROM t1 ORDER BY seq ASC; SELECT sequence() AS seq, a FROM t1 ORDER BY seq DESC; SELECT * FROM t1 WHERE a = sequence(); SELECT * FROM t2 WHERE a = sequence(); DROP FUNCTION sequence; DROP TABLE t1,t2; --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.