| 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: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 8.0.25 | OS: | Windows |
| Assigned to: | CPU Architecture: | x86 (x64) | |
[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 >
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.