MySQL Bugs: #71386: STR_TO_DATE in TRADITIONAL sql_mode fails to parse time-only field

Bug #71386	STR_TO_DATE in TRADITIONAL sql_mode fails to parse time-only field
Submitted:	15 Jan 2014 6:52	Modified:	1 Aug 2019 16:28
Reporter:	Udi S	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.6.13, 5.6.17, 5.5.36, 5.7.4	OS:	Any (Mac OS X, Linux)
Assigned to:		CPU Architecture:	Any
Tags:	regression, SQL_MODE, STR_TO_DATE, traditional

Description:
When using TRADITIONAL my_sql mode, the following query fails:
SELECT STR_TO_DATE('01:35 PM', '%h:%i %p');

whereas when not using traditional mode the query works fine.

How to repeat:
1. Run mysql with traditional mode: SET SESSION sql_mode='TRADITIONAL';
2. Issue the query SELECT STR_TO_DATE('01:35 PM', '%h:%i %p');
3. See that it returns NULL

to compare with standard execution:
1. Set blank sql_mode: SET SESSION sql_mode='';
2. Issue the query SELECT STR_TO_DATE('01:35 PM', '%h:%i %p');
3. See that it returns a time value

Thank you for the report.

Verified as described. Bug is not repeatable with version 5.1 and older

Manual says (http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_str-to-date):

STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts.

In the given case format string only contain time parts.  Hence, I agree that  NO_ZERO_DATES should not have any impact here.

Contribution submitted via Github - BUG#71386 STR_TO_DATE in TRADITIONAL sql_mode fails to parse time-only field (*) Contribution by Daniel Black (Github grooverdan, mysql-server/pull/224#issuecomment-429520269): I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: git_patch_222634949.txt (text/plain), 3.22 KiB.

Posted by developer:
 
Fixed in 8.0.18.

With strict SQL mode enabled, the STR_TO_DATE() function did not
properly handle values with time parts only. Thanks to Daniel Black
for the contribution.

Hi, 

I just updated the contribution status.

Thank you for your contribution !