Bug#34384: Slow down on constant conversion.

When values of different types are compared they're converted to a type that
allows correct comparison. This conversion is done for each comparison and
takes some time. When a constant is being compared it's possible to cache the
value after conversion to speedup comparison. In some cases (large dataset,
complex WHERE condition with many type conversions) query might be executed
7% faster.

A test case isn't provided because all changes are internal and isn't visible
outside.

The behavior of the Item_cache is changed to cache values on the first request
of cached value rather than at the moment of storing item to be cached.
A flag named value_cached is added to the Item_cache class. It's set to TRUE
when cache holds the value of the last stored item.
Function named cache_value() is added to the Item_cache class and derived classes.
This function actually caches the value of the saved item.
Item_cache_xxx::store functions now only store item to be cached and set
value_cached flag to FALSE.
Item_cache_xxx::val_xxx functions are changed to call cache_value function
prior to returning cached value if value_cached is FALSE.
The Arg_comparator::set_cmp_func function now calls cache_converted_constant
to cache constants if they need a type conversion.
The Item_cache::get_cache function is overloaded to allow setting of the
cache type.
The cache_converted_constant function is added to the Arg_comparator class.
It checks whether a value can and should be cached and if so caches it.

sql/item.cc:
  Bug#34384: Slow down on constant conversion.
  Function named cache_value() is added to the Item_cache class and derived classes.
  This function actually caches the value of the saved item.
  Item_cache_xxx::store functions now only store item to be cached and set
  value_cached flag to FALSE.
  Item_cache_xxx::val_xxx functions are changed to call cache_value function
  prior to returning cached value if value_cached is FALSE.
  The Item_cache::get_cache function is overloaded to allow setting of the
  cache type.
sql/item.h:
  Bug#34384: Slow down on constant conversion.
  A flag named value_cached is added to the Item_cache class. It's set to TRUE
  when we need to start caching values when the store method is called.
  Function named cache_value() is added to the Item_cache class and derived classes.
sql/item_cmpfunc.cc:
  Bug#34384: Slow down on constant conversion.
  A helper function cache_converted_constant is added to the Arg_comparator class.
  It checks whether a given item can and should be cached and caches it if so.
  The Arg_comparator::set_cmp_func function now calls cache_converted_constant
  to cache constants if they need a type conversion.
sql/item_cmpfunc.h:
  Bug#34384: Slow down on constant conversion.
  The cache_converted_constant function is added to the Arg_comparator class.
  It checks whether a value can and should be cached and if so caches it.
sql/item_subselect.cc:
  Bug#34384: Slow down on constant conversion.
  Force immediate caching of subselect result.
sql/item_xmlfunc.cc:
  Bug#34384: Slow down on constant conversion.
sql/sp_rcontext.cc:
  Bug#34384: Slow down on constant conversion.
  Force immediate caching of values of an SP CASE function.

1 files changed, 43 insertions, 4 deletions

diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index c29031d25b5..97ad45d9774 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -855,13 +855,13 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
 {
   enum enum_date_cmp_type cmp_type;
   ulonglong const_value= (ulonglong)-1;
+  thd= current_thd;
+  owner= owner_arg;
   a= a1;
   b= a2;
 
   if ((cmp_type= can_compare_as_dates(*a, *b, &const_value)))
   {
-    thd= current_thd;
-    owner= owner_arg;
     a_type= (*a)->field_type();
     b_type= (*b)->field_type();
     a_cache= 0;
@@ -869,6 +869,10 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
 
     if (const_value != (ulonglong)-1)
     {
+      /*
+        cache_converted_constant can't be used here because it can't
+        correctly convert a DATETIME value from string to int representation.
+      */
       Item_cache_int *cache= new Item_cache_int();
       /* Mark the cache as non-const to prevent re-caching. */
       cache->set_used_tables(1);
@@ -894,8 +898,6 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
            (*b)->field_type() == MYSQL_TYPE_TIME)
   {
     /* Compare TIME values as integers. */
-    thd= current_thd;
-    owner= owner_arg;
     a_cache= 0;
     b_cache= 0;
     is_nulls_eq= test(owner && owner->functype() == Item_func::EQUAL_FUNC);
@@ -914,10 +916,46 @@ int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
       return 1;
   }
 
+  a= cache_converted_constant(thd, a, &a_cache, type);
+  b= cache_converted_constant(thd, b, &b_cache, type);
   return set_compare_func(owner_arg, type);
 }
 
 
+/**
+  Convert and cache a constant.
+
+  @param value      [in]  An item to cache
+  @param cache_item [out] Placeholder for the cache item
+  @param type       [in]  Comparison type
+
+  @details
+    When given item is a constant and its type differs from comparison type
+    then cache its value to avoid type conversion of this constant on each
+    evaluation. In this case the value is cached and the reference to the cache
+    is returned.
+    Original value is returned otherwise.
+
+  @return cache item or original value.
+*/
+
+Item** Arg_comparator::cache_converted_constant(THD *thd, Item **value,
+                                                Item **cache_item,
+                                                Item_result type)
+{
+  /* Don't need cache if doing context analysis only. */
+  if (!thd->is_context_analysis_only() &&
+      (*value)->const_item() && type != (*value)->result_type())
+  {
+    Item_cache *cache= Item_cache::get_cache(*value, type);
+    cache->store(*value);
+    *cache_item= cache;
+    return cache_item;
+  }
+  return value;
+}
+
+
 void Arg_comparator::set_datetime_cmp_func(Item **a1, Item **b1)
 {
   thd= current_thd;
@@ -1556,6 +1594,7 @@ longlong Item_in_optimizer::val_int()
   bool tmp;
   DBUG_ASSERT(fixed == 1);
   cache->store(args[0]);
+  cache->cache_value();
   
   if (cache->null_value)
   {