MySQL Bugs: #98903: Delusive example in STR_TO

Bug #98903	Delusive example in STR_TO_DATE documentation
Submitted:	11 Mar 2020 8:04	Modified:	11 Jun 2020 17:35
Reporter:	Arthur Tokarchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	8.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The example is about NO_ZERO_DATE or NO_ZERO_IN_DATE mode usage and located on this page:
https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_str-to-date

It says:

If the NO_ZERO_DATE or NO_ZERO_IN_DATE SQL mode is enabled, zero dates or part of dates are disallowed. In that case, STR_TO_DATE() returns NULL and generates a warning:

mysql> SET sql_mode = '';
mysql> SELECT STR_TO_DATE('15:35:00', '%H:%i:%s');
+-------------------------------------+
| STR_TO_DATE('15:35:00', '%H:%i:%s') |
+-------------------------------------+
| 15:35:00                            |
+-------------------------------------+
mysql> SET sql_mode = 'NO_ZERO_IN_DATE';
mysql> SELECT STR_TO_DATE('15:35:00', '%h:%i:%s');
+-------------------------------------+
| STR_TO_DATE('15:35:00', '%h:%i:%s') |
+-------------------------------------+
| NULL                                |
+-------------------------------------+

This example shows that if you enable sql_mode NO_ZERO_IN_DATE, STR_TO_DATE returns NULL because of this change, but actually in second case it happens because the format is different — 12-hour %h is used instead of 24-hour %H format specifier, 15 is not in range 01..12, hence NULL in the result.

Executing the following statements (using format from first case above) renders valid result:
SET sql_mode = 'NO_ZERO_IN_DATE';
SELECT STR_TO_DATE('15:35:00', '%H:%i:%s');
+-------------------------------------+
| STR_TO_DATE('15:35:00', '%H:%i:%s') |
+-------------------------------------+
| 15:35:00                            |
+-------------------------------------+

How to repeat:
---

Suggested fix:
An example about DATE part with zero fields might be appropriate.

Thank you for the bug report.

Posted by developer:
 
Fixed using a date (not a time) and a format string with date (not time) specifiers.

If the NO_ZERO_DATE SQL mode is enabled, zero dates are disallowed.
In that case, STR_TO_DATE() returns NULL and generates a warning:

mysql> SET sql_mode = ';
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| 0000-00-00                            |
+---------------------------------------+
mysql> SET sql_mode = 'NO_ZERO_DATE';
mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
+---------------------------------------+
| STR_TO_DATE('00/00/0000', '%m/%d/%Y') |
+---------------------------------------+
| NULL                                  |
+---------------------------------------+
mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1411
Message: Incorrect datetime value: '00/00/0000' for function str_to_date