diff options
Diffstat (limited to 'mysql-test/suite/pbxt/t/udf.test')
| -rw-r--r-- | mysql-test/suite/pbxt/t/udf.test | 444 |
1 files changed, 0 insertions, 444 deletions
diff --git a/mysql-test/suite/pbxt/t/udf.test b/mysql-test/suite/pbxt/t/udf.test deleted file mode 100644 index 97c2cf8acb4..00000000000 --- a/mysql-test/suite/pbxt/t/udf.test +++ /dev/null @@ -1,444 +0,0 @@ ---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 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 functions 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); ---enable_result_log - -select reverse_lookup("localhost"); ---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 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 #28921: Queries containing UDF functions are cached -# - ---replace_result $UDF_EXAMPLE_SO UDF_EXAMPLE_LIB -eval CREATE FUNCTION metaphon RETURNS STRING SONAME "$UDF_EXAMPLE_SO"; -create table t1 (a char); - -set GLOBAL query_cache_size=1355776; -reset query cache; -flush status; - -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_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 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 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; - - ---disable_query_log -drop database pbxt; ---enable_query_log ---echo End of 5.0 tests. |