| Bug #103202 | Procedure using str_to_date behaving differently as stored function | ||
|---|---|---|---|
| Submitted: | 5 Apr 2021 7:55 | Modified: | 25 Jul 17:45 |
| Reporter: | Geert Vanderkelen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| 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 2021 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 2021 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 2021 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 2021 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 2021 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 2021 12:25]
MySQL Verification Team
Hi Mr. Vanderkelen, We agree with you that this is a good request for documentation update. Verified.
[6 Apr 2021 12:25]
MySQL Verification Team
Correct version.
[5 Aug 2021 13:38]
Stefan Hinz
Adding something like "some condition handling might not work" to the documentation will not be helpful, but rather cause additional confusion, thus rejecting this request.
[25 Jul 17:45]
Stefan Hinz
Posted by developer: No additional feedback was provided. Closing.
[26 Jul 9:37]
MySQL Verification Team
Thank you, Stefan.
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');