Bug #111526 STR_TO_DATE fails in MySQL stored function with error instead of returning NULL

• Submitted: 21 Jun 2023 19:02
• Modified: 22 Jun 2023 13:09
• Reporter: Lewis Graham
• Status: Verified
• Category: MySQL Server: Stored Routines
• Severity: S3 (Non-critical)
• Version: 8.0
• OS: Any
• CPU Architecture: Any

[21 Jun 2023 19:02] Lewis Graham

Description:
The following script fails in the stored function and the stored procedure.

According to the doc:

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

STR_TO_DATE should return NULL, so the second conversion in the IF could be tried in the function or the procedure. Instead an error is returned:

ERROR 1411 (HY000): Incorrect datetime value: '2023-06-21' for function str_to_date

Note, the simple statement before the function / procedure call:

SELECT STR_TO_DATE( '2023-06-21', '%Y%m%d' );

works fine and returns NULL as expected.

Also note, replacing this line:

SET ret_ = STR_TO_DATE( '2023-06-21', '%Y%m%d' );

with this: 

SELECT STR_TO_DATE( '2023-06-21', '%Y%m%d' ) INTO ret_;

also does not work as suggested in several threads.

Also found this thread:

https://dba.stackexchange.com/questions/202627/incorrect-value-for-str-to-date

But inserting this in the stored function / procedure also does not work:

	SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_DATE', '');
	SET @@SQL_MODE = REPLACE(@@SQL_MODE, 'NO_ZERO_IN_DATE', '');

How to repeat:
# STORED FUNCTION
# ===============

DROP FUNCTION IF EXISTS JTELWeb.TEST;

DELIMITER $$

CREATE FUNCTION IF NOT EXISTS JTELWeb.TEST()
RETURNS TIMESTAMP(6)
DETERMINISTIC
BEGIN
	DECLARE ret_ TIMESTAMP(6);
	SET ret_ = STR_TO_DATE( '2023-06-21', '%Y%m%d' );
	IF( ret_ IS NULL ) THEN
		SET ret_ = STR_TO_DATE( '2023-06-21', '%Y-%m-%d' );	
	END IF;
	RETURN ret_;
END $$

DELIMITER ;

SELECT STR_TO_DATE( '2023-06-21', '%Y%m%d' );
SELECT JTELWeb.TEST();

# STORED PROCEDURE
# ================

DROP PROCEDURE IF EXISTS JTELWeb.TEST;

DELIMITER $$

CREATE PROCEDURE IF NOT EXISTS JTELWeb.TEST()
BEGIN
	DECLARE ret_ TIMESTAMP(6);
	SET ret_ = STR_TO_DATE( '2023-06-21', '%Y%m%d' );
	IF( ret_ IS NULL ) THEN
		SET ret_ = STR_TO_DATE( '2023-06-21', '%Y-%m-%d' );	
	END IF;
END $$

DELIMITER ;

SELECT STR_TO_DATE( '2023-06-21', '%Y%m%d' );
CALL TEST();

[21 Jun 2023 19:09] Lewis Graham

Additional information. As a workaround, this works. 
Weird that it works with session variables, but not with declared variables in the function / procedure.
IMHO - must be a bug.

DROP FUNCTION IF EXISTS JTELWeb.TEST;

DELIMITER $$

CREATE FUNCTION IF NOT EXISTS JTELWeb.TEST()
RETURNS TIMESTAMP(6)
DETERMINISTIC
BEGIN
	SELECT STR_TO_DATE( '2023-06-21', '%Y%m%d' ) INTO @ret_;
	IF( @ret_ IS NULL ) THEN
		SELECT STR_TO_DATE( '2023-06-21', '%Y-%m-%d' ) INTO @ret_;
	END IF;
	RETURN @ret_;
END $$

DELIMITER ;

SELECT STR_TO_DATE( '2023-06-21', '%Y%m%d' );
SELECT JTELWeb.TEST();

DROP FUNCTION IF EXISTS JTELWeb.TEST;

DELIMITER $$

CREATE FUNCTION IF NOT EXISTS JTELWeb.TEST()
RETURNS TIMESTAMP(6)
DETERMINISTIC
BEGIN
	SET  @ret_ = STR_TO_DATE( '2023-06-21', '%Y%m%d' );
	IF( @ret_ IS NULL ) THEN
		SET  @ret_ = STR_TO_DATE( '2023-06-21', '%Y-%m-%d' );
	END IF;
	RETURN @ret_;
END $$

DELIMITER ;

SELECT STR_TO_DATE( '2023-06-21', '%Y%m%d' );
SELECT JTELWeb.TEST();

[22 Jun 2023 13:09] MySQL Verification Team

Hi Mr. Graham,

Thank you for your bug report.

We did not use local variables within the function, but declared variables.

Still, behaviour is the same and we got the same results.

---------------------------

select test();
ERROR 1411 (HY000): Incorrect datetime value: '2023-06-21' for function str_to_date

SELECT STR_TO_DATE( '2023-06-21', '%Y%m%d' );
+---------------------------------------+
| STR_TO_DATE( '2023-06-21', '%Y%m%d' ) |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

SELECT STR_TO_DATE( '2023-06-21', '%Y-%m-%d' );
+-----------------------------------------+
| STR_TO_DATE( '2023-06-21', '%Y-%m-%d' ) |
+-----------------------------------------+
| 2023-06-21                              |
+-----------------------------------------+
---------------------------

This is now a verified bug.

However, it is possible that behaviour is intentionally different for stored routines, so this can be (possibly) changed into a Documentation bug.

Thanks for your report.