Bug#31990: MINUTE() and SECOND() return bogus results when used on a DATE

HOUR(), MINUTE(), ... returned spurious results when used on a DATE-cast.
This happened because DATE-cast object did not overload get_time() method
in superclass Item. The default method was inappropriate here and
misinterpreted the data.

Patch adds missing method; get_time() on DATE-casts now returns SQL-NULL
on NULL input, 0 otherwise. This coincides with the way DATE-columns
behave.


mysql-test/r/cast.result:
  Show that HOUR(), MINUTE(), ... return sensible values when used
  on DATE-cast objects, namely NULL for NULL-dates and 0 otherwise.
  Show that this coincides with how DATE-columns behave.
mysql-test/t/cast.test:
  Show that HOUR(), MINUTE(), ... return sensible values when used
  on DATE-cast objects, namely NULL for NULL-dates and 0 otherwise.
  Show that this coincides with how DATE-columns behave.
sql/item_timefunc.cc:
  Add get_time() method to DATE-cast object to overload
  the method in Item superclass that would return spurious
  results. Return zero-result; flag NULL if input was NULL.
sql/item_timefunc.h:
  Add get_time() declaration to DATE-cast object.

2 files changed, 8 insertions, 0 deletions

diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index c1fa9dce038..7ed5e375f5b 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2645,6 +2645,13 @@ bool Item_date_typecast::get_date(MYSQL_TIME *ltime, uint fuzzy_date)
 }
 
 
+bool Item_date_typecast::get_time(MYSQL_TIME *ltime)
+{
+  bzero((char *)ltime, sizeof(MYSQL_TIME));
+  return args[0]->null_value;
+}
+
+
 String *Item_date_typecast::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index a5ecbc57e8d..b647e93b700 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -779,6 +779,7 @@ public:
   const char *func_name() const { return "cast_as_date"; }
   String *val_str(String *str);
   bool get_date(MYSQL_TIME *ltime, uint fuzzy_date);
+  bool get_time(MYSQL_TIME *ltime);
   const char *cast_type() const { return "date"; }
   enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
   Field *tmp_table_field(TABLE *t_arg)