Bug #96166 Deterministic function return different value after error.
Submitted: 11 Jul 2019 1:51 Modified: 9 Oct 2019 14:55
Reporter: Seunguck Lee Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:8.0.15, 8.0.11, 8.0.16 OS:CentOS (localhost 3.10.0-229.14.1.el7.centos.plus.x86_64 #1 SMP Tue Sep 15 18:05:55 UTC 2015 x86_64 x86_64 x)
Assigned to: CPU Architecture:x86 (x86_84)

[11 Jul 2019 1:51] Seunguck Lee 
Description:
I have made simple DETERMINISTIC function.

This function returns expected values until no error.
But once error happens, following function call with same parameter returns different value from before error. This is not consistent.

And interesting thing is, this case only happen MySQL 8.0.15, Not happened in MySQL 5.7.21

How to repeat:
-- // CREATE FUNCTION
delimiter ;;

create function myfunc(p varchar(50)) returns varchar(32) charset utf8mb4
  deterministic
  sql security invoker
begin
  return concat(unix_timestamp(p));
end;;

delimiter ;

-- // CASE TEST 
-- // This function call return expected value
> select myfunc('2019-01-01 00:00:00');
+-------------------------------+
| myfunc('2019-01-01 00:00:00') |
+-------------------------------+
| 1546300800                    |
+-------------------------------+

-- // Make error for invalid date format
> select myfunc('2019-01-01 00:00:71');
ERROR 1292 (22007): Incorrect datetime value: '2019-01-01 00:00:71'

-- // After error, all function call return "fractional seconds part" 
> select myfunc('2019-01-01 00:00:00');
+-------------------------------+
| myfunc('2019-01-01 00:00:00') |
+-------------------------------+
| 1546300800.000000             |
+-------------------------------+

Suggested fix:
Return same value if parameter is valid date format.
[11 Jul 2019 8:18] MySQL Verification Team 
Hello!

Thank you for the report.

regards,
Umesh
[9 Oct 2019 14:55] Paul DuBois 
Posted by developer:
 
Fixed in 8.0.19.

For UNIX_TIMESTAMP() errors occurring within stored functions, the
number of fractional seconds for subsequent function invocations
could be incorrect.