Merge paul@bk-internal.mysql.com:/home/bk/mysql-4.1

into teton.kitebird.com:/home/paul/mysql-4.1


sql/share/english/errmsg.txt:
  Auto merged
sql/share/russian/errmsg.txt:
  Auto merged
sql/share/ukrainian/errmsg.txt:
  Auto merged

27 files changed, 753 insertions, 390 deletions

diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc
index 161b9fe6c32..40ba7ab1cef 100644
--- a/sql/ha_innodb.cc
+++ b/sql/ha_innodb.cc
@@ -290,7 +290,7 @@ convert_error_code_to_mysql(
 
         } else if (error == (int) DB_CANNOT_DROP_CONSTRAINT) {
 
-    		return(HA_ERR_ROW_IS_REFERENCED);
+		return(HA_ERR_ROW_IS_REFERENCED);
 
         } else if (error == (int) DB_COL_APPEARS_TWICE_IN_INDEX) {
 
@@ -755,7 +755,7 @@ innobase_init(void)
 	        srv_set_thread_priorities = TRUE;
 	        srv_query_thread_priority = QUERY_PRIOR;
 	}
-	
+
 	/* Set InnoDB initialization parameters according to the values
 	read from MySQL .cnf file */
 
@@ -870,16 +870,22 @@ innobase_init(void)
 
 	srv_print_verbose_log = mysql_embedded ? 0 : 1;
 
-	if (strcmp(default_charset_info->name, "latin1") == 0) {
+		/* Store the default charset-collation number of this MySQL
+	installation */
 
-		/* Store the character ordering table to InnoDB.
-		For non-latin1 charsets we use the MySQL comparison
-		functions, and consequently we do not need to know
-		the ordering internally in InnoDB. */
+	data_mysql_default_charset_coll = (ulint)default_charset_info->number;
 
-		memcpy(srv_latin1_ordering,
-				default_charset_info->sort_order, 256);
-	}
+	data_mysql_latin1_swedish_charset_coll =
+					(ulint)my_charset_latin1.number;
+
+	/* Store the latin1_swedish_ci character ordering table to InnoDB. For
+	non-latin1_swedish_ci charsets we use the MySQL comparison functions,
+	and consequently we do not need to know the ordering internally in
+	InnoDB. */
+
+	ut_a(0 == ut_strcmp((char*)my_charset_latin1.name,
+						(char*)"latin1_swedish_ci"));
+	memcpy(srv_latin1_ordering, my_charset_latin1.sort_order, 256);
 
 	/* Since we in this module access directly the fields of a trx
         struct, and due to different headers and flags it might happen that
@@ -1661,10 +1667,10 @@ reset_null_bits(
 
 extern "C" {
 /*****************************************************************
-InnoDB uses this function is to compare two data fields for which the
-data type is such that we must use MySQL code to compare them. NOTE that the
-prototype of this function is in rem0cmp.c in InnoDB source code!
-If you change this function, remember to update the prototype there! */
+InnoDB uses this function to compare two data fields for which the data type
+is such that we must use MySQL code to compare them. NOTE that the prototype
+of this function is in rem0cmp.c in InnoDB source code! If you change this
+function, remember to update the prototype there! */
 
 int
 innobase_mysql_cmp(
@@ -1672,6 +1678,7 @@ innobase_mysql_cmp(
 					/* out: 1, 0, -1, if a is greater,
 					equal, less than b, respectively */
 	int		mysql_type,	/* in: MySQL type */
+	uint		charset_number,	/* in: number of the charset */
 	unsigned char*	a,		/* in: data field */
 	unsigned int	a_length,	/* in: data field length,
 					not UNIV_SQL_NULL */
@@ -1679,6 +1686,7 @@ innobase_mysql_cmp(
 	unsigned int	b_length)	/* in: data field length,
 					not UNIV_SQL_NULL */
 {
+	CHARSET_INFO*		charset;
 	enum_field_types	mysql_tp;
 	int                     ret;
 
@@ -1695,9 +1703,27 @@ innobase_mysql_cmp(
 	case FIELD_TYPE_MEDIUM_BLOB:
 	case FIELD_TYPE_BLOB:
 	case FIELD_TYPE_LONG_BLOB:
-               // BAR TODO: Discuss with heikki.tuuri@innodb.com
-               // so that he sends CHARSET_INFO for the field to this function.
-                ret = my_strnncoll(default_charset_info,
+		/* Use the charset number to pick the right charset struct for
+		the comparison. Since the MySQL function get_charset may be
+		slow before Bar removes the mutex operation there, we first
+		look at 2 common charsets directly. */
+
+		if (charset_number == default_charset_info->number) {
+			charset = default_charset_info;
+		} else if (charset_number == my_charset_latin1.number) {
+			charset = &my_charset_latin1;
+		} else {
+			charset = get_charset(charset_number, MYF(MY_WME));
+
+			if (charset == NULL) {
+				fprintf(stderr,
+"InnoDB: fatal error: InnoDB needs charset %lu for doing a comparison,\n"
+"InnoDB: but MySQL cannot find that charset.\n", (ulong)charset_number);
+				ut_a(0);
+			}
+		}
+
+                ret = my_strnncoll(charset,
                                   a, a_length,
                                   b, b_length);
 		if (ret < 0) {
@@ -1724,9 +1750,9 @@ get_innobase_type_from_mysql_type(
 			/* out: DATA_BINARY, DATA_VARCHAR, ... */
 	Field*	field)	/* in: MySQL field */
 {
-	/* The following asserts check that the MySQL type code fits in
-	8 bits: this is used in ibuf and also when DATA_NOT_NULL is
-	ORed to the type */
+	/* The following asserts try to check that the MySQL type code fits in
+	8 bits: this is used in ibuf and also when DATA_NOT_NULL is ORed to
+	the type */
 
 	DBUG_ASSERT((ulint)FIELD_TYPE_STRING < 256);
 	DBUG_ASSERT((ulint)FIELD_TYPE_VAR_STRING < 256);
@@ -1741,8 +1767,8 @@ get_innobase_type_from_mysql_type(
 
 						return(DATA_BINARY);
 					} else if (strcmp(
-						   default_charset_info->name,
-							"latin1") == 0) {
+						  field->charset()->name,
+						 "latin1_swedish_ci") == 0) {
 						return(DATA_VARCHAR);
 					} else {
 						return(DATA_VARMYSQL);
@@ -1751,8 +1777,8 @@ get_innobase_type_from_mysql_type(
 
 						return(DATA_FIXBINARY);
 					} else if (strcmp(
-						   default_charset_info->name,
-							"latin1") == 0) {
+						   field->charset()->name,
+						   "latin1_swedish_ci") == 0) {
 						return(DATA_CHAR);
 					} else {
 						return(DATA_MYSQL);
@@ -3237,7 +3263,7 @@ create_table_def(
   	ulint		nulls_allowed;
 	ulint		unsigned_type;
 	ulint		binary_type;
-	ulint		nonlatin1_type;
+	ulint		charset_no;
   	ulint		i;
 
   	DBUG_ENTER("create_table_def");
@@ -3266,24 +3292,28 @@ create_table_def(
 			unsigned_type = 0;
 		}
 
-		if (col_type == DATA_BLOB
-		    && strcmp(default_charset_info->name, "latin1") != 0) {
-			nonlatin1_type = DATA_NONLATIN1;
-		} else {
-		        nonlatin1_type = 0;
-		}
-
 		if (field->binary()) {
 			binary_type = DATA_BINARY_TYPE;
-		        nonlatin1_type = 0;
 		} else {
 			binary_type = 0;
 		}
 
+		charset_no = 0;	
+
+		if (dtype_is_string_type(col_type)) {
+
+			charset_no = (ulint)field->charset()->number;
+
+			ut_a(charset_no < 256); /* in ut0type.h we assume that
+						the number fits in one byte */
+		}
+
 		dict_mem_table_add_col(table, (char*) field->field_name,
-					col_type, (ulint)field->type()
+					col_type, dtype_form_prtype( 
+					(ulint)field->type()
 					| nulls_allowed | unsigned_type
-					| nonlatin1_type | binary_type,
+					| binary_type,
+					+ charset_no),
 					field->pack_length(), 0);
 	}
 
@@ -3467,7 +3497,7 @@ ha_innobase::create(
 		/* The limit probably should be REC_MAX_N_FIELDS - 3 = 1020,
 		but we play safe here */
 
-	     DBUG_RETURN(HA_ERR_TO_BIG_ROW);
+	        DBUG_RETURN(HA_ERR_TO_BIG_ROW);
 	} 
 
 	/* Get the transaction associated with the current thd, or create one
@@ -3681,7 +3711,7 @@ ha_innobase::delete_table(
 	int	error;
 	trx_t*	parent_trx;
 	trx_t*	trx;
-	THD	*thd= current_thd;
+	THD     *thd= current_thd;
 	char	norm_name[1000];
 
  	DBUG_ENTER("ha_innobase::delete_table");
@@ -4408,7 +4438,7 @@ ha_innobase::get_foreign_key_create_info(void)
         prebuilt->trx->op_info = (char*)"";
 
   	return(str);
-}
+}			
 
 /***********************************************************************
 Checks if a table is referenced by a foreign key. The MySQL manual states that
@@ -4649,10 +4679,10 @@ ha_innobase::external_lock(
 		if (trx->isolation_level == TRX_ISO_SERIALIZABLE
 		    && prebuilt->select_lock_type == LOCK_NONE
 		    && (thd->options
-				 & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN))) {
+				& (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN))) {
 
-		    	/* To get serializable execution, we let InnoDB
-		    	conceptually add 'LOCK IN SHARE MODE' to all SELECTs
+			/* To get serializable execution, we let InnoDB
+			conceptually add 'LOCK IN SHARE MODE' to all SELECTs
 			which otherwise would have been consistent reads. An
 			exception is consistent reads in the AUTOCOMMIT=1 mode:
 			we know that they are read-only transactions, and they
diff --git a/sql/item.cc b/sql/item.cc
index eb2550fdbcc..5aaeffff5d2 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -969,8 +969,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,
@@ -1613,9 +1615,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 4d081905e0d..b06bc85d573 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);
@@ -413,6 +419,7 @@ public:
   int save_in_field(Field *field, bool no_conversions);
   bool basic_const_item() const { return 1; }
   Item *new_item() { return new Item_int(name,value,max_length); }
+  void cleanup() { fixed= 1; } // to prevent drop fixed flag
   void print(String *str);
 };
 
@@ -900,6 +907,8 @@ public:
   enum Type type() const { return CACHE_ITEM; }
   static Item_cache* get_cache(Item_result type);
   table_map used_tables() const { return used_table_map; }
+  virtual void keep_array() {}
+  void cleanup() { fixed= 1; } // to prevent drop fixed flag
   void print(String *str);
 };
 
@@ -952,8 +961,10 @@ class Item_cache_row: public Item_cache
 {
   Item_cache  **values;
   uint item_count;
+  bool save_array;
 public:
-  Item_cache_row(): Item_cache(), values(0), item_count(2) {}
+  Item_cache_row()
+    :Item_cache(), values(0), item_count(2), save_array(0) {}
   
   /*
     'allocate' used only in row transformer, to preallocate space for row 
@@ -994,10 +1005,16 @@ public:
   bool check_cols(uint c);
   bool null_inside();
   void bring_value();
+  void keep_array() { save_array= 1; }
   void cleanup()
   {
+    DBUG_ENTER("Item_cache_row::cleanup");
     Item_cache::cleanup();
-    values= 0;
+    if (save_array)
+      bzero(values, item_count*sizeof(Item**));
+    else
+      values= 0;
+    DBUG_VOID_RETURN;
   }
 };
 
@@ -1023,8 +1040,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 1bba934cf8f..f0bc73e9501 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -500,7 +500,6 @@ bool Item_in_optimizer::fix_left(THD *thd,
 }
 
 
-
 bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
 				   Item ** ref)
 {
@@ -526,6 +525,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
   return 0;
 }
 
+
 longlong Item_in_optimizer::val_int()
 {
   cache->store(args[0]);
@@ -539,18 +539,38 @@ longlong Item_in_optimizer::val_int()
   return tmp;
 }
 
+
+void Item_in_optimizer::keep_top_level_cache()
+{
+  cache->keep_array();
+  save_cache= 1;
+}
+
+
+void Item_in_optimizer::cleanup()
+{
+  DBUG_ENTER("Item_in_optimizer::cleanup");
+  Item_bool_func::cleanup();
+  if (!save_cache)
+    cache= 0;
+  DBUG_VOID_RETURN;
+}
+
+
 bool Item_in_optimizer::is_null()
 {
   cache->store(args[0]);
   return (null_value= (cache->null_value || args[1]->is_null()));
 }
 
+
 longlong Item_func_eq::val_int()
 {
   int value= cmp.compare();
   return value == 0 ? 1 : 0;
 }
 
+
 /* Same as Item_func_eq, but NULL = NULL */
 
 void Item_func_equal::fix_length_and_dec()
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 3c70a50502a..9d39ddf4e76 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -93,9 +93,10 @@ class Item_in_optimizer: public Item_bool_func
 {
 protected:
   Item_cache *cache;
+  bool save_cache;
 public:
   Item_in_optimizer(Item *a, Item_in_subselect *b):
-    Item_bool_func(a, (Item *)b), cache(0) {}
+    Item_bool_func(a, (Item *)b), cache(0), save_cache(0) {}
   bool fix_fields(THD *, struct st_table_list *, Item **);
   bool fix_left(THD *thd, struct st_table_list *tables, Item **ref);
   bool is_null();
@@ -107,8 +108,10 @@ 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; }
+  void keep_top_level_cache();
 };
 
 class Comp_creator
@@ -209,9 +212,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;
   }
 };
 
@@ -720,10 +725,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..3890e7c6de5 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);
@@ -1008,7 +1009,11 @@ public:
       table->fulltext_searched=0;
     }
     if (concat)
+    {
       delete concat;
+      concat= 0;
+    }
+    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..bfe41726f72 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,10 +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,
-				       this);
+    engine= new subselect_union_engine(unit, result, this);
   else
     engine= new subselect_single_select_engine(select_lex, result, this);
   DBUG_VOID_RETURN;
@@ -65,8 +65,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 +103,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 +267,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 +306,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(stmt, &backup);
     return RES_REDUCE;
   }
   return RES_OK;
+  
+err:
+  if (stmt)
+    thd->restore_backup_item_arena(stmt, &backup);
+  return RES_ERROR;
 }
 
 void Item_singlerow_subselect::store(uint i, Item *item)
@@ -550,15 +591,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 +615,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 +645,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 +658,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 +676,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 +712,17 @@ 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);
+    /*
+      AND and comparison functions can't be changed during fix_fields()
+      we can assign select_lex->having here, and pass 0 as last
+      argument (reference) to fix_fields()
+    */
+    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, 0))
     {
       select_lex->having_fix_field= 0;
-      DBUG_RETURN(RES_ERROR);
+      goto err;
     }
     select_lex->having_fix_field= 0;
   }
@@ -687,39 +739,56 @@ 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);
+	/*
+	  Item_is_not_null_test can't be changed during fix_fields()
+	  we can assign select_lex->having here, and pass 0 as last
+	  argument (reference) to fix_fields()
+	*/
+	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,
-				     &join->having))
+	if (join->having->fix_fields(thd, join->tables_list, 0))
 	{
 	  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);
+      /*
+	AND can't be changed during fix_fields()
+	we can assign select_lex->having here, and pass 0 as last
+	argument (reference) to fix_fields()
+      */
+      select_lex->where= join->conds= and_items(join->conds, item);
+      if (join->conds->fix_fields(thd, join->tables_list, 0))
+	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>"));
+	/*
+	  comparison functions can't be changed during fix_fields()
+	  we can assign select_lex->having here, and pass 0 as last
+	  argument (reference) to fix_fields()
+	*/
+	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,
-				     &join->having))
+	if (join->having->fix_fields(thd, join->tables_list,
+				     0))
 	{
 	  select_lex->having_fix_field= 0;
-	  DBUG_RETURN(RES_ERROR);
+	  goto err;
 	}
 	select_lex->having_fix_field= 0;
       }
@@ -730,18 +799,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->restore_backup_item_arena(stmt, &backup);
 	DBUG_RETURN(RES_REDUCE);
       }
     }
   }
+
+ok:
+  if (stmt)
+    thd->restore_backup_item_arena(stmt, &backup);
   DBUG_RETURN(RES_OK);
+
+err:
+  if (stmt)
+    thd->restore_backup_item_arena(stmt, &backup);
+  DBUG_RETURN(RES_ERROR);
 }
 
 
@@ -750,15 +830,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 +855,82 @@ 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;
+
+    // we will refer to apper level cache array => we have to save it in PS
+    optimizer->keep_top_level_cache();
+
+    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);
+    /*
+      AND can't be changed during fix_fields()
+      we can assign select_lex->having here, and pass 0 as last
+      argument (reference) to fix_fields()
+    */
+    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, 0))
     {
       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);
+    /*
+      AND can't be changed during fix_fields()
+      we can assign select_lex->having here, and pass 0 as last
+      argument (reference) to fix_fields()
+    */
+    select_lex->where= join->conds= and_items(join->conds, item);
+    if (join->conds->fix_fields(thd, join->tables_list, 0))
+      goto err;
   }
+  if (stmt)
+    thd->restore_backup_item_arena(stmt, &backup);
   DBUG_RETURN(RES_OK);
+
+err:
+  if (stmt)
+    thd->restore_backup_item_arena(stmt, &backup);
+  DBUG_RETURN(RES_ERROR);
 }
 
 
@@ -894,13 +1002,31 @@ 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;
+  join= 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 0848886d6d8..11c95100db5 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 54b8c486673..f5031f926af 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -350,6 +350,11 @@ inline THD *_current_thd(void)
 #include "sql_udf.h"
 #include "item.h"
 typedef Comp_creator* (*chooser_compare_func_creator)(bool invert);
+/* sql_parse.cc */
+void free_items(Item *item);
+void cleanup_items(Item *item);
+class THD;
+void close_thread_tables(THD *thd, bool locked=0, bool skip_derived=0);
 #include "sql_class.h"
 #include "opt_range.h"
 
@@ -411,7 +416,6 @@ bool mysql_rename_tables(THD *thd, TABLE_LIST *table_list);
 bool mysql_change_db(THD *thd,const char *name);
 void mysql_parse(THD *thd,char *inBuf,uint length);
 bool is_update_query(enum enum_sql_command command);
-void free_items(Item *item);
 bool alloc_query(THD *thd, char *packet, ulong packet_length);
 void mysql_init_select(LEX *lex);
 void mysql_init_query(THD *thd);
@@ -688,7 +692,6 @@ bool rm_temporary_table(enum db_type base, char *path);
 void free_io_cache(TABLE *entry);
 void intern_close_table(TABLE *entry);
 bool close_thread_table(THD *thd, TABLE **table_ptr);
-void close_thread_tables(THD *thd, bool locked=0, bool skip_derived=0);
 void close_temporary_tables(THD *thd);
 TABLE_LIST * find_table_in_list(TABLE_LIST *table,
 				const char *db_name, const char *table_name);
@@ -766,9 +769,6 @@ uint check_word(TYPELIB *lib, const char *val, const char *end,
 
 bool is_keyword(const char *name, uint len);
 
-/* sql_parse.cc */
-void free_items(Item *item);
-void cleanup_items(Item *item);
 
 #define MY_DB_OPT_FILE "db.opt"
 bool load_db_opt(THD *thd, const char *path, HA_CREATE_INFO *create);
@@ -779,7 +779,8 @@ bool load_db_opt(THD *thd, const char *path, HA_CREATE_INFO *create);
 
 extern time_t start_time;
 extern char *mysql_data_home,server_version[SERVER_VERSION_LENGTH],
-	    mysql_real_data_home[], *opt_mysql_tmpdir, mysql_charsets_dir[];
+	    mysql_real_data_home[], *opt_mysql_tmpdir, mysql_charsets_dir[],
+            opt_ft_boolean_syntax[sizeof(ft_boolean_syntax)];
 #define mysql_tmpdir (my_tmpdir(&mysql_tmpdir_list))
 extern MY_TMPDIR mysql_tmpdir_list;
 extern const char *command_name[];
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 4feadd8ac20..af61d624464 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -183,9 +183,6 @@ inline void reset_floating_point_exceptions()
 #else
 #include <my_pthread.h>			// For thr_setconcurency()
 #endif
-#if defined(HAVE_GETRLIMIT) && defined(RLIMIT_NOFILE) && !defined(HAVE_mit_thread)
-#define SET_RLIMIT_NOFILE
-#endif
 
 #ifdef SOLARIS
 extern "C" int gethostname(char *name, int namelen);
@@ -318,7 +315,8 @@ char* log_error_file_ptr= log_error_file;
 char mysql_real_data_home[FN_REFLEN],
      language[LIBLEN],reg_ext[FN_EXTLEN], mysql_charsets_dir[FN_REFLEN],
      max_sort_char,*mysqld_user,*mysqld_chroot, *opt_init_file,
-     *opt_init_connect, *opt_init_slave;
+     *opt_init_connect, *opt_init_slave,
+     opt_ft_boolean_syntax[sizeof(ft_boolean_syntax)];
 
 const char *opt_date_time_formats[3];
 
@@ -492,9 +490,6 @@ extern "C" pthread_handler_decl(handle_connections_namedpipes,arg);
 static pthread_handler_decl(handle_connections_shared_memory,arg);
 #endif
 extern "C" pthread_handler_decl(handle_slave,arg);
-#ifdef SET_RLIMIT_NOFILE
-static uint set_maximum_open_files(uint max_file_limit);
-#endif
 static ulong find_bit_type(const char *x, TYPELIB *bit_lib);
 static void clean_up(bool print_message);
 static void clean_up_mutexes(void);
@@ -919,6 +914,7 @@ void clean_up(bool print_message)
 #ifdef USE_RAID
   end_raid();
 #endif
+  my_free_open_file_info();
   my_free((char*) global_system_variables.date_format,
 	  MYF(MY_ALLOW_ZERO_PTR));
   my_free((char*) global_system_variables.time_format,
@@ -2108,28 +2104,32 @@ static int init_common_variables(const char *conf_file_name, int argc,
   DBUG_PRINT("info",("%s  Ver %s for %s on %s\n",my_progname,
 		     server_version, SYSTEM_TYPE,MACHINE_TYPE));
 
-#if defined( SET_RLIMIT_NOFILE) || defined( OS2)
   /* connections and databases needs lots of files */
   {
-    uint wanted_files=10+(uint) max(max_connections*5,
-				    max_connections+table_cache_size*2);
+    uint files, wanted_files;
+
+    wanted_files= 10+(uint) max(max_connections*5,
+				 max_connections+table_cache_size*2);
     set_if_bigger(wanted_files, open_files_limit);
-    // Note that some system returns 0 if we succeed here:
-    uint files=set_maximum_open_files(wanted_files);
-    if (files && files < wanted_files && ! open_files_limit)
+    files= my_set_max_open_files(wanted_files);
+
+    if (files < wanted_files)
     {
-      max_connections=	(ulong) min((files-10),max_connections);
-      table_cache_size= (ulong) max((files-10-max_connections)/2,64);
-      DBUG_PRINT("warning",
-		 ("Changed limits: max_connections: %ld  table_cache: %ld",
-		  max_connections,table_cache_size));
-      sql_print_error("Warning: Changed limits: max_connections: %ld  table_cache: %ld",max_connections,table_cache_size);
+      if (!open_files_limit)
+      {
+	max_connections=	(ulong) min((files-10),max_connections);
+	table_cache_size= (ulong) max((files-10-max_connections)/2,64);
+	DBUG_PRINT("warning",
+		   ("Changed limits: max_open_files: %u  max_connections: %ld  table_cache: %ld",
+		    files, max_connections, table_cache_size));
+	sql_print_error("Warning: Changed limits: max_open_files: %u  max_connections: %ld  table_cache: %ld",
+			files, max_connections, table_cache_size);
+      }
+      else
+	sql_print_error("Warning: Could not increase number of max_open_files to more than %u (request: %u)", files, wanted_files);
     }
     open_files_limit= files;
   }
-#else
-  open_files_limit= 0;		/* Can't set or detect limit */
-#endif
   unireg_init(opt_specialflag); /* Set up extern variabels */
   if (init_errmessage())	/* Read error messages from file */
     return 1;
@@ -3603,7 +3603,7 @@ enum options_mysqld
   OPT_BACK_LOG, OPT_BINLOG_CACHE_SIZE,
   OPT_CONNECT_TIMEOUT, OPT_DELAYED_INSERT_TIMEOUT,
   OPT_DELAYED_INSERT_LIMIT, OPT_DELAYED_QUEUE_SIZE,
-  OPT_FLUSH_TIME, OPT_FT_MIN_WORD_LEN,
+  OPT_FLUSH_TIME, OPT_FT_MIN_WORD_LEN, OPT_FT_BOOLEAN_SYNTAX,
   OPT_FT_MAX_WORD_LEN, OPT_FT_QUERY_EXPANSION_LIMIT, OPT_FT_STOPWORD_FILE,
   OPT_INTERACTIVE_TIMEOUT, OPT_JOIN_BUFF_SIZE,
   OPT_KEY_BUFFER_SIZE, OPT_KEY_CACHE_BLOCK_SIZE,
@@ -3926,7 +3926,8 @@ Disable with --skip-bdb (will save memory).",
    0, 0, 0, 0},
   {"master-password", OPT_MASTER_PASSWORD,
    "The password the slave thread will authenticate with when connecting to the master. If not set, an empty password is assumed.The value in master.info will take precedence if it can be read.",
-   0, 0, 0, GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
+   (gptr*)&master_password, (gptr*)&master_password, 0,
+   GET_STR, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
   {"master-port", OPT_MASTER_PORT,
    "The port the master is listening on. If not set, the compiled setting of MYSQL_PORT is assumed. If you have not tinkered with configure options, this should be 3306. The value in master.info will take precedence if it can be read.",
    (gptr*) &master_port, (gptr*) &master_port, 0, GET_UINT, REQUIRED_ARG,
@@ -4257,6 +4258,10 @@ replicating a LOAD DATA INFILE command.",
     "A dedicated thread is created to flush all tables at the given interval.",
     (gptr*) &flush_time, (gptr*) &flush_time, 0, GET_ULONG, REQUIRED_ARG,
     FLUSH_TIME, 0, LONG_TIMEOUT, 0, 1, 0},
+  { "ft_boolean_syntax", OPT_FT_BOOLEAN_SYNTAX,
+    "List of operators for MATCH ... AGAINST ( ... IN BOOLEAN MODE)",
+    0, 0, 0, GET_STR,
+    REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
   { "ft_min_word_len", OPT_FT_MIN_WORD_LEN,
     "The minimum length of the word to be included in a FULLTEXT index. Note: FULLTEXT indexes must be rebuilt after changing this variable.",
     (gptr*) &ft_min_word_len, (gptr*) &ft_min_word_len, 0, GET_ULONG,
@@ -4506,7 +4511,7 @@ The minimum value for this variable is 4096.",
   {"open_files_limit", OPT_OPEN_FILES_LIMIT,
    "If this is not 0, then mysqld will use this value to reserve file descriptors to use with setrlimit(). If this value is 0 then mysqld will reserve max_connections*5 or max_connections + table_cache*2 (whichever is larger) number of files.",
    (gptr*) &open_files_limit, (gptr*) &open_files_limit, 0, GET_ULONG,
-   REQUIRED_ARG, 0, 0, 65535, 0, 1, 0},
+   REQUIRED_ARG, 0, 0, OS_FILE_LIMIT, 0, 1, 0},
    {"preload_buffer_size", OPT_PRELOAD_BUFFER_SIZE,
     "The size of the buffer that is allocated when preloading indexes",
     (gptr*) &global_system_variables.preload_buff_size,
@@ -5435,8 +5440,8 @@ get_one_option(int optid, const struct my_option *opt __attribute__((unused)),
     break;
   case OPT_STORAGE_ENGINE:
   {
-    if ((enum db_type)((global_system_variables.table_type= 
-    	  ha_resolve_by_name(argument, strlen(argument)))) == DB_TYPE_UNKNOWN)
+    if ((enum db_type)((global_system_variables.table_type=
+	  ha_resolve_by_name(argument, strlen(argument)))) == DB_TYPE_UNKNOWN)
     {
       fprintf(stderr,"Unknown table type: %s\n",argument);
       exit(1);
@@ -5608,8 +5613,13 @@ get_one_option(int optid, const struct my_option *opt __attribute__((unused)),
     global_system_variables.sql_mode= fix_sql_mode(global_system_variables.
 						   sql_mode);
   }
-  case OPT_MASTER_PASSWORD:
-    master_password=argument;
+  case OPT_FT_BOOLEAN_SYNTAX:
+    if (ft_boolean_check_syntax_string(argument))
+    {
+      fprintf(stderr, "Invalid ft-boolean-syntax string: %s\n", argument);
+      exit(1);
+    }
+    strmake(opt_ft_boolean_syntax, argument, sizeof(ft_boolean_syntax)-1);
     break;
   case OPT_SKIP_SAFEMALLOC:
 #ifdef SAFEMALLOC
@@ -5658,6 +5668,8 @@ static void get_options(int argc,char **argv)
   int ho_error;
 
   my_getopt_register_get_addr(mysql_getopt_value);
+  strmake(opt_ft_boolean_syntax, ft_boolean_syntax, 
+	  sizeof(ft_boolean_syntax)-1);
   if ((ho_error=handle_options(&argc, &argv, my_long_options, get_one_option)))
     exit(ho_error);
   if (argc > 0)
@@ -5713,6 +5725,9 @@ static void get_options(int argc,char **argv)
   table_alias_charset= (lower_case_table_names ?
 			files_charset_info :
 			&my_charset_bin);
+  strmake(ft_boolean_syntax, opt_ft_boolean_syntax,
+	  sizeof(ft_boolean_syntax)-1);
+
   if (opt_short_log_format)
     opt_specialflag|= SPECIAL_SHORT_LOG_FORMAT;
   if (opt_log_queries_not_using_indexes)
@@ -5813,95 +5828,6 @@ static void fix_paths(void)
 
 
 /*
-  set how many open files we want to be able to handle
-
-  SYNOPSIS
-    set_maximum_open_files()
-    max_file_limit		Files to open
-
-  NOTES
-    The request may not fulfilled becasue of system limitations
-
-  RETURN
-    Files available to open
-*/
-
-#ifdef SET_RLIMIT_NOFILE
-
-#ifndef RLIM_INFINITY
-#define RLIM_INFINITY ((uint) 0xffffffff)
-#endif
-
-static uint set_maximum_open_files(uint max_file_limit)
-{
-  struct rlimit rlimit;
-  uint old_cur;
-  DBUG_ENTER("set_maximum_open_files");
-  DBUG_PRINT("enter",("files: %u", max_file_limit));
-
-  if (!getrlimit(RLIMIT_NOFILE,&rlimit))
-  {
-    old_cur= (uint) rlimit.rlim_cur;
-    DBUG_PRINT("info", ("rlim_cur: %u  rlim_max: %u",
-			(uint) rlimit.rlim_cur,
-			(uint) rlimit.rlim_max));
-    if (rlimit.rlim_cur >= max_file_limit ||
-	rlimit.rlim_cur == RLIM_INFINITY)
-      DBUG_RETURN(rlimit.rlim_cur);		/* purecov: inspected */
-    rlimit.rlim_cur= rlimit.rlim_max= max_file_limit;
-    if (setrlimit(RLIMIT_NOFILE,&rlimit))
-    {
-      if (global_system_variables.log_warnings)
-	sql_print_error("Warning: setrlimit couldn't increase number of open files to more than %u (request: %u)",
-			old_cur, max_file_limit); /* purecov: inspected */
-      max_file_limit= old_cur;
-    }
-    else
-    {
-      rlimit.rlim_cur= 0;			// Safety if next call fails
-      (void) getrlimit(RLIMIT_NOFILE,&rlimit);
-      DBUG_PRINT("info", ("rlim_cur: %u", (uint) rlimit.rlim_cur));
-      if ((uint) rlimit.rlim_cur < max_file_limit &&
-	  global_system_variables.log_warnings)
-	sql_print_error("Warning: setrlimit returned ok, but didn't change limits. Max open files is %u (request: %u)",
-			(uint) rlimit.rlim_cur,
-			max_file_limit); /* purecov: inspected */
-      max_file_limit= (uint) rlimit.rlim_cur;
-    }
-  }
-  DBUG_PRINT("exit",("max_file_limit: %u", max_file_limit));
-  DBUG_RETURN(max_file_limit);
-}
-#endif
-
-
-#ifdef OS2
-static uint set_maximum_open_files(uint max_file_limit)
-{
-  LONG     cbReqCount;
-  ULONG    cbCurMaxFH, cbCurMaxFH0;
-  APIRET   ulrc;
-  DBUG_ENTER("set_maximum_open_files");
-
-  // get current limit
-  cbReqCount = 0;
-  DosSetRelMaxFH( &cbReqCount, &cbCurMaxFH0);
-
-  // set new limit
-  cbReqCount = max_file_limit - cbCurMaxFH0;
-  ulrc = DosSetRelMaxFH( &cbReqCount, &cbCurMaxFH);
-  if (ulrc) {
-    sql_print_error("Warning: DosSetRelMaxFH couldn't increase number of open files to more than %d",
-		    cbCurMaxFH0);
-    cbCurMaxFH = cbCurMaxFH0;
-  }
-
-  DBUG_RETURN(cbCurMaxFH);
-}
-#endif
-
-
-/*
   Return a bitfield from a string of substrings separated by ','
   returns ~(ulong) 0 on error.
 */
diff --git a/sql/set_var.cc b/sql/set_var.cc
index 7f5cf9503bf..f3728ce9e5d 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -19,18 +19,18 @@
 
   To add a new variable, one has to do the following:
 
-  - If the variable is thread specific, add it to 'system_variables' struct.
-    If not, add it to mysqld.cc and an declaration in 'mysql_priv.h'
-  - Don't forget to initialize new fields in global_system_variables and
-     max_system_variables!
   - Use one of the 'sys_var... classes from set_var.h or write a specific
     one for the variable type.
   - Define it in the 'variable definition list' in this file.
   - If the variable should be changeable or one should be able to access it
     with @@variable_name, it should be added to the 'list of all variables'
-    list in this file.
+    list (sys_variables) in this file.
+  - If the variable is thread specific, add it to 'system_variables' struct.
+    If not, add it to mysqld.cc and an declaration in 'mysql_priv.h'
   - If the variable should be changed from the command line, add a definition
     of it in the my_option structure list in mysqld.dcc
+  - Don't forget to initialize new fields in global_system_variables and
+    max_system_variables!
   - If the variable should show up in 'show variables' add it to the
     init_vars[] struct in this file
 
@@ -73,9 +73,12 @@ TYPELIB delay_key_write_typelib=
   array_elements(delay_key_write_type_names)-1, "", delay_key_write_type_names
 };
 
-static bool sys_check_charset(THD *thd, set_var *var);
+static int sys_check_charset(THD *thd, set_var *var);
 static bool sys_update_charset(THD *thd, set_var *var);
 static void sys_set_default_charset(THD *thd, enum_var_type type);
+static int sys_check_ftb_syntax(THD *thd,  set_var *var);
+static bool sys_update_ftb_syntax(THD *thd, set_var * var);
+static void sys_default_ftb_syntax(THD *thd, enum_var_type type);
 static bool sys_update_init_connect(THD*, set_var*);
 static void sys_default_init_connect(THD*, enum_var_type type);
 static bool sys_update_init_slave(THD*, set_var*);
@@ -119,12 +122,6 @@ sys_var_str			sys_charset_system("character_set_system",
 				    sys_check_charset,
 				    sys_update_charset,
 				    sys_set_default_charset);
-sys_var_str                     sys_init_connect("init_connect", 0,
-                                                 sys_update_init_connect,
-                                                 sys_default_init_connect);
-sys_var_str                     sys_init_slave("init_slave", 0,
-                                               sys_update_init_slave,
-                                               sys_default_init_slave);
 sys_var_character_set_database	sys_character_set_database("character_set_database");
 sys_var_character_set_client  sys_character_set_client("character_set_client");
 sys_var_character_set_connection  sys_character_set_connection("character_set_connection");
@@ -150,6 +147,16 @@ sys_var_long_ptr	sys_expire_logs_days("expire_logs_days",
 					     &expire_logs_days);
 sys_var_bool_ptr	sys_flush("flush", &myisam_flush);
 sys_var_long_ptr	sys_flush_time("flush_time", &flush_time);
+sys_var_str             sys_ft_boolean_syntax("ft_boolean_syntax",
+                                         sys_check_ftb_syntax,
+                                         sys_update_ftb_syntax,
+                                         sys_default_ftb_syntax);
+sys_var_str             sys_init_connect("init_connect", 0,
+                                         sys_update_init_connect,
+                                         sys_default_init_connect);
+sys_var_str             sys_init_slave("init_slave", 0,
+                                       sys_update_init_slave,
+                                       sys_default_init_slave);
 sys_var_thd_ulong	sys_interactive_timeout("interactive_timeout",
 						&SV::net_interactive_timeout);
 sys_var_thd_ulong	sys_join_buffer_size("join_buffer_size",
@@ -318,7 +325,7 @@ sys_var_thd_ulong	sys_net_wait_timeout("wait_timeout",
 #ifdef HAVE_INNOBASE_DB
 sys_var_long_ptr        sys_innodb_max_dirty_pages_pct("innodb_max_dirty_pages_pct",
                                                         &srv_max_buf_pool_modified_pct);
-#endif 					     
+#endif
 
 /* Time/date/datetime formats */
 
@@ -450,6 +457,7 @@ sys_var *sys_variables[]=
   &sys_expire_logs_days,
   &sys_flush,
   &sys_flush_time,
+  &sys_ft_boolean_syntax,
   &sys_foreign_key_checks,
   &sys_group_concat_max_len,
   &sys_identity,
@@ -593,7 +601,7 @@ struct show_var_st init_vars[]= {
   {sys_expire_logs_days.name, (char*) &sys_expire_logs_days,        SHOW_SYS},
   {sys_flush.name,             (char*) &sys_flush,                  SHOW_SYS},
   {sys_flush_time.name,        (char*) &sys_flush_time,             SHOW_SYS},
-  {"ft_boolean_syntax",       (char*) ft_boolean_syntax,	    SHOW_CHAR},
+  {sys_ft_boolean_syntax.name,(char*) &ft_boolean_syntax,	    SHOW_CHAR},
   {"ft_min_word_len",         (char*) &ft_min_word_len,             SHOW_LONG},
   {"ft_max_word_len",         (char*) &ft_max_word_len,             SHOW_LONG},
   {"ft_query_expansion_limit",(char*) &ft_query_expansion_limit,    SHOW_LONG},
@@ -775,6 +783,18 @@ bool sys_var::check(THD *thd, set_var *var)
   return 0;
 }
 
+bool sys_var_str::check(THD *thd, set_var *var)
+{
+  int res;
+  if (!check_func)
+    return 0;
+
+  if ((res=(*check_func)(thd, var)) < 0)
+    my_error(ER_WRONG_VALUE_FOR_VAR, MYF(0), name,
+             var->value->str_value.ptr());
+  return res;
+}
+
 /*
   Functions to check and update variables
 */
@@ -837,13 +857,37 @@ static void sys_default_init_slave(THD* thd, enum_var_type type)
   update_sys_var_str(&sys_init_slave, &LOCK_sys_init_slave, 0);
 }
 
+static int sys_check_ftb_syntax(THD *thd,  set_var *var)
+{
+  if (thd->master_access & SUPER_ACL)
+    return ft_boolean_check_syntax_string(var->value->str_value.c_ptr()) ?
+      -1 : 0;
+  else
+  {
+    my_error(ER_SPECIFIC_ACCESS_DENIED_ERROR, MYF(0), "SUPER");
+    return 1;
+  }
+}
+
+static bool sys_update_ftb_syntax(THD *thd, set_var * var)
+{
+  strmake(ft_boolean_syntax, var->value->str_value.c_ptr(),
+	  sizeof(ft_boolean_syntax)-1);
+  return 0;
+}
+
+static void sys_default_ftb_syntax(THD *thd, enum_var_type type)
+{
+  strmake(ft_boolean_syntax, opt_ft_boolean_syntax,
+	  sizeof(ft_boolean_syntax)-1);
+}
 
 /*
   The following 3 functions need to be changed in 4.1 when we allow
   one to change character sets
 */
 
-static bool sys_check_charset(THD *thd, set_var *var)
+static int sys_check_charset(THD *thd, set_var *var)
 {
   return 0;
 }
@@ -1898,7 +1942,7 @@ byte *sys_var_key_cache_param::value_ptr(THD *thd, enum_var_type type,
     key_cache= &zero_key_cache;
   return (byte*) key_cache + offset ;
 }
-    
+
 
 bool sys_var_key_buffer_size::update(THD *thd, set_var *var)
 {
@@ -1993,14 +2037,14 @@ bool sys_var_key_cache_long::update(THD *thd, set_var *var)
 
   pthread_mutex_lock(&LOCK_global_system_variables);
   KEY_CACHE *key_cache= get_key_cache(base_name);
-                            
+
   if (!key_cache && !(key_cache= create_key_cache(base_name->str,
 				                  base_name->length)))
   {
     error= 1;
     goto end;
   }
- 
+
   /*
     Abort if some other thread is changing the key cache
     TODO: This should be changed so that we wait until the previous
diff --git a/sql/set_var.h b/sql/set_var.h
index 85871c90ebb..9087a3e023e 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -35,7 +35,7 @@ enum enum_var_type
   OPT_DEFAULT, OPT_SESSION, OPT_GLOBAL
 };
 
-typedef bool (*sys_check_func)(THD *,  set_var *);
+typedef int (*sys_check_func)(THD *,  set_var *);
 typedef bool (*sys_update_func)(THD *, set_var *);
 typedef void (*sys_after_update_func)(THD *,enum_var_type);
 typedef void (*sys_set_default_func)(THD *, enum_var_type);
@@ -143,10 +143,7 @@ public:
     :sys_var(name_arg), check_func(check_func_arg),
     update_func(update_func_arg),set_default_func(set_default_func_arg)
   {}
-  bool check(THD *thd, set_var *var)
-  {
-    return check_func ? (*check_func)(thd, var) : 0;
-  }
+  bool check(THD *thd, set_var *var);
   bool update(THD *thd, set_var *var)
   {
     return (*update_func)(thd, var);
diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt
index ed9b7c3931a..49d74165dbd 100644
--- a/sql/share/english/errmsg.txt
+++ b/sql/share/english/errmsg.txt
@@ -72,9 +72,9 @@ character-set=latin1
 "Not unique table/alias: '%-.64s'",
 "Invalid default value for '%-.64s'",
 "Multiple primary key defined",
-"Too many keys specified. Max %d keys allowed",
+"Too many keys specified; max %d keys allowed",
 "Too many key parts specified. Max %d parts allowed",
-"Specified key was too long. Max key length is %d",
+"Specified key was too long; max key length is %d bytes",
 "Key column '%-.64s' doesn't exist in table",
 "BLOB column '%-.64s' can't be used in key specification with the used table type",
 "Too big column length for column '%-.64s' (max = %d). Use BLOB instead",
diff --git a/sql/share/russian/errmsg.txt b/sql/share/russian/errmsg.txt
index 7bfd2264178..4282c5326af 100644
--- a/sql/share/russian/errmsg.txt
+++ b/sql/share/russian/errmsg.txt
@@ -76,7 +76,7 @@ character-set=koi8r
 "Указано несколько первичных ключей",
 "Указано слишком много ключей. Разрешается указывать не более %d ключей",
 "Указано слишком много частей составного ключа. Разрешается указывать не более %d частей",
-"Указан слишком длинный ключ. Максимальная длина ключа составляет %d",
+"Указан слишком длинный ключ. Максимальная длина ключа составляет %d байт",
 "Ключевой столбец '%-.64s' в таблице не существует",
 "Столбец типа BLOB '%-.64s' не может быть использован как значение ключа в таблице такого типа",
 "Слишком большая длина столбца '%-.64s' (максимум = %d). Используйте тип BLOB вместо текущего",
diff --git a/sql/share/ukrainian/errmsg.txt b/sql/share/ukrainian/errmsg.txt
index 722e96ec5a7..fa312fcc4b4 100644
--- a/sql/share/ukrainian/errmsg.txt
+++ b/sql/share/ukrainian/errmsg.txt
@@ -79,7 +79,7 @@ character-set=koi8u
 "Первинного ключа визначено неодноразово",
 "Забагато ключ╕в зазначено. Дозволено не б╕льше %d ключ╕в",
 "Забагато частин ключа зазначено. Дозволено не б╕льше %d частин",
-"Зазначений ключ задовгий. Найб╕льша довжина ключа %d",
+"Зазначений ключ задовгий. Найб╕льша довжина ключа %d байт╕в",
 "Ключовий стовбець '%-.64s' не ╕сну╓ у таблиц╕",
 "BLOB стовбець '%-.64s' не може бути використаний у визначенн╕ ключа в цьому тип╕ таблиц╕",
 "Задовга довжина стовбця '%-.64s' (max = %d). Використайте тип BLOB",
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index b11c71b6ff7..5cfe6f7a870 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -2080,6 +2080,14 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
 {
   if (!wild_num)
     return 0;
+  Statement *stmt= thd->current_statement, backup;
+
+  /*
+    If we are in preparing prepared statement phase then we have change
+    temporary mem_root to statement mem root to save changes of SELECT list
+  */
+  if (stmt)
+    thd->set_n_backup_item_arena(stmt, &backup);
   reg2 Item *item;
   List_iterator<Item> it(fields);
   while ( wild_num && (item= it++))
@@ -2091,7 +2099,11 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
       uint elem= fields.elements;
       if (insert_fields(thd,tables,((Item_field*) item)->db_name,
 			((Item_field*) item)->table_name, &it))
+      {
+	if (stmt)
+	  thd->restore_backup_item_arena(stmt, &backup);
 	return (-1);
+      }
       if (sum_func_list)
       {
 	/*
@@ -2104,6 +2116,8 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
       wild_num--;
     }
   }
+  if (stmt)
+      thd->restore_backup_item_arena(stmt, &backup);
   return 0;
 }
 
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index e7f867ccd61..9ed604033ab 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)
@@ -1247,6 +1247,27 @@ void Statement::set_statement(Statement *stmt)
 }
 
 
+void Statement::set_n_backup_item_arena(Statement *set, Statement *backup)
+{
+  backup->set_item_arena(this);
+  set_item_arena(set);
+}
+
+
+void Statement::restore_backup_item_arena(Statement *set, Statement *backup)
+{
+  set->set_item_arena(this);
+  set_item_arena(backup);
+  // reset backup mem_root to avoid its freeing
+  init_alloc_root(&backup->mem_root, 0, 0);
+}
+
+void Statement::set_item_arena(Statement *set)
+{
+  mem_root= set->mem_root;
+  free_list= set->free_list;
+}
+
 Statement::~Statement()
 {
   free_root(&mem_root, MYF(0));
diff --git a/sql/sql_class.h b/sql/sql_class.h
index b0899428f32..6816d141dac 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,32 @@ public:
   void set_statement(Statement *stmt);
   /* return class type */
   virtual Type type() const;
+
+  inline gptr alloc(unsigned int size) { return alloc_root(&mem_root,size); }
+  inline gptr calloc(unsigned int size)
+  {
+    gptr ptr;
+    if ((ptr=alloc_root(&mem_root,size)))
+      bzero((char*) ptr,size);
+    return ptr;
+  }
+  inline char *strdup(const char *str)
+  { return strdup_root(&mem_root,str); }
+  inline char *strmake(const char *str, uint size)
+  { return strmake_root(&mem_root,str,size); }
+  inline char *memdup(const char *str, uint size)
+  { return memdup_root(&mem_root,str,size); }
+  inline char *memdup_w_gap(const char *str, uint size, uint gap)
+  {
+    gptr ptr;
+    if ((ptr=alloc_root(&mem_root,size+gap)))
+      memcpy(ptr,str,size);
+    return ptr;
+  }
+
+  void set_n_backup_item_arena(Statement *set, Statement *backup);
+  void restore_backup_item_arena(Statement *set, Statement *backup);
+  void Statement::set_item_arena(Statement *set);
 };
 
 
@@ -690,6 +716,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
   */
@@ -850,34 +880,14 @@ public:
     return 0;
 #endif
   }
-  inline gptr alloc(unsigned int size) { return alloc_root(&mem_root,size); }
-  inline gptr calloc(unsigned int size)
-  {
-    gptr ptr;
-    if ((ptr=alloc_root(&mem_root,size)))
-      bzero((char*) ptr,size);
-    return ptr;
-  }
-  inline char *strdup(const char *str)
-  { return strdup_root(&mem_root,str); }
-  inline char *strmake(const char *str, uint size)
-  { return strmake_root(&mem_root,str,size); }
-  inline char *memdup(const char *str, uint size)
-  { return memdup_root(&mem_root,str,size); }
-  inline char *memdup_w_gap(const char *str, uint size, uint gap)
-  {
-    gptr ptr;
-    if ((ptr=alloc_root(&mem_root,size+gap)))
-      memcpy(ptr,str,size);
-    return ptr;
-  }
-  bool convert_string(LEX_STRING *to, CHARSET_INFO *to_cs,
-		      const char *from, uint from_length,
-		      CHARSET_INFO *from_cs);
   inline gptr trans_alloc(unsigned int size) 
   { 
     return alloc_root(&transaction.mem_root,size);
   }
+
+  bool convert_string(LEX_STRING *to, CHARSET_INFO *to_cs,
+		      const char *from, uint from_length,
+		      CHARSET_INFO *from_cs);
   void add_changed_table(TABLE *table);
   void add_changed_table(const char *key, long key_length);
   CHANGED_TABLE_LIST * changed_table_dup(const char *key, long key_length);
@@ -900,6 +910,33 @@ public:
   }
   inline CHARSET_INFO *charset() { return variables.character_set_client; }
   void update_charset();
+
+  inline void allocate_temporary_memory_pool_for_ps_preparing()
+  {
+    DBUG_ASSERT(current_statement!=0);
+    /*
+      We do not want to have in PS memory all that junk,
+      which will be created by preparation => substitute memory
+      from original thread pool.
+
+      We know that PS memory pool is now copied to THD, we move it back
+      to allow some code use it.
+    */
+    current_statement->set_item_arena(this);
+    init_sql_alloc(&mem_root,
+		   variables.query_alloc_block_size,
+		   variables.query_prealloc_size);
+    free_list= 0;
+  }
+  inline void free_temporary_memory_pool_for_ps_preparing()
+  {
+    DBUG_ASSERT(current_statement!=0);
+    cleanup_items(current_statement->free_list);
+    free_items(free_list);
+    close_thread_tables(this); // to close derived tables
+    free_root(&mem_root, MYF(0));
+    set_item_arena(current_statement);
+  }
 };
 
 /* Flags for the THD::system_thread (bitmap) variable */
diff --git a/sql/sql_db.cc b/sql/sql_db.cc
index 0323e90a166..7bf1268597b 100644
--- a/sql/sql_db.cc
+++ b/sql/sql_db.cc
@@ -58,9 +58,9 @@ static bool write_db_opt(THD *thd, const char *path, HA_CREATE_INFO *create)
   if ((file=my_create(path, CREATE_MODE,O_RDWR | O_TRUNC,MYF(MY_WME))) >= 0)
   {
     ulong length;
-    CHARSET_INFO *cs= (create && create->default_table_charset) ? 
-		     create->default_table_charset :
-		     thd->variables.collation_server;
+    CHARSET_INFO *cs= ((create && create->default_table_charset) ? 
+		       create->default_table_charset :
+		       thd->variables.collation_server);
     length= my_sprintf(buf,(buf,
 			    "default-character-set=%s\ndefault-collation=%s\n",
 			    cs->csname,cs->name));
@@ -116,9 +116,10 @@ bool load_db_opt(THD *thd, const char *path, HA_CREATE_INFO *create)
       {
 	if (!strncmp(buf,"default-character-set", (pos-buf)))
 	{
-	  if (!(create->default_table_charset= get_charset_by_csname(pos+1, 
-								    MY_CS_PRIMARY,
-								    MYF(0))))
+	  if (!(create->default_table_charset=
+		get_charset_by_csname(pos+1, 
+				      MY_CS_PRIMARY,
+				      MYF(0))))
 	  {
 	    sql_print_error(ER(ER_UNKNOWN_CHARACTER_SET),pos+1);
 	  }
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index d93134a3a0c..ce7aa4d02db 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -1500,11 +1500,17 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num)
 {
   if (ref_pointer_array)
     return 0;
+
+  /*
+    We have to create array in prepared statement memory if it is
+    prepared statement
+  */
+  Statement *stmt= thd->current_statement ? thd->current_statement : thd;
   return (ref_pointer_array= 
-	  (Item **)thd->alloc(sizeof(Item*) *
-			      (item_list.elements +
-			       select_n_having_items +
-			       order_group_num)* 5)) == 0;
+	  (Item **)stmt->alloc(sizeof(Item*) *
+			       (item_list.elements +
+				select_n_having_items +
+				order_group_num)* 5)) == 0;
 }
 
 
@@ -1629,7 +1635,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 3f56be18c4a..17cccd75697 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -355,6 +355,8 @@ public:
   int prepare(THD *thd, select_result *result, ulong additional_options);
   int exec();
   int cleanup();
+  inline void unclean() { cleaned= 0; }
+  void reinit_exec_mechanism();
 
   bool check_updateable(char *db, char *table);
   void print(String *str);
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index 3e51844e8cf..7c2913bc495 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -621,8 +621,18 @@ static bool mysql_test_insert_fields(Prepared_statement *stmt,
       (grant_option && check_grant(thd,privilege,table_list,0,0)))
     DBUG_RETURN(1); 
 #endif
+
+  /* 
+     open temporary memory pool for temporary data allocated by derived
+     tables & preparation procedure
+  */
+  thd->allocate_temporary_memory_pool_for_ps_preparing();
   if (open_and_lock_tables(thd, table_list))
-    DBUG_RETURN(1); 
+  {
+    thd->free_temporary_memory_pool_for_ps_preparing();
+    DBUG_RETURN(1);
+  }
+
   table= table_list->table;
 
   if ((values= its++))
@@ -631,7 +641,11 @@ static bool mysql_test_insert_fields(Prepared_statement *stmt,
     ulong counter= 0;
     
     if (check_insert_fields(thd,table,fields,*values,1))
+    {
+      thd->free_temporary_memory_pool_for_ps_preparing();
       DBUG_RETURN(1);
+    }
+    thd->free_temporary_memory_pool_for_ps_preparing();
 
     value_count= values->elements;
     its.rewind();
@@ -648,6 +662,10 @@ static bool mysql_test_insert_fields(Prepared_statement *stmt,
       }
     }
   }
+  else
+  {
+    thd->free_temporary_memory_pool_for_ps_preparing();
+  }
   if (send_prep_stmt(stmt, 0))
     DBUG_RETURN(1);
   DBUG_RETURN(0);
@@ -677,12 +695,21 @@ static bool mysql_test_upd_fields(Prepared_statement *stmt,
       (grant_option && check_grant(thd,UPDATE_ACL,table_list,0,0)))
     DBUG_RETURN(1);
 #endif
+
+  /* 
+     open temporary memory pool for temporary data allocated by derived
+     tables & preparation procedure
+  */
+  thd->allocate_temporary_memory_pool_for_ps_preparing();
+
   if (open_and_lock_tables(thd, table_list))
-    DBUG_RETURN(1);
+    goto err;
   if (setup_tables(table_list) ||
-      setup_fields(thd, 0, table_list, fields, 1, 0, 0) || 
-      setup_conds(thd, table_list, &conds) || thd->net.report_error)      
-    DBUG_RETURN(1);
+      setup_fields(thd, 0, table_list, fields, 1, 0, 0) ||
+      setup_conds(thd, table_list, &conds) || thd->net.report_error)
+    goto err;
+
+  thd->free_temporary_memory_pool_for_ps_preparing();
 
   /* 
      Currently return only column list info only, and we are not
@@ -691,6 +718,9 @@ static bool mysql_test_upd_fields(Prepared_statement *stmt,
   if (send_prep_stmt(stmt, 0))
     DBUG_RETURN(1);
   DBUG_RETURN(0);
+err:
+  thd->free_temporary_memory_pool_for_ps_preparing();
+  DBUG_RETURN(1);
 }
 
 /*
@@ -735,41 +765,51 @@ static bool mysql_test_select_fields(Prepared_statement *stmt,
   if ((&lex->select_lex != lex->all_selects_list &&
        lex->unit.create_total_list(thd, lex, &tables)))
    DBUG_RETURN(1);
-    
+
+  /* 
+     open temporary memory pool for temporary data allocated by derived
+     tables & preparation procedure
+  */
+  thd->allocate_temporary_memory_pool_for_ps_preparing();
   if (open_and_lock_tables(thd, tables))
-    DBUG_RETURN(1);
+    goto err;
 
   if (lex->describe)
   {
     if (send_prep_stmt(stmt, 0))
-      DBUG_RETURN(1);      
+      goto err;
   }
   else 
   {
     if (!result && !(result= new select_send()))
     {
       send_error(thd, ER_OUT_OF_RESOURCES);
-      DBUG_RETURN(1);
+      goto err;
     }
 
-    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))
-      DBUG_RETURN(1);
+    if (unit->prepare(thd, result, 0))
+      goto err_prep;
+
     if (send_prep_stmt(stmt, fields.elements) ||
         thd->protocol_simple.send_fields(&fields, 0)
 #ifndef EMBEDDED_LIBRARY
-         || net_flush(&thd->net)
+	|| net_flush(&thd->net)
 #endif
-       )
-      DBUG_RETURN(1);
-    join->cleanup();
+	)
+      goto err_prep;
+
+    unit->cleanup();
   }
-  DBUG_RETURN(0);  
+  thd->free_temporary_memory_pool_for_ps_preparing();
+  DBUG_RETURN(0);
+
+err_prep:
+  unit->cleanup();
+err:
+  thd->free_temporary_memory_pool_for_ps_preparing();
+  DBUG_RETURN(1);
 }
 
 
@@ -898,6 +938,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;
@@ -925,9 +966,9 @@ bool mysql_stmt_prepare(THD *thd, char *packet, uint packet_length)
     sl->prep_where= sl->where;
   }
 
-  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;
@@ -944,8 +985,14 @@ init_param_err:
 alloc_query_err:
   /* Statement map deletes statement on erase */
   thd->stmt_map.erase(stmt);
+  thd->current_statement= 0;
   DBUG_RETURN(1);
 insert_stmt_err:
+  stmt->set_statement(thd);
+  thd->set_statement(&thd->stmt_backup);
+  /* Statement map deletes statement on erase */
+  thd->stmt_map.erase(stmt);
+  thd->current_statement= 0;
   delete stmt;
   DBUG_RETURN(1);
 }
@@ -1010,24 +1057,36 @@ 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;
+    }
+    
+    {
+      SELECT_LEX_UNIT *unit= sl->master_unit();
+      unit->unclean();
+      unit->types.empty();
+      // for derived tables & PS (which can't be reset by Item_subquery)
+      unit->reinit_exec_mechanism();
+    }
   }
 
-  /*
-    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))
-    DBUG_VOID_RETURN;
+    goto end;
 #else
   if (stmt->param_count && (*stmt->setup_params_data)(stmt))
-    DBUG_VOID_RETURN;
+    goto end;
 #endif
 
   if (!(specialflag & SPECIAL_NO_PRIOR))
@@ -1048,8 +1107,10 @@ void mysql_stmt_execute(THD *thd, char *packet)
 
   free_items(thd->free_list);
   cleanup_items(stmt->free_list);
+  close_thread_tables(thd); // to close derived tables
   free_root(&thd->mem_root, MYF(0));
   thd->set_statement(&thd->stmt_backup);
+end:
   DBUG_VOID_RETURN;
 }
 
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 7ae25efebc6..7c1a5ad67b4 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -327,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)) !=
@@ -1519,10 +1518,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;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 8d1d64e9491..ee11b7b9da0 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -853,26 +853,35 @@ int mysql_create_table(THD *thd,const char *db, const char *table_name,
 	{
 	  if ((length=column->length) > file->max_key_length() ||
 	      length > file->max_key_part_length())
-	  {
-	    my_error(ER_WRONG_SUB_KEY,MYF(0));
-	    DBUG_RETURN(-1);
-	  }
-	}
-        /* TODO HF What's this for??? */
-	else if (f_is_geom(sql_field->pack_flag))
-	{
-	}
-	else if (column->length > length ||
-		 ((f_is_packed(sql_field->pack_flag) ||
-		   ((file->table_flags() & HA_NO_PREFIX_CHAR_KEYS) &&
-		    (key_info->flags & HA_NOSAME))) &&
-		  column->length != length))
-	{
-	  my_error(ER_WRONG_SUB_KEY,MYF(0));
-	  DBUG_RETURN(-1);
+          {
+            length=min(file->max_key_length(), file->max_key_part_length());
+            if (key->type == Key::MULTIPLE)
+            {
+              /* not a critical problem */
+              char warn_buff[MYSQL_ERRMSG_SIZE];
+              sprintf(warn_buff,ER(ER_TOO_LONG_KEY),length);
+              push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                           ER_TOO_LONG_KEY, warn_buff);
+            }
+            else
+            {
+              my_error(ER_TOO_LONG_KEY,MYF(0),length);
+              DBUG_RETURN(-1);
+            }
+          }
 	}
-	if (!(file->table_flags() & HA_NO_PREFIX_CHAR_KEYS))
-	  length=column->length;
+	else if (!f_is_geom(sql_field->pack_flag) &&
+                  (column->length > length ||
+                   ((f_is_packed(sql_field->pack_flag) ||
+                     ((file->table_flags() & HA_NO_PREFIX_CHAR_KEYS) &&
+                      (key_info->flags & HA_NOSAME))) &&
+                    column->length != length)))
+        {
+          my_error(ER_WRONG_SUB_KEY,MYF(0));
+          DBUG_RETURN(-1);
+        }
+        else if (!(file->table_flags() & HA_NO_PREFIX_CHAR_KEYS))
+          length=column->length;
       }
       else if (length == 0)
       {
@@ -882,8 +891,20 @@ int mysql_create_table(THD *thd,const char *db, const char *table_name,
       }
       if (length > file->max_key_part_length())
       {
-	my_error(ER_WRONG_SUB_KEY,MYF(0));
-	DBUG_RETURN(-1);
+        length=file->max_key_part_length();
+        if (key->type == Key::MULTIPLE)
+        {
+          /* not a critical problem */
+          char warn_buff[MYSQL_ERRMSG_SIZE];
+          sprintf(warn_buff,ER(ER_TOO_LONG_KEY),length);
+          push_warning(thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                       ER_TOO_LONG_KEY, warn_buff);
+        }
+        else
+        {
+          my_error(ER_TOO_LONG_KEY,MYF(0),length);
+          DBUG_RETURN(-1);
+        }
       }
       key_part_info->length=(uint16) length;
       /* Use packed keys for long strings on the first column */
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 2f55ec6e211..a05fa44cfc9 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -453,3 +453,9 @@ int st_select_lex_unit::cleanup()
   }
   DBUG_RETURN(error);
 }
+
+
+void st_select_lex_unit::reinit_exec_mechanism()
+{
+  prepared= optimized= executed= 0;
+}