[SOLVED] Can't create a functional index on top of my custom function

Can't create a functional index on top of my custom function

I'm playing with functional index in MySQL 8, and i'm using the *employees* database.

So I created this function:

DELIMITER $$
CREATE FUNCTION salary_range2(salary DECIMAL(10,2))
    RETURNS INT
    DETERMINISTIC
    READS SQL DATA
BEGIN
    RETURN CASE
               WHEN salary < 40000 THEN 1
               WHEN salary < 80000 THEN 2
               WHEN salary < 120000 THEN 3
               ELSE 4
        END;
END$$
DELIMITER ;

And this statement fails:

CREATE INDEX idx_salary_range ON salaries ((salary_range2(salary)));

With error: [HY000][3758] Expression of functional index 'idx_salary_range' contains a disallowed function.

My doubt was if the function was deterministic or not, but it seems this is not the problem, because the deterministc signature is not checked by MySQL. And by the way it seems to me deterministc.

Am I missing something ? Or any workaround to this problem, like creating temporary column, views, or table ? But i was interested in experimenting some funcional index out of hello world tutorials.

I tried to create a functional index with my own custom function and it fails with the error [HY000][3758] Expression of functional index 'idx_salary_range' contains a disallowed function.

Solution

As mysql documentation on create index says:

Functional key parts inherit all restrictions that apply to generated columns. Examples:

Only functions permitted for generated columns are permitted for functional key parts.

Subqueries, parameters, variables, stored functions, and loadable functions are not permitted.

You simply cannot use your function in a functional index. However, you can use the expression in the body of the function directly in the functional index.