Merge remote-tracking branch 'origin/10.0' into 10.1

18 files changed, 190 insertions, 52 deletions

diff --git a/mysql-test/r/func_misc.result b/mysql-test/r/func_misc.result
index b184a9b633c..545d515176d 100644
--- a/mysql-test/r/func_misc.result
+++ b/mysql-test/r/func_misc.result
@@ -1446,3 +1446,17 @@ SELECT CONCAT(NAME_CONST('name',15),'오');
 CONCAT(NAME_CONST('name',15),'오')
 15오
 SET NAMES latin1;
+#
+# MDEV-14116 INET6_NTOA output is set as null to varchar(39) variable
+#
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE ip_full_addr varchar(39) DEFAULT "";
+SELECT INET6_NTOA(UNHEX('20000000000000000000000000000000')) into ip_full_addr;
+SELECT ip_full_addr;
+END;
+$$
+CALL p1();
+ip_full_addr
+2000::
+DROP PROCEDURE p1;
diff --git a/mysql-test/r/subselect_exists2in.result b/mysql-test/r/subselect_exists2in.result
index 5deb2dfa9c5..d47e446fe8f 100644
--- a/mysql-test/r/subselect_exists2in.result
+++ b/mysql-test/r/subselect_exists2in.result
@@ -934,5 +934,42 @@ f2
 foo
 set optimizer_switch= @optimizer_switch_save;
 DROP TABLE t1;
+#
+# MDEV-14164: Unknown column error when adding aggregate to function
+# in oracle style procedure FOR loop
+#
+CREATE TABLE t1(id INT, val INT);
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE cur1 CURSOR FOR SELECT * FROM (
+SELECT DISTINCT id FROM t1) a
+WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b
+WHERE a.id=b.id);
+OPEN cur1;
+CLOSE cur1;
+OPEN cur1;
+CLOSE cur1;
+END;
+//
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+CREATE TABLE t1(id INT, val INT);
+CREATE PROCEDURE p1()
+BEGIN
+SELECT * FROM (SELECT DISTINCT id FROM t1) a
+WHERE NOT a.id IN (SELECT b.id FROM t1 b);
+SELECT * FROM (SELECT DISTINCT id FROM t1) a
+WHERE NOT EXISTS (SELECT * FROM t1 b WHERE a.id=b.id);
+END;
+//
+CALL p1();
+id
+id
+CALL p1();
+id
+id
+DROP PROCEDURE p1;
+DROP TABLE t1;
 # End of 10.0 tests
 set optimizer_switch=default;
diff --git a/mysql-test/t/func_misc.test b/mysql-test/t/func_misc.test
index 497ebf44e69..ffb7838ee78 100644
--- a/mysql-test/t/func_misc.test
+++ b/mysql-test/t/func_misc.test
@@ -1118,3 +1118,19 @@ SELECT COERCIBILITY(NAME_CONST('name',TIME'00:00:00'));
 SELECT COERCIBILITY(NAME_CONST('name',15));
 SELECT CONCAT(NAME_CONST('name',15),'오');
 SET NAMES latin1;
+
+--echo #
+--echo # MDEV-14116 INET6_NTOA output is set as null to varchar(39) variable
+--echo #
+
+DELIMITER $$;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE ip_full_addr varchar(39) DEFAULT "";
+  SELECT INET6_NTOA(UNHEX('20000000000000000000000000000000')) into ip_full_addr;
+  SELECT ip_full_addr;
+END;
+$$
+DELIMITER ;$$
+CALL p1();
+DROP PROCEDURE p1;
diff --git a/mysql-test/t/subselect_exists2in.test b/mysql-test/t/subselect_exists2in.test
index a4fdbe5c50b..5a8ddb3612f 100644
--- a/mysql-test/t/subselect_exists2in.test
+++ b/mysql-test/t/subselect_exists2in.test
@@ -786,6 +786,46 @@ set optimizer_switch= @optimizer_switch_save;
  
 DROP TABLE t1;
 
+--echo #
+--echo # MDEV-14164: Unknown column error when adding aggregate to function
+--echo # in oracle style procedure FOR loop
+--echo #
+
+CREATE TABLE t1(id INT, val INT);
+DELIMITER //;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE cur1 CURSOR FOR SELECT * FROM (
+  SELECT DISTINCT id FROM t1) a
+  WHERE NOT EXISTS (SELECT * FROM ( SELECT id FROM t1) b
+                    WHERE a.id=b.id);
+  OPEN cur1;
+  CLOSE cur1;
+  OPEN cur1;
+  CLOSE cur1;
+END;
+//
+DELIMITER ;//
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
+CREATE TABLE t1(id INT, val INT);
+DELIMITER //;
+CREATE PROCEDURE p1()
+BEGIN
+  SELECT * FROM (SELECT DISTINCT id FROM t1) a
+  WHERE NOT a.id IN (SELECT b.id FROM t1 b);
+  SELECT * FROM (SELECT DISTINCT id FROM t1) a
+  WHERE NOT EXISTS (SELECT * FROM t1 b WHERE a.id=b.id);
+END;
+//
+DELIMITER ;//
+CALL p1();
+CALL p1();
+DROP PROCEDURE p1;
+DROP TABLE t1;
+
 --echo # End of 10.0 tests
 
 #restore defaults
diff --git a/sql/item.cc b/sql/item.cc
index ff172e69a04..8c64a10c496 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -2681,7 +2681,8 @@ table_map Item_field::all_used_tables() const
   return (get_depended_from() ? OUTER_REF_TABLE_BIT : field->table->map);
 }
 
-void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref,
+                                   bool merge)
 {
   if (new_parent == get_depended_from())
     depended_from= NULL;
@@ -2725,6 +2726,19 @@ void Item_field::fix_after_pullout(st_select_lex *new_parent, Item **ref)
     if (!need_change)
       return;
 
+    if (!merge)
+    {
+      /*
+        It is transformation without merge.
+        This field was "outer" for the inner SELECT where it was taken and
+        moved up.
+        "Outer" fields uses normal SELECT_LEX context of upper SELECTs for
+        name resolution, so we can switch everything to it safely.
+      */
+      this->context= &new_parent->context;
+      return;
+    }
+
     Name_resolution_context *ctx= new Name_resolution_context();
     if (context->select_lex == new_parent)
     {
@@ -8061,18 +8075,19 @@ bool Item_outer_ref::fix_fields(THD *thd, Item **reference)
 
 
 void Item_outer_ref::fix_after_pullout(st_select_lex *new_parent,
-                                       Item **ref_arg)
+                                       Item **ref_arg, bool merge)
 {
   if (get_depended_from() == new_parent)
   {
     *ref_arg= outer_ref;
-    (*ref_arg)->fix_after_pullout(new_parent, ref_arg);
+    (*ref_arg)->fix_after_pullout(new_parent, ref_arg, merge);
   }
 }
 
-void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr)
+void Item_ref::fix_after_pullout(st_select_lex *new_parent, Item **refptr,
+                                 bool merge)
 {
-  (*ref)->fix_after_pullout(new_parent, ref);
+  (*ref)->fix_after_pullout(new_parent, ref, merge);
   if (get_depended_from() == new_parent)
     depended_from= NULL;
 }
diff --git a/sql/item.h b/sql/item.h
index 17d97fe56e3..921179ce232 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -729,7 +729,9 @@ public:
     Fix after some tables has been pulled out. Basically re-calculate all
     attributes that are dependent on the tables.
   */
-  virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref) {};
+  virtual void fix_after_pullout(st_select_lex *new_parent, Item **ref,
+                                 bool merge)
+    {};
 
   /*
     This method should be used in case where we are sure that we do not need
@@ -2384,7 +2386,7 @@ public:
   bool send(Protocol *protocol, String *str_arg);
   void reset_field(Field *f);
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   void make_field(Send_field *tmp_field);
   int save_in_field(Field *field,bool no_conversions);
   void save_org_in_field(Field *field, fast_field_copier optimizer_data);
@@ -3905,7 +3907,7 @@ public:
   bool send(Protocol *prot, String *tmp);
   void make_field(Send_field *field);
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   int save_in_field(Field *field, bool no_conversions);
   void save_org_in_field(Field *field, fast_field_copier optimizer_data);
   fast_field_copier setup_fast_field_copier(Field *field)
@@ -4175,9 +4177,9 @@ public:
     Item *it= ((Item *) item)->real_item();
     return orig_item->eq(it, binary_cmp);
   }
-  void fix_after_pullout(st_select_lex *new_parent, Item **refptr)
+  void fix_after_pullout(st_select_lex *new_parent, Item **refptr, bool merge)
   {
-    orig_item->fix_after_pullout(new_parent, &orig_item);
+    orig_item->fix_after_pullout(new_parent, &orig_item, merge);
   }
   int save_in_field(Field *to, bool no_conversions);
   enum Item_result result_type () const { return orig_item->result_type(); }
@@ -4435,7 +4437,7 @@ public:
     outer_ref->save_org_in_field(result_field, NULL);
   }
   bool fix_fields(THD *, Item **);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   table_map used_tables() const
   {
     return (*ref)->const_item() ? 0 : OUTER_REF_TABLE_BIT;
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index e45e568e3da..c50b3e58a19 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1229,10 +1229,11 @@ bool Item_in_optimizer::is_top_level_item()
 }
 
 
-void Item_in_optimizer::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_in_optimizer::fix_after_pullout(st_select_lex *new_parent,
+                                          Item **ref, bool merge)
 {
   /* This will re-calculate attributes of our Item_in_subselect: */
-  Item_bool_func::fix_after_pullout(new_parent, ref);
+  Item_bool_func::fix_after_pullout(new_parent, ref, merge);
 
   /* Then, re-calculate not_null_tables_cache: */
   eval_not_null_tables(NULL);
@@ -2060,10 +2061,11 @@ bool Item_func_between::count_sargable_conds(uchar *arg)
 }
 
 
-void Item_func_between::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func_between::fix_after_pullout(st_select_lex *new_parent,
+                                          Item **ref, bool merge)
 {
   /* This will re-calculate attributes of the arguments */
-  Item_func_opt_neg::fix_after_pullout(new_parent, ref);
+  Item_func_opt_neg::fix_after_pullout(new_parent, ref, merge);
   /* Then, re-calculate not_null_tables_cache according to our special rules */
   eval_not_null_tables(NULL);
 }
@@ -2406,10 +2408,11 @@ Item_func_if::eval_not_null_tables(uchar *opt_arg)
 }
 
 
-void Item_func_if::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func_if::fix_after_pullout(st_select_lex *new_parent,
+                                     Item **ref, bool merge)
 {
   /* This will re-calculate attributes of the arguments */
-  Item_func::fix_after_pullout(new_parent, ref);
+  Item_func::fix_after_pullout(new_parent, ref, merge);
   /* Then, re-calculate not_null_tables_cache according to our special rules */
   eval_not_null_tables(NULL);
 }
@@ -4138,10 +4141,11 @@ Item_func_in::eval_not_null_tables(uchar *opt_arg)
 }
 
 
-void Item_func_in::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func_in::fix_after_pullout(st_select_lex *new_parent, Item **ref,
+                                     bool merge)
 {
   /* This will re-calculate attributes of the arguments */
-  Item_func_opt_neg::fix_after_pullout(new_parent, ref);
+  Item_func_opt_neg::fix_after_pullout(new_parent, ref, merge);
   /* Then, re-calculate not_null_tables_cache according to our special rules */
   eval_not_null_tables(NULL);
 }
@@ -4663,7 +4667,8 @@ Item_cond::eval_not_null_tables(uchar *opt_arg)
 }
 
 
-void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref,
+                                  bool merge)
 {
   List_iterator<Item> li(list);
   Item *item;
@@ -4676,7 +4681,7 @@ void Item_cond::fix_after_pullout(st_select_lex *new_parent, Item **ref)
   while ((item=li++))
   {
     table_map tmp_table_map;
-    item->fix_after_pullout(new_parent, li.ref());
+    item->fix_after_pullout(new_parent, li.ref(), merge);
     item= *li.ref();
     used_tables_and_const_cache_join(item);
 
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index a34e70f9918..cc336289355 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -342,7 +342,7 @@ public:
   virtual void get_cache_parameters(List<Item> &parameters);
   bool is_top_level_item();
   bool eval_not_null_tables(uchar *opt_arg);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   bool invisible_mode();
   void reset_cache() { cache= NULL; }
 };
@@ -824,7 +824,7 @@ public:
   void fix_length_and_dec();
   virtual void print(String *str, enum_query_type query_type);
   bool eval_not_null_tables(uchar *opt_arg);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   bool count_sargable_conds(uchar *arg);
   void add_key_fields(JOIN *join, KEY_FIELD **key_fields,
                       uint *and_level, table_map usable_tables,
@@ -980,7 +980,7 @@ public:
   }
   const char *func_name() const { return "if"; }
   bool eval_not_null_tables(uchar *opt_arg);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
 private:
   void cache_type_info(Item *source);
 };
@@ -1595,7 +1595,7 @@ public:
   enum Functype functype() const { return IN_FUNC; }
   const char *func_name() const { return " IN "; }
   bool eval_not_null_tables(uchar *opt_arg);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   bool count_sargable_conds(uchar *arg);
 };
 
@@ -2063,7 +2063,7 @@ public:
     list.append(nlist);
   }
   bool fix_fields(THD *, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
 
   enum Type type() const { return COND_ITEM; }
   List<Item>* argument_list() { return &list; }
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 5539ba7fc48..6b444a7e86d 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -270,7 +270,8 @@ Item_func::eval_not_null_tables(uchar *opt_arg)
 }
 
 
-void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref,
+                                  bool merge)
 {
   Item **arg,**arg_end;
 
@@ -281,7 +282,7 @@ void Item_func::fix_after_pullout(st_select_lex *new_parent, Item **ref)
   {
     for (arg=args, arg_end=args+arg_count; arg != arg_end ; arg++)
     {
-      (*arg)->fix_after_pullout(new_parent, arg);
+      (*arg)->fix_after_pullout(new_parent, arg, merge);
       Item *item= *arg;
 
       used_tables_and_const_cache_join(item);
diff --git a/sql/item_func.h b/sql/item_func.h
index ec8ec6fca74..1209fc5cdd8 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -126,7 +126,7 @@ public:
     Item_func_or_sum::cleanup();
     used_tables_and_const_cache_init();
   }
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   void quick_fix_field();
   table_map not_null_tables() const;
   void update_used_tables()
diff --git a/sql/item_inetfunc.cc b/sql/item_inetfunc.cc
index 6a09747fa1a..4c4dfa4497b 100644
--- a/sql/item_inetfunc.cc
+++ b/sql/item_inetfunc.cc
@@ -181,7 +181,8 @@ String *Item_func_inet_str_base::val_str_ascii(String *buffer)
     return NULL;
   }
 
-  String *arg_str= args[0]->val_str(buffer);
+  StringBuffer<STRING_BUFFER_USUAL_SIZE> tmp;
+  String *arg_str= args[0]->val_str(&tmp);
   if (!arg_str) // Out-of memory happened. The error has been reported.
   {             // Or: the underlying field is NULL
     null_value= true;
@@ -679,7 +680,7 @@ static void ipv6_to_str(const in6_addr *ipv6, char *str)
   @retval true  The string has been converted sucessfully.
 */
 
-bool Item_func_inet6_aton::calc_value(String *arg, String *buffer)
+bool Item_func_inet6_aton::calc_value(const String *arg, String *buffer)
 {
   // ipv4-string -> varbinary(4)
   // ipv6-string -> varbinary(16)
@@ -719,7 +720,7 @@ bool Item_func_inet6_aton::calc_value(String *arg, String *buffer)
   @retval true  The string has been converted sucessfully.
 */
 
-bool Item_func_inet6_ntoa::calc_value(String *arg, String *buffer)
+bool Item_func_inet6_ntoa::calc_value(const String *arg, String *buffer)
 {
   if (arg->charset() != &my_charset_bin)
     return false;
diff --git a/sql/item_inetfunc.h b/sql/item_inetfunc.h
index 82a4405df1e..eaafd005f91 100644
--- a/sql/item_inetfunc.h
+++ b/sql/item_inetfunc.h
@@ -98,7 +98,7 @@ public:
   virtual String *val_str_ascii(String *buffer);
 
 protected:
-  virtual bool calc_value(String *arg, String *buffer) = 0;
+  virtual bool calc_value(const String *arg, String *buffer) = 0;
 };
 
 
@@ -125,7 +125,7 @@ public:
   }
 
 protected:
-  virtual bool calc_value(String *arg, String *buffer);
+  virtual bool calc_value(const String *arg, String *buffer);
 };
 
 
@@ -157,7 +157,7 @@ public:
   }
 
 protected:
-  virtual bool calc_value(String *arg, String *buffer);
+  virtual bool calc_value(const String *arg, String *buffer);
 };
 
 
diff --git a/sql/item_row.cc b/sql/item_row.cc
index 9c53d65b317..97f75c4b4cf 100644
--- a/sql/item_row.cc
+++ b/sql/item_row.cc
@@ -109,13 +109,14 @@ void Item_row::split_sum_func(THD *thd, Item **ref_pointer_array,
 }
 
 
-void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_row::fix_after_pullout(st_select_lex *new_parent, Item **ref,
+                                 bool merge)
 {
   used_tables_and_const_cache_init();
   not_null_tables_cache= 0;
   for (uint i= 0; i < arg_count; i++)
   {
-    args[i]->fix_after_pullout(new_parent, &args[i]);
+    args[i]->fix_after_pullout(new_parent, &args[i], merge);
     used_tables_and_const_cache_join(args[i]);
     not_null_tables_cache|= args[i]->not_null_tables();
   }
diff --git a/sql/item_row.h b/sql/item_row.h
index 640f27bfa1f..5e8071ec495 100644
--- a/sql/item_row.h
+++ b/sql/item_row.h
@@ -80,7 +80,7 @@ public:
     return 0;
   };
   bool fix_fields(THD *thd, Item **ref);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   void cleanup();
   void split_sum_func(THD *thd, Item **ref_pointer_array, List<Item> &fields,
                       uint flags);
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index d923b28af5a..1c1f09c4567 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -447,7 +447,8 @@ bool Item_subselect::mark_as_dependent(THD *thd, st_select_lex *select,
       OUTER_REF_TABLE_BIT.
 */
 
-void Item_subselect::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_subselect::fix_after_pullout(st_select_lex *new_parent,
+                                       Item **ref, bool merge)
 {
   recalc_used_tables(new_parent, TRUE);
   parent_select= new_parent;
@@ -1153,7 +1154,8 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
     /*
       as far as we moved content to upper level we have to fix dependences & Co
     */
-    substitution->fix_after_pullout(select_lex->outer_select(), &substitution);
+    substitution->fix_after_pullout(select_lex->outer_select(),
+                                    &substitution, TRUE);
   }
   DBUG_RETURN(false);
 }
@@ -2934,7 +2936,7 @@ bool Item_exists_subselect::exists2in_processor(uchar *opt_arg)
           goto out;
         }
       }
-      outer_exp->fix_after_pullout(unit->outer_select(), &outer_exp);
+      outer_exp->fix_after_pullout(unit->outer_select(), &outer_exp, FALSE);
       outer_exp->update_used_tables();
       outer.push_back(outer_exp, thd->mem_root);
     }
@@ -3315,10 +3317,11 @@ err:
 }
 
 
-void Item_in_subselect::fix_after_pullout(st_select_lex *new_parent, Item **ref)
+void Item_in_subselect::fix_after_pullout(st_select_lex *new_parent,
+                                          Item **ref, bool merge)
 {
-  left_expr->fix_after_pullout(new_parent, &left_expr);
-  Item_subselect::fix_after_pullout(new_parent, ref);
+  left_expr->fix_after_pullout(new_parent, &left_expr, merge);
+  Item_subselect::fix_after_pullout(new_parent, ref, merge);
   used_tables_cache |= left_expr->used_tables();
 }
 
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 009aff5ed80..424ea6f0512 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -169,7 +169,7 @@ public:
   }
   bool fix_fields(THD *thd, Item **ref);
   bool mark_as_dependent(THD *thd, st_select_lex *select, Item *item);
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   void recalc_used_tables(st_select_lex *new_parent, bool after_pullout);
   virtual bool exec();
   /*
@@ -607,7 +607,7 @@ public:
   virtual void print(String *str, enum_query_type query_type);
   bool fix_fields(THD *thd, Item **ref);
   void fix_length_and_dec();
-  void fix_after_pullout(st_select_lex *new_parent, Item **ref);
+  void fix_after_pullout(st_select_lex *new_parent, Item **ref, bool merge);
   bool const_item() const
   {
     return Item_subselect::const_item() && left_expr->const_item();
diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc
index 26e854cfbfd..138af3f14b3 100644
--- a/sql/opt_subselect.cc
+++ b/sql/opt_subselect.cc
@@ -1665,7 +1665,7 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
     {
       tl->jtbm_table_no= table_no;
       Item *dummy= tl->jtbm_subselect;
-      tl->jtbm_subselect->fix_after_pullout(parent_lex, &dummy);
+      tl->jtbm_subselect->fix_after_pullout(parent_lex, &dummy, true);
       DBUG_ASSERT(dummy == tl->jtbm_subselect);
     }
     SELECT_LEX *old_sl= tl->select_lex;
@@ -1806,7 +1806,8 @@ static bool convert_subq_to_sj(JOIN *parent_join, Item_in_subselect *subq_pred)
     Walk through sj nest's WHERE and ON expressions and call
     item->fix_table_changes() for all items.
   */
-  sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr);
+  sj_nest->sj_on_expr->fix_after_pullout(parent_lex, &sj_nest->sj_on_expr,
+                                         TRUE);
   fix_list_after_tbl_changes(parent_lex, &sj_nest->nested_join->join_list);
 
 
@@ -1965,7 +1966,7 @@ static bool convert_subq_to_jtbm(JOIN *parent_join,
   DBUG_ASSERT(parent_join->table_count < MAX_TABLES);
 
   Item *conds= hash_sj_engine->semi_join_conds;
-  conds->fix_after_pullout(parent_lex, &conds);
+  conds->fix_after_pullout(parent_lex, &conds, TRUE);
 
   DBUG_EXECUTE("where", print_where(conds,"SJ-EXPR", QT_ORDINARY););
   
@@ -2017,7 +2018,7 @@ void fix_list_after_tbl_changes(SELECT_LEX *new_parent, List<TABLE_LIST> *tlist)
   while ((table= it++))
   {
     if (table->on_expr)
-      table->on_expr->fix_after_pullout(new_parent, &table->on_expr);
+      table->on_expr->fix_after_pullout(new_parent, &table->on_expr, TRUE);
     if (table->nested_join)
       fix_list_after_tbl_changes(new_parent, &table->nested_join->join_list);
   }
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 5d12091c4b4..5a0879c20c4 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -466,7 +466,8 @@ bool mysql_derived_merge(THD *thd, LEX *lex, TABLE_LIST *derived)
     // Update used tables cache according to new table map
     if (derived->on_expr)
     {
-      derived->on_expr->fix_after_pullout(parent_lex, &derived->on_expr);
+      derived->on_expr->fix_after_pullout(parent_lex, &derived->on_expr,
+                                          TRUE);
       fix_list_after_tbl_changes(parent_lex, &derived->nested_join->join_list);
     }
   }
@@ -636,7 +637,8 @@ bool mysql_derived_prepare(THD *thd, LEX *lex, TABLE_LIST *derived)
   SELECT_LEX_UNIT *unit= derived->get_unit();
   DBUG_ENTER("mysql_derived_prepare");
   bool res= FALSE;
-  DBUG_PRINT("enter", ("unit 0x%lx", (ulong) unit));
+  DBUG_PRINT("enter", ("unit: %p  table_list: %p  Alias '%s'",
+                       unit, derived, derived->alias));
 
   // Skip already prepared views/DT
   if (!unit || unit->prepared ||