Bug #102649 Calling a UDF aggregate function corrupts plugin when a join is involved
Submitted: 18 Feb 17:21 Modified: 19 Feb 6:52
Reporter: Jason Perrone Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: User-defined functions ( UDF ) Severity:S2 (Serious)
Version:8.0.23 OS:Ubuntu (20.04.02 LTS)
Assigned to: CPU Architecture:x86
Tags: error, udf

[18 Feb 17:21] Jason Perrone
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
[18 Feb 17:28] Jason Perrone
I have a mistake above where I used the same id on the two inserts into the users table. That's just a typo. Use id = 2 on the second user insert.
[19 Feb 6:52] MySQL Verification Team
Hello Jason,

Thank you for the report and test case.

regards,
Umesh