Bug#23800: Outer fields in correlated subqueries is used in a temporary table

created for sorting.

Any outer reference in a subquery was represented by an Item_field object.
If the outer select employs a temporary table all such fields should be
replaced with fields from that temporary table in order to point to the 
actual data. This replacement wasn't done and that resulted in a wrong
subquery evaluation and a wrong result of the whole query.

Now any outer field is represented by two objects - Item_field placed in the
outer select and Item_outer_ref in the subquery. Item_field object is
processed as a normal field and the reference to it is saved in the
ref_pointer_array. Thus the Item_outer_ref is always references the correct
field. The original field is substituted for a reference in the
Item_field::fix_outer_field() function.

New function called fix_inner_refs() is added to fix fields referenced from
inner selects and to fix references (Item_ref objects) to these fields.

The new Item_outer_ref class is a descendant of the Item_direct_ref class.
It additionally stores a reference to the original field and designed to
behave more like a field.


sql/item.cc:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Now all outer fields are substituted with references to them (Item_outer_ref objects)
  in the Item_field::fix_outer_field() function.
  The original field is saved in the Item_outer_ref object.
sql/item.h:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Added the Item_outer_ref class.
sql/mysql_priv.h:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Added the fix_inner_refs() function prototype.
sql/sql_delete.cc:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Added call to the fix_inner_refs() function.
sql/sql_select.cc:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  The new function called fix_inner_refs() is added.
mysql-test/r/subselect.result:
  Added a test case for bug#23800: Correlated sub query returning incorrect results when
  operated upon.
sql/sql_update.cc:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Added call to the fix_inner_refs() function.
mysql-test/r/subselect3.result:
  Corrected test cases result after fix for bug#23800: Correlated sub query returning
  incorrect results when operated upon.
mysql-test/t/subselect.test:
  Added a test case for bug#23800: Correlated sub query returning incorrect results when
  operated upon.
sql/sql_lex.cc:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  Added cleanup of the inner_refs_list.
sql/sql_lex.h:
  Bug#23800: Correlated sub query returning incorrect results when operated upon.
  The inner_refs_list is added to the SELECT_LEX class.

8 files changed, 200 insertions, 20 deletions

diff --git a/sql/item.cc b/sql/item.cc
index 95001809e9a..54aaf573525 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1609,7 +1609,7 @@ void Item_ident_for_show::make_field(Send_field *tmp_field)
 Item_field::Item_field(Field *f)
   :Item_ident(0, NullS, *f->table_name, f->field_name),
   item_equal(0), no_const_subst(0),
-   have_privileges(0), any_privileges(0)
+   have_privileges(0), any_privileges(0), fixed_as_field(0)
 {
   set_field(f);
   /*
@@ -1623,7 +1623,7 @@ Item_field::Item_field(THD *thd, Name_resolution_context *context_arg,
                        Field *f)
   :Item_ident(context_arg, f->table->s->db, *f->table_name, f->field_name),
    item_equal(0), no_const_subst(0),
-   have_privileges(0), any_privileges(0)
+   have_privileges(0), any_privileges(0), fixed_as_field(0)
 {
   /*
     We always need to provide Item_field with a fully qualified field
@@ -1662,7 +1662,7 @@ Item_field::Item_field(Name_resolution_context *context_arg,
                        const char *field_name_arg)
   :Item_ident(context_arg, db_arg,table_name_arg,field_name_arg),
    field(0), result_field(0), item_equal(0), no_const_subst(0),
-   have_privileges(0), any_privileges(0)
+   have_privileges(0), any_privileges(0), fixed_as_field(0)
 {
   collation.set(DERIVATION_IMPLICIT);
 }
@@ -1675,7 +1675,8 @@ Item_field::Item_field(THD *thd, Item_field *item)
    item_equal(item->item_equal),
    no_const_subst(item->no_const_subst),
    have_privileges(item->have_privileges),
-   any_privileges(item->any_privileges)
+   any_privileges(item->any_privileges),
+   fixed_as_field(item->fixed_as_field)
 {
   collation.set(DERIVATION_IMPLICIT);
 }
@@ -3484,8 +3485,46 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
         }
         if (*from_field != view_ref_found)
         {
+
           prev_subselect_item->used_tables_cache|= (*from_field)->table->map;
           prev_subselect_item->const_item_cache= 0;
+          if (!last_checked_context->select_lex->having_fix_field &&
+              !fixed_as_field)
+          {
+            Item_outer_ref *rf;
+            Query_arena *arena= 0, backup;
+            /*
+              Each outer field is replaced for an Item_outer_ref object.
+              This is done in order to get correct results when the outer
+              select employs a temporary table.
+              The original fields are saved in the inner_fields_list of the
+              outer select. This list is created by the following reasons:
+              1. We can't add field items to the outer select list directly
+                 because the outer select hasn't been fully fixed yet.
+              2. We need a location to refer to in the Item_ref object
+                 so the inner_fields_list is used as such temporary
+                 reference storage.
+              The new Item_outer_ref object replaces the original field and is
+              also saved in the inner_refs_list of the outer select. Here
+              it is only created. It can be fixed only after the original
+              field has been fixed and this is done in the fix_inner_refs()
+              function.
+            */
+            set_field(*from_field);
+            arena= thd->activate_stmt_arena_if_needed(&backup);
+            rf= new Item_outer_ref(context, this);
+            if (!rf)
+            {
+              if (arena)
+                thd->restore_active_arena(arena, &backup);
+              return -1;
+            }
+            *reference= rf;
+            select->inner_refs_list.push_back(rf);
+            if (arena)
+              thd->restore_active_arena(arena, &backup);
+            fixed_as_field= 1;
+          }
           if (thd->lex->in_sum_func &&
               thd->lex->in_sum_func->nest_level == 
               thd->lex->current_select->nest_level)
@@ -3612,7 +3651,7 @@ 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, this);
+                      this, (Item_ident*)*reference);
     if (last_checked_context->select_lex->having_fix_field)
     {
       Item_ref *rf;
@@ -4818,8 +4857,7 @@ Item_ref::Item_ref(Name_resolution_context *context_arg,
   /*
     This constructor used to create some internals references over fixed items
   */
-  DBUG_ASSERT(ref != 0);
-  if (*ref && (*ref)->fixed)
+  if (ref && *ref && (*ref)->fixed)
     set_properties();
 }
 
@@ -5119,7 +5157,7 @@ void Item_ref::print(String *str)
   if (ref)
   {
     if ((*ref)->type() != Item::CACHE_ITEM && ref_type() != VIEW_REF &&
-        name && alias_name_used)
+        ref_type() != OUTER_REF && name && alias_name_used)
     {
       THD *thd= current_thd;
       append_identifier(thd, str, name, (uint) strlen(name));
@@ -5367,7 +5405,7 @@ bool Item_direct_ref::get_date(TIME *ltime,uint fuzzydate)
 
 
 /*
-  Prepare referenced view viewld then call usual Item_direct_ref::fix_fields
+  Prepare referenced field then call usual Item_direct_ref::fix_fields
 
   SYNOPSIS
     Item_direct_view_ref::fix_fields()
@@ -5391,6 +5429,30 @@ bool Item_direct_view_ref::fix_fields(THD *thd, Item **reference)
 }
 
 /*
+  Prepare referenced outer field then call usual Item_direct_ref::fix_fields
+
+  SYNOPSIS
+    Item_outer_ref::fix_fields()
+    thd         thread handler
+    reference   reference on reference where this item stored
+
+  RETURN
+    FALSE   OK
+    TRUE    Error
+*/
+
+bool Item_outer_ref::fix_fields(THD *thd, Item **reference)
+{
+  DBUG_ASSERT(*ref);
+  /* outer_field->check_cols() will be made in Item_direct_ref::fix_fields */
+  outer_field->fixed_as_field= 1;
+  if (!outer_field->fixed &&
+      (outer_field->fix_fields(thd, reference)))
+    return TRUE;
+  return Item_direct_ref::fix_fields(thd, reference);
+}
+
+/*
   Compare two view column references for equality.
 
   SYNOPSIS
diff --git a/sql/item.h b/sql/item.h
index 6c41aa09f80..c2bc483e87b 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -1214,7 +1214,7 @@ public:
   uint have_privileges;
   /* field need any privileges (for VIEW creation) */
   bool any_privileges;
-
+  bool fixed_as_field;
   Item_field(Name_resolution_context *context_arg,
              const char *db_arg,const char *table_name_arg,
 	     const char *field_name_arg);
@@ -1817,7 +1817,7 @@ class Item_ref :public Item_ident
 protected:
   void set_properties();
 public:
-  enum Ref_Type { REF, DIRECT_REF, VIEW_REF };
+  enum Ref_Type { REF, DIRECT_REF, VIEW_REF, OUTER_REF };
   Field *result_field;			 /* Save result here */
   Item **ref;
   Item_ref(Name_resolution_context *context_arg,
@@ -1878,7 +1878,7 @@ public:
                           (*ref)->get_tmp_table_item(thd));
   }
   table_map used_tables() const		
-  { 
+  {
     return depended_from ? OUTER_REF_TABLE_BIT : (*ref)->used_tables(); 
   }
   table_map not_null_tables() const { return (*ref)->not_null_tables(); }
@@ -1951,6 +1951,40 @@ public:
 };
 
 
+class Item_outer_ref :public Item_direct_ref
+{
+public:
+  Item_field *outer_field;
+  Item_outer_ref(Name_resolution_context *context_arg,
+                 Item_field *outer_field_arg)
+    :Item_direct_ref(context_arg, 0, outer_field_arg->table_name,
+                          outer_field_arg->field_name),
+    outer_field(outer_field_arg)
+  {
+    ref= (Item**)&outer_field;
+    set_properties();
+    fixed= 0;
+  }
+  void cleanup()
+  {
+    ref= (Item**)&outer_field;
+    fixed= 0;
+    Item_direct_ref::cleanup();
+    outer_field->cleanup();
+  }
+  void save_in_result_field(bool no_conversions)
+  {
+    outer_field->save_org_in_field(result_field);
+  }
+  bool fix_fields(THD *, Item **);
+  table_map used_tables() const
+  {
+    return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;
+  }
+  virtual Ref_Type ref_type() { return OUTER_REF; }
+};
+
+
 class Item_in_subselect;
 
 
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 1dd3c9dceca..792afeca59e 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -747,6 +747,8 @@ int setup_order(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
 int setup_group(THD *thd, Item **ref_pointer_array, TABLE_LIST *tables,
 		List<Item> &fields, List<Item> &all_fields, ORDER *order,
 		bool *hidden_group_fields);
+bool fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
+                   Item **ref_pointer_array);
 
 bool handle_select(THD *thd, LEX *lex, select_result *result,
                    ulong setup_tables_done_option);
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 94d753eb703..749ee04493b 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -353,6 +353,7 @@ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds)
   Item *fake_conds= 0;
   SELECT_LEX *select_lex= &thd->lex->select_lex;
   DBUG_ENTER("mysql_prepare_delete");
+  List<Item> all_fields;
 
   thd->lex->allow_sum_func= 0;
   if (setup_tables_and_check_access(thd, &thd->lex->select_lex.context,
@@ -376,6 +377,11 @@ bool mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, Item **conds)
       DBUG_RETURN(TRUE);
     }
   }
+
+  if (select_lex->inner_refs_list.elements &&
+    fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
+    DBUG_RETURN(-1);
+
   select_lex->fix_prepare_information(thd, conds, &fake_conds);
   DBUG_RETURN(FALSE);
 }
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index ce76c35b33c..c3e18394c41 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1192,6 +1192,7 @@ void st_select_lex::init_select()
   is_correlated= 0;
   cur_pos_in_select_list= UNDEF_POS;
   non_agg_fields.empty();
+  inner_refs_list.empty();
 }
 
 /*
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index ae2b0d30a9c..f8d8e6efcb2 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -547,7 +547,8 @@ public:
   bool  braces;   	/* SELECT ... UNION (SELECT ... ) <- this braces */
   /* TRUE when having fix field called in processing of this SELECT */
   bool having_fix_field;
-
+  /* List of references to fields referenced from inner selects */
+  List<Item_outer_ref> inner_refs_list;
   /* Number of Item_sum-derived objects in this SELECT */
   uint n_sum_items;
   /* Number of Item_sum-derived objects in children and descendant SELECTs */
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 63f2604a934..2f2eb0ecca1 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -269,6 +269,70 @@ bool handle_select(THD *thd, LEX *lex, select_result *result,
 
 
 /*
+  Fix fields referenced from inner selects.
+
+  SYNOPSIS
+    fix_inner_refs()
+    thd               Thread handle
+    all_fields        List of all fields used in select
+    select            Current select
+    ref_pointer_array Array of references to Items used in current select
+
+  DESCRIPTION
+    The function fixes fields referenced from inner selects and
+    also fixes references (Item_ref objects) to these fields. Each field
+    is fixed as a usual hidden field of the current select - it is added
+    to the all_fields list and the pointer to it is saved in the
+    ref_pointer_array if latter is provided.
+    After the field has been fixed we proceed with fixing references
+    (Item_ref objects) to this field from inner subqueries. If the
+    ref_pointer_array is provided then Item_ref objects is set to
+    reference element in that array with the pointer to the field.
+
+  RETURN
+    TRUE  an error occured
+    FALSE ok
+*/
+
+bool
+fix_inner_refs(THD *thd, List<Item> &all_fields, SELECT_LEX *select,
+                 Item **ref_pointer_array)
+{
+  Item_outer_ref *ref;
+  bool res= FALSE;
+  List_iterator<Item_outer_ref> ref_it(select->inner_refs_list);
+  while ((ref= ref_it++))
+  {
+    Item_field *item= ref->outer_field;
+    /*
+      TODO: this field item already might be present in the select list.
+      In this case instead of adding new field item we could use an
+      existing one. The change will lead to less operations for copying fields,
+      smaller temporary tables and less data passed through filesort.
+    */
+    if (ref_pointer_array)
+    {
+      int el= all_fields.elements;
+      ref_pointer_array[el]= (Item*)item;
+      /* Add the field item to the select list of the current select. */
+      all_fields.push_front((Item*)item);
+      /*
+        If it's needed reset each Item_ref item that refers this field with
+        a new reference taken from ref_pointer_array.
+      */
+      ref->ref= ref_pointer_array + el;
+    }
+    if (!ref->fixed && ref->fix_fields(thd, 0))
+    {
+      res= TRUE;
+      break;
+    }
+    thd->used_tables|= item->used_tables();
+  }
+  return res;
+}
+
+/*
   Function to setup clauses without sum functions
 */
 inline int setup_without_group(THD *thd, Item **ref_pointer_array,
@@ -395,6 +459,10 @@ JOIN::prepare(Item ***rref_pointer_array,
   if (having && having->with_sum_func)
     having->split_sum_func2(thd, ref_pointer_array, all_fields,
                             &having, TRUE);
+  if (select_lex->inner_refs_list.elements &&
+      fix_inner_refs(thd, all_fields, select_lex, ref_pointer_array))
+    DBUG_RETURN(-1);
+
   if (select_lex->inner_sum_func_list)
   {
     Item_sum *end=select_lex->inner_sum_func_list;
@@ -5133,13 +5201,15 @@ get_store_key(THD *thd, KEYUSE *keyuse, table_map used_tables,
 				    key_part->length,
 				    keyuse->val);
   }
-  else if (keyuse->val->type() == Item::FIELD_ITEM)
+  else if (keyuse->val->type() == Item::FIELD_ITEM ||
+           (keyuse->val->type() == Item::REF_ITEM &&
+            ((Item_ref*)keyuse->val)->ref_type() == Item_ref::OUTER_REF) )
     return new store_key_field(thd,
 			       key_part->field,
 			       key_buff + maybe_null,
 			       maybe_null ? key_buff : 0,
 			       key_part->length,
-			       ((Item_field*) keyuse->val)->field,
+			       ((Item_field*) keyuse->val->real_item())->field,
 			       keyuse->val->full_name());
   return new store_key_item(thd,
 			    key_part->field,
@@ -8697,7 +8767,8 @@ static Field *create_tmp_field_from_item(THD *thd, Item *item, TABLE *table,
       type they needed to be handled separately.
     */
     if ((type= item->field_type()) == MYSQL_TYPE_DATETIME ||
-        type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE)
+        type == MYSQL_TYPE_TIME || type == MYSQL_TYPE_DATE ||
+        type == MYSQL_TYPE_TIMESTAMP)
       new_field= item->tmp_table_field_from_field_type(table);
     /* 
       Make sure that the blob fits into a Field_varstring which has 
@@ -8804,8 +8875,7 @@ Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
   Item *orig_item= 0;
 
   if (type != Item::FIELD_ITEM &&
-      item->real_item()->type() == Item::FIELD_ITEM &&
-      !((Item_ref *) item)->depended_from)
+      item->real_item()->type() == Item::FIELD_ITEM)
   {
     orig_item= item;
     item= item->real_item();
@@ -13465,8 +13535,7 @@ count_field_types(TMP_TABLE_PARAM *param, List<Item> &fields,
   {
     Item::Type type=field->type();
     Item::Type real_type= field->real_item()->type();
-    if (type == Item::FIELD_ITEM || (real_type == Item::FIELD_ITEM &&
-        !((Item_ref *) field)->depended_from))
+    if (real_type == Item::FIELD_ITEM)
       param->field_count++;
     else if (real_type == Item::SUM_FUNC_ITEM)
     {
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 1db77f8704c..60b7ac1df1c 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -134,6 +134,7 @@ int mysql_update(THD *thd,
   READ_RECORD	info;
   SELECT_LEX    *select_lex= &thd->lex->select_lex;
   bool need_reopen;
+  List<Item> all_fields;
   DBUG_ENTER("mysql_update");
 
   LINT_INIT(timestamp_query_id);
@@ -226,6 +227,10 @@ int mysql_update(THD *thd,
     DBUG_RETURN(1);				/* purecov: inspected */
   }
 
+  if (select_lex->inner_refs_list.elements &&
+    fix_inner_refs(thd, all_fields, select_lex, select_lex->ref_pointer_array))
+    DBUG_RETURN(-1);
+
   if (conds)
   {
     Item::cond_result cond_value;