Description:
Let's say you have a plugin installed like udf_example that comes with MySQL:
CREATE AGGREGATE FUNCTION my_median RETURNS REAL SONAME 'udf_example.so';
Or you could use a third party plugin like from here https://github.com/quantum13/mysql-median-udf.
If you run an SQL that uses the aggregate function on a single table, it will run no problem, no matter how many times you run it. For example, I have a table called user_values which has these columns:
id:INT
user_id:INT
value:INT
The table has two rows:
(1,1,1)
(2,2,10)
I have a users table as well, obviously:
id:INT
name:VARCHAR(255)
It has these two rows:
(1,'Jason')
(2,'Brian')
Run this SQL:
select my_median(value) from user_values => 2 (I don't know why the answer is wrong with my_median from udf_example.so. Maybe I am not calling it right. The point is I am trying to demonstrate this error with a library from MySQL so the library itself cannot be blamed. If I use udf_median.so, I get 5.50).
You can run that query again and again and get the same result.
But, do a table join like this:
select users.id, my_median(value)
from users,user_values
where users.id = user_values.user_id
group by users.id
Run it once and it will get back the result set. BUT RUN IT A SECOND TIME: Error Code: 1305. FUNCTION my_median does not exist
The library still appears in mysql.func:
select * from mysql.func => {name: 'my_median', ret: 1, dl: 'udf_example.so', type: 'aggregate'}
If I delete the row from mysql.func, install the aggregate function again, I can run that query once again but not a second time.
This does not happen in 8.0.21. I skipped 8.0.22 and went straight to 8.0.23 so I don't know if it appeared in 8.0.22 or 8.0.23.
How to repeat:
Create a database:
create database my_database.
Create a user table:
create table users ( id INT, name VARCHAR(255));
Populate it:
insert into users (id, name) values (1, 'Jason');
insert into users (id, name) values (1, 'Brian');
Create a table with data:
create table user_values (id INT, user_id INT, value INT);
Populate it
insert into user_values (id, user_id, value) values (1,1,1);
insert into user_values (id, user_id, value) values (2,2,10);
Install an aggregate UDF. You can use MySQL's udf_example.so:
CREATE AGGREGATE FUNCTION my_median RETURNS REAL SONAME 'udf_example.so';
Prove to yourself it works:
select my_median(value) from user_values; <= execute again and again and again till you're bored.
Prove to yourself it is fine with aggregation on a single table:
select user_id, my_median(value) from user_values group by user_id <= execute again and again
Now, join the two tables and group by:
select users.id, my_median(value)
from users,user_values
where users.id = user_values.user_id
group by users.id
Run it once and get no error.
Run it a second time and get Error Code: 1305. FUNCTION my_median does not exist
