Subquery cache (MWL#66) added.

libmysqld/Makefile.am:
  The new file added.
mysql-test/r/index_merge_myisam.result:
  subquery_cache optimization option added.
mysql-test/r/myisam_mrr.result:
  subquery_cache optimization option added.
mysql-test/r/subquery_cache.result:
  The subquery cache tests added.
mysql-test/r/subselect3.result:
  Subquery cache switched off to avoid changing read statistics.
mysql-test/r/subselect3_jcl6.result:
  Subquery cache switched off to avoid changing read statistics.
mysql-test/r/subselect_no_mat.result:
  subquery_cache optimization option added.
mysql-test/r/subselect_no_opts.result:
  subquery_cache optimization option added.
mysql-test/r/subselect_no_semijoin.result:
  subquery_cache optimization option added.
mysql-test/r/subselect_sj.result:
  subquery_cache optimization option added.
mysql-test/r/subselect_sj_jcl6.result:
  subquery_cache optimization option added.
mysql-test/t/subquery_cache.test:
  The subquery cache tests added.
mysql-test/t/subselect3.test:
  Subquery cache switched off to avoid changing read statistics.
sql/CMakeLists.txt:
  The new file added.
sql/Makefile.am:
  The new files added.
sql/item.cc:
  Expression cache item (Item_cache_wrapper) added.
  Item_ref and Item_field fixed for correct usage of result field and fast resolwing in SP.
sql/item.h:
  Expression cache item (Item_cache_wrapper) added.
  Item_ref and Item_field fixed for correct usage of result field and fast resolwing in SP.
sql/item_cmpfunc.cc:
  Subquery cache added.
sql/item_cmpfunc.h:
  Subquery cache added.
sql/item_subselect.cc:
  Subquery cache added.
sql/item_subselect.h:
  Subquery cache added.
sql/item_sum.cc:
  Registration of subquery parameters added.
sql/mysql_priv.h:
  subquery_cache optimization option added.
sql/mysqld.cc:
  subquery_cache optimization option added.
sql/opt_range.cc:
  Fix due to subquery cache.
sql/opt_subselect.cc:
  Parameters of the function cahnged.
sql/procedure.h:
  .h file guard added.
sql/sql_base.cc:
  Registration of subquery parameters added.
sql/sql_class.cc:
  Option to allow add indeces to temporary table.
sql/sql_class.h:
  Item iterators added.
  Option to allow add indeces to temporary table.
sql/sql_expression_cache.cc:
  Expression cache for caching subqueries added.
sql/sql_expression_cache.h:
  Expression cache for caching subqueries added.
sql/sql_lex.cc:
  Registration of subquery parameters added.
sql/sql_lex.h:
  Registration of subqueries and subquery parameters added.
sql/sql_select.cc:
  Subquery cache added.
sql/sql_select.h:
  Subquery cache added.
sql/sql_union.cc:
  A new parameter to the function added.
sql/sql_update.cc:
  A new parameter to the function added.
sql/table.cc:
  Procedures to manage temporarty tables index added.
sql/table.h:
  Procedures to manage temporarty tables index added.
storage/maria/ha_maria.cc:
  Fix of handler to allow destoy a table in case of error during the table creation.
storage/maria/ha_maria.h:
  .h file guard added.
storage/myisam/ha_myisam.cc:
  Fix of handler to allow destoy a table in case of error during the table creation.

27 files changed, 1847 insertions, 128 deletions

diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index b59080adfb7..bae77f80b22 100755
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -82,6 +82,7 @@ SET (SQL_SOURCE
                opt_subselect.cc
                opt_index_cond_pushdown.cc
                create_options.cc
+               sql_expression_cache.cc
                ${PROJECT_SOURCE_DIR}/sql/sql_yacc.cc
                ${PROJECT_SOURCE_DIR}/sql/sql_yacc.h
                ${PROJECT_SOURCE_DIR}/include/mysqld_error.h
diff --git a/sql/Makefile.am b/sql/Makefile.am
index 6f5f03f8637..13a60ba5c79 100644
--- a/sql/Makefile.am
+++ b/sql/Makefile.am
@@ -81,7 +81,8 @@ noinst_HEADERS =	item.h item_func.h item_sum.h item_cmpfunc.h \
 			sql_partition.h partition_info.h partition_element.h \
 			contributors.h sql_servers.h \
                         multi_range_read.h \
-			create_options.h
+			create_options.h \
+			sql_expression_cache.h
 
 mysqld_SOURCES =	sql_lex.cc sql_handler.cc sql_partition.cc \
 			item.cc item_sum.cc item_buff.cc item_func.cc \
@@ -131,7 +132,7 @@ mysqld_SOURCES =	sql_lex.cc sql_handler.cc sql_partition.cc \
 			sql_servers.cc event_parse_data.cc \
                         opt_table_elimination.cc create_options.cc \
 			multi_range_read.cc \
-			opt_index_cond_pushdown.cc
+			opt_index_cond_pushdown.cc sql_expression_cache.cc
 
 nodist_mysqld_SOURCES =	mini_client_errors.c pack.c client.c my_time.c my_user.c client_plugin.c
 
diff --git a/sql/item.cc b/sql/item.cc
index dd303c93844..274a467f4e3 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -28,6 +28,9 @@
 
 const String my_null_string("NULL", 4, default_charset_info);
 
+static int save_field_in_field(Field *from, my_bool *null_value,
+                               Field *to, bool no_conversions);
+
 /****************************************************************************/
 
 /* Hybrid_type_traits {_real} */
@@ -541,6 +544,36 @@ Item* Item::transform(Item_transformer transformer, uchar *arg)
 }
 
 
+/**
+  Create and set up an expression cache for this item
+
+  @param thd             Thread handle
+  @param depends_on      List of the expression parameters
+
+  @details
+  The function creates an expression cache for an item and its parameters
+  specified by the 'depends_on' list. Then the expression cache is placed
+  into a cache wrapper that is returned as the result of the function.
+
+  @returns
+  A pointer to created wrapper item if successful, NULL - otherwise
+*/
+
+Item* Item::set_expr_cache(THD *thd, List<Item *> &depends_on)
+{
+  DBUG_ENTER("Item::set_expr_cache");
+  Item_cache_wrapper *wrapper;
+  if ((wrapper= new Item_cache_wrapper(this)) &&
+      !wrapper->fix_fields(thd, (Item**)&wrapper))
+  {
+    if (wrapper->set_cache(thd, depends_on))
+      DBUG_RETURN(this);
+    DBUG_RETURN(wrapper);
+  }
+  DBUG_RETURN(this);
+}
+
+
 Item_ident::Item_ident(Name_resolution_context *context_arg,
                        const char *db_name_arg,const char *table_name_arg,
 		       const char *field_name_arg)
@@ -549,7 +582,7 @@ Item_ident::Item_ident(Name_resolution_context *context_arg,
    db_name(db_name_arg), table_name(table_name_arg),
    field_name(field_name_arg),
    alias_name_used(FALSE), cached_field_index(NO_CACHED_FIELD_INDEX),
-   cached_table(0), depended_from(0)
+   cached_table(0), depended_from(0), can_be_depended(TRUE)
 {
   name = (char*) field_name_arg;
 }
@@ -561,7 +594,7 @@ Item_ident::Item_ident(TABLE_LIST *view_arg, const char *field_name_arg)
    db_name(NullS), table_name(view_arg->alias),
    field_name(field_name_arg),
    alias_name_used(FALSE), cached_field_index(NO_CACHED_FIELD_INDEX),
-   cached_table(NULL), depended_from(NULL)
+   cached_table(NULL), depended_from(NULL), can_be_depended(TRUE)
 {
   name = (char*) field_name_arg;
 }
@@ -583,7 +616,8 @@ Item_ident::Item_ident(THD *thd, Item_ident *item)
    alias_name_used(item->alias_name_used),
    cached_field_index(item->cached_field_index),
    cached_table(item->cached_table),
-   depended_from(item->depended_from)
+   depended_from(item->depended_from),
+   can_be_depended(item->can_be_depended)
 {}
 
 void Item_ident::cleanup()
@@ -601,7 +635,8 @@ void Item_ident::cleanup()
   db_name= orig_db_name; 
   table_name= orig_table_name;
   field_name= orig_field_name;
-  depended_from= 0;
+  /* Store if this Item was depended */
+  can_be_depended= test(depended_from);
   DBUG_VOID_RETURN;
 }
 
@@ -1424,7 +1459,7 @@ void Item::split_sum_func2(THD *thd, Item **ref_pointer_array,
   /* An item of type Item_sum  is registered <=> ref_by != 0 */ 
   if (type() == SUM_FUNC_ITEM && skip_registered && 
       ((Item_sum *) this)->ref_by)
-    return;                                                 
+    return;
   if ((type() != SUM_FUNC_ITEM && with_sum_func) ||
       (type() == FUNC_ITEM &&
        (((Item_func *) this)->functype() == Item_func::ISNOTNULLTEST_FUNC ||
@@ -1745,7 +1780,10 @@ bool agg_item_set_converter(DTCollation &coll, const char *fname,
         subselect transformation does not happen in view_prepare_mode
         and thus val_...() methods can not be called for const items.
       */
-      bool resolve_const= ((*arg)->type() == Item::SUBSELECT_ITEM &&
+      bool resolve_const= (((*arg)->type() == Item::SUBSELECT_ITEM ||
+                            ((*arg)->get_cached_item() &&
+                             (*arg)->get_cached_item()->type() ==
+                             Item::SUBSELECT_ITEM)) &&
                            thd->lex->view_prepare_mode) ? FALSE : TRUE;
       conv= new Item_func_conv_charset(*arg, coll.collation, resolve_const);
     }
@@ -2131,6 +2169,12 @@ bool Item_field::get_time(MYSQL_TIME *ltime)
   return 0;
 }
 
+void Item_field::save_result(Field *to)
+{
+  save_field_in_field(result_field, &null_value, to, TRUE);
+}
+
+
 double Item_field::val_result()
 {
   if ((null_value=result_field->is_null()))
@@ -3596,6 +3640,15 @@ bool Item::fix_fields(THD *thd, Item **ref)
   return FALSE;
 }
 
+
+void Item_ref_null_helper::save_val(Field *to)
+{
+  DBUG_ASSERT(fixed == 1);
+  (*ref)->save_val(to);
+  owner->was_null|= null_value= (*ref)->null_value;
+}
+
+
 double Item_ref_null_helper::val_real()
 {
   DBUG_ASSERT(fixed == 1);
@@ -3668,7 +3721,7 @@ static bool mark_as_dependent(THD *thd, SELECT_LEX *last, SELECT_LEX *current,
   const char *table_name= (resolved_item->table_name ?
                            resolved_item->table_name : "");
   /* store pointer on SELECT_LEX from which item is dependent */
-  if (mark_item)
+  if (mark_item && mark_item->can_be_depended)
     mark_item->depended_from= last;
   if (current->mark_as_dependent(thd, last, /** resolved_item psergey-thu
     **/mark_item))
@@ -4134,7 +4187,9 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
                             ((ref_type == REF_ITEM || ref_type == FIELD_ITEM) ?
                              (Item_ident*) (*reference) :
                              0));
-                            
+          context->select_lex->
+              register_dependency_item(last_checked_context->select_lex,
+                                       reference);
           /*
             A reference to a view field had been found and we
             substituted it instead of this Item (find_field_in_tables
@@ -4236,6 +4291,10 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
     mark_as_dependent(thd, last_checked_context->select_lex,
                       context->select_lex, rf,
                       rf);
+    context->select_lex->
+              register_dependency_item(last_checked_context->select_lex,
+                                       reference);
+
     return 0;
   }
   else
@@ -4243,6 +4302,9 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
     mark_as_dependent(thd, last_checked_context->select_lex,
                       context->select_lex,
                       this, (Item_ident*)*reference);
+    context->select_lex->
+              register_dependency_item(last_checked_context->select_lex,
+                                       reference);
     if (last_checked_context->select_lex->having_fix_field)
     {
       Item_ref *rf;
@@ -4549,6 +4611,7 @@ void Item_field::cleanup()
 {
   DBUG_ENTER("Item_field::cleanup");
   Item_ident::cleanup();
+  depended_from= NULL;
   /*
     Even if this object was created by direct link to field in setup_wild()
     it will be linked correctly next time by name of field and table alias.
@@ -5107,47 +5170,69 @@ void Item_field::make_field(Send_field *tmp_field)
 
 
 /**
-  Set a field's value from a item.
-*/
+  Save a field value in another field
 
-void Item_field::save_org_in_field(Field *to)
-{
-  if (field->is_null())
-  {
-    null_value=1;
-    set_field_to_null_with_conversions(to, 1);
-  }
-  else
-  {
-    to->set_notnull();
-    field_conv(to,field);
-    null_value=0;
-  }
-}
+  @param from             Field to take the value from
+  @param [out] null_value Pointer to the null_value flag to set
+  @param to               Field to save the value in
+  @param no_conversions   How to deal with NULL value
 
-int Item_field::save_in_field(Field *to, bool no_conversions)
+  @details
+  The function takes the value of the field 'from' and, if this value
+  is not null, it saves in the field 'to' setting off the flag referenced
+  by 'null_value'. Otherwise this flag is set on and field 'to' is
+  also set to null possibly with conversion.
+
+  @note
+  This function is used by the functions Item_field::save_in_field,
+  Item_field::save_org_in_field and Item_ref::save_in_field
+
+  @retval FALSE OK
+  @retval TRUE  Error
+
+*/
+
+static int save_field_in_field(Field *from, my_bool *null_value,
+                               Field *to, bool no_conversions)
 {
   int res;
-  if (result_field->is_null())
+  DBUG_ENTER("save_field_in_field");
+  if (from->is_null())
   {
-    null_value=1;
-    return set_field_to_null_with_conversions(to, no_conversions);
+    (*null_value)= 1;
+    DBUG_RETURN(set_field_to_null_with_conversions(to, no_conversions));
   }
   to->set_notnull();
 
   /*
     If we're setting the same field as the one we're reading from there's 
     nothing to do. This can happen in 'SET x = x' type of scenarios.
-  */  
-  if (to == result_field)
+  */
+  if (to == from)
   {
-    null_value=0;
-    return 0;
+    (*null_value)= 0;
+    DBUG_RETURN(0);
   }
 
-  res= field_conv(to,result_field);
-  null_value=0;
-  return res;
+  res= field_conv(to, from);
+  (*null_value)= 0;
+  DBUG_RETURN(res);
+}
+
+
+/**
+  Set a field's value from a item.
+*/
+
+void Item_field::save_org_in_field(Field *to)
+{
+  save_field_in_field(field, &null_value, to, TRUE);
+}
+
+
+int Item_field::save_in_field(Field *to, bool no_conversions)
+{
+  return save_field_in_field(result_field, &null_value, to, no_conversions);
 }
 
 
@@ -6037,6 +6122,9 @@ bool Item_ref::fix_fields(THD *thd, Item **reference)
                                 refer_type == FIELD_ITEM) ?
                                (Item_ident*) (*reference) :
                                0));
+           context->select_lex->
+              register_dependency_item(last_checked_context->select_lex,
+                                       reference);
             /*
               view reference found, we substituted it instead of this
               Item, so can quit
@@ -6087,6 +6175,9 @@ bool Item_ref::fix_fields(THD *thd, Item **reference)
         thd->change_item_tree(reference, fld);
         mark_as_dependent(thd, last_checked_context->select_lex,
                           thd->lex->current_select, fld, fld);
+        context->select_lex->
+              register_dependency_item(last_checked_context->select_lex,
+                                       reference);
         /*
           A reference is resolved to a nest level that's outer or the same as
           the nest level of the enclosing set function : adjust the value of
@@ -6110,6 +6201,9 @@ bool Item_ref::fix_fields(THD *thd, Item **reference)
       DBUG_ASSERT(*ref && (*ref)->fixed);
       mark_as_dependent(thd, last_checked_context->select_lex,
                         context->select_lex, this, this);
+      context->select_lex->
+              register_dependency_item(last_checked_context->select_lex,
+                                       reference);
       /*
         A reference is resolved to a nest level that's outer or the same as
         the nest level of the enclosing set function : adjust the value of
@@ -6124,7 +6218,11 @@ bool Item_ref::fix_fields(THD *thd, Item **reference)
   }
   else
   {
-    ;
+    if (depended_from && reference)
+    {
+      DBUG_ASSERT(context->select_lex != depended_from);
+      context->select_lex->register_dependency_item(depended_from, reference);
+    }
     /*
       It could be that we're referring to something that's in ancestor selects.
       We must make an appropriate mark_as_dependent() call for each such
@@ -6315,6 +6413,25 @@ bool Item_ref::val_bool_result()
 }
 
 
+void Item_ref::save_result(Field *to)
+{
+  if (result_field)
+  {
+    save_field_in_field(result_field, &null_value, to, TRUE);
+    return;
+  }
+  (*ref)->save_result(to);
+  null_value= (*ref)->null_value;
+}
+
+
+void Item_ref::save_val(Field *to)
+{
+  (*ref)->save_result(to);
+  null_value= (*ref)->null_value;
+}
+
+
 double Item_ref::val_real()
 {
   DBUG_ASSERT(fixed);
@@ -6432,6 +6549,13 @@ void Item_ref_null_helper::print(String *str, enum_query_type query_type)
 }
 
 
+void Item_direct_ref::save_val(Field *to)
+{
+  (*ref)->save_val(to);
+  null_value=(*ref)->null_value;
+}
+
+
 double Item_direct_ref::val_real()
 {
   double tmp=(*ref)->val_real();
@@ -6484,6 +6608,364 @@ bool Item_direct_ref::get_date(MYSQL_TIME *ltime,uint fuzzydate)
 }
 
 
+Item_cache_wrapper::~Item_cache_wrapper()
+{
+  delete expr_cache;
+  /* expr_value is Item so it will be destroyed from list of Items */
+}
+
+
+Item_cache_wrapper::Item_cache_wrapper(Item *item_arg)
+:orig_item(item_arg), expr_cache(NULL), expr_value(NULL)
+{
+  DBUG_ASSERT(orig_item->fixed);
+  max_length= orig_item->max_length;
+  maybe_null= orig_item->maybe_null;
+  decimals=   orig_item->decimals;
+  collation.set(orig_item->collation);
+  with_sum_func= orig_item->with_sum_func;
+  unsigned_flag= orig_item->unsigned_flag;
+  name= item_arg->name;
+  name_length= item_arg->name_length;
+
+  if ((expr_value= Item_cache::get_cache(orig_item)))
+    expr_value->setup(orig_item);
+
+  fixed= 1;
+}
+
+
+/**
+  Prepare the expression cache wrapper (do nothing)
+
+  @retval FALSE OK
+*/
+
+bool Item_cache_wrapper::fix_fields(THD *thd  __attribute__((unused)),
+                                    Item **it __attribute__((unused)))
+{
+  DBUG_ASSERT(orig_item->fixed);
+  DBUG_ASSERT(fixed);
+  return FALSE;
+}
+
+
+/**
+  Clean the expression cache wrapper up before reusing it.
+*/
+
+void Item_cache_wrapper::cleanup()
+{
+  delete expr_cache;
+  expr_cache= 0;
+  // expr_value is Item so it will be destroyed from list of Items
+  expr_value= 0;
+}
+
+
+/**
+  Create an expression cache that uses a temporary table
+
+  @param thd           Thread handle
+  @param depends_on    Parameters of the expression to create cache for
+
+  @details
+  The function takes 'depends_on' as the list of all parameters for
+  the expression wrapped into this object and creates an expression
+  cache in a temporary table containing the field for the parameters
+  and the result of the expression.
+
+  @retval FALSE OK
+  @retval TRUE  Error
+*/
+
+bool Item_cache_wrapper::set_cache(THD *thd, List<Item*> &depends_on)
+{
+  DBUG_ENTER("Item_cache_wrapper::set_cache");
+  expr_cache= new Expression_cache_tmptable(thd, depends_on, expr_value);
+  DBUG_RETURN(expr_cache == NULL);
+}
+
+
+/**
+  Check if the current values of the parameters are in the expression cache
+
+  @details
+  The function checks whether the current set of the parameters of the
+  referenced item can be found in the expression cache. If so the function
+  returns the item by which the result of the expression can be easily
+  extracted from the cache with the corresponding val_* method.
+
+  @retval NULL    - parameters are not in the cache
+  @retval <item*> - item providing the result of the expression found in cache
+*/
+
+Item *Item_cache_wrapper::check_cache()
+{
+  DBUG_ENTER("Item_cache_wrapper::check_cache");
+  if (expr_cache)
+  {
+    Expression_cache_tmptable::result res;
+    Item *cached_value;
+    res= expr_cache->check_value(&cached_value);
+    if (res == Expression_cache_tmptable::HIT)
+      DBUG_RETURN(cached_value);
+  }
+  DBUG_RETURN(NULL);
+}
+
+
+/**
+  Get the value of the cached expression and put it in the cache
+*/
+
+inline void Item_cache_wrapper::cache()
+{
+  expr_value->store(orig_item);
+  expr_value->cache_value();
+  expr_cache->put_value(expr_value); // put in expr_cache
+}
+
+
+/**
+  Get the value of the possibly cached item into the field.
+*/
+
+void Item_cache_wrapper::save_val(Field *to)
+{
+  Item *cached_value;
+  DBUG_ENTER("Item_cache_wrapper::val_int");
+  if (!expr_cache)
+  {
+    orig_item->save_val(to);
+    null_value= orig_item->null_value;
+    DBUG_VOID_RETURN;
+  }
+
+  if ((cached_value= check_cache()))
+  {
+    cached_value->save_val(to);
+    null_value= cached_value->null_value;
+    DBUG_VOID_RETURN;
+  }
+  cache();
+  null_value= expr_value->null_value;
+  expr_value->save_val(to);
+  DBUG_VOID_RETURN;
+}
+
+
+/**
+  Get the integer value of the possibly cached item.
+*/
+
+longlong Item_cache_wrapper::val_int()
+{
+  Item *cached_value;
+  DBUG_ENTER("Item_cache_wrapper::val_int");
+  if (!expr_cache)
+  {
+    longlong tmp= orig_item->val_int();
+    null_value= orig_item->null_value;
+    DBUG_RETURN(tmp);
+  }
+
+  if ((cached_value= check_cache()))
+  {
+    longlong tmp= cached_value->val_int();
+    null_value= cached_value->null_value;
+    DBUG_RETURN(tmp);
+  }
+  cache();
+  null_value= expr_value->null_value;
+  DBUG_RETURN(expr_value->val_int());
+}
+
+
+/**
+  Get the real value of the possibly cached item
+*/
+
+double Item_cache_wrapper::val_real()
+{
+  Item *cached_value;
+  DBUG_ENTER("Item_cache_wrapper::val_real");
+  if (!expr_cache)
+  {
+    double tmp= orig_item->val_real();
+    null_value= orig_item->null_value;
+    DBUG_RETURN(tmp);
+  }
+
+  if ((cached_value= check_cache()))
+  {
+    double tmp= cached_value->val_real();
+    null_value= cached_value->null_value;
+    DBUG_RETURN(tmp);
+  }
+  cache();
+  null_value= expr_value->null_value;
+  DBUG_RETURN(expr_value->val_real());
+}
+
+
+/**
+  Get the string value of the possibly cached item
+*/
+
+String *Item_cache_wrapper::val_str(String* str)
+{
+  Item *cached_value;
+  DBUG_ENTER("Item_cache_wrapper::val_str");
+  if (!expr_cache)
+  {
+    String *tmp= orig_item->val_str(str);
+    null_value= orig_item->null_value;
+    DBUG_RETURN(tmp);
+  }
+
+  if ((cached_value= check_cache()))
+  {
+    String *tmp= cached_value->val_str(str);
+    null_value= cached_value->null_value;
+    DBUG_RETURN(tmp);
+  }
+  cache();
+  if ((null_value= expr_value->null_value))
+    DBUG_RETURN(NULL);
+  DBUG_RETURN(expr_value->val_str(str));
+}
+
+
+/**
+  Get the decimal value of the possibly cached item
+*/
+
+my_decimal *Item_cache_wrapper::val_decimal(my_decimal* decimal_value)
+{
+  Item *cached_value;
+  DBUG_ENTER("Item_cache_wrapper::val_decimal");
+  if (!expr_cache)
+  {
+    my_decimal *tmp= orig_item->val_decimal(decimal_value);
+    null_value= orig_item->null_value;
+    DBUG_RETURN(tmp);
+  }
+
+  if ((cached_value= check_cache()))
+  {
+    my_decimal *tmp= cached_value->val_decimal(decimal_value);
+    null_value= cached_value->null_value;
+    DBUG_RETURN(tmp);
+  }
+  cache();
+  if ((null_value= expr_value->null_value))
+    DBUG_RETURN(NULL);
+  DBUG_RETURN(expr_value->val_decimal(decimal_value));
+}
+
+
+/**
+  Get the boolean value of the possibly cached item
+*/
+
+bool Item_cache_wrapper::val_bool()
+{
+  Item *cached_value;
+  DBUG_ENTER("Item_cache_wrapper::val_bool");
+  if (!expr_cache)
+  {
+    bool tmp= orig_item->val_bool();
+    null_value= orig_item->null_value;
+    DBUG_RETURN(tmp);
+  }
+
+  if ((cached_value= check_cache()))
+  {
+    bool tmp= cached_value->val_bool();
+    null_value= cached_value->null_value;
+    DBUG_RETURN(tmp);
+  }
+  cache();
+  null_value= expr_value->null_value;
+  DBUG_RETURN(expr_value->val_bool());
+}
+
+
+/**
+  Check for NULL the value of the possibly cached item
+*/
+
+bool Item_cache_wrapper::is_null()
+{
+  Item *cached_value;
+  DBUG_ENTER("Item_cache_wrapper::is_null");
+  if (!expr_cache)
+  {
+    bool tmp= orig_item->is_null();
+    null_value= orig_item->null_value;
+    DBUG_RETURN(tmp);
+  }
+
+  if ((cached_value= check_cache()))
+  {
+    bool tmp= cached_value->is_null();
+    null_value= cached_value->null_value;
+    DBUG_RETURN(tmp);
+  }
+  cache();
+  DBUG_RETURN((null_value= expr_value->null_value));
+}
+
+
+/**
+  Get the date value of the possibly cached item
+*/
+
+bool Item_cache_wrapper::get_date(MYSQL_TIME *ltime, uint fuzzydate)
+{
+  Item *cached_value;
+  DBUG_ENTER("Item_cache_wrapper::get_date");
+  if (!expr_cache)
+    DBUG_RETURN((null_value= orig_item->get_date(ltime, fuzzydate)));
+
+  if ((cached_value= check_cache()))
+    DBUG_RETURN((null_value= cached_value->get_date(ltime, fuzzydate)));
+
+  cache();
+  DBUG_RETURN((null_value= expr_value->get_date(ltime, fuzzydate)));
+}
+
+
+/**
+  Get the time value of the possibly cached item
+*/
+
+bool Item_cache_wrapper::get_time(MYSQL_TIME *ltime)
+{
+  Item *cached_value;
+  DBUG_ENTER("Item_cache_wrapper::get_time");
+  if (!expr_cache)
+    DBUG_RETURN((null_value= orig_item->get_time(ltime)));
+
+  if ((cached_value= check_cache()))
+    DBUG_RETURN((null_value= cached_value->get_time(ltime)));
+
+  cache();
+  DBUG_RETURN((null_value= expr_value->get_time(ltime)));
+}
+
+
+int Item_cache_wrapper::save_in_field(Field *to, bool no_conversions)
+{
+  int res;
+  DBUG_ASSERT(!result_field);
+  res= orig_item->save_in_field(to, no_conversions);
+  null_value= orig_item->null_value;
+  return res;
+}
+
+
 /**
   Prepare referenced field then call usual Item_direct_ref::fix_fields .
 
diff --git a/sql/item.h b/sql/item.h
index 8cab1c523f2..b627729374d 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -507,7 +507,7 @@ public:
              SUBSELECT_ITEM, ROW_ITEM, CACHE_ITEM, TYPE_HOLDER,
              PARAM_ITEM, TRIGGER_FIELD_ITEM, DECIMAL_ITEM,
              XPATH_NODESET, XPATH_NODESET_CMP,
-             VIEW_FIXER_ITEM};
+             VIEW_FIXER_ITEM, EXPR_CACHE_ITEM};
 
   enum cond_result { COND_UNDEF,COND_OK,COND_TRUE,COND_FALSE };
 
@@ -737,6 +737,17 @@ public:
   */
   virtual bool val_bool();
   virtual String *val_nodeset(String*) { return 0; }
+
+  /*
+    save_val() is method of val_* family which stores value in the given
+    field.
+  */
+  virtual void save_val(Field *to) { save_org_in_field(to); }
+  /*
+    save_result() is method of val*result() family which stores value in
+    the given field.
+  */
+  virtual void save_result(Field *to) { save_val(to); }
   /* Helper functions, see item_sum.cc */
   String *val_string_from_real(String *str);
   String *val_string_from_int(String *str);
@@ -1092,6 +1103,8 @@ public:
 
   virtual Item *neg_transformer(THD *thd) { return NULL; }
   virtual Item *update_value_transformer(uchar *select_arg) { return this; }
+  virtual Item *expr_cache_insert_transformer(uchar *thd_arg) { return this; }
+  virtual bool expr_cache_is_needed(THD *) { return FALSE; }
   virtual Item *safe_charset_converter(CHARSET_INFO *tocs);
   void delete_self()
   {
@@ -1145,6 +1158,9 @@ public:
     { return Field::GEOM_GEOMETRY; };
   String *check_well_formed_result(String *str, bool send_error= 0);
   bool eq_by_collation(Item *item, bool binary_cmp, CHARSET_INFO *cs); 
+
+  Item* set_expr_cache(THD *thd, List<Item*> &depends_on);
+  virtual Item *get_cached_item() { return NULL; }
 };
 
 
@@ -1524,6 +1540,19 @@ public:
   */
   TABLE_LIST *cached_table;
   st_select_lex *depended_from;
+  /*
+    Some Items resolved in another select should not be marked as dependency
+    of the subquery where they are. During normal name resolution, we check
+    this. Stored procedures and prepared statements first try to resolve an
+    ident item using a cached table reference and field position from the
+    previous query execution (cached_table/cached_field_index). If the
+    tables were not changed, the ident matches the table/field, and we have
+    faster resolution of the ident without looking through all tables and
+    fields in the query. But in this case, we can not check all conditions
+    about this ident item dependency, so we should cache the condition in
+    this variable.
+  */
+  bool can_be_depended;
   Item_ident(Name_resolution_context *context_arg,
              const char *db_name_arg, const char *table_name_arg,
              const char *field_name_arg);
@@ -1606,6 +1635,7 @@ public:
   longlong val_int();
   my_decimal *val_decimal(my_decimal *);
   String *val_str(String*);
+  void save_result(Field *to);
   double val_result();
   longlong val_int_result();
   String *str_result(String* tmp);
@@ -1926,8 +1956,6 @@ public:
   virtual void print(String *str, enum_query_type query_type);
   Item_num *neg ();
   uint decimal_precision() const { return max_length; }
-  bool check_partition_func_processor(uchar *bool_arg) { return FALSE;}
-  bool check_vcol_func_processor(uchar *arg) { return FALSE;}
 };
 
 
@@ -2389,6 +2417,8 @@ public:
     Item *it= ((Item *) item)->real_item();
     return ref && (*ref)->eq(it, binary_cmp);
   }
+  void save_val(Field *to);
+  void save_result(Field *to);
   double val_real();
   longlong val_int();
   my_decimal *val_decimal(my_decimal *);
@@ -2519,6 +2549,7 @@ public:
               alias_name_used_arg)
   {}
 
+  void save_val(Field *to);
   double val_real();
   longlong val_int();
   String *val_str(String* tmp);
@@ -2529,6 +2560,134 @@ public:
   virtual Ref_Type ref_type() { return DIRECT_REF; }
 };
 
+class Expression_cache;
+class Item_cache;
+
+
+/**
+  The objects of this class can store its values in an expression cache.
+*/
+
+class Item_cache_wrapper :public Item_result_field
+{
+private:
+  /* Pointer on the cached expression */
+  Item *orig_item;
+  Expression_cache *expr_cache;
+  /*
+    In order to put the expression into the expression cache and return
+    value of val_*() method, we will need to get the expression value twice
+    (probably in different types).  In order to avoid making two
+    (potentially costly) orig_item->val_*() calls, we store expression value
+    in this Item_cache object.
+  */
+  Item_cache *expr_value;
+
+  Item *check_cache();
+  inline void cache();
+
+public:
+  Item_cache_wrapper(Item *item_arg);
+  ~Item_cache_wrapper();
+
+  const char *func_name() const { return "<expr_cache>"; }
+  enum Type type() const { return EXPR_CACHE_ITEM; }
+  virtual Item *get_cached_item() { return orig_item; }
+
+  bool set_cache(THD *thd, List<Item*> &depends_on);
+
+  bool fix_fields(THD *thd, Item **it);
+  void fix_length_and_dec() {}
+  void cleanup();
+
+  /* Methods of getting value which should be cached in the cache */
+  void save_val(Field *to);
+  double val_real();
+  longlong val_int();
+  String *val_str(String* tmp);
+  my_decimal *val_decimal(my_decimal *);
+  bool val_bool();
+  bool is_null();
+  bool get_date(MYSQL_TIME *ltime, uint fuzzydate);
+  bool get_time(MYSQL_TIME *ltime);
+  bool send(Protocol *protocol, String *buffer)
+  {
+    if (result_field)
+      return protocol->store(result_field);
+    return Item::send(protocol, buffer);
+  }
+  void save_org_in_field(Field *field)
+  {
+    save_val(field);
+  }
+  void save_in_result_field(bool no_conversions)
+  {
+    save_val(result_field);
+  }
+
+  /* Following methods make this item transparent as much as possible */
+
+  virtual void print(String *str, enum_query_type query_type)
+  {
+    /* TODO: maybe print something for EXPLAIN EXTENDED */
+    return orig_item->print(str, query_type);
+  }
+  virtual const char *full_name() const { return orig_item->full_name(); }
+  virtual void make_field(Send_field *field) { orig_item->make_field(field); }
+  bool eq(const Item *item, bool binary_cmp) const
+  {
+    Item *it= ((Item *) item)->real_item();
+    return orig_item->eq(it, binary_cmp);
+  }
+  void fix_after_pullout(st_select_lex *new_parent, Item **refptr)
+  {
+    orig_item->fix_after_pullout(new_parent, &orig_item);
+  }
+  int save_in_field(Field *to, bool no_conversions);
+  enum Item_result result_type () const { return orig_item->result_type(); }
+  enum_field_types field_type() const   { return orig_item->field_type(); }
+  table_map used_tables() const { return orig_item->used_tables(); }
+  void update_used_tables() { orig_item->update_used_tables(); }
+  bool const_item() const { return orig_item->const_item(); }
+  table_map not_null_tables() const { return orig_item->not_null_tables(); }
+  bool walk(Item_processor processor, bool walk_subquery, uchar *arg)
+  {
+    return orig_item->walk(processor, walk_subquery, arg) ||
+      (this->*processor)(arg);
+  }
+  bool enumerate_field_refs_processor(uchar *arg)
+  { return orig_item->enumerate_field_refs_processor(arg); }
+  bool result_as_longlong() { return orig_item->result_as_longlong(); }
+  Item_field *filed_for_view_update()
+  { return orig_item->filed_for_view_update(); }
+
+  /* Row emulation: forwarding of ROW-related calls to orig_item */
+  uint cols()
+  { return result_type() == ROW_RESULT ? orig_item->cols() : 1; }
+  Item* element_index(uint i)
+  { return result_type() == ROW_RESULT ? orig_item->element_index(i) : this; }
+  Item** addr(uint i)
+  { return result_type() == ROW_RESULT ? orig_item->addr(i) : 0; }
+  bool check_cols(uint c)
+  {
+    return (result_type() == ROW_RESULT ?
+            orig_item->check_cols(c) :
+            Item::check_cols(c));
+  }
+  bool null_inside()
+  { return result_type() == ROW_RESULT ? orig_item->null_inside() : 0; }
+  void bring_value()
+  {
+    if (result_type() == ROW_RESULT)
+      orig_item->bring_value();
+  }
+  bool check_vcol_func_processor(uchar *arg)
+  {
+    return trace_unsupported_by_check_vcol_func_processor("cache");
+  }
+};
+
+
 /*
   Class for view fields, the same as Item_direct_ref, but call fix_fields
   of reference if it is not called yet
@@ -2638,6 +2797,7 @@ public:
 		       const char *table_name_arg, const char *field_name_arg)
     :Item_ref(context_arg, item, table_name_arg, field_name_arg),
      owner(master) {}
+  void save_val(Field *to);
   double val_real();
   longlong val_int();
   String* val_str(String* s);
@@ -3172,7 +3332,8 @@ public:
     example(0), used_table_map(0), cached_field(0), cached_field_type(MYSQL_TYPE_STRING),
     value_cached(0)
   {
-    fixed= 1; 
+    fixed= 1;
+    maybe_null= 1;
     null_value= 1;
   }
   Item_cache(enum_field_types field_type_arg):
@@ -3180,6 +3341,7 @@ public:
     value_cached(0)
   {
     fixed= 1;
+    maybe_null= 1;
     null_value= 1;
   }
 
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index f4a814b7aee..d382b3b7a4e 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1750,6 +1750,45 @@ bool Item_in_optimizer::fix_fields(THD *thd, Item **ref)
 }
 
 
+/**
+  Add an expression cache for this subquery if it is needed
+
+  @param thd_arg         Thread handle
+
+  @details
+  The function checks whether an expression cache is needed for this item
+  and if if so wraps the item into an item of the class
+  Item_exp_cache_wrapper with an appropriate expression cache set up there.
+
+  @note
+  used from Item::transform()
+
+  @return
+  new wrapper item if an expression cache is needed,
+  this item - otherwise
+*/
+
+Item *Item_in_optimizer::expr_cache_insert_transformer(uchar *thd_arg)
+{
+  THD *thd= (THD*) thd_arg;
+  DBUG_ENTER("Item_in_optimizer::expr_cache_insert_transformer");
+  List<Item*> &depends_on= ((Item_subselect *)args[1])->depends_on;
+
+  /* Add left expression to the list of the parameters of the subquery */
+  if (args[0]->cols() == 1)
+    depends_on.push_front((Item**)args);
+  else
+    for (int i= 0; i < args[0]->cols(); i++)
+    {
+      depends_on.push_front(args[0]->addr(i));
+    }
+
+  if (args[1]->expr_cache_is_needed(thd))
+    DBUG_RETURN(set_expr_cache(thd, depends_on));
+  DBUG_RETURN(this);
+}
+
+
 longlong Item_in_optimizer::val_int()
 {
   bool tmp;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index e431116a70d..053a54349c2 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -264,6 +264,7 @@ public:
   Item_cache **get_cache() { return &cache; }
   void keep_top_level_cache();
   Item *transform(Item_transformer transformer, uchar *arg);
+  virtual Item *expr_cache_insert_transformer(uchar *thd_arg);
 };
 
 class Comp_creator
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 0d0acdb29e4..454a3929f99 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -93,6 +93,8 @@ void Item_subselect::init(st_select_lex *select_lex,
     SELECT_LEX *upper= unit->outer_select();
     if (upper->parsing_place == IN_HAVING)
       upper->subquery_in_having= 1;
+    /* The subquery is an expression cache candidate */
+    upper->expr_cache_may_be_used[upper->parsing_place]= TRUE;
   }
   DBUG_VOID_RETURN;
 }
@@ -116,6 +118,7 @@ void Item_subselect::cleanup()
   }
   if (engine)
     engine->cleanup();
+  depends_on.empty();
   reset();
   value_assigned= 0;
   DBUG_VOID_RETURN;
@@ -486,6 +489,56 @@ bool Item_subselect::exec()
 }
 
 
+/**
+  Check if an expression cache is needed for this subquery
+
+  @param thd             Thread handle
+
+  @details
+  The function checks whether a cache is needed for a subquery and whether
+  the result of the subquery can be put in cache.
+
+  @retval TRUE  cache is needed
+  @retval FALSE otherwise
+*/
+
+bool Item_subselect::expr_cache_is_needed(THD *thd)
+{
+  return (depends_on.elements &&
+          engine->cols() == 1 &&
+          optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) &&
+          !(engine->uncacheable() & (UNCACHEABLE_RAND |
+                                     UNCACHEABLE_SIDEEFFECT)));
+}
+
+
+/**
+  Check if an expression cache is needed for this subquery
+
+  @param thd             Thread handle
+
+  @details
+  The function checks whether a cache is needed for a subquery and whether
+  the result of the subquery can be put in cache.
+
+  @note
+  This method allows many columns in the subquery because it is supported by
+  Item_in optimizer and result of the IN subquery will be scalar in this
+  case.
+
+  @retval TRUE  cache is needed
+  @retval FALSE otherwise
+*/
+
+bool Item_in_subselect::expr_cache_is_needed(THD *thd)
+{
+  return (depends_on.elements &&
+          optimizer_flag(thd, OPTIMIZER_SWITCH_SUBQUERY_CACHE) &&
+          !(engine->uncacheable() & (UNCACHEABLE_RAND |
+                                     UNCACHEABLE_SIDEEFFECT)));
+}
+
+
 /*
   Compute the IN predicate if the left operand's cache changed.
 */
@@ -784,6 +837,36 @@ void Item_singlerow_subselect::fix_length_and_dec()
     maybe_null= engine->may_be_null();
 }
 
+
+/**
+  Add an expression cache for this subquery if it is needed
+
+  @param thd_arg         Thread handle
+
+  @details
+  The function checks whether an expression cache is needed for this item
+  and if if so wraps the item into an item of the class
+  Item_exp_cache_wrapper with an appropriate expression cache set up there.
+
+  @note
+  used from Item::transform()
+
+  @return
+  new wrapper item if an expression cache is needed,
+  this item - otherwise
+*/
+
+Item* Item_singlerow_subselect::expr_cache_insert_transformer(uchar *thd_arg)
+{
+  THD *thd= (THD*) thd_arg;
+  DBUG_ENTER("Item_singlerow_subselect::expr_cache_insert_transformer");
+
+  if (expr_cache_is_needed(thd))
+    DBUG_RETURN(set_expr_cache(thd, depends_on));
+  DBUG_RETURN(this);
+}
+
+
 uint Item_singlerow_subselect::cols()
 {
   return engine->cols();
@@ -976,6 +1059,36 @@ void Item_exists_subselect::fix_length_and_dec()
   unit->global_parameters->select_limit= new Item_int((int32) 1);
 }
 
+
+/**
+  Add an expression cache for this subquery if it is needed
+
+  @param thd_arg         Thread handle
+
+  @details
+  The function checks whether an expression cache is needed for this item
+  and if if so wraps the item into an item of the class
+  Item_exp_cache_wrapper with an appropriate expression cache set up there.
+
+  @note
+  used from Item::transform()
+
+  @return
+  new wrapper item if an expression cache is needed,
+  this item - otherwise
+*/
+
+Item* Item_exists_subselect::expr_cache_insert_transformer(uchar *thd_arg)
+{
+  THD *thd= (THD*) thd_arg;
+  DBUG_ENTER("Item_exists_subselect::expr_cache_insert_transformer");
+
+  if (substype() == EXISTS_SUBS && expr_cache_is_needed(thd))
+    DBUG_RETURN(set_expr_cache(thd, depends_on));
+  DBUG_RETURN(this);
+}
+
+
 double Item_exists_subselect::val_real()
 {
   DBUG_ASSERT(fixed == 1);
@@ -3841,6 +3954,7 @@ subselect_uniquesubquery_engine*
 subselect_hash_sj_engine::make_unique_engine()
 {
   Item_in_subselect *item_in= (Item_in_subselect *) item;
+  Item_iterator_row it(item_in->left_expr);
   /* The only index on the temporary table. */
   KEY *tmp_key= tmp_table->key_info;
   /* Number of keyparts in tmp_key. */
@@ -3858,41 +3972,9 @@ subselect_hash_sj_engine::make_unique_engine()
   */
   if (!(tab= (JOIN_TAB*) thd->alloc(sizeof(JOIN_TAB))))
     DBUG_RETURN(NULL);
-  tab->table= tmp_table;
-  tab->ref.key= 0; /* The only temp table index. */
-  tab->ref.key_length= tmp_key->key_length;
-  if (!(tab->ref.key_buff=
-        (uchar*) thd->calloc(ALIGN_SIZE(tmp_key->key_length) * 2)) ||
-      !(tab->ref.key_copy=
-        (store_key**) thd->alloc((sizeof(store_key*) *
-                                  (tmp_key_parts + 1)))) ||
-      !(tab->ref.items=
-        (Item**) thd->alloc(sizeof(Item*) * tmp_key_parts)))
-    DBUG_RETURN(NULL);
 
-  KEY_PART_INFO *cur_key_part= tmp_key->key_part;
-  store_key **ref_key= tab->ref.key_copy;
-  uchar *cur_ref_buff= tab->ref.key_buff;
-  
-  for (uint i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++)
-  {
-    tab->ref.items[i]= item_in->left_expr->element_index(i);
-    int null_count= test(cur_key_part->field->real_maybe_null());
-    *ref_key= new store_key_item(thd, cur_key_part->field,
-                                 /* TIMOUR:
-                                    the NULL byte is taken into account in
-                                    cur_key_part->store_length, so instead of
-                                    cur_ref_buff + test(maybe_null), we could
-                                    use that information instead.
-                                 */
-                                 cur_ref_buff + null_count,
-                                 null_count ? tab->ref.key_buff : 0,
-                                 cur_key_part->length, tab->ref.items[i]);
-    cur_ref_buff+= cur_key_part->store_length;
-  }
-  *ref_key= NULL; /* End marker. */
-  tab->ref.key_err= 1;
-  tab->ref.key_parts= tmp_key_parts;
+  tab->table= tmp_table;
+  tab->ref.tmp_table_index_lookup_init(thd, tmp_key, it, FALSE);
 
   DBUG_RETURN(new subselect_uniquesubquery_engine(thd, tab, item,
                                                   semi_join_conds));
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 2e7e117096e..72f73d58867 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -88,11 +88,19 @@ public:
   */
   List<Ref_to_outside> upper_refs;
   st_select_lex *parent_select;
-  
- /*
+
+  /**
+     List of references on items subquery depends on (externally resolved);
+
+     @note We can't store direct links on Items because it could be
+           substituted with other item (for example for grouping).
+   */
+  List<Item*> depends_on;
+
+  /*
    TRUE<=>Table Elimination has made it redundant to evaluate this select
           (and so it is not part of QEP, etc)
- */
+  */
   bool eliminated;
   
   /* changed engine indicator */
@@ -185,6 +193,7 @@ public:
   */
   st_select_lex* get_select_lex();
   const char *func_name() const { DBUG_ASSERT(0); return "subselect"; }
+  virtual bool expr_cache_is_needed(THD *);
 
   friend class select_result_interceptor;
   friend class Item_in_optimizer;
@@ -243,6 +252,8 @@ public:
   */
   st_select_lex* invalidate_and_restore_select_lex();
 
+  Item* expr_cache_insert_transformer(uchar *thd_arg);
+
   friend class select_singlerow_subselect;
 };
 
@@ -290,6 +301,8 @@ public:
   void fix_length_and_dec();
   virtual void print(String *str, enum_query_type query_type);
 
+  Item* expr_cache_insert_transformer(uchar *thd_arg);
+
   friend class select_exists_subselect;
   friend class subselect_uniquesubquery_engine;
   friend class subselect_indexsubquery_engine;
@@ -434,6 +447,7 @@ public:
   /* Inform 'this' that it was computed, and contains a valid result. */
   void set_first_execution() { if (first_execution) first_execution= FALSE; }
   bool is_expensive_processor(uchar *arg);
+  bool expr_cache_is_needed(THD *thd);
   
   /* 
     Return the identifier that we could use to identify the subquery for the
@@ -813,7 +827,7 @@ public:
   {
     return materialize_engine->cols();
   }
-  uint8 uncacheable() { return UNCACHEABLE_DEPENDENT; }
+  uint8 uncacheable() { return materialize_engine->uncacheable(); }
   table_map upper_select_const_tables() { return 0; }
   bool no_rows() { return !tmp_table->file->stats.records; }
   virtual enum_engine_type engine_type() { return HASH_SJ_ENGINE; }
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 89441a29a76..75d98bc6d2a 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -319,6 +319,7 @@ bool Item_sum::register_sum_func(THD *thd, Item **ref)
   if (aggr_level >= 0)
   {
     ref_by= ref;
+    thd->lex->current_select->register_dependency_item(aggr_sel, ref);
     /* Add the object to the list of registered objects assigned to aggr_sel */
     if (!aggr_sel->inner_sum_func_list)
       next= this;
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 024d20472c3..ba60bab9b50 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -570,12 +570,13 @@ protected:
 #define OPTIMIZER_SWITCH_SEMIJOIN 256
 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE 512
 #define OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN 1024
+#define OPTIMIZER_SWITCH_SUBQUERY_CACHE (1<<11)
 
 #ifdef DBUG_OFF
-#  define OPTIMIZER_SWITCH_LAST 2048
+#  define OPTIMIZER_SWITCH_LAST (1<<12)
 #else
-#  define OPTIMIZER_SWITCH_TABLE_ELIMINATION 2048
-#  define OPTIMIZER_SWITCH_LAST 4096
+#  define OPTIMIZER_SWITCH_TABLE_ELIMINATION (1<<12)
+#  define OPTIMIZER_SWITCH_LAST (1<<13)
 #endif
 
 #ifdef DBUG_OFF 
@@ -590,7 +591,8 @@ protected:
                                     OPTIMIZER_SWITCH_MATERIALIZATION | \
                                     OPTIMIZER_SWITCH_SEMIJOIN | \
                                     OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\
-                                    OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN)
+                                    OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\
+                                    OPTIMIZER_SWITCH_SUBQUERY_CACHE)
 #else
 #  define OPTIMIZER_SWITCH_DEFAULT (OPTIMIZER_SWITCH_INDEX_MERGE | \
                                     OPTIMIZER_SWITCH_INDEX_MERGE_UNION | \
@@ -603,7 +605,8 @@ protected:
                                     OPTIMIZER_SWITCH_MATERIALIZATION | \
                                     OPTIMIZER_SWITCH_SEMIJOIN | \
                                     OPTIMIZER_SWITCH_PARTIAL_MATCH_ROWID_MERGE|\
-                                    OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN)
+                                    OPTIMIZER_SWITCH_PARTIAL_MATCH_TABLE_SCAN|\
+                                    OPTIMIZER_SWITCH_SUBQUERY_CACHE)
 #endif
 
 /*
@@ -683,7 +686,8 @@ enum enum_parsing_place
   SELECT_LIST,
   IN_WHERE,
   IN_ON,
-  IN_GROUP_BY
+  IN_GROUP_BY,
+  PARSING_PLACE_SIZE /* always should be the last */
 };
 
 struct st_table;
@@ -925,6 +929,7 @@ bool general_log_write(THD *thd, enum enum_server_command command,
 #ifdef MYSQL_SERVER
 #include "sql_servers.h"
 #include "opt_range.h"
+#include "sql_expression_cache.h"
 
 #ifdef HAVE_QUERY_CACHE
 struct Query_cache_query_flags
@@ -1258,6 +1263,9 @@ bool mysql_select(THD *thd, Item ***rref_pointer_array,
                   Item *having, ORDER *proc_param, ulonglong select_type, 
                   select_result *result, SELECT_LEX_UNIT *unit, 
                   SELECT_LEX *select_lex);
+
+int join_read_key2(THD *thd, struct st_join_table *tab, TABLE *table,
+                   struct st_table_ref *table_ref);
 void free_underlaid_joins(THD *thd, SELECT_LEX *select);
 bool mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit,
                          select_result *result);
@@ -1277,6 +1285,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
 			bool table_cant_handle_bit_fields,
                         bool make_copy_field,
                         uint convert_blob_length);
+bool open_tmp_table(TABLE *table);
 void sp_prepare_create_field(THD *thd, Create_field *sql_field);
 int prepare_create_field(Create_field *sql_field, 
 			 uint *blob_columns, 
@@ -1832,7 +1841,7 @@ bool close_cached_connection_tables(THD *thd, bool wait_for_refresh,
                                     bool have_lock = FALSE);
 void copy_field_from_tmp_record(Field *field,int offset);
 bool fill_record(THD *thd, Field **field, List<Item> &values,
-                 bool ignore_errors);
+                 bool ignore_errors, bool use_value);
 bool fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields,
                                           List<Item> &values,
                                           bool ignore_errors,
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 55cb1b3b6fd..5bc26154790 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -307,6 +307,7 @@ static const char *optimizer_switch_names[]=
   "firstmatch","loosescan","materialization", "semijoin",
   "partial_match_rowid_merge",
   "partial_match_table_scan",
+  "subquery_cache",
 #ifndef DBUG_OFF
   "table_elimination",
 #endif
@@ -327,6 +328,7 @@ static const unsigned int optimizer_switch_names_len[]=
   sizeof("semijoin") - 1,
   sizeof("partial_match_rowid_merge") - 1,
   sizeof("partial_match_table_scan") - 1,
+  sizeof("subquery_cache") - 1,
 #ifndef DBUG_OFF
   sizeof("table_elimination") - 1,
 #endif
@@ -406,8 +408,9 @@ static const char *sql_mode_str= "OFF";
 static const char *optimizer_switch_str="index_merge=on,index_merge_union=on,"
                                         "index_merge_sort_union=on,"
                                         "index_merge_intersection=on,"
-                                        "index_condition_pushdown=on"
-#ifndef DBUG_OFF                                        
+                                        "index_condition_pushdown=on,"
+                                        "subquery_cache=on"
+#ifndef DBUG_OFF
                                         ",table_elimination=on";
 #else
                                         ;
@@ -5890,7 +5893,9 @@ enum options_mysqld
   OPT_RECORD_RND_BUFFER, OPT_DIV_PRECINCREMENT, OPT_RELAY_LOG_SPACE_LIMIT,
   OPT_RELAY_LOG_PURGE,
   OPT_SLAVE_NET_TIMEOUT, OPT_SLAVE_COMPRESSED_PROTOCOL, OPT_SLOW_LAUNCH_TIME,
-  OPT_SLAVE_TRANS_RETRIES, OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE,
+  OPT_SLAVE_TRANS_RETRIES,
+  OPT_SUBQUERY_CACHE,
+  OPT_READONLY, OPT_ROWID_MERGE_BUFF_SIZE,
   OPT_DEBUGGING, OPT_DEBUG_FLUSH,
   OPT_SORT_BUFFER, OPT_TABLE_OPEN_CACHE, OPT_TABLE_DEF_CACHE,
   OPT_THREAD_CONCURRENCY, OPT_THREAD_CACHE_SIZE,
@@ -7222,7 +7227,7 @@ The minimum value for this variable is 4096.",
   {"optimizer_switch", OPT_OPTIMIZER_SWITCH,
    "optimizer_switch=option=val[,option=val...], where option={index_merge, "
    "index_merge_union, index_merge_sort_union, index_merge_intersection, "
-   "index_condition_pushdown"
+   "index_condition_pushdown, subquery_cache"
 #ifndef DBUG_OFF
    ", table_elimination"
 #endif 
@@ -7936,6 +7941,12 @@ SHOW_VAR status_vars[]= {
   {"Ssl_version",              (char*) &show_ssl_get_version, SHOW_FUNC},
 #endif /* HAVE_OPENSSL */
   {"Syncs",                    (char*) &my_sync_count,          SHOW_LONG_NOFLUSH},
+  /*
+    Expression cache used only for caching subqueries now, so its statistic
+    variables we call subquery_cache*.
+  */
+  {"Subquery_cache_hit",       (char*) &subquery_cache_hit, SHOW_LONG},
+  {"Subquery_cache_miss",      (char*) &subquery_cache_miss, SHOW_LONG},
   {"Table_locks_immediate",    (char*) &locks_immediate,        SHOW_LONG},
   {"Table_locks_waited",       (char*) &locks_waited,           SHOW_LONG},
 #ifdef HAVE_MMAP
@@ -8075,6 +8086,7 @@ static int mysql_init_variables(void)
   abort_loop= select_thread_in_use= signal_thread_in_use= 0;
   ready_to_exit= shutdown_in_progress= grant_option= 0;
   aborted_threads= aborted_connects= 0;
+  subquery_cache_miss= subquery_cache_hit= 0;
   delayed_insert_threads= delayed_insert_writes= delayed_rows_in_use= 0;
   delayed_insert_errors= thread_created= 0;
   specialflag= 0;
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 0d8a0a6d73e..83abf3beac8 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -9569,7 +9569,9 @@ check_group_min_max_predicates(COND *cond, Item_field *min_max_arg_item,
     the MIN/MAX argument field, and disallow the optimization only if this is
     so.
   */
-  if (cond_type == Item::SUBSELECT_ITEM)
+  if (cond_type == Item::SUBSELECT_ITEM ||
+      (cond->get_cached_item() &&
+       cond->get_cached_item()->type() == Item::SUBSELECT_ITEM))
     DBUG_RETURN(FALSE);
 
   /*
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 1855224440c..8567d291341 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -2297,7 +2297,8 @@ bool setup_sj_materialization(JOIN_TAB *tab)
                                    */
                                    cur_ref_buff + null_count,
                                    null_count ? tab_ref->key_buff : 0,
-                                   cur_key_part->length, tab_ref->items[i]);
+                                   cur_key_part->length, tab_ref->items[i],
+                                   FALSE);
       cur_ref_buff+= cur_key_part->store_length;
     }
     *ref_key= NULL; /* End marker. */
diff --git a/sql/procedure.h b/sql/procedure.h
index 30a8a0efccb..488d461905e 100644
--- a/sql/procedure.h
+++ b/sql/procedure.h
@@ -13,6 +13,8 @@
    along with this program; if not, write to the Free Software
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
 
+#ifndef PROCEDURE_INCLUDED
+#define PROCEDURE_INCLUDED
 
 /* When using sql procedures */
 
@@ -153,3 +155,5 @@ public:
 
 Procedure *setup_procedure(THD *thd,ORDER *proc_param,select_result *result,
 			   List<Item> &field_list,int *error);
+
+#endif /* PROCEDURE_INCLUDED */
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 89a3ce7a5fc..a93b48bfe94 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1375,10 +1375,10 @@ bool close_thread_table(THD *thd, TABLE **table_ptr)
   bool found_old_table= 0;
   TABLE *table= *table_ptr;
   DBUG_ENTER("close_thread_table");
-  DBUG_ASSERT(table->key_read == 0);
-  DBUG_ASSERT(!table->file || table->file->inited == handler::NONE);
   DBUG_PRINT("tcache", ("table: '%s'.'%s' 0x%lx", table->s->db.str,
                         table->s->table_name.str, (long) table));
+  DBUG_ASSERT(table->key_read == 0);
+  DBUG_ASSERT(!table->file || table->file->inited == handler::NONE);
 
  if (table->file)
  {
@@ -6328,12 +6328,21 @@ find_field_in_tables(THD *thd, Item_ident *item,
           sub query as dependent on the outer query
         */
         if (current_sel != last_select)
+        {
           mark_select_range_as_dependent(thd, last_select, current_sel,
                                          found, *ref, item);
+          if (item->can_be_depended)
+          {
+            DBUG_ASSERT((*ref) == (Item*)item);
+            current_sel->register_dependency_item(last_select, ref);
+          }
+        }
       }
       return found;
     }
   }
+  else
+    item->can_be_depended= TRUE;
 
   if (db && lower_case_table_names)
   {
@@ -8083,6 +8092,10 @@ int setup_conds(THD *thd, TABLE_LIST *tables, TABLE_LIST *leaves,
   if (*conds)
   {
     thd->where="where clause";
+    DBUG_EXECUTE("where",
+                 print_where(*conds,
+                             "WHERE in setup_conds",
+                             QT_ORDINARY););
     if ((!(*conds)->fixed && (*conds)->fix_fields(thd, conds)) ||
 	(*conds)->check_cols(1))
       goto err_no_arena;
@@ -8341,6 +8354,7 @@ fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields,
     ptr           pointer on pointer to record
     values        list of fields
     ignore_errors TRUE if we should ignore errors
+    use_value     forces usage of value of the items instead of result
 
   NOTE
     fill_record() may set table->auto_increment_field_not_null and a
@@ -8353,7 +8367,8 @@ fill_record_n_invoke_before_triggers(THD *thd, List<Item> &fields,
 */
 
 bool
-fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors)
+fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors,
+            bool use_value)
 {
   List_iterator_fast<Item> v(values);
   List<TABLE> tbl_list;
@@ -8395,8 +8410,11 @@ fill_record(THD *thd, Field **ptr, List<Item> &values, bool ignore_errors)
                           field->field_name, table->s->table_name.str);
       thd->abort_on_warning= abort_on_warning_saved;
     }
-    if (value->save_in_field(field, 0) < 0)
-      goto err;
+    if (use_value)
+      value->save_val(field);
+    else
+      if (value->save_in_field(field, 0) < 0)
+        goto err;
     tbl_list.push_back(table);
   }
   /* Update virtual fields*/
@@ -8465,7 +8483,7 @@ fill_record_n_invoke_before_triggers(THD *thd, Field **ptr,
                                      enum trg_event_type event)
 {
   bool result;
-  result= (fill_record(thd, ptr, values, ignore_errors) ||
+  result= (fill_record(thd, ptr, values, ignore_errors, FALSE) ||
            (triggers && triggers->process_triggers(thd, event,
                                                    TRG_ACTION_BEFORE, TRUE)));
   /*
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 9cc66d4d8be..c85ea76c70d 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -3052,6 +3052,7 @@ void TMP_TABLE_PARAM::init()
   table_charset= 0;
   precomputed_group_by= 0;
   bit_fields_as_long= 0;
+  skip_create_table= 0;
   DBUG_VOID_RETURN;
 }
 
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 932f2234766..6ef330e8ee6 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -23,6 +23,76 @@
 #include "log.h"
 #include "rpl_tblmap.h"
 
+
+/**
+  Interface for Item iterator
+*/
+
+class Item_iterator
+{
+public:
+  /**
+    Shall set this iterator to the position before the first item
+
+    @note
+    This method also may perform some other initialization actions like
+    allocation of certain resources.
+  */
+  virtual void open()= 0;
+  /**
+    Shall return the next Item (or NULL if there is no next item) and
+    move pointer to position after it.
+  */
+  virtual Item *next()= 0;
+  /**
+    Shall force iterator to free resources (if it holds them)
+
+    @note
+    One should not use the iterator without open() call after close()
+  */
+  virtual void close()= 0;
+
+  virtual ~Item_iterator() {}
+};
+
+
+/**
+  Item iterator over List_iterator_fast for Item references
+*/
+
+class Item_iterator_ref_list: public Item_iterator
+{
+  List_iterator_fast<Item*> list;
+public:
+  Item_iterator_ref_list(List_iterator_fast<Item*> &arg_list):
+    list(arg_list) {}
+  void open() { list.rewind(); }
+  Item *next() { return *(list++); }
+  void close() {}
+};
+
+
+/**
+  Item iterator over Item interface for rows
+*/
+
+class Item_iterator_row: public Item_iterator
+{
+  Item *base_item;
+  uint current;
+public:
+  Item_iterator_row(Item *base) : base_item(base), current(0) {}
+  void open() { current= 0; }
+  Item *next()
+  {
+    if (current >= base_item->cols())
+      return NULL;
+    return base_item->element_index(current++);
+  }
+  void close() {}
+};
+
+
 /**
   An interface that is used to take an action when
   the locking module notices that a table version has changed
@@ -2796,12 +2866,17 @@ public:
     that MEMORY tables cannot index BIT columns.
   */
   bool bit_fields_as_long;
+  /*
+    Whether to create or postpone actual creation of this temporary table.
+    TRUE <=> create_tmp_table will create only the TABLE structure.
+  */
+  bool skip_create_table;
 
   TMP_TABLE_PARAM()
     :copy_field(0), group_parts(0),
      group_length(0), group_null_parts(0), convert_blob_length(0),
      schema_table(0), precomputed_group_by(0), force_copy_fields(0),
-     bit_fields_as_long(0)
+     bit_fields_as_long(0), skip_create_table(0)
   {}
   ~TMP_TABLE_PARAM()
   {
diff --git a/sql/sql_expression_cache.cc b/sql/sql_expression_cache.cc
new file mode 100644
index 00000000000..70c36b141b6
--- /dev/null
+++ b/sql/sql_expression_cache.cc
@@ -0,0 +1,314 @@
+
+#include "mysql_priv.h"
+#include "sql_select.h"
+
+/*
+  Expression cache is used only for caching subqueries now, so its statistic
+  variables we call subquery_cache*.
+*/
+ulonglong subquery_cache_miss, subquery_cache_hit;
+
+Expression_cache_tmptable::Expression_cache_tmptable(THD *thd,
+                                                 List<Item*> &dependants,
+                                                 Item *value)
+  :cache_table(NULL), table_thd(thd), list(&dependants), val(value),
+   equalities(NULL), inited (0)
+{
+  DBUG_ENTER("Expression_cache_tmptable::Expression_cache_tmptable");
+  DBUG_VOID_RETURN;
+};
+
+
+/**
+  Build and of equalities for the expression's parameters of certain types
+
+  @details
+  If the temporary table used as an expression cache contains fields of
+  certain types then it's not enough to perform a lookup into the table to
+  verify that there is no row in the table for a given set of parameters.
+  Additionally for those fields we have to check equalities of the form
+  fld=val, where val is the value of the parameter stored in the column
+  fld.
+  The function generates a conjunction of all such equality predicates
+  and saves a pointer to it in the field 'equalities'.
+
+  @retval FALSE OK
+  @retval TRUE  Error
+*/
+
+bool Expression_cache_tmptable::make_equalities()
+{
+  List<Item> args;
+  List_iterator_fast<Item*> li(*list);
+  Item **ref;
+  Name_resolution_context *cn= NULL;
+  DBUG_ENTER("Expression_cache_tmptable::make_equalities");
+
+  for (uint i= 1 /* skip result filed */; (ref= li++); i++)
+  {
+    Field *fld= cache_table->field[i];
+    /* Only some field types should be checked after lookup */
+    if (fld->type() == MYSQL_TYPE_VARCHAR ||
+        fld->type() == MYSQL_TYPE_TINY_BLOB ||
+        fld->type() == MYSQL_TYPE_MEDIUM_BLOB ||
+        fld->type() == MYSQL_TYPE_LONG_BLOB ||
+        fld->type() == MYSQL_TYPE_BLOB ||
+        fld->type() == MYSQL_TYPE_VAR_STRING ||
+        fld->type() == MYSQL_TYPE_STRING ||
+        fld->type() == MYSQL_TYPE_NEWDECIMAL ||
+        fld->type() == MYSQL_TYPE_DECIMAL)
+    {
+      if (!cn)
+      {
+        // dummy resolution context
+        cn= new Name_resolution_context();
+        cn->init();
+      }
+      args.push_front(new Item_func_eq(new Item_ref(cn, ref, "", "", FALSE),
+                                       new Item_field(fld)));
+    }
+  }
+  if (args.elements == 1)
+    equalities= args.head();
+  else
+    equalities= new Item_cond_and(args);
+
+  DBUG_RETURN(equalities->fix_fields(table_thd, &equalities));
+}
+
+
+/**
+  Field enumerator for TABLE::add_tmp_key
+
+  @param arg             reference variable with current field number
+
+  @return field number
+*/
+
+static uint field_enumerator(uchar *arg)
+{
+  return ((uint*)arg)[0]++;
+}
+
+
+/**
+  Initialize temporary table and auxiliary structures for the expression
+  cache
+
+  @details
+  The function creates a temporary table for the expression cache, defines
+  the search index and initializes auxiliary search structures used to check
+  whether a given set of of values of the expression parameters is in some
+  cache entry.
+*/
+
+void Expression_cache_tmptable::init()
+{
+  List_iterator_fast<Item*> li(*list);
+  Item_iterator_ref_list it(li);
+  Item **item;
+  uint field_counter;
+  DBUG_ENTER("Expression_cache_tmptable::init");
+  DBUG_ASSERT(!inited);
+  inited= TRUE;
+
+  if (!(ULONGLONG_MAX >> (list->elements + 1)))
+  {
+    DBUG_PRINT("info", ("Too many dependencies"));
+    DBUG_VOID_RETURN;
+  }
+
+  cache_table= NULL;
+
+  cache_table_param.init();
+  /* dependent items and result */
+  cache_table_param.field_count= list->elements + 1;
+  /* postpone table creation to index description */
+  cache_table_param.skip_create_table= 1;
+  cache_table= NULL;
+
+  while ((item= li++))
+  {
+    DBUG_ASSERT(item);
+    DBUG_ASSERT(*item);
+    DBUG_ASSERT((*item)->fixed);
+    items.push_back((*item));
+  }
+  items.push_front(val);
+
+  if (!(cache_table= create_tmp_table(table_thd, &cache_table_param,
+                                      items, (ORDER*) NULL,
+                                      FALSE, FALSE,
+                                      ((table_thd->options |
+                                        TMP_TABLE_ALL_COLUMNS) &
+                                       ~(OPTION_BIG_TABLES |
+                                         TMP_TABLE_FORCE_MYISAM)),
+                                      HA_POS_ERROR,
+                                      (char *)"subquery-cache-table")))
+  {
+    DBUG_PRINT("error", ("create_tmp_table failed, caching switched off"));
+    DBUG_VOID_RETURN;
+  }
+
+  if (cache_table->s->db_type() != heap_hton)
+  {
+    DBUG_PRINT("error", ("we need only heap table"));
+    goto error;
+  }
+
+  /* This list do not contain result field */
+  it.open();
+
+  field_counter=1;
+
+  if (cache_table->alloc_keys(1) ||
+      (cache_table->add_tmp_key(0, items.elements - 1,
+                                &field_enumerator,
+                                (uchar*)&field_counter) < 0) ||
+      ref.tmp_table_index_lookup_init(table_thd, cache_table->key_info, it,
+                                      TRUE))
+  {
+    DBUG_PRINT("error", ("creating index failed"));
+    goto error;
+  }
+  cache_table->s->keys= 1;
+  cache_table->s->uniques= 1;
+  ref.null_rejecting= 1;
+  ref.disable_cache= FALSE;
+  ref.has_record= 0;
+  ref.use_count= 0;
+
+
+  if (open_tmp_table(cache_table))
+  {
+    DBUG_PRINT("error", ("Opening (creating) temporary table failed"));
+    goto error;
+  }
+
+  if (!(cached_result= new Item_field(cache_table->field[0])))
+  {
+    DBUG_PRINT("error", ("Creating Item_field failed"));
+    goto error;
+  }
+
+  if (make_equalities())
+  {
+    DBUG_PRINT("error", ("Creating equalities failed"));
+    goto error;
+  }
+
+  DBUG_VOID_RETURN;
+
+error:
+  /* switch off cache */
+  free_tmp_table(table_thd, cache_table);
+  cache_table= NULL;
+  DBUG_VOID_RETURN;
+}
+
+
+Expression_cache_tmptable::~Expression_cache_tmptable()
+{
+  if (cache_table)
+    free_tmp_table(table_thd, cache_table);
+}
+
+
+/**
+  Check if a given set of parameters of the expression is in the cache
+
+  @param [out] value     the expression value found in the cache if any
+
+  @details
+  For a given set of the parameters of the expression the function
+  checks whether it can be found in some entry of the cache. If so
+  the function returns the result of the expression extracted from
+  the cache.
+
+  @retval Expression_cache::HIT if the set of parameters is in the cache
+  @retval Expression_cache::MISS - otherwise
+*/
+
+Expression_cache::result Expression_cache_tmptable::check_value(Item **value)
+{
+  int res;
+  DBUG_ENTER("Expression_cache_tmptable::check_value");
+
+  /*
+    We defer cache initialization to get item references that are
+    used at the execution phase.
+  */
+  if (!inited)
+    init();
+
+  if (cache_table)
+  {
+    DBUG_PRINT("info", ("status: %u  has_record %u",
+                        (uint)cache_table->status, (uint)ref.has_record));
+    if ((res= join_read_key2(table_thd, NULL, cache_table, &ref)) == 1)
+      DBUG_RETURN(ERROR);
+    if (res || (equalities && !equalities->val_int()))
+    {
+      subquery_cache_miss++;
+      DBUG_RETURN(MISS);
+    }
+
+    subquery_cache_hit++;
+    *value= cached_result;
+    DBUG_RETURN(Expression_cache::HIT);
+  }
+  DBUG_RETURN(Expression_cache::MISS);
+}
+
+
+/**
+  Put a new entry into the expression cache
+
+  @param value     the result of the expression to be put into the cache
+
+  @details
+  The function evaluates 'value' and puts the result into the cache as the
+  result of the expression for the current set of parameters.
+
+  @retval FALSE OK
+  @retval TRUE  Error
+*/
+
+my_bool Expression_cache_tmptable::put_value(Item *value)
+{
+  int error;
+  DBUG_ENTER("Expression_cache_tmptable::put_value");
+  DBUG_ASSERT(inited);
+
+  if (!cache_table)
+  {
+    DBUG_PRINT("info", ("No table so behave as we successfully put value"));
+    DBUG_RETURN(FALSE);
+  }
+
+  *(items.head_ref())= value;
+  fill_record(table_thd, cache_table->field, items, TRUE, TRUE);
+  if (table_thd->is_error())
+    goto err;;
+
+  if ((error= cache_table->file->ha_write_row(cache_table->record[0])))
+  {
+    /* create_myisam_from_heap will generate error if needed */
+    if (cache_table->file->is_fatal_error(error, HA_CHECK_DUP) &&
+        create_internal_tmp_table_from_heap(table_thd, cache_table,
+                                            cache_table_param.start_recinfo,
+                                            &cache_table_param.recinfo,
+                                            error, 1))
+      goto err;
+  }
+  cache_table->status= 0; /* cache_table->record contains an existed record */
+  ref.has_record= TRUE; /* the same as above */
+  DBUG_PRINT("info", ("has_record: TRUE  status: 0"));
+
+  DBUG_RETURN(FALSE);
+
+err:
+  free_tmp_table(table_thd, cache_table);
+  cache_table= NULL;
+  DBUG_RETURN(TRUE);
+}
diff --git a/sql/sql_expression_cache.h b/sql/sql_expression_cache.h
new file mode 100644
index 00000000000..bb5dc7a76a2
--- /dev/null
+++ b/sql/sql_expression_cache.h
@@ -0,0 +1,80 @@
+#ifndef SQL_EXPRESSION_CACHE_INCLUDED
+#define SQL_EXPRESSION_CACHE_INCLUDED
+
+#include "sql_select.h"
+
+/**
+  Interface for expression cache
+
+  @note
+  Parameters of an expression cache interface are set on the creation of the
+  cache. They are passed when a cache object of the implementation class is
+  constructed. That's why they are not visible in this interface.
+*/
+
+extern ulonglong subquery_cache_miss, subquery_cache_hit;
+
+class Expression_cache :public Sql_alloc
+{
+public:
+  enum result {ERROR, HIT, MISS};
+
+  Expression_cache(){};
+  virtual ~Expression_cache() {};
+  /**
+    Shall check the presence of expression value in the cache for a given
+    set of values of the expression parameters.  Return the result of the
+    expression if it's found in the cache.
+  */
+  virtual result check_value(Item **value)= 0;
+  /**
+    Shall put the value of an expression for given set of its parameters
+    into the expression cache
+  */
+  virtual my_bool put_value(Item *value)= 0;
+};
+
+struct st_table_ref;
+struct st_join_table;
+class Item_field;
+
+
+/**
+  Implementation of expression cache over a temporary table
+*/
+
+class Expression_cache_tmptable :public Expression_cache
+{
+public:
+  Expression_cache_tmptable(THD *thd, List<Item*> &dependants, Item *value);
+  virtual ~Expression_cache_tmptable();
+  virtual result check_value(Item **value);
+  virtual my_bool put_value(Item *value);
+
+private:
+  void init();
+  bool make_equalities();
+
+  /* tmp table parameters */
+  TMP_TABLE_PARAM cache_table_param;
+  /* temporary table to store this cache */
+  TABLE *cache_table;
+  /* Thread handle for the temporary table */
+  THD *table_thd;
+  /* TABLE_REF for index lookup */
+  struct st_table_ref ref;
+  /* Cached result */
+  Item_field *cached_result;
+  /* List of references to the parameters of the expression */
+  List<Item*> *list;
+  /* List of items */
+  List<Item> items;
+  /* Value Item example */
+  Item *val;
+  /* Expression to check after index lookup */
+  Item *equalities;
+  /* Set on if the object has been succesfully initialized with init() */
+  bool inited;
+};
+
+#endif /* SQL_EXPRESSION_CACHE_INCLUDED */
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 0c4a6210231..7b19b4874b1 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1632,6 +1632,8 @@ void st_select_lex::init_query()
   nest_level= 0;
   link_next= 0;
   lock_option= TL_READ_DEFAULT;
+
+  bzero((char*) expr_cache_may_be_used, sizeof(expr_cache_may_be_used));
 }
 
 void st_select_lex::init_select()
@@ -1829,6 +1831,55 @@ void st_select_lex_unit::exclude_tree()
 }
 
 
+/**
+  Register reference to an item which the subqueries depends on
+
+  @param def_sel         select against which the item is resolved
+  @param dependency      reference to the item
+
+  @details
+  This function puts the reference dependency to an item that is either an
+  outer field or an aggregate function resolved against an outer select into
+  the list 'depends_on'. It adds it to the 'depends_on' lists for each
+  subquery between this one and 'def_sel' - the subquery against which the
+  item is resolved.
+*/
+
+void st_select_lex::register_dependency_item(st_select_lex *def_sel,
+                                             Item **dependency)
+{
+  SELECT_LEX *s= this;
+  DBUG_ENTER("st_select_lex::register_dependency_item");
+  DBUG_ASSERT(this != def_sel);
+  DBUG_ASSERT(*dependency);
+  do
+  {
+    /* check duplicates */
+    List_iterator_fast<Item*> li(s->master_unit()->item->depends_on);
+    Item **dep;
+    while ((dep= li++))
+    {
+      if ((*dep)->eq(*dependency, FALSE))
+      {
+         DBUG_PRINT("info", ("dependency %s already present",
+                             ((*dependency)->name ?
+                              (*dependency)->name :
+                              "<no name>")));
+         DBUG_VOID_RETURN;
+      }
+    }
+
+    s->master_unit()->item->depends_on.push_back(dependency);
+    DBUG_PRINT("info", ("depends_on: Select: %d  added: %s",
+                        s->select_number,
+                        ((*dependency)->name ?
+                         (*dependency)->name :
+                         "<no name>")));
+  } while ((s= s->outer_select()) != def_sel);
+  DBUG_VOID_RETURN;
+}
+
+
 /*
   st_select_lex_node::mark_as_dependent mark all st_select_lex struct from 
   this to 'last' as dependent
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index af007b713ca..335aa7f4ed4 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -661,6 +661,11 @@ public:
   /* explicit LIMIT clause was used */
   bool explicit_limit;
   /*
+    This array is used to note  whether we have any candidates for
+    expression caching in the corresponding clauses
+  */
+  bool expr_cache_may_be_used[PARSING_PLACE_SIZE];
+  /*
     there are subquery in HAVING clause => we can't close tables before
     query processing end even if we use temporary table
   */
@@ -749,6 +754,7 @@ public:
   inline bool is_subquery_function() { return master_unit()->item != 0; }
 
   bool mark_as_dependent(THD *thd, st_select_lex *last, Item *dependency);
+  void register_dependency_item(st_select_lex *last, Item **dependency);
 
   bool set_braces(bool value);
   bool inc_in_sum_expr();
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 731f4a5901f..887196fed9b 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -152,7 +152,6 @@ static int join_read_const_table(JOIN_TAB *tab, POSITION *pos);
 static int join_read_system(JOIN_TAB *tab);
 static int join_read_const(JOIN_TAB *tab);
 static int join_read_key(JOIN_TAB *tab);
-static int join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref);
 static void join_read_key_unlock_row(st_join_table *tab);
 static int join_read_always_key(JOIN_TAB *tab);
 static int join_read_last_key(JOIN_TAB *tab);
@@ -1293,6 +1292,9 @@ JOIN::optimize()
   int res;
   if ((res= rewrite_to_index_subquery_engine(this)) != -1)
     DBUG_RETURN(res);
+  if (setup_subquery_caches())
+    DBUG_RETURN(-1);
+
   /*
     Need to tell handlers that to play it safe, it should fetch all
     columns of the primary key of the tables: this is because MySQL may
@@ -1515,6 +1517,152 @@ setup_subq_exit:
 
 
 /**
+  Transform given item with changing it in all_fields if it is needed.
+
+  @param expr            Reference on expression to transform
+  @param transformer     Transformer to apply to the expression
+
+  @details
+  The function transforms the expression expr and, if the top item of the
+  expression has changed, the function looks for the item in
+  JOIN::all_fields and replaces it with the result of transformation.
+*/
+
+void JOIN::transform_and_change_in_all_fields(Item** expr,
+                                              Item_transformer transformer)
+{
+  DBUG_ENTER("JOIN::transform_and_change_in_all_fields");
+  Item *new_item= (*expr)->transform(transformer,
+                                    (uchar*) thd);
+  if (new_item != (*expr))
+  {
+    List_iterator<Item> li(all_fields);
+    Item *it;
+
+    /*
+      Check if this item already has expression cache and if it has then use
+      that cache instead of the cache we have just created
+   */
+    while ((it= li++))
+    {
+      if (((*expr) == it->get_cached_item()))
+      {
+        /*
+          We have to forget about the created cache, but this situation is
+          really rare.
+        */
+        new_item->cleanup();
+        new_item= it;
+        DBUG_PRINT("info", ("Other cache found"));
+        break;
+      }
+    }
+
+    li.rewind();
+    while ((it= li++))
+    {
+      if (it == (*expr))
+      {
+        li.replace(new_item);
+        DBUG_PRINT("info", ("Cache Added"));
+      }
+    }
+    *expr= new_item;
+  }
+  DBUG_VOID_RETURN;
+}
+
+
+/**
+  Setup expression caches for subqueries that need them
+
+  @details
+  The function wraps correlated subquery expressions that return one value
+  into objects of the class Item_cache_wrapper setting up an expression
+  cache for each of them. The result values of the subqueries are to be
+  cached together with the corresponding sets of the parameters - outer
+  references of the subqueries.
+
+  @retval FALSE OK
+  @retval TRUE  Error
+*/
+
+bool JOIN::setup_subquery_caches()
+{
+  DBUG_ENTER("JOIN::setup_subquery_caches");
+
+  /*
+    We have to check all this condition together because items created in
+    one of this clauses can be moved to another one by optimizer
+  */
+  if (select_lex->expr_cache_may_be_used[IN_WHERE] ||
+      select_lex->expr_cache_may_be_used[IN_HAVING] ||
+      select_lex->expr_cache_may_be_used[IN_ON] ||
+      select_lex->expr_cache_may_be_used[NO_MATTER])
+  {
+    if (conds)
+      conds= conds->transform(&Item::expr_cache_insert_transformer,
+                              (uchar*) thd);
+    for (JOIN_TAB *tab= join_tab + const_tables;
+         tab < join_tab + tables ;
+         tab++)
+    {
+      if (tab->select_cond)
+        tab->select_cond=
+          tab->select_cond->transform(&Item::expr_cache_insert_transformer,
+                                      (uchar*) thd);
+      if (tab->cache_select && tab->cache_select->cond)
+        tab->cache_select->cond=
+          tab->cache_select->
+          cond->transform(&Item::expr_cache_insert_transformer,
+                          (uchar*) thd);
+
+    }
+
+    if (having)
+      having= having->transform(&Item::expr_cache_insert_transformer,
+                                (uchar*) thd);
+    if (tmp_having)
+    {
+      DBUG_ASSERT(having == NULL);
+      tmp_having= tmp_having->transform(&Item::expr_cache_insert_transformer,
+                                        (uchar*) thd);
+    }
+  }
+  if (select_lex->expr_cache_may_be_used[SELECT_LIST] ||
+      select_lex->expr_cache_may_be_used[IN_GROUP_BY] ||
+      select_lex->expr_cache_may_be_used[NO_MATTER])
+  {
+    List_iterator<Item> li(fields_list);
+    Item *item;
+    while ((item= li++))
+    {
+      Item *new_item=
+        item->transform(&Item::expr_cache_insert_transformer, (uchar*) thd);
+      if (new_item != item)
+      {
+        thd->change_item_tree(li.ref(), new_item);
+      }
+    }
+    for (ORDER *group= group_list; group ; group= group->next)
+    {
+      transform_and_change_in_all_fields(group->item,
+                                         &Item::expr_cache_insert_transformer);
+    }
+  }
+  if (select_lex->expr_cache_may_be_used[NO_MATTER])
+  {
+    for (ORDER *ord= order; ord; ord= ord->next)
+    {
+      transform_and_change_in_all_fields(ord->item,
+                                         &Item::expr_cache_insert_transformer);
+    }
+  }
+  DBUG_RETURN(FALSE);
+}
+
+
+/**
   Restore values in temporary join.
 */
 void JOIN::restore_tmp()
@@ -5244,7 +5392,7 @@ greedy_search(JOIN      *join,
         'join->best_positions' contains a complete optimal extension of the
         current partial QEP.
       */
-      DBUG_EXECUTE("opt", print_plan(join, join->tables,
+      DBUG_EXECUTE("opt", print_plan(join, n_tables,
                                      record_count, read_time, read_time,
                                      "optimal"););
       DBUG_RETURN(FALSE);
@@ -6035,7 +6183,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
 	store_key_item tmp(thd, keyinfo->key_part[i].field,
                            key_buff + maybe_null,
                            maybe_null ?  key_buff : 0,
-                           keyinfo->key_part[i].length, keyuse->val);
+                           keyinfo->key_part[i].length, keyuse->val,
+                           FALSE);
 	if (thd->is_fatal_error)
 	  DBUG_RETURN(TRUE);
 	tmp.copy();
@@ -6117,7 +6266,7 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables,
 			    key_buff + maybe_null,
 			    maybe_null ? key_buff : 0,
 			    key_part->length,
-			    keyuse->val);
+			    keyuse->val, FALSE);
 }
 
 /**
@@ -7156,7 +7305,7 @@ end_sj_materialize(JOIN *join, JOIN_TAB *join_tab, bool end_of_records)
       if (item->is_null())
         DBUG_RETURN(NESTED_LOOP_OK);
     }
-    fill_record(thd, table->field, sjm->sjm_table_cols, 1);
+    fill_record(thd, table->field, sjm->sjm_table_cols, TRUE, FALSE);
     if (thd->is_error())
       DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */
     if ((error= table->file->ha_write_row(table->record[0])))
@@ -7722,6 +7871,72 @@ void JOIN_TAB::cleanup()
 
 
 /**
+  Build a TABLE_REF structure for index lookup in the temporary table
+
+  @param thd             Thread handle
+  @param tmp_key         The temporary table key
+  @param it              The iterator of items for lookup in the key
+
+  @details
+  Build TABLE_REF object for lookup in the key 'tmp_key' using items
+  accessible via item iterator 'it'.
+
+  @retval TRUE  Error
+  @retval FALSE OK
+*/
+
+bool TABLE_REF::tmp_table_index_lookup_init(THD *thd,
+                                            KEY *tmp_key,
+                                            Item_iterator &it,
+                                            bool value)
+{
+  uint tmp_key_parts= tmp_key->key_parts;
+  DBUG_ENTER("TABLE_REF::tmp_table_index_lookup_init");
+
+  key= 0; /* The only temp table index. */
+  key_length= tmp_key->key_length;
+  if (!(key_buff=
+        (uchar*) thd->calloc(ALIGN_SIZE(tmp_key->key_length) * 2)) ||
+      !(key_copy=
+        (store_key**) thd->alloc((sizeof(store_key*) *
+                                  (tmp_key_parts + 1)))) ||
+      !(items=
+        (Item**) thd->alloc(sizeof(Item*) * tmp_key_parts)))
+    DBUG_RETURN(TRUE);
+
+  key_buff2= key_buff + ALIGN_SIZE(tmp_key->key_length);
+
+  KEY_PART_INFO *cur_key_part= tmp_key->key_part;
+  store_key **ref_key= key_copy;
+  uchar *cur_ref_buff= key_buff;
+
+  it.open();
+  for (uint i= 0; i < tmp_key_parts; i++, cur_key_part++, ref_key++)
+  {
+    Item *item= it.next();
+    DBUG_ASSERT(item);
+    items[i]= item;
+    int null_count= test(cur_key_part->field->real_maybe_null());
+    *ref_key= new store_key_item(thd, cur_key_part->field,
+                                 /* TIMOUR:
+                                    the NULL byte is taken into account in
+                                    cur_key_part->store_length, so instead of
+                                    cur_ref_buff + test(maybe_null), we could
+                                    use that information instead.
+                                 */
+                                 cur_ref_buff + null_count,
+                                 null_count ? key_buff : 0,
+                                 cur_key_part->length, items[i], value);
+    cur_ref_buff+= cur_key_part->store_length;
+  }
+  *ref_key= NULL; /* End marker. */
+  key_err= 1;
+  key_parts= tmp_key_parts;
+  DBUG_RETURN(FALSE);
+}
+
+
+/**
   Partially cleanup JOIN after it has executed: close index or rnd read
   (table cursors), free quick selects.
 
@@ -10829,6 +11044,8 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
   case Item::REF_ITEM:
   case Item::NULL_ITEM:
   case Item::VARBIN_ITEM:
+  case Item::CACHE_ITEM:
+  case Item::EXPR_CACHE_ITEM:
     if (make_copy_field)
     {
       DBUG_ASSERT(((Item_result_field*)item)->result_field);
@@ -11089,6 +11306,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
         if (item->used_tables() & OUTER_REF_TABLE_BIT)
           item->update_used_tables();
         if (type == Item::SUBSELECT_ITEM ||
+            (item->get_cached_item() &&
+             item->get_cached_item()->type() == Item::SUBSELECT_ITEM ) ||
             (item->used_tables() & ~OUTER_REF_TABLE_BIT))
         {
 	  /*
@@ -11616,7 +11835,8 @@ create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields,
                                   &param->recinfo, select_options))
       goto err;
   }
-  if (open_tmp_table(table))
+  DBUG_PRINT("info", ("skip_create_table: %d", (int)param->skip_create_table));
+  if (!param->skip_create_table && open_tmp_table(table))
     goto err;
 
   thd->mem_root= mem_root_save;
@@ -12609,7 +12829,8 @@ sub_select_sjm(JOIN *join, JOIN_TAB *join_tab, bool end_of_records)
   else
   {
     /* Do index lookup in the materialized table */
-    if ((res= join_read_key2(join_tab, sjm->table, sjm->tab_ref)) == 1)
+    if ((res= join_read_key2(join_tab->join->thd, join_tab,
+                             sjm->table, sjm->tab_ref)) == 1)
       DBUG_RETURN(NESTED_LOOP_ERROR); /* purecov: inspected */
     if (res || !sjm->in_equality->val_int())
       DBUG_RETURN(NESTED_LOOP_NO_MORE_ROWS);
@@ -13409,25 +13630,24 @@ join_read_const(JOIN_TAB *tab)
 static int
 join_read_key(JOIN_TAB *tab)
 {
-  return join_read_key2(tab, tab->table, &tab->ref);
+  return join_read_key2(tab->join->thd, tab, tab->table, &tab->ref);
 }
 
 
-/* 
+/*
   eq_ref access handler but generalized a bit to support TABLE and TABLE_REF
   not from the join_tab. See join_read_key for detailed synopsis.
 */
-static int
-join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref)
+int join_read_key2(THD *thd, JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref)
 {
   int error;
   if (!table->file->inited)
   {
-    table->file->ha_index_init(table_ref->key, tab->sorted);
+    table->file->ha_index_init(table_ref->key, (tab ? tab->sorted : TRUE));
   }
 
   /* TODO: Why don't we do "Late NULLs Filtering" here? */
-  if (cmp_buffer_with_ref(tab->join->thd, table, table_ref) ||
+  if (cmp_buffer_with_ref(thd, table, table_ref) ||
       (table->status & (STATUS_GARBAGE | STATUS_NO_PARENT | STATUS_NULL_ROW)))
   {
     if (table_ref->key_err)
@@ -13439,10 +13659,10 @@ join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref)
       Moving away from the current record. Unlock the row
       in the handler if it did not match the partial WHERE.
     */
-    if (tab->ref.has_record && tab->ref.use_count == 0)
+    if (tab && tab->ref.has_record && tab->ref.use_count == 0)
     {
       tab->read_record.file->unlock_row();
-      tab->ref.has_record= FALSE;
+      table_ref->has_record= FALSE;
     }
     error=table->file->ha_index_read_map(table->record[0],
                                   table_ref->key_buff,
@@ -13453,14 +13673,14 @@ join_read_key2(JOIN_TAB *tab, TABLE *table, TABLE_REF *table_ref)
 
     if (! error)
     {
-      tab->ref.has_record= TRUE;
-      tab->ref.use_count= 1;
+      table_ref->has_record= TRUE;
+      table_ref->use_count= 1;
     }
   }
   else if (table->status == 0)
   {
-    DBUG_ASSERT(tab->ref.has_record);
-    tab->ref.use_count++;
+    DBUG_ASSERT(table_ref->has_record);
+    table_ref->use_count++;
   }
   table->null_row=0;
   return table->status ? -1 : 0;
@@ -16960,6 +17180,8 @@ setup_copy_fields(THD *thd, TMP_TABLE_PARAM *param,
     }
     else if ((real_pos->type() == Item::FUNC_ITEM ||
 	      real_pos->type() == Item::SUBSELECT_ITEM ||
+              (real_pos->get_cached_item() &&
+               real_pos->get_cached_item()->type() == Item::SUBSELECT_ITEM) ||
 	      real_pos->type() == Item::CACHE_ITEM ||
 	      real_pos->type() == Item::COND_ITEM) &&
 	     !real_pos->with_sum_func)
diff --git a/sql/sql_select.h b/sql/sql_select.h
index b75813fcbcb..2b7fc8fd47c 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -14,6 +14,8 @@
    Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA */
 
 
+#ifndef SQL_SELECT_INCLUDED
+#define SQL_SELECT_INCLUDED
 /**
   @file
 
@@ -114,6 +116,9 @@ typedef struct st_table_ref
 
   */
   bool          disable_cache;
+
+  bool tmp_table_index_lookup_init(THD *thd, KEY *tmp_key, Item_iterator &it,
+                                   bool value);
 } TABLE_REF;
 
 
@@ -1729,6 +1734,7 @@ public:
             ((group || tmp_table_param.sum_func_count) && !group_list)) ?
               NULL : join_tab+const_tables;
   }
+  bool setup_subquery_caches();
 private:
   /**
     TRUE if the query contains an aggregate function but has no GROUP
@@ -1736,6 +1742,8 @@ private:
   */
   bool implicit_grouping; 
   bool make_simple_join(JOIN *join, TABLE *tmp_table);
+  void transform_and_change_in_all_fields(Item** item,
+                                          Item_transformer transformer);
 };
 
 
@@ -1867,12 +1875,17 @@ class store_key_item :public store_key
 {
  protected:
   Item *item;
+  /*
+    Flag that forces usage of save_val() method which save value of the
+    item instead of save_in_field() method which saves result.
+  */
+  bool use_value;
 public:
   store_key_item(THD *thd, Field *to_field_arg, uchar *ptr,
-                 uchar *null_ptr_arg, uint length, Item *item_arg)
+                 uchar *null_ptr_arg, uint length, Item *item_arg, bool val)
     :store_key(thd, to_field_arg, ptr,
 	       null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ?
-	       &err : (uchar*) 0, length), item(item_arg)
+	       &err : (uchar*) 0, length), item(item_arg), use_value(val)
   {}
   const char *name() const { return "func"; }
 
@@ -1882,7 +1895,11 @@ public:
     TABLE *table= to_field->table;
     my_bitmap_map *old_map= dbug_tmp_use_all_columns(table,
                                                      table->write_set);
-    int res= item->save_in_field(to_field, 1);
+    int res= FALSE;
+    if (use_value)
+      item->save_val(to_field);
+    else
+      res= item->save_in_field(to_field, 1);
     /*
      Item::save_in_field() may call Item::val_xxx(). And if this is a subquery
      we need to check for errors executing it and react accordingly
@@ -1906,7 +1923,7 @@ public:
 		       Item *item_arg)
     :store_key_item(thd, to_field_arg,ptr,
 		    null_ptr_arg ? null_ptr_arg : item_arg->maybe_null ?
-		    &err : (uchar*) 0, length, item_arg), inited(0)
+		    &err : (uchar*) 0, length, item_arg, FALSE), inited(0)
   {
   }
   const char *name() const { return "const"; }
@@ -1997,6 +2014,4 @@ bool create_internal_tmp_table(TABLE *table, KEY *keyinfo,
 bool open_tmp_table(TABLE *table);
 void setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps);
 
-
-
-
+#endif /* SQL_SELECT_INCLUDED */
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index ee9ff833726..21fe3de4c17 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -57,7 +57,7 @@ bool select_union::send_data(List<Item> &values)
     unit->offset_limit_cnt--;
     return 0;
   }
-  fill_record(thd, table->field, values, 1);
+  fill_record(thd, table->field, values, TRUE, FALSE);
   if (thd->is_error())
     return 1;
 
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 3b3063063af..a7d12ce6efd 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -1866,7 +1866,7 @@ bool multi_update::send_data(List<Item> &not_used_values)
       /* Store regular updated fields in the row. */
       fill_record(thd,
                   tmp_table->field + 1 + unupdated_check_opt_tables.elements,
-                  *values_for_table[offset], 1);
+                  *values_for_table[offset], TRUE, FALSE);
 
       /* Write row, ignoring duplicated updates to a row */
       error= tmp_table->file->ha_write_row(tmp_table->record[0]);
diff --git a/sql/table.cc b/sql/table.cc
index 177dfc6fec1..3a4ea3064be 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -21,6 +21,8 @@
 #include "create_options.h"
 #include <m_ctype.h>
 #include "my_md5.h"
+#include "my_bit.h"
+#include "sql_select.h"
 
 /* INFORMATION_SCHEMA name */
 LEX_STRING INFORMATION_SCHEMA_NAME= {C_STRING_WITH_LEN("information_schema")};
@@ -5130,6 +5132,126 @@ void st_table::mark_virtual_columns_for_write(void)
     file->column_bitmaps_signal();
 }
 
+
+/**
+  Allocate space for keys
+
+  @param key_count  number of keys to allocate.
+
+  @details
+  Allocates space enough to fit 'key_count' keys for this table.
+
+  @return FALSE space was successfully allocated.
+  @return TRUE an error occur.
+*/
+
+bool TABLE::alloc_keys(uint key_count)
+{
+  DBUG_ASSERT(!s->keys);
+  key_info= s->key_info= (KEY*) alloc_root(&mem_root, sizeof(KEY)*key_count);
+  max_keys= key_count;
+  return !(key_info);
+}
+
+
+/**
+  Add a key to a temporary  table
+
+  @param key            the number of the key
+  @param key_parts      number of components of the key
+  @param next_field_no  the call-back function that returns the number of
+                        the field used as the next component of the key
+  @param arg            the argument for the above function
+
+  @details
+  The function adds a new key to the table that is assumed to be
+  temprary table. The call-back function must at each call must return
+  the number of the field that used as next component of this key
+
+  @return FALSE is a success
+  @return TRUE if a failure
+*/
+
+bool TABLE::add_tmp_key(uint key, uint key_parts,
+                        uint (*next_field_no) (uchar *), uchar *arg)
+{
+  DBUG_ASSERT(key < max_keys);
+
+  char buf[NAME_CHAR_LEN];
+  KEY* keyinfo;
+  Field **reg_field;
+  uint i;
+  bool key_start= TRUE;
+  KEY_PART_INFO* key_part_info=
+      (KEY_PART_INFO*) alloc_root(&mem_root, sizeof(KEY_PART_INFO)*key_parts);
+  if (!key_part_info)
+    return TRUE;
+  keyinfo= key_info + key;
+  keyinfo->key_part= key_part_info;
+  keyinfo->usable_key_parts= keyinfo->key_parts = key_parts;
+  keyinfo->key_length=0;
+  keyinfo->algorithm= HA_KEY_ALG_UNDEF;
+  keyinfo->flags= HA_GENERATED_KEY;
+  sprintf(buf, "key%i", key);
+  if (!(keyinfo->name= strdup_root(&mem_root, buf)))
+    return TRUE;
+  keyinfo->rec_per_key= (ulong*) alloc_root(&mem_root,
+                                            sizeof(ulong)*key_parts);
+  if (!keyinfo->rec_per_key)
+    return TRUE;
+  bzero(keyinfo->rec_per_key, sizeof(ulong)*key_parts);
+  for (i= 0; i < key_parts; i++)
+  {
+    reg_field= field + next_field_no(arg);
+    if (key_start)
+      (*reg_field)->key_start.set_bit(key);
+    key_start= FALSE;
+      (*reg_field)->part_of_key.set_bit(key);
+    (*reg_field)->flags|= PART_KEY_FLAG;
+    key_part_info->null_bit= (*reg_field)->null_bit;
+    key_part_info->null_offset= (uint) ((*reg_field)->null_ptr -
+                                          (uchar*) record[0]);
+    key_part_info->field=    *reg_field;
+    key_part_info->offset=   (*reg_field)->offset(record[0]);
+    key_part_info->length=   (uint16) (*reg_field)->pack_length();
+    keyinfo->key_length+= key_part_info->length;
+    key_part_info->key_part_flag= 0;
+    /* TODO:
+      The below method of computing the key format length of the
+      key part is a copy/paste from opt_range.cc, and table.cc.
+      This should be factored out, e.g. as a method of Field.
+      In addition it is not clear if any of the Field::*_length
+      methods is supposed to compute the same length. If so, it
+      might be reused.
+    */
+    key_part_info->store_length= key_part_info->length;
+
+    if ((*reg_field)->real_maybe_null())
+    {
+      key_part_info->store_length+= HA_KEY_NULL_LENGTH;
+      keyinfo->key_length+= HA_KEY_NULL_LENGTH;
+    }
+    if ((*reg_field)->type() == MYSQL_TYPE_BLOB || 
+        (*reg_field)->real_type() == MYSQL_TYPE_VARCHAR)
+    {
+      key_part_info->store_length+= HA_KEY_BLOB_LENGTH;
+      keyinfo->key_length+= HA_KEY_BLOB_LENGTH; // ???
+    }
+
+    key_part_info->type=     (uint8) (*reg_field)->key_type();
+    key_part_info->key_type =
+      ((ha_base_keytype) key_part_info->type == HA_KEYTYPE_TEXT ||
+       (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT1 ||
+       (ha_base_keytype) key_part_info->type == HA_KEYTYPE_VARTEXT2) ?
+      0 : FIELDFLAG_BINARY;
+    key_part_info++;
+  }
+  set_if_bigger(s->max_key_length, keyinfo->key_length);
+  s->keys++;
+  return FALSE;
+}
+
+
 /**
   @brief Check if this is part of a MERGE table with attached children.
 
diff --git a/sql/table.h b/sql/table.h
index be15c2059a7..5102955e2ad 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -785,6 +785,7 @@ struct st_table {
   uint          temp_pool_slot;		/* Used by intern temp tables */
   uint		status;                 /* What's in record[0] */
   uint		db_stat;		/* mode of file as in handler.h */
+  uint          max_keys;               /* Size of allocated key_info array. */
   /* number of select if it is derived table */
   uint          derived_select_number;
   int		current_lock;           /* Type of lock on table */
@@ -917,6 +918,9 @@ struct st_table {
   */
   inline bool needs_reopen_or_name_lock()
   { return s->version != refresh_version; }
+  bool alloc_keys(uint key_count);
+  bool add_tmp_key(uint key, uint key_parts,
+                   uint (*next_field_no) (uchar *), uchar *arg);
   bool is_children_attached(void);
   inline void enable_keyread()
   {