Merge

mysql-test/r/subselect.result:
  Auto merged
mysql-test/t/subselect.test:
  Auto merged
sql/item.cc:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/sql_union.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged
sql/sql_lex.h:
  SCCS merged

16 files changed, 522 insertions, 309 deletions

diff --git a/mysql-test/r/subselect.result b/mysql-test/r/subselect.result
index ee24ef445fb..7758c0b96c9 100644
--- a/mysql-test/r/subselect.result
+++ b/mysql-test/r/subselect.result
@@ -46,7 +46,7 @@ SELECT 1 FROM (SELECT 1 as a) as b HAVING (SELECT a)=1;
 1
 1
 SELECT (SELECT 1), a;
-ERROR 42S22: Unknown column 'a' in 'field list'
+ERROR 42S22: Unknown column 'a' in 'checking transformed subquery'
 SELECT 1 as a FROM (SELECT 1) as b HAVING (SELECT a)=1;
 a
 1
@@ -677,7 +677,6 @@ id
 EXPLAIN SELECT * FROM t2 WHERE id IN (SELECT 1+(select 1));
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
 1	PRIMARY	t2	ref	id	id	5	const	1	Using where; Using index
-3	SUBQUERY	NULL	NULL	NULL	NULL	NULL	NULL	NULL	No tables used
 Warnings:
 Note	1247	Select 3 was reduced during optimisation
 Note	1247	Select 2 was reduced during optimisation
@@ -1025,7 +1024,7 @@ CREATE TABLE t1 SELECT (SELECT 1 as a UNION SELECT 1+1 limit 1,1) as a;
 SHOW CREATE TABLE t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
-  `a` bigint(1) NOT NULL default '0'
+  `a` bigint(17) NOT NULL default '0'
 ) TYPE=MyISAM CHARSET=latin1
 drop table t1;
 create table t1 (a int);
@@ -1200,3 +1199,53 @@ SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1
 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1')
 0
 drop table t1;
+create table t1 (a int not null, b int, primary key (a));
+create table t2 (a int not null, primary key (a));
+create table t3 (a int not null, b int, primary key (a));
+insert into t1 values (1,10), (2,20), (3,30),  (4,40);
+insert into t2 values (2), (3), (4), (5);
+insert into t3 values (10,3), (20,4), (30,5);
+select * from t2 where t2.a in (select a from t1);
+a
+2
+3
+4
+explain select * from t2 where t2.a in (select a from t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	4	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where; Using index
+select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+a
+2
+3
+explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	PRIMARY	4	NULL	4	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	eq_ref	PRIMARY	PRIMARY	4	func	1	Using where
+2	DEPENDENT SUBQUERY	t3	eq_ref	PRIMARY	PRIMARY	4	test.t1.b	1	Using where; Using index
+drop table t1, t2, t3;
+create table t1 (a int, b int, index a (a));
+create table t2 (a int, index a (a));
+create table t3 (a int, b int, index a (a));
+insert into t1 values (1,10), (2,20), (3,30), (4,40);
+insert into t2 values (2), (3), (4), (5);
+insert into t3 values (10,3), (20,4), (30,5);
+select * from t2 where t2.a in (select a from t1);
+a
+2
+3
+4
+explain select * from t2 where t2.a in (select a from t1);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	a	5	NULL	4	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	10	Using where; Using index
+select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+a
+2
+3
+explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
+1	PRIMARY	t2	index	NULL	a	5	NULL	4	Using where; Using index
+2	DEPENDENT SUBQUERY	t1	ref	a	a	5	func	10	Using where
+2	DEPENDENT SUBQUERY	t3	index	a	a	5	NULL	3	Using where; Using index
+drop table t1, t2, t3;
diff --git a/mysql-test/t/subselect.test b/mysql-test/t/subselect.test
index 7f957638fff..34cc2e51894 100644
--- a/mysql-test/t/subselect.test
+++ b/mysql-test/t/subselect.test
@@ -792,3 +792,38 @@ INSERT INTO t1 VALUES("2f6161e879db43c1a5b82c21ddc49089", "Default", "System", "
 INSERT INTO t1 VALUES("c373e9f5ad0791724315444553544200", "AddDocumentTest", "admin", "2003-06-09 10:51:25", "Movie Reviews", "0", "2003-06-09 10:51:25", "admin", "0", "2f6161e879db43c1a5b82c21ddc49089", "03eea05112b845949f3fd03278b5fe43", NULL);
 SELECT 'c373e9f5ad0791a0dab5444553544200' IN(SELECT t1.FOLDERID FROM t1 WHERE t1.PARENTID='2f6161e879db43c1a5b82c21ddc49089' AND t1.FOLDERNAME = 'Level1');
 drop table t1;
+
+#
+# IN subselect optimization test
+#
+create table t1 (a int not null, b int, primary key (a));
+create table t2 (a int not null, primary key (a));
+create table t3 (a int not null, b int, primary key (a));
+insert into t1 values (1,10), (2,20), (3,30),  (4,40);
+insert into t2 values (2), (3), (4), (5);
+insert into t3 values (10,3), (20,4), (30,5);
+select * from t2 where t2.a in (select a from t1);
+explain select * from t2 where t2.a in (select a from t1);
+select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+drop table t1, t2, t3;
+create table t1 (a int, b int, index a (a));
+create table t2 (a int, index a (a));
+create table t3 (a int, b int, index a (a));
+insert into t1 values (1,10), (2,20), (3,30), (4,40);
+disable_query_log;
+# making table large enough
+let $1 = 10000;
+while ($1)
+ {
+  eval insert into t1 values (rand()*100000+200,rand()*100000); 
+  dec $1;
+ }
+enable_query_log;
+insert into t2 values (2), (3), (4), (5);
+insert into t3 values (10,3), (20,4), (30,5);
+select * from t2 where t2.a in (select a from t1);
+explain select * from t2 where t2.a in (select a from t1);
+select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+explain select * from t2 where t2.a in (select t1.a from t1,t3 where t1.b=t3.a);
+drop table t1, t2, t3;
diff --git a/sql/item.cc b/sql/item.cc
index 3ea537a19de..703118fb65c 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1412,17 +1412,15 @@ bool Item_ref::fix_fields(THD *thd,TABLE_LIST *tables, Item **reference)
     }
   }
 
-/*
- * The following conditional is changed as to correctly identify 
- * incorrect references in group functions or forward references 
- * with sub-select's / derived tables, while it prevents this 
- * check when Item_ref is created in an expression involving 
- * summing function, which is to be placed in the user variable.
- *
- */
- 
+  /*
+    The following conditional is changed as to correctly identify 
+    incorrect references in group functions or forward references 
+    with sub-select's / derived tables, while it prevents this 
+    check when Item_ref is created in an expression involving 
+    summing function, which is to be placed in the user variable.
+  */
   if (((*ref)->with_sum_func && name &&
-       (depended_from || 
+       (depended_from ||
 	!(thd->lex.current_select->linkage != GLOBAL_OPTIONS_TYPE &&
 	  thd->lex.current_select->select_lex()->having_fix_field))) ||
       !(*ref)->fixed)
@@ -1669,11 +1667,11 @@ Item_cache* Item_cache::get_cache(Item_result type)
 
 void Item_cache_str::store(Item *item)
 {
-  str_value.set(buffer, sizeof(buffer), item->charset());
-  value= item->str_result(&str_value);
+  value_buff.set(buffer, sizeof(buffer), item->charset());
+  value= item->str_result(&value_buff);
   if ((null_value= item->null_value))
     value= 0;
-  else if (value != &str_value)
+  else if (value != &value_buff)
   {
     /*
       We copy string value to avoid changing value if 'item' is table field
@@ -1683,10 +1681,10 @@ void Item_cache_str::store(Item *item)
              (select c from t1 where a=t2.a)
         from t2;
     */
-    str_value.copy(*value);
-    value= &str_value;
+    value_buff.copy(*value);
+    value= &value_buff;
   }
-
+  set_charset(&item->collation);
 }
 double Item_cache_str::val()
 { 
diff --git a/sql/item.h b/sql/item.h
index e80648b89de..f5f04b7eb96 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -159,7 +159,7 @@ public:
   virtual bool get_time(TIME *ltime);
   virtual bool get_date_result(TIME *ltime,bool fuzzydate)
   { return get_date(ltime,fuzzydate); }
-  virtual bool is_null() { return 0; };
+  virtual bool is_null() { return 0; }
   virtual void top_level_item() {}
   virtual void set_result_field(Field *field) {}
   virtual bool is_result_field() { return 0; }
@@ -626,6 +626,15 @@ public:
   longlong val_int();
   String* val_str(String* s);
   bool get_date(TIME *ltime, bool fuzzydate);
+  void print(String *str)
+  {
+    str->append("ref_null_helper(");
+    if (ref && *ref)
+      (*ref)->print(str);
+    else
+      str->append('?');
+    str->append(')');
+  }
 };
 
 
@@ -671,6 +680,15 @@ public:
   {}
   bool fix_fields(THD *, struct st_table_list *, Item ** ref);
   Item **storage() {return &item;}
+  void print(String *str)
+  {
+    str->append("ref_null_helper('");
+    if (item)
+      item->print(str);
+    else
+      str->append('?');
+    str->append(')');
+  }
 };
 
 /*
@@ -902,7 +920,7 @@ public:
 class Item_cache_str: public Item_cache
 {
   char buffer[80];
-  String *value;
+  String *value, value_buff;
 public:
   Item_cache_str(): Item_cache() { }
   
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index e3586fef260..7c4ab46e30d 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -367,16 +367,24 @@ int Arg_comparator::compare_e_row()
   return 1;
 }
 
-bool Item_in_optimizer::preallocate_row()
+
+bool Item_in_optimizer::fix_left(THD *thd,
+				 struct st_table_list *tables,
+				 Item **ref)
 {
-  return (!(cache= Item_cache::get_cache(ROW_RESULT)));
+  if (args[0]->fix_fields(thd, tables, ref) ||
+      (!cache && !(cache= Item_cache::get_cache(args[0]->result_type()))))
+    return 1;
+  cache->setup(args[0]);
+  return 0;
 }
 
 
+
 bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
 				   Item ** ref)
 {
-  if (args[0]->fix_fields(thd, tables, args))
+  if (fix_left(thd, tables, ref))
     return 1;
   if (args[0]->maybe_null)
     maybe_null=1;
@@ -389,9 +397,6 @@ bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
   with_sum_func= args[0]->with_sum_func;
   used_tables_cache= args[0]->used_tables();
   const_item_cache= args[0]->const_item();
-  if (!cache && !(cache= Item_cache::get_cache(args[0]->result_type())))
-    return 1;
-  cache->setup(args[0]);
   if (cache->cols() == 1)
   {
     if (args[0]->used_tables())
@@ -410,7 +415,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
 	((Item_cache *)cache->el(i))->set_used_tables(0);
     }
   }
-  if (args[1]->fix_fields(thd, tables, args))
+  if (!args[1]->fixed && args[1]->fix_fields(thd, tables, args))
     return 1;
   Item_in_subselect * sub= (Item_in_subselect *)args[1];
   if (args[0]->cols() != sub->engine->cols())
@@ -1648,7 +1653,8 @@ Item_cond::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
     }
     if (abort_on_null)
       item->top_level_item();
-    if (item->fix_fields(thd, tables, li.ref()) || item->check_cols(1))
+    if ((!item->fixed &&
+	 item->fix_fields(thd, tables, li.ref())) || item->check_cols(1))
       return 1; /* purecov: inspected */
     used_tables_cache|=item->used_tables();
     with_sum_func= with_sum_func || item->with_sum_func;
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 1311cae335f..cab3ede6032 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -91,9 +91,8 @@ protected:
 public:
   Item_in_optimizer(Item *a, Item_in_subselect *b):
     Item_bool_func(a, (Item *)b), cache(0) {}
-  // used by row in transformer
-  bool preallocate_row();
   bool fix_fields(THD *, struct st_table_list *, Item **);
+  bool fix_left(THD *thd, struct st_table_list *tables, Item **ref);
   bool is_null();
   /*
     Item_in_optimizer item is special boolean function. On value request 
@@ -103,7 +102,7 @@ public:
     Item_in_optimizer return NULL, else it evaluate Item_in_subselect.
   */
   longlong val_int();
-  
+  const char *func_name() const { return "IN_OPTIMIZER"; }
   Item_cache **get_cache() { return &cache; }
 };
 
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index cd78edfee7b..7bbd5bb7f83 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -56,7 +56,6 @@ void Item_subselect::init(THD *thd, st_select_lex *select_lex,
   DBUG_ENTER("Item_subselect::init");
   DBUG_PRINT("subs", ("select_lex 0x%xl", (ulong) select_lex));
 
-  select_transformer(thd, select_lex->master_unit());
   if (select_lex->next_select())
     engine= new subselect_union_engine(thd, select_lex->master_unit(), result,
 				       this);
@@ -72,10 +71,12 @@ Item_subselect::~Item_subselect()
     delete engine;
 }
 
-void Item_subselect::select_transformer(THD *thd, st_select_lex_unit *unit) 
+Item_subselect::trans_res
+Item_subselect::select_transformer(THD *thd,
+				   JOIN *join) 
 {
   DBUG_ENTER("Item_subselect::select_transformer");
-  DBUG_VOID_RETURN;
+  DBUG_RETURN(OK);
 }
 
 
@@ -83,27 +84,28 @@ bool Item_subselect::fix_fields(THD *thd_param, TABLE_LIST *tables, Item **ref)
 {
   thd= thd_param;
 
-  if (substitution)
-  {
-    (*ref)= substitution;
-    substitution->name= name;
-    if (have_to_be_excluded)
-      engine->exclude();
-    substitution= 0;
-    int ret= (*ref)->fix_fields(thd, tables, ref);
-    // We can't substitute aggregate functions (like (SELECT (max(i)))
-    if ((*ref)->with_sum_func)
-    {
-      my_error(ER_INVALID_GROUP_FUNC_USE, MYF(0));
-      return 1;
-    }
-    return ret;
-  }
-
   char const *save_where= thd->where;
   int res= engine->prepare();
   if (!res)
   {
+    if (substitution)
+    {
+      (*ref)= substitution;
+      substitution->name= name;
+      if (have_to_be_excluded)
+	engine->exclude();
+      substitution= 0;
+      fixed= 1;
+      thd->where= "checking transformed subquery";
+      int ret= (*ref)->fix_fields(thd, tables, ref);
+      // We can't substitute aggregate functions (like (SELECT (max(i)))
+      if ((*ref)->with_sum_func)
+      {
+	my_error(ER_INVALID_GROUP_FUNC_USE, MYF(0));
+	return 1;
+      }
+      return ret;
+    }
     // Is it one field subselect?
     if (engine->cols() > max_columns)
     {  
@@ -166,12 +168,14 @@ void Item_singlerow_subselect::reset()
     value->null_value= 1;
 }
 
-void Item_singlerow_subselect::select_transformer(THD *thd,
-						  st_select_lex_unit *unit)
+Item_subselect::trans_res
+Item_singlerow_subselect::select_transformer(THD *thd,
+						    JOIN *join)
 {
-  SELECT_LEX *select_lex= unit->first_select();
+  SELECT_LEX *select_lex= join->select_lex;
   
-  if (!select_lex->next_select() && !select_lex->table_list.elements &&
+  if (!select_lex->master_unit()->first_select()->next_select() &&
+      !select_lex->table_list.elements &&
       select_lex->item_list.elements == 1 &&
       /*
 	We cant change name of Item_field or Item_ref, because it will
@@ -199,18 +203,20 @@ void Item_singlerow_subselect::select_transformer(THD *thd,
     if (select_lex->where || select_lex->having)
     {
       Item *cond;
-      if (!select_lex->having)
-	cond= select_lex->where;
-      else if (!select_lex->where)
-	cond= select_lex->having;
+      if (!join->having)
+	cond= join->conds;
+      else if (!join->conds)
+	cond= join->having;
       else
-	if (!(cond= new Item_cond_and(select_lex->having, select_lex->where)))
-	  return;
+	if (!(cond= new Item_cond_and(join->conds, join->having)))
+	  return ERROR;
       if (!(substitution= new Item_func_if(cond, substitution,
 					   new Item_null())))
-	return;
+	return ERROR;
     }
+    return REDUCE;
   }
+  return OK;
 }
 
 void Item_singlerow_subselect::store(uint i, Item *item)
@@ -329,6 +335,37 @@ Item_exists_subselect::Item_exists_subselect(THD *thd,
   DBUG_VOID_RETURN;
 }
 
+bool Item_in_subselect::test_limit(SELECT_LEX_UNIT *unit)
+{
+  SELECT_LEX_NODE *global= unit->global_parameters;
+  if (global->select_limit != HA_POS_ERROR)
+  {
+    my_error(ER_NOT_SUPPORTED_YET, MYF(0),
+	     "LIMIT & IN/ALL/ANY/SOME subquery");
+    return(1);
+  }
+  SELECT_LEX *sl= unit->first_select();
+  for (; sl; sl= sl->next_select())
+  {
+    if (sl->select_limit != HA_POS_ERROR)
+    {
+      my_error(ER_NOT_SUPPORTED_YET, MYF(0),
+	       "LIMIT & IN/ALL/ANY/SOME subquery");
+      return(1);
+    }
+    // We need only 1 row to determinate existence
+    sl->select_limit= 1;
+    // no sense in ORDER BY without LIMIT
+    sl->order_list.empty();
+  }
+  // no sense in ORDER BY without LIMIT
+  global->order_list.empty();
+  // We need only 1 row to determinate existence
+  global->select_limit= 1;
+
+  return(0);
+}
+
 Item_in_subselect::Item_in_subselect(THD *thd, Item * left_exp,
 				     st_select_lex *select_lex):
   Item_exists_subselect()
@@ -338,25 +375,25 @@ Item_in_subselect::Item_in_subselect(THD *thd, Item * left_exp,
   init(thd, select_lex, new select_exists_subselect(this));
   max_columns= UINT_MAX;
   maybe_null= 1;
+  abort_on_null= 0;
   reset();
-  // We need only 1 row to determinate existence
-  select_lex->master_unit()->global_parameters->select_limit= 1;
+  test_limit(select_lex->master_unit());
   DBUG_VOID_RETURN;
 }
 
 Item_allany_subselect::Item_allany_subselect(THD *thd, Item * left_exp,
-					     compare_func_creator f,
+					     compare_func_creator fn,
 					     st_select_lex *select_lex):
   Item_in_subselect()
 {
   DBUG_ENTER("Item_in_subselect::Item_in_subselect");
   left_expr= left_exp;
-  func= f;
+  func= fn;
   init(thd, select_lex, new select_exists_subselect(this));
   max_columns= 1;
+  abort_on_null= 0;
   reset();
-  // We need only 1 row to determinate existence
-  select_lex->master_unit()->global_parameters->select_limit= 1;
+  test_limit(select_lex->master_unit());
   DBUG_VOID_RETURN;
 }
 
@@ -446,6 +483,7 @@ Item_in_subselect::Item_in_subselect(Item_in_subselect *item):
   Item_exists_subselect(item)
 {
   left_expr= item->left_expr;
+  abort_on_null= item->abort_on_null;
 }
 
 Item_allany_subselect::Item_allany_subselect(Item_allany_subselect *item):
@@ -454,227 +492,258 @@ Item_allany_subselect::Item_allany_subselect(Item_allany_subselect *item):
   func= item->func;
 }
 
-void Item_in_subselect::single_value_transformer(THD *thd,
-						 st_select_lex_unit *unit,
-						 Item *left_expr,
-						 compare_func_creator func)
+Item_subselect::trans_res
+Item_in_subselect::single_value_transformer(THD *thd,
+					    JOIN *join,
+					    Item *left_expr,
+					    compare_func_creator func)
 {
   DBUG_ENTER("Item_in_subselect::single_value_transformer");
 
-  if (unit->global_parameters->select_limit != HA_POS_ERROR)
-  {
-    my_error(ER_NOT_SUPPORTED_YET, MYF(0),
-	     "LIMIT & IN/ALL/ANY/SOME subquery");
-    DBUG_VOID_RETURN;
-  }
-  // no sense in ORDER BY without LIMIT
-  unit->global_parameters->order_list.empty();
+  SELECT_LEX *select_lex= join->select_lex;
 
-  Item_in_optimizer *optimizer;
-  substitution= optimizer= new Item_in_optimizer(left_expr, this);
-  if (!optimizer)
-    DBUG_VOID_RETURN;
+  thd->where= "scalar IN/ALL/ANY subquery";
 
-  /*
-    As far as  Item_ref_in_optimizer do not substitude itself on fix_fields
-    we can use same item for all selects.
-  */
-  Item *expr= new Item_ref((Item**)optimizer->get_cache(), 
-			   (char *)"<no matter>",
-			   (char*)"<left expr>");
-  unit->dependent= 1;
-  for (SELECT_LEX * sl= unit->first_select(); sl; sl= sl->next_select())
+  if (!substitution)
   {
-    if (sl->select_limit != HA_POS_ERROR)
+    //first call for this unit
+    SELECT_LEX_UNIT *unit= select_lex->master_unit();
+    substitution= optimizer= new Item_in_optimizer(left_expr, this);
+
+    SELECT_LEX_NODE *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, up->get_table_list(), 0))
     {
-      my_error(ER_NOT_SUPPORTED_YET, MYF(0),
-	       "LIMIT & IN/ALL/ANY/SOME subquery");
-      DBUG_VOID_RETURN;
+      thd->lex.current_select= current;
+      DBUG_RETURN(ERROR);
     }
+    thd->lex.current_select= current;
 
-    sl->dependent= 1;
-    Item *item;
-    if (sl->item_list.elements > 1)
-    {
-      my_error(ER_CARDINALITY_COL, MYF(0), 1);
-      DBUG_VOID_RETURN;
-    }
-    else
-      item= (Item*) sl->item_list.pop();
+    /*
+      As far as  Item_ref_in_optimizer do not substitude itself on fix_fields
+      we can use same item for all selects.
+    */
+    expr= new Item_ref((Item**)optimizer->get_cache(), 
+		       (char *)"<no matter>",
+		       (char *)"<left expr>");
 
-    sl->order_list.empty(); // no sense in ORDER BY without LIMIT
+    unit->dependent= 1;
+  }
 
-    if (sl->having || sl->with_sum_func || sl->group_list.elements)
+  select_lex->dependent= 1;
+  Item *item;
+  if (select_lex->item_list.elements > 1)
+  {
+    my_error(ER_CARDINALITY_COL, MYF(0), 1);
+    DBUG_RETURN(ERROR);
+  }
+  else
+    item= (Item*) select_lex->item_list.head();
+
+  if (join->having || select_lex->with_sum_func ||
+      select_lex->group_list.elements)
+  {
+    item= (*func)(expr,
+		  new Item_ref_null_helper(this,
+					   select_lex->ref_pointer_array,
+					   (char *)"<ref>",
+					   this->full_name()));
+    join->having= and_items(join->having, item);
+    select_lex->having_fix_field= 1;
+    if (join->having->fix_fields(thd, join->tables_list, &join->having))
     {
-      sl->item_list.push_back(item);
-      setup_ref_array(thd, &sl->ref_pointer_array,
-		      1 + sl->select_items +
-		      sl->order_list.elements + sl->group_list.elements);
-      // To prevent crash on Item_ref_null_helper destruction in case of error
-      sl->ref_pointer_array[0]= 0;
-      item= (*func)(expr, new Item_ref_null_helper(this,
-						   sl->ref_pointer_array,
-						   (char *)"<ref>",
-						   this->full_name()));
-      sl->having= and_items(sl->having, item);
+      select_lex->having_fix_field= 0;
+      DBUG_RETURN(ERROR);
     }
-    else
+    select_lex->having_fix_field= 0;
+  }
+  else
+  {
+    select_lex->item_list.empty();
+    select_lex->item_list.push_back(new Item_int("Not_used",
+						 (longlong) 1, 21));
+    select_lex->ref_pointer_array[0]= select_lex->item_list.head();
+    if (select_lex->table_list.elements)
     {
-      sl->item_list.empty();
-      sl->item_list.push_back(new Item_int("Not_used", (longlong) 1, 21));
-      if (sl->table_list.elements)
+      Item *having= item, *isnull= item;
+      if (item->type() == Item::FIELD_ITEM &&
+	  ((Item_field*) item)->field_name[0] == '*')
       {
-	Item *having= item, *isnull= item;
-	if (item->type() == Item::FIELD_ITEM &&
-	    ((Item_field*) item)->field_name[0] == '*')
+	Item_asterisk_remover *remover;
+	item= remover= new Item_asterisk_remover(this, item,
+						 (char *)"<no matter>",
+						 (char *)"<result>");
+	if (!abort_on_null)
 	{
-	  Item_asterisk_remover *remover;
-	  item= remover= new Item_asterisk_remover(this, item,
-						   (char*)"<no matter>",
-						   (char*)"<result>");
 	  having= 
 	    new Item_is_not_null_test(this,
 				      new Item_ref(remover->storage(),
-						   (char*)"<no matter>",
-						   (char*)"<null test>"));
+						   (char *)"<no matter>",
+						   (char *)"<null test>"));
 	  isnull=
 	    new Item_is_not_null_test(this,
 				      new Item_ref(remover->storage(),
-						   (char*)"<no matter>",
-						   (char*)"<null test>"));
+						   (char *)"<no matter>",
+						   (char *)"<null test>"));
 	}
-	having= new Item_is_not_null_test(this, having);
-	sl->having= (sl->having ?
-		     new Item_cond_and(having, sl->having) :
-		     having);
-	item= new Item_cond_or((*func)(expr, item),
-			       new Item_func_isnull(isnull));
-	sl->where= and_items(sl->where, item);
       }
-      else
+      item= (*func)(expr, item);
+      if (!abort_on_null)
       {
-	if (item->type() == Item::FIELD_ITEM &&
-	    ((Item_field*) item)->field_name[0] == '*')
+	having= new Item_is_not_null_test(this, 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, join->tables_list, &join->having))
 	{
-	  my_error(ER_NO_TABLES_USED, MYF(0));
-	  DBUG_VOID_RETURN;
+	  select_lex->having_fix_field= 0;
+	  DBUG_RETURN(ERROR);
 	}
-	if (unit->first_select()->next_select())
-	{
-	  /* 
-	     It is in union => we should perform it.
-	     Item_asterisk_remover used only as wrapper to receine NULL value
-	  */
-	  sl->having= (*func)(expr, 
+	select_lex->having_fix_field= 0;
+	item= new Item_cond_or(item,
+			       new Item_func_isnull(isnull));
+      }
+      join->conds= and_items(join->conds, item);
+      if (join->conds->fix_fields(thd, join->tables_list, &join->conds))
+	DBUG_RETURN(ERROR);
+    }
+    else
+    {
+      if (item->type() == Item::FIELD_ITEM &&
+	  ((Item_field*) item)->field_name[0] == '*')
+      {
+	my_error(ER_NO_TABLES_USED, MYF(0));
+	DBUG_RETURN(ERROR);
+      }
+      if (select_lex->master_unit()->first_select()->next_select())
+      {
+	/* 
+	   It is in union => we should perform it.
+	   Item_asterisk_remover used only as wrapper to receine NULL value
+	*/
+	join->having= (*func)(expr, 
 			      new Item_asterisk_remover(this, item,
 							(char *)"<no matter>",
-							(char*)"<result>"));
+							(char *)"<result>"));
+	select_lex->having_fix_field= 1;
+	if (join->having->fix_fields(thd, join->tables_list, &join->having))
+	{
+	  select_lex->having_fix_field= 0;
+	  DBUG_RETURN(ERROR);
 	}
-	else
+	select_lex->having_fix_field= 0;
+      }
+      else
+      {
+	// it is single select without tables => possible optimization
+	item= (*func)(left_expr, item);
+	// fix_field of item will be done in time of substituting 
+	substitution= item;
+	have_to_be_excluded= 1;
+	if (thd->lex.describe)
 	{
-	  // it is single select without tables => possible optimization
-	  item= (*func)(left_expr, item);
-	  substitution= item;
-	  have_to_be_excluded= 1;
-	  if (thd->lex.describe)
-	  {
-	    char warn_buff[MYSQL_ERRMSG_SIZE];
-	    sprintf(warn_buff, ER(ER_SELECT_REDUCED), sl->select_number);
-	    push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
-			 ER_SELECT_REDUCED, warn_buff);
-	  }
+	  char warn_buff[MYSQL_ERRMSG_SIZE];
+	  sprintf(warn_buff, ER(ER_SELECT_REDUCED), select_lex->select_number);
+	  push_warning(thd, MYSQL_ERROR::WARN_LEVEL_NOTE,
+		       ER_SELECT_REDUCED, warn_buff);
 	}
+	DBUG_RETURN(REDUCE);
       }
     }
   }
-  DBUG_VOID_RETURN;
+  DBUG_RETURN(OK);
 }
 
-void Item_in_subselect::row_value_transformer(THD *thd,
-					      st_select_lex_unit *unit,
+Item_subselect::trans_res
+Item_in_subselect::row_value_transformer(THD *thd,
+					      JOIN *join,
 					      Item *left_expr)
 {
   DBUG_ENTER("Item_in_subselect::row_value_transformer");
-  if (unit->global_parameters->select_limit != 
-      HA_POS_ERROR)
-  {
-    /*
-      Because we do the following (not exactly, following is just explenation) 
-      transformation
-      SELECT * from t1 WHERE t1.a IN (SELECT t2.a FROM t2)
-        ->
-      SELECT * from t1 WHERE EXISTS(SELECT 1 FROM t2 t1.a = t2.a LIMIT 1)
 
-      it's impossible to support limit in the sub select.
-    */
-    my_error(ER_NOT_SUPPORTED_YET, MYF(0),
-	     "LIMIT & IN/ALL/ANY/SOME subquery");
-    DBUG_VOID_RETURN;
-  }
-  // no sense in ORDER BY without LIMIT
-  unit->global_parameters->order_list.empty();
+  thd->where= "row IN/ALL/ANY subquery";
 
-  Item_in_optimizer *optimizer;
-  substitution= optimizer= new Item_in_optimizer(left_expr, this);
-  if (!optimizer)
-    DBUG_VOID_RETURN;
+  SELECT_LEX *select_lex= join->select_lex;
 
-  unit->dependent= 1;
-  uint n= left_expr->cols();
-  if (optimizer->preallocate_row() || (*optimizer->get_cache())->allocate(n))
-    DBUG_VOID_RETURN;
-  for (SELECT_LEX * sl= unit->first_select(); sl; sl= sl->next_select())
+  if (!substitution)
   {
-    if (sl->select_limit != HA_POS_ERROR)
+    //first call for this unit
+    SELECT_LEX_UNIT *unit= select_lex->master_unit();
+    substitution= optimizer= new Item_in_optimizer(left_expr, this);
+
+    SELECT_LEX_NODE *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, up->get_table_list(), 0))
     {
-      my_error(ER_NOT_SUPPORTED_YET, MYF(0),
-	       "LIMIT & IN/ALL/ANY/SOME subquery");
-      DBUG_VOID_RETURN;
+      thd->lex.current_select= current;
+      DBUG_RETURN(ERROR);
     }
-    sl->order_list.empty(); // no sense in ORDER BY without LIMIT
+    thd->lex.current_select= current;
+
+    unit->dependent= 1;
+  }
+
+  uint n= left_expr->cols();
 
-    sl->dependent= 1;
+  select_lex->dependent= 1;
 
-    Item *item= 0;
-    List_iterator_fast<Item> li(sl->item_list);
-    for (uint i= 0; i < n; i++)
+  Item *item= 0;
+  List_iterator_fast<Item> li(select_lex->item_list);
+  for (uint i= 0; i < n; i++)
+  {
+    Item *func=
+      new Item_ref_on_list_position(this, select_lex, i,
+				    (char *) "<no matter>",
+				    (char *) "<list ref>");
+    func=
+      Item_bool_func2::eq_creator(new Item_ref((*optimizer->get_cache())->
+					       addr(i), 
+					       (char *)"<no matter>",
+					       (char *)"<left expr>"),
+				  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_fix_field= 1;
+    if (join->having->fix_fields(thd, join->tables_list, &join->having))
     {
-      Item *func=
-	new Item_ref_on_list_position(this, sl, i,
-				      (char *) "<no matter>",
-				      (char *) "<list ref>");
-      func=
-	Item_bool_func2::eq_creator(new Item_ref((*optimizer->get_cache())->
-						 addr(i), 
-						 (char *)"<no matter>",
-						 (char *)"<left expr>"),
-				    func);
-      item= and_items(item, func);
+      select_lex->having_fix_field= 0;
+      DBUG_RETURN(ERROR);
     }
-
-    if (sl->having || sl->with_sum_func || sl->group_list.first ||
-	!sl->table_list.elements)
-      sl->having= and_items(sl->having, item);
-    else
-      sl->where= and_items(sl->where, item);
+    select_lex->having_fix_field= 0;
   }
-  DBUG_VOID_RETURN;
+  else
+  {
+    join->conds= and_items(join->conds, item);
+    if (join->conds->fix_fields(thd, join->tables_list, &join->having))
+      DBUG_RETURN(ERROR);
+  }
+  DBUG_RETURN(OK);
 }
 
-
-void Item_in_subselect::select_transformer(THD *thd, st_select_lex_unit *unit)
+Item_subselect::trans_res
+Item_in_subselect::select_transformer(THD *thd, JOIN *join)
 {
   if (left_expr->cols() == 1)
-    single_value_transformer(thd, unit, left_expr,
-			     &Item_bool_func2::eq_creator);
+    return single_value_transformer(thd, join, left_expr,
+				    &Item_bool_func2::eq_creator);
   else
-    row_value_transformer(thd, unit, left_expr);
+    return row_value_transformer(thd, join, left_expr);
 }
 
-void Item_allany_subselect::select_transformer(THD *thd,
-					       st_select_lex_unit *unit)
+Item_subselect::trans_res
+Item_allany_subselect::select_transformer(THD *thd,
+					  JOIN *join)
 {
-  single_value_transformer(thd, unit, left_expr, func);
+  return single_value_transformer(thd, join, left_expr, func);
 }
 
 subselect_single_select_engine::subselect_single_select_engine(THD *thd, 
diff --git a/sql/item_subselect.h b/sql/item_subselect.h
index 3ed3f2af0e9..1efdf23d1fc 100644
--- a/sql/item_subselect.h
+++ b/sql/item_subselect.h
@@ -48,6 +48,8 @@ protected:
   bool have_to_be_excluded;
 
 public:
+  enum trans_res {OK, REDUCE, ERROR};
+
   Item_subselect();
   Item_subselect(Item_subselect *item)
   {
@@ -73,7 +75,7 @@ public:
   {
     null_value= 1;
   }
-  virtual void select_transformer(THD *thd, st_select_lex_unit *unit);
+  virtual trans_res select_transformer(THD *thd, JOIN *join);
   bool assigned() { return value_assigned; }
   void assigned(bool a) { value_assigned= a; }
   enum Type type() const;
@@ -86,6 +88,13 @@ public:
   bool exec();
   virtual void fix_length_and_dec();
   table_map used_tables() const;
+  void print(String *str)
+  {
+    if (name)
+      str->append(name);
+    else
+      str->append("-subselect-");
+  }
 
   friend class select_subselect;
   friend class Item_in_optimizer;
@@ -108,7 +117,7 @@ public:
     decimals= item->decimals;
   }
   void reset();
-  void select_transformer(THD *thd, st_select_lex_unit *unit);
+  trans_res select_transformer(THD *thd, JOIN *join);
   void store(uint i, Item* item);
   double val();
   longlong val_int ();
@@ -164,25 +173,35 @@ class Item_in_subselect :public Item_exists_subselect
 {
 protected:
   Item * left_expr;
+  /*
+    expr & optinizer used in subselect rewriting to store Item for
+    all JOIN in UNION
+  */
+  Item *expr;
+  Item_in_optimizer *optimizer;
   bool was_null;
+  bool abort_on_null;
 public:
   Item_in_subselect(THD *thd, Item * left_expr, st_select_lex *select_lex);
   Item_in_subselect(Item_in_subselect *item);
-  Item_in_subselect(): Item_exists_subselect() {}
+  Item_in_subselect(): Item_exists_subselect(),  abort_on_null(0)  {}
   void reset() 
   {
     value= 0;
     null_value= 0;
     was_null= 0;
   }
-  virtual void select_transformer(THD *thd, st_select_lex_unit *unit);
-  void single_value_transformer(THD *thd, st_select_lex_unit *unit,
-				Item *left_expr, compare_func_creator func);
-  void row_value_transformer(THD *thd, st_select_lex_unit *unit,
-			     Item *left_expr);
+  trans_res select_transformer(THD *thd, JOIN *join);
+  trans_res single_value_transformer(THD *thd, JOIN *join,
+				     Item *left_expr,
+				     compare_func_creator func);
+  trans_res row_value_transformer(THD *thd, JOIN * join,
+				  Item *left_expr);
   longlong val_int();
   double val();
   String *val_str(String*);
+  void top_level_item() { abort_on_null=1; }
+  bool test_limit(st_select_lex_unit *unit);
 
   friend class Item_asterisk_remover;
   friend class Item_ref_null_helper;
@@ -199,7 +218,7 @@ public:
   Item_allany_subselect(THD *thd, Item * left_expr, compare_func_creator f,
 		     st_select_lex *select_lex);
   Item_allany_subselect(Item_allany_subselect *item);
-  virtual void select_transformer(THD *thd, st_select_lex_unit *unit);
+  trans_res select_transformer(THD *thd, JOIN *join);
 };
 
 class subselect_engine: public Sql_alloc
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index c61e8f42343..0f8b9b96455 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -200,7 +200,8 @@ int mysql_derived(THD *thd, LEX *lex, SELECT_LEX_UNIT *unit,
 	    if (tables)
 	    {
 	      for (TABLE_LIST *cursor= tables;  cursor;  cursor= cursor->next)
-		cursor->table_list->table=cursor->table;
+		if (cursor->table_list)
+		  cursor->table_list->table=cursor->table;
 	    }
 	  }
 	  else
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 1658d5d14c1..797aa808187 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1310,11 +1310,13 @@ bool st_select_lex_unit::create_total_list_n_last_return(THD *thd, st_lex *lex,
 	    return 1;
 	  }
 	  *new_table_list= cursor;
+	  cursor->table_list= aux; //to be able mark this table as shared
 	  new_table_list= &cursor->next;
 	  *new_table_list= 0;			// end result list
 	}
 	else
-	  aux->shared= 1;			// Mark that it's used twice
+	  // Mark that it's used twice
+	  cursor->table_list->shared= aux->shared= 1;
 	aux->table_list= cursor;
       }
     }
@@ -1411,6 +1413,7 @@ ulong st_select_lex::get_table_join_options()
 }
 
 st_select_lex::st_select_lex(struct st_lex *lex)
+  :fake_select(0)
 {
   select_number= ++lex->thd->select_number;
   init_query();
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index 5cebddaf02e..e8b44d50295 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -367,6 +367,8 @@ public:
      before passing to handle_select)
   */
   bool insert_select;
+  /* TRUE for fake select, which used in UNION processing */
+  bool fake_select;
 
   void init_query();
   void init_select();
@@ -420,7 +422,7 @@ public:
   
   friend void mysql_init_query(THD *thd);
   st_select_lex(struct st_lex *lex);
-  st_select_lex() {}
+  st_select_lex() :fake_select(0) {}
   void make_empty_select(st_select_lex *last_select)
   {
     select_number=INT_MAX;
diff --git a/sql/sql_olap.cc b/sql/sql_olap.cc
index a5f164e1e38..ef7bf013be8 100644
--- a/sql/sql_olap.cc
+++ b/sql/sql_olap.cc
@@ -150,7 +150,8 @@ int handle_olaps(LEX *lex, SELECT_LEX *select_lex)
   {
     if (cursor->do_redirect)
     {
-      cursor->table= ((TABLE_LIST*) cursor->table)->table;
+      //Sinisa TODO: there are function for this purpose: fix_tables_pointers
+      cursor->table= cursor->table_list->table;
       cursor->do_redirect= 0;
     }
   }
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 31bb8ffc032..8c96976c841 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -200,7 +200,8 @@ void relink_tables(SELECT_LEX *select_lex)
   for (TABLE_LIST *cursor= (TABLE_LIST *) select_lex->table_list.first;
        cursor;
        cursor=cursor->next)
-    cursor->table= cursor->table_list->table;
+    if (cursor->table_list)
+      cursor->table= cursor->table_list->table;
 }
 
 
@@ -313,6 +314,19 @@ JOIN::prepare(Item ***rref_pointer_array,
       having->split_sum_func(ref_pointer_array, all_fields);
   }
 
+  // Is it subselect
+  {
+    Item_subselect *subselect;
+    if ((subselect= select_lex->master_unit()->item) &&
+	!select_lex->fake_select)
+    {
+      Item_subselect::trans_res res;
+      if ((res= subselect->select_transformer(thd, this)) !=
+	  Item_subselect::OK)
+	DBUG_RETURN((res == Item_subselect::ERROR));
+    }
+  }
+
   if (setup_ftfuncs(select_lex)) /* should be after having->fix_fields */
     DBUG_RETURN(-1);
   
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 724cc658b15..616f54d7109 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -188,42 +188,33 @@ int st_select_lex_unit::prepare(THD *thd, select_result *sel_result,
   union_result->not_describe=1;
   union_result->tmp_table_param=tmp_table_param;
 
-  /*
-    The following piece of code is placed here solely for the purpose of 
-    getting correct results with EXPLAIN when UNION is withing a sub-select
-    or derived table ...
-  */
-
-  if (thd->lex.describe)
+  for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select())
   {
-    for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select())
-    {
-      JOIN *join= new JOIN(thd, sl->item_list, 
-			   sl->options | thd->options | SELECT_NO_UNLOCK,
-			   union_result);
-      thd->lex.current_select= sl;
-      offset_limit_cnt= sl->offset_limit;
-      select_limit_cnt= sl->select_limit+sl->offset_limit;
-      if (select_limit_cnt < sl->select_limit)
-	select_limit_cnt= HA_POS_ERROR;		// no limit
-      if (select_limit_cnt == HA_POS_ERROR)
-	sl->options&= ~OPTION_FOUND_ROWS;
-      
-      res= join->prepare(&sl->ref_pointer_array,
-			 (TABLE_LIST*) sl->table_list.first, sl->with_wild,
-			 sl->where,
-			 ((sl->braces) ? sl->order_list.elements : 0) +
-			 sl->group_list.elements,
-			 (sl->braces) ? 
-			 (ORDER *)sl->order_list.first : (ORDER *) 0,
-			 (ORDER*) sl->group_list.first,
-			 sl->having,
-			 (ORDER*) NULL,
-			 sl, this, t_and_f);
-      t_and_f= 0;
-      if (res || thd->is_fatal_error)
-	goto err;
-    }
+    JOIN *join= new JOIN(thd, sl->item_list, 
+			 sl->options | thd->options | SELECT_NO_UNLOCK,
+			 union_result);
+    thd->lex.current_select= sl;
+    offset_limit_cnt= sl->offset_limit;
+    select_limit_cnt= sl->select_limit+sl->offset_limit;
+    if (select_limit_cnt < sl->select_limit)
+      select_limit_cnt= HA_POS_ERROR;		// no limit
+    if (select_limit_cnt == HA_POS_ERROR)
+      sl->options&= ~OPTION_FOUND_ROWS;
+    
+    res= join->prepare(&sl->ref_pointer_array,
+		       (TABLE_LIST*) sl->table_list.first, sl->with_wild,
+		       sl->where,
+		       ((sl->braces) ? sl->order_list.elements : 0) +
+		       sl->group_list.elements,
+		       (sl->braces) ? 
+		       (ORDER *)sl->order_list.first : (ORDER *) 0,
+		       (ORDER*) sl->group_list.first,
+		       sl->having,
+		       (ORDER*) NULL,
+		       sl, this, t_and_f);
+    t_and_f= 0;
+    if (res || thd->is_fatal_error)
+      goto err;
   }
 
   item_list.empty();
@@ -268,14 +259,12 @@ int st_select_lex_unit::exec()
     }
     for (SELECT_LEX *sl= select_cursor; sl; sl= sl->next_select())
     {
+      thd->lex.current_select= sl;
+
       if (optimized)
 	res= sl->join->reinit();
       else
       {
-	JOIN *join= new JOIN(thd, sl->item_list, 
-			     sl->options | thd->options | SELECT_NO_UNLOCK,
-			     union_result);
-	thd->lex.current_select= sl;
 	offset_limit_cnt= sl->offset_limit;
 	select_limit_cnt= sl->select_limit+sl->offset_limit;
 	if (select_limit_cnt < sl->select_limit)
@@ -283,22 +272,36 @@ int st_select_lex_unit::exec()
 	if (select_limit_cnt == HA_POS_ERROR)
 	  sl->options&= ~OPTION_FOUND_ROWS;
 	
-	res= join->prepare(&sl->ref_pointer_array,
-			   (TABLE_LIST*) sl->table_list.first, sl->with_wild,
-			   sl->where,
-			   ((sl->braces) ? sl->order_list.elements : 0) +
-			   sl->group_list.elements,
-			   (sl->braces) ? 
-			   (ORDER *)sl->order_list.first : (ORDER *) 0,
-			   (ORDER*) sl->group_list.first,
-			   sl->having,
-			   (ORDER*) NULL,
-			   sl, this, t_and_f);
-	t_and_f=0;
-	if (res | thd->is_fatal_error)
+	/* 
+	   As far as union share table space we should reassign table map,
+	   which can be spoiled by 'prepare' of JOIN of other UNION parts
+	   if it use same tables
+	*/
+	uint tablenr=0;
+	for (TABLE_LIST *table_list= (TABLE_LIST*) sl->table_list.first;
+	     table_list;
+	     table_list= table_list->next, tablenr++)
 	{
-	  thd->lex.current_select= lex_select_save;
-	  DBUG_RETURN(res);
+	  if (table_list->shared)
+	  {
+	    /*
+	      review notes: Check it carefully. I still can't understand
+	      why I should not touch table->used_keys. For my point of
+	      view we should do here same procedura as it was done by
+	      setup_table
+	    */
+	    DBUG_PRINT("SUBS", ("shared %s", table_list->real_name));
+	    TABLE *table= table_list->table;
+	    table->used_fields=0;
+	    table->const_table=0;
+	    table->outer_join=table->null_row=0;
+	    table->status=STATUS_NO_RECORD;
+	    table->keys_in_use_for_query= table->keys_in_use;
+	    table->maybe_null=test(table->outer_join=table_list->outer_join);
+	    table->tablenr=tablenr;
+	    table->map= (table_map) 1 << tablenr;
+	    table->force_index= table_list->force_index;
+	  }
 	}
 	res= sl->join->optimize();
       }
@@ -334,6 +337,7 @@ int st_select_lex_unit::exec()
     if (!thd->is_fatal_error)			// Check if EOM
     {
       SELECT_LEX *fake_select  = new SELECT_LEX(&thd->lex);
+      fake_select->fake_select= 1;
       offset_limit_cnt= (select_cursor->braces ?
 			 global_parameters->offset_limit : 0);
       select_limit_cnt= (select_cursor->braces ?
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 1d605abe8a3..659744ce677 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -5149,7 +5149,9 @@ subselect_start:
 	{
 	  LEX *lex=Lex;
 	  if (((int)lex->sql_command >= (int)SQLCOM_HA_OPEN &&
-	       lex->sql_command <= (int)SQLCOM_HA_READ) || lex->sql_command == (int)SQLCOM_KILL)	  {	
+	       lex->sql_command <= (int)SQLCOM_HA_READ) ||
+	       lex->sql_command == (int)SQLCOM_KILL)
+	  {	
 	    send_error(lex->thd, ER_SYNTAX_ERROR);
 	    YYABORT;
 	  }
diff --git a/sql/table.h b/sql/table.h
index 3132e72fb2f..185b22a64f2 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -163,15 +163,8 @@ typedef struct st_table_list
   struct st_table_list *natural_join;	/* natural join on this table*/
   /* ... join ... USE INDEX ... IGNORE INDEX */
   List<String>	*use_index, *ignore_index; 
-  /*
-    Usually hold reference on opened table, but may hold reference
-    to node of complete list of tables used in UNION & subselect.
-  */
-  union
-  {
-    TABLE          *table;      /* opened table */
-    st_table_list  *table_list; /* pointer to node of list of all tables */
-  };
+  TABLE          *table;      /* opened table */
+  st_table_list  *table_list; /* pointer to node of list of all tables */
   class st_select_lex_unit *derived;	/* SELECT_LEX_UNIT of derived table */
  GRANT_INFO	grant;
   thr_lock_type lock_type;