1include/master-slave.inc 2[connection master] 3connection master; 4drop table if exists t1; 5"*** Test 1) Test UDFs via loadable libraries *** 6CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB"; 7affected rows: 0 8CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; 9affected rows: 0 10CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB"; 11ERROR HY000: Can't find symbol 'myfunc_nonexist' in library 12SELECT * FROM mysql.func ORDER BY name; 13name ret dl type 14myfunc_double 1 UDF_LIB function 15myfunc_int 2 UDF_LIB function 16affected rows: 2 17connection slave; 18SELECT * FROM mysql.func ORDER BY name; 19name ret dl type 20myfunc_double 1 UDF_LIB function 21myfunc_int 2 UDF_LIB function 22affected rows: 2 23connection master; 24CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM; 25affected rows: 0 26INSERT INTO t1 VALUES(myfunc_int(100), myfunc_double(50.00)); 27affected rows: 1 28INSERT INTO t1 VALUES(myfunc_int(10), myfunc_double(5.00)); 29affected rows: 1 30INSERT INTO t1 VALUES(myfunc_int(200), myfunc_double(25.00)); 31affected rows: 1 32INSERT INTO t1 VALUES(myfunc_int(1), myfunc_double(500.00)); 33affected rows: 1 34SELECT * FROM t1 ORDER BY sum; 35sum price 361 48.5 3710 48.75 38100 48.6 39200 49 40affected rows: 4 41connection slave; 42SELECT * FROM t1 ORDER BY sum; 43sum price 441 48.5 4510 48.75 46100 48.6 47200 49 48affected rows: 4 49SELECT myfunc_int(25); 50myfunc_int(25) 5125 52affected rows: 1 53SELECT myfunc_double(75.00); 54myfunc_double(75.00) 5550.00 56affected rows: 1 57connection master; 58DROP FUNCTION myfunc_double; 59affected rows: 0 60DROP FUNCTION myfunc_int; 61affected rows: 0 62SELECT * FROM mysql.func ORDER BY name; 63name ret dl type 64affected rows: 0 65connection slave; 66SELECT * FROM mysql.func ORDER BY name; 67name ret dl type 68affected rows: 0 69connection master; 70DROP TABLE t1; 71affected rows: 0 72"*** Test 2) Test UDFs with SQL body *** 73CREATE FUNCTION myfuncsql_int(i INT) RETURNS INTEGER DETERMINISTIC RETURN i; 74affected rows: 0 75CREATE FUNCTION myfuncsql_double(d DOUBLE) RETURNS INTEGER DETERMINISTIC RETURN d * 2.00; 76affected rows: 0 77SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 78db name type param_list body comment 79test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00 80test myfuncsql_int FUNCTION i INT RETURN i 81affected rows: 2 82connection slave; 83SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 84db name type param_list body comment 85test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00 86test myfuncsql_int FUNCTION i INT RETURN i 87affected rows: 2 88connection master; 89CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM; 90affected rows: 0 91INSERT INTO t1 VALUES(myfuncsql_int(100), myfuncsql_double(50.00)); 92affected rows: 1 93INSERT INTO t1 VALUES(myfuncsql_int(10), myfuncsql_double(5.00)); 94affected rows: 1 95INSERT INTO t1 VALUES(myfuncsql_int(200), myfuncsql_double(25.00)); 96affected rows: 1 97INSERT INTO t1 VALUES(myfuncsql_int(1), myfuncsql_double(500.00)); 98affected rows: 1 99SELECT * FROM t1 ORDER BY sum; 100sum price 1011 1000 10210 10 103100 100 104200 50 105affected rows: 4 106connection slave; 107SELECT * FROM t1 ORDER BY sum; 108sum price 1091 1000 11010 10 111100 100 112200 50 113affected rows: 4 114connection master; 115ALTER FUNCTION myfuncsql_int COMMENT "This was altered."; 116affected rows: 0 117ALTER FUNCTION myfuncsql_double COMMENT "This was altered."; 118affected rows: 0 119SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 120db name type param_list body comment 121test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00 This was altered. 122test myfuncsql_int FUNCTION i INT RETURN i This was altered. 123affected rows: 2 124connection slave; 125SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 126db name type param_list body comment 127test myfuncsql_double FUNCTION d DOUBLE RETURN d * 2.00 This was altered. 128test myfuncsql_int FUNCTION i INT RETURN i This was altered. 129affected rows: 2 130SELECT myfuncsql_int(25); 131myfuncsql_int(25) 13225 133affected rows: 1 134SELECT myfuncsql_double(75.00); 135myfuncsql_double(75.00) 136150 137affected rows: 1 138connection master; 139DROP FUNCTION myfuncsql_double; 140affected rows: 0 141DROP FUNCTION myfuncsql_int; 142affected rows: 0 143SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 144db name type param_list body comment 145affected rows: 0 146connection slave; 147SELECT db, name, type, param_list, body, comment FROM mysql.proc WHERE db = 'test' AND name LIKE 'myfuncsql%' ORDER BY name; 148db name type param_list body comment 149affected rows: 0 150connection master; 151DROP TABLE t1; 152affected rows: 0 153include/rpl_end.inc 154