merge

mysql-test/r/derived.result:
  Auto merged
mysql-test/t/derived.test:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/item_func.h:
  Auto merged
sql/item_sum.h:
  Auto merged
sql/sql_acl.cc:
  Auto merged
sql/sql_class.cc:
  Auto merged
sql/sql_class.h:
  Auto merged
sql/sql_prepare.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_update.cc:
  Auto merged

32 files changed, 784 insertions, 388 deletions

diff --git a/mysql-test/r/derived.result b/mysql-test/r/derived.result
index 44d35148f87..049d88c5154 100644
--- a/mysql-test/r/derived.result
+++ b/mysql-test/r/derived.result
@@ -59,7 +59,7 @@ explain select * from t1 as x1, (select * from t1) as x2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	x1	ALL	NULL	NULL	NULL	NULL	4	
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	4	
-2	DERIVED	x1	ALL	NULL	NULL	NULL	NULL	4	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	4	
 drop table if exists  t2,t3;
 select * from (select 1) as a;
 1
@@ -141,7 +141,7 @@ a	t
 explain select count(*) from t1 as tt1, (select * from t1) as tt2;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	Select tables optimized away
-2	DERIVED	tt1	ALL	NULL	NULL	NULL	NULL	10000	
+2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	10000	
 drop table t1;
 SELECT * FROM (SELECT (SELECT * FROM (SELECT 1 as a) as a )) as b;
 (SELECT * FROM (SELECT 1 as a) as a )
@@ -189,13 +189,13 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	Using where
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
-2	DERIVED	m2	index	NULL	PRIMARY	3	NULL	9	Using index
+2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 explain SELECT STRAIGHT_JOIN d.pla_id, m2.test FROM t1 m2  INNER JOIN (SELECT mp.pla_id, MIN(m1.matintnum) AS matintnum FROM t2 mp INNER JOIN t1 m1 ON mp.mat_id=m1.mat_id GROUP BY mp.pla_id) d ON d.matintnum=m2.matintnum;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	m2	ALL	NULL	NULL	NULL	NULL	9	
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	6	Using where
 2	DERIVED	mp	ALL	NULL	NULL	NULL	NULL	9	Using temporary; Using filesort
-2	DERIVED	m2	index	NULL	PRIMARY	3	NULL	9	Using index
+2	DERIVED	m1	eq_ref	PRIMARY	PRIMARY	3	test.mp.mat_id	1	
 drop table t1,t2;
 SELECT a.x FROM (SELECT 1 AS x) AS a HAVING a.x = 1;
 x
@@ -229,7 +229,7 @@ explain select count(*) from t1 INNER JOIN (SELECT A.E1, A.E2, A.E3 FROM t1 AS A
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	2	
 1	PRIMARY	t1	eq_ref	PRIMARY	PRIMARY	4	THEMAX.E2	1	Using where
-2	DERIVED	t1	ALL	NULL	NULL	NULL	NULL	2	Using where
+2	DERIVED	A	ALL	NULL	NULL	NULL	NULL	2	Using where
 3	DEPENDENT SUBQUERY	B	ALL	NULL	NULL	NULL	NULL	2	Using where
 drop table t1;
 create table t1 (a int);
@@ -265,12 +265,16 @@ N	M
 3	0
 UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2;
 ERROR HY000: The target table P2 of the UPDATE is not updatable.
+UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
+ERROR 42S22: Unknown column 'aaaa' in 'field list'
 delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
 select * from t1;
 N	M
 3	0
 delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
 ERROR HY000: The target table P2 of the DELETE is not updatable.
+delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
+ERROR 42S22: Unknown column 'aaa' in 'field list'
 drop table t1;
 CREATE TABLE t1 (
 OBJECTID int(11) NOT NULL default '0',
@@ -294,3 +298,25 @@ INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00
 select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA      from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP;
 497	ID	NULL
 drop table t1, t2, t3;
+CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL);
+INSERT INTO t1 VALUES ('a',1),  ('a',2),  ('a',2),  ('a',2),  ('a',3),  ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5);
+SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
+name	median
+a	7.0000
+b	3.5000
+explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	ALL	NULL	NULL	NULL	NULL	3	Using temporary; Using filesort
+2	DERIVED	x	ALL	NULL	NULL	NULL	NULL	17	Using temporary; Using filesort
+2	DERIVED	y	ALL	NULL	NULL	NULL	NULL	17	Using where
+drop table t1;
+create table t2 (a int, b int, primary key (a));
+insert into t2 values (1,7),(2,7);
+explain select a from t2 where a>1;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	SIMPLE	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+explain select a from (select a from t2 where a>1) tt;
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
+2	DERIVED	t2	range	PRIMARY	PRIMARY	4	NULL	2	Using where; Using index
+drop table t2;
diff --git a/mysql-test/t/derived.test b/mysql-test/t/derived.test
index 8646a98551f..a9341ada416 100644
--- a/mysql-test/t/derived.test
+++ b/mysql-test/t/derived.test
@@ -153,10 +153,14 @@ UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1)
 select * from t1;
 -- error 1287
 UPDATE `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2, P2.N = 2;
+-- error 1054
+UPDATE `t1` AS P1 INNER JOIN (SELECT aaaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N SET P1.M = 2;
 delete P1.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
 select * from t1;
 -- error 1287
 delete P1.*,P2.* from `t1` AS P1 INNER JOIN (SELECT N FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
+-- error 1054
+delete P1.* from `t1` AS P1 INNER JOIN (SELECT aaa FROM `t1` GROUP BY N HAVING Count(M) > 1) AS P2 ON P1.N = P2.N;
 drop table t1;
 
 #
@@ -183,3 +187,22 @@ CREATE TABLE t3 (
 INSERT INTO t3 VALUES (1000,0.00),(1001,0.25),(1002,0.50),(1003,0.75),(1008,1.00),(1009,1.25),(1010,1.50),(1011,1.75);
 select 497, TMP.ID, NULL from (select 497 as ID, MAX(t3.DATA) as DATA      from t1 join t2 on (t1.ObjectID = t2.ID) join t3 on (t1.ObjectID = t3.ID) group by t2.ParID order by DATA DESC) as TMP;
 drop table t1, t2, t3;
+
+
+#
+# explain derived
+#
+CREATE TABLE t1 (name char(1) default NULL, val int(5) default NULL);
+INSERT INTO t1 VALUES ('a',1),  ('a',2),  ('a',2),  ('a',2),  ('a',3),  ('a',6), ('a',7), ('a',11), ('a',11), ('a',12), ('a',13), ('a',13), ('a',20), ('b',2), ('b',3), ('b',4), ('b',5);
+SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
+explain SELECT s.name, AVG(s.val) AS median FROM (SELECT x.name, x.val FROM t1 x, t1 y WHERE x.name=y.name GROUP BY x.name, x.val HAVING SUM(y.val <= x.val) >= COUNT(*)/2 AND SUM(y.val >= x.val) >= COUNT(*)/2) AS s GROUP BY s.name;
+drop table t1;
+
+#
+# "Using index" in explain
+#
+create table t2 (a int, b int, primary key (a));
+insert into t2 values (1,7),(2,7);
+explain select a from t2 where a>1;
+explain select a from (select a from t2 where a>1) tt;
+drop table t2;
diff --git a/sql/item.cc b/sql/item.cc
index 4d06d0d7765..3b9f49b8c32 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -966,8 +966,10 @@ bool Item_field::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
 
 void Item_field::cleanup()
 {
+  DBUG_ENTER("Item_field::cleanup");
   Item_ident::cleanup();
   field= result_field= 0;
+  DBUG_VOID_RETURN;
 }
 
 void Item::init_make_field(Send_field *tmp_field,
@@ -1610,9 +1612,11 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
 
 void Item_ref::cleanup()
 {
+  DBUG_ENTER("Item_ref::cleanup");
   Item_ident::cleanup();
   if (hook_ptr)
     *hook_ptr= orig_item;
+  DBUG_VOID_RETURN;
 }
 
 
diff --git a/sql/item.h b/sql/item.h
index a5648c5889b..a1c7f2e16f1 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -128,7 +128,13 @@ public:
   virtual ~Item() { name=0; }		/*lint -e1509 */
   void set_name(const char *str,uint length, CHARSET_INFO *cs);
   void init_make_field(Send_field *tmp_field,enum enum_field_types type);
-  virtual void cleanup() { fixed=0; }
+  virtual void cleanup()
+  {
+    DBUG_ENTER("Item::cleanup");
+    DBUG_PRINT("info", ("Type: %d", (int)type()));
+    fixed=0;
+    DBUG_VOID_RETURN;
+  }
   virtual void make_field(Send_field *field);
   virtual bool fix_fields(THD *, struct st_table_list *, Item **);
   virtual int save_in_field(Field *field, bool no_conversions);
@@ -996,8 +1002,10 @@ public:
   void bring_value();
   void cleanup()
   {
+    DBUG_ENTER("Item_cache_row::cleanup");
     Item_cache::cleanup();
     values= 0;
+    DBUG_VOID_RETURN;
   }
 };
 
@@ -1023,8 +1031,10 @@ public:
   Field *example() { return field_example; }
   void cleanup()
   {
+    DBUG_ENTER("Item_type_holder::cleanup");
     Item::cleanup();
     item_type= orig_type;
+    DBUG_VOID_RETURN;
   }
 };
 
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 4046a4d6414..276c374c353 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -484,6 +484,14 @@ longlong Item_in_optimizer::val_int()
   return tmp;
 }
 
+void Item_in_optimizer::cleanup()
+{
+  DBUG_ENTER("Item_in_optimizer::cleanup");
+  Item_bool_func::cleanup();
+  cache= 0;
+  DBUG_VOID_RETURN;
+}
+
 bool Item_in_optimizer::is_null()
 {
   cache->store(args[0]);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 7e1749ef7a0..b6f78827ca5 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -105,6 +105,7 @@ public:
     Item_in_optimizer return NULL, else it evaluate Item_in_subselect.
   */
   longlong val_int();
+  void cleanup();
   const char *func_name() const { return "<in_optimizer>"; }
   Item_cache **get_cache() { return &cache; }
 };
@@ -207,9 +208,11 @@ public:
   }
   void cleanup()
   {
+    DBUG_ENTER("Item_bool_rowready_func2::cleanup");
     Item_bool_func2::cleanup();
     tmp_arg[0]= orig_a;
     tmp_arg[1]= orig_b;
+    DBUG_VOID_RETURN;
   }
 };
 
@@ -718,10 +721,12 @@ class Item_func_in :public Item_int_func
   void fix_length_and_dec();
   void cleanup()
   {
+    DBUG_ENTER("Item_func_in::cleanup");
     delete array;
     delete in_item;
     array= 0;
     in_item= 0;
+    DBUG_VOID_RETURN;
   }
   optimize_type select_optimize() const
     { return array ? OPTIMIZE_KEY : OPTIMIZE_NONE; }
diff --git a/sql/item_func.h b/sql/item_func.h
index 30f817d133b..3d79a0eb9f3 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -999,6 +999,7 @@ public:
        join_key(0), ft_handler(0), table(0), master(0), concat(0) { }
   void cleanup()
   {
+    DBUG_ENTER("Item_func_match");
     if (!master && ft_handler)
     {
       ft_handler->please->close_search(ft_handler);
@@ -1009,6 +1010,7 @@ public:
     }
     if (concat)
       delete concat;
+    DBUG_VOID_RETURN;
   }
   enum Functype functype() const { return FT_FUNC; }
   const char *func_name() const { return "match"; }
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 5b3cc326679..5371030fafa 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -35,9 +35,9 @@ inline Item * and_items(Item* cond, Item *item)
 }
 
 Item_subselect::Item_subselect():
-  Item_result_field(), value_assigned(0), substitution(0),
-  engine(0), used_tables_cache(0), have_to_be_excluded(0),
-  const_item_cache(1), engine_changed(0)
+  Item_result_field(), value_assigned(0), thd(0), substitution(0),
+  engine(0), old_engine(0), used_tables_cache(0), have_to_be_excluded(0),
+  const_item_cache(1), engine_changed(0), changed(0)
 {
   reset();
   /*
@@ -54,9 +54,10 @@ void Item_subselect::init(st_select_lex *select_lex,
 
   DBUG_ENTER("Item_subselect::init");
   DBUG_PRINT("subs", ("select_lex 0x%xl", (ulong) select_lex));
+  unit= select_lex->master_unit();
 
   if (select_lex->next_select())
-    engine= new subselect_union_engine(select_lex->master_unit(), result,
+    engine= new subselect_union_engine(unit, result,
 				       this);
   else
     engine= new subselect_single_select_engine(select_lex, result, this);
@@ -65,8 +66,26 @@ void Item_subselect::init(st_select_lex *select_lex,
 
 void Item_subselect::cleanup()
 {
+  DBUG_ENTER("Item_subselect::cleanup");
   Item_result_field::cleanup();
-  engine->cleanup(); 
+  if (old_engine)
+  {
+    engine->cleanup();
+    engine= old_engine;
+    old_engine= 0;
+  }
+  engine->cleanup();
+  reset();
+  value_assigned= 0;
+  DBUG_VOID_RETURN;
+}
+
+void Item_singlerow_subselect::cleanup()
+{
+  DBUG_ENTER("Item_singlerow_subselect::cleanup");
+  value= 0; row= 0;
+  Item_subselect::cleanup();
+  DBUG_VOID_RETURN;
 }
 
 Item_subselect::~Item_subselect()
@@ -85,13 +104,22 @@ Item_subselect::select_transformer(JOIN *join)
 bool Item_subselect::fix_fields(THD *thd_param, TABLE_LIST *tables, Item **ref)
 {
   engine->set_thd((thd= thd_param));
+  stmt= thd->current_statement;
 
   char const *save_where= thd->where;
   int res= engine->prepare();
+
+  // all transformetion is done (used by prepared statements)
+  changed= 1;
+
   if (!res)
   {
     if (substitution)
     {
+      // did we changed top item of WHERE condition
+      if (unit->outer_select()->where == (*ref))
+	unit->outer_select()->where= substitution; // correct WHERE for PS
+
       (*ref)= substitution;
       substitution->name= name;
       if (have_to_be_excluded)
@@ -240,8 +268,12 @@ void Item_singlerow_subselect::reset()
 Item_subselect::trans_res
 Item_singlerow_subselect::select_transformer(JOIN *join)
 {
+  if (changed)
+    return RES_OK;
+    
   SELECT_LEX *select_lex= join->select_lex;
-  
+  Statement backup;
+
   if (!select_lex->master_unit()->first_select()->next_select() &&
       !select_lex->table_list.elements &&
       select_lex->item_list.elements == 1 &&
@@ -275,20 +307,30 @@ Item_singlerow_subselect::select_transformer(JOIN *join)
     if (join->conds || join->having)
     {
       Item *cond;
+      if (stmt)
+	thd->set_n_backup_item_arena(stmt, &backup);
+
       if (!join->having)
 	cond= join->conds;
       else if (!join->conds)
 	cond= join->having;
       else
 	if (!(cond= new Item_cond_and(join->conds, join->having)))
-	  return RES_ERROR;
+	  goto err;
       if (!(substitution= new Item_func_if(cond, substitution,
 					   new Item_null())))
-	return RES_ERROR;
+	goto err;
     }
+    if (stmt)
+      thd->restore_backup_item_arena(&backup);
     return RES_REDUCE;
   }
   return RES_OK;
+  
+err:
+  if (stmt)
+    thd->restore_backup_item_arena(&backup);
+  return RES_ERROR;
 }
 
 void Item_singlerow_subselect::store(uint i, Item *item)
@@ -550,15 +592,22 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 {
   DBUG_ENTER("Item_in_subselect::single_value_transformer");
 
+  if (changed)
+  {
+    DBUG_RETURN(RES_OK);
+  }
+
   SELECT_LEX *select_lex= join->select_lex;
+  Statement backup;
 
-  THD *thd_tmp= join->thd;
-  thd_tmp->where= "scalar IN/ALL/ANY subquery";
+  thd->where= "scalar IN/ALL/ANY subquery";
+  if (stmt)
+    thd->set_n_backup_item_arena(stmt, &backup);
 
   if (select_lex->item_list.elements > 1)
   {
     my_error(ER_OPERAND_COLUMNS, MYF(0), 1);
-    DBUG_RETURN(RES_ERROR);
+    goto err;
   }
 
   if ((abort_on_null || (upper_not && upper_not->top_level())) &&
@@ -567,7 +616,7 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     if (substitution)
     {
       // It is second (third, ...) SELECT of UNION => All is done
-      DBUG_RETURN(RES_OK);
+      goto ok;
     }
 
     Item *subs;
@@ -597,10 +646,9 @@ Item_in_subselect::single_value_transformer(JOIN *join,
       select_lex->item_list.empty();
       select_lex->item_list.push_back(item);
     
-      if (item->fix_fields(thd_tmp, join->tables_list, &item))
-      {
-	DBUG_RETURN(RES_ERROR);
-      }
+      if (item->fix_fields(thd, join->tables_list, &item))
+	goto err;
+
       subs= new Item_singlerow_subselect(select_lex);
     }
     else
@@ -611,16 +659,16 @@ Item_in_subselect::single_value_transformer(JOIN *join,
       subs= new Item_maxmin_subselect(this, select_lex, func->l_op());
     }
     // left expression belong to outer select
-    SELECT_LEX *current= thd_tmp->lex->current_select, *up;
-    thd_tmp->lex->current_select= up= current->return_after_parsing();
-    if (left_expr->fix_fields(thd_tmp, up->get_table_list(), &left_expr))
+    SELECT_LEX *current= thd->lex->current_select, *up;
+    thd->lex->current_select= up= current->return_after_parsing();
+    if (left_expr->fix_fields(thd, up->get_table_list(), &left_expr))
     {
-      thd_tmp->lex->current_select= current;
-      DBUG_RETURN(RES_ERROR);
+      thd->lex->current_select= current;
+      goto err;
     }
-    thd_tmp->lex->current_select= current;
+    thd->lex->current_select= current;
     substitution= func->create(left_expr, subs);
-    DBUG_RETURN(RES_OK);
+    goto ok;
   }
 
   if (!substitution)
@@ -629,16 +677,16 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     SELECT_LEX_UNIT *unit= select_lex->master_unit();
     substitution= optimizer= new Item_in_optimizer(left_expr, this);
 
-    SELECT_LEX *current= thd_tmp->lex->current_select, *up;
+    SELECT_LEX *current= thd->lex->current_select, *up;
 
-    thd_tmp->lex->current_select= up= current->return_after_parsing();
+    thd->lex->current_select= up= current->return_after_parsing();
     //optimizer never use Item **ref => we can pass 0 as parameter
-    if (!optimizer || optimizer->fix_left(thd_tmp, up->get_table_list(), 0))
+    if (!optimizer || optimizer->fix_left(thd, up->get_table_list(), 0))
     {
-      thd_tmp->lex->current_select= current;
-      DBUG_RETURN(RES_ERROR);
+      thd->lex->current_select= current;
+      goto err;
     }
-    thd_tmp->lex->current_select= current;
+    thd->lex->current_select= current;
 
     /*
       As far as  Item_ref_in_optimizer do not substitude itself on fix_fields
@@ -665,12 +713,12 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 						select_lex->ref_pointer_array,
 						(char *)"<ref>",
 						this->full_name()));
-    join->having= and_items(join->having, item);
+    select_lex->having= join->having= and_items(join->having, item);
     select_lex->having_fix_field= 1;
-    if (join->having->fix_fields(thd_tmp, join->tables_list, &join->having))
+    if (join->having->fix_fields(thd, join->tables_list, &join->having))
     {
       select_lex->having_fix_field= 0;
-      DBUG_RETURN(RES_ERROR);
+      goto err;
     }
     select_lex->having_fix_field= 0;
   }
@@ -687,39 +735,42 @@ Item_in_subselect::single_value_transformer(JOIN *join,
       if (!abort_on_null)
       {
 	having= new Item_is_not_null_test(this, having);
-	join->having= (join->having ?
-		       new Item_cond_and(having, join->having) :
-		       having);
+	select_lex->having=
+	  join->having= (join->having ?
+			 new Item_cond_and(having, join->having) :
+			 having);
 	select_lex->having_fix_field= 1;
-	if (join->having->fix_fields(thd_tmp, join->tables_list,
+	if (join->having->fix_fields(thd, join->tables_list,
 				     &join->having))
 	{
 	  select_lex->having_fix_field= 0;
-	  DBUG_RETURN(RES_ERROR);
+	  goto err;
 	}
 	select_lex->having_fix_field= 0;
 	item= new Item_cond_or(item,
 			       new Item_func_isnull(isnull));
       }
       item->name= (char *)in_additional_cond;
-      join->conds= and_items(join->conds, item);
-      if (join->conds->fix_fields(thd_tmp, join->tables_list, &join->conds))
-	DBUG_RETURN(RES_ERROR);
+      select_lex->where= join->conds= and_items(join->conds, item);
+      if (join->conds->fix_fields(thd, join->tables_list, &join->conds))
+	goto err;
     }
     else
     {
       if (select_lex->master_unit()->first_select()->next_select())
       {
-	join->having= func->create(expr, 
-				   new Item_null_helper(this, item,
-							(char *)"<no matter>",
-							(char *)"<result>"));
+	select_lex->having=
+	  join->having=
+	  func->create(expr, 
+		       new Item_null_helper(this, item,
+					    (char *)"<no matter>",
+					    (char *)"<result>"));
 	select_lex->having_fix_field= 1;
-	if (join->having->fix_fields(thd_tmp, join->tables_list,
+	if (join->having->fix_fields(thd, join->tables_list,
 				     &join->having))
 	{
 	  select_lex->having_fix_field= 0;
-	  DBUG_RETURN(RES_ERROR);
+	  goto err;
 	}
 	select_lex->having_fix_field= 0;
       }
@@ -730,18 +781,29 @@ Item_in_subselect::single_value_transformer(JOIN *join,
 	// fix_field of item will be done in time of substituting 
 	substitution= item;
 	have_to_be_excluded= 1;
-	if (thd_tmp->lex->describe)
+	if (thd->lex->describe)
 	{
 	  char warn_buff[MYSQL_ERRMSG_SIZE];
 	  sprintf(warn_buff, ER(ER_SELECT_REDUCED), select_lex->select_number);
-	  push_warning(thd_tmp, MYSQL_ERROR::WARN_LEVEL_NOTE,
+	  push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
 		       ER_SELECT_REDUCED, warn_buff);
 	}
+	if (stmt)
+	  thd->set_item_arena(&backup);
 	DBUG_RETURN(RES_REDUCE);
       }
     }
   }
+
+ok:
+  if (stmt)
+    thd->restore_backup_item_arena(&backup);
   DBUG_RETURN(RES_OK);
+
+err:
+  if (stmt)
+    thd->restore_backup_item_arena(&backup);
+  DBUG_RETURN(RES_ERROR);
 }
 
 
@@ -750,15 +812,23 @@ Item_in_subselect::row_value_transformer(JOIN *join)
 {
   DBUG_ENTER("Item_in_subselect::row_value_transformer");
 
-  THD *thd_tmp= join->thd;
-  thd_tmp->where= "row IN/ALL/ANY subquery";
+  if (changed)
+  {
+    DBUG_RETURN(RES_OK);
+  }
+  Statement backup;
+  Item *item= 0;
+
+  thd->where= "row IN/ALL/ANY subquery";
+  if (stmt)
+    thd->set_n_backup_item_arena(stmt, &backup);  
 
   SELECT_LEX *select_lex= join->select_lex;
 
   if (select_lex->item_list.elements != left_expr->cols())
   {
     my_error(ER_OPERAND_COLUMNS, MYF(0), left_expr->cols());
-    DBUG_RETURN(RES_ERROR);
+    goto err;
   }
 
   if (!substitution)
@@ -767,62 +837,68 @@ Item_in_subselect::row_value_transformer(JOIN *join)
     SELECT_LEX_UNIT *unit= select_lex->master_unit();
     substitution= optimizer= new Item_in_optimizer(left_expr, this);
 
-    SELECT_LEX *current= thd_tmp->lex->current_select, *up;
-    thd_tmp->lex->current_select= up= current->return_after_parsing();
+    SELECT_LEX *current= thd->lex->current_select, *up;
+    thd->lex->current_select= up= current->return_after_parsing();
     //optimizer never use Item **ref => we can pass 0 as parameter
-    if (!optimizer || optimizer->fix_left(thd_tmp, up->get_table_list(), 0))
+    if (!optimizer || optimizer->fix_left(thd, up->get_table_list(), 0))
     {
-      thd_tmp->lex->current_select= current;
-      DBUG_RETURN(RES_ERROR);
+      thd->lex->current_select= current;
+      goto err;
     }
-    thd_tmp->lex->current_select= current;
+    thd->lex->current_select= current;
     unit->uncacheable|= UNCACHEABLE_DEPENDENT;
   }
 
-  uint n= left_expr->cols();
-
   select_lex->uncacheable|= UNCACHEABLE_DEPENDENT;
-  select_lex->setup_ref_array(thd_tmp,
+  select_lex->setup_ref_array(thd,
 			      select_lex->order_list.elements +
 			      select_lex->group_list.elements);
-  Item *item= 0;
-  List_iterator_fast<Item> li(select_lex->item_list);
-  for (uint i= 0; i < n; i++)
   {
-    Item *func= new Item_ref_null_helper(this, 
-					 select_lex->ref_pointer_array+i,
-					 (char *) "<no matter>",
-					 (char *) "<list ref>");
-    func=
-      eq_creator.create(new Item_ref((*optimizer->get_cache())->
-				     addr(i),
-				     NULL,
-				     (char *)"<no matter>",
+    uint n= left_expr->cols();
+    List_iterator_fast<Item> li(select_lex->item_list);
+    for (uint i= 0; i < n; i++)
+    {
+      Item *func= new Item_ref_null_helper(this, 
+					   select_lex->ref_pointer_array+i,
+					   (char *) "<no matter>",
+					   (char *) "<list ref>");
+      func=
+	eq_creator.create(new Item_ref((*optimizer->get_cache())->
+				       addr(i),
+				       NULL,
+				       (char *)"<no matter>",
 				     (char *)in_left_expr_name),
-			func);
-    item= and_items(item, func);
+			  func);
+      item= and_items(item, func);
+    }
   }
-
   if (join->having || select_lex->with_sum_func ||
       select_lex->group_list.first ||
       !select_lex->table_list.elements)
   {
-    join->having= and_items(join->having, item);
+    select_lex->having= join->having= and_items(join->having, item);
     select_lex->having_fix_field= 1;
-    if (join->having->fix_fields(thd_tmp, join->tables_list, &join->having))
+    if (join->having->fix_fields(thd, join->tables_list, &join->having))
     {
       select_lex->having_fix_field= 0;
-      DBUG_RETURN(RES_ERROR);
+      goto err;
     }
     select_lex->having_fix_field= 0;
   }
   else
   {
-    join->conds= and_items(join->conds, item);
-    if (join->conds->fix_fields(thd_tmp, join->tables_list, &join->having))
-      DBUG_RETURN(RES_ERROR);
+    select_lex->where= join->conds= and_items(join->conds, item);
+    if (join->conds->fix_fields(thd, join->tables_list, &join->having))
+      goto err;
   }
+  if (stmt)
+    thd->restore_backup_item_arena(&backup);
   DBUG_RETURN(RES_OK);
+
+err:
+  if (stmt)
+    thd->restore_backup_item_arena(&backup);
+  DBUG_RETURN(RES_ERROR);
 }
 
 
@@ -894,13 +970,30 @@ subselect_single_select_engine(st_select_lex *select,
   this->select_lex= select_lex;
 }
 
+
 void subselect_single_select_engine::cleanup()
 {
-  prepared= 0;
-  optimized= 0;
-  executed= 0;
+  DBUG_ENTER("subselect_single_select_engine::cleanup");
+  prepared= optimized= executed= 0;
+  DBUG_VOID_RETURN;
+}
+
+
+void subselect_union_engine::cleanup()
+{
+  DBUG_ENTER("subselect_union_engine::cleanup");
+  unit->reinit_exec_mechanism();
+  DBUG_VOID_RETURN;
 }
 
+
+void subselect_uniquesubquery_engine::cleanup()
+{
+  DBUG_ENTER("subselect_uniquesubquery_engine::cleanup");
+  DBUG_VOID_RETURN;
+}
+
+
 subselect_union_engine::subselect_union_engine(st_select_lex_unit *u,
 					       select_subselect *result_arg,
 					       Item_subselect *item_arg)
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index dc3d07540da..d550cde64b7 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -26,6 +26,7 @@ class JOIN;
 class select_subselect;
 class subselect_engine;
 class Item_bool_func2;
+class Statement;
 
 /* base class for subselects */
 
@@ -35,22 +36,30 @@ class Item_subselect :public Item_result_field
 protected:
   /* thread handler, will be assigned in fix_fields only */
   THD *thd;
+  /* prepared statement, or 0 */
+  Statement *stmt;
   /* substitution instead of subselect in case of optimization */
   Item *substitution;
+  /* unit of subquery */
+  st_select_lex_unit *unit;
   /* engine that perform execution of subselect (single select or union) */
   subselect_engine *engine;
+  /* old engine if engine was changed */
+  subselect_engine *old_engine;
   /* cache of used external tables */
   table_map used_tables_cache; 
   /* allowed number of columns (1 for single value subqueries) */
   uint max_columns;
   /* work with 'substitution' */
   bool have_to_be_excluded;
-  /* cache of constante state */
+  /* cache of constant state */
   bool const_item_cache;
 
 public:
   /* changed engine indicator */
   bool engine_changed;
+  /* subquery is transformed */
+  bool changed;
 
   enum trans_res {RES_OK, RES_REDUCE, RES_ERROR};
   enum subs_type {UNKNOWN_SUBS, SINGLEROW_SUBS,
@@ -94,6 +103,7 @@ public:
   void print(String *str);
   bool change_engine(subselect_engine *eng)
   {
+    old_engine= engine;
     engine= eng;
     engine_changed= 1;
     return eng == 0;
@@ -116,6 +126,7 @@ public:
   Item_singlerow_subselect(st_select_lex *select_lex);
   Item_singlerow_subselect() :Item_subselect(), value(0), row (0) {}
 
+  void cleanup();
   subs_type substype() { return SINGLEROW_SUBS; }
 
   void reset();
@@ -200,13 +211,6 @@ public:
      
   {}
 
-  void cleanup()
-  {
-    Item_exists_subselect::cleanup();
-    abort_on_null= 0;
-    transformed= 0;
-    upper_not= 0;
-  }
   subs_type substype() { return IN_SUBS; }
   void reset() 
   {
@@ -269,7 +273,7 @@ public:
     maybe_null= 0;
   }
   virtual ~subselect_engine() {}; // to satisfy compiler
-  virtual void cleanup() {}
+  virtual void cleanup()= 0;
   
   // set_thd should be called before prepare()
   void set_thd(THD *thd_arg) { thd= thd_arg; }
@@ -318,6 +322,7 @@ public:
   subselect_union_engine(st_select_lex_unit *u,
 			 select_subselect *result,
 			 Item_subselect *item);
+  void cleanup();
   int prepare();
   void fix_length_and_dec(Item_cache** row);
   int exec();
@@ -345,6 +350,7 @@ public:
     set_thd(thd_arg);
   }
   ~subselect_uniquesubquery_engine();
+  void cleanup();
   int prepare();
   void fix_length_and_dec(Item_cache** row);
   int exec();
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 10b50fa5b3b..879c5f99ebd 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1084,6 +1084,7 @@ int dump_leaf(byte* key, uint32 count __attribute__((unused)),
 
 void Item_sum_count_distinct::cleanup()
 {
+  DBUG_ENTER("Item_sum_count_distinct::cleanup");
   Item_sum_int::cleanup();
   /*
     Free table and tree if they belong to this item (if item have not pointer
@@ -1104,6 +1105,7 @@ void Item_sum_count_distinct::cleanup()
       use_tree= 0;
     }
   }
+  DBUG_VOID_RETURN;
 }
 
 
@@ -1672,6 +1674,7 @@ Item_func_group_concat::Item_func_group_concat(bool is_distinct,
 
 void Item_func_group_concat::cleanup()
 {
+  DBUG_ENTER("Item_func_group_concat::cleanup");
   /*
     Free table and tree if they belong to this item (if item have not pointer
     to original item from which was made copy => it own its objects )
@@ -1692,6 +1695,7 @@ void Item_func_group_concat::cleanup()
       delete_tree(tree); 
     }
   }
+  DBUG_VOID_RETURN;
 }
 
 
diff --git a/sql/item_sum.h b/sql/item_sum.h
index bb03f029997..06989f30ae5 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -60,8 +60,10 @@ public:
   Item_sum(THD *thd, Item_sum *item);
   void cleanup()
   {
+    DBUG_ENTER("Item_sum::cleanup");
     Item_result_field::cleanup();
     result_field=0;
+    DBUG_VOID_RETURN;
   }
 
   enum Type type() const { return SUM_FUNC_ITEM; }
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 35740ff8bf1..73e30e72a3c 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -247,7 +247,9 @@ extern CHARSET_INFO *national_charset_info, *table_alias_charset;
 // uncachable cause
 #define UNCACHEABLE_DEPENDENT   1
 #define UNCACHEABLE_RAND        2
-#define UNCACHEABLE_SIDEEFFECT 4
+#define UNCACHEABLE_SIDEEFFECT	4
+// forcing to save JOIN for explain
+#define UNCACHEABLE_EXPLAIN     8
 
 #ifdef EXTRA_DEBUG
 /*
@@ -481,14 +483,13 @@ int mysql_select(THD *thd, Item ***rref_pointer_array,
 		 SELECT_LEX *select_lex);
 void free_underlaid_joins(THD *thd, SELECT_LEX *select);
 void fix_tables_pointers(SELECT_LEX *select_lex);
-void fix_tables_pointers(SELECT_LEX_UNIT *select_lex);
 int mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit,
 			select_result *result);
 int mysql_explain_select(THD *thd, SELECT_LEX *sl, char const *type,
 			 select_result *result);
 int mysql_union(THD *thd, LEX *lex, select_result *result,
 		SELECT_LEX_UNIT *unit);
-int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *s, TABLE_LIST *t);
+int mysql_handle_derived(LEX *lex);
 Field *create_tmp_field(THD *thd, TABLE *table,Item *item, Item::Type type,
 			Item ***copy_func, Field **from_field,
 			bool group,bool modify_item);
@@ -674,9 +675,10 @@ int setup_conds(THD *thd,TABLE_LIST *tables,COND **conds);
 int setup_ftfuncs(SELECT_LEX* select);
 int init_ftfuncs(THD *thd, SELECT_LEX* select, bool no_order);
 void wait_for_refresh(THD *thd);
-int open_tables(THD *thd,TABLE_LIST *tables);
+int open_tables(THD *thd, TABLE_LIST *tables, uint *counter);
+int simple_open_n_lock_tables(THD *thd,TABLE_LIST *tables);
 int open_and_lock_tables(THD *thd,TABLE_LIST *tables);
-int lock_tables(THD *thd,TABLE_LIST *tables);
+int lock_tables(THD *thd, TABLE_LIST *tables, uint counter);
 TABLE *open_temporary_table(THD *thd, const char *path, const char *db,
 			    const char *table_name, bool link_in_list);
 bool rm_temporary_table(enum db_type base, char *path);
diff --git a/sql/repl_failsafe.cc b/sql/repl_failsafe.cc
index 81ea9d9e2ac..d125c95e839 100644
--- a/sql/repl_failsafe.cc
+++ b/sql/repl_failsafe.cc
@@ -732,7 +732,7 @@ static int fetch_db_tables(THD *thd, MYSQL *mysql, const char *db,
     int error;
     if (table_rules_on)
     {
-      table.next= 0;
+      bzero((char*) &table, sizeof(table)); //just for safe
       table.db= (char*) db;
       table.real_name= (char*) table_name;
       table.updating= 1;
diff --git a/sql/sql_acl.cc b/sql/sql_acl.cc
index 9d3ab8621d7..c500f0cf1f7 100644
--- a/sql/sql_acl.cc
+++ b/sql/sql_acl.cc
@@ -173,7 +173,8 @@ my_bool acl_init(THD *org_thd, bool dont_read_acl_tables)
   tables[0].lock_type=tables[1].lock_type=tables[2].lock_type=TL_READ;
   tables[0].db=tables[1].db=tables[2].db=thd->db;
 
-  if (open_tables(thd,tables))
+  uint counter;
+  if (open_tables(thd, tables, &counter))
   {
     sql_print_error("Fatal error: Can't open privilege tables: %s",
 		    thd->net.last_error);
@@ -2247,7 +2248,7 @@ int mysql_table_grant(THD *thd, TABLE_LIST *table_list,
   }
 #endif
 
-  if (open_and_lock_tables(thd,tables))
+  if (simple_open_n_lock_tables(thd,tables))
   {						// Should never happen
     close_thread_tables(thd);			/* purecov: deadcode */
     DBUG_RETURN(-1);				/* purecov: deadcode */
@@ -2395,7 +2396,7 @@ int mysql_grant(THD *thd, const char *db, List <LEX_USER> &list,
   }
 
   /* open the mysql.user and mysql.db tables */
-
+  bzero((char*) &tables,sizeof(tables));
   tables[0].alias=tables[0].real_name=(char*) "user";
   tables[1].alias=tables[1].real_name=(char*) "db";
   tables[0].next=tables+1;
@@ -2421,7 +2422,7 @@ int mysql_grant(THD *thd, const char *db, List <LEX_USER> &list,
   }
 #endif
 
-  if (open_and_lock_tables(thd,tables))
+  if (simple_open_n_lock_tables(thd,tables))
   {						// This should never happen
     close_thread_tables(thd);			/* purecov: deadcode */
     DBUG_RETURN(-1);				/* purecov: deadcode */
@@ -2517,14 +2518,15 @@ my_bool grant_init(THD *org_thd)
   thd->store_globals();
   thd->db= my_strdup("mysql",MYF(0));
   thd->db_length=5;				// Safety
-  bzero((char*) &tables,sizeof(tables));
+  bzero((char*) &tables, sizeof(tables));
   tables[0].alias=tables[0].real_name= (char*) "tables_priv";
   tables[1].alias=tables[1].real_name= (char*) "columns_priv";
   tables[0].next=tables+1;
   tables[0].lock_type=tables[1].lock_type=TL_READ;
   tables[0].db=tables[1].db=thd->db;
 
-  if (open_tables(thd,tables))
+  uint counter;
+  if (open_tables(thd, tables, &counter))
     goto end;
 
   TABLE *ptr[2];				// Lock tables for quick update
@@ -3376,7 +3378,7 @@ int open_grant_tables(THD *thd, TABLE_LIST *tables)
   }
 #endif
 
-  if (open_and_lock_tables(thd, tables))
+  if (simple_open_n_lock_tables(thd, tables))
   {						// This should never happen
     close_thread_tables(thd);
     DBUG_RETURN(-1);
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 2a4f73a986c..0ededa80ad6 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1305,9 +1305,9 @@ static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
       goto err;					// Can't repair the table
 
     TABLE_LIST table_list;
+    bzero((char*) &table_list, sizeof(table_list)); // just for safe
     table_list.db=(char*) db;
     table_list.real_name=(char*) name;
-    table_list.next=0;
     safe_mutex_assert_owner(&LOCK_open);
 
     if ((error=lock_table_name(thd,&table_list)))
@@ -1356,27 +1356,45 @@ err:
   DBUG_RETURN(1);
 }
 
-/*****************************************************************************
-** open all tables in list
-*****************************************************************************/
+/*
+  Open all tables in list
+
+  SYNOPSIS
+    open_tables()
+    thd - thread handler
+    start - list of tables
+    counter - number of opened tables will be return using this parameter
 
-int open_tables(THD *thd,TABLE_LIST *start)
+  RETURN
+    0  - OK
+    -1 - error
+*/
+
+int open_tables(THD *thd, TABLE_LIST *start, uint *counter)
 {
   TABLE_LIST *tables;
   bool refresh;
   int result=0;
   DBUG_ENTER("open_tables");
+  *counter= 0;
 
   thd->current_tablenr= 0;
  restart:
   thd->proc_info="Opening tables";
   for (tables=start ; tables ; tables=tables->next)
   {
+    /*
+      Ignore placeholders for derived tables. After derived tables
+      processing, link to created temporary table will be put here.
+     */
+    if (tables->derived)
+      continue;
+    (*counter)++;
     if (!tables->table &&
-	!(tables->table=open_table(thd,
-				   tables->db,
-				   tables->real_name,
-				   tables->alias, &refresh)))
+	!(tables->table= open_table(thd,
+				    tables->db,
+				    tables->real_name,
+				    tables->alias, &refresh)))
     {
       if (refresh)				// Refresh in progress
       {
@@ -1522,15 +1540,57 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
 
 
 /*
-  Open all tables in list and locks them for read.
-  The lock will automaticly be freed by close_thread_tables()
+  Open all tables in list and locks them for read without derived
+  tables processing.
+
+  SYNOPSIS
+    simple_open_n_lock_tables()
+    thd		- thread handler
+    tables	- list of tables for open&locking
+
+  RETURN
+    0  - ok
+    -1 - error
+
+  NOTE
+    The lock will automaticly be freed by close_thread_tables()
 */
 
-int open_and_lock_tables(THD *thd,TABLE_LIST *tables)
+int simple_open_n_lock_tables(THD *thd, TABLE_LIST *tables)
 {
-  if (open_tables(thd,tables) || lock_tables(thd,tables))
-    return -1;					/* purecov: inspected */
-  return 0;
+  DBUG_ENTER("simple_open_n_lock_tables");
+  uint counter;
+  if (open_tables(thd, tables, &counter) || lock_tables(thd, tables, counter))
+    DBUG_RETURN(-1);				/* purecov: inspected */
+  DBUG_RETURN(0);
+}
+
+
+/*
+  Open all tables in list, locks them and process derived tables
+  tables processing.
+
+  SYNOPSIS
+    open_and_lock_tables()
+    thd		- thread handler
+    tables	- list of tables for open&locking
+
+  RETURN
+    0  - ok
+    -1 - error
+
+  NOTE
+    The lock will automaticly be freed by close_thread_tables()
+*/
+
+int open_and_lock_tables(THD *thd, TABLE_LIST *tables)
+{
+  DBUG_ENTER("open_and_lock_tables");
+  uint counter;
+  if (open_tables(thd, tables, &counter) || lock_tables(thd, tables, counter))
+    DBUG_RETURN(-1);				/* purecov: inspected */
+  fix_tables_pointers(thd->lex->all_selects_list);
+  DBUG_RETURN(mysql_handle_derived(thd->lex));
 }
 
 
@@ -1541,6 +1601,7 @@ int open_and_lock_tables(THD *thd,TABLE_LIST *tables)
     lock_tables()
     thd			Thread handler
     tables		Tables to lock
+    count		umber of opened tables
 
   NOTES
     You can't call lock_tables twice, as this would break the dead-lock-free
@@ -1552,7 +1613,7 @@ int open_and_lock_tables(THD *thd,TABLE_LIST *tables)
    -1	Error
 */
 
-int lock_tables(THD *thd,TABLE_LIST *tables)
+int lock_tables(THD *thd, TABLE_LIST *tables, uint count)
 {
   TABLE_LIST *table;
   if (!tables)
@@ -1561,14 +1622,14 @@ int lock_tables(THD *thd,TABLE_LIST *tables)
   if (!thd->locked_tables)
   {
     DBUG_ASSERT(thd->lock == 0);	// You must lock everything at once
-    uint count=0;
-    for (table = tables ; table ; table=table->next)
-      count++;
     TABLE **start,**ptr;
     if (!(ptr=start=(TABLE**) sql_alloc(sizeof(TABLE*)*count)))
       return -1;
     for (table = tables ; table ; table=table->next)
-      *(ptr++)= table->table;
+    {
+      if (!table->derived)
+	*(ptr++)= table->table;
+    }
     if (!(thd->lock=mysql_lock_tables(thd,start,count)))
       return -1;				/* purecov: inspected */
   }
@@ -1576,7 +1637,8 @@ int lock_tables(THD *thd,TABLE_LIST *tables)
   {
     for (table = tables ; table ; table=table->next)
     {
-      if (check_lock_and_start_stmt(thd, table->table, table->lock_type))
+      if (!table->derived && 
+	  check_lock_and_start_stmt(thd, table->table, table->lock_type))
       {
 	ha_rollback_stmt(thd);
 	return -1;
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index cbac11ac42e..cfbf0385b6f 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -83,7 +83,7 @@ extern "C" void free_user_var(user_var_entry *entry)
 ** Thread specific functions
 ****************************************************************************/
 
-THD::THD():user_time(0), is_fatal_error(0),
+THD::THD():user_time(0), current_statement(0), is_fatal_error(0),
 	   last_insert_id_used(0),
 	   insert_id_used(0), rand_used(0), in_lock_tables(0),
 	   global_read_lock(0), bootstrap(0)
@@ -1244,6 +1244,21 @@ void Statement::set_statement(Statement *stmt)
   mem_root=       stmt->mem_root;
 }
 
+void Statement::set_n_backup_item_arena(Statement *set, Statement *backup)
+{
+  backup->mem_root= mem_root;
+  backup->free_list= free_list;
+  set_item_arena(set);
+}
+
+
+void Statement::set_item_arena(Statement *set)
+{
+  mem_root= set->mem_root;
+  free_list= set->free_list;
+}
+
+
 
 Statement::~Statement()
 {
diff --git a/sql/sql_class.h b/sql/sql_class.h
index b3135202ad6..e5b33476d13 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -427,7 +427,7 @@ class Statement
 public:
   /* FIXME: must be private */
   LEX     main_lex;
-public:
+
   /*
     Uniquely identifies each statement object in thread scope; change during
     statement lifetime. FIXME: must be const
@@ -476,7 +476,7 @@ public:
   char *query;
   uint32 query_length;                          // current query length
   /*
-    List of items created in the parser for this query. Every item puts 
+    List of items created in the parser for this query. Every item puts
     itself to the list on creation (see Item::Item() for details))
   */
   Item *free_list;
@@ -503,6 +503,15 @@ public:
   void set_statement(Statement *stmt);
   /* return class type */
   virtual Type type() const;
+
+  void set_n_backup_item_arena(Statement *set, Statement *backup);
+  inline void restore_backup_item_arena(Statement *backup)
+  {
+    set_item_arena(backup);
+    // reset backup mem_root to avoid its freeing
+    init_alloc_root(&backup->mem_root, 0, 0);
+  }
+  void set_item_arena(Statement *set);
 };
 
 
@@ -689,6 +698,10 @@ public:
   Vio* active_vio;
 #endif
   /*
+    Current prepared Statement if there one, or 0
+  */
+  Statement *current_statement;
+  /*
     next_insert_id is set on SET INSERT_ID= #. This is used as the next
     generated auto_increment value in handler.cc
   */
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index ed9136a6d5b..46dd4d7996e 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -43,7 +43,6 @@ int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, SQL_LIST *order,
 
   if ((open_and_lock_tables(thd, table_list)))
     DBUG_RETURN(-1);
-  fix_tables_pointers(thd->lex->all_selects_list);
   table= table_list->table;
   table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
   thd->proc_info="init";
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 374e56ecdd4..6e70fdd46d5 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -25,6 +25,58 @@
 #include "sql_select.h"
 #include "sql_acl.h"
 
+static int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *s,
+			 TABLE_LIST *t);
+
+/*
+  Resolve derived tables in all queries
+
+  SYNOPSIS
+    mysql_handle_derived()
+    lex                 LEX for this thread
+
+  RETURN
+    0	ok
+    -1	Error
+    1	Error and error message given
+*/
+
+int
+mysql_handle_derived(LEX *lex)
+{
+  if (lex->derived_tables)
+  {
+    for (SELECT_LEX *sl= lex->all_selects_list;
+	 sl;
+	 sl= sl->next_select_in_list())
+    {
+      for (TABLE_LIST *cursor= sl->get_table_list();
+	   cursor;
+	   cursor= cursor->next)
+      {
+	int res;
+	if (cursor->derived && (res=mysql_derived(lex->thd, lex,
+						  cursor->derived,
+						  cursor)))
+	{
+	  return res;
+	}
+      }
+      if (lex->describe)
+      {
+	/*
+	  Force join->join_tmp creation, because we will use this JOIN
+	  twice for EXPLAIN and we have to have unchanged join for EXPLAINing
+	*/
+	sl->uncacheable|= UNCACHEABLE_EXPLAIN;
+	sl->master_unit()->uncacheable|= UNCACHEABLE_EXPLAIN;
+      }
+    }
+  }
+  return 0;
+}
+
+
 /*
   Resolve derived tables in all queries
 
@@ -49,9 +101,6 @@
     Derived tables is stored in thd->derived_tables and freed in
     close_thread_tables()
 
-  TODO
-    Move creation of derived tables in open_and_lock_tables()
-
   RETURN
     0	ok
     1	Error
@@ -59,8 +108,8 @@
 */  
 
 
-int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit,
-		  TABLE_LIST *org_table_list)
+static int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit,
+			 TABLE_LIST *org_table_list)
 {
   SELECT_LEX *first_select= unit->first_select();
   TABLE *table;
@@ -72,143 +121,87 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit,
   bool is_subsel= first_select->first_inner_unit() ? 1: 0;
   SELECT_LEX *save_current_select= lex->current_select;
   DBUG_ENTER("mysql_derived");
-  
-  /*
-    In create_total_list, derived tables have to be treated in case of
-    EXPLAIN, This is because unit/node is not deleted in that
-    case. Current code in this function has to be improved to
-    recognize better when this function is called from derived tables
-    and when from other functions.
-  */
-  if ((is_union || is_subsel) && unit->create_total_list(thd, lex, &tables, 1))
-    DBUG_RETURN(-1);
 
+  if (!(derived_result= new select_union(0)))
+    DBUG_RETURN(1); // out of memory
+
+  // st_select_lex_unit::prepare correctly work for single select
+  if ((res= unit->prepare(thd, derived_result, 0)))
+    goto exit;
+
+	
+  derived_result->tmp_table_param.init();
+  derived_result->tmp_table_param.field_count= unit->types.elements;
   /*
-    We have to do access checks here as this code is executed before any
-    sql command is started to execute.
+    Temp table is created so that it hounours if UNION without ALL is to be 
+    processed
   */
-#ifndef NO_EMBEDDED_ACCESS_CHECKS
-  if (tables)
-    res= check_table_access(thd,SELECT_ACL, tables,0);
-  else
-    res= check_access(thd, SELECT_ACL, any_db,0,0,0);
-  if (res)
-    DBUG_RETURN(1);
-#endif
-
-  if (!(res=open_and_lock_tables(thd,tables)))
+  if (!(table= create_tmp_table(thd, &derived_result->tmp_table_param,
+				unit->types, (ORDER*) 0, 
+				is_union && !unit->union_option, 1,
+				(first_select->options | thd->options |
+				 TMP_TABLE_ALL_COLUMNS),
+				HA_POS_ERROR,
+				org_table_list->alias)))
   {
-    if (is_union || is_subsel)
-    {
-      /* 
-	 The following code is a re-do of fix_tables_pointers() found
-	 in sql_select.cc for UNION's within derived tables. The only
-	 difference is in navigation, as in derived tables we care for
-	 this level only.
-
-      */
-      fix_tables_pointers(unit);
-    }
+    res= -1;
+    goto exit;
+  }
+  derived_result->set_table(table);
 
-    if (!(derived_result= new select_union(0)))
-      DBUG_RETURN(1); // out of memory
 
-    // st_select_lex_unit::prepare correctly work for single select
-    if ((res= unit->prepare(thd, derived_result, 0)))
-      goto exit;
+  if (is_union)
+    res= mysql_union(thd, lex, derived_result, unit);
+  else
+  {
+    unit->offset_limit_cnt= first_select->offset_limit;
+    unit->select_limit_cnt= first_select->select_limit+
+      first_select->offset_limit;
+    if (unit->select_limit_cnt < first_select->select_limit)
+      unit->select_limit_cnt= HA_POS_ERROR;
+    if (unit->select_limit_cnt == HA_POS_ERROR)
+      first_select->options&= ~OPTION_FOUND_ROWS;
+
+    lex->current_select= first_select;
+    res= mysql_select(thd, &first_select->ref_pointer_array, 
+		      (TABLE_LIST*) first_select->table_list.first,
+		      first_select->with_wild,
+		      first_select->item_list, first_select->where,
+		      (first_select->order_list.elements+
+		       first_select->group_list.elements),
+		      (ORDER *) first_select->order_list.first,
+		      (ORDER *) first_select->group_list.first,
+		      first_select->having, (ORDER*) NULL,
+		      (first_select->options | thd->options |
+		       SELECT_NO_UNLOCK),
+		      derived_result, unit, first_select);
+  }
 
-    /* 
-       This is done in order to redo all field optimisations when any of the 
-       involved tables is used in the outer query 
-    */
-    if (tables)
-    {
-      for (TABLE_LIST *cursor= tables;  cursor;  cursor= cursor->next)
-	cursor->table->clear_query_id= 1;
-    }
-	
-    derived_result->tmp_table_param.init();
-    derived_result->tmp_table_param.field_count= unit->types.elements;
+  if (!res)
+  {
     /*
-      Temp table is created so that it hounours if UNION without ALL is to be 
-      processed
+      Here we entirely fix both TABLE_LIST and list of SELECT's as
+      there were no derived tables
     */
-    if (!(table= create_tmp_table(thd, &derived_result->tmp_table_param,
-				  unit->types, (ORDER*) 0, 
-				  is_union && !unit->union_option, 1,
-				  (first_select->options | thd->options |
-				   TMP_TABLE_ALL_COLUMNS),
-				  HA_POS_ERROR,
-				  org_table_list->alias)))
-    {
-      res= -1;
-      goto exit;
-    }
-    derived_result->set_table(table);
-
-    if (is_union)
-      res= mysql_union(thd, lex, derived_result, unit);
+    if (derived_result->flush())
+      res= 1;
     else
     {
-      unit->offset_limit_cnt= first_select->offset_limit;
-      unit->select_limit_cnt= first_select->select_limit+
-	first_select->offset_limit;
-      if (unit->select_limit_cnt < first_select->select_limit)
-	unit->select_limit_cnt= HA_POS_ERROR;
-      if (unit->select_limit_cnt == HA_POS_ERROR)
-	first_select->options&= ~OPTION_FOUND_ROWS;
-
-      lex->current_select= first_select;
-      res= mysql_select(thd, &first_select->ref_pointer_array, 
-			(TABLE_LIST*) first_select->table_list.first,
-			first_select->with_wild,
-			first_select->item_list, first_select->where,
-			(first_select->order_list.elements+
-			 first_select->group_list.elements),
-			(ORDER *) first_select->order_list.first,
-			(ORDER *) first_select->group_list.first,
-			first_select->having, (ORDER*) NULL,
-			(first_select->options | thd->options |
-			 SELECT_NO_UNLOCK),
-			derived_result, unit, first_select);
-    }
-
-    if (!res)
-    {
-      /*
-	Here we entirely fix both TABLE_LIST and list of SELECT's as
-	there were no derived tables
-      */
-      if (derived_result->flush())
-	res= 1;
-      else
+      org_table_list->real_name= table->real_name;
+      org_table_list->table= table;
+      if (org_table_list->table_list)
       {
-	org_table_list->real_name=table->real_name;
-	org_table_list->table=table;
-	table->derived_select_number= first_select->select_number;
-	table->tmp_table= TMP_TABLE;
+	org_table_list->table_list->real_name= table->real_name;
+	org_table_list->table_list->table= table;
+      }
+      table->derived_select_number= first_select->select_number;
+      table->tmp_table= TMP_TABLE;
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
-	org_table_list->grant.privilege= SELECT_ACL;
+      table->grant.privilege= SELECT_ACL;
 #endif
-	if (lex->describe)
-	{
-	  // to fix a problem in EXPLAIN
-	  if (tables)
-	  {
-	    for (TABLE_LIST *cursor= tables;  cursor;  cursor= cursor->next)
-	      if (cursor->table_list)
-		cursor->table_list->table=cursor->table;
-	  }
-	}
-	else
-	{
-	  unit->exclude_tree();
-	  unit->cleanup();
-	}
-	org_table_list->db= (char *)"";
-	  // Force read of table stats in the optimizer
-	table->file->info(HA_STATUS_VARIABLE);
-      }
+      org_table_list->db= (char *)"";
+      // Force read of table stats in the optimizer
+      table->file->info(HA_STATUS_VARIABLE);
     }
 
     if (res)
@@ -223,7 +216,6 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit,
 exit:
     delete derived_result;
     lex->current_select= save_current_select;
-    close_thread_tables(thd, 0, 1);
   }
   DBUG_RETURN(res);
 }
diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc
index 405cfdb5bdc..897aa37ba11 100644
--- a/sql/sql_handler.cc
+++ b/sql/sql_handler.cc
@@ -48,7 +48,8 @@ static TABLE **find_table_ptr_by_name(THD *thd,const char *db,
 int mysql_ha_open(THD *thd, TABLE_LIST *tables)
 {
   HANDLER_TABLES_HACK(thd);
-  int err=open_tables(thd,tables);
+  uint counter;
+  int err=open_tables(thd, tables, &counter);
   HANDLER_TABLES_HACK(thd);
   if (err)
     return -1;
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 74de1772555..58c3d143a4f 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -188,7 +188,6 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list,
     res= open_and_lock_tables(thd, table_list);
   if (res)
     DBUG_RETURN(-1);
-  fix_tables_pointers(thd->lex->all_selects_list);
 
   table= table_list->table;
   thd->proc_info="init";
@@ -647,7 +646,8 @@ public:
     thd.command=COM_DELAYED_INSERT;
     thd.lex->current_select= 0; /* for my_message_sql */
 
-    bzero((char*) &thd.net,sizeof(thd.net));	// Safety
+    bzero((char*) &thd.net, sizeof(thd.net));		// Safety
+    bzero((char*) &table_list, sizeof(table_list));	// Safety
     thd.system_thread= SYSTEM_THREAD_DELAYED_INSERT;
     thd.host_or_ip= "";
     bzero((char*) &info,sizeof(info));
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 65c958093bd..89529cec720 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1286,12 +1286,10 @@ bool st_select_lex::test_limit()
     !0 - error
 */
 bool st_select_lex_unit::create_total_list(THD *thd_arg, st_lex *lex,
-					   TABLE_LIST **result_arg,
-					   bool check_derived)
+					   TABLE_LIST **result_arg)
 {
   *result_arg= 0;
-  res= create_total_list_n_last_return(thd_arg, lex, &result_arg,
-				       check_derived);
+  res= create_total_list_n_last_return(thd_arg, lex, &result_arg);
   return res;
 }
 
@@ -1303,8 +1301,7 @@ bool st_select_lex_unit::create_total_list(THD *thd_arg, st_lex *lex,
     thd            THD pointer
     lex            pointer on LEX stricture
     result         pointer on pointer on result list of tables pointer
-    check_derived  force derived table chacking (used for creating 
-                   table list for derived query)
+
   DESCRIPTION
     This is used for UNION & subselect to create a new table list of all used 
     tables.
@@ -1318,8 +1315,7 @@ bool st_select_lex_unit::create_total_list(THD *thd_arg, st_lex *lex,
 bool st_select_lex_unit::
 create_total_list_n_last_return(THD *thd_arg,
 				st_lex *lex,
-				TABLE_LIST ***result_arg,
-				bool check_derived)
+				TABLE_LIST ***result_arg)
 {
   TABLE_LIST *slave_list_first=0, **slave_list_last= &slave_list_first;
   TABLE_LIST **new_table_list= *result_arg, *aux;
@@ -1345,15 +1341,12 @@ create_total_list_n_last_return(THD *thd_arg,
       return 1;
     }
 
-    if (sl->linkage == DERIVED_TABLE_TYPE && !check_derived)
-      goto end;
-
     for (SELECT_LEX_UNIT *inner=  sl->first_inner_unit();
 	 inner;
 	 inner= inner->next_unit())
     {
       if (inner->create_total_list_n_last_return(thd, lex,
-						 &slave_list_last, 0))
+						 &slave_list_last))
 	return 1;
     }
 
@@ -1400,63 +1393,75 @@ end:
   return 0;
 }
 
+
 st_select_lex_unit* st_select_lex_unit::master_unit()
 {
     return this;
 }
 
+
 st_select_lex* st_select_lex_unit::outer_select()
 {
   return (st_select_lex*) master;
 }
 
+
 bool st_select_lex::add_order_to_list(THD *thd, Item *item, bool asc)
 {
   return add_to_list(thd, order_list, item, asc);
 }
 
+
 bool st_select_lex::add_item_to_list(THD *thd, Item *item)
 {
   return item_list.push_back(item);
 }
 
+
 bool st_select_lex::add_group_to_list(THD *thd, Item *item, bool asc)
 {
   return add_to_list(thd, group_list, item, asc);
 }
 
+
 bool st_select_lex::add_ftfunc_to_list(Item_func_match *func)
 {
   return !func || ftfunc_list->push_back(func); // end of memory?
 }
 
+
 st_select_lex_unit* st_select_lex::master_unit()
 {
   return (st_select_lex_unit*) master;
 }
 
+
 st_select_lex* st_select_lex::outer_select()
 {
   return (st_select_lex*) master->get_master();
 }
 
+
 bool st_select_lex::set_braces(bool value)
 {
   braces= value;
   return 0; 
 }
 
+
 bool st_select_lex::inc_in_sum_expr()
 {
   in_sum_expr++;
   return 0;
 }
 
+
 uint st_select_lex::get_in_sum_expr()
 {
   return in_sum_expr;
 }
 
+
 TABLE_LIST* st_select_lex::get_table_list()
 {
   return (TABLE_LIST*) table_list.first;
@@ -1467,21 +1472,25 @@ List<Item>* st_select_lex::get_item_list()
   return &item_list;
 }
 
+
 List<String>* st_select_lex::get_use_index()
 {
   return use_index_ptr;
 }
 
+
 List<String>* st_select_lex::get_ignore_index()
 {
   return ignore_index_ptr;
 }
 
+
 ulong st_select_lex::get_table_join_options()
 {
   return table_join_options;
 }
 
+
 bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num)
 {
   if (ref_pointer_array)
@@ -1493,6 +1502,58 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num)
 			       order_group_num)* 5)) == 0;
 }
 
+
+/*
+  Find db.table which will be updated in this unit
+
+  SYNOPSIS
+    st_select_lex_unit::check_updateable()
+    db		- data base name
+    table	- real table name
+
+  RETURN
+    1 - found
+    0 - OK (table did not found)
+*/
+bool st_select_lex_unit::check_updateable(char *db, char *table)
+{
+  for(SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
+    if (sl->check_updateable(db, table))
+      return 1;
+  return 0;
+}
+
+
+/*
+  Find db.table which will be updated in this select and 
+  underlayed ones (except derived tables)
+
+  SYNOPSIS
+    st_select_lex::check_updateable()
+    db		- data base name
+    table	- real table name
+
+  RETURN
+    1 - found
+    0 - OK (table did not found)
+*/
+bool st_select_lex::check_updateable(char *db, char *table)
+{
+  if (find_real_table_in_list(get_table_list(), db, table))
+    return 1;
+
+  for (SELECT_LEX_UNIT *un= first_inner_unit();
+       un;
+       un= un->next_unit())
+  {
+    if (un->first_select()->linkage != DERIVED_TABLE_TYPE &&
+	un->check_updateable(db, table))
+      return 1;
+  }
+  return 0;
+}
+
+
 void st_select_lex_unit::print(String *str)
 {
   for (SELECT_LEX *sl= first_select(); sl; sl= sl->next_select())
@@ -1535,6 +1596,7 @@ void st_select_lex::print_order(String *str, ORDER *order)
   }
 }
  
+
 void st_select_lex::print_limit(THD *thd, String *str)
 {
   if (!thd)
@@ -1561,7 +1623,11 @@ void st_select_lex::print_limit(THD *thd, String *str)
 
 /*
   There are st_select_lex::add_table_to_list & 
-  st_select_lex::set_lock_for_tables in sql_parse.cc
+  st_select_lex::set_lock_for_tables are in sql_parse.cc
 
   st_select_lex::print is in sql_select.h
+
+  st_select_lex_unit::prepare, st_select_lex_unit::exec,
+  st_select_lex_unit::cleanup, st_select_lex_unit::reinit_exec_mechanism
+  are in sql_union.cc
 */
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 68385bc7bba..8c4b9850e0a 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -232,6 +232,7 @@ public:
       UNCACHEABLE_DEPENDENT
       UNCACHEABLE_RAND
       UNCACHEABLE_SIDEEFFECT
+      UNCACHEABLE_EXPLAIN
   */
   uint8 uncacheable;
   enum sub_select_type linkage;
@@ -336,8 +337,7 @@ public:
   uint union_option;
 
   void init_query();
-  bool create_total_list(THD *thd, st_lex *lex, TABLE_LIST **result,
-			 bool check_current_derived);
+  bool create_total_list(THD *thd, st_lex *lex, TABLE_LIST **result);
   st_select_lex_unit* master_unit();
   st_select_lex* outer_select();
   st_select_lex* first_select() { return (st_select_lex*) slave; }
@@ -354,15 +354,17 @@ public:
   int prepare(THD *thd, select_result *result, ulong additional_options);
   int exec();
   int cleanup();
+  void reinit_exec_mechanism();
 
+  bool check_updateable(char *db, char *table);
   void print(String *str);
+  
 
   friend void mysql_init_query(THD *thd);
   friend int subselect_union_engine::exec();
 private:
   bool create_total_list_n_last_return(THD *thd, st_lex *lex,
-				       TABLE_LIST ***result,
-				       bool check_current_derived);
+				       TABLE_LIST ***result);
 };
 typedef class st_select_lex_unit SELECT_LEX_UNIT;
 
@@ -497,6 +499,7 @@ public:
     init_select();
   }
   bool setup_ref_array(THD *thd, uint order_group_num);
+  bool check_updateable(char *db, char *table);
   void print(THD *thd, String *str);
   static void print_order(String *str, ORDER *order);
   void print_limit(THD *thd, String *str);
diff --git a/sql/sql_olap.cc b/sql/sql_olap.cc
index 1d16771c1a4..efc4cf0921d 100644
--- a/sql/sql_olap.cc
+++ b/sql/sql_olap.cc
@@ -143,18 +143,6 @@ int handle_olaps(LEX *lex, SELECT_LEX *select_lex)
   int count=select_lex->group_list.elements;
   int sl_return=0;
 
-// a fix for UNION's
-  for (TABLE_LIST *cursor= (TABLE_LIST *)select_lex->table_list.first;
-       cursor;
-       cursor=cursor->next)
-  {
-    if (cursor->do_redirect)
-    {
-      //Sinisa TODO: there are function for this purpose: fix_tables_pointers
-      cursor->table= cursor->table_list->table;
-      cursor->do_redirect= 0;
-    }
-  }
 
   lex->last_selects=select_lex;
 
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 9d742995976..42ea6039b6c 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1789,34 +1789,9 @@ mysql_execute_command(THD *thd)
 #endif
   }
 #endif /* !HAVE_REPLICATION */
-  /*
-    TODO: make derived tables processing 'inside' SELECT processing.
-    TODO: solve problem with depended derived tables in subselects
-  */
-  if (lex->derived_tables)
-  {
-    for (SELECT_LEX *sl= lex->all_selects_list;
-	 sl;
-	 sl= sl->next_select_in_list())
-    {
-      for (TABLE_LIST *cursor= sl->get_table_list();
-	   cursor;
-	   cursor= cursor->next)
-      {
-	if (cursor->derived && (res=mysql_derived(thd, lex,
-						  cursor->derived,
-						  cursor)))
-	{
-	  if (res < 0 || thd->net.report_error)
-	    send_error(thd,thd->killed ? ER_SERVER_SHUTDOWN : 0);
-	  DBUG_VOID_RETURN;
-	}
-      }
-    }
-  }
   if (&lex->select_lex != lex->all_selects_list &&
       lex->sql_command != SQLCOM_CREATE_TABLE &&
-      lex->unit.create_total_list(thd, lex, &tables, 0))
+      lex->unit.create_total_list(thd, lex, &tables))
     DBUG_VOID_RETURN;
   
   /*
@@ -1875,7 +1850,6 @@ mysql_execute_command(THD *thd)
 	}
 	else
 	  thd->send_explain_fields(result);
-	fix_tables_pointers(lex->all_selects_list);
 	res= mysql_explain_union(thd, &thd->lex->unit, result);
 	MYSQL_LOCK *save_lock= thd->lock;
 	thd->lock= (MYSQL_LOCK *)0;
@@ -1914,7 +1888,6 @@ mysql_execute_command(THD *thd)
 		   (res= open_and_lock_tables(thd,tables))))
 	break;
 
-    fix_tables_pointers(lex->all_selects_list);
     res= mysql_do(thd, *lex->insert_list);
     if (thd->net.report_error)
       res= -1;
@@ -2123,7 +2096,7 @@ mysql_execute_command(THD *thd)
     lex->select_lex.table_list.first= (byte*) (tables);
     create_table->next= 0;
     if (&lex->select_lex != lex->all_selects_list &&
-	lex->unit.create_total_list(thd, lex, &tables, 0))
+	lex->unit.create_total_list(thd, lex, &tables))
       DBUG_VOID_RETURN;
 
     ulong want_priv= ((lex->create_info.options & HA_LEX_CREATE_TMP_TABLE) ?
@@ -2338,6 +2311,8 @@ mysql_execute_command(THD *thd)
       if (grant_option)
       {
 	TABLE_LIST old_list,new_list;
+	bzero((char*) &old_list, sizeof(old_list));
+	bzero((char*) &new_list, sizeof(new_list)); // Safety
 	old_list=table[0];
 	new_list=table->next[0];
 	old_list.next=new_list.next=0;
@@ -2669,23 +2644,15 @@ mysql_execute_command(THD *thd)
       }
       if (!walk)
       {
-	if (lex->derived_tables)
-	{
-	  // are we trying to delete derived table?
-	  for (walk= (TABLE_LIST*) tables; walk; walk= walk->next)
-	  {
-	    if (!strcmp(auxi->real_name,walk->alias) &&
-		walk->derived)
-	    {
-	      net_printf(thd, ER_NON_UPDATABLE_TABLE,
-			 auxi->real_name, "DELETE");
-	      goto error;
-	    }
-	  }
-	}
 	net_printf(thd, ER_NONUNIQ_TABLE, auxi->real_name);
 	goto error;
       }
+      if (walk->derived)
+      {
+	net_printf(thd, ER_NON_UPDATABLE_TABLE,
+		   auxi->real_name, "DELETE");
+	goto error;
+      }
       walk->lock_type= auxi->lock_type;
       auxi->table_list=  walk;		// Remember corresponding table
     }
@@ -2699,21 +2666,27 @@ mysql_execute_command(THD *thd)
       break;
     /* Fix tables-to-be-deleted-from list to point at opened tables */
     for (auxi=(TABLE_LIST*) aux_tables ; auxi ; auxi=auxi->next)
-      auxi->table= auxi->table_list->table;
-    if (&lex->select_lex != lex->all_selects_list)
     {
-      for (TABLE_LIST *t= select_lex->get_table_list();
-	   t; t= t->next)
+      auxi->table= auxi->table_list->table;
+      /* 
+	 Multi-delete can't be constructed over-union => we always have
+	 single SELECT on top and have to check underlaying SELECTs of it
+      */
+      for (SELECT_LEX_UNIT *un= lex->select_lex.first_inner_unit();
+	   un;
+	   un= un->next_unit())
       {
-	if (find_real_table_in_list(t->table_list->next, t->db, t->real_name))
+	if (un->first_select()->linkage != DERIVED_TABLE_TYPE &&
+	    un->check_updateable(auxi->table_list->db,
+				 auxi->table_list->real_name))
 	{
-	  my_error(ER_UPDATE_TABLE_USED, MYF(0), t->real_name);
+	  my_error(ER_UPDATE_TABLE_USED, MYF(0), auxi->table_list->real_name);
 	  res= -1;
 	  break;
 	}
       }
     }
-    fix_tables_pointers(lex->all_selects_list);
+
     if (!thd->is_fatal_error && (result= new multi_delete(thd,aux_tables,
 							  table_count)))
     {
@@ -2958,7 +2931,6 @@ mysql_execute_command(THD *thd)
     if (tables && ((res= check_table_access(thd, SELECT_ACL, tables,0)) ||
 		   (res= open_and_lock_tables(thd,tables))))
       break;
-    fix_tables_pointers(lex->all_selects_list);
     if (!(res= sql_set_variables(thd, &lex->var_list)))
       send_ok(thd);
     if (thd->net.report_error)
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 9b8ef37757c..0a0aa01fd11 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -733,7 +733,7 @@ static bool mysql_test_select_fields(Prepared_statement *stmt,
     DBUG_RETURN(1);
 #endif
   if ((&lex->select_lex != lex->all_selects_list &&
-       lex->unit.create_total_list(thd, lex, &tables, 0)))
+       lex->unit.create_total_list(thd, lex, &tables)))
    DBUG_RETURN(1);
     
   if (open_and_lock_tables(thd, tables))
@@ -746,21 +746,27 @@ static bool mysql_test_select_fields(Prepared_statement *stmt,
   }
   else 
   {
-    fix_tables_pointers(lex->all_selects_list);
     if (!result && !(result= new select_send()))
     {
       send_error(thd, ER_OUT_OF_RESOURCES);
       DBUG_RETURN(1);
     }
 
-    JOIN *join= new JOIN(thd, fields, select_options, result);
     thd->used_tables= 0;	// Updated by setup_fields  
-
-    if (join->prepare(&select_lex->ref_pointer_array,
-		      (TABLE_LIST*)select_lex->get_table_list(),
-                      wild_num, conds, og_num, order, group, having, proc, 
-                      select_lex, unit))
+    Statement backup;
+    /*
+      we do not want to have in statement memory all that junk,
+      which will be created by preparation => substitute memory
+      from original thread pool
+    */
+    thd->set_n_backup_item_arena(&thd->stmt_backup, &backup);
+    if ((unit->prepare(thd, result, 0)))
+    {
+      thd->restore_backup_item_arena(&backup);
       DBUG_RETURN(1);
+    }
+    thd->restore_backup_item_arena(&backup);
+   
     if (send_prep_stmt(stmt, fields.elements) ||
         thd->protocol_simple.send_fields(&fields, 0)
 #ifndef EMBEDDED_LIBRARY
@@ -768,7 +774,7 @@ static bool mysql_test_select_fields(Prepared_statement *stmt,
 #endif
        )
       DBUG_RETURN(1);
-    join->cleanup();
+    unit->cleanup();
   }
   DBUG_RETURN(0);  
 }
@@ -899,6 +905,7 @@ bool mysql_stmt_prepare(THD *thd, char *packet, uint packet_length)
 
   thd->stmt_backup.set_statement(thd);
   thd->set_statement(stmt);
+  thd->current_statement= stmt;
 
   if (alloc_query(thd, packet, packet_length))
     goto alloc_query_err;
@@ -929,6 +936,7 @@ bool mysql_stmt_prepare(THD *thd, char *packet, uint packet_length)
   cleanup_items(thd->free_list);
   stmt->set_statement(thd);
   thd->set_statement(&thd->stmt_backup);
+  thd->current_statement= 0;
 
   if (init_param_items(stmt))
     goto init_param_err;
@@ -947,6 +955,7 @@ alloc_query_err:
   thd->stmt_map.erase(stmt);
   DBUG_RETURN(1);
 insert_stmt_err:
+  thd->current_statement= 0;
   delete stmt;
   DBUG_RETURN(1);
 }
@@ -980,6 +989,7 @@ void mysql_stmt_execute(THD *thd, char *packet)
   stmt->query_id= thd->query_id;
   thd->stmt_backup.set_statement(thd);
   thd->set_statement(stmt);
+  thd->current_statement= stmt;
   thd->free_list= 0;
 
   /*
@@ -1011,17 +1021,21 @@ void mysql_stmt_execute(THD *thd, char *packet)
     /* Fix ORDER list */
     for (order=(ORDER *)sl->order_list.first ; order ; order=order->next)
       order->item= (Item **)(order+1);
+
+    /*
+      TODO: When the new table structure is ready, then have a status bit 
+      to indicate the table is altered, and re-do the setup_* 
+      and open the tables back.
+    */
+    for (TABLE_LIST *tables= (TABLE_LIST*) sl->table_list.first;
+	 tables;
+	 tables= tables->next)
+    {
+      tables->table= 0; // safety - nasty init
+      tables->table_list= 0;
+    }
   }
 
-  /*
-    TODO: When the new table structure is ready, then have a status bit 
-    to indicate the table is altered, and re-do the setup_* 
-    and open the tables back.
-  */
-  for (TABLE_LIST *tables= (TABLE_LIST*) stmt->lex->select_lex.table_list.first;
-       tables;
-       tables= tables->next)
-    tables->table= 0; // safety - nasty init
 
 #ifndef EMBEDDED_LIBRARY
   if (stmt->param_count && setup_params_data(stmt))
@@ -1051,6 +1065,7 @@ void mysql_stmt_execute(THD *thd, char *packet)
   cleanup_items(stmt->free_list);
   free_root(&thd->mem_root, MYF(0));
   thd->set_statement(&thd->stmt_backup);
+  thd->current_statement= 0;
   DBUG_VOID_RETURN;
 }
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 81051f70a4a..3bdab82d9b6 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -173,7 +173,6 @@ int handle_select(THD *thd, LEX *lex, select_result *result)
   register SELECT_LEX *select_lex = &lex->select_lex;
   DBUG_ENTER("handle_select");
 
-  fix_tables_pointers(lex->all_selects_list);
   if (select_lex->next_select())
     res=mysql_union(thd, lex, result, &lex->unit);
   else
@@ -328,8 +327,7 @@ JOIN::prepare(Item ***rref_pointer_array,
   // Is it subselect
   {
     Item_subselect *subselect;
-    if ((subselect= select_lex->master_unit()->item) &&
-	select_lex->linkage != GLOBAL_OPTIONS_TYPE)
+    if ((subselect= select_lex->master_unit()->item))
     {
       Item_subselect::trans_res res;
       if ((res= subselect->select_transformer(this)) !=
@@ -1525,10 +1523,10 @@ JOIN::cleanup()
 
   lock=0;                                     // It's faster to unlock later
   join_free(1);
-   if (exec_tmp_table1)
-     free_tmp_table(thd, exec_tmp_table1);
-   if (exec_tmp_table2)
-     free_tmp_table(thd, exec_tmp_table2);
+  if (exec_tmp_table1)
+    free_tmp_table(thd, exec_tmp_table1);
+  if (exec_tmp_table2)
+    free_tmp_table(thd, exec_tmp_table2);
   delete select;
   delete_dynamic(&keyuse);
   delete procedure;
@@ -1580,8 +1578,8 @@ mysql_select(THD *thd, Item ***rref_pointer_array,
 	  goto err;
 	}
       }
-      free_join= 0;
     }
+    free_join= 0;
     join->select_options= select_options;
   }
   else
@@ -3794,11 +3792,6 @@ JOIN::join_free(bool full)
       {
 	if (tab->table)
 	{
-	  if (tab->table->key_read)
-	  {
-	    tab->table->key_read= 0;
-	    tab->table->file->extra(HA_EXTRA_NO_KEYREAD);
-	  }
 	  /* Don't free index if we are using read_record */
 	  if (!tab->read_record.table)
 	    tab->table->file->index_end();
@@ -9149,6 +9142,9 @@ int mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
        sl;
        sl= sl->next_select())
   {
+    // drop UNCACHEABLE_EXPLAIN, because it is for internal usage only
+    uint8 uncacheable= (sl->uncacheable & ~UNCACHEABLE_EXPLAIN);
+
     res= mysql_explain_select(thd, sl,
 			      (((&thd->lex->select_lex)==sl)?
 			       ((thd->lex->all_selects_list != sl) ? 
@@ -9156,13 +9152,13 @@ int mysql_explain_union(THD *thd, SELECT_LEX_UNIT *unit, select_result *result)
 			       ((sl == first)?
 				((sl->linkage == DERIVED_TABLE_TYPE) ?
 				 "DERIVED":
-				((sl->uncacheable & UNCACHEABLE_DEPENDENT) ?
+				((uncacheable & UNCACHEABLE_DEPENDENT) ?
 				 "DEPENDENT SUBQUERY":
-				 (sl->uncacheable?"UNCACHEABLE SUBQUERY":
+				 (uncacheable?"UNCACHEABLE SUBQUERY":
 				   "SUBQUERY"))):
-				((sl->uncacheable & UNCACHEABLE_DEPENDENT) ?
+				((uncacheable & UNCACHEABLE_DEPENDENT) ?
 				 "DEPENDENT UNION":
-				 sl->uncacheable?"UNCACHEABLE UNION":
+				 uncacheable?"UNCACHEABLE UNION":
 				  "UNION"))),
 			      result);
     if (res)
diff --git a/sql/sql_select.h b/sql/sql_select.h
index ed650c450c0..a463378006b 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -428,7 +428,6 @@ public:
 
 bool cp_buffer_from_ref(TABLE_REF *ref);
 bool error_if_full_join(JOIN *join);
-void relink_tables(SELECT_LEX *select_lex);
 int report_error(TABLE *table, int error);
 int safe_index_read(JOIN_TAB *tab);
 COND *eliminate_not_funcs(COND *cond);
diff --git a/sql/sql_udf.cc b/sql/sql_udf.cc
index 337f2540a39..6e8aae54b23 100644
--- a/sql/sql_udf.cc
+++ b/sql/sql_udf.cc
@@ -149,7 +149,7 @@ void udf_init()
   tables.lock_type = TL_READ;
   tables.db=new_thd->db;
 
-  if (open_and_lock_tables(new_thd, &tables))
+  if (simple_open_n_lock_tables(new_thd, &tables))
   {
     DBUG_PRINT("error",("Can't open udf table"));
     sql_print_error("Can't open mysql/func table");
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 226086d0d24..a7719a3b519 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -468,3 +468,9 @@ int st_select_lex_unit::cleanup()
   }
   DBUG_RETURN(error);
 }
+
+
+void st_select_lex_unit::reinit_exec_mechanism()
+{
+  prepared= optimized= executed= 0;
+}
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 86593f5ea20..df7b2cf809f 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -79,7 +79,6 @@ int mysql_update(THD *thd,
   if ((open_and_lock_tables(thd, table_list)))
     DBUG_RETURN(-1);
   thd->proc_info="init";
-  fix_tables_pointers(thd->lex->all_selects_list);
   table= table_list->table;
   table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
 
@@ -431,7 +430,6 @@ int mysql_multi_update(THD *thd,
 #endif
   if ((res=open_and_lock_tables(thd,table_list)))
     DBUG_RETURN(res);
-  fix_tables_pointers(thd->lex->all_selects_list);
 
   select_lex->select_limit= HA_POS_ERROR;
 
diff --git a/tests/client_test.c b/tests/client_test.c
index 66637dcb04b..604514594dc 100644
--- a/tests/client_test.c
+++ b/tests/client_test.c
@@ -8095,6 +8095,85 @@ static void test_bug1946()
   rc= mysql_query(mysql,"DROP TABLE prepare_command");
 }
 
+static void test_subqueries()
+{
+  MYSQL_STMT *stmt;
+  int rc, i;
+  const char *query= "SELECT (SELECT SUM(a+b) FROM t2 where t1.b=t2.b GROUP BY t1.a LIMIT 1) as scalar_s, exists (select 1 from t2 where t2.a/2=t1.a) as exists_s, a in (select a+3 from t2) as in_s, (a-1,b-1) in (select a,b from t2) as in_row_s FROM t1";
+
+  myheader("test_subquery");
+  
+  rc = mysql_query(mysql, "DROP TABLE IF EXISTS t1,t2");
+  myquery(rc);
+  
+  rc= mysql_query(mysql,"CREATE TABLE t1 (a int , b int);");
+  myquery(rc);
+
+  rc= mysql_query(mysql,
+		  "insert into t1 values (1,1), (2, 2), (3,3), (4,4), (5,5);");
+  myquery(rc);
+
+  rc= mysql_query(mysql,"create table t2 select * from t1;");
+  myquery(rc);
+
+  stmt= mysql_prepare(mysql, query, strlen(query));
+  mystmt_init(stmt);
+  for (i= 0; i < 3; i++)
+  {
+    rc= mysql_execute(stmt);
+    mystmt(stmt, rc);
+    assert(5 == my_process_stmt_result(stmt));
+  }
+  mysql_stmt_close(stmt);
+
+  rc= mysql_query(mysql, "DROP TABLE t1,t2");
+  myquery(rc);
+}
+
+
+static void test_bad_union()
+{
+  MYSQL_STMT *stmt;
+  const char *query= "SELECT 1, 2 union SELECT 1";
+
+  myheader("test_bad_union");
+  
+  stmt= mysql_prepare(mysql, query, strlen(query));
+  assert(stmt == 0);
+  myerror(NULL); 
+}
+
+static void test_distinct()
+{
+  MYSQL_STMT *stmt;
+  int rc, i;
+  const char *query= "SELECT count(distinct b), group_concat(a) FROM t1";
+
+  myheader("test_subquery");
+  
+  rc = mysql_query(mysql, "DROP TABLE IF EXISTS t1");
+  myquery(rc);
+  
+  rc= mysql_query(mysql,"CREATE TABLE t1 (a int , b int);");
+  myquery(rc);
+
+  rc= mysql_query(mysql,
+		  "insert into t1 values (1,1), (2, 2), (3,3), (4,4), (5,5);");
+  myquery(rc);
+
+  for (i= 0; i < 3; i++)
+  {
+    stmt= mysql_prepare(mysql, query, strlen(query));
+    mystmt_init(stmt);
+    rc= mysql_execute(stmt);
+    mystmt(stmt, rc);
+    assert(1 == my_process_stmt_result(stmt));
+    mysql_stmt_close(stmt);
+  }
+
+  rc= mysql_query(mysql, "DROP TABLE t1");
+  myquery(rc);
+}
 
 /*
   Read and parse arguments and MySQL options from my.cnf
@@ -8234,6 +8313,7 @@ int main(int argc, char **argv)
     test_count= 1;
    
     start_time= time((time_t *)0);
+
     client_query();         /* simple client query test */
 #if NOT_YET_WORKING
     /* Used for internal new development debugging */
@@ -8340,6 +8420,10 @@ int main(int argc, char **argv)
     test_bug1644();	    /* BUG#1644 */
     test_bug1946();         /* test that placeholders are allowed only in 
                                prepared queries */
+    test_subqueries();	    /* repeatable subqueries */
+    test_bad_union();       /* correct setup of UNION */
+    test_distinct();	    /* distinct aggregate functions */
+
 
     end_time= time((time_t *)0);
     total_time+= difftime(end_time, start_time);