This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-15. For the latest, interactive version please visit https://mariadb.com/kb/.

mariadb user defined aggregate function

I am using mariadb 10.3.9, and have created a user defined aggregate function (UDAF) and placed in a common_schema. This schema contains my utility functions to be used by other schema/databases on the same server.

The issue is that when calling the UDAF while using any other schema, it always return NULL!

The following is to demonstrate the issue:

CREATE SCHEMA IF NOT EXISTS common_schema;
DELIMITER $$
DROP FUNCTION IF EXISTS common_schema.add_ints $$
CREATE FUNCTION common_schema.add_ints(int_1 INT, int_2 INT) RETURNS INT NO SQL 
BEGIN 
    RETURN int_1 + int_2; 
END $$ 
DROP FUNCTION IF EXISTS common_schema.sum_ints $$
CREATE AGGREGATE FUNCTION common_schema.sum_ints(int_val INT) RETURNS INT 
BEGIN 
    DECLARE result INT DEFAULT 0; 
    DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN result; 
    LOOP FETCH GROUP NEXT ROW; 
        SET result = common_schema.add_ints(result, int_val); 
    END LOOP; 
END $$

DELIMITER ; 
 

Now, calling it this way, returns the result as expected:

USE common_schema;
SELECT common_schema.sum_ints(seq)
FROM (SELECT 1 seq UNION ALL SELECT 2) t; 
-- result: 3
 

Calling it using any other schema, it returns NULL:

USE other_schema;
SELECT common_schema.sum_ints(seq) 
FROM (SELECT 1 seq UNION ALL SELECT 2) t; 
-- result: null
 

Am I missing something here? Is there any configuration that is missing?

Appreciate your help.

Answer Answered by Ian Gilfillan in this comment.

Thanks, this is a bug, and has been reported at MDEV-18100.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.