Fixed LP BUG#800696.

The problem was that optimizer removes some outer references (it they are
constant for example) and the list of outer items built during prepare phase is
not actual during execution phase when we need it as the cache parameters.
First solution was use pointer on pointer on outer reference Item and
initialize temporary table on demand. This solved most problem except case
when optimiser also reduce Item which contains outer references ('OR' in
this bug test suite).

The solution is to build the list of outer reference items on execution
phase (after optimization) on demand (just before temporary table creation)
by walking Item tree and finding outer references among Item_ident
(Item_field/Item_ref) and Item_sum items.

Removed depends_on list (because it is not neede any mnore for the cache, in the place where it was used it replaced with upper_refs).

Added processor (collect_outer_ref_processor) and get_cache_parameters() methods to collect outer references (or other expression parameters in future).

mysql-test/r/subselect_cache.result:
  A new test added.
mysql-test/r/subselect_scache.result:
  Changes in creating the cache and its paremeters order or adding arguments of aggregate function (which is a parameter also, but this has no influence on the result).
mysql-test/t/subselect_cache.test:
  Added a new test.
sql/item.cc:
  depends_on removed.
  
  Added processor (collect_outer_ref_processor) and get_cache_parameters() methods to collect outer references.
  
  Item_cache_wrapper collect parameters befor initialization of its cache.
sql/item.h:
  depends_on removed.
  
  Added processor (collect_outer_ref_processor) and get_cache_parameters() methods to collect outer references.
sql/item_cmpfunc.cc:
  depends_on removed.
  
  Added processor (collect_outer_ref_processor) to collect outer references.
sql/item_cmpfunc.h:
  Added processor (collect_outer_ref_processor) to collect outer references.
sql/item_subselect.cc:
  depends_on removed.
  Added processor get_cache_parameters() method to collect outer references.
sql/item_subselect.h:
  depends_on removed.
  Added processor get_cache_parameters() method to collect outer references.
sql/item_sum.cc:
  Added processor (collect_outer_ref_processor) method to collect outer references.
sql/item_sum.h:
  Added processor (collect_outer_ref_processor) and get_cache_parameters() methods to collect outer references.
sql/opt_range.cc:
  depends_on removed.
sql/sql_base.cc:
  depends_on removed.
sql/sql_class.h:
  New iterator added.
sql/sql_expression_cache.cc:
  Build of list of items resolved in outer query done just before creating expression cache on the first execution of the subquery which removes influence of optimizer removing items (all optimization already done).
sql/sql_expression_cache.h:
  Build of list of items resolved in outer query done just before creating expression cache on the first execution of the subquery which removes influence of optimizer removing items (all optimization already done).
sql/sql_lex.cc:
  depends_on removed.
sql/sql_lex.h:
  depends_on removed.
sql/sql_list.h:
  Added add_unique method to add only unique elements to the list.
sql/sql_select.cc:
  Support of new Item list added.
sql/sql_select.h:
  Support of new Item list added.

1 files changed, 38 insertions, 3 deletions

diff --git a/sql/item.h b/sql/item.h
index de819dae8ee..f2e7a2587da 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1155,6 +1155,15 @@ public:
   {
     return FALSE;
   }
+  struct Collect_deps_prm
+  {
+    int nest_level;
+    List<Item> *parameters;
+  };
+  /**
+    Collect outer references
+  */
+  virtual bool collect_outer_ref_processor(uchar *arg) {return FALSE; }
 
   /**
     Find a function of a given type
@@ -1249,7 +1258,7 @@ 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);
+  Item* set_expr_cache(THD *thd);
   virtual Item *get_cached_item() { return NULL; }
 
   virtual Item_equal *get_item_equal() { return NULL; }
@@ -1273,9 +1282,25 @@ public:
     walk(&Item::view_used_tables_processor, 0, (uchar *) view);
     return view->view_used_tables;
   }
+
+  /**
+    Collect and add to the list cache parameters for this Item.
+
+    @note Now implemented only for subqueries and in_optimizer,
+    if we need it for general function then this method should
+    be defined for Item_func.
+  */
+  virtual void get_cache_parameters(List<Item> &parameters) { };
 };
 
 
+/**
+  Compare two Items for List<Item>::add_unique()
+*/
+
+bool cmp_items(Item *a, Item *b);
+
+
 /*
   Class to be used to enumerate all field references in an item tree. This
   includes references to outside but not fields of the tables within a
@@ -1677,6 +1702,10 @@ public:
   virtual void print(String *str, enum_query_type query_type);
   virtual bool change_context_processor(uchar *cntx)
     { context= (Name_resolution_context *)cntx; return FALSE; }
+  /**
+    Collect outer references
+  */
+  virtual bool collect_outer_ref_processor(uchar *arg);
   friend bool insert_fields(THD *thd, Name_resolution_context *context,
                             const char *db_name,
                             const char *table_name, List_iterator<Item> *it,
@@ -2739,8 +2768,11 @@ private:
   */
   Item_cache *expr_value;
 
+  List<Item> parameters;
+
   Item *check_cache();
-  inline void cache();
+  void cache();
+  void init_on_demand();
 
 public:
   Item_cache_wrapper(Item *item_arg);
@@ -2750,7 +2782,7 @@ public:
   enum Type type() const { return EXPR_CACHE_ITEM; }
   enum Type real_type() const { return orig_item->type(); }
 
-  bool set_cache(THD *thd, List<Item*> &depends_on);
+  bool set_cache(THD *thd);
 
   bool fix_fields(THD *thd, Item **it);
   void fix_length_and_dec() {}
@@ -2832,6 +2864,9 @@ public:
     if (result_type() == ROW_RESULT)
       orig_item->bring_value();
   }
+  virtual bool is_expensive() { return orig_item->is_expensive(); }
+  bool is_expensive_processor(uchar *arg)
+  { return orig_item->is_expensive_processor(arg); }
   bool check_vcol_func_processor(uchar *arg)
   {
     return trace_unsupported_by_check_vcol_func_processor("cache");