Debian Codegraph @

mysql-test/suite/rpl/r/rpl_udf.result

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