Fixed bug#16377: result of DATE/TIME functions were compared as strings which

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.

1 files changed, 10 insertions, 4 deletions

diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index ade09113c63..08d9de6ffd6 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -43,8 +43,11 @@ public:
   int set_compare_func(Item_bool_func2 *owner, Item_result type);
   inline int set_compare_func(Item_bool_func2 *owner_arg)
   {
-    return set_compare_func(owner_arg, item_cmp_type((*a)->result_type(),
-						     (*b)->result_type()));
+    Item_result ar= (*a)->result_as_longlong() && (*b)->const_item() ?
+                      INT_RESULT : (*a)->result_type();
+    Item_result br= (*b)->result_as_longlong() && (*a)->const_item() ?
+                      INT_RESULT : (*b)->result_type();
+    return set_compare_func(owner_arg, item_cmp_type(ar, br));
   }
   inline int set_cmp_func(Item_bool_func2 *owner_arg,
 			  Item **a1, Item **a2,
@@ -57,8 +60,11 @@ public:
   inline int set_cmp_func(Item_bool_func2 *owner_arg,
 			  Item **a1, Item **a2)
   {
-    return set_cmp_func(owner_arg, a1, a2, item_cmp_type((*a1)->result_type(),
-							 (*a2)->result_type()));
+    Item_result ar= (*a1)->result_as_longlong() && (*a2)->const_item() ?
+                      INT_RESULT : (*a1)->result_type();
+    Item_result br= (*a2)->result_as_longlong() && (*a1)->const_item() ?
+                      INT_RESULT : (*a2)->result_type();
+    return set_cmp_func(owner_arg, a1, a2, item_cmp_type(ar, br));
   }
   inline int compare() { return (this->*func)(); }