MDEV-4857 Wrong result of HOUR('1 00:00:00')

modified:
  mysql-test/r/func_time.result
  mysql-test/t/func_time.test
  sql-common/my_time.c
  sql/item_func.h
  sql/item_timefunc.cc
  sql/mysql_priv.h
  sql/time.cc

7 files changed, 70 insertions, 5 deletions

diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index c1b17948402..7020007f940 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -365,7 +365,7 @@ extract(DAY_MINUTE FROM "02 10:11:12")
 21011
 select extract(DAY_SECOND FROM "225 10:11:12");
 extract(DAY_SECOND FROM "225 10:11:12")
-8385959
+34225959
 Warnings:
 Warning	1292	Truncated incorrect time value: '225 10:11:12'
 select extract(HOUR FROM "1999-01-02 10:11:12");
@@ -1035,7 +1035,7 @@ Warning	1105	Cast to unsigned converted negative integer to it's positive comple
 Warning	1292	Truncated incorrect time value: '18446744073709551615:00:00'
 SELECT EXTRACT(HOUR FROM '100000:02:03');
 EXTRACT(HOUR FROM '100000:02:03')
-838
+22
 Warnings:
 Warning	1292	Truncated incorrect time value: '100000:02:03'
 CREATE TABLE t1(f1 TIME);
@@ -2320,3 +2320,30 @@ DROP TABLE t1;
 SELECT MAKETIME(0, 0, -0.1);
 MAKETIME(0, 0, -0.1)
 NULL
+#
+# MDEV-4857 Wrong result of HOUR('1 00:00:00')
+#
+SELECT HOUR('1 02:00:00'), HOUR('26:00:00');
+HOUR('1 02:00:00')	HOUR('26:00:00')
+26	26
+SELECT HOUR(TIME'1 02:00:00'), HOUR(TIME'26:00:00');
+HOUR(TIME'1 02:00:00')	HOUR(TIME'26:00:00')
+26	26
+SELECT HOUR(TIME('1 02:00:00')), HOUR(TIME('26:00:00'));
+HOUR(TIME('1 02:00:00'))	HOUR(TIME('26:00:00'))
+26	26
+SELECT DAY(TIME('1 02:00:00')), DAY(TIME('26:00:00'));
+DAY(TIME('1 02:00:00'))	DAY(TIME('26:00:00'))
+0	0
+SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00');
+EXTRACT(HOUR FROM '1 02:00:00')	EXTRACT(HOUR FROM '26:00:00')
+2	2
+SELECT EXTRACT(HOUR FROM TIME'1 02:00:00'), EXTRACT(HOUR FROM TIME'26:00:00');
+EXTRACT(HOUR FROM TIME'1 02:00:00')	EXTRACT(HOUR FROM TIME'26:00:00')
+2	2
+SELECT EXTRACT(HOUR FROM TIME('1 02:00:00')), EXTRACT(HOUR FROM TIME('26:00:00'));
+EXTRACT(HOUR FROM TIME('1 02:00:00'))	EXTRACT(HOUR FROM TIME('26:00:00'))
+2	2
+SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00'));
+EXTRACT(DAY FROM TIME('1 02:00:00'))	EXTRACT(DAY FROM TIME('26:00:00'))
+1	1
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 72efff02699..91ccb06655e 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1405,3 +1405,15 @@ DROP TABLE t1;
 --echo #
 SELECT MAKETIME(0, 0, -0.1);
 
+--echo #
+--echo # MDEV-4857 Wrong result of HOUR('1 00:00:00')
+--echo #
+SELECT HOUR('1 02:00:00'), HOUR('26:00:00');
+SELECT HOUR(TIME'1 02:00:00'), HOUR(TIME'26:00:00');
+SELECT HOUR(TIME('1 02:00:00')), HOUR(TIME('26:00:00'));
+SELECT DAY(TIME('1 02:00:00')), DAY(TIME('26:00:00'));
+
+SELECT EXTRACT(HOUR FROM '1 02:00:00'), EXTRACT(HOUR FROM '26:00:00');
+SELECT EXTRACT(HOUR FROM TIME'1 02:00:00'), EXTRACT(HOUR FROM TIME'26:00:00');
+SELECT EXTRACT(HOUR FROM TIME('1 02:00:00')), EXTRACT(HOUR FROM TIME('26:00:00'));
+SELECT EXTRACT(DAY FROM TIME('1 02:00:00')), EXTRACT(DAY FROM TIME('26:00:00'));
diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index d3a968ec53f..1f058f89bb7 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -648,8 +648,8 @@ fractional:
   
   l_time->year=         0;                      /* For protocol::store_time */
   l_time->month=        0;
-  l_time->day=          date[0];
-  l_time->hour=         date[1];
+  l_time->day=          0;
+  l_time->hour=         date[1] + date[0] * 24; /* Mix days and hours */
   l_time->minute=       date[2];
   l_time->second=       date[3];
   l_time->second_part=  date[4];
diff --git a/sql/item_func.h b/sql/item_func.h
index b3ce85b96c4..a176d5781ac 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -158,7 +158,9 @@ public:
   }
   inline bool get_arg0_time(MYSQL_TIME *ltime)
   {
-    return (null_value=args[0]->get_time(ltime));
+    null_value= args[0]->get_time(ltime);
+    DBUG_ASSERT(ltime->time_type != MYSQL_TIMESTAMP_TIME || ltime->day == 0);
+    return null_value;
   }
   bool is_null() { 
     update_null_value();
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 3baf583eaa0..b618ec038f2 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2033,6 +2033,10 @@ longlong Item_extract::val_int()
     return 0;
   neg= ltime.neg ? -1 : 1;
 
+  DBUG_ASSERT(ltime.time_type != MYSQL_TIMESTAMP_TIME ||  ltime.day == 0);
+  if (ltime.time_type == MYSQL_TIMESTAMP_TIME)
+    time_to_daytime_interval(&ltime);
+
   switch (int_type) {
   case INTERVAL_YEAR:		return ltime.year;
   case INTERVAL_YEAR_MONTH:	return ltime.year*100L+ltime.month;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 913ab66a843..191e6663ff1 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -2527,6 +2527,7 @@ bool str_to_time_with_warn(const char *str,uint length,MYSQL_TIME *l_time,
                            ulong fuzzydate);
 timestamp_type str_to_datetime_with_warn(const char *str, uint length,
                                          MYSQL_TIME *l_time, ulong flags);
+void time_to_daytime_interval(MYSQL_TIME *l_time);
 void localtime_to_TIME(MYSQL_TIME *to, struct tm *from);
 void calc_time_from_sec(MYSQL_TIME *to, long seconds, long microseconds);
 
diff --git a/sql/time.cc b/sql/time.cc
index f106bf1bb5f..dc724e21a16 100644
--- a/sql/time.cc
+++ b/sql/time.cc
@@ -1045,3 +1045,22 @@ int my_time_compare(MYSQL_TIME *a, MYSQL_TIME *b)
   return 0;
 }
 
+
+/*
+  Convert a TIME value to DAY-TIME interval, e.g. for extraction:
+    EXTRACT(DAY FROM x), EXTRACT(HOUR FROM x), etc.
+  Moves full days from ltime->hour to ltime->day.
+  Note, time_type is set to MYSQL_TIMESTAMP_NONE, to make sure that
+  the structure is not used for anything else other than extraction:
+  non-extraction TIME functions expect zero day value!
+*/
+void time_to_daytime_interval(MYSQL_TIME *ltime)
+{
+  DBUG_ASSERT(ltime->time_type == MYSQL_TIMESTAMP_TIME);
+  DBUG_ASSERT(ltime->year == 0);
+  DBUG_ASSERT(ltime->month == 0);
+  DBUG_ASSERT(ltime->day == 0);
+  ltime->day= ltime->hour / 24;
+  ltime->hour%= 24;
+  ltime->time_type= MYSQL_TIMESTAMP_NONE;
+}