Bug#49771: Incorrect MIN/MAX for date/time values.

This bug is a design flaw of the fix for the bug#33546. It assumed that an
item can be used only in one comparison context, but actually it isn't the
case. Item_cache_datetime is used to store result for MIX/MAX aggregate
functions. Because Arg_comparator always compares datetime values as INTs when
possible the Item_cache_datetime most time caches only INT value. But
since all datetime values has STRING result type MIN/MAX functions are asked
for a STRING value when the result is being sent to a client. The
Item_cache_datetime was designed to avoid conversions and get INT/STRING
values from an underlying item, but at the moment the values is asked
underlying item doesn't hold it anymore thus wrong result is returned.
Beside that MIN/MAX aggregate functions was wrongly initializing cached result
and this led to a wrong result.

The Item::has_compatible_context helper function is added. It checks whether
this and given items has the same comparison context or can be compared as
DATETIME values by Arg_comparator. The equality propagation optimization is
adjusted to take into account that items which being compared as DATETIME
can have different comparison contexts.
The Item_cache_datetime now converts cached INT value to a correct STRING
DATETIME value by means of number_to_datetime & my_TIME_to_str functions.
The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
It sets comparison context of items being compared as DATETIMEs to INT if
items will be compared as longlong.
The Item_sum_hybrid::setup function now correctly initializes its result
value.
In order to avoid unnecessary conversions Item_sum_hybrid now states that it
can provide correct longlong value if the item being aggregated can do it
too.

mysql-test/r/group_by.result:
  Added a test case for the bug#49771.
sql/item.cc:
  Bug#49771: Incorrect MIN/MAX for date/time values.
  The equality propagation mechanism is adjusted to take into account that
  items which being compared as DATETIME can have different comparison
  contexts.
  The Item_cache_datetime now converts cached INT value to a correct STRING
  DATETIME/TIME value.
sql/item.h:
  Bug#49771: Incorrect MIN/MAX for date/time values.
  The Item::has_compatible_context helper function is added. It checks whether
  this and given items has the same comparison context or can be compared as
  DATETIME values by Arg_comparator.
  Added Item_cache::clear helper function.
sql/item_cmpfunc.cc:
  Bug#49771: Incorrect MIN/MAX for date/time values.
  The Arg_comparator::set_cmp_func now sets the correct comparison context
  for items being compared as DATETIME values.
sql/item_cmpfunc.h:
  Bug#49771: Incorrect MIN/MAX for date/time values.
  The Arg_comparator::set_cmp_context_for_datetime helper function is added. 
  It sets comparison context of items being compared as DATETIMEs to INT if
  items will be compared as longlong.
sql/item_sum.cc:
  Bug#49771: Incorrect MIN/MAX for date/time values.
  The Item_sum_hybrid::setup function now correctly initializes its result
  value.
sql/item_sum.h:
  Bug#49771: Incorrect MIN/MAX for date/time values.
  In order to avoid unnecessary conversions Item_sum_hybrid now states that it
  can provide correct longlong value if the item being aggregated can do it
  too.

1 files changed, 5 insertions, 0 deletions

diff --git a/sql/item_sum.h b/sql/item_sum.h
index b4539995632..2a722b93165 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -1010,6 +1010,11 @@ protected:
   void no_rows_in_result();
   Field *create_tmp_field(bool group, TABLE *table,
 			  uint convert_blob_length);
+  /*
+    MIN/MAX uses Item_cache_datetime for storing DATETIME values, thus
+    in this case a correct INT value can be provided.
+  */
+  bool result_as_longlong() { return args[0]->result_as_longlong(); }
 };