merge

14 files changed, 289 insertions, 34 deletions

diff --git a/sql/filesort.cc b/sql/filesort.cc
index e2bd83f1ac4..bec43d98e9e 100644
--- a/sql/filesort.cc
+++ b/sql/filesort.cc
@@ -1,5 +1,5 @@
 /*
-   Copyright (c) 2000, 2011, Oracle and/or its affiliates.
+   Copyright (c) 2000, 2011, Oracle and/or its affiliates.  
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
diff --git a/sql/item.cc b/sql/item.cc
index 8eb05d49d5d..4fdec8eaca0 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1001,6 +1001,21 @@ void Item::set_name(const char *str, uint length, CHARSET_INFO *cs)
 }
 
 
+void Item::set_name_for_rollback(THD *thd, const char *str, uint length,
+                                 CHARSET_INFO *cs)
+{
+  char *old_name, *new_name; 
+  old_name= name;
+  set_name(str, length, cs);
+  new_name= name;
+  if (old_name != new_name)
+  {
+    name= old_name;
+    thd->change_item_tree((Item **) &name, (Item *) new_name);
+  }
+}
+
+
 /**
   @details
   This function is called when:
diff --git a/sql/item.h b/sql/item.h
index cf13f501c69..a28cc402d2d 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -656,6 +656,8 @@ public:
 #endif
   }		/*lint -e1509 */
   void set_name(const char *str, uint length, CHARSET_INFO *cs);
+  void set_name_for_rollback(THD *thd, const char *str, uint length,
+                             CHARSET_INFO *cs);
   void rename(char *new_name);
   void init_make_field(Send_field *tmp_field,enum enum_field_types type);
   virtual void cleanup();
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index bfd74767669..b1829c1892a 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -2531,7 +2531,8 @@ void Item_in_subselect::update_used_tables()
 {
   Item_subselect::update_used_tables();
   left_expr->update_used_tables();
-  used_tables_cache |= left_expr->used_tables();
+  //used_tables_cache |= left_expr->used_tables();
+  used_tables_cache= Item_subselect::used_tables() | left_expr->used_tables();
 }
 
 
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 633158ae06c..857d9bc2080 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2435,7 +2435,7 @@ bool Item_time_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 
 bool Item_date_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 {
-  if (get_arg0_date(ltime, TIME_FUZZY_DATE))
+  if (get_arg0_date(ltime, fuzzy_date & ~TIME_TIME_ONLY))
     return 1;
 
   ltime->hour= ltime->minute= ltime->second= ltime->second_part= 0;
@@ -3145,7 +3145,7 @@ bool Item_func_str_to_date::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 
 bool Item_func_last_day::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 {
-  if (get_arg0_date(ltime, fuzzy_date & ~TIME_FUZZY_DATE) ||
+  if (get_arg0_date(ltime, fuzzy_date) ||
       (ltime->month == 0))
     return (null_value=1);
   uint month_idx= ltime->month-1;
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 202e97dc190..56b76f5982e 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1475,6 +1475,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
   sj_nest->embedding= emb_tbl_nest;
   sj_nest->alias= (char*) "(sj-nest)";
   sj_nest->sj_subq_pred= subq_pred;
+  sj_nest->original_subq_pred_used_tables= subq_pred->used_tables() |
+                                           subq_pred->left_expr->used_tables();
   /* Nests do not participate in those 'chains', so: */
   /* sj_nest->next_leaf= sj_nest->next_local= sj_nest->next_global == NULL*/
   emb_join_list->push_back(sj_nest);
diff --git a/sql/password.c b/sql/password.c
index f4ff3156bec..1a12a81f7c6 100644
--- a/sql/password.c
+++ b/sql/password.c
@@ -503,7 +503,7 @@ check_scramble(const uchar *scramble_arg, const char *message,
   mysql_sha1_reset(&sha1_context);
   mysql_sha1_input(&sha1_context, buf, SHA1_HASH_SIZE);
   mysql_sha1_result(&sha1_context, hash_stage2_reassured);
-  return memcmp(hash_stage2, hash_stage2_reassured, SHA1_HASH_SIZE);
+  return test(memcmp(hash_stage2, hash_stage2_reassured, SHA1_HASH_SIZE));
 }
 
 
diff --git a/sql/protocol.cc b/sql/protocol.cc
index 63b945f7078..10c85939986 100644
--- a/sql/protocol.cc
+++ b/sql/protocol.cc
@@ -1,5 +1,5 @@
-/* Copyright (c) 2000, 2010, Oracle and/or its affiliates.
-   Copyright (c) 2008-2011 Monty Program Ab
+/* Copyright (c) 2000, 2011, Oracle and/or its affiliates.
+   Copyright (c) 2008, 2011, Monty Program Ab
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 110f140afe0..624fc87815c 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -6162,15 +6162,22 @@ find_field_in_view(THD *thd, TABLE_LIST *table_list,
       */
       if (*ref && !(*ref)->is_autogenerated_name)
       {
-        if (register_tree_change &&
-            thd->stmt_arena->is_stmt_prepare_or_first_stmt_execute())
-          arena= thd->activate_stmt_arena_if_needed(&backup);
-        item->set_name((*ref)->name, (*ref)->name_length,
-                       system_charset_info);
-        item->real_item()->set_name((*ref)->name, (*ref)->name_length,
-                       system_charset_info);
-        if (arena)
-          thd->restore_active_arena(arena, &backup);
+        if (register_tree_change)
+	{
+          item->set_name_for_rollback(thd, (*ref)->name, 
+                                      (*ref)->name_length,
+                                      system_charset_info);
+          item->real_item()->set_name_for_rollback(thd, (*ref)->name,
+                                                   (*ref)->name_length,
+                                                   system_charset_info);
+        }
+        else
+	{
+          item->set_name((*ref)->name, (*ref)->name_length,
+                         system_charset_info);
+          item->real_item()->set_name((*ref)->name, (*ref)->name_length,
+                                      system_charset_info);
+        }
       }
       if (register_tree_change)
         thd->change_item_tree(ref, item);
@@ -7452,6 +7459,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 6cc05ff1f62..e3a04c36071 100644
--- a/sql/sql_list.h
+++ b/sql/sql_list.h
@@ -260,6 +260,7 @@ public:
       last= &first;
     return tmp->info;
   }
+
   /*
     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/sql_select.cc b/sql/sql_select.cc
index d905516b075..338b0475626 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3131,6 +3131,12 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
     table->pos_in_table_list= tables;
     error= tables->fetch_number_of_rows();
 
+#ifdef WITH_PARTITION_STORAGE_ENGINE
+    const bool no_partitions_used= table->no_partitions_used;
+#else
+    const bool no_partitions_used= FALSE;
+#endif
+
     DBUG_EXECUTE_IF("bug11747970_raise_error",
                     {
                       if (!error)
@@ -3162,13 +3168,10 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
     if (*s->on_expr_ref)
     {
       /* s is the only inner table of an outer join */
-#ifdef WITH_PARTITION_STORAGE_ENGINE
-      if (!table->is_filled_at_execution() &&
-           (!table->file->stats.records || table->no_partitions_used) && !embedding)
-#else
       if (!table->is_filled_at_execution() &&
-          !table->file->stats.records && !embedding)
-#endif
+          ((!table->file->stats.records &&
+            (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT)) ||
+           no_partitions_used) && !embedding)
       {						// Empty table
         s->dependent= 0;                        // Ignore LEFT JOIN depend.
         no_rows_const_tables |= table->map;
@@ -3208,16 +3211,12 @@ make_join_statistics(JOIN *join, List<TABLE_LIST> &tables_list,
       if (inside_an_outer_join)
         continue;
     }
-#ifdef WITH_PARTITION_STORAGE_ENGINE
-    const bool no_partitions_used= table->no_partitions_used;
-#else
-    const bool no_partitions_used= FALSE;
-#endif
-    if (!table->is_filled_at_execution() && 
-        (table->s->system || table->file->stats.records <= 1 ||
+    if (!table->is_filled_at_execution() &&
+        (table->s->system ||
+         (table->file->stats.records <= 1 &&
+          (table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT)) ||
          no_partitions_used) &&
 	!s->dependent &&
-	(table->file->ha_table_flags() & HA_STATS_RECORDS_IS_EXACT) &&
         !table->fulltext_searched && !join->no_const_tables)
     {
       set_position(join,const_count++,s,(KEYUSE*) 0);
@@ -5766,6 +5765,114 @@ best_access_path(JOIN      *join,
 }
 
 
+/*
+  Find JOIN_TAB's embedding (i.e, parent) subquery.
+  - For merged semi-joins, tables inside the semi-join nest have their
+    semi-join nest as parent.  We intentionally ignore results of table 
+    pullout action here.
+  - For non-merged semi-joins (JTBM tabs), the embedding subquery is the 
+    JTBM join tab itself.
+*/
+
+static TABLE_LIST* get_emb_subq(JOIN_TAB *tab)
+{
+  TABLE_LIST *tlist= tab->table->pos_in_table_list;
+  if (tlist->jtbm_subselect)
+    return tlist;
+  TABLE_LIST *embedding= tlist->embedding;
+  if (!embedding || !embedding->sj_subq_pred)
+    return NULL;
+  return embedding;
+}
+
+
+/*
+  Choose initial table order that "helps" semi-join optimizations.
+
+  The idea is that we should start with the order that is the same as the one
+  we would have had if we had semijoin=off:
+  - Top-level tables go first
+  - subquery tables are grouped together by the subquery they are in,
+  - subquery tables are attached where the subquery predicate would have been
+    attached if we had semi-join off.
+  
+  This function relies on join_tab_cmp()/join_tab_cmp_straight() to produce
+  certain pre-liminary ordering, see compare_embedding_subqueries() for its
+  description.
+*/
+
+static void choose_initial_table_order(JOIN *join)
+{
+  TABLE_LIST *emb_subq;
+  JOIN_TAB **tab= join->best_ref + join->const_tables;
+  JOIN_TAB **tabs_end= tab + join->table_count - join->const_tables;
+  /* Find where the top-level JOIN_TABs end and subquery JOIN_TABs start */
+  for (; tab != tabs_end; tab++)
+  {
+    if ((emb_subq= get_emb_subq(*tab)))
+      break;
+  }
+  uint n_subquery_tabs= tabs_end - tab;
+
+  if (!n_subquery_tabs)
+    return;
+
+  /* Copy the subquery JOIN_TABs to a separate array */
+  JOIN_TAB *subquery_tabs[MAX_TABLES];
+  memcpy(subquery_tabs, tab, sizeof(JOIN_TAB*) * n_subquery_tabs);
+  
+  JOIN_TAB **last_top_level_tab= tab;
+  JOIN_TAB **subq_tab= subquery_tabs;
+  JOIN_TAB **subq_tabs_end= subquery_tabs + n_subquery_tabs;
+  TABLE_LIST *cur_subq_nest= NULL;
+  for (; subq_tab < subq_tabs_end; subq_tab++)
+  {
+    if (get_emb_subq(*subq_tab)!= cur_subq_nest)
+    {
+      /*
+        Reached the part of subquery_tabs that covers tables in some subquery.
+      */
+      cur_subq_nest= get_emb_subq(*subq_tab);
+
+      /* Determine how many tables the subquery has */
+      JOIN_TAB **last_tab_for_subq;
+      for (last_tab_for_subq= subq_tab;
+           last_tab_for_subq < subq_tabs_end && 
+           get_emb_subq(*last_tab_for_subq) == cur_subq_nest;
+           last_tab_for_subq++) {}
+      uint n_subquery_tables= last_tab_for_subq - subq_tab;
+
+      /* 
+        Walk the original array and find where this subquery would have been
+        attached to
+      */
+      table_map need_tables= cur_subq_nest->original_subq_pred_used_tables;
+      need_tables &= ~(join->const_table_map | PSEUDO_TABLE_BITS);
+      for (JOIN_TAB **top_level_tab= join->best_ref + join->const_tables;
+           top_level_tab < last_top_level_tab;
+           //top_level_tab < join->best_ref + join->table_count;
+           top_level_tab++)
+      {
+        need_tables &= ~(*top_level_tab)->table->map;
+        /* Check if this is the place where subquery should be attached */
+        if (!need_tables)
+        {
+          /* Move away the top-level tables that are after top_level_tab */
+          uint top_tail_len= last_top_level_tab - top_level_tab - 1;
+          memmove(top_level_tab + 1 + n_subquery_tables, top_level_tab + 1,
+                  sizeof(JOIN_TAB*)*top_tail_len);
+          last_top_level_tab += n_subquery_tables;
+          memcpy(top_level_tab + 1, subq_tab, sizeof(JOIN_TAB*)*n_subquery_tables);
+          break;
+        }
+      }
+      DBUG_ASSERT(!need_tables);
+      subq_tab += n_subquery_tables - 1;
+    }
+  }
+}
+
+
 /**
   Selects and invokes a search strategy for an optimal query plan.
 
@@ -5821,9 +5928,21 @@ choose_plan(JOIN *join, table_map join_tables)
     */
     jtab_sort_func= straight_join ? join_tab_cmp_straight : join_tab_cmp;
   }
+
+  /*
+    psergey-todo: if we're not optimizing an SJM nest, 
+     - sort that outer tables are first, and each sjm nest follows
+     - then, put each [sjm_table1, ... sjm_tableN] sub-array right where 
+       WHERE clause pushdown would have put it.
+  */
   my_qsort2(join->best_ref + join->const_tables,
             join->table_count - join->const_tables, sizeof(JOIN_TAB*),
             jtab_sort_func, (void*)join->emb_sjm_nest);
+
+  if (!join->emb_sjm_nest)
+  {
+    choose_initial_table_order(join);
+  }
   join->cur_sj_inner_tables= 0;
 
   if (straight_join)
@@ -5863,6 +5982,64 @@ choose_plan(JOIN *join, table_map join_tables)
 }
 
 
+/*
+  Compare two join tabs based on the subqueries they are from.
+   - top-level join tabs go first
+   - then subqueries are ordered by their select_id (we're using this 
+     criteria because we need a cross-platform, deterministic ordering)
+
+  @return 
+     0   -  equal
+     -1  -  jt1 < jt2
+     1   -  jt1 > jt2
+*/
+
+static int compare_embedding_subqueries(JOIN_TAB *jt1, JOIN_TAB *jt2)
+{
+  /* Determine if the first table is originally from a subquery */
+  TABLE_LIST *tbl1= jt1->table->pos_in_table_list;
+  uint tbl1_select_no;
+  if (tbl1->jtbm_subselect)
+  {
+    tbl1_select_no= 
+      tbl1->jtbm_subselect->unit->first_select()->select_number;
+  }
+  else if (tbl1->embedding && tbl1->embedding->sj_subq_pred)
+  {
+    tbl1_select_no= 
+      tbl1->embedding->sj_subq_pred->unit->first_select()->select_number;
+  }
+  else
+    tbl1_select_no= 1; /* Top-level */
+
+  /* Same for the second table */
+  TABLE_LIST *tbl2= jt2->table->pos_in_table_list;
+  uint tbl2_select_no;
+  if (tbl2->jtbm_subselect)
+  {
+    tbl2_select_no= 
+      tbl2->jtbm_subselect->unit->first_select()->select_number;
+  }
+  else if (tbl2->embedding && tbl2->embedding->sj_subq_pred)
+  {
+    tbl2_select_no= 
+      tbl2->embedding->sj_subq_pred->unit->first_select()->select_number;
+  }
+  else
+    tbl2_select_no= 1; /* Top-level */
+
+  /* 
+    Put top-level tables in front. Tables from within subqueries must follow,
+    grouped by their owner subquery. We don't care about the order that
+    subquery groups are in, because choose_initial_table_order() will re-order
+    the groups.
+  */
+  if (tbl1_select_no != tbl2_select_no)
+    return tbl1_select_no > tbl2_select_no ? 1 : -1;
+  return 0;
+}
+
+
 /**
   Compare two JOIN_TAB objects based on the number of accessed records.
 
@@ -5879,6 +6056,9 @@ choose_plan(JOIN *join, table_map join_tables)
       a: dependent = 0x0 table->map = 0x1 found_records = 3 ptr = 0x907e6b0
       b: dependent = 0x0 table->map = 0x2 found_records = 3 ptr = 0x907e838
       c: dependent = 0x6 table->map = 0x10 found_records = 2 ptr = 0x907ecd0
+
+   As for subuqueries, this function must produce order that can be fed to 
+   choose_initial_table_order().
      
   @retval
     1  if first is bigger
@@ -5893,7 +6073,15 @@ join_tab_cmp(const void *dummy, const void* ptr1, const void* ptr2)
 {
   JOIN_TAB *jt1= *(JOIN_TAB**) ptr1;
   JOIN_TAB *jt2= *(JOIN_TAB**) ptr2;
+  int cmp;
 
+  if ((cmp= compare_embedding_subqueries(jt1, jt2)) != 0)
+    return cmp;
+  /*
+    After that,
+    take care about ordering imposed by LEFT JOIN constraints,
+    possible [eq]ref accesses, and numbers of matching records in the table.
+  */
   if (jt1->dependent & jt2->table->map)
     return 1;
   if (jt2->dependent & jt1->table->map)
@@ -5924,6 +6112,10 @@ join_tab_cmp_straight(const void *dummy, const void* ptr1, const void* ptr2)
   DBUG_ASSERT(!jt1->emb_sj_nest);
   DBUG_ASSERT(!jt2->emb_sj_nest);
 
+  int cmp;
+  if ((cmp= compare_embedding_subqueries(jt1, jt2)) != 0)
+    return cmp;
+
   if (jt1->dependent & jt2->table->map)
     return 1;
   if (jt2->dependent & jt1->table->map)
@@ -15230,6 +15422,7 @@ free_tmp_table(THD *thd, TABLE *entry)
     else
       entry->file->ha_delete_table(entry->s->table_name.str);
     delete entry->file;
+    entry->file= 0;
   }
 
   /* free blobs */
diff --git a/sql/table.cc b/sql/table.cc
index fa5fff365d7..dc8ab3c6b42 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -4043,7 +4043,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)
   {
@@ -5107,7 +5121,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);
 }
 
@@ -6678,7 +6692,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 13b0274b3aa..7b50caae536 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1574,6 +1574,8 @@ struct TABLE_LIST
   /* If this is a non-jtbm semi-join nest: corresponding subselect predicate */
   Item_in_subselect  *sj_subq_pred;
 
+  table_map     original_subq_pred_used_tables;
+
   /* If this is a jtbm semi-join object: corresponding subselect predicate */
   Item_in_subselect  *jtbm_subselect;
   /* TODO: check if this can be joined with tablenr_exec */
@@ -1853,7 +1855,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. */
diff --git a/sql/unireg.h b/sql/unireg.h
index 50aaa103b34..f8317a89c8c 100644
--- a/sql/unireg.h
+++ b/sql/unireg.h
@@ -2,7 +2,7 @@
 #define UNIREG_INCLUDED
 
 /*
-   Copyright (c) 2000, 2010, Oracle and/or its affiliates.
+   Copyright (c) 2000, 2011, Oracle and/or its affiliates.
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by