• unknown's avatar
    Fixed bug#16377: result of DATE/TIME functions were compared as strings which · 47311e8e
    unknown authored
    can lead to a wrong result.
    
    All date/time functions has the STRING result type thus their results are
    compared as strings. The string date representation allows a user to skip 
    some of leading zeros. This can lead to wrong comparison result if a date/time 
    function result is compared to such a string constant.
    
    The idea behind this bug fix is to compare results of date/time functions
    and data/time constants as ints, because that date/time representation is 
    more exact. To achieve this the agg_cmp_type() is changed to take in the
    account that a date/time field or an date/time item should be compared 
    as ints.
    
    This bug fix is partially back ported from 5.0.
    
    The agg_cmp_type() function now accepts THD as one of parameters. 
    In addition, it now checks if a date/time field/function is present in the
    list. If so, it tries to coerce all constants to INT to make date/time
    comparison return correct result. The field for the constant coercion is
    taken from the Item_field or constructed from the Item_func. In latter case
    the constructed field will be freed after conversion of all constant items.
    Otherwise the result is same as before - aggregated with help of the
    item_cmp_type() function.
    
    From the Item_func_between::fix_length_and_dec() function removed the part
    which was converting date/time constants to int if possible. Now this is 
    done by the agg_cmp_type() function.
    
    The new function result_as_longlong() is added to the Item class. 
    It indicates that the item is a date/time item and result of it can be
    compared as int. Such items are date/time fields/functions.
    
    Correct val_int() methods are implemented for classes Item_date_typecast, 
    Item_func_makedate, Item_time_typecast, Item_datetime_typecast. All these
    classes are derived from Item_str_func and Item_str_func::val_int() converts
    its string value to int without regard to the date/time type of these items.
    
    Arg_comparator::set_compare_func() and Arg_comparator::set_cmp_func()
    functions are changed to substitute result type of an item with the INT_RESULT
    if the item is a date/time item and another item is a constant. This is done
    to get a correct result of comparisons like date_time_function() = string_constant.
    
    
    mysql-test/r/cast.result:
      Fixed wrong test case result after bug fix#16377.
    sql/item_timefunc.h:
      Fixed bug#16377: result of DATE/TIME functions were compared as strings which
      can lead to a wrong result.
      The result_as_longlong() function is set to return TRUE for these classes:
      Item_date, Item_date_func, Item_func_curtime, Item_func_sec_to_time,
      Item_date_typecast, Item_time_typecast, Item_datetime_typecast,
      Item_func_makedate.
    sql/item_timefunc.cc:
      Fixed bug#16377: result of DATE/TIME functions were compared as strings which
      can lead to a wrong result.Correct val_int() methods are implemented for classes Item_date_typecast, 
      Item_func_makedate, Item_time_typecast, Item_datetime_typecast.
    sql/item_cmpfunc.h:
      Fixed bug#16377: result of DATE/TIME functions were compared as strings which
      can lead to a wrong result.
      Arg_comparator::set_compare_func() and Arg_comparator::set_cmp_func()
      functions are changed to substitute result type of an item with the INT_RESULT
      if the item is a date/time item and another item is a constant.
    sql/field.cc:
      Fixed bug#16377: result of DATE/TIME functions were compared as strings which
      can lead to a wrong result.
      Field::set_warning(), Field::set_datetime_warning() now use current_thd to get thd if table isn't set.
    sql/item_cmpfunc.cc:
      Fixed bug#16377: result of DATE/TIME functions were compared as strings which
      can lead to a wrong result.
      The agg_cmp_type() function now accepts THD as one of parameters. 
      In addition, it now checks if a date/time field/function is present in the
      list. If so, it tries to coerce all constants to INT to make date/time
      comparison return correct result. The field for the constant coercion is
      taken from the Item_field or constructed from the Item_func. In latter case
      the constructed field will be freed after conversion of all constant items.
      Otherwise the result is same as before - aggregated with help of the
      item_cmp_type() function.
    sql/item.h:
      The new function result_as_longlong() is added to the Item class. 
      It indicates that the item is a date/time item and result of it can be
      compared as int. Such items are date/time fields/functions.
    mysql-test/t/func_time.test:
      Added test case fot bug#16377: result of DATE/TIME functions were compared as strings which
      can lead to a wrong result.
    mysql-test/r/func_time.result:
      Added test case fot bug#16377: result of DATE/TIME functions were compared as strings which
      can lead to a wrong result.
    47311e8e
func_time.test 18.4 KB