Bug #104239 Calling REGEXP_INSTR() with NULL causes all further calls of it to return NULL
Submitted: 7 Jul 2021 16:22 Modified: 10 Jul 2021 3:12
Reporter: Samer Alkhabbaz Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:8.0.25-0ubuntu0.20.04.1 OS:Ubuntu (20.04.2 LTS)
Assigned to: CPU Architecture:Any
Tags: custom function, null, REGEXP, regexp_instr, stored routine

[7 Jul 2021 16:22] Samer Alkhabbaz
Description:
When REGEXP_INSTR() is used in a custom function like this:

CREATE FUNCTION func(arg VARCHAR(255)) RETURNS INT
RETURN REGEXP_INSTR(arg, 'pattern');

and the custom function func is called with NULL once, it will return NULL as expected, but every further call of func will also return NULL, even if the provided arg is a normal string.

How to repeat:
DROP FUNCTION IF EXISTS func;
CREATE FUNCTION func(input_text VARCHAR(255)) RETURNS INT DETERMINISTIC
    RETURN REGEXP_INSTR(input_text, 'pattern');
    -- On the contrary, INSTR(input_text, 'pattern') works fine.

SELECT func('Find this pattern'); -- expected 11, got 11
SELECT func(NULL);                -- expected NULL, got NULL
SELECT func('Find this pattern'); -- expected 11, got NULL
-- Every further call of func will now have NULL as a result, regardless of the input.
[7 Jul 2021 19:20] MySQL Verification Team
Thank you for the bug report.
[10 Jul 2021 3:12] Jon Stephens
Documented fix in the MySQL 8.0.27 changelog as follows:

    When NULL was passed to a user-created function that called
    REGEXP_INSTR(), the first invocation of the function returned
    NULL as expected, but each subsequent invocation of the function
    also returned NULL without regard to the value passed to it.

Closed.