MDEV-17351 Wrong results for GREATEST,TIMESTAMP,ADDTIME with an out-of-range TIME-alike argument

Problems:

Functions LEAST() and GREATEST() in TIME context, as well as functions
TIMESTAMP(a,b) and ADDTIME(a,b), returned confusing results when the
input TIME-alike value in a number or in a string was out of the TIME
supported range.

In case of TIMESTAMP(a,b) and ADDTIME(a,b), the second argument
value could get extra unexpected digits. For example, in:
    ADDTIME('2001-01-01 00:00:00', 10000000)  or
    ADDTIME('2001-01-01 00:00:00', '1000:00:00')
the second argument was converted to '838:59:59.999999'
with six fractional digits, which contradicted "decimals"
previously set to 0 in fix_length_and_dec().
These unexpected fractional digits led to confusing function results.

Changes:
1. GREATEST(), LEAST()

   - fixing Item_func_min_max::get_time_native()
   to respect "decimals" set by fix_length_and_dec().
   If a value of some numeric or string time-alike argument
   goes outside of the TIME range and gets limited to '838:59:59.999999',
   it's now right-truncated to the correct fractional precision.

   - fixing, Type_handler_temporal_result::Item_func_min_max_fix_attributes()
   to take into account arguments' time_precision() or datetime_precision(),
   rather than rely on "decimals" calculated by the generic implementation
   in Type_handler::Item_func_min_max_fix_attributes(). This makes
   GREATEST() and LEAST() return better data types, with the same
   fractional precision with what TIMESTAMP(a,b) and ADDTIME(a,b) return
   for the same arguments, and with DATE(a) and TIMESTAMP(a).

2. Item_func_add_time and Item_func_timestamp

   It was semantically wrong to apply the limit of the TIME data type
   to the argument "b", which plays the role of "INTERVAL DAY TO SECOND" here.
   Changing the code to fetch the argument "b" as INTERVAL rather than as TIME.

   The low level routine calc_time_diff() now gets the interval
   value without limiting to '838:59:59.999999', so in these examples:
     ADDTIME('2001-01-01 00:00:00', 10000000)
     ADDTIME('2001-01-01 00:00:00', '1000:00:00')
   calc_time_diff() gets '1000:00:00' as is.  The SQL function result
   now gets limited to the supported result data type range
   (datetime or time) inside calc_time_diff(), which now calculates
   the return value using the real fractional digits that
   came directly from the arguments (without the effect of limiting
   to the TIME range), so the result does not have any unexpected
   fractional digits any more.

   Detailed changes in TIMESTAMP() and ADDTIME():

   - Adding a new class Interval_DDhhmmssff. It's similar to Time, but:
     * does not try to parse datetime format, as it's not needed for
       functions TIMESTAMP() and ADDTIME().
     * does not cut values to '838:59:59.999999'

     The maximum supported Interval_DDhhmmssff's hard limit is
     'UINT_MAX32:59:59.999999'. The maximum used soft limit is:
     - '87649415:59:59.999999'   (in 'hh:mm:ss.ff' format)
     - '3652058 23:59:59.999999' (in 'DD hh:mm:ss.ff' format)
     which is a difference between:
     - TIMESTAMP'0001-01-01 00:00:00' and
     - TIMESTAMP'9999-12-31 23:59:59.999999'
     (the minimum datetime that supports arithmetic, and the
     maximum possible datetime value).

   - Fixing get_date() methods in the classes related to functions
     ADDTIME(a,b) and TIMESTAMP(a,b) to use the new class Interval_DDhhmmssff
     for fetching data from the second argument, instead of get_date().

   - Fixing fix_length_and_dec() methods in the classes related
     to functions ADDTIME(a,b) and TIMESTAMP(a,b) to use
     Interval_DDhhmmssff::fsp(item) instead of item->time_precision()
     to get the fractional precision of the second argument correctly.

   - Splitting the low level function str_to_time() into smaller pieces
     to reuse the code. Adding a new function str_to_DDhhmmssff(), to
     parse "INTERVAL DAY TO SECOND" values.

   After these changes, functions TIMESTAMP() and ADDTIME()
   return much more predictable results, in terms of fractional
   digits, and in terms of the overall result.

   The full ranges of DATETIME and TIME values are now covered by TIMESTAMP()
   and ADDTIME(), so the following can now be calculated:

    SELECT ADDTIME(TIMESTAMP'0001-01-01 00:00:00', '87649415:59:59.999999');
    -> '9999-12-31 23:59:59.999999'

    SELECT TIMESTAMP(DATE'0001-01-01', '87649415:59:59.999999')
    -> '9999-12-31 23:59:59.999999'

    SELECT ADDTIME(TIME'-838:59:59.999999', '1677:59:59.999998');
    -> '838:59:59.999999'

1 files changed, 129 insertions, 22 deletions

diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index 2841d33f7fd..008b339e955 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -261,6 +261,23 @@ static void get_microseconds(ulong *val, MYSQL_TIME_STATUS *status,
 }
 
 
+static int check_time_range_internal(MYSQL_TIME *ltime,
+                                     ulong max_hour, uint dec,
+                                     int *warning);
+
+int check_time_range(MYSQL_TIME *ltime, uint dec, int *warning)
+{
+  return check_time_range_internal(ltime, TIME_MAX_HOUR, dec, warning);
+}
+
+
+static my_bool
+str_to_DDhhmmssff_internal(my_bool neg, const char *str, size_t length,
+                           MYSQL_TIME *l_time, ulong max_hour,
+                           MYSQL_TIME_STATUS *status,
+                           const char **endptr);
+
+
 /*
   Convert a timestamp string to a MYSQL_TIME value.
 
@@ -443,6 +460,20 @@ err:
 }
 
 
+static size_t get_prefix_and_sign(my_bool *neg, const char *str, size_t length)
+{
+  const char *str0= str, *end= str + length;
+  for (; str < end && my_isspace(&my_charset_latin1, *str) ; str++)
+  { }
+  if (str < end && *str == '-')
+  {
+    *neg= TRUE;
+    str++;
+  }
+  return str - str0;
+}
+
+
 /*
  Convert a time string to a MYSQL_TIME struct.
 
@@ -475,23 +506,19 @@ err:
 my_bool str_to_time(const char *str, size_t length, MYSQL_TIME *l_time,
                     ulonglong fuzzydate, MYSQL_TIME_STATUS *status)
 {
-  ulong date[5];
-  ulonglong value;
-  const char *end=str+length, *end_of_days;
-  my_bool found_days,found_hours, neg= 0;
-  uint UNINIT_VAR(state);
+  my_bool neg= 0;
+  size_t tmp_length;
+  const char *endptr;
   DBUG_ASSERT(C_FLAGS_OK(fuzzydate));
 
   my_time_status_init(status);
-  for (; str != end && my_isspace(&my_charset_latin1,*str) ; str++)
-    length--;
-  if (str != end && *str == '-')
+
+  if ((tmp_length= get_prefix_and_sign(&neg, str, length)))
   {
-    neg=1;
-    str++;
-    length--;
+    str+= tmp_length;
+    length-= tmp_length;
   }
-  if (str == end)
+  if (!length)
   {
     status->warnings|= MYSQL_TIME_WARN_TRUNCATED;
     goto err;
@@ -508,8 +535,80 @@ my_bool str_to_time(const char *str, size_t length, MYSQL_TIME *l_time,
     my_time_status_init(status);
   }
 
+  if (!str_to_DDhhmmssff_internal(neg, str, length, l_time, TIME_MAX_HOUR,
+                                  status, &endptr))
+    return FALSE;
+err:
+  bzero((char*) l_time, sizeof(*l_time));
+  l_time->time_type= MYSQL_TIMESTAMP_ERROR;
+  return TRUE;
+}
+
+
+my_bool str_to_DDhhmmssff(const char *str, size_t length, MYSQL_TIME *ltime,
+                          ulong max_hour, MYSQL_TIME_STATUS *status)
+{
+  my_bool neg= 0;
+  size_t tmp_length;
+  const char *endptr;
+  my_time_status_init(status);
+
+  /* Remove trailing spaces */
+  for ( ; length > 0 && my_isspace(&my_charset_latin1, str[length - 1]) ; )
+    length--;
+
+  if ((tmp_length= get_prefix_and_sign(&neg, str, length)))
+  {
+    str+= tmp_length;
+    length-= tmp_length;
+  }
+  if (!length)
+  {
+    status->warnings|= MYSQL_TIME_WARN_TRUNCATED;
+    set_zero_time(ltime, MYSQL_TIMESTAMP_ERROR);
+    return TRUE;
+  }
+
+  /* Reject anything that might be parsed as a full TIMESTAMP */
+  if (length >= 12) /* The same condition with str_to_time() */
+  {
+    (void) str_to_datetime(str, length, ltime, C_TIME_DATETIME_ONLY, status);
+    if (ltime->time_type > MYSQL_TIMESTAMP_ERROR)
+    {
+      status->warnings|= MYSQL_TIME_WARN_TRUNCATED;
+      ltime->time_type= MYSQL_TIMESTAMP_NONE;
+      return TRUE;
+    }
+    my_time_status_init(status);
+  }
+
+  /*
+    Scan DDhhmmssff then reject anything that can remind date/datetime.
+    For example, in case of '2001-01-01', str_to_DDhhmmssff_internal()
+    will scan only '2001'.
+  */
+  if (str_to_DDhhmmssff_internal(neg, str, length, ltime, max_hour,
+                                 status, &endptr) ||                             
+      (endptr < str + length && endptr[0] == '-'))
+    return TRUE;
+  return FALSE;
+}
+
+
+static my_bool
+str_to_DDhhmmssff_internal(my_bool neg, const char *str, size_t length,
+                           MYSQL_TIME *l_time, ulong max_hour,
+                           MYSQL_TIME_STATUS *status, const char **endptr)
+{
+  ulong date[5];
+  ulonglong value;
+  const char *end=str + length, *end_of_days;
+  my_bool found_days, found_hours;
+  uint UNINIT_VAR(state);
+
+  *endptr= str;
   l_time->neg= neg;
-  /* Not a timestamp. Try to get this as a DAYS_TO_SECOND string */
+  /* Not a timestamp. Try to get this as a DAYS TO SECOND string */
   for (value=0; str != end && my_isdigit(&my_charset_latin1,*str) ; str++)
     value=value*10L + (long) (*str - '0');
 
@@ -621,6 +720,11 @@ fractional:
     goto err;
   }  
 
+  if ((ulonglong) date[0] * 24 + date[1] > (ulonglong) UINT_MAX32)
+  {
+    status->warnings|= MYSQL_TIME_WARN_OUT_OF_RANGE;
+    goto err;
+  }
   l_time->year=         0;                      /* For protocol::store_time */
   l_time->month=        0;
   l_time->day=          0;
@@ -630,8 +734,10 @@ fractional:
   l_time->second_part=  date[4];
   l_time->time_type= MYSQL_TIMESTAMP_TIME;
 
+  *endptr= str;
+
   /* Check if the value is valid and fits into MYSQL_TIME range */
-  if (check_time_range(l_time, 6, &status->warnings))
+  if (check_time_range_internal(l_time, max_hour, 6, &status->warnings))
     return TRUE;
 
   /* Check if there is garbage at end of the MYSQL_TIME specification */
@@ -649,8 +755,7 @@ fractional:
   return FALSE;
 
 err:
-  bzero((char*) l_time, sizeof(*l_time));
-  l_time->time_type= MYSQL_TIMESTAMP_ERROR;
+  *endptr= str;
   return TRUE;
 }
 
@@ -659,8 +764,9 @@ err:
   Check 'time' value to lie in the MYSQL_TIME range
 
   SYNOPSIS:
-    check_time_range()
+    check_time_range_internal()
     time     pointer to MYSQL_TIME value
+    ulong    max_hour - maximum allowed hour value
     uint     dec
     warning  set MYSQL_TIME_WARN_OUT_OF_RANGE flag if the value is out of range
 
@@ -674,9 +780,10 @@ err:
     1        time value is invalid
 */
 
-int check_time_range(struct st_mysql_time *my_time, uint dec, int *warning) 
+int check_time_range_internal(struct st_mysql_time *my_time,
+                              ulong max_hour, uint dec, int *warning)
 {
-  longlong hour;
+  ulonglong hour;
   static ulong max_sec_part[TIME_SECOND_PART_DIGITS+1]= {000000, 900000, 990000,
                                              999000, 999900, 999990, 999999};
 
@@ -691,14 +798,14 @@ int check_time_range(struct st_mysql_time *my_time, uint dec, int *warning)
   if (dec == AUTO_SEC_PART_DIGITS)
     dec= TIME_SECOND_PART_DIGITS;
 
-  if (hour <= TIME_MAX_HOUR &&
-      (hour != TIME_MAX_HOUR || my_time->minute != TIME_MAX_MINUTE ||
+  if (hour <= max_hour &&
+      (hour != max_hour || my_time->minute != TIME_MAX_MINUTE ||
        my_time->second != TIME_MAX_SECOND ||
        my_time->second_part <= max_sec_part[dec]))
     return 0;
 
   my_time->day= 0;
-  my_time->hour= TIME_MAX_HOUR;
+  my_time->hour= max_hour;
   my_time->minute= TIME_MAX_MINUTE;
   my_time->second= TIME_MAX_SECOND;
   my_time->second_part= max_sec_part[dec];