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

[15 Jan 2014 6:52] Udi S
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
[16 Jan 2014 18:14] Sveta Smirnova
Thank you for the report.

Verified as described. Bug is not repeatable with version 5.1 and older
[17 Jan 2014 10:46] Øystein Grøvlen
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.
[15 Oct 2018 16:04] OCA Admin
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.

[1 Aug 2019 16:28] Paul DuBois
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.
[9 Mar 2020 12:03] Frederic Descamps
Hi, 

I just updated the contribution status.

Thank you for your contribution !