Bug #103202 Procedure using str_to_date behaving differently as stored function
Submitted: 5 Apr 7:55 Modified: 6 Apr 12:25
Reporter: Geert Vanderkelen Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:8.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: cast, stored routine, STR_TO_DATE

[5 Apr 7:55] Geert Vanderkelen
Description:
(Using MySQL 8.0.23, Linux)

I store timestamp in a JSON field, and need to extract them creating an index. All good using CAST (with 8.0.22 and up). However, I have timestamps which do _not_ have fractional parts, have 0 milliseconds for example. This is OK, I can work my work around it outside of MySQL.

While investigating a solution with MySQL I did hit the fact that ALTER TABLE does not ignore errors like 1411 (incorrect value for str_to_state).

Anyways, I was trying to make a Stored Function to use that in the ALTER TABLE, but that doesn't work because of the restrictions with Generated Columns.

However, I did hit something odd where a Stored Function doing the apparently exact same thing, behaving differently from the Stored Procedure.

The following is what I want in stored routine:

-- SET @d = '2019-01-28T18:20:00.234Z';
SET @d = '2019-01-28T18:20:00Z';

SELECT  CAST(IFNULL(STR_TO_DATE(@d, '%Y-%m-%dT%H:%i:%sZ'),
                   STR_TO_DATE(@d, '%Y-%m-%dT%H:%i:%s.%fZ')) AS DATETIME (6)) as published;

I defined a stored function, that does this str_to_date, but it gives instead a NULL when the first first str_to_date fails. Which is OK, but I am handling that error in the function (see How to repeat):

mysql> SELECT rfc3339_to_datetime('2019-01-28T18:20:00Z');
+---------------------------------------------+
| rfc3339_to_datetime('2019-01-28T18:20:00Z') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+

mysql> SELECT rfc3339_to_datetime('2019-01-28T18:20:00.234Z');
+-------------------------------------------------+
| rfc3339_to_datetime('2019-01-28T18:20:00.234Z') |
+-------------------------------------------------+
| 2019-01-28 18:20:00.234000                      |
+-------------------------------------------------+

I expected no NULL in this case.

I am working around the limitation and restrictions in my code outside MySQL (forcing fractions), but it different in behaviour between the stored function and procedure are odd.

Or have missed something in the fine manual? :)

How to repeat:
-- SQL Modes:
-- ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION 

-- 
-- Stored procedure that works
-- 
DROP PROCEDURE IF EXISTS p_rfc3339_to_datetime;
DELIMITER //
CREATE PROCEDURE p_rfc3339_to_datetime(dt CHAR(30))
BEGIN
    DECLARE CONTINUE HANDLER FOR 1411 BEGIN END; -- incorrect value for str_to_date
    SELECT CAST(
            IFNULL(STR_TO_DATE(dt, '%Y-%m-%dT%H:%i:%s.%fZ'),
                   STR_TO_DATE(dt, '%Y-%m-%dT%H:%i:%sZ')) AS DATETIME (6));
END//;

-- both calls produce wanted DATETIME(6)
CALL p_rfc3339_to_datetime('2019-01-28T18:20:00Z');
CALL p_rfc3339_to_datetime('2019-01-28T18:20:00.234Z');

-- 
-- Stored Function that does not work
-- 
DROP FUNCTION IF EXISTS rfc3339_to_datetime;
DELIMITER //
CREATE FUNCTION rfc3339_to_datetime(dt CHAR(30))
     RETURNS DATETIME(6) DETERMINISTIC
BEGIN
    DECLARE CONTINUE HANDLER FOR 1411 BEGIN END; -- incorrect value for str_to_date
    RETURN CAST(
            IFNULL(STR_TO_DATE(dt, '%Y-%m-%dT%H:%i:%s.%fZ'),
                   STR_TO_DATE(dt, '%Y-%m-%dT%H:%i:%sZ')) AS DATETIME (6));
END//;

mysql> SELECT rfc3339_to_datetime('2019-01-28T18:20:00Z');
+---------------------------------------------+
| rfc3339_to_datetime('2019-01-28T18:20:00Z') |
+---------------------------------------------+
| NULL                                        |
+---------------------------------------------+

mysql> SELECT rfc3339_to_datetime('2019-01-28T18:20:00.234Z');
+-------------------------------------------------+
| rfc3339_to_datetime('2019-01-28T18:20:00.234Z') |
+-------------------------------------------------+
| 2019-01-28 18:20:00.234000                      |
+-------------------------------------------------+

-- 
-- Trying to look for a workaround, I am doing the CAST manually, and this WORKS!
-- 

DROP FUNCTION IF EXISTS rfc3339_to_datetime_no_cast;
DELIMITER //
CREATE FUNCTION rfc3339_to_datetime_no_cast(dt CHAR(30))
    RETURNS DATETIME(6) DETERMINISTIC
BEGIN
    DECLARE CONTINUE HANDLER FOR 1411 BEGIN
    END; -- incorrect value for str_to_date
    SET @d = STR_TO_DATE(dt, '%Y-%m-%dT%H:%i:%s.%fZ');
    IF @d IS NULL THEN
        SET @d = STR_TO_DATE(dt, '%Y-%m-%dT%H:%i:%sZ');
    END IF;
    RETURN @d;
END//;

-- Both following selects give correct values.
SELECT rfc3339_to_datetime_no_cast('2019-01-28T18:20:00Z');
SELECT rfc3339_to_datetime_no_cast('2019-01-28T18:20:00.234Z');
[5 Apr 8:01] Geert Vanderkelen
You know, I have been looking at this for the past 1.5h, and when I post it, when I see my own bug report, I just realise that:
* Stored procedure uses SELECT which gives a warning for str_to_date failing
* Stored function does not use as SELECT, so fails hard
* The workaround, doing the `SET @d ...` is following the SELECT-path, so also not failing

However.. the HANDLER should have worked? Why is that error not handled the same way? Or is that not possible in a RETURN?

Maybe a fact to note in https://dev.mysql.com/doc/refman/8.0/en/return.html ?
[5 Apr 13:46] MySQL Verification Team
Hi Mr. Vanderkelen,

Thank you for your bug report.

However, this is not a bug.

According to SQL standard, sub-chapter 4.28, stored procedures and functions do not behave the same. However, you could try storing in your DATETIME local variable the same expression that you used in your stored procedure.

This will require additional effort on your side.

Not a bug.
[5 Apr 13:46] MySQL Verification Team
Hi Mr. Vanderkelen,

Thank you for your bug report.

However, this is not a bug.

According to SQL standard, sub-chapter 4.28, stored procedures and functions do not behave the same. However, you could try storing in your DATETIME local variable the same expression that you used in your stored procedure.

This will require additional effort on your side.

Not a bug.
[5 Apr 15:19] MySQL Verification Team
Hi Mr. Vanderkelen,

Your diagnosis is correct. It boils down to the error 1411 and how is it handled when you can run select and when you don't.

This is a difference in how stored functions and procedures behave.

A better workaround is to use SELECT ..... INTO @local_datetime_variable;

Still not a bug, but expected behaviour.
[5 Apr 15:56] Geert Vanderkelen
I already added a workaround, it is working, that is fine (it is useless in indexing sadly). That's not the really the point of this report.

Stored functions allow to declare condition handling with DECLARE .. HANDLER.
Quoting the MySQL documentation:

https://dev.mysql.com/doc/refman/8.0/en/declare-handler.html:

"To ignore a condition, declare a CONTINUE handler for it and associate
 it with an empty block."

That's what I did in the Stored Routine (a function in this case). But apparently, it doesn't work for the statement in the RETURN.
This is not mentioned in:
* https://dev.mysql.com/doc/refman/8.0/en/condition-handling-restrictions.html
* or https://dev.mysql.com/doc/refman/8.0/en/return.html

If this is particular thing is documented it in the SQL Standard, then it would be great to maybe mention that "some condition handling might not work"? I find it a bit odd since a function is all about the RETURN.
[6 Apr 12:25] MySQL Verification Team
Hi Mr. Vanderkelen,

We agree with you that this is a good request for documentation update.

Verified.
[6 Apr 12:25] MySQL Verification Team
Correct version.