MySQL Bugs: #104239: Calling REGEXP_INSTR() with NULL causes all further calls of it to return NULL

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

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.

Thank you for the bug report.

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.