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:34]
David personal
[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 >