Bug #104266 Passing null to insert() function causes subsequent calls to fail
Submitted: 9 Jul 2021 18:34 Modified: 9 Jul 2021 19:21
Reporter: David personal Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:8.0.25 OS:Windows
Assigned to: CPU Architecture:x86 (x64)

[9 Jul 2021 18:34] David personal
Description:
My function works correctly when passed a non-null string.  It also works correctly if you pass it null.  However after you have passed it a null, it now always returns null no matter the input.

Dropping/readding the connection allows non-nulls to work properly again.

Note that this ONLY seems to happen with insert().  For example replacing insert() with left() does not exhibit this behavior.

I ran my tests in Workbench, but using "select fBug('abc'), fBug(null), fBug('abc');" with the mysql command line produces the same behavior.  Since each invocation uses a new connection, each run produces "xbc NULL NULL" instead of the expected "xbc NULL xbc".

How to repeat:
Create this function:

CREATE FUNCTION `fBug`(a varchar(10)) RETURNS varchar(10) CHARSET utf8mb4
    NO SQL
    DETERMINISTIC
BEGIN
    set a = insert(a, 1, 1, 'x');
    return a;
END

Then call it like this using a single connection:

select fBug('abc'); -- Correctly returns 'xbc'
select fBug(null); -- Correctly returns null
select fBug('abc'); -- Now returns null instead of 'xbc'

After having typed all that, I realized that the problem is even weirder than I thought.  Try this code:

CREATE DEFINER=`root`@`localhost` FUNCTION `fBug`(a varchar(10)) RETURNS varchar(10) CHARSET utf8mb4
    NO SQL
    DETERMINISTIC
BEGIN
    if (a = 'xyz') then return insert(a, 2, 1, '7'); end if;
    set a = insert(a, 1, 1, '8');
    return a;
END

And invoke it like this:

select fBug('abc'), fBug(null), fBug('abc'), fBug('xyz');

For results I'm getting 8bc (correct), null (correct), null (incorrect) and x7z (surprising).  So while insert() is broken after the first null call, it's *only* broken for that specific line.  This smells like a problem with 'deterministic,' but removing it didn't change the behavior.

Suggested fix:
Using null with insert() should be able to return null without affecting subsequent invocations of the function using different parameters.
[9 Jul 2021 18:41] MySQL Verification Team
Thank you for the bug report. Looks like duplicate/related to?:

https://bugs.mysql.com/bug.php?id=104239
[9 Jul 2021 19:15] David personal
Hard to say if this is a duplicate of 104239.  

That bug is using REGEXP_INSTR, while I'm using insert().  Other than that, the behavior sounds the same.  It would be a huge coincidence for these not to be related.

But it's possible the fix would need to be applied in more than one place.  I'd hate to see REGEXP_INSTR get fixed, only to find (later) that they need to apply the same fix to insert().

104239 also lacks the detail about how it only affects a single line within the function, which may help point to exactly where the problem lies.  Knowing it affects Windows as well as Ubuntu might be useful as well.

Your call: Leave this open so they check both functions?  Or close this and just add a comment to 104239 with the additional info?
[9 Jul 2021 19:21] MySQL Verification Team
Thank you for the feedback. I was able to repeat with current released version 8.0.25 but not anymore with source server 5.7 and 8.0. So please wait for the new release which I don't have ETA for:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.25 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> USE test
Database changed
mysql> delimiter $$
mysql> CREATE FUNCTION `fBug`(a varchar(10)) RETURNS varchar(10) CHARSET utf8mb4
    ->     NO SQL
    ->     DETERMINISTIC
    -> BEGIN
    ->     set a = insert(a, 1, 1, 'x');
    ->     return a;
    -> END$$
Query OK, 0 rows affected (0.07 sec)

mysql> delimiter ;
mysql> select fBug('abc'); -- Correctly returns 'xbc'
+-------------+
| fBug('abc') |
+-------------+
| xbc         |
+-------------+
1 row in set (0.01 sec)

mysql> select fBug(null); -- Correctly returns null
+------------+
| fBug(null) |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql> select fBug('abc'); -- Now returns null instead of 'xbc'
+-------------+
| fBug('abc') |
+-------------+
| NULL        |
+-------------+
1 row in set (0.00 sec)

mysql>
==============================================================================================
d:\dbs>d:\dbs\5.7\bin\mysql -uroot --port=3570 -p  --prompt="mysql 5.7 > " --default-character-set=latin1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35-log Source distribution BUILT: 2021-MAY-26

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.7 > use test
Database changed
mysql 5.7 > delimiter $$
mysql 5.7 > CREATE FUNCTION `fBug`(a varchar(10)) RETURNS varchar(10) CHARSET utf8mb4
    ->     NO SQL
    ->     DETERMINISTIC
    -> BEGIN
    ->     set a = insert(a, 1, 1, 'x');
    ->     return a;
    -> END$$
Query OK, 0 rows affected (0.03 sec)

mysql 5.7 > delimiter ;
mysql 5.7 > select fBug('abc'); -- Correctly returns 'xbc'
+-------------+
| fBug('abc') |
+-------------+
| xbc         |
+-------------+
1 row in set (0.00 sec)

mysql 5.7 > select fBug(null); -- Correctly returns null
+------------+
| fBug(null) |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql 5.7 > select fBug('abc'); -- Now returns null instead of 'xbc'
+-------------+
| fBug('abc') |
+-------------+
| xbc         |
+-------------+
1 row in set (0.00 sec)

mysql 5.7 >
===============================================================================================
d:\dbs>d:\dbs\8.0\bin\mysql -uroot --port=3580 -p --local-infile=1 --prompt="mysql 8.0 > " --default-character-set=latin1
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution BUILT: 2021-MAY-26

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 8.0 > use test
Database changed
mysql 8.0 > delimiter $$
mysql 8.0 > CREATE FUNCTION `fBug`(a varchar(10)) RETURNS varchar(10) CHARSET utf8mb4
    ->     NO SQL
    ->     DETERMINISTIC
    -> BEGIN
    ->     set a = insert(a, 1, 1, 'x');
    ->     return a;
    -> END$$
Query OK, 0 rows affected (0.05 sec)

mysql 8.0 > delimiter ;
mysql 8.0 > select fBug('abc'); -- Correctly returns 'xbc'
+-------------+
| fBug('abc') |
+-------------+
| xbc         |
+-------------+
1 row in set (0.01 sec)

mysql 8.0 > select fBug(null); -- Correctly returns null
+------------+
| fBug(null) |
+------------+
| NULL       |
+------------+
1 row in set (0.00 sec)

mysql 8.0 > select fBug('abc'); -- Now returns null instead of 'xbc'
+-------------+
| fBug('abc') |
+-------------+
| xbc         |
+-------------+
1 row in set (0.00 sec)

mysql 8.0 >