Problem was that for cases like:

SELECT ... WHERE XX IN (SELECT YY)
this was transformed to something like:
SELECT ... WHERE IF_EXISTS(SELECT ... HAVING XX=YY)

The bug was that for normal execution XX was fixed in the original outer SELECT context while in PS it was fixed in the sub query context and this confused the optimizer.

Fixed by ensuring that XX is always fixed in the outer context.

3 files changed, 28 insertions, 8 deletions

diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 62df666b71f..8b0c6efc9ee 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -1999,12 +1999,31 @@ Item_in_subselect::create_single_in_to_exists_cond(JOIN * join,
     during JOIN::optimize: this->tmp_having= this->having; this->having= 0;
   */
   Item* join_having= join->having ? join->having : join->tmp_having;
-
   DBUG_ENTER("Item_in_subselect::create_single_in_to_exists_cond");
 
   *where_item= NULL;
   *having_item= NULL;
 
+  /*
+    For PS we have to do fix_fields(expr) here to ensure that it's
+    evaluated in the outer context.  If not, then fix_having() will do
+    a fix_fields(expr) in the inner context and mark expr as
+    'depended', which will cause update_ref_and_keys() to find wrong
+    keys.
+    When not running PS, fix_fields(expr) as already been done earlier and
+    the following test does nothing.
+  */
+  if (expr && !expr->fixed)
+  {
+    SELECT_LEX *save_current_select= thd->lex->current_select;
+    thd->lex->current_select= thd->lex->current_select->outer_select();
+    bool tmp;
+    tmp= expr->fix_fields(thd, 0);
+    thd->lex->current_select= save_current_select;
+    if (tmp)
+      DBUG_RETURN(true);
+  }
+
   if (join_having || select_lex->with_sum_func ||
       select_lex->group_list.elements)
   {
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index fcd17b25b2d..aefa82405ff 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -6859,6 +6859,7 @@ find_field_in_tables(THD *thd, Item_ident *item,
 
   if (item->cached_table)
   {
+    DBUG_PRINT("info", ("using cached table"));
     /*
       This shortcut is used by prepared statements. We assume that
       TABLE_LIST *first_table is not changed during query execution (which
@@ -6935,8 +6936,6 @@ find_field_in_tables(THD *thd, Item_ident *item,
       return found;
     }
   }
-  else
-    item->can_be_depended= TRUE;
 
   if (db && lower_case_table_names)
   {
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 5ed7a67da61..e542b08f911 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -4904,6 +4904,8 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
   KEY_FIELD *key_fields, *end, *field;
   uint sz;
   uint m= max(select_lex->max_equal_elems,1);
+  DBUG_ENTER("update_ref_and_keys");
+  DBUG_PRINT("enter", ("normal_tables: %llx", normal_tables));
 
   SELECT_LEX *sel=thd->lex->current_select; 
   sel->cond_count= 0;
@@ -4950,7 +4952,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
   sz= max(sizeof(KEY_FIELD),sizeof(SARGABLE_PARAM))*
     ((sel->cond_count*2 + sel->between_count)*m+1);
   if (!(key_fields=(KEY_FIELD*)	thd->alloc(sz)))
-    return TRUE; /* purecov: inspected */
+    DBUG_RETURN(TRUE); /* purecov: inspected */
   and_level= 0;
   field= end= key_fields;
   *sargables= (SARGABLE_PARAM *) key_fields + 
@@ -4959,7 +4961,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
   (*sargables)[0].field= 0; 
 
   if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64))
-    return TRUE;
+    DBUG_RETURN(TRUE);
 
   if (cond)
   {
@@ -5009,16 +5011,16 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
   for ( ; field != end ; field++)
   {
     if (add_key_part(keyuse,field))
-      return TRUE;
+      DBUG_RETURN(TRUE);
   }
 
   if (select_lex->ftfunc_list->elements)
   {
     if (add_ft_keys(keyuse,join_tab,cond,normal_tables))
-      return TRUE;
+      DBUG_RETURN(TRUE);
   }
 
-  return FALSE;
+  DBUG_RETURN(FALSE);
 }