User Defined Function in generated column definition in MariaDB?

Question

According the the documentation, MariaDB allows user defined functions to be used in generated column definitions https://mariadb.com/kb/en/generated-columns/

User-defined functions (UDFs) are supported in expressions for generated columns. However, MariaDB can't check whether a UDF is deterministic, so it is up to the user to be sure that they do not use non-deterministic UDFs with VIRTUAL generated columns.

I'm using MariaDB 10.3.20 and I've created a deterministic function but when trying to create the field, it fails. Here is some sample code.

CREATE TABLE `json_virt` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `json_arr` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ;

INSERT INTO json_virt SET json_arr = '["a","b"]' ;
INSERT INTO json_virt SET json_arr = '["c","d"]' ;

DELIMITER //
CREATE OR REPLACE FUNCTION `test`.`json_implode`(`data` TEXT, `sep` TEXT) 
RETURNS text 
CHARSET utf8mb4 
COLLATE utf8mb4_unicode_ci
DETERMINISTIC
SQL SECURITY INVOKER
BEGIN

    DECLARE i INT UNSIGNED DEFAULT 0 ;
    DECLARE v_count INT UNSIGNED DEFAULT JSON_LENGTH(data) ;
    DECLARE v_current_item BLOB DEFAULT NULL ;
    DECLARE v_current_path BLOB DEFAULT NULL ;
    
    DECLARE sep_length INT UNSIGNED DEFAULT CHAR_LENGTH(sep) ;
    
    DECLARE str TEXT DEFAULT '' ;
    
    WHILE i < v_count DO
        SET v_current_path =  CONCAT('$[', i, ']') ;
        
        SET v_current_item = JSON_EXTRACT(data, v_current_path) ;
        SET v_current_item = JSON_UNQUOTE(v_current_item) ;
        
        SET str = CONCAT(str, sep, v_current_item) ;
        
        SET i := i + 1;
    END WHILE;
    
    SET str = SUBSTRING(str, sep_length+1) ;
    
    RETURN str ;
    
END //
DELIMITER ;

SELECT 
json_virt.*,
json_implode(json_virt.json_arr, ', ') AS json_imp
FROM json_virt
;

ALTER TABLE `json_virt` ADD `json_str` TEXT AS (json_implodex(json_arr, ', ')) VIRTUAL ;

ALTER TABLE `json_virt` ADD `json_str1` TEXT AS json_implode('["x","y"]', ', ') VIRTUAL ;

The json_implode() function works nicely as you can see by the SELECT statement, but when trying to do the last two ALTER TABLE statements, both fail.

I'm interested in this from a few different angles:

Why doesn't this work?
What is an example of a UDF that will work for a generated column definition?
Is it possible to use multi-line or more complex code in a generated column definition?
Is there a better way accomplish what I'm trying to do (Take a field that is a JSON array and implode it so each item is separated by a comma as a string)?

You have a typo in the first one: `json_implodex`. – Barmar Oct 01 '20 at 19:00 — Barmar, Oct 01 '20 at 19:00
`json_implode` is a stored function, not a UDF. – Barmar Oct 01 '20 at 19:01 — Barmar, Oct 01 '20 at 19:01

score 3 · Accepted Answer · answered Oct 01 '20 at 23:55

I see a lot of developers using "UDF" when they mean what MySQL/MariaDB calls a Stored Function. That is, a routine written in a language based on ANSI SQL and deployed dynamically on a running MySQL Server.

Whereas the term UDF is used by MySQL/MariaDB for a completely different feature.

https://mariadb.com/kb/en/create-function-udf/ says:

A user-defined function (UDF) is a way to extend MariaDB with a new function that works like a native (built-in) MariaDB function such as ABS() or CONCAT().

UDFs need to be written in C, C++ or another language that uses C calling conventions, MariaDB needs to have been dynamically compiled, and your operating system must support dynamic loading.

Note that Microsoft SQL Server uses UDF for something more like MySQL/MariaDB's Stored Function. This might be the source of the confusion.

In my case I assumed a "User Defined Function" is a function that the user defines. Silly me! I should have remembered this is 2020. Thanks. — Ryan, Oct 03 '20 at 20:52

User Defined Function in generated column definition in MariaDB?

1 Answers1