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.

1 files changed, 22 insertions, 0 deletions

diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index 316b79efe4d..df475b49746 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -246,4 +246,26 @@ INSERT INTO t1(d1) VALUES ('2007-07-19 08:30:00'), (NULL),
 SELECT cast(date(d1) as signed) FROM t1;
 drop table t1;
 
+#
+# Bug #31990: MINUTE() and SECOND() return bogus results when used on a DATE
+#
+
+# Show that HH:MM:SS of a DATE are 0, and that it's the same for columns
+# and typecasts (NULL in, NULL out).
+CREATE TABLE t1 (f1 DATE);
+INSERT INTO t1 VALUES ('2007-07-19'), (NULL);
+SELECT HOUR(f1),
+       MINUTE(f1),
+       SECOND(f1) FROM t1;
+SELECT HOUR(CAST('2007-07-19' AS DATE)),
+       MINUTE(CAST('2007-07-19' AS DATE)),
+       SECOND(CAST('2007-07-19' AS DATE));
+SELECT HOUR(CAST(NULL AS DATE)),
+       MINUTE(CAST(NULL AS DATE)),
+       SECOND(CAST(NULL AS DATE));
+SELECT HOUR(NULL),
+       MINUTE(NULL),
+       SECOND(NULL);
+DROP TABLE t1;
+
 --echo End of 5.0 tests