From 5df8b72ce1786c7520956357dd238b7334b4b0b0 Mon Sep 17 00:00:00 2001
From: Daniel Black <daniel@linux.ibm.com>
Date: Sat, 13 Oct 2018 18:45:04 +1100
Subject: [PATCH] BUG#71386 STR_TO_DATE in TRADITIONAL sql_mode fails to parse
 time-only field

Problem:

Time only parsing in STR_TO_DATE obeys NON_ZERO_DATES in sql_mode strictly such
that time only strings fail.

Fix:

Only check the NON_ZERO_DATE if the resulting time is DATETIME or DATE.
---
 mysql-test/r/func_time.result | 4 +---
 mysql-test/r/strict.result    | 2 ++
 mysql-test/t/strict.test      | 7 +++++++
 sql/item_timefunc.cc          | 1 +
 4 files changed, 11 insertions(+), 3 deletions(-)

diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 8c37d5d3a1e..7a03927438e 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1228,9 +1228,7 @@ DROP TABLE t1,t2;
 set time_zone= @@global.time_zone;
 select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
 str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE
-NULL
-Warnings:
-Warning	1411	Incorrect datetime value: '10:00 PM' for function str_to_date
+22:10:00
 create table t1 (field DATE);
 insert into t1 values ('2006-11-06');
 select * from t1 where field < '2006-11-06 04:08:36.0';
diff --git a/mysql-test/r/strict.result b/mysql-test/r/strict.result
index ccb5f34ea17..a567d81804a 100644
--- a/mysql-test/r/strict.result
+++ b/mysql-test/r/strict.result
@@ -275,6 +275,8 @@ INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
 ERROR HY000: Incorrect datetime value: '15.13.2004 15.30' for function str_to_date
 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
 ERROR HY000: Incorrect datetime value: '00.00.0000' for function str_to_date
+INSERT INTO t1 (col3) VALUES(STR_TO_DATE('09:22', '%H:%i'));
+INSERT INTO t1 (col3) VALUES(STR_TO_DATE('09:22:23.33', '%H:%i:%s.%f'));
 drop table t1;
 CREATE TABLE t1 (col1 date, col2 datetime, col3 timestamp);
 INSERT INTO t1 (col1) VALUES (CAST('2004-10-15' AS DATE));
diff --git a/mysql-test/t/strict.test b/mysql-test/t/strict.test
index feca61a26be..59cb746651e 100644
--- a/mysql-test/t/strict.test
+++ b/mysql-test/t/strict.test
@@ -251,6 +251,13 @@ INSERT INTO t1 (col3) VALUES(STR_TO_DATE('15.13.2004 15.30','%d.%m.%Y %H.%i'));
 --error 1411
 INSERT INTO t1 (col3) VALUES(STR_TO_DATE('00.00.0000','%d.%m.%Y'));
 
+# Bug#71386
+## Test INSERT with STR_TO_DATE into TIME
+#       All test cases expected to succeed
+#
+INSERT INTO t1 (col3) VALUES(STR_TO_DATE('09:22', '%H:%i'));
+INSERT INTO t1 (col3) VALUES(STR_TO_DATE('09:22:23.33', '%H:%i:%s.%f'));
+
 drop table t1;
 
 
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index c79616daab3..e110e879ad7 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -3048,6 +3048,7 @@ bool Item_func_str_to_date::val_datetime(MYSQL_TIME *ltime,
   if (extract_date_time(&date_time_format, val->ptr(), val->length(), ltime,
                         cached_timestamp_type, 0, "datetime") ||
       ((fuzzy_date & TIME_NO_ZERO_DATE) &&
+       (data_type() == MYSQL_TYPE_DATETIME || data_type() == MYSQL_TYPE_DATE) &&
        (ltime->year == 0 || ltime->month == 0 || ltime->day == 0)))
     goto null_date;
   ltime->time_type = cached_timestamp_type;