Problem using CAST() in stored procedures and functions

[Robert Hairgrove]

Using MySQL Community Server 5.7.33 on Linux Ubuntu 18.04.

If I issue the SQL statement "SELECT CAST('1,2,3' AS SIGNED);" it gives me
the expected result (1) with the warning #1292 "Truncated incorrect INTEGER value: '1,2,3'".

-- mysql> SELECT CAST('1,2,3' AS SIGNED);
-- +-------------------------+
-- | CAST('1,2,3' AS SIGNED) |
-- +-------------------------+
-- | 1 |
-- +-------------------------+
-- 1 row in set, 1 warning (0.00 sec)
--
-- mysql> SHOW WARNINGS;
-- +---------+------+--------------------------------------------+
-- | Level | Code | Message |
-- +---------+------+--------------------------------------------+
-- | Warning | 1292 | Truncated incorrect INTEGER value: '1,2,3' |
-- +---------+------+--------------------------------------------+
-- 1 row in set (0.00 sec)

But if I use the CAST() function in a stored procedure or function, I get an error. When I ignore the error by declaring a CONTINUE handler, I get no error or warning, but the result is NULL.

Is there a good workaround for this?

Here is my test setup:

mysql> CREATE DATABASE IF NOT EXISTS test_db CHARACTER SET=utf8 COLLATE=utf8_general_ci;
Query OK, 1 row affected (0.00 sec)

mysql> USE test_db;
Database changed
mysql> DELIMITER $$
mysql> CREATE FUNCTION fn_cast_test(v_input VARCHAR(255)) RETURNS INTEGER DETERMINISTIC
-> BEGIN
-> DECLARE v_retval INTEGER DEFAULT 0;
-> DECLARE CONTINUE HANDLER FOR 1292 BEGIN END;
-> SET v_retval := CAST(v_input AS SIGNED);
-> RETURN v_retval;
-> END$$
Query OK, 0 rows affected (0.00 sec)

mysql> DELIMITER ;
mysql> SELECT fn_cast_test('1,2,3');

-- Results:
-- +-----------------------+
-- | fn_cast_test('1,2,3') |
-- +-----------------------+
-- | NULL |
-- +-----------------------+
-- 1 row in set (0.00 sec)