• Alexander Barkov's avatar
    MDEV-17351 Wrong results for GREATEST,TIMESTAMP,ADDTIME with an out-of-range TIME-alike argument · b639fe2b
    Alexander Barkov authored
    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'
    b639fe2b
sql_time.cc 40.9 KB