Debian Codegraph @

mysql-test/suite/service_udf_registration/t/udf_reg_unreg_single_func.test

raw
1 # Test that hows register/unregister of single and multiple UDFsi with different components.
2
3 # Register 3 UDFs.
4 INSTALL COMPONENT "file://component_udf_reg_only_3_func";
5
6 # Try to register an already registered UDF (myfunc_int).
7 --error ER_COMPONENTS_LOAD_CANT_INITIALIZE
8 INSTALL COMPONENT "file://component_udf_reg_int_func";
9
10 # Check performance_schmea
11 SELECT * FROM performance_schema.user_defined_functions
12 WHERE (udf_name LIKE 'myfunc%') or (udf_name LIKE 'avgcost%')
13 ORDER BY udf_name;
14
15 # Verify, that UDFs are working.
16 --error ER_CANT_INITIALIZE_UDF
17 select myfunc_double();
18 select myfunc_double(1);
19 select myfunc_double(78654);
20
21 select myfunc_int();
22 select myfunc_int(5);
23 select myfunc_int('MySQL V8.0');
24
25 # Verify that myfunc_int is visible (once).
26 SELECT udf_name,udf_usage_count FROM performance_schema.user_defined_functions
27 WHERE udf_name= 'myfunc_int'
28 ORDER BY udf_name;
29
30 # Verify, that UDFs are working.
31 CREATE TABLE t1(a INT, b INT);
32 INSERT INTO t1 values (1,1),(2,2);
33 SELECT myfunc_int(a AS attr_name) FROM t1;
34 drop table t1;
35
36 create table t1(sum int, price float(24));
37 insert into t1 values(100, 50.00), (100, 100.00);
38 --error ER_CANT_INITIALIZE_UDF
39 select avgcost(price) from t1;
40 select avgcost(sum, price) from t1;
41 delete from t1;
42 insert into t1 values(100, 54.33), (200, 199.99);
43 select avgcost(sum, price) from t1;
44 drop table t1;
45
46 # Unregister all3 UDFs.
47 --echo # remove the plugin
48 INSTALL COMPONENT "file://component_udf_unreg_3_func";
49
50 # Verify, that UDFs are not more working.
51 --error ER_SP_DOES_NOT_EXIST
52 select myfunc_double(1);
53 --error ER_SP_DOES_NOT_EXIST
54 select myfunc_int(5);
55
56 # Register myfunc_int.
57 INSTALL COMPONENT "file://component_udf_reg_int_func";
58
59 # Check performance_schema is showing only 1 UDF.
60 SELECT * FROM performance_schema.user_defined_functions
61 WHERE (udf_name LIKE 'myfunc%') or (udf_name LIKE 'avgcost%')
62 ORDER BY udf_name;
63
64 # Check components.
65 SELECT * FROM mysql.func;
66 SELECT component_urn FROM mysql.component;
67
68 # Verfiy, that only the registered UDF works.
69 --error ER_SP_DOES_NOT_EXIST
70 select myfunc_double(1);
71 select myfunc_int(5);
72
73 # Register myfunc_double.
74 INSTALL COMPONENT "file://component_udf_reg_real_func";
75
76 # Check performance_schema is showing 2 UDFs.
77 SELECT * FROM performance_schema.user_defined_functions
78 WHERE (udf_name LIKE 'myfunc%') or (udf_name LIKE 'avgcost%')
79 ORDER BY udf_name;
80
81 # Check components.
82 SELECT * FROM mysql.func;
83
84 # Verfiy, that the registered UDFs work.
85 select myfunc_double(1);
86 select myfunc_int(5);
87
88 # Unregister myfunc_double.
89 UNINSTALL COMPONENT "file://component_udf_reg_real_func";
90
91 # Check performance_schema is showing only 1 UDF.
92 SELECT * FROM performance_schema.user_defined_functions
93 WHERE (udf_name LIKE 'myfunc%') or (udf_name LIKE 'avgcost%')
94 ORDER BY udf_name;
95 # Check components.
96 SELECT component_urn FROM mysql.component;
97
98 # Unregister 3 UDFs with init.
99 UNINSTALL COMPONENT "file://component_udf_reg_only_3_func";
100
101 # Check performance_schema is showing only 3 UDFs.
102 SELECT * FROM performance_schema.user_defined_functions
103 WHERE (udf_name LIKE 'myfunc%') or (udf_name LIKE 'avgcost%')
104 ORDER BY udf_name;
105
106 # Check compoinents.
107 SELECT component_urn FROM mysql.component;
108
109 # Unregister 3 UDFs (init).
110 UNINSTALL COMPONENT "file://component_udf_unreg_3_func";
111
112 # No UDF shown by performance_schema.
113 SELECT * FROM performance_schema.user_defined_functions
114 WHERE (udf_name LIKE 'myfunc%') or (udf_name LIKE 'avgcost%')
115 ORDER BY udf_name;
116 # Check components.
117 SELECT component_urn FROM mysql.component;
118
119 # Unregister myfunc_int wich is already unregistered (error log).
120 UNINSTALL COMPONENT "file://component_udf_reg_int_func";
121 # check performance_schema and compoents, both empty.
122 SELECT * FROM performance_schema.user_defined_functions
123 WHERE (udf_name LIKE 'myfunc%') or (udf_name LIKE 'avgcost%')
124 ORDER BY udf_name;
125 SELECT component_urn FROM mysql.component;