Debian Codegraph @

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

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 drop table if exists t1;
7 "*** Test 1) Test UDFs via loadable libraries ***
8 "Running on the master"
9 CREATE FUNCTION myfunc_double RETURNS REAL SONAME "UDF_EXAMPLE_LIB";
10 affected rows: 0
11 CREATE FUNCTION myfunc_int RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
12 affected rows: 0
13 CREATE FUNCTION myfunc_nonexist RETURNS INTEGER SONAME "UDF_EXAMPLE_LIB";
14 ERROR HY000: Can't find symbol 'myfunc_nonexist' in library
15 SELECT * FROM mysql.func ORDER BY name;
16 name ret dl type
17 myfunc_double 1 UDF_LIB function
18 myfunc_int 2 UDF_LIB function
19 affected rows: 2
20 "Running on the slave"
21 SELECT * FROM mysql.func ORDER BY name;
22 name ret dl type
23 myfunc_double 1 UDF_LIB function
24 myfunc_int 2 UDF_LIB function
25 affected rows: 2
26 "Running on the master"
27 CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM;
28 affected rows: 0
29 INSERT INTO t1 VALUES(myfunc_int(100), myfunc_double(50.00));
30 affected rows: 1
31 INSERT INTO t1 VALUES(myfunc_int(10), myfunc_double(5.00));
32 affected rows: 1
33 INSERT INTO t1 VALUES(myfunc_int(200), myfunc_double(25.00));
34 affected rows: 1
35 INSERT INTO t1 VALUES(myfunc_int(1), myfunc_double(500.00));
36 affected rows: 1
37 SELECT * FROM t1 ORDER BY sum;
38 sum price
39 1 48.5
40 10 48.75
41 100 48.6
42 200 49
43 affected rows: 4
44 "Running on the slave"
45 SELECT * FROM t1 ORDER BY sum;
46 sum price
47 1 48.5
48 10 48.75
49 100 48.6
50 200 49
51 affected rows: 4
52 SELECT myfunc_int(25);
53 myfunc_int(25)
54 25
55 affected rows: 1
56 SELECT myfunc_double(75.00);
57 myfunc_double(75.00)
58 50.00
59 affected rows: 1
60 "Running on the master"
61 DROP FUNCTION myfunc_double;
62 affected rows: 0
63 DROP FUNCTION myfunc_int;
64 affected rows: 0
65 SELECT * FROM mysql.func ORDER BY name;
66 name ret dl type
67 affected rows: 0
68 "Running on the slave"
69 SELECT * FROM mysql.func ORDER BY name;
70 name ret dl type
71 affected rows: 0
72 "Running on the master"
73 DROP TABLE t1;
74 affected rows: 0
75 "*** Test 2) Test UDFs with SQL body ***
76 "Running on the master"
77 CREATE FUNCTION myfuncsql_int(i INT) RETURNS INTEGER DETERMINISTIC RETURN i;
78 affected rows: 0
79 CREATE FUNCTION myfuncsql_double(d DOUBLE) RETURNS INTEGER DETERMINISTIC RETURN d * 2.00;
80 affected rows: 0
81 SELECT routine_schema, routine_name, routine_type, routine_definition
82 FROM INFORMATION_SCHEMA.ROUTINES
83 WHERE routine_schema = 'test' AND routine_name LIKE 'myfuncsql%' ORDER BY routine_name;
84 ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE ROUTINE_DEFINITION
85 test myfuncsql_double FUNCTION RETURN d * 2.00
86 test myfuncsql_int FUNCTION RETURN i
87 affected rows: 2
88 "Running on the slave"
89 SELECT routine_schema, routine_name, routine_type, routine_definition
90 FROM INFORMATION_SCHEMA.ROUTINES
91 WHERE routine_schema = 'test' AND routine_name LIKE 'myfuncsql%' ORDER BY routine_name;
92 ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE ROUTINE_DEFINITION
93 test myfuncsql_double FUNCTION RETURN d * 2.00
94 test myfuncsql_int FUNCTION RETURN i
95 affected rows: 2
96 "Running on the master"
97 CREATE TABLE t1(sum INT, price FLOAT(24)) ENGINE=MyISAM;
98 affected rows: 0
99 INSERT INTO t1 VALUES(myfuncsql_int(100), myfuncsql_double(50.00));
100 affected rows: 1
101 INSERT INTO t1 VALUES(myfuncsql_int(10), myfuncsql_double(5.00));
102 affected rows: 1
103 INSERT INTO t1 VALUES(myfuncsql_int(200), myfuncsql_double(25.00));
104 affected rows: 1
105 INSERT INTO t1 VALUES(myfuncsql_int(1), myfuncsql_double(500.00));
106 affected rows: 1
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 "Running on the slave"
115 SELECT * FROM t1 ORDER BY sum;
116 sum price
117 1 1000
118 10 10
119 100 100
120 200 50
121 affected rows: 4
122 "Running on the master"
123 ALTER FUNCTION myfuncsql_int COMMENT "This was altered.";
124 affected rows: 0
125 ALTER FUNCTION myfuncsql_double COMMENT "This was altered.";
126 affected rows: 0
127 SELECT routine_schema, routine_name, routine_type, routine_definition
128 FROM INFORMATION_SCHEMA.ROUTINES
129 WHERE routine_schema = 'test' AND routine_name LIKE 'myfuncsql%' ORDER BY routine_name;
130 ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE ROUTINE_DEFINITION
131 test myfuncsql_double FUNCTION RETURN d * 2.00
132 test myfuncsql_int FUNCTION RETURN i
133 affected rows: 2
134 "Running on the slave"
135 SELECT routine_schema, routine_name, routine_type, routine_definition
136 FROM INFORMATION_SCHEMA.ROUTINES
137 WHERE routine_schema = 'test' AND routine_name LIKE 'myfuncsql%' ORDER BY routine_name;
138 ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE ROUTINE_DEFINITION
139 test myfuncsql_double FUNCTION RETURN d * 2.00
140 test myfuncsql_int FUNCTION RETURN i
141 affected rows: 2
142 SELECT myfuncsql_int(25);
143 myfuncsql_int(25)
144 25
145 affected rows: 1
146 SELECT myfuncsql_double(75.00);
147 myfuncsql_double(75.00)
148 150
149 affected rows: 1
150 "Running on the master"
151 DROP FUNCTION myfuncsql_double;
152 affected rows: 0
153 DROP FUNCTION myfuncsql_int;
154 affected rows: 0
155 SELECT routine_schema, routine_name, routine_type, routine_definition
156 FROM INFORMATION_SCHEMA.ROUTINES
157 WHERE routine_schema = 'test' AND routine_name LIKE 'myfuncsql%' ORDER BY routine_name;
158 ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE ROUTINE_DEFINITION
159 affected rows: 0
160 "Running on the slave"
161 SELECT routine_schema, routine_name, routine_type, routine_definition
162 FROM INFORMATION_SCHEMA.ROUTINES
163 WHERE routine_schema = 'test' AND routine_name LIKE 'myfuncsql%' ORDER BY routine_name;
164 ROUTINE_SCHEMA ROUTINE_NAME ROUTINE_TYPE ROUTINE_DEFINITION
165 affected rows: 0
166 "Running on the master"
167 DROP TABLE t1;
168 affected rows: 0
169 include/rpl_end.inc