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, 12 insertions, 0 deletions

diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 163b1591e52..95f1dfa75d1 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -339,6 +339,7 @@ public:
   {
     return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
   }  
+  bool result_as_longlong() { return TRUE; }
 };
 
 
@@ -354,6 +355,7 @@ public:
   {
     return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
   }
+  bool result_as_longlong() { return TRUE; }
 };
 
 
@@ -383,6 +385,7 @@ public:
     TIME representation using UTC-SYSTEM or per-thread time zone.
   */
   virtual void store_now_in_TIME(TIME *now_time)=0;
+  bool result_as_longlong() { return TRUE; }
 };
 
 
@@ -588,6 +591,7 @@ public:
   {
     return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
   }
+  bool result_as_longlong() { return TRUE; }
 };
 
 /*
@@ -715,6 +719,8 @@ public:
     max_length= 10;
     maybe_null= 1;
   }
+  bool result_as_longlong() { return TRUE; }
+  longlong val_int();
 };
 
 
@@ -731,6 +737,8 @@ public:
   {
     return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
   }
+  bool result_as_longlong() { return TRUE; }
+  longlong val_int();
 };
 
 
@@ -746,6 +754,8 @@ public:
   {
     return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
   }
+  bool result_as_longlong() { return TRUE; }
+  longlong val_int();
 };
 
 class Item_func_makedate :public Item_str_func
@@ -764,6 +774,8 @@ public:
   {
     return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
   }
+  bool result_as_longlong() { return TRUE; }
+  longlong val_int();
 };