Fix of LP bug#968720.

When a view/derived table is converted from merged to materialized the
items from the used_item lists are substituted for items referring to
the fields of the result of the materialization. The problem appeared
with queries employing natural joins. Since the resolution of a natural
join was performed only once the used_item list formed at the second
execution of the query lacked the references to the fields that were
used only in the equality predicates generated for the natural join.

4 files changed, 55 insertions, 3 deletions

diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 03d8a925fc2..2e085ece93d 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -7276,6 +7276,14 @@ mark_common_columns(THD *thd, TABLE_LIST *table_ref_1, TABLE_LIST *table_ref_2,
   */
   result= FALSE;
 
+  /*
+    Save the lists made during natural join matching (because
+    the matching done only once but we need the list in case
+    of prepared statements).
+  */
+  table_ref_1->persistent_used_items= table_ref_1->used_items;
+  table_ref_2->persistent_used_items= table_ref_2->used_items;
+
 err:
   if (arena)
     thd->restore_active_arena(arena, &backup);
diff --git a/sql/sql_list.h b/sql/sql_list.h
index adedd9a3a4d..e71fdc83177 100644
--- a/sql/sql_list.h
+++ b/sql/sql_list.h
@@ -257,6 +257,26 @@ public:
       last= &first;
     return tmp->info;
   }
+
+  /**
+    Cut the list with leaving not more then n elements
+  */
+  inline uint cut(uint n)
+  {
+    list_node *element= first;
+    uint i= 0;
+    for (;
+         i < n && element != &end_of_list;
+         element= element->next, i++);
+    if (element != &end_of_list)
+    {
+      elements= i + 1;
+      last= &element->next;
+      element->next= &end_of_list;
+    }
+    return i + 1;
+  }
+
   /*
     Remove from this list elements that are contained in the passed list. 
     We assume that the passed list is a tail of this list (that is, the whole 
diff --git a/sql/table.cc b/sql/table.cc
index d713ac1166c..8e420c715d5 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -3540,7 +3540,21 @@ bool TABLE_LIST::create_field_translation(THD *thd)
   Query_arena *arena= thd->stmt_arena, backup;
   bool res= FALSE;
 
-  used_items.empty();
+  if (thd->stmt_arena->is_conventional() ||
+      thd->stmt_arena->is_stmt_prepare_or_first_sp_execute())
+  {
+    /* initialize lists */
+    used_items.empty();
+    persistent_used_items.empty();
+  }
+  else
+  {
+    /*
+      Copy the list created by natural join procedure because the procedure
+      will not be repeated.
+    */
+    used_items= persistent_used_items;
+  }
 
   if (field_translation)
   {
@@ -4598,7 +4612,7 @@ Item *create_view_field(THD *thd, TABLE_LIST *view, Item **field_ref,
   if (view->table && view->table->maybe_null)
     item->maybe_null= TRUE;
   /* Save item in case we will need to fall back to materialization. */
-  view->used_items.push_back(item);
+  view->used_items.push_front(item);
   DBUG_RETURN(item);
 }
 
@@ -6070,7 +6084,11 @@ bool TABLE_LIST::change_refs_to_fields()
       if (!materialized_items[idx])
         return TRUE;
     }
-    ref->ref= materialized_items + idx;
+    /*
+      We need to restore the pointers after the execution of the
+      prepared statement.
+    */
+    thd->change_item_tree((Item **)&ref->ref, (Item*)materialized_items + idx);
   }
 
   return FALSE;
diff --git a/sql/table.h b/sql/table.h
index ab63250a8ce..da2109809d4 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1602,7 +1602,13 @@ struct TABLE_LIST
   /* TRUE <=> don't prepare this derived table/view as it should be merged.*/
   bool          skip_prepare_derived;
 
+  /*
+    Items created by create_view_field and collected to change them in case
+    of materialization of the view/derived table
+  */
   List<Item>    used_items;
+  /* Sublist (tail) of persistent used_items */
+  List<Item>    persistent_used_items;
   Item          **materialized_items;
 
   /* View creation context. */