MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime)

The problem was that Item_func_hybrid_field_type::get_date() did not
convert the result to the correct data type, so MYSQL_TIME::time_type
of the get_date() result could be not in sync with field_type().

Changes:
1. Adding two new classes Datetime and Date to store MYSQL_TIMESTAMP_DATETIME
   and MYSQL_TIMESTAMP_DATE values respectively
   (in addition to earlier added class Time, for MYSQL_TIMESTAMP_TIME values).
2. Adding Item_func_hybrid_field_type::time_op().
   It performs the operation using TIME representation,
   and always returns a MYSQL_TIME value with time_type=MYSQL_TIMESTAMP_TIME.
   Implementing time_op() for all affected children classes.
3. Fixing all implementations of date_op() to perform the operation
   using strictly DATETIME representation. Now they always return a MYSQL_TIME
   value with time_type=MYSQL_TIMESTAMP_{DATE|DATETIME},
   according to the result data type.
4. Removing assignment of ltime.time_type to mysql_timestamp_type()
   from all val_xxx_from_date_op(), because now date_op() makes sure
   to return a proper MYSQL_TIME value with a good time_type (and other member)
5. Adding Item_func_hybrid_field_type::val_xxx_from_time_op().
6. Overriding Type_handler_time_common::Item_func_hybrid_field_type_val_xxx()
   to call val_xxx_from_time_op() instead of val_xxx_from_date_op().
7. Modified Item_func::get_arg0_date() to return strictly a TIME value
   if TIME_TIME_ONLY is passed, or return strictly a DATETIME value otherwise.
   If args[0] returned a value of a different temporal type,
   (for example a TIME value when TIME_TIME_ONLY was not passed,
    or a DATETIME value when TIME_TIME_ONLY was passed), the conversion
   is automatically applied.
   Earlier, get_arg0_date() did not guarantee a result in
   accordance to TIME_TIME_ONLY flag.

14 files changed, 527 insertions, 119 deletions

diff --git a/mysql-test/r/func_in.result b/mysql-test/r/func_in.result
index 692806c7cac..65313148bf8 100644
--- a/mysql-test/r/func_in.result
+++ b/mysql-test/r/func_in.result
@@ -896,3 +896,16 @@ EXECUTE stmt;
 a	b
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
+#
+# MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime)
+#
+SELECT
+TIME'00:00:00'='' AS c1_true,
+TIME'00:00:00' IN ('', TIME'10:20:30') AS c2_true,
+TIME'00:00:00' NOT IN ('', TIME'10:20:30') AS c3_false;
+c1_true	c2_true	c3_false
+1	1	0
+Warnings:
+Warning	1292	Truncated incorrect time value: ''
+Warning	1292	Truncated incorrect time value: ''
+Warning	1292	Truncated incorrect time value: ''
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index e710f3ac438..53f61f6644f 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -3351,3 +3351,35 @@ SELECT CONVERT_TZ(1, ROW(1,1), 1);
 ERROR HY000: Illegal parameter data type row for operation 'convert_tz'
 SELECT CONVERT_TZ(1, 1, ROW(1,1));
 ERROR HY000: Illegal parameter data type row for operation 'convert_tz'
+#
+# MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime)
+#
+SET TIMESTAMP=UNIX_TIMESTAMP('2018-02-17 01:02:03');
+SELECT
+COALESCE(TIME'800:00:00', NOW()) AS c,
+HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc;
+c	hc
+2018-03-22 08:00:00	8
+SELECT
+CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END AS c,
+HOUR(CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END) AS hc;
+c	hc
+2018-03-22 08:00:00	8
+SELECT
+IFNULL(TIME'800:00:00', NOW()) AS c,
+HOUR(IFNULL(TIME'800:00:00', NOW())) AS hc;
+c	hc
+2018-03-22 08:00:00	8
+SELECT
+IF(TRUE,TIME'800:00:00', NOW()) AS c,
+HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc;
+c	hc
+2018-03-22 08:00:00	8
+SELECT
+ADDTIME(TIME'10:20:30', TIMESTAMP'2001-01-01 00:00:00') AS c1,
+ADDTIME(TIME'10:20:30', COALESCE(TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:00')) AS c2,
+ADDTIME(TIME'10:20:30', DATE'2001-01-01') AS c3,
+ADDTIME(TIME'10:20:30', COALESCE(DATE'2001-01-01',TIMESTAMP'2001-01-01 00:00:00')) AS c4;
+c1	c2	c3	c4
+NULL	NULL	NULL	NULL
+SET TIMESTAMP=DEFAULT;
diff --git a/mysql-test/t/func_in.test b/mysql-test/t/func_in.test
index 8f6062fd2c2..b99fad159c2 100644
--- a/mysql-test/t/func_in.test
+++ b/mysql-test/t/func_in.test
@@ -676,3 +676,17 @@ EXECUTE stmt;
 EXECUTE stmt;
 DEALLOCATE PREPARE stmt;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime)
+--echo #
+
+# This is to make sure that TIME_FUZZY_DATE is always passed to str_to_time(),
+# so empty strings are compared as TIME'00:00:00' all around the code:
+# when using Arg_comparator (e.g. in binary comparison operators), and
+# when not using it (e.g. in IN predicate).
+
+SELECT
+  TIME'00:00:00'='' AS c1_true,
+  TIME'00:00:00' IN ('', TIME'10:20:30') AS c2_true,
+  TIME'00:00:00' NOT IN ('', TIME'10:20:30') AS c3_false;
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 09c13598cfd..0066d4a434b 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1945,3 +1945,33 @@ SELECT CONVERT_TZ(ROW(1,1),1,1);
 SELECT CONVERT_TZ(1, ROW(1,1), 1);
 --error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
 SELECT CONVERT_TZ(1, 1, ROW(1,1));
+
+
+--echo #
+--echo # MDEV-15340 Wrong result HOUR(case_expression_with_time_and_datetime)
+--echo #
+
+SET TIMESTAMP=UNIX_TIMESTAMP('2018-02-17 01:02:03');
+SELECT
+  COALESCE(TIME'800:00:00', NOW()) AS c,
+  HOUR(COALESCE(TIME'800:00:00',NOW())) AS hc;
+
+SELECT
+  CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END AS c,
+  HOUR(CASE WHEN TRUE THEN TIME'800:00:00' ELSE NOW() END) AS hc;
+
+SELECT
+  IFNULL(TIME'800:00:00', NOW()) AS c,
+  HOUR(IFNULL(TIME'800:00:00', NOW())) AS hc;
+
+SELECT
+  IF(TRUE,TIME'800:00:00', NOW()) AS c,
+  HOUR(IF(TRUE,TIME'800:00:00', NOW())) AS hc;
+
+SELECT
+  ADDTIME(TIME'10:20:30', TIMESTAMP'2001-01-01 00:00:00') AS c1,
+  ADDTIME(TIME'10:20:30', COALESCE(TIMESTAMP'2001-01-01 00:00:00',TIMESTAMP'2001-01-01 00:00:00')) AS c2,
+  ADDTIME(TIME'10:20:30', DATE'2001-01-01') AS c3,
+  ADDTIME(TIME'10:20:30', COALESCE(DATE'2001-01-01',TIMESTAMP'2001-01-01 00:00:00')) AS c4;
+
+SET TIMESTAMP=DEFAULT;
diff --git a/sql/field.cc b/sql/field.cc
index 9b6465988f6..38978fbb727 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -5655,30 +5655,28 @@ Item *Field_temporal::get_equal_const_item_datetime(THD *thd,
          const_item->field_type() != MYSQL_TYPE_TIMESTAMP) ||
         const_item->decimals != decimals())
     {
-      MYSQL_TIME ltime;
-      if (const_item->field_type() == MYSQL_TYPE_TIME ?
-          const_item->get_date_with_conversion(&ltime, 0) :
-          const_item->get_date(&ltime, 0))
+      Datetime dt(thd, const_item, 0);
+      if (!dt.is_valid_datetime())
         return NULL;
       /*
         See comments about truncation in the same place in
         Field_time::get_equal_const_item().
       */
-      return new (thd->mem_root) Item_datetime_literal(thd, &ltime,
+      return new (thd->mem_root) Item_datetime_literal(thd,
+                                                       dt.get_mysql_time(),
                                                        decimals());
     }
     break;
   case ANY_SUBST:
     if (!is_temporal_type_with_date(const_item->field_type()))
     {
-      MYSQL_TIME ltime;
-      if (const_item->get_date_with_conversion(&ltime,
-                                               TIME_FUZZY_DATES |
-                                               TIME_INVALID_DATES))
+      Datetime dt(thd, const_item, TIME_FUZZY_DATES | TIME_INVALID_DATES);
+      if (!dt.is_valid_datetime())
         return NULL;
       return new (thd->mem_root)
-        Item_datetime_literal_for_invalid_dates(thd, &ltime,
-                                                ltime.second_part ?
+        Item_datetime_literal_for_invalid_dates(thd, dt.get_mysql_time(),
+                                                dt.get_mysql_time()->
+                                                second_part ?
                                                 TIME_SECOND_PART_DIGITS : 0);
     }
     break;
@@ -6030,10 +6028,8 @@ Item *Field_time::get_equal_const_item(THD *thd, const Context &ctx,
     {
       MYSQL_TIME ltime;
       // Get the value of const_item with conversion from DATETIME to TIME
-      if (const_item->get_time_with_conversion(thd, &ltime,
-                                               TIME_TIME_ONLY |
-                                               TIME_FUZZY_DATES |
-                                               TIME_INVALID_DATES))
+      ulonglong fuzzydate= Time::comparison_flags_for_get_date();
+      if (const_item->get_time_with_conversion(thd, &ltime, fuzzydate))
         return NULL;
       /*
         Replace a DATE/DATETIME constant to a TIME constant:
@@ -6506,10 +6502,9 @@ Item *Field_newdate::get_equal_const_item(THD *thd, const Context &ctx,
   case ANY_SUBST:
     if (!is_temporal_type_with_date(const_item->field_type()))
     {
-      MYSQL_TIME ltime;
       // Get the value of const_item with conversion from TIME to DATETIME
-      if (const_item->get_date_with_conversion(&ltime,
-                                        TIME_FUZZY_DATES | TIME_INVALID_DATES))
+      Datetime dt(thd, const_item, TIME_FUZZY_DATES | TIME_INVALID_DATES);
+      if (!dt.is_valid_datetime())
         return NULL;
       /*
         Replace the constant to a DATE or DATETIME constant.
@@ -6522,26 +6517,23 @@ Item *Field_newdate::get_equal_const_item(THD *thd, const Context &ctx,
 
         (assuming CURRENT_DATE is '2015-08-30'
       */
-      if (non_zero_hhmmssuu(&ltime))
+      if (!dt.hhmmssff_is_zero())
         return new (thd->mem_root)
-          Item_datetime_literal_for_invalid_dates(thd, &ltime,
-                                                  ltime.second_part ?
+          Item_datetime_literal_for_invalid_dates(thd, dt.get_mysql_time(),
+                                                  dt.get_mysql_time()->
+                                                    second_part ?
                                                   TIME_SECOND_PART_DIGITS : 0);
-      datetime_to_date(&ltime);
       return new (thd->mem_root)
-        Item_date_literal_for_invalid_dates(thd, &ltime);
+        Item_date_literal_for_invalid_dates(thd, Date(&dt).get_mysql_time());
     }
     break;
   case IDENTITY_SUBST:
     if (const_item->field_type() != MYSQL_TYPE_DATE)
     {
-      MYSQL_TIME ltime;
-      if (const_item->field_type() == MYSQL_TYPE_TIME ?
-          const_item->get_date_with_conversion(&ltime, 0) :
-          const_item->get_date(&ltime, 0))
+      Date d(thd, const_item, 0);
+      if (!d.is_valid_date())
         return NULL;
-      datetime_to_date(&ltime);
-      return new (thd->mem_root) Item_date_literal(thd, &ltime);
+      return new (thd->mem_root) Item_date_literal(thd, d.get_mysql_time());
     }
     break;
   }
diff --git a/sql/item.cc b/sql/item.cc
index 6ca9a2e1812..95ff0a65fb9 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -124,38 +124,6 @@ longlong Item::val_datetime_packed_result()
 
 /**
   Get date/time/datetime.
-  Optionally extend TIME result to DATETIME.
-*/
-bool Item::get_date_with_conversion(MYSQL_TIME *ltime, ulonglong fuzzydate)
-{
-  THD *thd= current_thd;
-
-  /*
-    Some TIME type items return error when trying to do get_date()
-    without TIME_TIME_ONLY set (e.g. Item_field for Field_time).
-    In the SQL standard time->datetime conversion mode we add TIME_TIME_ONLY.
-    In the legacy time->datetime conversion mode we do not add TIME_TIME_ONLY
-    and leave it to get_date() to check date.
-  */
-  ulonglong time_flag= (field_type() == MYSQL_TYPE_TIME &&
-           !(thd->variables.old_behavior & OLD_MODE_ZERO_DATE_TIME_CAST)) ?
-           TIME_TIME_ONLY : 0;
-  if (get_date(ltime, fuzzydate | time_flag))
-    return true;
-  if (ltime->time_type == MYSQL_TIMESTAMP_TIME &&
-      !(fuzzydate & TIME_TIME_ONLY))
-  {
-    MYSQL_TIME tmp;
-    if (time_to_datetime_with_warn(thd, ltime, &tmp, fuzzydate))
-      return null_value= true;
-    *ltime= tmp;
-  }
-  return false;
-}
-
-
-/**
-  Get date/time/datetime.
   If DATETIME or DATE result is returned, it's converted to TIME.
 */
 bool Item::get_time_with_conversion(THD *thd, MYSQL_TIME *ltime,
diff --git a/sql/item.h b/sql/item.h
index e045a2a30bf..84cb08142dc 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -665,13 +665,6 @@ protected:
       value= NULL;
     return value;
   }
-  bool get_date_with_conversion_from_item(Item *item,
-                                          MYSQL_TIME *ltime,
-                                          ulonglong fuzzydate)
-  {
-    DBUG_ASSERT(fixed == 1);
-    return (null_value= item->get_date_with_conversion(ltime, fuzzydate));
-  }
   /*
     This method is used if the item was not null but convertion to
     TIME/DATE/DATETIME failed. We return a zero date if allowed,
@@ -1363,17 +1356,16 @@ public:
   bool get_date_from_decimal(MYSQL_TIME *ltime, ulonglong fuzzydate);
   bool get_date_from_string(MYSQL_TIME *ltime, ulonglong fuzzydate);
   bool get_time(MYSQL_TIME *ltime)
-  { return get_date(ltime, TIME_TIME_ONLY | TIME_INVALID_DATES); }
-  // Get date with automatic TIME->DATETIME conversion
-  bool get_date_with_conversion(MYSQL_TIME *ltime, ulonglong fuzzydate);
+  { return get_date(ltime, Time::flags_for_get_date()); }
   /*
-    Get time with automatic DATE/DATETIME to TIME conversion.
+    Get time with automatic DATE/DATETIME to TIME conversion,
+    by subtracting CURRENT_DATE.
 
-    Performce a reserve operation to get_date_with_conversion().
+    Performce a reverse operation to CAST(time AS DATETIME)
     Suppose:
     - we have a set of items (typically with the native MYSQL_TYPE_TIME type)
       whose item->get_date() return TIME1 value, and
-    - item->get_date_with_conversion() for the same Items return DATETIME1,
+    - CAST(AS DATETIME) for the same Items return DATETIME1,
       after applying time-to-datetime conversion to TIME1.
 
     then all items (typically of the native MYSQL_TYPE_{DATE|DATETIME} types)
@@ -1402,22 +1394,21 @@ public:
   // Get a DATE or DATETIME value in numeric packed format for comparison
   virtual longlong val_datetime_packed()
   {
-    MYSQL_TIME ltime;
     ulonglong fuzzydate= TIME_FUZZY_DATES | TIME_INVALID_DATES;
-    return get_date_with_conversion(&ltime, fuzzydate) ? 0 : pack_time(&ltime);
+    Datetime dt(current_thd, this, fuzzydate);
+    return dt.is_valid_datetime() ? pack_time(dt.get_mysql_time()) : 0;
   }
   // Get a TIME value in numeric packed format for comparison
   virtual longlong val_time_packed()
   {
-    MYSQL_TIME ltime;
-    ulonglong fuzzydate= TIME_FUZZY_DATES | TIME_INVALID_DATES | TIME_TIME_ONLY;
-    return get_date(&ltime, fuzzydate) ? 0 : pack_time(&ltime);
+    Time tm(this, Time::comparison_flags_for_get_date());
+    return tm.is_valid_time() ? pack_time(tm.get_mysql_time()) : 0;
   }
   longlong val_datetime_packed_result();
   longlong val_time_packed_result()
   {
     MYSQL_TIME ltime;
-    ulonglong fuzzydate= TIME_FUZZY_DATES | TIME_INVALID_DATES | TIME_TIME_ONLY;
+    ulonglong fuzzydate= Time::comparison_flags_for_get_date();
     return get_date_result(&ltime, fuzzydate) ? 0 : pack_time(&ltime);
   }
 
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 5846ef094d9..9f30b238660 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2368,9 +2368,25 @@ Item_func_ifnull::str_op(String *str)
 bool Item_func_ifnull::date_op(MYSQL_TIME *ltime, ulonglong fuzzydate)
 {
   DBUG_ASSERT(fixed == 1);
-  if (!args[0]->get_date_with_conversion(ltime, fuzzydate & ~TIME_FUZZY_DATES))
-    return (null_value= false);
-  return (null_value= args[1]->get_date_with_conversion(ltime, fuzzydate & ~TIME_FUZZY_DATES));
+  for (uint i= 0; i < 2; i++)
+  {
+    Datetime dt(current_thd, args[i], fuzzydate & ~TIME_FUZZY_DATES);
+    if (!(dt.copy_to_mysql_time(ltime, mysql_timestamp_type())))
+      return (null_value= false);
+  }
+  return (null_value= true);
+}
+
+
+bool Item_func_ifnull::time_op(MYSQL_TIME *ltime)
+{
+  DBUG_ASSERT(fixed == 1);
+  for (uint i= 0; i < 2; i++)
+  {
+    if (!Time(args[i]).copy_to_mysql_time(ltime))
+      return (null_value= false);
+  }
+  return (null_value= true);
 }
 
 
@@ -2850,7 +2866,19 @@ Item_func_nullif::date_op(MYSQL_TIME *ltime, ulonglong fuzzydate)
   DBUG_ASSERT(fixed == 1);
   if (!compare())
     return (null_value= true);
-  return (null_value= args[2]->get_date(ltime, fuzzydate));
+  Datetime dt(current_thd, args[2], fuzzydate);
+  return (null_value= dt.copy_to_mysql_time(ltime, mysql_timestamp_type()));
+}
+
+
+bool
+Item_func_nullif::time_op(MYSQL_TIME *ltime)
+{
+  DBUG_ASSERT(fixed == 1);
+  if (!compare())
+    return (null_value= true);
+  return (null_value= Time(args[2]).copy_to_mysql_time(ltime));
+
 }
 
 
@@ -2991,7 +3019,18 @@ bool Item_func_case::date_op(MYSQL_TIME *ltime, ulonglong fuzzydate)
   Item *item= find_item();
   if (!item)
     return (null_value= true);
-  return (null_value= item->get_date_with_conversion(ltime, fuzzydate));
+  Datetime dt(current_thd, item, fuzzydate);
+  return (null_value= dt.copy_to_mysql_time(ltime, mysql_timestamp_type()));
+}
+
+
+bool Item_func_case::time_op(MYSQL_TIME *ltime)
+{
+  DBUG_ASSERT(fixed == 1);
+  Item *item= find_item();
+  if (!item)
+    return (null_value= true);
+  return (null_value= Time(item).copy_to_mysql_time(ltime));
 }
 
 
@@ -3401,7 +3440,20 @@ bool Item_func_coalesce::date_op(MYSQL_TIME *ltime, ulonglong fuzzydate)
   DBUG_ASSERT(fixed == 1);
   for (uint i= 0; i < arg_count; i++)
   {
-    if (!args[i]->get_date_with_conversion(ltime, fuzzydate & ~TIME_FUZZY_DATES))
+    Datetime dt(current_thd, args[i], fuzzydate & ~TIME_FUZZY_DATES);
+    if (!dt.copy_to_mysql_time(ltime, mysql_timestamp_type()))
+      return (null_value= false);
+  }
+  return (null_value= true);
+}
+
+
+bool Item_func_coalesce::time_op(MYSQL_TIME *ltime)
+{
+  DBUG_ASSERT(fixed == 1);
+  for (uint i= 0; i < arg_count; i++)
+  {
+    if (!Time(args[i]).copy_to_mysql_time(ltime))
       return (null_value= false);
   }
   return (null_value= true);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 900a979ce97..9d790b75ebb 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -1005,6 +1005,7 @@ public:
   String *str_op(String *);
   my_decimal *decimal_op(my_decimal *);
   bool date_op(MYSQL_TIME *ltime, ulonglong fuzzydate);
+  bool time_op(MYSQL_TIME *ltime);
   void fix_length_and_dec()
   {
     if (!aggregate_for_result(func_name(), args, arg_count, true))
@@ -1077,6 +1078,7 @@ public:
   String *str_op(String *str);
   my_decimal *decimal_op(my_decimal *);
   bool date_op(MYSQL_TIME *ltime, ulonglong fuzzydate);
+  bool time_op(MYSQL_TIME *ltime);
   void fix_length_and_dec()
   {
     Item_func_case_abbreviation2::fix_length_and_dec2(args);
@@ -1110,7 +1112,12 @@ public:
 
   bool date_op(MYSQL_TIME *ltime, ulonglong fuzzydate)
   {
-    return get_date_with_conversion_from_item(find_item(), ltime, fuzzydate);
+    Datetime dt(current_thd, find_item(), fuzzydate);
+    return (null_value= dt.copy_to_mysql_time(ltime, mysql_timestamp_type()));
+  }
+  bool time_op(MYSQL_TIME *ltime)
+  {
+    return (null_value= Time(find_item()).copy_to_mysql_time(ltime));
   }
   longlong int_op()
   {
@@ -1222,6 +1229,7 @@ public:
     arg_count= 2; // See the comment to the constructor
   }
   bool date_op(MYSQL_TIME *ltime, ulonglong fuzzydate);
+  bool time_op(MYSQL_TIME *ltime);
   double real_op();
   longlong int_op();
   String *str_op(String *str);
@@ -2111,6 +2119,7 @@ public:
   String *str_op(String *);
   my_decimal *decimal_op(my_decimal *);
   bool date_op(MYSQL_TIME *ltime, ulonglong fuzzydate);
+  bool time_op(MYSQL_TIME *ltime);
   bool fix_fields(THD *thd, Item **ref);
   table_map not_null_tables() const { return 0; }
   const char *func_name() const { return "case"; }
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 4903e552c9d..71078185586 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -929,7 +929,6 @@ String *Item_func_hybrid_field_type::val_str_from_date_op(String *str)
   if (date_op_with_null_check(&ltime) ||
       (null_value= str->alloc(MAX_DATE_STRING_REP_LENGTH)))
     return (String *) 0;
-  ltime.time_type= mysql_timestamp_type();
   str->length(my_TIME_to_str(&ltime, const_cast<char*>(str->ptr()), decimals));
   str->set_charset(&my_charset_bin);
   DBUG_ASSERT(!null_value);
@@ -941,7 +940,6 @@ double Item_func_hybrid_field_type::val_real_from_date_op()
   MYSQL_TIME ltime;
   if (date_op_with_null_check(&ltime))
     return 0;
-  ltime.time_type= mysql_timestamp_type();
   return TIME_to_double(&ltime);
 }
 longlong Item_func_hybrid_field_type::val_int_from_date_op()
@@ -949,7 +947,6 @@ longlong Item_func_hybrid_field_type::val_int_from_date_op()
   MYSQL_TIME ltime;
   if (date_op_with_null_check(&ltime))
     return 0;
-  ltime.time_type= mysql_timestamp_type();
   return TIME_to_ulonglong(&ltime);
 }
 
@@ -962,7 +959,40 @@ Item_func_hybrid_field_type::val_decimal_from_date_op(my_decimal *dec)
     my_decimal_set_zero(dec);
     return 0;
   }
-  ltime.time_type= mysql_timestamp_type();
+  return date2my_decimal(&ltime, dec);
+}
+
+
+String *Item_func_hybrid_field_type::val_str_from_time_op(String *str)
+{
+  MYSQL_TIME ltime;
+  if (time_op_with_null_check(&ltime) ||
+      (null_value= my_TIME_to_str(&ltime, str, decimals)))
+    return NULL;
+  return str;
+}
+
+double Item_func_hybrid_field_type::val_real_from_time_op()
+{
+  MYSQL_TIME ltime;
+  return time_op_with_null_check(&ltime) ? 0 : TIME_to_double(&ltime);
+}
+
+longlong Item_func_hybrid_field_type::val_int_from_time_op()
+{
+  MYSQL_TIME ltime;
+  return time_op_with_null_check(&ltime) ? 0 : TIME_to_ulonglong(&ltime);
+}
+
+my_decimal *
+Item_func_hybrid_field_type::val_decimal_from_time_op(my_decimal *dec)
+{
+  MYSQL_TIME ltime;
+  if (time_op_with_null_check(&ltime))
+  {
+    my_decimal_set_zero(dec);
+    return 0;
+  }
   return date2my_decimal(&ltime, dec);
 }
 
diff --git a/sql/item_func.h b/sql/item_func.h
index adc5c238abd..063a80de737 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -160,14 +160,17 @@ public:
   void print_args(String *str, uint from, enum_query_type query_type);
   inline bool get_arg0_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
   {
-    return (null_value=args[0]->get_date_with_conversion(ltime, fuzzy_date));
+    return fuzzy_date & TIME_TIME_ONLY ? get_arg0_time(ltime) :
+                                         get_arg0_datetime(ltime, fuzzy_date);
+  }
+  inline bool get_arg0_datetime(MYSQL_TIME *ltime, ulonglong fuzzy_date)
+  {
+    Datetime dt(current_thd, args[0], fuzzy_date);
+    return (null_value= dt.copy_to_mysql_time(ltime));
   }
   inline bool get_arg0_time(MYSQL_TIME *ltime)
   {
-    null_value= args[0]->get_time(ltime);
-    DBUG_ASSERT(null_value ||
-                ltime->time_type != MYSQL_TIMESTAMP_TIME || ltime->day == 0);
-    return null_value;
+    return (null_value= Time(args[0]).copy_to_mysql_time(ltime));
   }
   bool is_null() { 
     update_null_value();
@@ -447,11 +450,17 @@ class Item_func_hybrid_field_type: public Item_hybrid_func
   */
   bool date_op_with_null_check(MYSQL_TIME *ltime)
   {
-     bool rc= date_op(ltime,
-                      field_type() == MYSQL_TYPE_TIME ? TIME_TIME_ONLY : 0);
+     bool rc= date_op(ltime, 0);
      DBUG_ASSERT(!rc ^ null_value);
      return rc;
   }
+  bool time_op_with_null_check(MYSQL_TIME *ltime)
+  {
+     bool rc= time_op(ltime);
+     DBUG_ASSERT(!rc ^ null_value);
+     DBUG_ASSERT(rc || ltime->time_type == MYSQL_TIMESTAMP_TIME);
+     return rc;
+  }
   String *str_op_with_null_check(String *str)
   {
     String *res= str_op(str);
@@ -488,32 +497,30 @@ public:
   {
     return real_op();
   }
-  bool get_date_from_date_op(MYSQL_TIME *ltime, ulonglong fuzzydate)
-  {
-    return date_op(ltime,
-                   (fuzzydate |
-                   (field_type() == MYSQL_TYPE_TIME ? TIME_TIME_ONLY : 0)));
-  }
 
   // Value methods that involve conversion
   String *val_str_from_decimal_op(String *str);
   String *val_str_from_real_op(String *str);
   String *val_str_from_int_op(String *str);
   String *val_str_from_date_op(String *str);
+  String *val_str_from_time_op(String *str);
 
   my_decimal *val_decimal_from_str_op(my_decimal *dec);
   my_decimal *val_decimal_from_real_op(my_decimal *dec);
   my_decimal *val_decimal_from_int_op(my_decimal *dec);
   my_decimal *val_decimal_from_date_op(my_decimal *dec);
+  my_decimal *val_decimal_from_time_op(my_decimal *dec);
 
   longlong val_int_from_str_op();
   longlong val_int_from_real_op();
   longlong val_int_from_decimal_op();
   longlong val_int_from_date_op();
+  longlong val_int_from_time_op();
 
   double val_real_from_str_op();
   double val_real_from_decimal_op();
   double val_real_from_date_op();
+  double val_real_from_time_op();
   double val_real_from_int_op();
 
   bool get_date_from_str_op(MYSQL_TIME *ltime, ulonglong fuzzydate);
@@ -609,11 +616,18 @@ public:
 
   /**
      @brief Performs the operation that this functions implements when
-     field type is a temporal type.
+     field type is DATETIME or DATE.
      @return The result of the operation.
   */
   virtual bool date_op(MYSQL_TIME *res, ulonglong fuzzy_date)= 0;
 
+  /**
+     @brief Performs the operation that this functions implements when
+     field type is TIME.
+     @return The result of the operation.
+  */
+  virtual bool time_op(MYSQL_TIME *res)= 0;
+
 };
 
 
@@ -676,6 +690,11 @@ public:
     DBUG_ASSERT(0);
     return true;
   }
+  bool time_op(MYSQL_TIME *ltime)
+  {
+    DBUG_ASSERT(0);
+    return true;
+  }
 };
 
 
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index a0fdd4bb8fc..816514dcc35 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2771,7 +2771,7 @@ bool Item_func_add_time::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
     // ADDTIME function AND the first argument is TIME
     if (args[0]->get_time(&l_time1) || 
         args[1]->get_time(&l_time2) ||
-        l_time2.time_type == MYSQL_TIMESTAMP_DATETIME)
+        l_time2.time_type != MYSQL_TIMESTAMP_TIME)
       return (null_value= 1);
     is_time= (l_time1.time_type == MYSQL_TIMESTAMP_TIME);
   }
@@ -2950,14 +2950,13 @@ longlong Item_func_timestamp_diff::val_int()
   long microseconds;
   long months= 0;
   int neg= 1;
+  THD *thd= current_thd;
+  ulonglong fuzzydate= TIME_NO_ZERO_DATE | TIME_NO_ZERO_IN_DATE;
+
+  null_value= 0;
 
-  null_value= 0;  
-  if (args[0]->get_date_with_conversion(&ltime1,
-                                        TIME_NO_ZERO_DATE |
-                                        TIME_NO_ZERO_IN_DATE) ||
-      args[1]->get_date_with_conversion(&ltime2,
-                                        TIME_NO_ZERO_DATE |
-                                        TIME_NO_ZERO_IN_DATE))
+  if (Datetime(thd, args[0], fuzzydate).copy_to_mysql_time(&ltime1) ||
+      Datetime(thd, args[1], fuzzydate).copy_to_mysql_time(&ltime2))
     goto null_date;
 
   if (calc_time_diff(&ltime2,&ltime1, 1,
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index ac9d19594fd..d2d05ae0ff4 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -121,15 +121,41 @@ bool Type_handler_data::init()
 Type_handler_data *type_handler_data= NULL;
 
 
-void Time::make_from_item(Item *item)
+void Time::make_from_item(Item *item, sql_mode_t flags)
 {
-  if (item->get_time(this))
+  if (item->get_date(this, flags))
     time_type= MYSQL_TIMESTAMP_NONE;
   else
     valid_MYSQL_TIME_to_valid_value();
 }
 
 
+void Temporal_with_date::make_from_item(THD *thd, Item *item, sql_mode_t flags)
+{
+  flags&= ~TIME_TIME_ONLY;
+  /*
+    Some TIME type items return error when trying to do get_date()
+    without TIME_TIME_ONLY set (e.g. Item_field for Field_time).
+    In the SQL standard time->datetime conversion mode we add TIME_TIME_ONLY.
+    In the legacy time->datetime conversion mode we do not add TIME_TIME_ONLY
+    and leave it to get_date() to check date.
+  */
+  ulonglong time_flag= (item->field_type() == MYSQL_TYPE_TIME &&
+           !(thd->variables.old_behavior & OLD_MODE_ZERO_DATE_TIME_CAST)) ?
+           TIME_TIME_ONLY : 0;
+  if (item->get_date(this, flags | time_flag))
+    time_type= MYSQL_TIMESTAMP_NONE;
+  else if (time_type == MYSQL_TIMESTAMP_TIME)
+  {
+    MYSQL_TIME tmp;
+    if (time_to_datetime_with_warn(thd, this, &tmp, flags))
+      time_type= MYSQL_TIMESTAMP_NONE;
+    else
+      *(static_cast<MYSQL_TIME*>(this))= tmp;
+  }
+}
+
+
 void Type_std_attributes::set(const Field *field)
 {
   decimals= field->decimals();
@@ -3523,7 +3549,55 @@ Type_handler_temporal_result::Item_func_hybrid_field_type_get_date(
                                         MYSQL_TIME *ltime,
                                         ulonglong fuzzydate) const
 {
-  return item->get_date_from_date_op(ltime, fuzzydate);
+  return item->date_op(ltime, fuzzydate);
+}
+
+
+/***************************************************************************/
+
+String *
+Type_handler_time_common::Item_func_hybrid_field_type_val_str(
+                                    Item_func_hybrid_field_type *item,
+                                    String *str) const
+{
+  return item->val_str_from_time_op(str);
+}
+
+
+double
+Type_handler_time_common::Item_func_hybrid_field_type_val_real(
+                                    Item_func_hybrid_field_type *item)
+                                    const
+{
+  return item->val_real_from_time_op();
+}
+
+
+longlong
+Type_handler_time_common::Item_func_hybrid_field_type_val_int(
+                                    Item_func_hybrid_field_type *item)
+                                    const
+{
+  return item->val_int_from_time_op();
+}
+
+
+my_decimal *
+Type_handler_time_common::Item_func_hybrid_field_type_val_decimal(
+                                    Item_func_hybrid_field_type *item,
+                                    my_decimal *dec) const
+{
+  return item->val_decimal_from_time_op(dec);
+}
+
+
+bool
+Type_handler_time_common::Item_func_hybrid_field_type_get_date(
+                                    Item_func_hybrid_field_type *item,
+                                    MYSQL_TIME *ltime,
+                                    ulonglong fuzzydate) const
+{
+  return item->time_op(ltime);
 }
 
 
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 9afdf1a915f..2e2029f43f1 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -165,19 +165,35 @@ class Time: private MYSQL_TIME
       break;
     }
   }
-  void make_from_item(class Item *item);
+  void make_from_item(class Item *item, sql_mode_t flags);
 public:
   Time() { time_type= MYSQL_TIMESTAMP_NONE; }
-  Time(Item *item) { make_from_item(item); }
+  Time(Item *item) { make_from_item(item, flags_for_get_date()); }
+  Time(Item *item, sql_mode_t flags) { make_from_item(item, flags); }
+  static sql_mode_t flags_for_get_date()
+  { return TIME_TIME_ONLY | TIME_INVALID_DATES; }
+  static sql_mode_t comparison_flags_for_get_date()
+  { return TIME_TIME_ONLY | TIME_INVALID_DATES | TIME_FUZZY_DATES; }
   bool is_valid_time() const
   {
     DBUG_ASSERT(is_valid_value_slow());
     return time_type == MYSQL_TIMESTAMP_TIME;
   }
-  void copy_to_mysql_time(MYSQL_TIME *ltime) const
+  const MYSQL_TIME *get_mysql_time() const
   {
     DBUG_ASSERT(is_valid_time_slow());
+    return this;
+  }
+  bool copy_to_mysql_time(MYSQL_TIME *ltime) const
+  {
+    if (time_type == MYSQL_TIMESTAMP_NONE)
+    {
+      ltime->time_type= MYSQL_TIMESTAMP_NONE;
+      return true;
+    }
+    DBUG_ASSERT(is_valid_time_slow());
     *ltime= *this;
+    return false;
   }
   int cmp(const Time *other) const
   {
@@ -194,6 +210,163 @@ public:
 };
 
 
+/**
+  Class Temporal_with_date is designed to store valid DATE or DATETIME values.
+  See also class Time.
+
+  1. Valid value:
+    a. MYSQL_TIMESTAMP_{DATE|DATETIME} - a valid DATE or DATETIME value
+    b. MYSQL_TIMESTAMP_NONE            - an undefined value
+
+  2. Invalid value (internally only):
+    a. MYSQL_TIMESTAMP_{DATE|DATETIME} - a DATE or DATETIME value, but with
+                                         MYSQL_TIME members outside of the
+                                         valid/supported range
+    b. MYSQL_TIMESTAMP_TIME            - a TIME value
+    c. MYSQL_TIMESTAMP_ERROR           - error
+
+  Temporarily is allowed to have an invalid value, but only internally,
+  during initialization time. All constructors and modification methods must
+  leave the value as described above (see "Valid value").
+
+  Derives from MYSQL_TIME using "protected" inheritance to make sure
+  it is accessed externally only in the valid state.
+*/
+
+class Temporal_with_date: protected MYSQL_TIME
+{
+protected:
+  void make_from_item(THD *thd, Item *item, sql_mode_t flags);
+  Temporal_with_date(THD *thd, Item *item, sql_mode_t flags)
+  {
+    make_from_item(thd, item, flags);
+  }
+};
+
+
+/**
+  Class Date is designed to store valid DATE values.
+  All constructors and modification methods leave instances
+  of this class in one of the following valid states:
+    a. MYSQL_TIMESTAMP_DATE - a DATE with all MYSQL_TIME members properly set
+    b. MYSQL_TIMESTAMP_NONE - an undefined value.
+  Other MYSQL_TIMESTAMP_XXX are not possible.
+  MYSQL_TIMESTAMP_DATE with MYSQL_TIME members improperly set is not possible.
+*/
+class Date: public Temporal_with_date
+{
+  bool is_valid_value_slow() const
+  {
+    return time_type == MYSQL_TIMESTAMP_NONE || is_valid_date_slow();
+  }
+  bool is_valid_date_slow() const
+  {
+    DBUG_ASSERT(time_type == MYSQL_TIMESTAMP_DATE);
+    return !check_datetime_range(this);
+  }
+public:
+  Date(THD *thd, Item *item, sql_mode_t flags)
+   :Temporal_with_date(thd, item, flags)
+  {
+    if (time_type == MYSQL_TIMESTAMP_DATETIME)
+      datetime_to_date(this);
+    DBUG_ASSERT(is_valid_value_slow());
+  }
+  Date(const Temporal_with_date *d)
+   :Temporal_with_date(*d)
+  {
+    datetime_to_date(this);
+    DBUG_ASSERT(is_valid_date_slow());
+  }
+  bool is_valid_date() const
+  {
+    DBUG_ASSERT(is_valid_value_slow());
+    return time_type == MYSQL_TIMESTAMP_DATE;
+  }
+  const MYSQL_TIME *get_mysql_time() const
+  {
+    DBUG_ASSERT(is_valid_date_slow());
+    return this;
+  }
+};
+
+
+/**
+  Class Datetime is designed to store valid DATETIME values.
+  All constructors and modification methods leave instances
+  of this class in one of the following valid states:
+    a. MYSQL_TIMESTAMP_DATETIME - a DATETIME with all members properly set
+    b. MYSQL_TIMESTAMP_NONE     - an undefined value.
+  Other MYSQL_TIMESTAMP_XXX are not possible.
+  MYSQL_TIMESTAMP_DATETIME with MYSQL_TIME members
+  improperly set is not possible.
+*/
+class Datetime: public Temporal_with_date
+{
+  bool is_valid_value_slow() const
+  {
+    return time_type == MYSQL_TIMESTAMP_NONE || is_valid_datetime_slow();
+  }
+  bool is_valid_datetime_slow() const
+  {
+    DBUG_ASSERT(time_type == MYSQL_TIMESTAMP_DATETIME);
+    return !check_datetime_range(this);
+  }
+public:
+  Datetime(THD *thd, Item *item, sql_mode_t flags)
+   :Temporal_with_date(thd, item, flags)
+  {
+    if (time_type == MYSQL_TIMESTAMP_DATE)
+      date_to_datetime(this);
+    DBUG_ASSERT(is_valid_value_slow());
+  }
+  bool is_valid_datetime() const
+  {
+    /*
+      Here we quickly check for the type only.
+      If the type is valid, the rest of value must also be valid.
+    */
+    DBUG_ASSERT(is_valid_value_slow());
+    return time_type == MYSQL_TIMESTAMP_DATETIME;
+  }
+  bool hhmmssff_is_zero() const
+  {
+    DBUG_ASSERT(is_valid_datetime_slow());
+    return hour == 0 && minute == 0 && second == 0 && second_part == 0;
+  }
+  const MYSQL_TIME *get_mysql_time() const
+  {
+    DBUG_ASSERT(is_valid_datetime_slow());
+    return this;
+  }
+  bool copy_to_mysql_time(MYSQL_TIME *ltime) const
+  {
+    if (time_type == MYSQL_TIMESTAMP_NONE)
+    {
+      ltime->time_type= MYSQL_TIMESTAMP_NONE;
+      return true;
+    }
+    DBUG_ASSERT(is_valid_datetime_slow());
+    *ltime= *this;
+    return false;
+  }
+  /**
+    Copy without data loss, with an optional DATETIME to DATE conversion.
+    If the value of the "type" argument is MYSQL_TIMESTAMP_DATE,
+    then "this" must be a datetime with a zero hhmmssff part.
+  */
+  bool copy_to_mysql_time(MYSQL_TIME *ltime, timestamp_type type)
+  {
+    DBUG_ASSERT(type == MYSQL_TIMESTAMP_DATE ||
+                type == MYSQL_TIMESTAMP_DATETIME);
+    if (copy_to_mysql_time(ltime))
+      return true;
+    DBUG_ASSERT(type != MYSQL_TIMESTAMP_DATE || hhmmssff_is_zero());
+    ltime->time_type= type;
+    return false;
+  }
+};
+
 /*
   Flags for collation aggregation modes, used in TDCollation::agg():
 
@@ -2220,6 +2393,18 @@ public:
                                        Type_handler_hybrid_field_type *,
                                        Type_all_attributes *atrr,
                                        Item **items, uint nitems) const;
+  String *Item_func_hybrid_field_type_val_str(Item_func_hybrid_field_type *,
+                                              String *) const;
+  double Item_func_hybrid_field_type_val_real(Item_func_hybrid_field_type *)
+                                              const;
+  longlong Item_func_hybrid_field_type_val_int(Item_func_hybrid_field_type *)
+                                               const;
+  my_decimal *Item_func_hybrid_field_type_val_decimal(
+                                              Item_func_hybrid_field_type *,
+                                              my_decimal *) const;
+  bool Item_func_hybrid_field_type_get_date(Item_func_hybrid_field_type *,
+                                            MYSQL_TIME *,
+                                            ulonglong fuzzydate) const;
   bool Item_func_min_max_get_date(Item_func_min_max*,
                                   MYSQL_TIME *, ulonglong fuzzydate) const;
   Item *make_const_item_for_comparison(THD *, Item *src, const Item *cmp) const;