Conflict resolution

include/my_sys.h:
  Auto merged
sql/handler.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/mysql_priv.h:
  Auto merged
sql/opt_range.cc:
  Auto merged
sql/sql_lex.cc:
  Auto merged
sql/sql_lex.h:
  Auto merged
sql/sql_parse.cc:
  Auto merged
sql/sql_table.cc:
  Auto merged
sql/sql_yacc.yy:
  Auto merged
sql/mysqld.cc:
  Manual merge

71 files changed, 2215 insertions, 1014 deletions

diff --git a/sql/Makefile.am b/sql/Makefile.am
index fd02cc906d7..5781b6181d2 100644
--- a/sql/Makefile.am
+++ b/sql/Makefile.am
@@ -117,7 +117,7 @@ gen_lex_hash.o:		gen_lex_hash.cc lex.h
 sql_yacc.cc:	sql_yacc.yy
 sql_yacc.h:	sql_yacc.yy
 
-sql_yacc.o:	sql_yacc.cc sql_yacc.h
+sql_yacc.o:	sql_yacc.cc sql_yacc.h $(HEADERS)
 		@echo "Note: The following compile may take a long time."
 		@echo "If it fails, re-run configure with --with-low-memory"
 		$(CXXCOMPILE) $(LM_CFLAGS) -c $<
diff --git a/sql/field.cc b/sql/field.cc
index 4aa58180a69..2f89dd43c3f 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -3152,11 +3152,13 @@ bool Field_time::get_time(TIME *ltime)
     ltime->neg= 1;
     tmp=-tmp;
   }
+  ltime->day= 0;
   ltime->hour=   (int) (tmp/10000);
   tmp-=ltime->hour*10000;
   ltime->minute= (int) tmp/100;
   ltime->second= (int) tmp % 100;
   ltime->second_part=0;
+  ltime->time_type= TIMESTAMP_TIME;
   return 0;
 }
 
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index ec2488f520a..144e6d7e74a 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -489,6 +489,8 @@ void (*Copy_field::get_copy_func(Field *to,Field *from))(Copy_field*)
 	if (!to->eq_def(from))
 	  return do_field_string;
       }
+      else if (to->charset() != from->charset())
+	return do_field_string;
       else if (to->real_type() == FIELD_TYPE_VAR_STRING && to_length !=
 	       from_length)
 	return do_varstring;
diff --git a/sql/ha_myisammrg.cc b/sql/ha_myisammrg.cc
index 5f07bbc4140..a0449e83222 100644
--- a/sql/ha_myisammrg.cc
+++ b/sql/ha_myisammrg.cc
@@ -303,14 +303,40 @@ THR_LOCK_DATA **ha_myisammrg::store_lock(THD *thd,
   return to;
 }
 
+
+/* Find out database name and table name from a filename */
+
+static void split_file_name(const char *file_name,
+			    LEX_STRING *db, LEX_STRING *name)
+{
+  uint name_length, dir_length, prefix_length;
+  char buff[FN_REFLEN];
+
+  db->length= 0;
+  name_length= (uint) (strmake(buff, file_name, sizeof(buff)-1) - buff);
+  dir_length= dirname_length(buff);
+  if (dir_length > 1)
+  {
+    /* Get database */
+    buff[dir_length-1]= 0;			// Remove end '/'
+    prefix_length= dirname_length(buff);
+    db->str= (char*) file_name+ prefix_length;
+    db->length= dir_length - prefix_length -1;
+  }
+  name->str= (char*) file_name+ dir_length;
+  name->length= (uint) (fn_ext(name->str) - name->str);
+}
+
+
 void ha_myisammrg::update_create_info(HA_CREATE_INFO *create_info)
 {
-  // [phi] auto_increment stuff is missing (but currently not needed)
   DBUG_ENTER("ha_myisammrg::update_create_info");
+
   if (!(create_info->used_fields & HA_CREATE_USED_UNION))
   {
     MYRG_TABLE *open_table;
     THD *thd=current_thd;
+
     create_info->merge_list.next= &create_info->merge_list.first;
     create_info->merge_list.elements=0;
 
@@ -318,14 +344,17 @@ void ha_myisammrg::update_create_info(HA_CREATE_INFO *create_info)
 	 open_table != file->end_table ;
 	 open_table++)
     {
-      char *name=open_table->table->filename;
-      char buff[FN_REFLEN];
       TABLE_LIST *ptr;
+      LEX_STRING db, name;
+
       if (!(ptr = (TABLE_LIST *) thd->calloc(sizeof(TABLE_LIST))))
 	goto err;
-      fn_format(buff,name,"","",3);
-      if (!(ptr->real_name=thd->strdup(buff)))
+      split_file_name(open_table->table->filename, &db, &name);
+      if (!(ptr->real_name= thd->strmake(name.str, name.length)))
+	goto err;
+      if (db.length && !(ptr->db= thd->strmake(db.str, db.length)))
 	goto err;
+
       create_info->merge_list.elements++;
       (*create_info->merge_list.next) = (byte*) ptr;
       create_info->merge_list.next= (byte**) &ptr->next;
@@ -344,37 +373,34 @@ err:
   DBUG_VOID_RETURN;
 }
 
+
 int ha_myisammrg::create(const char *name, register TABLE *form,
 			 HA_CREATE_INFO *create_info)
 {
   char buff[FN_REFLEN],**table_names,**pos;
   TABLE_LIST *tables= (TABLE_LIST*) create_info->merge_list.first;
+  THD *thd= current_thd;
   DBUG_ENTER("ha_myisammrg::create");
 
-  if (!(table_names= (char**) sql_alloc((create_info->merge_list.elements+1)*
-					sizeof(char*))))
+  if (!(table_names= (char**) thd->alloc((create_info->merge_list.elements+1)*
+					 sizeof(char*))))
     DBUG_RETURN(1);
   for (pos=table_names ; tables ; tables=tables->next)
   {
     char *table_name;
+    TABLE **tbl= 0;
     if (create_info->options & HA_LEX_CREATE_TMP_TABLE)
+      tbl= find_temporary_table(thd, tables->db, tables->real_name);
+    if (!tbl)
     {
-      TABLE **tbl=find_temporary_table(current_thd,
-          tables->db, tables->real_name);
-      if (!tbl)
-      {
-        table_name=sql_alloc(1+
-            my_snprintf(buff,FN_REFLEN,"%s/%s/%s",mysql_real_data_home,
-                        tables->db, tables->real_name));
-        if (!table_name)
-          DBUG_RETURN(1);
-        strcpy(table_name, buff);
-      }
-      else
-        table_name=(*tbl)->path;
+      uint length= my_snprintf(buff,FN_REFLEN,"%s%s/%s",
+			       mysql_real_data_home,
+			       tables->db, tables->real_name);
+      if (!(table_name= thd->strmake(buff, length)))
+	DBUG_RETURN(1);
     }
     else
-      table_name=tables->real_name;
+      table_name=(*tbl)->path;
     *pos++= table_name;
   }
   *pos=0;
@@ -384,9 +410,13 @@ int ha_myisammrg::create(const char *name, register TABLE *form,
                           (my_bool) 0));
 }
 
+
 void ha_myisammrg::append_create_info(String *packet)
 {
-  char buff[FN_REFLEN];
+  const char *current_db;
+  uint db_length;
+  THD *thd= current_thd;
+
   if (file->merge_insert_method != MERGE_INSERT_DISABLED)
   {
     packet->append(" INSERT_METHOD=",15);
@@ -395,15 +425,26 @@ void ha_myisammrg::append_create_info(String *packet)
   packet->append(" UNION=(",8);
   MYRG_TABLE *open_table,*first;
 
+  current_db= table->table_cache_key;
+  db_length=  strlen(current_db);
+
   for (first=open_table=file->open_tables ;
        open_table != file->end_table ;
        open_table++)
   {
-    char *name= open_table->table->filename;
-    fn_format(buff,name,"","",3);
+    LEX_STRING db, name;
+    split_file_name(open_table->table->filename, &db, &name);
     if (open_table != first)
       packet->append(',');
-    packet->append(buff,(uint) strlen(buff));
+    /* Report database for mapped table if it isn't in current database */
+    if (db.length &&
+	(db_length != db.length ||
+	 strncmp(current_db, db.str, db.length)))
+    {
+      append_identifier(thd, packet, db.str, db.length);
+      packet->append('.');
+    }
+    append_identifier(thd, packet, name.str, name.length);
   }
   packet->append(')');
 }
diff --git a/sql/handler.cc b/sql/handler.cc
index af8e14f9160..049194a18db 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -714,6 +714,8 @@ void handler::update_auto_increment()
     nr=get_auto_increment();
   if (!table->next_number_field->store(nr))
     thd->insert_id((ulonglong) nr);
+  else
+    thd->insert_id(table->next_number_field->val_int());
   auto_increment_column_changed=1;
   DBUG_VOID_RETURN;
 }
diff --git a/sql/item.cc b/sql/item.cc
index 3ea537a19de..072dec4e6a6 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -50,7 +50,13 @@ Item::Item():
   next= thd->free_list;			// Put in free list
   thd->free_list= this;
   loop_id= 0;
-  if (thd->lex.current_select->parsing_place == SELECT_LEX_NODE::SELECT_LIST)
+  /*
+    Item constructor can be called during execution other tnen SQL_COM
+    command => we should check thd->lex.current_select on zero (thd->lex
+    can be uninitialised)
+  */
+  if (thd->lex.current_select &&
+      thd->lex.current_select->parsing_place == SELECT_LEX_NODE::SELECT_LIST)
     thd->lex.current_select->select_items++;
 }
 
diff --git a/sql/item.h b/sql/item.h
index e80648b89de..57061221878 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -185,9 +185,6 @@ public:
     collation.collation= collation_arg->collation; 
     collation.derivation= collation_arg->derivation;
   }
-  bool binary() const
-  { return charset()->state & MY_CS_BINSORT ? 1 : 0 ; }
-  
   virtual void set_outer_resolving() {}
 
   // Row emulation
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index e3586fef260..758733aaa2c 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -32,18 +32,6 @@ static void my_coll_agg_error(DTCollation &c1, DTCollation &c2, const char *fnam
 	   fname);
 }
 
-static void my_coll_agg3_error(DTCollation &c1, 
-			       DTCollation &c2,
-			       DTCollation &c3,
-			       const char *fname)
-{
-  my_error(ER_CANT_AGGREGATE_3COLLATIONS,MYF(0),
-  	   c1.collation->name,c1.derivation_name(),
-	   c2.collation->name,c2.derivation_name(),
-	   c3.collation->name,c3.derivation_name(),
-	   fname);
-}
-
 Item_bool_func2* Item_bool_func2::eq_creator(Item *a, Item *b)
 {
   return new Item_func_eq(a, b);
@@ -109,15 +97,6 @@ static bool convert_constant_item(Field *field, Item **item)
 }
 
 
-bool Item_bool_func2::fix_fields(THD *thd, struct st_table_list *tables,
-				 Item ** ref)
-{
-  if (Item_int_func::fix_fields(thd, tables, ref))
-    return 1;
-  return 0;
-}
-
-
 void Item_bool_func2::fix_length_and_dec()
 {
   max_length= 1;				     // Function returns 0 or 1
@@ -191,8 +170,6 @@ void Item_bool_func2::fix_length_and_dec()
       {
 	cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
 			 INT_RESULT);		// Works for all types.
-	cmp_collation.set(&my_charset_bin, 
-			  DERIVATION_NONE);	// For test in fix_fields
 	return;
       }
     }
@@ -206,23 +183,11 @@ void Item_bool_func2::fix_length_and_dec()
       {
 	cmp.set_cmp_func(this, tmp_arg, tmp_arg+1,
 			 INT_RESULT); // Works for all types.
-	cmp_collation.set(&my_charset_bin,
-			  DERIVATION_NONE);	// For test in fix_fields
 	return;
       }
     }
   }
   set_cmp_func();
-  /*
-    We must set cmp_charset here as we may be called from for an automatic
-    generated item, like in natural join
-  */
-  if (cmp_collation.set(args[0]->collation, args[1]->collation))
-  {
-    /* set_cmp_charset() failed */
-    my_coll_agg_error(args[0]->collation, args[1]->collation, func_name());
-    return;
-  }
 }
 
 
@@ -252,6 +217,18 @@ int Arg_comparator::set_compare_func(Item_bool_func2 *item, Item_result type)
       comparators[i].set_cmp_func(owner, (*a)->addr(i), (*b)->addr(i));
     }
   }
+  else if (type == STRING_RESULT)
+  {
+    /*
+      We must set cmp_charset here as we may be called from for an automatic
+      generated item, like in natural join
+    */
+    if (cmp_collation.set((*a)->collation, (*b)->collation))
+    {
+      my_coll_agg_error((*a)->collation, (*b)->collation, owner->func_name());
+      return 1;
+    }
+  }
   return 0;
 }
 
@@ -264,7 +241,7 @@ int Arg_comparator::compare_string()
     if ((res2= (*b)->val_str(&owner->tmp_value2)))
     {
       owner->null_value= 0;
-      return sortcmp(res1,res2,owner->cmp_collation.collation);
+      return sortcmp(res1,res2,cmp_collation.collation);
     }
   }
   owner->null_value= 1;
@@ -278,7 +255,7 @@ int Arg_comparator::compare_e_string()
   res2= (*b)->val_str(&owner->tmp_value2);
   if (!res1 || !res2)
     return test(res1 == res2);
-  return test(sortcmp(res1, res2, owner->cmp_collation.collation) == 0);
+  return test(sortcmp(res1, res2, cmp_collation.collation) == 0);
 }
 
 
@@ -380,12 +357,7 @@ bool Item_in_optimizer::fix_fields(THD *thd, struct st_table_list *tables,
     return 1;
   if (args[0]->maybe_null)
     maybe_null=1;
-  /*
-    TODO: Check if following is right
-    (set_charset set type of result, not how compare should be used)
-  */
-  if (args[0]->binary())
-    set_charset(&my_charset_bin);
+
   with_sum_func= args[0]->with_sum_func;
   used_tables_cache= args[0]->used_tables();
   const_item_cache= args[0]->const_item();
@@ -507,7 +479,7 @@ longlong Item_func_strcmp::val_int()
     null_value=1;
     return 0;
   }
-  int value= sortcmp(a,b,cmp_collation.collation);
+  int value= sortcmp(a,b,cmp.cmp_collation.collation);
   null_value=0;
   return !value ? 0 : (value < 0 ? (longlong) -1 : (longlong) 1);
 }
@@ -588,18 +560,9 @@ void Item_func_between::fix_length_and_dec()
            item_cmp_type(args[1]->result_type(),
                          args[2]->result_type()));
 
-  if (cmp_type == STRING_RESULT)
-  {
-    cmp_collation.set(args[0]->collation);
-    if (!cmp_collation.aggregate(args[1]->collation))
-      cmp_collation.aggregate(args[2]->collation);
-    if (cmp_collation.derivation == DERIVATION_NONE)
-    {
-      my_coll_agg3_error(args[0]->collation, args[1]->collation, 
-			 args[2]->collation, func_name());
-      return;
-    }
-  }
+  if (cmp_type == STRING_RESULT &&
+      agg_arg_collations_for_comparison(cmp_collation, args, 3))
+    return;
 
   /*
     Make a special case of compare with date/time and longlong fields.
@@ -707,8 +670,8 @@ Item_func_ifnull::fix_length_and_dec()
 					  args[1]->result_type())) !=
       REAL_RESULT)
     decimals= 0;
-  if (collation.set(args[0]->collation,args[1]->collation))
-    my_coll_agg_error(args[0]->collation, args[1]->collation, func_name());
+  if (cached_result_type == STRING_RESULT)
+    agg_arg_collations(collation, args, arg_count);
 }
 
 
@@ -784,11 +747,8 @@ Item_func_if::fix_length_and_dec()
   else if (arg1_type == STRING_RESULT || arg2_type == STRING_RESULT)
   {
     cached_result_type = STRING_RESULT;
-    if (collation.set(args[1]->collation, args[2]->collation))
-    {
-      my_coll_agg_error(args[0]->collation, args[1]->collation, func_name());
+    if (agg_arg_collations(collation, args+1, 2))
       return;
-    }
   }
   else
   {
@@ -933,7 +893,7 @@ Item *Item_func_case::find_item(String *str)
       if ((tmp=args[i]->val_str(str)))		// If not null
       {
 	/* QQ: COERCIBILITY */
-	if (first_expr_is_binary || args[i]->binary())
+	if (first_expr_is_binary || (args[i]->charset()->state & MY_CS_BINSORT))
 	{
 	  if (sortcmp(tmp,first_expr_str,&my_charset_bin)==0)
 	    return args[i+1];
@@ -1044,7 +1004,7 @@ Item_func_case::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
     used_tables_cache|=(first_expr)->used_tables();
     const_item_cache&= (first_expr)->const_item();
     with_sum_func= with_sum_func || (first_expr)->with_sum_func;
-    first_expr_is_binary= first_expr->binary();
+    first_expr_is_binary= first_expr->charset()->state & MY_CS_BINSORT;
   }
   if (else_expr)
   {
@@ -1189,7 +1149,13 @@ void Item_func_coalesce::fix_length_and_dec()
   {
     set_if_bigger(max_length,args[i]->max_length);
     set_if_bigger(decimals,args[i]->decimals);
+    cached_result_type=item_store_type(cached_result_type,
+				       args[i]->result_type());
   }
+  if (cached_result_type == STRING_RESULT)
+    agg_arg_collations(collation, args, arg_count);
+  else if (cached_result_type != REAL_RESULT)
+    decimals= 0;
 }
 
 /****************************************************************************
@@ -1454,7 +1420,7 @@ int cmp_item_row::compare(cmp_item *c)
 bool Item_func_in::nulls_in_row()
 {
   Item **arg,**arg_end;
-  for (arg= args, arg_end= args+arg_count; arg != arg_end ; arg++)
+  for (arg= args+1, arg_end= args+arg_count; arg != arg_end ; arg++)
   {
     if ((*arg)->null_inside())
       return 1;
@@ -1471,42 +1437,43 @@ static int srtcmp_in(CHARSET_INFO *cs, const String *x,const String *y)
 
 void Item_func_in::fix_length_and_dec()
 {
+  Item **arg, **arg_end;
+  uint const_itm= 1;
+  
+  if ((args[0]->result_type() == STRING_RESULT) &&
+      (agg_arg_collations_for_comparison(cmp_collation, args, arg_count)))
+    return;
+  
+  for (arg=args+1, arg_end=args+arg_count; arg != arg_end ; arg++)
+    const_itm&= arg[0]->const_item();
+  
   /*
     Row item with NULLs inside can return NULL or FALSE => 
     they can't be processed as static
   */
-  if (const_item() && !nulls_in_row())
+  if (const_itm && !nulls_in_row())
   {
-    switch (item->result_type()) {
+    switch (args[0]->result_type()) {
     case STRING_RESULT:
       uint i;
-      cmp_collation.set(item->collation);
-      for (i=0 ; i<arg_count; i++)
-	if (cmp_collation.aggregate(args[i]->collation))
-	  break;
-      if (cmp_collation.derivation == DERIVATION_NONE)
-      {
-        my_error(ER_CANT_AGGREGATE_NCOLLATIONS,MYF(0),func_name());
-	return;
-      }
-      array=new in_string(arg_count,(qsort2_cmp) srtcmp_in, 
+      array=new in_string(arg_count-1,(qsort2_cmp) srtcmp_in, 
 			  cmp_collation.collation);
       break;
     case INT_RESULT:
-      array= new in_longlong(arg_count);
+      array= new in_longlong(arg_count-1);
       break;
     case REAL_RESULT:
-      array= new in_double(arg_count);
+      array= new in_double(arg_count-1);
       break;
     case ROW_RESULT:
-      array= new in_row(arg_count, item);
+      array= new in_row(arg_count-1, args[0]);
       break;
     default:
       DBUG_ASSERT(0);
       return;
     }
     uint j=0;
-    for (uint i=0 ; i < arg_count ; i++)
+    for (uint i=1 ; i < arg_count ; i++)
     {
       array->set(j,args[i]);
       if (!args[i]->null_value)			// Skip NULL values
@@ -1519,19 +1486,19 @@ void Item_func_in::fix_length_and_dec()
   }
   else
   {
-    in_item= cmp_item::get_comparator(item);
+    in_item= cmp_item::get_comparator(args[0]);
+    if (args[0]->result_type() == STRING_RESULT)
+      in_item->cmp_charset= cmp_collation.collation;
   }
-  maybe_null= item->maybe_null;
+  maybe_null= args[0]->maybe_null;
   max_length= 1;
-  used_tables_cache|=item->used_tables();
-  const_item_cache&=item->const_item();
+  const_item_cache&=args[0]->const_item();
 }
 
 
 void Item_func_in::print(String *str)
 {
   str->append('(');
-  item->print(str);
   Item_func::print(str);
   str->append(')');
 }
@@ -1541,15 +1508,15 @@ longlong Item_func_in::val_int()
 {
   if (array)
   {
-    int tmp=array->find(item);
-    null_value=item->null_value || (!tmp && have_null);
+    int tmp=array->find(args[0]);
+    null_value=args[0]->null_value || (!tmp && have_null);
     return tmp;
   }
-  in_item->store_value(item);
-  if ((null_value=item->null_value))
+  in_item->store_value(args[0]);
+  if ((null_value=args[0]->null_value))
     return 0;
   have_null= 0;
-  for (uint i=0 ; i < arg_count ; i++)
+  for (uint i=1 ; i < arg_count ; i++)
   {
     if (!in_item->cmp(args[i]) && !args[i]->null_value)
       return 1;					// Would maybe be nice with i ?
@@ -1560,29 +1527,6 @@ longlong Item_func_in::val_int()
 }
 
 
-void Item_func_in::update_used_tables()
-{
-  Item_func::update_used_tables();
-  item->update_used_tables();
-  used_tables_cache|=item->used_tables();
-  const_item_cache&=item->const_item();
-}
-
-void Item_func_in::split_sum_func(Item **ref_pointer_array, List<Item> &fields)
-{
-  if (item->with_sum_func && item->type() != SUM_FUNC_ITEM)
-    item->split_sum_func(ref_pointer_array, fields);
-  else if (item->used_tables() || item->type() == SUM_FUNC_ITEM)
-  {
-    uint el= fields.elements;
-    fields.push_front(item);
-    ref_pointer_array[el]= item;
-    item= new Item_ref(ref_pointer_array + el, 0, item->name);
-  }  
-  Item_func::split_sum_func(ref_pointer_array, fields);
-}
-
-
 longlong Item_func_bit_or::val_int()
 {
   ulonglong arg1= (ulonglong) args[0]->val_int();
@@ -1898,7 +1842,7 @@ longlong Item_func_like::val_int()
   null_value=0;
   if (canDoTurboBM)
     return turboBM_matches(res->ptr(), res->length()) ? 1 : 0;
-  return my_wildcmp(cmp_collation.collation,
+  return my_wildcmp(cmp.cmp_collation.collation,
 		    res->ptr(),res->ptr()+res->length(),
 		    res2->ptr(),res2->ptr()+res2->length(),
 		    escape,wild_one,wild_many) ? 0 : 1;
@@ -1988,11 +1932,8 @@ Item_func_regex::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
   max_length= 1;
   decimals= 0;
 
-  if (cmp_collation.set(args[0]->collation, args[1]->collation))
-  {
-    my_coll_agg_error(args[0]->collation, args[1]->collation, func_name());
+  if (agg_arg_collations(cmp_collation, args, 2))
     return 1;
-  }
 
   used_tables_cache=args[0]->used_tables() | args[1]->used_tables();
   const_item_cache=args[0]->const_item() && args[1]->const_item();
@@ -2108,7 +2049,7 @@ void Item_func_like::turboBM_compute_suffixes(int *suff)
 
   *splm1 = pattern_len;
 
-  if (cmp_collation.collation == &my_charset_bin)
+  if (cmp.cmp_collation.collation == &my_charset_bin)
   {
     int i;
     for (i = pattern_len - 2; i >= 0; i--)
@@ -2211,7 +2152,7 @@ void Item_func_like::turboBM_compute_bad_character_shifts()
   for (i = bmBc; i < end; i++)
     *i = pattern_len;
 
-  if (cmp_collation.collation == &my_charset_bin)
+  if (cmp.cmp_collation.collation == &my_charset_bin)
   {
     for (j = 0; j < plm1; j++)
       bmBc[(uint) (uchar) pattern[j]] = plm1 - j;
@@ -2242,7 +2183,7 @@ bool Item_func_like::turboBM_matches(const char* text, int text_len) const
   const int tlmpl= text_len - pattern_len;
 
   /* Searching */
-  if (cmp_collation.collation == &my_charset_bin)
+  if (cmp.cmp_collation.collation == &my_charset_bin)
   {
     while (j <= tlmpl)
     {
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index d0a5eab2c6a..0d20382eb92 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -35,6 +35,8 @@ class Arg_comparator: public Sql_alloc
   Arg_comparator *comparators;   // used only for compare_row()
 
 public:
+  DTCollation cmp_collation;
+
   Arg_comparator() {};
   Arg_comparator(Item **a1, Item **a2): a(a1), b(a2) {};
 
@@ -112,13 +114,10 @@ class Item_bool_func2 :public Item_int_func
 protected:
   Arg_comparator cmp;
   String tmp_value1,tmp_value2;
-  DTCollation cmp_collation;
 
 public:
   Item_bool_func2(Item *a,Item *b):
-    Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1)
-    { cmp_collation.set(0,DERIVATION_NONE);}
-  bool fix_fields(THD *thd, TABLE_LIST *tlist, Item ** ref);
+    Item_int_func(a,b), cmp(tmp_arg, tmp_arg+1) {}
   void fix_length_and_dec();
   void set_cmp_func()
   {
@@ -129,8 +128,6 @@ public:
   bool have_rev_func() const { return rev_functype() != UNKNOWN_FUNC; }
   void print(String *str) { Item_func::print_op(str); }
   bool is_null() { return test(args[0]->is_null() || args[1]->is_null()); }
-  virtual bool binary() const 
-  { return test(cmp_collation.collation->state & MY_CS_BINSORT); }
 
   static Item_bool_func2* eq_creator(Item *a, Item *b);
   static Item_bool_func2* ne_creator(Item *a, Item *b);
@@ -603,42 +600,26 @@ public:
 
 class Item_func_in :public Item_int_func
 {
-  Item *item;
   in_vector *array;
   cmp_item *in_item;
   bool have_null;
   DTCollation cmp_collation;
  public:
-  Item_func_in(Item *a,List<Item> &list)
-    :Item_int_func(list), item(a), array(0), in_item(0), have_null(0)
+  Item_func_in(List<Item> &list)
+    :Item_int_func(list), array(0), in_item(0), have_null(0)
   {
-    allowed_arg_cols= item->cols();
+    allowed_arg_cols= args[0]->cols();
   }
   longlong val_int();
-  bool fix_fields(THD *thd, struct st_table_list *tlist, Item **ref)
-  {
-    // We do not check item->cols(), because allowed_arg_cols assigned from it
-    bool res=(item->fix_fields(thd, tlist, &item) ||
-	      Item_func::fix_fields(thd, tlist, ref));
-    with_sum_func= with_sum_func || item->with_sum_func;
-    return res;
-  }
   void fix_length_and_dec();
-  ~Item_func_in() { delete item; delete array; delete in_item; }
+  ~Item_func_in() { delete array; delete in_item; }
   optimize_type select_optimize() const
     { return array ? OPTIMIZE_KEY : OPTIMIZE_NONE; }
-  Item *key_item() const { return item; }
+  Item *key_item() const { return args[0]; }
   void print(String *str);
   enum Functype functype() const { return IN_FUNC; }
   const char *func_name() const { return " IN "; }
-  void update_used_tables();
-  void split_sum_func(Item **ref_pointer_array, List<Item> &fields);
   bool nulls_in_row();
-  void set_outer_resolving()
-  {
-    item->set_outer_resolving();
-    Item_int_func::set_outer_resolving();
-  }
 };
 
 /* Functions used by where clause */
diff --git a/sql/item_create.cc b/sql/item_create.cc
index 90f42cee959..eaa27c1009d 100644
--- a/sql/item_create.cc
+++ b/sql/item_create.cc
@@ -691,3 +691,38 @@ Item *create_func_uncompressed_length(Item* a)
 
 #endif
 
+Item *create_func_datediff(Item *a, Item *b)
+{
+  return new Item_func_minus(new Item_func_to_days(a),
+			     new Item_func_to_days(b));
+}
+
+Item *create_func_weekofyear(Item *a)
+{
+  return new Item_func_week(a, new Item_int((char*) "0", 3, 1));
+}
+
+Item *create_func_makedate(Item* a,Item* b)
+{
+  return new Item_func_makedate(a, b);
+}
+
+Item *create_func_addtime(Item* a,Item* b)
+{
+  return new Item_func_add_time(a, b, 0, 0);
+}
+
+Item *create_func_subtime(Item* a,Item* b)
+{
+  return new Item_func_add_time(a, b, 0, 1);
+}
+
+Item *create_func_timediff(Item* a,Item* b)
+{
+  return new Item_func_timediff(a, b);
+}
+
+Item *create_func_maketime(Item* a,Item* b,Item* c)
+{
+  return new Item_func_maketime(a, b, c);
+}
diff --git a/sql/item_create.h b/sql/item_create.h
index 4151f59a87f..32e452c15dd 100644
--- a/sql/item_create.h
+++ b/sql/item_create.h
@@ -146,3 +146,10 @@ Item *create_func_compress(Item *a);
 Item *create_func_uncompress(Item *a);
 Item *create_func_uncompressed_length(Item *a);
 
+Item *create_func_datediff(Item *a, Item *b);
+Item *create_func_weekofyear(Item *a);
+Item *create_func_makedate(Item* a,Item* b);
+Item *create_func_addtime(Item* a,Item* b);
+Item *create_func_subtime(Item* a,Item* b);
+Item *create_func_timediff(Item* a,Item* b);
+Item *create_func_maketime(Item* a,Item* b,Item* c);
diff --git a/sql/item_func.cc b/sql/item_func.cc
index d7237f55522..7e236225067 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -39,6 +39,61 @@ static void my_coll_agg_error(DTCollation &c1, DTCollation &c2, const char *fnam
 	   fname);
 }
 
+static void my_coll_agg_error(DTCollation &c1, 
+			       DTCollation &c2,
+			       DTCollation &c3,
+			       const char *fname)
+{
+  my_error(ER_CANT_AGGREGATE_3COLLATIONS,MYF(0),
+  	   c1.collation->name,c1.derivation_name(),
+	   c2.collation->name,c2.derivation_name(),
+	   c3.collation->name,c3.derivation_name(),
+	   fname);
+}
+
+static void my_coll_agg_error(Item** args, uint ac, const char *fname)
+{
+  if (2 == ac)
+    my_coll_agg_error(args[0]->collation, args[1]->collation, fname);
+  else if (3 == ac)
+    my_coll_agg_error(args[0]->collation,
+		      args[1]->collation,
+		      args[2]->collation,
+		      fname);
+  else
+    my_error(ER_CANT_AGGREGATE_NCOLLATIONS,MYF(0),fname);
+}
+
+bool Item_func::agg_arg_collations(DTCollation &c, Item **av, uint ac)
+{
+  uint i;
+  c.set(av[0]->collation);
+  for (i= 1; i < ac; i++)
+  {
+    if (c.aggregate(av[i]->collation))
+    {
+      my_coll_agg_error(av, ac, func_name());
+      return TRUE;
+    }
+  }
+  return FALSE;
+}
+
+bool Item_func::agg_arg_collations_for_comparison(DTCollation &c, 
+						  Item **av, uint ac)
+{
+  if (agg_arg_collations(c, av, ac))
+    return TRUE;
+  
+  if (c.derivation == DERIVATION_NONE)
+  {
+    my_coll_agg_error(av, ac, func_name());
+    return TRUE;
+  }
+  return FALSE;
+}
+
+
 /* return TRUE if item is a constant */
 
 bool
@@ -866,14 +921,9 @@ void Item_func_min_max::fix_length_and_dec()
     if (!args[i]->maybe_null)
       maybe_null=0;
     cmp_type=item_cmp_type(cmp_type,args[i]->result_type());
-    if (i==0)
-      collation.set(args[0]->collation);
-    if (collation.aggregate(args[i]->collation))
-    {
-      my_coll_agg_error(collation, args[i]->collation, func_name());
-      break;
-    }
   }
+  if (cmp_type == STRING_RESULT)
+    agg_arg_collations_for_comparison(collation, args, arg_count);
 }
 
 
@@ -1048,8 +1098,7 @@ longlong Item_func_coercibility::val_int()
 void Item_func_locate::fix_length_and_dec()
 {
   maybe_null=0; max_length=11;
-  if (cmp_collation.set(args[0]->collation, args[1]->collation))
-    my_coll_agg_error(args[0]->collation, args[1]->collation, func_name());
+  agg_arg_collations_for_comparison(cmp_collation, args, 2);
 }
 
 longlong Item_func_locate::val_int()
@@ -1118,33 +1167,48 @@ longlong Item_func_locate::val_int()
 
 longlong Item_func_field::val_int()
 {
-  String *field;
-  if (!(field=item->val_str(&value)))
-    return 0;					// -1 if null ?
-  for (uint i=0 ; i < arg_count ; i++)
+  if (cmp_type == STRING_RESULT)
+  {
+    String *field;
+    if (!(field=args[0]->val_str(&value)))
+      return 0;					// -1 if null ?
+    for (uint i=1 ; i < arg_count ; i++)
+    {
+      String *tmp_value=args[i]->val_str(&tmp);
+      if (tmp_value && field->length() == tmp_value->length() &&
+	  !sortcmp(field,tmp_value,cmp_collation.collation))
+        return (longlong) (i);
+    }
+  }
+  else if (cmp_type == INT_RESULT)
+  {
+    longlong val= args[0]->val_int();
+    for (uint i=1; i < arg_count ; i++)
+    {
+      if (val == args[i]->val_int())
+ 	return (longlong) (i);
+    }
+  }
+  else
   {
-    String *tmp_value=args[i]->val_str(&tmp);
-    if (tmp_value && field->length() == tmp_value->length() &&
-	!memcmp(field->ptr(),tmp_value->ptr(),tmp_value->length()))
-      return (longlong) (i+1);
+    double val= args[0]->val();
+    for (uint i=1; i < arg_count ; i++)
+    {
+      if (val == args[i]->val())
+ 	return (longlong) (i);
+    }
   }
   return 0;
 }
 
-
-void Item_func_field::split_sum_func(Item **ref_pointer_array,
-				     List<Item> &fields)
+void Item_func_field::fix_length_and_dec()
 {
-  if (item->with_sum_func && item->type() != SUM_FUNC_ITEM)
-    item->split_sum_func(ref_pointer_array, fields);
-  else if (item->used_tables() || item->type() == SUM_FUNC_ITEM)
-  {
-    uint el= fields.elements;
-    fields.push_front(item);
-    ref_pointer_array[el]= item;
-    item= new Item_ref(ref_pointer_array + el, 0, item->name);
-  }
-  Item_func::split_sum_func(ref_pointer_array, fields);
+  maybe_null=0; max_length=3;
+  cmp_type= args[0]->result_type();
+  for (uint i=1; i < arg_count ; i++)
+    cmp_type= item_cmp_type(cmp_type, args[i]->result_type());
+  if (cmp_type == STRING_RESULT)
+    agg_arg_collations_for_comparison(cmp_collation, args, arg_count);
 }
 
 
@@ -1209,8 +1273,7 @@ void Item_func_find_in_set::fix_length_and_dec()
       }
     }
   }
-  if (cmp_collation.set(args[0]->collation, args[1]->collation))
-    my_coll_agg_error(args[0]->collation, args[1]->collation, func_name());
+  agg_arg_collations_for_comparison(cmp_collation, args, 2);
 }
 
 static const char separator=',';
@@ -1353,7 +1416,18 @@ udf_handler::fix_fields(THD *thd, TABLE_LIST *tables, Item_result_field *func,
       Item *item= *arg;
       if (item->fix_fields(thd, tables, arg) || item->check_cols(1))
 	return 1;
-      if (item->binary())
+      /*
+	TODO: We should think about this. It is not always
+	right way just to set an UDF result to return my_charset_bin
+	if one argument has binary sorting order.
+	The result collation should be calculated according to arguments
+	derivations in some cases and should not in other cases.
+	Moreover, some arguments can represent a numeric input
+	which doesn't effect the result character set and collation.
+	There is no a general rule for UDF. Everything depends on
+	the particular user definted function.
+      */
+      if (item->charset()->state & MY_CS_BINSORT)
 	func->set_charset(&my_charset_bin);
       if (item->maybe_null)
 	func->maybe_null=1;
@@ -2589,21 +2663,61 @@ longlong Item_func_bit_xor::val_int()
   System variables
 ****************************************************************************/
 
-Item *get_system_var(enum_var_type var_type, LEX_STRING name)
+/*
+  Return value of an system variable base[.name] as a constant item
+
+  SYNOPSIS
+    get_system_var()
+    thd			Thread handler
+    var_type		global / session
+    name		Name of base or system variable
+    component		Component.
+
+  NOTES
+    If component.str = 0 then the variable name is in 'name'
+
+  RETURN
+    0	error
+    #	constant item
+*/
+  
+
+Item *get_system_var(THD *thd, enum_var_type var_type, LEX_STRING name,
+		     LEX_STRING component)
 {
-  if (!my_strcasecmp(system_charset_info, name.str, "VERSION"))
+  if (component.str == 0 &&
+      !my_strcasecmp(system_charset_info, name.str, "VERSION"))
     return new Item_string("@@VERSION", server_version,
 			   (uint) strlen(server_version),
 			   system_charset_info);
 
-  THD *thd=current_thd;
   Item *item;
   sys_var *var;
-  char buff[MAX_SYS_VAR_LENGTH+3+8], *pos;
+  char buff[MAX_SYS_VAR_LENGTH*2+4+8], *pos;
+  LEX_STRING *base_name, *component_name;
 
-  if (!(var= find_sys_var(name.str, name.length)))
+  if (component.str)
+  {
+    base_name= &component;
+    component_name= &name;
+  }
+  else
+  {
+    base_name= &name;
+    component_name= &component;			// Empty string
+  }
+
+  if (!(var= find_sys_var(base_name->str, base_name->length)))
     return 0;
-  if (!(item=var->item(thd, var_type)))
+  if (component.str)
+  {
+    if (!var->is_struct())
+    {
+      net_printf(thd, ER_VARIABLE_IS_NOT_STRUCT, base_name->str);
+      return 0;
+    }
+  }
+  if (!(item=var->item(thd, var_type, component_name)))
     return 0;					// Impossible
   thd->lex.uncacheable();
   buff[0]='@';
@@ -2613,23 +2727,37 @@ Item *get_system_var(enum_var_type var_type, LEX_STRING name)
     pos=strmov(pos,"session.");
   else if (var_type == OPT_GLOBAL)
     pos=strmov(pos,"global.");
-  memcpy(pos, var->name, var->name_length+1);
+  
+  set_if_smaller(component_name->length, MAX_SYS_VAR_LENGTH);
+  set_if_smaller(base_name->length, MAX_SYS_VAR_LENGTH);
+
+  if (component_name->str)
+  {
+    memcpy(pos, component_name->str, component_name->length);
+    pos+= component_name->length;
+    *pos++= '.';
+  }
+  memcpy(pos, base_name->str, base_name->length);
+  pos+= base_name->length;
+
   // set_name() will allocate the name
-  item->set_name(buff,(uint) (pos-buff)+var->name_length, system_charset_info);
+  item->set_name(buff,(uint) (pos-buff), system_charset_info);
   return item;
 }
 
 
-Item *get_system_var(enum_var_type var_type, const char *var_name, uint length,
-		     const char *item_name)
+Item *get_system_var(THD *thd, enum_var_type var_type, const char *var_name,
+		     uint length, const char *item_name)
 {
-  THD *thd=current_thd;
   Item *item;
   sys_var *var;
+  LEX_STRING null_lex_string;
+
+  null_lex_string.str= 0;
 
   var= find_sys_var(var_name, length);
   DBUG_ASSERT(var != 0);
-  if (!(item=var->item(thd, var_type)))
+  if (!(item=var->item(thd, var_type, &null_lex_string)))
     return 0;						// Impossible
   thd->lex.uncacheable();
   item->set_name(item_name, 0, system_charset_info);	// Will use original name
diff --git a/sql/item_func.h b/sql/item_func.h
index 9ba5bea8b87..a0969fc6b9a 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -135,6 +135,9 @@ public:
   Field *tmp_table_field(TABLE *t_arg);
   void set_outer_resolving();
   Item *get_tmp_table_item(THD *thd);
+  
+  bool agg_arg_collations(DTCollation &c, Item **items, uint nitems);
+  bool agg_arg_collations_for_comparison(DTCollation &c, Item **items, uint nitems);
 };
 
 
@@ -622,37 +625,14 @@ public:
 
 class Item_func_field :public Item_int_func
 {
-  Item *item;
   String value,tmp;
+  Item_result cmp_type;
+  DTCollation cmp_collation;
 public:
-  Item_func_field(Item *a,List<Item> &list) :Item_int_func(list),item(a) {}
-  ~Item_func_field() { delete item; }
+  Item_func_field(List<Item> &list) :Item_int_func(list) {}
   longlong val_int();
-  bool fix_fields(THD *thd,struct st_table_list *tlist, Item **ref)
-  {
-    return (item->fix_fields(thd, tlist, &item) || item->check_cols(1) ||
-	    Item_func::fix_fields(thd, tlist, ref));
-  }
-  void split_sum_func(Item **ref_pointer_array, List<Item> &fields);
-  void update_used_tables()
-  {
-    item->update_used_tables() ; Item_func::update_used_tables();
-    used_tables_cache|= item->used_tables();
-    const_item_cache&=  item->const_item();
-  }
   const char *func_name() const { return "field"; }
-  void fix_length_and_dec()
-  {
-    maybe_null=0; max_length=3;
-    used_tables_cache|= item->used_tables();
-    const_item_cache&=  item->const_item();
-    with_sum_func= with_sum_func || item->with_sum_func;
-  }
-  void set_outer_resolving()
-  {
-    item->set_outer_resolving();
-    Item_int_func::set_outer_resolving();
-  }
+  void fix_length_and_dec();
 };
 
 
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index ae63ac85d4d..4e35e90b429 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -44,18 +44,6 @@ static void my_coll_agg_error(DTCollation &c1, DTCollation &c2, const char *fnam
 	   fname);
 }
 
-static void my_coll_agg3_error(DTCollation &c1, 
-			       DTCollation &c2,
-			       DTCollation &c3,
-			       const char *fname)
-{
-  my_error(ER_CANT_AGGREGATE_3COLLATIONS,MYF(0),
-  	   c1.collation->name,c1.derivation_name(),
-	   c2.collation->name,c2.derivation_name(),
-	   c3.collation->name,c3.derivation_name(),
-	   fname);
-}
-
 uint nr_of_decimals(const char *str)
 {
   if ((str=strchr(str,'.')))
@@ -336,16 +324,11 @@ void Item_func_concat::fix_length_and_dec()
   bool first_coll= 1;
   max_length=0;
 
-  collation.set(args[0]->collation);
+  if (agg_arg_collations(collation, args, arg_count))
+    return;
+
   for (uint i=0 ; i < arg_count ; i++)
-  {
     max_length+=args[i]->max_length;
-    if (collation.aggregate(args[i]->collation))
-    {
-      my_coll_agg_error(collation, args[i]->collation, func_name());
-      break;
-    }
-  }
 
   if (max_length > MAX_BLOB_WIDTH)
   {
@@ -748,7 +731,7 @@ String *Item_func_replace::val_str(String *str)
   res->set_charset(collation.collation);
 
 #ifdef USE_MB
-  binary_cmp = (args[0]->binary() || args[1]->binary() || !use_mb(res->charset()));
+  binary_cmp = ((res->charset()->state & MY_CS_BINSORT) || !use_mb(res->charset()));
 #endif
 
   if (res2->length() == 0)
@@ -840,13 +823,8 @@ void Item_func_replace::fix_length_and_dec()
     maybe_null=1;
   }
   
-  collation.set(args[0]->collation);
-  if (!collation.aggregate(args[1]->collation))
-    collation.aggregate(args[2]->collation);
-  
-  if (collation.derivation == DERIVATION_NONE)
-    my_coll_agg3_error(args[0]->collation, args[1]->collation, 
-		       args[2]->collation, func_name());
+  if (agg_arg_collations_for_comparison(collation, args, 3))
+    return;
 }
 
 
@@ -1001,13 +979,14 @@ void Item_func_right::fix_length_and_dec()
 String *Item_func_substr::val_str(String *str)
 {
   String *res  = args[0]->val_str(str);
-  int32 start	= (int32) args[1]->val_int()-1;
+  int32 start	= (int32) args[1]->val_int();
   int32 length	= arg_count == 3 ? (int32) args[2]->val_int() : INT_MAX32;
   int32 tmp_length;
 
   if ((null_value=(args[0]->null_value || args[1]->null_value ||
 		   (arg_count == 3 && args[2]->null_value))))
     return 0; /* purecov: inspected */
+  start= (int32)((start < 0) ? res->length() + start : start -1);
   start=res->charpos(start);
   length=res->charpos(length,start);
   if (start < 0 || (uint) start+1 > res->length() || length <= 0)
@@ -1050,9 +1029,9 @@ void Item_func_substr::fix_length_and_dec()
 void Item_func_substr_index::fix_length_and_dec()
 { 
   max_length= args[0]->max_length;
-  if (collation.set(args[0]->collation, args[1]->collation) ||
-      (collation.derivation == DERIVATION_NONE))
-    my_coll_agg_error(args[0]->collation, args[1]->collation, func_name());
+
+  if (agg_arg_collations_for_comparison(collation, args, 2))
+    return;
 }
 
 
@@ -1339,7 +1318,8 @@ void Item_func_trim::fix_length_and_dec()
     remove.set_ascii(" ",1);
   }
   else
-  if (collation.set(args[1]->collation, args[0]->collation))
+  if (collation.set(args[1]->collation, args[0]->collation) ||
+      collation.derivation == DERIVATION_NONE)
   {
     my_coll_agg_error(args[1]->collation, args[0]->collation, func_name());
   }
@@ -1680,88 +1660,53 @@ void Item_func_elt::fix_length_and_dec()
   max_length=0;
   decimals=0;
   
-  for (uint i=0 ; i < arg_count ; i++)
+  if (agg_arg_collations(collation, args+1, arg_count-1))
+    return;
+
+  for (uint i=1 ; i < arg_count ; i++)
   {
     set_if_bigger(max_length,args[i]->max_length);
     set_if_bigger(decimals,args[i]->decimals);
-    if (i == 0)
-      collation.set(args[0]->collation);
-    else
-    {
-      if (collation.aggregate(args[i]->collation))
-      {
-        my_coll_agg_error(collation, args[i]->collation, func_name());
-        break;
-      }
-    }
   }
   maybe_null=1;					// NULL if wrong first arg
-  with_sum_func= with_sum_func || item->with_sum_func;
-  used_tables_cache|=item->used_tables();
-  const_item_cache&=item->const_item();
-}
-
-
-void Item_func_elt::split_sum_func(Item **ref_pointer_array,
-				   List<Item> &fields)
-{
-  if (item->with_sum_func && item->type() != SUM_FUNC_ITEM)
-    item->split_sum_func(ref_pointer_array, fields);
-  else if (item->used_tables() || item->type() == SUM_FUNC_ITEM)
-  {
-    uint el= fields.elements;
-    fields.push_front(item);
-    ref_pointer_array[el]= item;
-    item= new Item_ref(ref_pointer_array + el, 0, item->name);
-  }
-  Item_str_func::split_sum_func(ref_pointer_array, fields);
-}
-
-
-void Item_func_elt::update_used_tables()
-{
-  Item_func::update_used_tables();
-  item->update_used_tables();
-  used_tables_cache|=item->used_tables();
-  const_item_cache&=item->const_item();
 }
 
 
 double Item_func_elt::val()
 {
   uint tmp;
-  if ((tmp=(uint) item->val_int()) == 0 || tmp > arg_count)
+  if ((tmp=(uint) args[0]->val_int()) == 0 || tmp >= arg_count)
   {
     null_value=1;
     return 0.0;
   }
   null_value=0;
-  return args[tmp-1]->val();
+  return args[tmp]->val();
 }
 
 longlong Item_func_elt::val_int()
 {
   uint tmp;
-  if ((tmp=(uint) item->val_int()) == 0 || tmp > arg_count)
+  if ((tmp=(uint) args[0]->val_int()) == 0 || tmp >= arg_count)
   {
     null_value=1;
     return 0;
   }
   null_value=0;
-  return args[tmp-1]->val_int();
+  return args[tmp]->val_int();
 }
 
 String *Item_func_elt::val_str(String *str)
 {
   uint tmp;
   String *res;
-  if ((tmp=(uint) item->val_int()) == 0 || tmp > arg_count)
+  if ((tmp=(uint) args[0]->val_int()) == 0 || tmp >= arg_count)
   {
     null_value=1;
     return NULL;
   }
   null_value=0;
-  res= args[tmp-1]->val_str(str);
+  res= args[tmp]->val_str(str);
   res->set_charset(charset());
   return res;
 }
@@ -1786,16 +1731,13 @@ void Item_func_make_set::split_sum_func(Item **ref_pointer_array,
 void Item_func_make_set::fix_length_and_dec()
 {
   max_length=arg_count-1;
-  collation.set(args[0]->collation);
+
+  if (agg_arg_collations(collation, args, arg_count))
+    return;
+  
   for (uint i=0 ; i < arg_count ; i++)
-  {
     max_length+=args[i]->max_length;
-    if (collation.aggregate(args[i]->collation))
-    {
-      my_coll_agg_error(collation, args[i]->collation, func_name());
-      break;
-    }
- }
+  
   used_tables_cache|=item->used_tables();
   const_item_cache&=item->const_item();
   with_sum_func= with_sum_func || item->with_sum_func;
@@ -2458,20 +2400,12 @@ String* Item_func_export_set::val_str(String* str)
 
 void Item_func_export_set::fix_length_and_dec()
 {
-  uint i;
   uint length=max(args[1]->max_length,args[2]->max_length);
   uint sep_length=(arg_count > 3 ? args[3]->max_length : 1);
   max_length=length*64+sep_length*63;
 
-  collation.set(args[1]->collation);
-  for (i=2 ; i < 4 && i < arg_count ; i++)
-  {
-    if (collation.aggregate(args[i]->collation))
-    {
-      my_coll_agg_error(collation, args[i]->collation, func_name());
-      break;
-    }
-  }
+  if (agg_arg_collations(collation, args+1, min(4,arg_count)-1))
+    return;
 }
 
 String* Item_func_inet_ntoa::val_str(String* str)
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index 6cc6d730627..7f8d7ade67b 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -372,29 +372,13 @@ public:
 
 class Item_func_elt :public Item_str_func
 {
-  Item *item;
-
 public:
-  Item_func_elt(Item *a,List<Item> &list) :Item_str_func(list),item(a) {}
-  ~Item_func_elt() { delete item; }
+  Item_func_elt(List<Item> &list) :Item_str_func(list) {}
   double val();
   longlong val_int();
   String *val_str(String *str);
-  bool fix_fields(THD *thd, TABLE_LIST *tlist, Item **ref)
-  {
-    return (item->fix_fields(thd, tlist, &item) ||
-	    item->check_cols(1) ||
-	    Item_func::fix_fields(thd, tlist, ref));
-  }
-  void split_sum_func(Item **ref_pointer_array, List<Item> &fields);
   void fix_length_and_dec();
-  void update_used_tables();
   const char *func_name() const { return "elt"; }
-  void set_outer_resolving()
-  {
-    item->set_outer_resolving();
-    Item_str_func::set_outer_resolving();
-  }
 };
 
 
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index 67dffb35724..8d3d0de466a 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -1114,7 +1114,7 @@ void Item_sum_count_distinct::make_unique()
 bool Item_sum_count_distinct::setup(THD *thd)
 {
   List<Item> list;
-  SELECT_LEX *select_lex= current_lex->current_select->select_lex();
+  SELECT_LEX *select_lex= thd->lex.current_select->select_lex();
   if (select_lex->linkage == GLOBAL_OPTIONS_TYPE)
     return 1;
     
@@ -1599,7 +1599,7 @@ Item_func_group_concat::Item_func_group_concat(bool is_distinct,
    warning_available(0), key_length(0), rec_offset(0),
    tree_mode(0), distinct(is_distinct), warning_for_row(0),
    separator(is_separator), tree(&tree_base), table(0),
-   order(0), tables_list(0), group_concat_max_len(0),
+   order(0), tables_list(0),
    show_elements(0), arg_count_order(0), arg_count_field(0),
    arg_show_fields(0), count_cut_values(0)
    
@@ -1607,8 +1607,11 @@ Item_func_group_concat::Item_func_group_concat(bool is_distinct,
   original= 0;
   quick_group= 0;
   mark_as_sum_func();
-  SELECT_LEX *select_lex= current_lex->current_select->select_lex();
+  item_thd= current_thd;
+  SELECT_LEX *select_lex= item_thd->lex.current_select->select_lex();
   order= 0;
+  group_concat_max_len= item_thd->variables.group_concat_max_len;
+
     
   arg_show_fields= arg_count_field= is_select->elements;
   arg_count_order= is_order ? is_order->elements : 0;
@@ -1773,7 +1776,7 @@ Item_func_group_concat::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
   }
   result_field= 0;
   null_value= 1;
-  fix_length_and_dec();
+  max_length= group_concat_max_len;
   thd->allow_sum_func= 1;			
   if (!(tmp_table_param= new TMP_TABLE_PARAM))
     return 1;
@@ -1785,11 +1788,12 @@ Item_func_group_concat::fix_fields(THD *thd, TABLE_LIST *tables, Item **ref)
 
 bool Item_func_group_concat::setup(THD *thd)
 {
+  DBUG_ENTER("Item_func_group_concat::setup");
   List<Item> list;
-  SELECT_LEX *select_lex= current_lex->current_select->select_lex();
+  SELECT_LEX *select_lex= thd->lex.current_select->select_lex();
 
   if (select_lex->linkage == GLOBAL_OPTIONS_TYPE)
-    return 1;
+    DBUG_RETURN(1);
   /*
     all not constant fields are push to list and create temp table
   */ 
@@ -1798,7 +1802,7 @@ bool Item_func_group_concat::setup(THD *thd)
   {
     Item *item= args[i];
     if (list.push_back(item))
-      return 1;
+      DBUG_RETURN(1);
     if (item->const_item())
     {
       (void) item->val_int();
@@ -1807,7 +1811,7 @@ bool Item_func_group_concat::setup(THD *thd)
     }
   }
   if (always_null)
-    return 0;
+    DBUG_RETURN(0);
         
   List<Item> all_fields(list);
   if (arg_count_order) 
@@ -1818,13 +1822,18 @@ bool Item_func_group_concat::setup(THD *thd)
   }
   
   count_field_types(tmp_table_param,all_fields,0);
+  if (table)
+  {
+    free_tmp_table(thd, table);
+    tmp_table_param->cleanup();
+  }
   /*
     We have to create a temporary table for that we get descriptions of fields 
     (types, sizes and so on).
   */
   if (!(table=create_tmp_table(thd, tmp_table_param, all_fields, 0,
-        0, 0, 0,select_lex->options | thd->options)))
-    return 1;
+			       0, 0, 0,select_lex->options | thd->options)))
+    DBUG_RETURN(1);
   table->file->extra(HA_EXTRA_NO_ROWS);
   table->no_rows= 1;
 
@@ -1873,9 +1882,6 @@ bool Item_func_group_concat::setup(THD *thd)
     max_elements_in_tree= ((key_length) ? 
            thd->variables.max_heap_table_size/key_length : 1);
   };
-  item_thd= thd;
-
-  group_concat_max_len= thd->variables.group_concat_max_len;
 
   /*
     Copy table and tree_mode if they belong to this item (if item have not 
@@ -1886,7 +1892,7 @@ bool Item_func_group_concat::setup(THD *thd)
     original->table= table;
     original->tree_mode= tree_mode;
   }
-  return 0;
+  DBUG_RETURN(0);
 }
 
 /* This is used by rollup to create a separate usable copy of the function */
diff --git a/sql/item_sum.h b/sql/item_sum.h
index c4876201a7c..6f0d7a028a7 100644
--- a/sql/item_sum.h
+++ b/sql/item_sum.h
@@ -713,7 +713,6 @@ class Item_func_group_concat : public Item_sum
   enum Sumfunctype sum_func () const {return GROUP_CONCAT_FUNC;}
   const char *func_name() const { return "group_concat"; }
   enum Type type() const { return SUM_FUNC_ITEM; }  
-  void fix_length_and_dec() { max_length=group_concat_max_len; }
   virtual Item_result result_type () const { return STRING_RESULT; }
   bool reset();
   bool add();
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 61c869cddba..c2d1504e94a 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -29,6 +29,8 @@
 ** Todo: Move month and days to language files
 */
 
+#define MAX_DAY_NUMBER 3652424L
+
 static String month_names[] = 
 { 
   String("January",	&my_charset_latin1), 
@@ -55,6 +57,82 @@ static String day_names[] =
   String("Sunday",	&my_charset_latin1)
 };
 
+enum date_time_format_types 
+{ 
+  TIME_ONLY= 0, TIME_MICROSECOND,
+  DATE_ONLY, DATE_TIME, DATE_TIME_MICROSECOND
+};
+
+typedef struct date_time_format 
+{
+  const char* format_str;
+  uint length;
+};
+
+static struct date_time_format date_time_formats[]=
+{
+  {"%s%02d:%02d:%02d", 10},
+  {"%s%02d:%02d:%02d.%06d", 17},
+  {"%04d-%02d-%02d", 10},
+  {"%04d-%02d-%02d %02d:%02d:%02d", 19},
+  {"%04d-%02d-%02d %02d:%02d:%02d.%06d", 26}
+};
+
+
+/*
+  OPTIMIZATION TODO:
+   - Replace the switch with a function that should be called for each
+     date type.
+   - Remove sprintf and opencode the conversion, like we do in
+     Field_datetime.
+*/
+
+String *make_datetime(String *str, TIME *ltime, 
+		      enum date_time_format_types format)
+{
+  char *buff;
+  CHARSET_INFO *cs= &my_charset_bin;
+  uint length= date_time_formats[format].length + 32;
+  const char* format_str= date_time_formats[format].format_str;
+
+  if (str->alloc(length))
+    return 0;
+
+  buff= (char*) str->ptr();
+  switch (format) {
+  case TIME_ONLY:
+    length= cs->cset->snprintf(cs, buff, length, format_str, ltime->neg ? "-" : "",
+			       ltime->hour, ltime->minute, ltime->second);
+    break;
+  case TIME_MICROSECOND:
+    length= cs->cset->snprintf(cs, buff, length, format_str, ltime->neg ? "-" : "",
+			       ltime->hour, ltime->minute, ltime->second,
+			       ltime->second_part);
+    break;
+  case DATE_ONLY:
+    length= cs->cset->snprintf(cs, buff, length, format_str,
+			       ltime->year, ltime->month, ltime->day);
+    break;
+  case DATE_TIME:
+    length= cs->cset->snprintf(cs, buff, length, format_str,
+			       ltime->year, ltime->month, ltime->day,
+			       ltime->hour, ltime->minute, ltime->second);
+    break;
+  case DATE_TIME_MICROSECOND:
+    length= cs->cset->snprintf(cs, buff, length, format_str,
+			       ltime->year, ltime->month, ltime->day,
+			       ltime->hour, ltime->minute, ltime->second,
+			       ltime->second_part);
+    break;
+  default:
+    return 0;
+  }
+
+  str->length(length);
+  str->set_charset(cs);
+  return str;
+}
+
 /*
 ** Get a array of positive numbers from a string object.
 ** Each number is separated by 1 non digit character
@@ -302,14 +380,14 @@ longlong Item_func_time_to_sec::val_int()
 static bool get_interval_value(Item *args,interval_type int_type,
 			       String *str_value, INTERVAL *t)
 {
-  long array[4],value;
+  long array[5],value;
   const char *str;
   uint32 length;
   LINT_INIT(value);  LINT_INIT(str); LINT_INIT(length);
   CHARSET_INFO *cs=str_value->charset();
 
   bzero((char*) t,sizeof(*t));
-  if ((int) int_type <= INTERVAL_SECOND)
+  if ((int) int_type <= INTERVAL_MICROSECOND)
   {
     value=(long) args->val_int();
     if (args->null_value)
@@ -352,6 +430,9 @@ static bool get_interval_value(Item *args,interval_type int_type,
   case INTERVAL_HOUR:
     t->hour=value;
     break;
+  case INTERVAL_MICROSECOND:
+    t->second_part=value;
+    break;
   case INTERVAL_MINUTE:
     t->minute=value;
     break;
@@ -370,6 +451,15 @@ static bool get_interval_value(Item *args,interval_type int_type,
     t->day=array[0];
     t->hour=array[1];
     break;
+  case INTERVAL_DAY_MICROSECOND:
+    if (get_interval_info(str,length,cs,5,array))
+      return (1);
+    t->day=array[0];
+    t->hour=array[1];
+    t->minute=array[2];
+    t->second=array[3];
+    t->second_part=array[4];
+    break;
   case INTERVAL_DAY_MINUTE:
     if (get_interval_info(str,length,cs,3,array))
       return (1);
@@ -385,6 +475,14 @@ static bool get_interval_value(Item *args,interval_type int_type,
     t->minute=array[2];
     t->second=array[3];
     break;
+  case INTERVAL_HOUR_MICROSECOND:
+    if (get_interval_info(str,length,cs,4,array))
+      return (1);
+    t->hour=array[0];
+    t->minute=array[1];
+    t->second=array[2];
+    t->second_part=array[3];
+    break;
   case INTERVAL_HOUR_MINUTE:
     if (get_interval_info(str,length,cs,2,array))
       return (1);
@@ -398,12 +496,25 @@ static bool get_interval_value(Item *args,interval_type int_type,
     t->minute=array[1];
     t->second=array[2];
     break;
+  case INTERVAL_MINUTE_MICROSECOND:
+    if (get_interval_info(str,length,cs,3,array))
+      return (1);
+    t->minute=array[0];
+    t->second=array[1];
+    t->second_part=array[2];
+    break;
   case INTERVAL_MINUTE_SECOND:
     if (get_interval_info(str,length,cs,2,array))
       return (1);
     t->minute=array[0];
     t->second=array[1];
     break;
+  case INTERVAL_SECOND_MICROSECOND:
+    if (get_interval_info(str,length,cs,2,array))
+      return (1);
+    t->second=array[0];
+    t->second_part=array[1];
+    break;
   }
   return 0;
 }
@@ -687,6 +798,9 @@ uint Item_func_date_format::format_length(const String *format)
       case 'T': /* time, 24-hour (hh:mm:ss) */
 	size += 8;
 	break;
+      case 'f': /* microseconds */
+	size += 6;
+	break;
       case 'w': /* day (of the week), numeric */
       case '%':
       default:
@@ -798,8 +912,8 @@ String *Item_func_date_format::val_str(String *str)
 	  null_value=1;
 	  return 0;
 	}
-	length= my_sprintf(intbuff, (intbuff,"%d",l_time.day));
-	str->append(intbuff, length);
+	length= int10_to_str(l_time.day, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
 	if (l_time.day >= 10 &&  l_time.day <= 19)
 	  str->append("th");
 	else
@@ -821,41 +935,45 @@ String *Item_func_date_format::val_str(String *str)
 	}
 	break;
       case 'Y':
-	sprintf(intbuff,"%04d",l_time.year);
-	str->append(intbuff,4);
+	length= int10_to_str(l_time.year, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 4, '0');
 	break;
       case 'y':
-	sprintf(intbuff,"%02d",l_time.year%100);
-	str->append(intbuff,2);
+	length= int10_to_str(l_time.year%100, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
 	break;
       case 'm':
-	sprintf(intbuff,"%02d",l_time.month);
-	str->append(intbuff,2);
+	length= int10_to_str(l_time.month, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
 	break;
       case 'c':
-	sprintf(intbuff,"%d",l_time.month);
-	str->append(intbuff);
+	length= int10_to_str(l_time.month, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
 	break;
       case 'd':
-	sprintf(intbuff,"%02d",l_time.day);
-	str->append(intbuff,2);
+	length= int10_to_str(l_time.day, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
 	break;
       case 'e':
-	sprintf(intbuff,"%d",l_time.day);
-	str->append(intbuff);
+	length= int10_to_str(l_time.day, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
+	break;
+      case 'f':
+	length= int10_to_str(l_time.second_part, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 6, '0');
 	break;
       case 'H':
-	sprintf(intbuff,"%02d",l_time.hour);
-	str->append(intbuff,2);
+	length= int10_to_str(l_time.hour, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
 	break;
       case 'h':
       case 'I':
-	sprintf(intbuff,"%02d", (l_time.hour+11)%12+1);
-	str->append(intbuff,2);
+	length= int10_to_str((l_time.hour+11)%12+1, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
 	break;
       case 'i':					/* minutes */
-	sprintf(intbuff,"%02d",l_time.minute);
-	str->append(intbuff,2);
+	length= int10_to_str(l_time.minute, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
 	break;
       case 'j':
 	if (date_or_time)
@@ -863,52 +981,60 @@ String *Item_func_date_format::val_str(String *str)
 	  null_value=1;
 	  return 0;
 	}
-	sprintf(intbuff,"%03d",
-		(int) (calc_daynr(l_time.year,l_time.month,l_time.day) -
-		       calc_daynr(l_time.year,1,1)) + 1);
-	str->append(intbuff,3);
+	length= int10_to_str(calc_daynr(l_time.year,l_time.month,l_time.day) - 
+		     calc_daynr(l_time.year,1,1) + 1, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 3, '0');
 	break;
       case 'k':
-	sprintf(intbuff,"%d",l_time.hour);
-	str->append(intbuff);
+	length= int10_to_str(l_time.hour, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
 	break;
       case 'l':
-	sprintf(intbuff,"%d", (l_time.hour+11)%12+1);
-	str->append(intbuff);
+	length= int10_to_str((l_time.hour+11)%12+1, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
 	break;
       case 'p':
 	str->append(l_time.hour < 12 ? "AM" : "PM",2);
 	break;
       case 'r':
-	sprintf(intbuff,(l_time.hour < 12) ? "%02d:%02d:%02d AM" :
-		"%02d:%02d:%02d PM",(l_time.hour+11)%12+1,l_time.minute,
-		l_time.second);
-	str->append(intbuff);
+	length= my_sprintf(intbuff, 
+		   (intbuff, 
+		    (l_time.hour < 12) ? "%02d:%02d:%02d AM" : "%02d:%02d:%02d PM",
+		    (l_time.hour+11)%12+1,
+		    l_time.minute,
+		    l_time.second));
+	str->append(intbuff, length);
 	break;
       case 'S':
       case 's':
-	sprintf(intbuff,"%02d",l_time.second);
-	str->append(intbuff);
+	length= int10_to_str(l_time.second, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
 	break;
       case 'T':
-	sprintf(intbuff,"%02d:%02d:%02d", l_time.hour, l_time.minute,
-		l_time.second);
-	str->append(intbuff);
+	length= my_sprintf(intbuff, 
+		   (intbuff, 
+		    "%02d:%02d:%02d", 
+		    l_time.hour, 
+		    l_time.minute,
+		    l_time.second));
+	str->append(intbuff, length);
 	break;
       case 'U':
       case 'u':
       {
 	uint year;
-	sprintf(intbuff,"%02d",calc_week(&l_time, 0, (*ptr) == 'U', &year));
-	str->append(intbuff,2);
+	length= int10_to_str(calc_week(&l_time, 0, (*ptr) == 'U', &year),
+		     intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
       }
       break;
       case 'v':
       case 'V':
       {
 	uint year;
-	sprintf(intbuff,"%02d",calc_week(&l_time, 1, (*ptr) == 'V', &year));
-	str->append(intbuff,2);
+	length= int10_to_str(calc_week(&l_time, 1, (*ptr) == 'V', &year),
+		     intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 2, '0');
       }
       break;
       case 'x':
@@ -916,14 +1042,15 @@ String *Item_func_date_format::val_str(String *str)
       {
 	uint year;
 	(void) calc_week(&l_time, 1, (*ptr) == 'X', &year);
-	sprintf(intbuff,"%04d",year);
-	str->append(intbuff,4);
+	length= int10_to_str(year, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 4, '0');
       }
       break;
       case 'w':
 	weekday=calc_weekday(calc_daynr(l_time.year,l_time.month,l_time.day),1);
-	sprintf(intbuff,"%d",weekday);
-	str->append(intbuff,1);
+	length= int10_to_str(weekday, intbuff, 10) - intbuff;
+	str->append_with_prefill(intbuff, length, 1, '0');
+
 	break;
       default:
 	str->append(*ptr);
@@ -1005,7 +1132,7 @@ void Item_date_add_interval::fix_length_and_dec()
   enum_field_types arg0_field_type;
   set_charset(default_charset());
   maybe_null=1;
-  max_length=19*default_charset()->mbmaxlen;
+  max_length=26*MY_CHARSET_BIN_MB_MAXLEN;
   value.alloc(32);
 
   /*
@@ -1051,39 +1178,55 @@ bool Item_date_add_interval::get_date(TIME *ltime, bool fuzzy_date)
   null_value=0;
   switch (int_type) {
   case INTERVAL_SECOND:
+  case INTERVAL_SECOND_MICROSECOND:
+  case INTERVAL_MICROSECOND:
   case INTERVAL_MINUTE:
   case INTERVAL_HOUR:
+  case INTERVAL_MINUTE_MICROSECOND:
   case INTERVAL_MINUTE_SECOND:
+  case INTERVAL_HOUR_MICROSECOND:
   case INTERVAL_HOUR_SECOND:
   case INTERVAL_HOUR_MINUTE:
+  case INTERVAL_DAY_MICROSECOND:
   case INTERVAL_DAY_SECOND:
   case INTERVAL_DAY_MINUTE:
   case INTERVAL_DAY_HOUR:
-    long sec,days,daynr;
+    long sec,days,daynr,microseconds,extra_sec;
     ltime->time_type=TIMESTAMP_FULL;		// Return full date
+    microseconds= ltime->second_part + sign*interval.second_part;
+    extra_sec= microseconds/1000000L;
+    microseconds= microseconds%1000000L;
 
     sec=((ltime->day-1)*3600*24L+ltime->hour*3600+ltime->minute*60+
 	 ltime->second +
 	 sign*(interval.day*3600*24L +
-	       interval.hour*3600+interval.minute*60+interval.second));
+	       interval.hour*3600+interval.minute*60+interval.second))+
+      extra_sec;
+
+    if (microseconds < 0)
+    {
+      microseconds+= 1000000L;	
+      sec--;
+    }
     days=sec/(3600*24L); sec=sec-days*3600*24L;
     if (sec < 0)
     {
       days--;
       sec+=3600*24L;
     }
+    ltime->second_part= microseconds;
     ltime->second=sec % 60;
     ltime->minute=sec/60 % 60;
     ltime->hour=sec/3600;
     daynr= calc_daynr(ltime->year,ltime->month,1) + days;
     get_date_from_daynr(daynr,&ltime->year,&ltime->month,&ltime->day);
-    if (daynr < 0 || daynr >= 3652424) // Day number from year 0 to 9999-12-31
+    if (daynr < 0 || daynr >= MAX_DAY_NUMBER) // Day number from year 0 to 9999-12-31
       goto null_date;
     break;
   case INTERVAL_DAY:
     period= calc_daynr(ltime->year,ltime->month,ltime->day) +
       sign*interval.day;
-    if (period < 0 || period >= 3652424) // Daynumber from year 0 to 9999-12-31
+    if (period < 0 || period >= MAX_DAY_NUMBER) // Daynumber from year 0 to 9999-12-31
       goto null_date;
     get_date_from_daynr((long) period,&ltime->year,&ltime->month,&ltime->day);
     break;
@@ -1124,34 +1267,21 @@ bool Item_date_add_interval::get_date(TIME *ltime, bool fuzzy_date)
 String *Item_date_add_interval::val_str(String *str)
 {
   TIME ltime;
-  CHARSET_INFO *cs=default_charset();
-  uint32 l;
+  enum date_time_format_types format;
 
   if (Item_date_add_interval::get_date(&ltime,0))
     return 0;
+
   if (ltime.time_type == TIMESTAMP_DATE)
-  {
-    l=11*cs->mbmaxlen+32;
-    if (str->alloc(l))
-      goto null_date;
-    l=cs->cset->snprintf(cs,(char*) str->ptr(),l,"%04d-%02d-%02d",
-	    ltime.year,ltime.month,ltime.day);
-    str->length(l);
-  }
+    format= DATE_ONLY;
+  else if (ltime.second_part)
+    format= DATE_TIME_MICROSECOND;
   else
-  {
-    l=20*cs->mbmaxlen+32;
-    if (str->alloc(l))
-      goto null_date;
-    l=cs->cset->snprintf(cs,(char*) str->ptr(),l,"%04d-%02d-%02d %02d:%02d:%02d",
-	    ltime.year,ltime.month,ltime.day,
-	    ltime.hour,ltime.minute,ltime.second);
-    str->length(l);
-  }
-  str->set_charset(cs);
-  return str;
+    format= DATE_TIME;
+
+  if (make_datetime(str, &ltime, format))
+    return str;
 
- null_date:
   null_value=1;
   return 0;
 }
@@ -1188,6 +1318,11 @@ void Item_extract::fix_length_and_dec()
   case INTERVAL_MINUTE:		max_length=2; date_value=0; break;
   case INTERVAL_MINUTE_SECOND:	max_length=4; date_value=0; break;
   case INTERVAL_SECOND:		max_length=2; date_value=0; break;
+  case INTERVAL_MICROSECOND:	max_length=2; date_value=0; break;
+  case INTERVAL_DAY_MICROSECOND: max_length=20; date_value=0; break;
+  case INTERVAL_HOUR_MICROSECOND: max_length=13; date_value=0; break;
+  case INTERVAL_MINUTE_MICROSECOND: max_length=11; date_value=0; break;
+  case INTERVAL_SECOND_MICROSECOND: max_length=9; date_value=0; break;
   }
 }
 
@@ -1234,6 +1369,21 @@ longlong Item_extract::val_int()
   case INTERVAL_MINUTE:		return (long) ltime.minute*neg;
   case INTERVAL_MINUTE_SECOND:	return (long) (ltime.minute*100+ltime.second)*neg;
   case INTERVAL_SECOND:		return (long) ltime.second*neg;
+  case INTERVAL_MICROSECOND:	return (long) ltime.second_part*neg;
+  case INTERVAL_DAY_MICROSECOND: return (((longlong)ltime.day*1000000L +
+					  (longlong)ltime.hour*10000L +
+					  ltime.minute*100 +
+					  ltime.second)*1000000L +
+					 ltime.second_part)*neg;
+  case INTERVAL_HOUR_MICROSECOND: return (((longlong)ltime.hour*10000L +
+					   ltime.minute*100 +
+					   ltime.second)*1000000L +
+					  ltime.second_part)*neg;
+  case INTERVAL_MINUTE_MICROSECOND: return (((longlong)(ltime.minute*100+
+							ltime.second))*1000000L+
+					    ltime.second_part)*neg;
+  case INTERVAL_SECOND_MICROSECOND: return ((longlong)ltime.second*1000000L+
+					    ltime.second_part)*neg;
   }
   return 0;					// Impossible
 }
@@ -1247,3 +1397,337 @@ void Item_typecast::print(String *str)
   str->append(func_name());
   str->append(')');
 }
+
+String *Item_datetime_typecast::val_str(String *str)
+{
+  TIME ltime;
+
+  if (!get_arg0_date(&ltime,1) &&
+      make_datetime(str, &ltime, ltime.second_part ?
+	    DATE_TIME_MICROSECOND : DATE_TIME))
+  return str;
+
+null_date:
+  null_value=1;
+  return 0;
+}
+
+
+bool Item_time_typecast::get_time(TIME *ltime)
+{
+  bool res= get_arg0_time(ltime);
+  ltime->time_type= TIMESTAMP_TIME;
+  return res;
+}
+
+
+String *Item_time_typecast::val_str(String *str)
+{
+  TIME ltime;
+
+  if (!get_arg0_time(&ltime) &&
+      make_datetime(str, &ltime, ltime.second_part ? TIME_MICROSECOND : TIME_ONLY))
+    return str;
+
+  null_value=1;
+  return 0;
+}
+
+
+bool Item_date_typecast::get_date(TIME *ltime, bool fuzzy_date)
+{
+  bool res= get_arg0_date(ltime,1);
+  ltime->time_type= TIMESTAMP_DATE;
+  return res;
+}
+
+
+String *Item_date_typecast::val_str(String *str)
+{
+  TIME ltime;
+
+  if (!get_arg0_date(&ltime,1) &&
+      make_datetime(str, &ltime, DATE_ONLY))
+  return str;
+
+null_date:
+  null_value=1;
+  return 0;
+}
+
+/*
+  MAKEDATE(a,b) is a date function that creates a date value 
+  from a year and day value.
+*/
+
+String *Item_func_makedate::val_str(String *str)
+{
+  TIME l_time;
+  long daynr= args[1]->val_int();
+  long yearnr= args[0]->val_int();
+  long days;
+
+  if (args[0]->null_value || args[1]->null_value ||
+      yearnr < 0 || daynr <= 0)
+    goto null_date;
+
+  days= calc_daynr(yearnr,1,1) + daynr - 1;
+  if (days > 0 || days < MAX_DAY_NUMBER) // Day number from year 0 to 9999-12-31
+  {
+    null_value=0;
+    get_date_from_daynr(days,&l_time.year,&l_time.month,&l_time.day);
+    if (make_datetime(str, &l_time, DATE_ONLY))
+      return str;
+  }
+
+null_date:
+  null_value=1;
+  return 0;
+}
+
+
+void Item_func_add_time::fix_length_and_dec()
+{
+  enum_field_types arg0_field_type;
+  decimals=0;
+  max_length=26*MY_CHARSET_BIN_MB_MAXLEN;
+
+  /*
+    The field type for the result of an Item_func_add_time function is defined as
+    follows:
+
+    - If first arg is a MYSQL_TYPE_DATETIME or MYSQL_TYPE_TIMESTAMP 
+      result is MYSQL_TYPE_DATETIME
+    - If first arg is a MYSQL_TYPE_TIME result is MYSQL_TYPE_TIME
+    - Otherwise the result is MYSQL_TYPE_STRING
+  */
+
+  cached_field_type= MYSQL_TYPE_STRING;
+  arg0_field_type= args[0]->field_type();
+  if (arg0_field_type == MYSQL_TYPE_DATE ||
+      arg0_field_type == MYSQL_TYPE_DATETIME ||
+      arg0_field_type == MYSQL_TYPE_TIMESTAMP)
+    cached_field_type= MYSQL_TYPE_DATETIME;
+  else if (arg0_field_type == MYSQL_TYPE_TIME)
+    cached_field_type= MYSQL_TYPE_TIME;
+}
+
+/*
+  ADDTIME(t,a) and SUBTIME(t,a) are time functions that calculate a time/datetime value 
+
+  t: time_or_datetime_expression
+  a: time_expression
+  
+  Result: Time value or datetime value
+*/
+
+String *Item_func_add_time::val_str(String *str)
+{
+  TIME l_time1, l_time2, l_time3;
+  bool is_time= 0;
+  long microseconds, seconds, days= 0;
+  int l_sign= sign;
+
+  null_value=0;
+  l_time3.neg= 0;
+  if (is_date)                        // TIMESTAMP function
+  {
+    if (get_arg0_date(&l_time1,1) || 
+        args[1]->get_time(&l_time2) ||
+        l_time1.time_type == TIMESTAMP_TIME || 
+        l_time2.time_type != TIMESTAMP_TIME)
+      goto null_date;
+  }
+  else                                // ADDTIME function
+  {
+    if (args[0]->get_time(&l_time1) || 
+        args[1]->get_time(&l_time2) ||
+        l_time2.time_type == TIMESTAMP_FULL)
+      goto null_date;
+    is_time= (l_time1.time_type == TIMESTAMP_TIME);
+    if (is_time && (l_time2.neg == l_time1.neg && l_time1.neg))
+      l_time3.neg= 1;
+  }
+  if (l_time1.neg != l_time2.neg)
+    l_sign= -l_sign;
+
+  microseconds= l_time1.second_part + l_sign*l_time2.second_part;
+  seconds= (l_time1.hour*3600L + l_time1.minute*60L + l_time1.second +
+	    (l_time2.day*86400L + l_time2.hour*3600L +
+	     l_time2.minute*60L + l_time2.second)*l_sign);
+  if (is_time)
+    seconds+= l_time1.day*86400L;
+  else
+    days+= calc_daynr((uint) l_time1.year,(uint) l_time1.month, (uint) l_time1.day);
+  seconds= seconds + microseconds/1000000L;
+  microseconds= microseconds%1000000L;
+  days+= seconds/86400L;
+  seconds= seconds%86400L;
+
+  if (microseconds < 0)
+  {
+    microseconds+= 1000000L;
+    seconds--;
+  }
+  if (seconds < 0)
+  {
+    days+= seconds/86400L - 1;
+    seconds+= 86400L;
+  }
+  if (days < 0)
+  {
+    if (!is_time)
+      goto null_date;
+    if (microseconds)
+    {
+      microseconds= 1000000L - microseconds;
+      seconds++; 
+    }
+    seconds= 86400L - seconds;
+    days= -(++days);
+    l_time3.neg= 1;
+  }
+
+  calc_time_from_sec(&l_time3, seconds, microseconds);
+  if (!is_time)
+  {
+    get_date_from_daynr(days,&l_time3.year,&l_time3.month,&l_time3.day);
+    if (l_time3.day &&
+	make_datetime(str, &l_time3,
+	              l_time1.second_part || l_time2.second_part ?
+	              DATE_TIME_MICROSECOND : DATE_TIME))
+      return str;
+    goto null_date;
+  }
+
+  l_time3.hour+= days*24;
+  if (make_datetime(str, &l_time3,
+	    l_time1.second_part || l_time2.second_part ?
+	    TIME_MICROSECOND : TIME_ONLY))
+    return str;
+
+null_date:
+  null_value=1;
+  return 0;
+}
+
+/*
+  TIMEDIFF(t,s) is a time function that calculates the 
+  time value between a start and end time.
+
+  t and s: time_or_datetime_expression
+  Result: Time value
+*/
+
+String *Item_func_timediff::val_str(String *str)
+{
+  longlong seconds;
+  long microseconds;
+  long days;
+  int l_sign= 1;
+  TIME l_time1 ,l_time2, l_time3;
+
+  null_value= 0;  
+  if (args[0]->get_time(&l_time1) ||
+      args[1]->get_time(&l_time2) ||
+      l_time1.time_type != l_time2.time_type)
+    goto null_date;
+
+  if (l_time1.neg != l_time2.neg)
+    l_sign= -l_sign;
+
+  if (l_time1.time_type == TIMESTAMP_TIME)  // Time value
+    days= l_time1.day - l_sign*l_time2.day;
+  else                                      // DateTime value
+    days= (calc_daynr((uint) l_time1.year,
+		      (uint) l_time1.month,
+		      (uint) l_time1.day) - 
+	   l_sign*calc_daynr((uint) l_time2.year,
+			     (uint) l_time2.month,
+			     (uint) l_time2.day));
+
+  microseconds= l_time1.second_part - l_sign*l_time2.second_part;
+  seconds= ((longlong) days*86400L + l_time1.hour*3600L + 
+	    l_time1.minute*60L + l_time1.second + microseconds/1000000L -
+	    (longlong)l_sign*(l_time2.hour*3600L+l_time2.minute*60L+l_time2.second));
+
+  l_time3.neg= 0;
+  if (seconds < 0)
+  {
+    seconds= -seconds;
+    l_time3.neg= 1;
+  }
+  else if (seconds == 0 && microseconds < 0)
+  {
+    microseconds= -microseconds;
+    l_time3.neg= 1;
+  }
+  if (microseconds < 0)
+  {
+    microseconds+= 1000000L;
+    seconds--;
+  }
+  if ((l_time2.neg == l_time1.neg) && l_time1.neg)
+    l_time3.neg= l_time3.neg ? 0 : 1;
+
+  calc_time_from_sec(&l_time3, seconds, microseconds);
+  if (make_datetime(str, &l_time3, 
+	    l_time1.second_part || l_time2.second_part ?
+	    TIME_MICROSECOND : TIME_ONLY))
+    return str;
+
+null_date:
+  null_value=1;
+  return 0;
+}
+
+/*
+  MAKETIME(h,m,s) is a time function that calculates a time value 
+  from the total number of hours, minutes, and seconds.
+  Result: Time value
+*/
+
+String *Item_func_maketime::val_str(String *str)
+{
+  TIME ltime;
+
+  long hour= args[0]->val_int();
+  long minute= args[1]->val_int();
+  long second= args[2]->val_int();
+
+  if ((null_value=(args[0]->null_value || 
+		   args[1]->null_value ||
+		   args[2]->null_value || 
+		   minute > 59 || minute < 0 || 
+		   second > 59 || second < 0)))
+    goto null_date;
+
+  ltime.neg= 0;
+  if (hour < 0)
+  {
+    ltime.neg= 1;
+    hour= -hour;
+  }
+  ltime.hour= (ulong)hour;
+  ltime.minute= (ulong)minute;
+  ltime.second= (ulong)second;
+  if (make_datetime(str, &ltime, TIME_ONLY))
+    return str;
+
+null_date:
+    return 0;
+}
+
+/*
+  MICROSECOND(a) is a function ( extraction) that extracts the microseconds from a.
+
+  a: Datetime or time value
+  Result: int value
+*/
+longlong Item_func_microsecond::val_int()
+{
+  TIME ltime;
+  if (!get_arg0_time(&ltime))
+    return ltime.second_part;
+  return 0;
+}
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 87563cf9f47..20b95f8e22d 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -478,9 +478,10 @@ public:
 enum interval_type
 {
   INTERVAL_YEAR, INTERVAL_MONTH, INTERVAL_DAY, INTERVAL_HOUR, INTERVAL_MINUTE,
-  INTERVAL_SECOND, INTERVAL_YEAR_MONTH, INTERVAL_DAY_HOUR, INTERVAL_DAY_MINUTE,
-  INTERVAL_DAY_SECOND, INTERVAL_HOUR_MINUTE, INTERVAL_HOUR_SECOND,
-  INTERVAL_MINUTE_SECOND
+  INTERVAL_SECOND, INTERVAL_MICROSECOND ,INTERVAL_YEAR_MONTH, INTERVAL_DAY_HOUR,
+  INTERVAL_DAY_MINUTE, INTERVAL_DAY_SECOND, INTERVAL_HOUR_MINUTE,
+  INTERVAL_HOUR_SECOND, INTERVAL_MINUTE_SECOND, INTERVAL_DAY_MICROSECOND,
+  INTERVAL_HOUR_MICROSECOND, INTERVAL_MINUTE_MICROSECOND, INTERVAL_SECOND_MICROSECOND
 };
 
 
@@ -556,6 +557,8 @@ class Item_date_typecast :public Item_typecast
 {
 public:
   Item_date_typecast(Item *a) :Item_typecast(a) {}
+  String *val_str(String *str);
+  bool get_date(TIME *ltime, bool fuzzy_date);
   const char *func_name() const { return "date"; }
   enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
   Field *tmp_table_field() { return result_field; }
@@ -570,6 +573,8 @@ class Item_time_typecast :public Item_typecast
 {
 public:
   Item_time_typecast(Item *a) :Item_typecast(a) {}
+  String *val_str(String *str);
+  bool get_time(TIME *ltime);
   const char *func_name() const { return "time"; }
   enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
   Field *tmp_table_field() { return result_field; }
@@ -584,6 +589,7 @@ class Item_datetime_typecast :public Item_typecast
 {
 public:
   Item_datetime_typecast(Item *a) :Item_typecast(a) {}
+  String *val_str(String *str);
   const char *func_name() const { return "datetime"; }
   enum_field_types field_type() const { return MYSQL_TYPE_DATETIME; }
   Field *tmp_table_field() { return result_field; }
@@ -592,3 +598,106 @@ public:
     return (new Field_datetime(maybe_null, name, t_arg, default_charset()));
   }
 };
+
+class Item_func_makedate :public Item_str_func
+{
+public:
+  Item_func_makedate(Item *a,Item *b) :Item_str_func(a,b) {}
+  String *val_str(String *str);
+  const char *func_name() const { return "makedate"; }
+  enum_field_types field_type() const { return MYSQL_TYPE_DATE; }
+  void fix_length_and_dec()
+  { 
+    decimals=0;
+    max_length=8*MY_CHARSET_BIN_MB_MAXLEN;
+  }
+  Field *tmp_table_field() { return result_field; }
+  Field *tmp_table_field(TABLE *t_arg)
+  {
+    return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
+  }
+};
+
+
+class Item_func_add_time :public Item_str_func
+{
+  const bool is_date;
+  int sign;
+  enum_field_types cached_field_type;
+
+public:
+  Item_func_add_time(Item *a, Item *b, bool type_arg, bool neg_arg)
+    :Item_str_func(a, b), is_date(type_arg) { sign= neg_arg ? -1 : 1; }
+  String *val_str(String *str);
+  const char *func_name() const { return "addtime"; }
+  enum_field_types field_type() const { return cached_field_type; }
+  void fix_length_and_dec();
+
+/*
+  TODO:
+       Change this when we support 
+       microseconds in TIME/DATETIME
+*/
+  Field *tmp_table_field() { return result_field; }
+  Field *tmp_table_field(TABLE *t_arg)
+  {
+    if (cached_field_type == MYSQL_TYPE_TIME)
+      return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
+    else if (cached_field_type == MYSQL_TYPE_DATETIME)
+      return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
+    return (new Field_string(max_length, maybe_null, name, t_arg, &my_charset_bin));
+  }
+};
+
+class Item_func_timediff :public Item_str_func
+{
+public:
+  Item_func_timediff(Item *a, Item *b)
+    :Item_str_func(a, b) {}
+  String *val_str(String *str);
+  const char *func_name() const { return "timediff"; }
+  enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+  void fix_length_and_dec()
+  {
+    decimals=0;
+    max_length=17*MY_CHARSET_BIN_MB_MAXLEN;
+  }
+  Field *tmp_table_field() { return result_field; }
+  Field *tmp_table_field(TABLE *t_arg)
+  {
+      return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
+  }
+};
+
+class Item_func_maketime :public Item_str_func
+{
+public:
+  Item_func_maketime(Item *a, Item *b, Item *c)
+    :Item_str_func(a, b ,c) {}
+  String *val_str(String *str);
+  const char *func_name() const { return "maketime"; }
+  enum_field_types field_type() const { return MYSQL_TYPE_TIME; }
+  void fix_length_and_dec()
+  {
+    decimals=0;
+    max_length=8*MY_CHARSET_BIN_MB_MAXLEN;
+  }
+  Field *tmp_table_field() { return result_field; }
+  Field *tmp_table_field(TABLE *t_arg)
+  {
+      return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
+  }
+};
+
+class Item_func_microsecond :public Item_int_func
+{
+public:
+  Item_func_microsecond(Item *a) :Item_int_func(a) {}
+  longlong val_int();
+  const char *func_name() const { return "microsecond"; }
+  void fix_length_and_dec() 
+  { 
+    decimals=0;
+    maybe_null=1;
+  }
+};
diff --git a/sql/lex.h b/sql/lex.h
index bb6e7a81ab4..c2860f4551a 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -116,6 +116,7 @@ static SYMBOL symbols[] = {
   { "DATETIME",		SYM(DATETIME),0,0},
   { "DAY",		SYM(DAY_SYM),0,0},
   { "DAY_HOUR",		SYM(DAY_HOUR_SYM),0,0},
+  { "DAY_MICROSECOND",	SYM(DAY_MICROSECOND_SYM),0,0},
   { "DAY_MINUTE",	SYM(DAY_MINUTE_SYM),0,0},
   { "DAY_SECOND",	SYM(DAY_SECOND_SYM),0,0},
   { "DEC",		SYM(DECIMAL_SYM),0,0},
@@ -186,6 +187,7 @@ static SYMBOL symbols[] = {
   { "HELP",		SYM(HELP_SYM),0,0},
   { "HIGH_PRIORITY",	SYM(HIGH_PRIORITY),0,0},
   { "HOUR",		SYM(HOUR_SYM),0,0},
+  { "HOUR_MICROSECOND",	SYM(HOUR_MICROSECOND_SYM),0,0},
   { "HOUR_MINUTE",	SYM(HOUR_MINUTE_SYM),0,0},
   { "HOUR_SECOND",	SYM(HOUR_SECOND_SYM),0,0},
   { "HOSTS",		SYM(HOSTS_SYM),0,0},
@@ -259,9 +261,11 @@ static SYMBOL symbols[] = {
   { "MERGE",		SYM(MERGE_SYM),0,0},
   { "MEDIUM",		SYM(MEDIUM_SYM),0,0},
   { "MEMORY",		SYM(MEMORY_SYM),0,0},
+  { "MICROSECOND",	SYM(MICROSECOND_SYM),0,0},
   { "MIDDLEINT",	SYM(MEDIUMINT),0,0},	/* For powerbuilder */
   { "MIN_ROWS",		SYM(MIN_ROWS),0,0},
   { "MINUTE",		SYM(MINUTE_SYM),0,0},
+  { "MINUTE_MICROSECOND", SYM(MINUTE_MICROSECOND_SYM),0,0},
   { "MINUTE_SECOND",	SYM(MINUTE_SECOND_SYM),0,0},
   { "MOD",		SYM(MOD_SYM),0,0},
   { "MODE",		SYM(MODE_SYM),0,0},
@@ -337,6 +341,7 @@ static SYMBOL symbols[] = {
   { "ROWS",		SYM(ROWS_SYM),0,0},
   { "RTREE",		SYM(RTREE_SYM),0,0},
   { "SECOND",		SYM(SECOND_SYM),0,0},
+  { "SECOND_MICROSECOND", SYM(SECOND_MICROSECOND_SYM),0,0},
   { "SEPARATOR",	SYM(SEPARATOR_SYM),0,0},
   { "SELECT",		SYM(SELECT_SYM),0,0},
   { "SERIAL",		SYM(SERIAL_SYM),0,0},
@@ -427,7 +432,8 @@ static SYMBOL symbols[] = {
 static SYMBOL sql_functions[] = {
   { "ABS",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_abs)},
   { "ACOS",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_acos)},
-  { "ADDDATE",		SYM(DATE_ADD_INTERVAL),0,0},
+  { "ADDDATE",		SYM(ADDDATE_SYM),0,0},
+  { "ADDTIME",		SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_addtime)},
   { "AES_ENCRYPT",      SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_aes_encrypt)},
   { "AES_DECRYPT",      SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_aes_decrypt)},
   { "AREA",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_area)},
@@ -470,6 +476,7 @@ static SYMBOL sql_functions[] = {
   { "CURDATE",		SYM(CURDATE),0,0},
   { "CURTIME",		SYM(CURTIME),0,0},
   { "DATE_ADD",		SYM(DATE_ADD_INTERVAL),0,0},
+  { "DATEDIFF",		SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_datediff)},
   { "DATE_FORMAT",	SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_date_format)},
   { "DATE_SUB",		SYM(DATE_SUB_INTERVAL),0,0},
   { "DAYNAME",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_dayname)},
@@ -545,6 +552,8 @@ static SYMBOL sql_functions[] = {
   { "LPAD",		SYM(FUNC_ARG3),0,CREATE_FUNC(create_func_lpad)},
   { "LTRIM",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_ltrim)},
   { "MAKE_SET",		SYM(MAKE_SET_SYM),0,0},
+  { "MAKEDATE",		SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_makedate)},
+  { "MAKETIME",		SYM(FUNC_ARG3),0,CREATE_FUNC(create_func_maketime)},
   { "MASTER_POS_WAIT",	SYM(MASTER_POS_WAIT),0,0},
   { "MAX",		SYM(MAX_SYM),0,0},
   { "MBRCONTAINS",	SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_contains)},
@@ -605,7 +614,7 @@ static SYMBOL sql_functions[] = {
   { "RTRIM",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_rtrim)},
   { "SEC_TO_TIME",	SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_sec_to_time)},
   { "SESSION_USER",	SYM(USER),0,0},
-  { "SUBDATE",		SYM(DATE_SUB_INTERVAL),0,0},
+  { "SUBDATE",		SYM(SUBDATE_SYM),0,0},
   { "SIGN",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_sign)},
   { "SIN",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_sin)},
   { "SHA",              SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_sha)},
@@ -618,14 +627,17 @@ static SYMBOL sql_functions[] = {
   { "STD",		SYM(STD_SYM),0,0},
   { "STDDEV",		SYM(STD_SYM),0,0},
   { "STRCMP",		SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_strcmp)},
+  { "SUBSTR",	  	SYM(SUBSTRING),0,0},
   { "SUBSTRING",	SYM(SUBSTRING),0,0},
   { "SUBSTRING_INDEX",	SYM(SUBSTRING_INDEX),0,0},
+  { "SUBTIME",          SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_subtime)},
   { "SUM",		SYM(SUM_SYM),0,0},
   { "SYSDATE",		SYM(NOW_SYM),0,0},
   { "SYSTEM_USER",	SYM(USER),0,0},
   { "TAN",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_tan)},
   { "TIME_FORMAT",	SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_time_format)},
   { "TIME_TO_SEC",	SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_time_to_sec)},
+  { "TIMEDIFF",         SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_timediff)},
   { "TO_DAYS",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_to_days)},
   { "TOUCHES",		SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_touches)},
   { "TRIM",		SYM(TRIM),0,0},
@@ -639,6 +651,7 @@ static SYMBOL sql_functions[] = {
   { "VERSION",		SYM(FUNC_ARG0),0,CREATE_FUNC(create_func_version)},
   { "WEEK",		SYM(WEEK_SYM),0,0},
   { "WEEKDAY",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_weekday)},
+  { "WEEKOFYEAR",	SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_weekofyear)},
   { "WITHIN",		SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_within)},
   { "X",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_x)},
   { "Y",		SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_y)},
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index 8d038aba44d..c9ec7e06f5b 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -224,6 +224,8 @@ extern CHARSET_INFO *national_charset_info, *table_alias_charset;
 
 #define RAID_BLOCK_SIZE 1024
 
+#define MY_CHARSET_BIN_MB_MAXLEN 1
+
 #ifdef EXTRA_DEBUG
 /*
   Sync points allow us to force the server to reach a certain line of code
@@ -533,6 +535,8 @@ int mysqld_show_fields(THD *thd,TABLE_LIST *table, const char *wild,
 		       bool verbose);
 int mysqld_show_keys(THD *thd, TABLE_LIST *table);
 int mysqld_show_logs(THD *thd);
+void append_identifier(THD *thd, String *packet, const char *name,
+		       uint length);
 void mysqld_list_fields(THD *thd,TABLE_LIST *table, const char *wild);
 int mysqld_dump_create_info(THD *thd, TABLE *table, int fd = -1);
 int mysqld_show_create(THD *thd, TABLE_LIST *table_list);
@@ -556,6 +560,7 @@ void free_prep_stmt(PREP_STMT *stmt, TREE_FREE mode, void *not_used);
 bool mysql_stmt_prepare(THD *thd, char *packet, uint packet_length);
 void mysql_stmt_execute(THD *thd, char *packet);
 void mysql_stmt_free(THD *thd, char *packet);
+void mysql_stmt_reset(THD *thd, char *packet);
 void mysql_stmt_get_longdata(THD *thd, char *pos, ulong packet_length);
 int check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
 			List<Item> &values, ulong counter);
@@ -769,6 +774,7 @@ extern rw_lock_t	LOCK_grant;
 extern pthread_cond_t COND_refresh, COND_thread_count, COND_manager;
 extern pthread_attr_t connection_attrib;
 extern I_List<THD> threads;
+extern I_List<NAMED_LIST> key_caches;
 extern MY_BITMAP temp_pool;
 extern DATE_FORMAT dayord;
 extern String empty_string;
@@ -850,6 +856,7 @@ longlong str_to_datetime(const char *str,uint length,bool fuzzy_date);
 timestamp_type str_to_TIME(const char *str, uint length, TIME *l_time,
 			   bool fuzzy_date);
 void localtime_to_TIME(TIME *to, struct tm *from);
+void calc_time_from_sec(TIME *to, long seconds, long microseconds);
 
 int test_if_number(char *str,int *res,bool allow_wildcards);
 void change_byte(byte *,uint,char,char);
@@ -902,9 +909,10 @@ extern void sql_cache_free();
 extern int sql_cache_hit(THD *thd, char *inBuf, uint length);
 
 /* item.cc */
-Item *get_system_var(enum_var_type var_type, LEX_STRING name);
-Item *get_system_var(enum_var_type var_type, const char *var_name, uint length,
-		     const char *item_name);
+Item *get_system_var(THD *thd, enum_var_type var_type, LEX_STRING name,
+		     LEX_STRING component);
+Item *get_system_var(THD *thd, enum_var_type var_type, const char *var_name,
+		     uint length, const char *item_name);
 /* log.cc */
 bool flush_error_log(void);
 
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index 9da15adcf68..52b1b26d7a9 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -312,11 +312,12 @@ const char *sql_mode_str="OFF";
 
 FILE *bootstrap_file;
 
-I_List <i_string_pair> replicate_rewrite_db;
+I_List<i_string_pair> replicate_rewrite_db;
 I_List<i_string> replicate_do_db, replicate_ignore_db;
 // allow the user to tell us which db to replicate and which to ignore
 I_List<i_string> binlog_do_db, binlog_ignore_db;
 I_List<THD> threads,thread_cache;
+I_List<NAMED_LIST> key_caches;
 
 struct system_variables global_system_variables;
 struct system_variables max_system_variables;
@@ -875,6 +876,7 @@ void clean_up(bool print_message)
 #endif
   (void) ha_panic(HA_PANIC_CLOSE);	/* close all tables and logs */
   end_key_cache(&dflt_keycache,1);
+  delete_elements(&key_caches, free_key_cache);
   end_thr_alarm(1);			/* Free allocated memory */
 #ifdef USE_RAID
   end_raid();
@@ -1818,15 +1820,28 @@ extern "C" int my_message_sql(uint error, const char *str,
 {
   THD *thd;
   DBUG_ENTER("my_message_sql");
-  DBUG_PRINT("error",("Message: '%s'",str));
-  if ((thd=current_thd))
+  DBUG_PRINT("error", ("Message: '%s'", str));
+  if ((thd= current_thd))
   {
-    NET *net= &thd->net;
-    net->report_error= 1;
-    if (!net->last_error[0])			// Return only first message
+    /*
+      thd->lex.current_select equel to zero if lex structure is not inited
+      (not query command (COM_QUERY))
+    */
+    if (thd->lex.current_select &&
+	thd->lex.current_select->no_error && !thd->is_fatal_error)
+    {
+      DBUG_PRINT("error", ("above error converted to warning"));
+      push_warning(thd, MYSQL_ERROR::WARN_LEVEL_ERROR, error, str);
+    }
+    else
     {
-      strmake(net->last_error,str,sizeof(net->last_error)-1);
-      net->last_errno=error ? error : ER_UNKNOWN_ERROR;
+      NET *net= &thd->net;
+      net->report_error= 1;
+      if (!net->last_error[0])			// Return only first message
+      {
+	strmake(net->last_error, str, sizeof(net->last_error)-1);
+	net->last_errno= error ? error : ER_UNKNOWN_ERROR;
+      }
     }
   }
   else
@@ -1988,22 +2003,6 @@ 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));
 
-#ifdef HAVE_PTHREAD_ATTR_GETSTACKSIZE
-  {
-    /* Retrieve used stack size;  Needed for checking stack overflows */
-    size_t stack_size= 0;
-    pthread_attr_getstacksize(&connection_attrib, &stack_size);
-    /* We must check if stack_size = 0 as Solaris 2.9 can return 0 here */
-    if (stack_size && stack_size < thread_stack)
-    {
-      if (global_system_variables.log_warnings)
-	sql_print_error("Warning: Asked for %ld thread stack, but got %ld",
-			thread_stack, stack_size);
-      thread_stack= stack_size;
-    }
-  }
-#endif
-
 #if defined( SET_RLIMIT_NOFILE) || defined( OS2)
   /* connections and databases needs lots of files */
   {
@@ -2353,6 +2352,21 @@ int main(int argc, char **argv)
   if (!(opt_specialflag & SPECIAL_NO_PRIOR))
     my_pthread_setprio(pthread_self(),CONNECT_PRIOR);
   pthread_attr_setstacksize(&connection_attrib,thread_stack);
+#ifdef HAVE_PTHREAD_ATTR_GETSTACKSIZE
+  {
+    /* Retrieve used stack size;  Needed for checking stack overflows */
+    size_t stack_size= 0;
+    pthread_attr_getstacksize(&connection_attrib, &stack_size);
+    /* We must check if stack_size = 0 as Solaris 2.9 can return 0 here */
+    if (stack_size && stack_size < thread_stack)
+    {
+      if (global_system_variables.log_warnings)
+	sql_print_error("Warning: Asked for %ld thread stack, but got %ld",
+			thread_stack, stack_size);
+      thread_stack= stack_size;
+    }
+  }
+#endif
   (void) thr_setconcurrency(concurrency);	// 10 by default
 
   select_thread=pthread_self();
@@ -4091,7 +4105,7 @@ replicating a LOAD DATA INFILE command.",
    REQUIRED_ARG, 128*1024L, IO_SIZE*2+MALLOC_OVERHEAD, ~0L, MALLOC_OVERHEAD,
    IO_SIZE, 0},
   {"key_buffer_size", OPT_KEY_BUFFER_SIZE,
-   "The size of the buffer used for index blocks. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common.",
+   "The size of the buffer used for index blocks for MyISAM tables. Increase this to get better index handling (for all reads and multiple writes) to as much as you can afford; 64M on a 256M machine that mainly runs MySQL is quite common.",
    (gptr*) &keybuff_size, (gptr*) &keybuff_size, 0,
    (enum get_opt_var_type) (GET_ULL | GET_ASK_ADDR),
    REQUIRED_ARG, KEY_CACHE_SIZE, MALLOC_OVERHEAD, (long) ~0, MALLOC_OVERHEAD,
@@ -4667,6 +4681,9 @@ static void mysql_init_variables(void)
   my_bind_addr = htonl(INADDR_ANY);
   threads.empty();
   thread_cache.empty();
+  key_caches.empty();
+  if (!get_or_create_key_cache("default", 7))
+    exit(1);
 
   /* Initialize structures that is used when processing options */
   replicate_rewrite_db.empty();
@@ -5297,16 +5314,30 @@ get_one_option(int optid, const struct my_option *opt __attribute__((unused)),
   }
   return 0;
 }
-	/* Initiates DEBUG - but no debugging here ! */
 
 
 extern "C" gptr *
-mysql_getopt_value(char *keyname, uint key_length,
+mysql_getopt_value(const char *keyname, uint key_length,
 		   const struct my_option *option)
 {
+  if (!key_length)
+  {
+    keyname= "default";
+    key_length= 7;
+  }
+  switch (option->id) {
+  case OPT_KEY_BUFFER_SIZE:
+  {
+    KEY_CACHE *key_cache;
+    if (!(key_cache= get_or_create_key_cache(keyname, key_length)))
+      exit(1);
+    return (gptr*) &key_cache->size;
+  }
+  }
   return option->value;
 }
 
+
 static void get_options(int argc,char **argv)
 {
   int ho_error;
@@ -5354,6 +5385,8 @@ static void get_options(int argc,char **argv)
   table_alias_charset= (lower_case_table_names ?
 			files_charset_info :
 			&my_charset_bin);
+  /* QQ To be deleted when we have key cache variables in a struct */
+  keybuff_size= (((KEY_CACHE *) find_named(&key_caches, "default", 7))->size);
 }
 
 
@@ -5568,6 +5601,6 @@ template class I_List<THD>;
 template class I_List_iterator<THD>;
 template class I_List<i_string>;
 template class I_List<i_string_pair>;
-
+template class I_List<NAMED_LIST>;
 FIX_GCC_LINKING_PROBLEM
 #endif
diff --git a/sql/net_serv.cc b/sql/net_serv.cc
index eb4d76bbf6e..fd5e4f1d71a 100644
--- a/sql/net_serv.cc
+++ b/sql/net_serv.cc
@@ -959,5 +959,5 @@ my_net_read(NET *net)
   return len;
 }
 
-#endif /* EMBEDDED_LIBRARY */
+#endif /* #ifndef EMBEDDED_LIBRARY */
 
diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index a3af2bde449..e982b211b89 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -832,10 +832,10 @@ static SEL_TREE *get_mm_tree(PARAM *param,COND *cond)
       Field *field=((Item_field*) (func->key_item()))->field;
       Item_result cmp_type=field->cmp_type();
       tree= get_mm_parts(param,field,Item_func::EQ_FUNC,
-			 func->arguments()[0],cmp_type);
+			 func->arguments()[1],cmp_type);
       if (!tree)
 	DBUG_RETURN(tree);			// Not key field
-      for (uint i=1 ; i < func->argument_count(); i++)
+      for (uint i=2 ; i < func->argument_count(); i++)
       {
 	SEL_TREE *new_tree=get_mm_parts(param,field,Item_func::EQ_FUNC,
 					func->arguments()[i],cmp_type);
@@ -2652,7 +2652,7 @@ int QUICK_SELECT_DESC::get_next()
     else
     {
       DBUG_ASSERT(range->flag & NEAR_MAX || range_reads_after_key(range));
-#ifdef NOT_IMPLEMENTED_YET
+#ifndef NOT_IMPLEMENTED_YET
       result=file->index_read(record, (byte*) range->max_key,
 			      range->max_length,
 			      ((range->flag & NEAR_MAX) ?
diff --git a/sql/protocol.cc b/sql/protocol.cc
index 1d730836d6e..1b9256c7723 100644
--- a/sql/protocol.cc
+++ b/sql/protocol.cc
@@ -572,7 +572,7 @@ bool Protocol::send_fields(List<Item> *list, uint flag)
 #endif
   }
 
-  send_eof(thd);
+  send_eof(thd, 1);
   DBUG_RETURN(prepare_for_send(list));
 
 err:
@@ -823,6 +823,13 @@ bool Protocol_simple::store(Field *field)
 }
 
 
+/*
+   TODO:
+        Second_part format ("%06") needs to change when 
+        we support 0-6 decimals for time.
+*/
+
+
 bool Protocol_simple::store(TIME *tm)
 {
 #ifndef DEBUG_OFF
@@ -840,6 +847,8 @@ bool Protocol_simple::store(TIME *tm)
 			   (int) tm->hour,
 			   (int) tm->minute,
 			   (int) tm->second));
+  if (tm->second_part)
+    length+= my_sprintf(buff+length,(buff+length, ".%06d", (int)tm->second_part));
   return net_store_data((char*) buff, length);
 }
 
@@ -861,6 +870,12 @@ bool Protocol_simple::store_date(TIME *tm)
 }
 
 
+/*
+   TODO:
+        Second_part format ("%06") needs to change when 
+        we support 0-6 decimals for time.
+*/
+
 bool Protocol_simple::store_time(TIME *tm)
 {
 #ifndef DEBUG_OFF
@@ -876,6 +891,8 @@ bool Protocol_simple::store_time(TIME *tm)
 			   (long) day*24L+(long) tm->hour,
 			   (int) tm->minute,
 			   (int) tm->second));
+  if (tm->second_part)
+    length+= my_sprintf(buff+length,(buff+length, ".%06d", (int)tm->second_part));
   return net_store_data((char*) buff, length);
 }
 
diff --git a/sql/set_var.cc b/sql/set_var.cc
index a281fac530a..cb6c875d513 100644
--- a/sql/set_var.cc
+++ b/sql/set_var.cc
@@ -1,4 +1,4 @@
-/* Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
+/* Copyright (C) 2000-2003 MySQL AB
 
    This program is free software; you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
@@ -86,9 +86,9 @@ static void fix_net_retry_count(THD *thd, enum_var_type type);
 static void fix_max_join_size(THD *thd, enum_var_type type);
 static void fix_query_cache_size(THD *thd, enum_var_type type);
 static void fix_query_cache_min_res_unit(THD *thd, enum_var_type type);
-static void fix_key_buffer_size(THD *thd, enum_var_type type);
 static void fix_myisam_max_extra_sort_file_size(THD *thd, enum_var_type type);
 static void fix_myisam_max_sort_file_size(THD *thd, enum_var_type type);
+static KEY_CACHE *create_key_cache(const char *name, uint length);
 void fix_sql_mode_var(THD *thd, enum_var_type type);
 static byte *get_error_count(THD *thd);
 static byte *get_warning_count(THD *thd);
@@ -136,9 +136,7 @@ sys_var_thd_ulong	sys_interactive_timeout("interactive_timeout",
 						&SV::net_interactive_timeout);
 sys_var_thd_ulong	sys_join_buffer_size("join_buffer_size",
 					     &SV::join_buff_size);
-sys_var_ulonglong_ptr	sys_key_buffer_size("key_buffer_size",
-					    &keybuff_size,
-					    fix_key_buffer_size);
+sys_var_key_buffer_size	sys_key_buffer_size("key_buffer_size");
 sys_var_bool_ptr	sys_local_infile("local_infile",
 					 &opt_local_infile);
 sys_var_thd_bool	sys_log_warnings("log_warnings", &SV::log_warnings);
@@ -793,12 +791,6 @@ static void fix_query_cache_min_res_unit(THD *thd, enum_var_type type)
 #endif
 
 
-static void fix_key_buffer_size(THD *thd, enum_var_type type)
-{
-  ha_resize_key_cache();
-}
-
-
 void fix_delay_key_write(THD *thd, enum_var_type type)
 {
   switch ((enum_delay_key_write) delay_key_write_options) {
@@ -870,7 +862,7 @@ bool sys_var_enum::update(THD *thd, set_var *var)
 }
 
 
-byte *sys_var_enum::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_enum::value_ptr(THD *thd, enum_var_type type, LEX_STRING *base)
 {
   return (byte*) enum_names->type_names[*value];
 }
@@ -906,7 +898,8 @@ void sys_var_thd_ulong::set_default(THD *thd, enum_var_type type)
 }
 
 
-byte *sys_var_thd_ulong::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_thd_ulong::value_ptr(THD *thd, enum_var_type type,
+				   LEX_STRING *base)
 {
   if (type == OPT_GLOBAL)
     return (byte*) &(global_system_variables.*offset);
@@ -951,7 +944,8 @@ void sys_var_thd_ha_rows::set_default(THD *thd, enum_var_type type)
 }
 
 
-byte *sys_var_thd_ha_rows::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_thd_ha_rows::value_ptr(THD *thd, enum_var_type type,
+				     LEX_STRING *base)
 {
   if (type == OPT_GLOBAL)
     return (byte*) &(global_system_variables.*offset);
@@ -994,7 +988,8 @@ void sys_var_thd_ulonglong::set_default(THD *thd, enum_var_type type)
 }
 
 
-byte *sys_var_thd_ulonglong::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_thd_ulonglong::value_ptr(THD *thd, enum_var_type type,
+				       LEX_STRING *base)
 {
   if (type == OPT_GLOBAL)
     return (byte*) &(global_system_variables.*offset);
@@ -1021,7 +1016,8 @@ void sys_var_thd_bool::set_default(THD *thd,  enum_var_type type)
 }
 
 
-byte *sys_var_thd_bool::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_thd_bool::value_ptr(THD *thd, enum_var_type type,
+				  LEX_STRING *base)
 {
   if (type == OPT_GLOBAL)
     return (byte*) &(global_system_variables.*offset);
@@ -1115,7 +1111,7 @@ err:
     to create an item that gets the current value at fix_fields() stage.
 */
 
-Item *sys_var::item(THD *thd, enum_var_type var_type)
+Item *sys_var::item(THD *thd, enum_var_type var_type, LEX_STRING *base)
 {
   if (check_type(var_type))
   {
@@ -1131,16 +1127,16 @@ Item *sys_var::item(THD *thd, enum_var_type var_type)
   }
   switch (type()) {
   case SHOW_LONG:
-    return new Item_uint((int32) *(ulong*) value_ptr(thd, var_type));
+    return new Item_uint((int32) *(ulong*) value_ptr(thd, var_type, base));
   case SHOW_LONGLONG:
-    return new Item_int(*(longlong*) value_ptr(thd, var_type));
+    return new Item_int(*(longlong*) value_ptr(thd, var_type, base));
   case SHOW_HA_ROWS:
-    return new Item_int((longlong) *(ha_rows*) value_ptr(thd, var_type));
+    return new Item_int((longlong) *(ha_rows*) value_ptr(thd, var_type, base));
   case SHOW_MY_BOOL:
-    return new Item_int((int32) *(my_bool*) value_ptr(thd, var_type),1);
+    return new Item_int((int32) *(my_bool*) value_ptr(thd, var_type, base),1);
   case SHOW_CHAR:
   {
-    char *str= (char*) value_ptr(thd, var_type);
+    char *str= (char*) value_ptr(thd, var_type, base);
     return new Item_string(str, strlen(str), system_charset_info);
   }
   default:
@@ -1169,7 +1165,8 @@ void sys_var_thd_enum::set_default(THD *thd, enum_var_type type)
 }
 
 
-byte *sys_var_thd_enum::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_thd_enum::value_ptr(THD *thd, enum_var_type type,
+				  LEX_STRING *base)
 {
   ulong tmp= ((type == OPT_GLOBAL) ?
 	      global_system_variables.*offset :
@@ -1186,7 +1183,8 @@ bool sys_var_thd_bit::update(THD *thd, set_var *var)
 }
 
 
-byte *sys_var_thd_bit::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_thd_bit::value_ptr(THD *thd, enum_var_type type,
+				 LEX_STRING *base)
 {
   /*
     If reverse is 0 (default) return 1 if bit is set.
@@ -1249,6 +1247,7 @@ bool sys_var_collation::check(THD *thd, set_var *var)
   return 0;
 }
 
+
 bool sys_var_character_set::check(THD *thd, set_var *var)
 {
   CHARSET_INFO *tmp;
@@ -1274,20 +1273,24 @@ bool sys_var_character_set::check(THD *thd, set_var *var)
   return 0;
 }
 
+
 bool sys_var_character_set::update(THD *thd, set_var *var)
 {
   ci_ptr(thd,var->type)[0]= var->save_result.charset;
   return 0;
 }
 
-byte *sys_var_character_set::value_ptr(THD *thd, enum_var_type type)
+
+byte *sys_var_character_set::value_ptr(THD *thd, enum_var_type type,
+				       LEX_STRING *base)
 {
   CHARSET_INFO *cs= ci_ptr(thd,type)[0];
   return cs ? (byte*) cs->csname : (byte*) "NULL";
 }
 
 
-CHARSET_INFO ** sys_var_character_set_connection::ci_ptr(THD *thd, enum_var_type type)
+CHARSET_INFO ** sys_var_character_set_connection::ci_ptr(THD *thd,
+							 enum_var_type type)
 {
   if (type == OPT_GLOBAL)
     return &global_system_variables.collation_connection;
@@ -1295,7 +1298,9 @@ CHARSET_INFO ** sys_var_character_set_connection::ci_ptr(THD *thd, enum_var_type
     return &thd->variables.collation_connection;
 }
 
-void sys_var_character_set_connection::set_default(THD *thd, enum_var_type type)
+
+void sys_var_character_set_connection::set_default(THD *thd,
+						   enum_var_type type)
 {
  if (type == OPT_GLOBAL)
    global_system_variables.collation_connection= default_charset_info;
@@ -1304,7 +1309,8 @@ void sys_var_character_set_connection::set_default(THD *thd, enum_var_type type)
 }
 
 
-CHARSET_INFO ** sys_var_character_set_client::ci_ptr(THD *thd, enum_var_type type)
+CHARSET_INFO ** sys_var_character_set_client::ci_ptr(THD *thd,
+						     enum_var_type type)
 {
   if (type == OPT_GLOBAL)
     return &global_system_variables.character_set_client;
@@ -1312,6 +1318,7 @@ CHARSET_INFO ** sys_var_character_set_client::ci_ptr(THD *thd, enum_var_type typ
     return &thd->variables.character_set_client;
 }
 
+
 void sys_var_character_set_client::set_default(THD *thd, enum_var_type type)
 {
  if (type == OPT_GLOBAL)
@@ -1320,6 +1327,7 @@ void sys_var_character_set_client::set_default(THD *thd, enum_var_type type)
    thd->variables.character_set_client= global_system_variables.character_set_client;
 }
 
+
 CHARSET_INFO ** sys_var_character_set_results::ci_ptr(THD *thd, enum_var_type type)
 {
   if (type == OPT_GLOBAL)
@@ -1328,6 +1336,7 @@ CHARSET_INFO ** sys_var_character_set_results::ci_ptr(THD *thd, enum_var_type ty
     return &thd->variables.character_set_results;
 }
 
+
 void sys_var_character_set_results::set_default(THD *thd, enum_var_type type)
 {
  if (type == OPT_GLOBAL)
@@ -1336,6 +1345,7 @@ void sys_var_character_set_results::set_default(THD *thd, enum_var_type type)
    thd->variables.character_set_results= global_system_variables.character_set_results;
 }
 
+
 CHARSET_INFO ** sys_var_character_set_server::ci_ptr(THD *thd, enum_var_type type)
 {
   if (type == OPT_GLOBAL)
@@ -1344,6 +1354,7 @@ CHARSET_INFO ** sys_var_character_set_server::ci_ptr(THD *thd, enum_var_type typ
     return &thd->variables.character_set_server;
 }
 
+
 void sys_var_character_set_server::set_default(THD *thd, enum_var_type type)
 {
  if (type == OPT_GLOBAL)
@@ -1352,7 +1363,9 @@ void sys_var_character_set_server::set_default(THD *thd, enum_var_type type)
    thd->variables.character_set_server= global_system_variables.character_set_server;
 }
 
-CHARSET_INFO ** sys_var_character_set_database::ci_ptr(THD *thd, enum_var_type type)
+
+CHARSET_INFO ** sys_var_character_set_database::ci_ptr(THD *thd,
+						       enum_var_type type)
 {
   if (type == OPT_GLOBAL)
     return &global_system_variables.character_set_database;
@@ -1360,6 +1373,7 @@ CHARSET_INFO ** sys_var_character_set_database::ci_ptr(THD *thd, enum_var_type t
     return &thd->variables.character_set_database;
 }
 
+
 void sys_var_character_set_database::set_default(THD *thd, enum_var_type type)
 {
  if (type == OPT_GLOBAL)
@@ -1368,6 +1382,7 @@ void sys_var_character_set_database::set_default(THD *thd, enum_var_type type)
     thd->variables.character_set_database= thd->db_charset;
 }
 
+
 bool sys_var_collation_connection::update(THD *thd, set_var *var)
 {
   if (var->type == OPT_GLOBAL)
@@ -1377,7 +1392,9 @@ bool sys_var_collation_connection::update(THD *thd, set_var *var)
   return 0;
 }
 
-byte *sys_var_collation_connection::value_ptr(THD *thd, enum_var_type type)
+
+byte *sys_var_collation_connection::value_ptr(THD *thd, enum_var_type type,
+					      LEX_STRING *base)
 {
   CHARSET_INFO *cs= ((type == OPT_GLOBAL) ?
 		  global_system_variables.collation_connection :
@@ -1385,6 +1402,7 @@ byte *sys_var_collation_connection::value_ptr(THD *thd, enum_var_type type)
   return cs ? (byte*) cs->name : (byte*) "NULL";
 }
 
+
 void sys_var_collation_connection::set_default(THD *thd, enum_var_type type)
 {
  if (type == OPT_GLOBAL)
@@ -1394,6 +1412,71 @@ void sys_var_collation_connection::set_default(THD *thd, enum_var_type type)
 }
 
 
+bool sys_var_key_buffer_size::update(THD *thd, set_var *var)
+{
+  ulonglong tmp= var->value->val_int();
+  if (!base_name.length)
+  {
+    base_name.str= (char*) "default";
+    base_name.length= 7;
+  }
+  KEY_CACHE *key_cache= (KEY_CACHE*) find_named(&key_caches, base_name.str,
+						base_name.length);
+  if (!key_cache)
+  {
+    if (!tmp)					// Tried to delete cache
+      return 0;					// Ok, nothing to do
+    if (!(key_cache= create_key_cache(base_name.str,
+				      base_name.length)))
+      return 1;
+  }
+  if (!tmp)
+  {
+    /* Delete not default key caches */
+    if (base_name.length != 7 || memcpy(base_name.str, "default", 7))
+    {
+      /*
+	QQ: Here we should move tables using this key cache to default
+	key cache
+      */
+      delete key_cache;
+      return 0;
+    }
+  }
+
+  key_cache->size= (ulonglong) getopt_ull_limit_value(tmp, option_limits);
+
+  /* QQ:  Needs to be updated when we have multiple key caches */
+  keybuff_size= key_cache->size;
+  ha_resize_key_cache();
+  return 0;
+}
+
+static ulonglong zero=0;
+
+byte *sys_var_key_buffer_size::value_ptr(THD *thd, enum_var_type type,
+					 LEX_STRING *base)
+{
+  const char *name;
+  uint length;
+
+  if (!base->str)
+  {
+    name= "default";
+    length= 7;
+  }
+  else
+  {
+    name=   base->str;
+    length= base->length;
+  }
+  KEY_CACHE *key_cache= (KEY_CACHE*) find_named(&key_caches, name, length);
+  if (!key_cache)
+    return (byte*) &zero;
+  return (byte*) &key_cache->size;
+}
+  
+
 
 /*****************************************************************************
   Functions to handle SET NAMES and SET CHARACTER SET
@@ -1429,7 +1512,8 @@ void sys_var_timestamp::set_default(THD *thd, enum_var_type type)
 }
 
 
-byte *sys_var_timestamp::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_timestamp::value_ptr(THD *thd, enum_var_type type,
+				   LEX_STRING *base)
 {
   thd->sys_var_tmp.long_value= (long) thd->start_time;
   return (byte*) &thd->sys_var_tmp.long_value;
@@ -1443,7 +1527,8 @@ bool sys_var_last_insert_id::update(THD *thd, set_var *var)
 }
 
 
-byte *sys_var_last_insert_id::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_last_insert_id::value_ptr(THD *thd, enum_var_type type,
+					LEX_STRING *base)
 {
   thd->sys_var_tmp.long_value= (long) thd->insert_id();
   return (byte*) &thd->last_insert_id;
@@ -1457,7 +1542,8 @@ bool sys_var_insert_id::update(THD *thd, set_var *var)
 }
 
 
-byte *sys_var_insert_id::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_insert_id::value_ptr(THD *thd, enum_var_type type,
+				   LEX_STRING *base)
 {
   return (byte*) &thd->current_insert_id;
 }
@@ -1848,7 +1934,8 @@ int set_var_password::update(THD *thd)
  Functions to handle sql_mode
 ****************************************************************************/
 
-byte *sys_var_thd_sql_mode::value_ptr(THD *thd, enum_var_type type)
+byte *sys_var_thd_sql_mode::value_ptr(THD *thd, enum_var_type type,
+				      LEX_STRING *base)
 {
   ulong val;
   char buff[256];
@@ -1945,6 +2032,68 @@ ulong fix_sql_mode(ulong sql_mode)
 }
 
 
+/****************************************************************************
+  Named list handling
+****************************************************************************/
+
+gptr find_named(I_List<NAMED_LIST> *list, const char *name, uint length)
+{
+  I_List_iterator<NAMED_LIST> it(*list);
+  NAMED_LIST *element;
+  while ((element= it++))
+  {
+    if (element->cmp(name, length))
+      return element->data;
+  }
+  return 0;
+}
+
+
+void delete_elements(I_List<NAMED_LIST> *list, void (*free_element)(gptr))
+{
+  NAMED_LIST *element;
+  while ((element= list->get()))
+  {
+    (*free_element)(element->data);
+    delete element;
+  }
+}
+
+
+/* Key cache functions */
+
+static KEY_CACHE *create_key_cache(const char *name, uint length)
+{
+  KEY_CACHE *key_cache;
+  DBUG_PRINT("info",("Creating key cache: %s", name));
+  if ((key_cache= (KEY_CACHE*) my_malloc(sizeof(KEY_CACHE),
+					 MYF(MY_ZEROFILL | MY_WME))))
+  {
+    if (!new NAMED_LIST(&key_caches, name, length, (gptr) key_cache))
+    {
+      my_free((char*) key_cache, MYF(0));
+      key_cache= 0;
+    }
+  }
+  return key_cache;
+}
+
+
+KEY_CACHE *get_or_create_key_cache(const char *name, uint length)
+{
+  KEY_CACHE *key_cache= (KEY_CACHE*) find_named(&key_caches, name,
+						length);
+  if (!key_cache)
+    key_cache= create_key_cache(name, length);
+  return key_cache;
+}
+
+
+void free_key_cache(gptr key_cache)
+{
+  my_free(key_cache, MYF(0));
+}
+
 
 /****************************************************************************
   Used templates
@@ -1953,4 +2102,5 @@ ulong fix_sql_mode(ulong sql_mode)
 #ifdef __GNUC__
 template class List<set_var_base>;
 template class List_iterator_fast<set_var_base>;
+template class I_List_iterator<NAMED_LIST>;
 #endif
diff --git a/sql/set_var.h b/sql/set_var.h
index 5a0fbd21809..978aba3384a 100644
--- a/sql/set_var.h
+++ b/sql/set_var.h
@@ -47,12 +47,18 @@ public:
   struct my_option *option_limits;	/* Updated by by set_var_init() */
   uint name_length;			/* Updated by by set_var_init() */
   const char *name;
+  LEX_STRING base_name;			/* for structs */
+  
   sys_after_update_func after_update;
   sys_var(const char *name_arg) :name(name_arg),after_update(0)
-  {}
+  {
+    base_name.length=0;
+  }
   sys_var(const char *name_arg,sys_after_update_func func)
     :name(name_arg),after_update(func)
-  {}
+  {
+    base_name.length=0;
+  }
   virtual ~sys_var() {}
   virtual bool check(THD *thd, set_var *var) { return 0; }
   bool check_enum(THD *thd, set_var *var, TYPELIB *enum_names);
@@ -60,14 +66,16 @@ public:
   virtual bool update(THD *thd, set_var *var)=0;
   virtual void set_default(THD *thd, enum_var_type type) {}
   virtual SHOW_TYPE type() { return SHOW_UNDEF; }
-  virtual byte *value_ptr(THD *thd, enum_var_type type) { return 0; }
+  virtual byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base)
+  { return 0; }
   virtual bool check_type(enum_var_type type)
   { return type != OPT_GLOBAL; }		/* Error if not GLOBAL */
   virtual bool check_update_type(Item_result type)
   { return type != INT_RESULT; }		/* Assume INT */
   virtual bool check_default(enum_var_type type)
   { return option_limits == 0; }
-  Item *item(THD *thd, enum_var_type type);
+  Item *item(THD *thd, enum_var_type type, LEX_STRING *base);
+  virtual bool is_struct() { return 0; }
 };
 
 
@@ -83,7 +91,8 @@ public:
   bool update(THD *thd, set_var *var);
   void set_default(THD *thd, enum_var_type type);
   SHOW_TYPE type() { return SHOW_LONG; }
-  byte *value_ptr(THD *thd, enum_var_type type) { return (byte*) value; }
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base)
+  { return (byte*) value; }
 };
 
 
@@ -99,7 +108,8 @@ public:
   bool update(THD *thd, set_var *var);
   void set_default(THD *thd, enum_var_type type);
   SHOW_TYPE type() { return SHOW_LONGLONG; }
-  byte *value_ptr(THD *thd, enum_var_type type) { return (byte*) value; }
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base)
+  { return (byte*) value; }
 };
 
 
@@ -117,7 +127,8 @@ public:
   bool update(THD *thd, set_var *var);
   void set_default(THD *thd, enum_var_type type);
   SHOW_TYPE type() { return SHOW_MY_BOOL; }
-  byte *value_ptr(THD *thd, enum_var_type type) { return (byte*) value; }
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base)
+  { return (byte*) value; }
   bool check_update_type(Item_result type) { return 0; }
 };
 
@@ -149,7 +160,8 @@ public:
     (*set_default_func)(thd, type);
   }
   SHOW_TYPE type() { return SHOW_CHAR; }
-  byte *value_ptr(THD *thd, enum_var_type type) { return (byte*) value; }
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base)
+  { return (byte*) value; }
   bool check_update_type(Item_result type)
   {
     return type != STRING_RESULT;		/* Only accept strings */
@@ -173,7 +185,7 @@ public:
   }
   bool update(THD *thd, set_var *var);
   SHOW_TYPE type() { return SHOW_CHAR; }
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
   bool check_update_type(Item_result type) { return 0; }
 };
 
@@ -209,7 +221,7 @@ public:
   bool update(THD *thd, set_var *var);
   void set_default(THD *thd, enum_var_type type);
   SHOW_TYPE type() { return SHOW_LONG; }
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
 };
 
 class sys_var_pseudo_thread_id :public sys_var_thd_ulong
@@ -236,7 +248,7 @@ public:
   bool update(THD *thd, set_var *var);
   void set_default(THD *thd, enum_var_type type);
   SHOW_TYPE type() { return SHOW_HA_ROWS; }
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
 };
 
 
@@ -256,7 +268,7 @@ public:
   bool update(THD *thd, set_var *var);
   void set_default(THD *thd, enum_var_type type);
   SHOW_TYPE type() { return SHOW_LONGLONG; }
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
   bool check_default(enum_var_type type)
   {
     return type == OPT_GLOBAL && !option_limits;
@@ -282,7 +294,7 @@ public:
   bool update(THD *thd, set_var *var);
   void set_default(THD *thd, enum_var_type type);
   SHOW_TYPE type() { return SHOW_MY_BOOL; }
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
   bool check(THD *thd, set_var *var)
   {
     return check_enum(thd, var, &bool_typelib);
@@ -313,7 +325,7 @@ public:
   bool update(THD *thd, set_var *var);
   void set_default(THD *thd, enum_var_type type);
   SHOW_TYPE type() { return SHOW_CHAR; }
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
   bool check_update_type(Item_result type) { return 0; }
 };
 
@@ -332,7 +344,7 @@ public:
     return check_set(thd, var, enum_names);
   }
   void set_default(THD *thd, enum_var_type type);
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
 };
 
 
@@ -355,7 +367,7 @@ public:
   bool check_update_type(Item_result type) { return 0; }
   bool check_type(enum_var_type type) { return type == OPT_GLOBAL; }
   SHOW_TYPE type() { return SHOW_MY_BOOL; }
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
 };
 
 
@@ -370,7 +382,7 @@ public:
   bool check_type(enum_var_type type)    { return type == OPT_GLOBAL; }
   bool check_default(enum_var_type type) { return 0; }
   SHOW_TYPE type() { return SHOW_LONG; }
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
 };
 
 
@@ -381,7 +393,7 @@ public:
   bool update(THD *thd, set_var *var);
   bool check_type(enum_var_type type) { return type == OPT_GLOBAL; }
   SHOW_TYPE type() { return SHOW_LONGLONG; }
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
 };
 
 
@@ -392,7 +404,7 @@ public:
   bool update(THD *thd, set_var *var);
   bool check_type(enum_var_type type) { return type == OPT_GLOBAL; }
   SHOW_TYPE type() { return SHOW_LONGLONG; }
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
 };
 
 
@@ -456,7 +468,7 @@ SHOW_TYPE type() { return SHOW_CHAR; }
   }
   bool check_default(enum_var_type type) { return 0; }
   bool update(THD *thd, set_var *var);
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
   virtual void set_default(THD *thd, enum_var_type type)= 0;
   virtual CHARSET_INFO **ci_ptr(THD *thd, enum_var_type type)= 0;
 };
@@ -513,9 +525,24 @@ public:
   sys_var_collation_connection(const char *name_arg) :sys_var_collation(name_arg) {}
   bool update(THD *thd, set_var *var);
   void set_default(THD *thd, enum_var_type type);
-  byte *value_ptr(THD *thd, enum_var_type type);
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
 };
 
+
+class sys_var_key_buffer_size :public sys_var
+{
+public:
+  sys_var_key_buffer_size(const char *name_arg)
+    :sys_var(name_arg)
+  {}
+  bool update(THD *thd, set_var *var);
+  SHOW_TYPE type() { return SHOW_LONGLONG; }
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base);
+  bool check_default(enum_var_type type) { return 1; }
+  bool is_struct() { return 1; }
+};
+
+
 /* Variable that you can only read from */
 
 class sys_var_readonly: public sys_var
@@ -534,7 +561,7 @@ public:
   bool check_default(enum_var_type type) { return 1; }
   bool check_type(enum_var_type type) { return type != var_type; }
   bool check_update_type(Item_result type) { return 1; }
-  byte *value_ptr(THD *thd, enum_var_type type)
+  byte *value_ptr(THD *thd, enum_var_type type, LEX_STRING *base)
   {
     return (*value_ptr_func)(thd);
   }
@@ -639,6 +666,33 @@ public:
 };
 
 
+/* Named lists (used for keycaches) */
+
+class NAMED_LIST :public ilink
+{
+  const char *name;
+  uint name_length;
+public:
+  gptr data;
+
+  NAMED_LIST(I_List<NAMED_LIST> *links, const char *name_arg,
+	     uint name_length_arg, gptr data_arg):
+    name_length(name_length_arg), data(data_arg)
+    {
+      name=my_strdup(name_arg,MYF(MY_WME));
+      links->push_back(this);
+    }
+  inline bool cmp(const char *name_cmp, uint length)
+  {
+    return length == name_length && !memcmp(name, name_cmp, length);
+  }
+  ~NAMED_LIST()
+  {
+    my_free((char*) name, MYF(0));
+  }
+};
+
+
 /*
   Prototypes for helper functions
 */
@@ -649,6 +703,11 @@ sys_var *find_sys_var(const char *str, uint length=0);
 int sql_set_variables(THD *thd, List<set_var_base> *var_list);
 void fix_delay_key_write(THD *thd, enum_var_type type);
 ulong fix_sql_mode(ulong sql_mode);
-
 extern sys_var_str sys_charset_system;
 CHARSET_INFO *get_old_charset_by_name(const char *old_name);
+gptr find_named(I_List<NAMED_LIST> *list, const char *name, uint length);
+void delete_elements(I_List<NAMED_LIST> *list, void (*free_element)(gptr));
+
+/* key_cache functions */
+KEY_CACHE *get_or_create_key_cache(const char *name, uint length);
+void free_key_cache(gptr key_cache);
diff --git a/sql/share/czech/errmsg.txt b/sql/share/czech/errmsg.txt
index 641b1384e9a..5ed743962eb 100644
--- a/sql/share/czech/errmsg.txt
+++ b/sql/share/czech/errmsg.txt
@@ -16,8 +16,8 @@ v/*
 "ANO",
 "Nemohu vytvo-Bøit soubor '%-.64s' (chybový kód: %d)",
 "Nemohu vytvo-Bøit tabulku '%-.64s' (chybový kód: %d)",
-"Nemohu vytvo-Bøit databázi '%-.64s', chyba %d",
-"Nemohu vytvo-Bøit databázi '%-.64s', databáze ji¾ existuje",
+"Nemohu vytvo-Bøit databázi '%-.64s' (chybový kód: %d)",
+"Nemohu vytvo-Bøit databázi '%-.64s'; databáze ji¾ existuje",
 "Nemohu zru-B¹it databázi '%-.64s', databáze neexistuje",
 "Chyba p-Bøi ru¹ení databáze (nemohu vymazat '%-.64s', chyba %d)",
 "Chyba p-Bøi ru¹ení databáze (nemohu vymazat adresáø '%-.64s', chyba %d)",
@@ -222,7 +222,7 @@ v/*
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -275,3 +275,4 @@ v/*
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/danish/errmsg.txt b/sql/share/danish/errmsg.txt
index 574d26b7c1c..224b168e15b 100644
--- a/sql/share/danish/errmsg.txt
+++ b/sql/share/danish/errmsg.txt
@@ -10,9 +10,9 @@
 "JA",
 "Kan ikke oprette filen '%-.64s' (Fejlkode: %d)",
 "Kan ikke oprette tabellen '%-.64s' (Fejlkode: %d)",
-"Kan ikke oprette databasen '%-.64s'. Fejl %d",
-"Kan ikke oprette databasen '%-.64s'. Databasen eksisterer",
-"Kan ikke slette (droppe) '%-.64s'. Databasen eksisterer ikke",
+"Kan ikke oprette databasen '%-.64s' (Fejlkode: %d)",
+"Kan ikke oprette databasen '%-.64s'; databasen eksisterer",
+"Kan ikke slette (droppe) '%-.64s'; databasen eksisterer ikke",
 "Fejl ved sletning (drop) af databasen (kan ikke slette '%-.64s', Fejl %d)",
 "Fejl ved sletting af database (kan ikke slette folderen '%-.64s', Fejl %d)",
 "Fejl ved sletning af '%-.64s' (Fejlkode: %d)",
@@ -216,7 +216,7 @@
 "CREATE DATABASE er ikke tilladt mens en tråd holder på globalt read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -269,3 +269,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/dutch/errmsg.txt b/sql/share/dutch/errmsg.txt
index c6c975cb141..ad843ae2e4e 100644
--- a/sql/share/dutch/errmsg.txt
+++ b/sql/share/dutch/errmsg.txt
@@ -19,8 +19,8 @@
 "Kan file '%-.64s' niet aanmaken (Errcode: %d)",
 "Kan tabel '%-.64s' niet aanmaken (Errcode: %d)",
 "Kan database '%-.64s' niet aanmaken (Errcode: %d)",
-"Kan database '%-.64s' niet aanmaken. Database bestaat reeds",
-"Kan database '%-.64s' niet verwijderen. Database bestaat niet",
+"Kan database '%-.64s' niet aanmaken; database bestaat reeds",
+"Kan database '%-.64s' niet verwijderen; database bestaat niet",
 "Fout bij verwijderen database (kan '%-.64s' niet verwijderen, Errcode: %d)",
 "Fout bij verwijderen database (kan rmdir '%-.64s' niet uitvoeren, Errcode: %d)",
 "Fout bij het verwijderen van '%-.64s' (Errcode: %d)",
@@ -224,7 +224,7 @@
 "CREATE DATABASE niet toegestaan terwijl thread een globale 'read lock' bezit",
 "Foutieve parameters voor %s",
 "%-.32s@%-.64s mag geen nieuwe gebruikers creeren",
-"Incorrecte tabel definitie; Alle MERGE tabellen moeten tot dezelfde database behoren",
+"Incorrecte tabel definitie; alle MERGE tabellen moeten tot dezelfde database behoren",
 "Deadlock gevonden tijdens lock-aanvraag poging; Probeer herstart van de transactie",
 "Het gebruikte tabel type ondersteund geen FULLTEXT indexen",
 "Kan foreign key beperking niet toevoegen",
@@ -277,3 +277,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt
index f39c415fa55..778854c820b 100644
--- a/sql/share/english/errmsg.txt
+++ b/sql/share/english/errmsg.txt
@@ -7,9 +7,9 @@
 "YES",
 "Can't create file '%-.64s' (errno: %d)",
 "Can't create table '%-.64s' (errno: %d)",
-"Can't create database '%-.64s'. (errno: %d)",
-"Can't create database '%-.64s'. Database exists",
-"Can't drop database '%-.64s'. Database doesn't exist",
+"Can't create database '%-.64s' (errno: %d)",
+"Can't create database '%-.64s'; database exists",
+"Can't drop database '%-.64s'; database doesn't exist",
 "Error dropping database (can't delete '%-.64s', errno: %d)",
 "Error dropping database (can't rmdir '%-.64s', errno: %d)",
 "Error on delete of '%-.64s' (errno: %d)",
@@ -213,7 +213,7 @@
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -271,3 +271,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/estonian/errmsg.txt b/sql/share/estonian/errmsg.txt
index de22d6fd111..0ac668ce01c 100644
--- a/sql/share/estonian/errmsg.txt
+++ b/sql/share/estonian/errmsg.txt
@@ -12,7 +12,7 @@
 "JAH",
 "Ei suuda luua faili '%-.64s' (veakood: %d)",
 "Ei suuda luua tabelit '%-.64s' (veakood: %d)",
-"Ei suuda luua andmebaasi '%-.64s'. (veakood: %d)",
+"Ei suuda luua andmebaasi '%-.64s' (veakood: %d)",
 "Ei suuda luua andmebaasi '%-.64s': andmebaas juba eksisteerib",
 "Ei suuda kustutada andmebaasi '%-.64s': andmebaasi ei eksisteeri",
 "Viga andmebaasi kustutamisel (ei suuda kustutada faili '%-.64s', veakood: %d)",
@@ -271,3 +271,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/french/errmsg.txt b/sql/share/french/errmsg.txt
index 6c1187cd0e4..51053dc5632 100644
--- a/sql/share/french/errmsg.txt
+++ b/sql/share/french/errmsg.txt
@@ -7,9 +7,9 @@
 "OUI",
 "Ne peut créer le fichier '%-.64s' (Errcode: %d)",
 "Ne peut créer la table '%-.64s' (Errcode: %d)",
-"Ne peut créer la base '%-.64s'. Erreur %d",
-"Ne peut créer la base '%-.64s'. Elle existe déjà",
-"Ne peut effacer la base '%-.64s'. Elle n'existe pas",
+"Ne peut créer la base '%-.64s' (Erreur %d)",
+"Ne peut créer la base '%-.64s'; elle existe déjà",
+"Ne peut effacer la base '%-.64s'; elle n'existe pas",
 "Ne peut effacer la base '%-.64s' (erreur %d)",
 "Erreur en effaçant la base (rmdir '%-.64s', erreur %d)",
 "Erreur en effaçant '%-.64s' (Errcode: %d)",
@@ -213,7 +213,7 @@
 "CREATE DATABASE n'est pas autorisée pendant qu'une tâche possède un verrou global en lecture",
 "Mauvais arguments à %s",
 "%-.32s@%-.64s n'est pas autorisé à créer de nouveaux utilisateurs",
-"Définition de table incorrecte : toutes les tables MERGE doivent être dans la même base de donnée",
+"Définition de table incorrecte; toutes les tables MERGE doivent être dans la même base de donnée",
 "Deadlock découvert en essayant d'obtenir les verrous : essayez de redémarrer la transaction",
 "Le type de table utilisé ne supporte pas les index FULLTEXT",
 "Impossible d'ajouter des contraintes d'index externe",
@@ -266,3 +266,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/german/errmsg.txt b/sql/share/german/errmsg.txt
index 78d53034a71..49615c08223 100644
--- a/sql/share/german/errmsg.txt
+++ b/sql/share/german/errmsg.txt
@@ -14,11 +14,11 @@
 "isamchk",
 "Nein",
 "Ja",
-"Kann Datei '%-.64s' nicht erzeugen. (Fehler: %d)",
-"Kann Tabelle '%-.64s' nicht erzeugen. (Fehler: %d)",
-"Kann Datenbank '%-.64s' nicht erzeugen. (Fehler: %d)",
-"Kann Datenbank '%-.64s' nicht erzeugen. Datenbank '%-.64s' existiert bereits.",
-"Kann Datenbank '%-.64s' nicht löschen. Keine Datenbank '%-.64s' vorhanden.",
+"Kann Datei '%-.64s' nicht erzeugen (Fehler: %d)",
+"Kann Tabelle '%-.64s' nicht erzeugen (Fehler: %d)",
+"Kann Datenbank '%-.64s' nicht erzeugen (Fehler: %d)",
+"Kann Datenbank '%-.64s' nicht erzeugen; datenbank '%-.64s' existiert bereits.",
+"Kann Datenbank '%-.64s' nicht löschen; keine Datenbank '%-.64s' vorhanden.",
 "Fehler beim Löschen der Datenbank. ('%-.64s' kann nicht gelöscht werden, Fehlernuumer: %d)",
 "Fehler beim Löschen der Datenbank. (Verzeichnis '%-.64s' kann nicht gelöscht werden, Fehlernummer: %d)",
 "Fehler beim Löschen von '%-.64s'. (Fehler: %d)",
@@ -222,7 +222,7 @@
 "Solange ein globaler Read LOCK gesetzt ist, ist CREATE DATABASE nicht zulässig.",
 "Falsche Argumente für %s",
 "%-.32s@%-.64s is nicht berechtigt neue Benutzer hinzuzufügen.",
-"Falsche Tabellendefinition: Sämtliche MERGE-Tabellen müssen in derselben Datenbank sein.",
+"Falsche Tabellendefinition; sämtliche MERGE-Tabellen müssen in derselben Datenbank sein.",
 "Beim Versuch einen Lock anzufordern ist ein Deadlock aufgetreten. Es wird versucht die Transaktion erneut zu starten.",
 "Der verwendete Tabellentyp unterstützt keinen FULLTEXT-Index.",
 "Foreign_Key Beschränkung konnte nicht hinzugefügt werden.",
@@ -275,3 +275,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/greek/errmsg.txt b/sql/share/greek/errmsg.txt
index 347370f1ac8..d49e80135e3 100644
--- a/sql/share/greek/errmsg.txt
+++ b/sql/share/greek/errmsg.txt
@@ -7,8 +7,8 @@
 "ÍÁÉ",
 "Áäýíáôç ç äçìéïõñãßá ôïõ áñ÷åßïõ '%-.64s' (êùäéêüò ëÜèïõò: %d)",
 "Áäýíáôç ç äçìéïõñãßá ôïõ ðßíáêá '%-.64s' (êùäéêüò ëÜèïõò: %d)",
-"Áäýíáôç ç äçìéïõñãßá ôçò âÜóçò äåäïìÝíùí '%-.64s'. (êùäéêüò ëÜèïõò: %d)",
-"Áäýíáôç ç äçìéïõñãßá ôçò âÜóçò äåäïìÝíùí '%-.64s'. Ç âÜóç äåäïìÝíùí õðÜñ÷åé Þäç",
+"Áäýíáôç ç äçìéïõñãßá ôçò âÜóçò äåäïìÝíùí '%-.64s' (êùäéêüò ëÜèïõò: %d)",
+"Áäýíáôç ç äçìéïõñãßá ôçò âÜóçò äåäïìÝíùí '%-.64s'; Ç âÜóç äåäïìÝíùí õðÜñ÷åé Þäç",
 "Áäýíáôç ç äéáãñáöÞ ôçò âÜóçò äåäïìÝíùí '%-.64s'. Ç âÜóç äåäïìÝíùí äåí õðÜñ÷åé",
 "ÐáñïõóéÜóôçêå ðñüâëçìá êáôÜ ôç äéáãñáöÞ ôçò âÜóçò äåäïìÝíùí (áäýíáôç ç äéáãñáöÞ '%-.64s', êùäéêüò ëÜèïõò: %d)",
 "ÐáñïõóéÜóôçêå ðñüâëçìá êáôÜ ôç äéáãñáöÞ ôçò âÜóçò äåäïìÝíùí (áäýíáôç ç äéáãñáöÞ ôïõ öáêÝëëïõ '%-.64s', êùäéêüò ëÜèïõò: %d)",
@@ -213,7 +213,7 @@
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -266,3 +266,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/hungarian/errmsg.txt b/sql/share/hungarian/errmsg.txt
index 313275b3cb6..8fc12da1f00 100644
--- a/sql/share/hungarian/errmsg.txt
+++ b/sql/share/hungarian/errmsg.txt
@@ -215,7 +215,7 @@
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -268,3 +268,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/italian/errmsg.txt b/sql/share/italian/errmsg.txt
index 8af7e3ba9f7..18ce664e7fd 100644
--- a/sql/share/italian/errmsg.txt
+++ b/sql/share/italian/errmsg.txt
@@ -7,9 +7,9 @@
 "SI",
 "Impossibile creare il file '%-.64s' (errno: %d)",
 "Impossibile creare la tabella '%-.64s' (errno: %d)",
-"Impossibile creare il database '%-.64s'. (errno: %d)",
-"Impossibile creare il database '%-.64s'. Il database esiste",
-"Impossibile cancellare '%-.64s'. Il database non esiste",
+"Impossibile creare il database '%-.64s' (errno: %d)",
+"Impossibile creare il database '%-.64s'; il database esiste",
+"Impossibile cancellare '%-.64s'; il database non esiste",
 "Errore durante la cancellazione del database (impossibile cancellare '%-.64s', errno: %d)",
 "Errore durante la cancellazione del database (impossibile rmdir '%-.64s', errno: %d)",
 "Errore durante la cancellazione di '%-.64s' (errno: %d)",
@@ -266,3 +266,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/japanese/errmsg.txt b/sql/share/japanese/errmsg.txt
index 417a03978fb..fedf0fd8e9c 100644
--- a/sql/share/japanese/errmsg.txt
+++ b/sql/share/japanese/errmsg.txt
@@ -215,7 +215,7 @@
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -268,3 +268,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/korean/errmsg.txt b/sql/share/korean/errmsg.txt
index 22395d0fb6a..8c37b824342 100644
--- a/sql/share/korean/errmsg.txt
+++ b/sql/share/korean/errmsg.txt
@@ -213,7 +213,7 @@
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -266,3 +266,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/norwegian-ny/errmsg.txt b/sql/share/norwegian-ny/errmsg.txt
index de6db62cdce..4e5bed92916 100644
--- a/sql/share/norwegian-ny/errmsg.txt
+++ b/sql/share/norwegian-ny/errmsg.txt
@@ -9,9 +9,9 @@
 "JA",
 "Kan ikkje opprette fila '%-.64s' (Feilkode: %d)",
 "Kan ikkje opprette tabellen '%-.64s' (Feilkode: %d)",
-"Kan ikkje opprette databasen '%-.64s'. Feil %d",
-"Kan ikkje opprette databasen '%-.64s'. Databasen eksisterer",
-"Kan ikkje fjerne (drop) '%-.64s'. Databasen eksisterer ikkje",
+"Kan ikkje opprette databasen '%-.64s' (Feilkode: %d)",
+"Kan ikkje opprette databasen '%-.64s'; databasen eksisterer",
+"Kan ikkje fjerne (drop) '%-.64s'; databasen eksisterer ikkje",
 "Feil ved fjerning (drop) av databasen (kan ikkje slette '%-.64s', feil %d)",
 "Feil ved sletting av database (kan ikkje slette katalogen '%-.64s', feil %d)",
 "Feil ved sletting av '%-.64s' (Feilkode: %d)",
@@ -215,7 +215,7 @@
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -268,3 +268,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/norwegian/errmsg.txt b/sql/share/norwegian/errmsg.txt
index 28db8caa8bc..4b4f4e6a1f3 100644
--- a/sql/share/norwegian/errmsg.txt
+++ b/sql/share/norwegian/errmsg.txt
@@ -9,9 +9,9 @@
 "JA",
 "Kan ikke opprette fila '%-.64s' (Feilkode: %d)",
 "Kan ikke opprette tabellen '%-.64s' (Feilkode: %d)",
-"Kan ikke opprette databasen '%-.64s'. Feil %d",
-"Kan ikke opprette databasen '%-.64s'. Databasen eksisterer",
-"Kan ikke fjerne (drop) '%-.64s'. Databasen eksisterer ikke",
+"Kan ikke opprette databasen '%-.64s' (Feilkode: %d)",
+"Kan ikke opprette databasen '%-.64s'; databasen eksisterer",
+"Kan ikke fjerne (drop) '%-.64s'; databasen eksisterer ikke",
 "Feil ved fjerning (drop) av databasen (kan ikke slette '%-.64s', feil %d)",
 "Feil ved sletting av database (kan ikke slette katalogen '%-.64s', feil %d)",
 "Feil ved sletting av '%-.64s' (Feilkode: %d)",
@@ -215,7 +215,7 @@
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -268,3 +268,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/polish/errmsg.txt b/sql/share/polish/errmsg.txt
index fdf856c7e56..cc710376052 100644
--- a/sql/share/polish/errmsg.txt
+++ b/sql/share/polish/errmsg.txt
@@ -11,9 +11,9 @@
 "TAK",
 "Nie mo¿na stworzyæ pliku '%-.64s' (Kod b³êdu: %d)",
 "Nie mo¿na stworzyæ tabeli '%-.64s' (Kod b³êdu: %d)",
-"Nie mo¿na stworzyæ bazy danych '%-.64s'. B³?d %d",
-"Nie mo¿na stworzyæ bazy danych '%-.64s'. Baza danych ju¿ istnieje",
-"Nie mo¿na usun?æ bazy danych '%-.64s'. Baza danych nie istnieje",
+"Nie mo¿na stworzyæ bazy danych '%-.64s' (Kod b³êdu: %d)",
+"Nie mo¿na stworzyæ bazy danych '%-.64s'; baza danych ju¿ istnieje",
+"Nie mo¿na usun?æ bazy danych '%-.64s'; baza danych nie istnieje",
 "B³?d podczas usuwania bazy danych (nie mo¿na usun?æ '%-.64s', b³?d %d)",
 "B³?d podczas usuwania bazy danych (nie mo¿na wykonaæ rmdir '%-.64s', b³?d %d)",
 "B³?d podczas usuwania '%-.64s' (Kod b³êdu: %d)",
@@ -217,7 +217,7 @@
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -270,3 +270,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/portuguese/errmsg.txt b/sql/share/portuguese/errmsg.txt
index 397784dc7dd..14a1e6763b4 100644
--- a/sql/share/portuguese/errmsg.txt
+++ b/sql/share/portuguese/errmsg.txt
@@ -8,8 +8,8 @@
 "Não pode criar o arquivo '%-.64s' (erro no. %d)",
 "Não pode criar a tabela '%-.64s' (erro no. %d)",
 "Não pode criar o banco de dados '%-.64s' (erro no. %d)",
-"Não pode criar o banco de dados '%-.64s'. Este banco de dados já existe",
-"Não pode eliminar o banco de dados '%-.64s'. Este banco de dados não existe",
+"Não pode criar o banco de dados '%-.64s'; este banco de dados já existe",
+"Não pode eliminar o banco de dados '%-.64s'; este banco de dados não existe",
 "Erro ao eliminar banco de dados (não pode eliminar '%-.64s' - erro no. %d)",
 "Erro ao eliminar banco de dados (não pode remover diretório '%-.64s' - erro no. %d)",
 "Erro na remoção de '%-.64s' (erro no. %d)",
@@ -266,3 +266,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/romanian/errmsg.txt b/sql/share/romanian/errmsg.txt
index 8f1cdb7b259..f8bbba75a9f 100644
--- a/sql/share/romanian/errmsg.txt
+++ b/sql/share/romanian/errmsg.txt
@@ -11,9 +11,9 @@
 "DA",
 "Nu pot sa creez fisierul '%-.64s' (Eroare: %d)",
 "Nu pot sa creez tabla '%-.64s' (Eroare: %d)",
-"Nu pot sa creez baza de date '%-.64s'. (Eroare: %d)",
-"Nu pot sa creez baza de date '%-.64s'. Baza de date exista deja",
-"Nu pot sa drop baza de date '%-.64s'. Baza da date este inexistenta",
+"Nu pot sa creez baza de date '%-.64s' (Eroare: %d)",
+"Nu pot sa creez baza de date '%-.64s'; baza de date exista deja",
+"Nu pot sa drop baza de date '%-.64s'; baza da date este inexistenta",
 "Eroare dropuind baza de date (nu pot sa sterg '%-.64s', Eroare: %d)",
 "Eroare dropuind baza de date (nu pot sa rmdir '%-.64s', Eroare: %d)",
 "Eroare incercind sa delete '%-.64s' (Eroare: %d)",
@@ -217,7 +217,7 @@
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -270,3 +270,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/russian/errmsg.txt b/sql/share/russian/errmsg.txt
index ec41a6acb34..1981e7e7a5a 100644
--- a/sql/share/russian/errmsg.txt
+++ b/sql/share/russian/errmsg.txt
@@ -9,7 +9,7 @@
 "äá",
 "îÅ×ÏÚÍÏÖÎÏ ÓÏÚÄÁÔØ ÆÁÊÌ '%-.64s' (ÏÛÉÂËÁ: %d)",
 "îÅ×ÏÚÍÏÖÎÏ ÓÏÚÄÁÔØ ÔÁÂÌÉÃÕ '%-.64s' (ÏÛÉÂËÁ: %d)",
-"îÅ×ÏÚÍÏÖÎÏ ÓÏÚÄÁÔØ ÂÁÚÕ ÄÁÎÎÙÈ '%-.64s'. (ÏÛÉÂËÁ: %d)",
+"îÅ×ÏÚÍÏÖÎÏ ÓÏÚÄÁÔØ ÂÁÚÕ ÄÁÎÎÙÈ '%-.64s' (ÏÛÉÂËÁ: %d)",
 "îÅ×ÏÚÍÏÖÎÏ ÓÏÚÄÁÔØ ÂÁÚÕ ÄÁÎÎÙÈ '%-.64s'. âÁÚÁ ÄÁÎÎÙÈ ÕÖÅ ÓÕÝÅÓÔ×ÕÅÔ",
 "îÅ×ÏÚÍÏÖÎÏ ÕÄÁÌÉÔØ ÂÁÚÕ ÄÁÎÎÙÈ '%-.64s'. ôÁËÏÊ ÂÁÚÙ ÄÁÎÎÙÈ ÎÅÔ",
 "ïÛÉÂËÁ ÐÒÉ ÕÄÁÌÅÎÉÉ ÂÁÚÙ ÄÁÎÎÙÈ (ÎÅ×ÏÚÍÏÖÎÏ ÕÄÁÌÉÔØ '%-.64s', ÏÛÉÂËÁ: %d)",
@@ -268,3 +268,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/serbian/errmsg.txt b/sql/share/serbian/errmsg.txt
index 77d35be2fc9..e1583bbb791 100644
--- a/sql/share/serbian/errmsg.txt
+++ b/sql/share/serbian/errmsg.txt
@@ -13,9 +13,9 @@
 "DA",
 "Ne mogu da kreiram file '%-.64s' (errno: %d)",
 "Ne mogu da kreiram tabelu '%-.64s' (errno: %d)",
-"Ne mogu da kreiram bazu '%-.64s'. (errno: %d)",
-"Ne mogu da kreiram bazu '%-.64s'. Baza veæ postoji.",
-"Ne mogu da izbrišem bazu '%-.64s'. Baza ne postoji.",
+"Ne mogu da kreiram bazu '%-.64s' (errno: %d)",
+"Ne mogu da kreiram bazu '%-.64s'; baza veæ postoji.",
+"Ne mogu da izbrišem bazu '%-.64s'; baza ne postoji.",
 "Ne mogu da izbrišem bazu (ne mogu da izbrišem '%-.64s', errno: %d)",
 "Ne mogu da izbrišem bazu (ne mogu da izbrišem direktorijum '%-.64s', errno: %d)",
 "Greška pri brisanju '%-.64s' (errno: %d)",
@@ -219,7 +219,7 @@
 "Komanda 'CREATE DATABASE' nije dozvoljena dok thread globalno zakljuèava èitanje podataka",
 "Pogrešni argumenti prosleðeni na %s",
 "Korisniku %-.32s@%-.64s nije dozvoljeno da kreira nove korisnike",
-"Pogrešna definicija tabele; Sve 'MERGE' tabele moraju biti u istoj bazi podataka",
+"Pogrešna definicija tabele; sve 'MERGE' tabele moraju biti u istoj bazi podataka",
 "Unakrsno zakljuèavanje pronaðeno kada sam pokušao da dobijem pravo na zakljuèavanje; Probajte da restartujete transakciju",
 "Upotrebljeni tip tabele ne podržava 'FULLTEXT' indekse",
 "Ne mogu da dodam proveru spoljnog kljuèa",
@@ -262,3 +262,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/slovak/errmsg.txt b/sql/share/slovak/errmsg.txt
index 33cabdfc752..c6fad79e15a 100644
--- a/sql/share/slovak/errmsg.txt
+++ b/sql/share/slovak/errmsg.txt
@@ -15,9 +15,9 @@
 "Áno",
 "Nemô¾em vytvori» súbor '%-.64s' (chybový kód: %d)",
 "Nemô¾em vytvori» tabuµku '%-.64s' (chybový kód: %d)",
-"Nemô¾em vytvori» databázu '%-.64s'. (chybový kód: %d)",
-"Nemô¾em vytvori» databázu '%-.64s'. Databáza existuje",
-"Nemô¾em zmaza» databázu '%-.64s'. Databáza neexistuje",
+"Nemô¾em vytvori» databázu '%-.64s' (chybový kód: %d)",
+"Nemô¾em vytvori» databázu '%-.64s'; databáza existuje",
+"Nemô¾em zmaza» databázu '%-.64s'; databáza neexistuje",
 "Chyba pri mazaní databázy (nemô¾em zmaza» '%-.64s', chybový kód: %d)",
 "Chyba pri mazaní databázy (nemô¾em vymaza» adresár '%-.64s', chybový kód: %d)",
 "Chyba pri mazaní '%-.64s' (chybový kód: %d)",
@@ -221,7 +221,7 @@
 "CREATE DATABASE not allowed while thread is holding global read lock",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -274,3 +274,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/spanish/errmsg.txt b/sql/share/spanish/errmsg.txt
index 35e26f35ff7..731e576fafd 100644
--- a/sql/share/spanish/errmsg.txt
+++ b/sql/share/spanish/errmsg.txt
@@ -8,9 +8,9 @@
 "SI",
 "No puedo crear archivo '%-.64s' (Error: %d)",
 "No puedo crear tabla '%-.64s' (Error: %d)",
-"No puedo crear base de datos '%-.64s'. Error %d",
-"No puedo crear base de datos '%-.64s'. La base de datos ya existe",
-"No puedo eliminar base de datos '%-.64s'. La base de datos no existe",
+"No puedo crear base de datos '%-.64s' (Error: %d)",
+"No puedo crear base de datos '%-.64s'; la base de datos ya existe",
+"No puedo eliminar base de datos '%-.64s'; la base de datos no existe",
 "Error eliminando la base de datos(no puedo borrar '%-.64s', error %d)",
 "Error eliminando la base de datos (No puedo borrar directorio '%-.64s', error %d)",
 "Error en el borrado de '%-.64s' (Error: %d)",
@@ -214,7 +214,7 @@
 "CREATE DATABASE no permitido mientras un thread está ejerciendo un bloqueo de lectura global",
 "Wrong arguments to %s",
 "%-.32s@%-.64s is not allowed to create new users",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "The used table type doesn't support FULLTEXT indexes",
 "Cannot add foreign key constraint",
@@ -267,3 +267,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/swedish/errmsg.txt b/sql/share/swedish/errmsg.txt
index 9cdcb20db35..9e34dbb138a 100644
--- a/sql/share/swedish/errmsg.txt
+++ b/sql/share/swedish/errmsg.txt
@@ -7,9 +7,9 @@
 "YES",
 "Kan inte skapa filen '%-.64s' (Felkod: %d)",
 "Kan inte skapa tabellen '%-.64s' (Felkod: %d)",
-"Kan inte skapa databasen '%-.64s'. (Felkod: %d)",
+"Kan inte skapa databasen '%-.64s' (Felkod: %d)",
 "Databasen '%-.64s' existerar redan",
-"Kan inte radera databasen '%-.64s'. Databasen finns inte",
+"Kan inte radera databasen '%-.64s'; databasen finns inte",
 "Fel vid radering av databasen (Kan inte radera '%-.64s'. Felkod: %d)",
 "Fel vid radering av databasen (Kan inte radera biblioteket '%-.64s'. Felkod: %d)",
 "Kan inte radera filen '%-.64s' (Felkod: %d)",
@@ -213,7 +213,7 @@
 "CREATE DATABASE är inte tillåtet när man har ett globalt läslås",
 "Felaktiga argument till %s",
 "%-.32s@%-.64s har inte rättighet att skapa nya användare",
-"Felaktig tabelldefinition. Alla tabeller i en MERGE-tabell måste vara i samma databas",
+"Felaktig tabelldefinition; alla tabeller i en MERGE-tabell måste vara i samma databas",
 "Fick 'DEADLOCK' vid låsförsök av block/rad. Försök att starta om transaktionen",
 "Tabelltypen har inte hantering av FULLTEXT-index",
 "Kan inte lägga till 'FOREIGN KEY constraint'",
@@ -266,3 +266,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/share/ukrainian/errmsg.txt b/sql/share/ukrainian/errmsg.txt
index 5a614714de2..adbb35dc7c0 100644
--- a/sql/share/ukrainian/errmsg.txt
+++ b/sql/share/ukrainian/errmsg.txt
@@ -12,7 +12,7 @@
 "ôáë",
 "îÅ ÍÏÖÕ ÓÔ×ÏÒÉÔÉ ÆÁÊÌ '%-.64s' (ÐÏÍÉÌËÁ: %d)",
 "îÅ ÍÏÖÕ ÓÔ×ÏÒÉÔÉ ÔÁÂÌÉÃÀ '%-.64s' (ÐÏÍÉÌËÁ: %d)",
-"îÅ ÍÏÖÕ ÓÔ×ÏÒÉÔÉ ÂÁÚÕ ÄÁÎÎÉÈ '%-.64s'. (ÐÏÍÉÌËÁ: %d)",
+"îÅ ÍÏÖÕ ÓÔ×ÏÒÉÔÉ ÂÁÚÕ ÄÁÎÎÉÈ '%-.64s' (ÐÏÍÉÌËÁ: %d)",
 "îÅ ÍÏÖÕ ÓÔ×ÏÒÉÔÉ ÂÁÚÕ ÄÁÎÎÉÈ '%-.64s'. âÁÚÁ ÄÁÎÎÉÈ ¦ÓÎÕ¤",
 "îÅ ÍÏÖÕ ×ÉÄÁÌÉÔÉ ÂÁÚÕ ÄÁÎÎÉÈ '%-.64s'. âÁÚÁ ÄÁÎÎÉÈ ÎÅ ¦ÓÎÕ¤",
 "îÅ ÍÏÖÕ ×ÉÄÁÌÉÔÉ ÂÁÚÕ ÄÁÎÎÉÈ (îÅ ÍÏÖÕ ×ÉÄÁÌÉÔÉ '%-.64s', ÐÏÍÉÌËÁ: %d)",
@@ -218,7 +218,7 @@
 "CREATE DATABASE ÎÅ ÄÏÚ×ÏÌÅÎÏ ÄÏËÉ Ç¦ÌËÁ ÐÅÒÅÂÕ×Á¤ Ð¦Ä ÚÁÇÁÌØÎÉÍ ÂÌÏËÕ×ÁÎÎÑÍ ÞÉÔÁÎÎÑ",
 "èÉÂÎÉÊ ÁÒÇÕÍÅÎÔ ÄÌÑ %s",
 "ëÏÒÉÓÔÕ×ÁÞÕ %-.32s@%-.64s ÎÅ ÄÏÚ×ÏÌÅÎÏ ÓÔ×ÏÒÀ×ÁÔÉ ÎÏ×ÉÈ ËÏÒÉÓÔÕ×ÁÞ¦×",
-"Incorrect table definition; All MERGE tables must be in the same database",
+"Incorrect table definition; all MERGE tables must be in the same database",
 "Deadlock found when trying to get lock; Try restarting transaction",
 "÷ÉËÏÒÉÓÔÁÎÉÊ ÔÉÐ ÔÁÂÌÉæ ΊЦÄÔÒÉÍÕ¤ FULLTEXT ¦ÎÄÅËÓ¦×",
 "Cannot add foreign key constraint",
@@ -271,3 +271,4 @@
 "Can't revoke all privileges, grant for one or more of the requested users"
 "Illegal mix of collations (%s,%s), (%s,%s), (%s,%s) for operation '%s'",
 "Illegal mix of collations for operation '%s'",
+"Variable '%-.64s' is not a variable component (Can't be used as XXXX.variable_name)",
diff --git a/sql/sql_analyse.cc b/sql/sql_analyse.cc
index 1b1f7c75c26..ced5993e293 100644
--- a/sql/sql_analyse.cc
+++ b/sql/sql_analyse.cc
@@ -44,12 +44,6 @@ int sortcmp2(void* cmp_arg __attribute__((unused)),
   return sortcmp(a,b,a->charset());
 }
 
-int stringcmp2(void* cmp_arg __attribute__((unused)),
-	     const String *a,const String *b)
-{
-  return sortcmp(a,b,&my_charset_bin);
-}
-
 int compare_double2(void* cmp_arg __attribute__((unused)),
 		    const double *s, const double *t)
 {
diff --git a/sql/sql_analyse.h b/sql/sql_analyse.h
index 2087e6b2b75..3d1cffecaef 100644
--- a/sql/sql_analyse.h
+++ b/sql/sql_analyse.h
@@ -99,8 +99,6 @@ int collect_string(String *element, element_count count,
 
 int sortcmp2(void* cmp_arg __attribute__((unused)),
 	     const String *a,const String *b);
-int stringcmp2(void* cmp_arg __attribute__((unused)),
-	     const String *a,const String *b);
 
 class field_str :public field_info
 {
@@ -117,8 +115,7 @@ public:
     max_arg("",default_charset_info), sum(0),
     must_be_blob(0), was_zero_fill(0),
     was_maybe_zerofill(0), can_be_still_num(1)
-    { init_tree(&tree, 0, 0, sizeof(String), a->binary() ?
-		(qsort_cmp2) stringcmp2 : (qsort_cmp2) sortcmp2,
+    { init_tree(&tree, 0, 0, sizeof(String), (qsort_cmp2) sortcmp2,
 		0, (tree_element_free) free_string, NULL); };
 
   void	 add();
diff --git a/sql/sql_class.h b/sql/sql_class.h
index ccfe2555518..f6336cb7dd9 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -503,8 +503,23 @@ public:
 #ifdef SIGNAL_WITH_VIO_CLOSE
   Vio* active_vio;
 #endif  
-  ulonglong  next_insert_id,last_insert_id,current_insert_id,
-             limit_found_rows;
+  /*
+    next_insert_id is set on SET INSERT_ID= #. This is used as the next
+    generated auto_increment value in handler.cc
+  */
+  ulonglong  next_insert_id;
+  /*
+    The insert_id used for the last statement or set by SET LAST_INSERT_ID=#
+    or SELECT LAST_INSERT_ID(#).  Used for binary log and returned by
+    LAST_INSERT_ID()
+  */
+  ulonglong  last_insert_id;
+  /*
+    Set to the first value that LAST_INSERT_ID() returned for the last
+    statement.  When this is set, last_insert_id_used is set to true.
+  */
+  ulonglong  current_insert_id;
+  ulonglong  limit_found_rows;
   ha_rows    select_limit, offset_limit, cuted_fields,
              sent_row_count, examined_row_count;
   table_map  used_tables;
diff --git a/sql/sql_handler.cc b/sql/sql_handler.cc
index 7c07c08bcac..79d13039784 100644
--- a/sql/sql_handler.cc
+++ b/sql/sql_handler.cc
@@ -222,6 +222,8 @@ int mysql_ha_read(THD *thd, TABLE_LIST *tables,
       goto err;
     }
 
+    if (err == HA_ERR_RECORD_DELETED)
+      continue;
     if (err)
     {
       if (err != HA_ERR_KEY_NOT_FOUND && err != HA_ERR_END_OF_FILE)
@@ -233,31 +235,24 @@ int mysql_ha_read(THD *thd, TABLE_LIST *tables,
       }
       goto ok;
     }
-    if (cond)
+    if (cond && !cond->val_int())
+      continue;
+    if (!err && num_rows >= offset_limit)
     {
-      err=err;
-      if (!cond->val_int())
-        continue;
-    }
-    if (num_rows >= offset_limit)
-    {
-      if (!err)
+      String *packet = &thd->packet;
+      Item *item;
+      protocol->prepare_for_resend();
+      it.rewind();
+      while ((item=it++))
       {
-        String *packet = &thd->packet;
-        Item *item;
-        protocol->prepare_for_resend();
-        it.rewind();
-        while ((item=it++))
-        {
-          if (item->send(thd->protocol, &buffer))
-          {
-            protocol->free();                             // Free used
-            my_error(ER_OUT_OF_RESOURCES,MYF(0));
-            goto err;
-          }
-        }
-	protocol->write();
+	if (item->send(thd->protocol, &buffer))
+	{
+	  protocol->free();                             // Free used
+	  my_error(ER_OUT_OF_RESOURCES,MYF(0));
+	  goto err;
+	}
       }
+      protocol->write();
     }
     num_rows++;
   }
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 091de9d406f..947205949f1 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -404,13 +404,12 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list,
   {
     char buff[160];
     if (duplic == DUP_IGNORE)
-      sprintf(buff,ER(ER_INSERT_INFO),info.records,
-	      (lock_type == TL_WRITE_DELAYED) ? 0 :
-	      info.records-info.copied,
-	      thd->cuted_fields);
+      sprintf(buff, ER(ER_INSERT_INFO), (ulong) info.records,
+	      (lock_type == TL_WRITE_DELAYED) ? (ulong) 0 :
+	      (ulong) (info.records - info.copied), (ulong) thd->cuted_fields);
     else
-      sprintf(buff,ER(ER_INSERT_INFO),info.records,info.deleted,
-	      thd->cuted_fields);
+      sprintf(buff, ER(ER_INSERT_INFO), (ulong) info.records,
+	      (ulong) info.deleted, (ulong) thd->cuted_fields);
     ::send_ok(thd,info.copied+info.deleted,(ulonglong)id,buff);
   }
   free_underlaid_joins(thd, &thd->lex.select_lex);
@@ -1494,11 +1493,11 @@ bool select_insert::send_eof()
   {
     char buff[160];
     if (info.handle_duplicates == DUP_IGNORE)
-      sprintf(buff,ER(ER_INSERT_INFO),info.records,info.records-info.copied,
-	      thd->cuted_fields);
+      sprintf(buff, ER(ER_INSERT_INFO), (ulong) info.records,
+	      (ulong) (info.records - info.copied), (ulong) thd->cuted_fields);
     else
-      sprintf(buff,ER(ER_INSERT_INFO),info.records,info.deleted,
-	      thd->cuted_fields);
+      sprintf(buff, ER(ER_INSERT_INFO), (ulong) info.records,
+	      (ulong) info.deleted, (ulong) thd->cuted_fields);
     ::send_ok(thd,info.copied+info.deleted,last_insert_id,buff);
     return 0;
   }
diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc
index 4cf13682f84..a4b4aa7f7b3 100644
--- a/sql/sql_lex.cc
+++ b/sql/sql_lex.cc
@@ -122,6 +122,7 @@ LEX *lex_start(THD *thd, uchar *buf,uint length)
   lex->yacc_yyss=lex->yacc_yyvs=0;
   lex->ignore_space=test(thd->variables.sql_mode & MODE_IGNORE_SPACE);
   lex->sql_command=SQLCOM_END;
+  lex->duplicates= DUP_ERROR;
   return lex;
 }
 
@@ -556,10 +557,12 @@ int yylex(void *arg, void *yythd)
         return(IDENT);
 
     case MY_LEX_IDENT_SEP:		// Found ident and now '.'
-      lex->next_state=MY_LEX_IDENT_START;// Next is an ident (not a keyword)
       yylval->lex_str.str=(char*) lex->ptr;
       yylval->lex_str.length=1;
       c=yyGet();			// should be '.'
+      lex->next_state= MY_LEX_IDENT_START;// Next is an ident (not a keyword)
+      if (!ident_map[yyPeek()])		// Probably ` or "
+	lex->next_state= MY_LEX_START;
       return((int) c);
 
     case MY_LEX_NUMBER_IDENT:		// number or ident which num-start
@@ -600,7 +603,7 @@ int yylex(void *arg, void *yythd)
 	yyUnget();
       }
       // fall through
-    case MY_LEX_IDENT_START:		// Incomplete ident
+    case MY_LEX_IDENT_START:			// We come here after '.'
 #if defined(USE_MB) && defined(USE_MB_IDENT)
       if (use_mb(cs))
       {
@@ -688,6 +691,7 @@ int yylex(void *arg, void *yythd)
       }
       if (c == delim)
 	yySkip();			// Skip end `
+      lex->next_state= MY_LEX_START;
       return(IDENT);
     }
     case MY_LEX_SIGNED_NUMBER:		// Incomplete signed number
@@ -706,9 +710,9 @@ int yylex(void *arg, void *yythd)
 	if (c != '.')
 	{
 	  if (c == '-' && my_isspace(cs,yyPeek()))
-	    state=MY_LEX_COMMENT;
+	    state= MY_LEX_COMMENT;
 	  else
-	    state = MY_LEX_CHAR;		// Return sign as single char
+	    state= MY_LEX_CHAR;		// Return sign as single char
 	  break;
 	}
 	yyUnget();			// Fix for next loop
@@ -867,7 +871,7 @@ int yylex(void *arg, void *yythd)
       else
 	state=MY_LEX_CHAR;		// Return '*'
       break;
-    case MY_LEX_SET_VAR:			// Check if ':='
+    case MY_LEX_SET_VAR:		// Check if ':='
       if (yyPeek() != '=')
       {
 	state=MY_LEX_CHAR;		// Return ':'
@@ -903,8 +907,8 @@ int yylex(void *arg, void *yythd)
 	state = MY_LEX_REAL;		// Real
       else
       {
-	state = MY_LEX_CHAR;		// return '.'
-	lex->next_state=MY_LEX_IDENT_START;// Next is an ident (not a keyword)
+	state= MY_LEX_IDENT_SEP;	// return '.'
+	yyUnget();			// Put back '.'
       }
       break;
     case MY_LEX_USER_END:		// end '@' of user@hostname
@@ -932,8 +936,11 @@ int yylex(void *arg, void *yythd)
     case MY_LEX_SYSTEM_VAR:
       yylval->lex_str.str=(char*) lex->ptr;
       yylval->lex_str.length=1;
-      lex->next_state=MY_LEX_IDENT_OR_KEYWORD;
       yySkip();					// Skip '@'
+      lex->next_state= (state_map[yyPeek()] ==
+			MY_LEX_USER_VARIABLE_DELIMITER ?
+			MY_LEX_OPERATOR_OR_IDENT :
+			MY_LEX_IDENT_OR_KEYWORD);
       return((int) '@');
     case MY_LEX_IDENT_OR_KEYWORD:
       /*
@@ -941,7 +948,6 @@ int yylex(void *arg, void *yythd)
 	We should now be able to handle:
 	[(global | local | session) .]variable_name
       */
-
       while (ident_map[c=yyGet()]) ;
       if (c == '.')
 	lex->next_state=MY_LEX_IDENT_SEP;
@@ -965,7 +971,7 @@ void st_select_lex_node::init_query()
 {
   options= 0;
   linkage= UNSPECIFIED_TYPE;
-  no_table_names_allowed= uncacheable= dependent= 0;
+  no_error= no_table_names_allowed= uncacheable= dependent= 0;
   ref_pointer_array= 0;
   cond_count= 0;
 }
diff --git a/sql/sql_lex.h b/sql/sql_lex.h
index f4bc0b9a8d1..e8fff47d7f5 100644
--- a/sql/sql_lex.h
+++ b/sql/sql_lex.h
@@ -214,6 +214,7 @@ public:
   bool dependent;	/* dependent from outer select subselect */
   bool uncacheable;     /* result of this query can't be cached */
   bool no_table_names_allowed; /* used for global order by */
+  bool no_error; /* suppress error message (convert it to warnings) */
 
   static void *operator new(size_t size)
   {
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index 12acfb9b5df..d030eaf617c 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -318,8 +318,8 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
     error= -1;				// Error on read
     goto err;
   }
-  sprintf(name,ER(ER_LOAD_INFO),info.records,info.deleted,
-	  info.records-info.copied,thd->cuted_fields);
+  sprintf(name, ER(ER_LOAD_INFO), (ulong) info.records, (ulong) info.deleted,
+	  (ulong) (info.records - info.copied), (ulong) thd->cuted_fields);
   send_ok(thd,info.copied+info.deleted,0L,name);
   // on the slave thd->query is never initialized
   if (!thd->slave_thread)
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 946dc654cad..534815460d0 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1057,6 +1057,11 @@ bool do_command(THD *thd)
 
   net= &thd->net;
   thd->current_tablenr=0;
+  /*
+    indicator of uninitialized lex => normal flow of errors handling
+    (see my_message_sql)
+  */
+  thd->lex.current_select= 0;
 
   packet=0;
   old_timeout=net->read_timeout;
@@ -1285,6 +1290,11 @@ restore_user:
     mysql_stmt_free(thd, packet);
     break;
   }
+  case COM_RESET_STMT:
+  {
+    mysql_stmt_reset(thd, packet);
+    break;
+  }
   case COM_QUERY:
   {
     if (alloc_query(thd, packet, packet_length))
@@ -3396,11 +3406,6 @@ static bool check_merge_table_access(THD *thd, char *db,
     {
       if (!tmp->db || !tmp->db[0])
 	tmp->db=db;
-      else if (strcmp(tmp->db,db))
-      {
-	send_error(thd,ER_UNION_TABLES_IN_DIFFERENT_DIR);
-	return 1;
-      }
     }
     error=check_table_access(thd, SELECT_ACL | UPDATE_ACL | DELETE_ACL,
 			     table_list);
@@ -3578,15 +3583,20 @@ mysql_new_select(LEX *lex, bool move_down)
 
 void create_select_for_variable(const char *var_name)
 {
+  THD *thd;
   LEX *lex;
-  LEX_STRING tmp;
+  LEX_STRING tmp, null_lex_string;
   DBUG_ENTER("create_select_for_variable");
-  lex= current_lex;
+
+  thd= current_thd;
+  lex= &thd->lex;
   mysql_init_select(lex);
   lex->sql_command= SQLCOM_SELECT;
   tmp.str= (char*) var_name;
   tmp.length=strlen(var_name);
-  add_item_to_list(lex->thd, get_system_var(OPT_SESSION, tmp));
+  bzero((char*) &null_lex_string.str, sizeof(null_lex_string));
+  add_item_to_list(thd, get_system_var(thd, OPT_SESSION, tmp,
+				       null_lex_string));
   DBUG_VOID_RETURN;
 }
 
@@ -4434,6 +4444,7 @@ static bool append_file_to_dir(THD *thd, char **filename_ptr, char *table_name)
   return 0;
 }
 
+
 /*
   Check if the select is a simple select (not an union)
 
diff --git a/sql/sql_prepare.cc b/sql/sql_prepare.cc
index fcddc2d2252..550e4bbe086 100644
--- a/sql/sql_prepare.cc
+++ b/sql/sql_prepare.cc
@@ -659,10 +659,13 @@ static bool mysql_test_select_fields(PREP_STMT *stmt, TABLE_LIST *tables,
 		    wild_num, conds, og_num, order, group, having, proc, 
                     select_lex, unit, 0))
     DBUG_RETURN(1);
+#ifndef EMBEDDED_LIBRARY
     if (send_prep_stmt(stmt, fields.elements) ||
         thd->protocol_simple.send_fields(&fields, 0) ||
+        net_flush(&thd->net) ||
         send_item_params(stmt))
       DBUG_RETURN(1);
+#endif
     join->cleanup();
   }
   DBUG_RETURN(0);  
@@ -962,7 +965,7 @@ void mysql_stmt_reset(THD *thd, char *packet)
   PREP_STMT *stmt;
   DBUG_ENTER("mysql_stmt_reset");
 
-  if (!(stmt=find_prepared_statement(thd, stmt_id, "close")))
+  if (!(stmt= find_prepared_statement(thd, stmt_id, "reset")))
   {
     send_error(thd);
     DBUG_VOID_RETURN;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 31bb8ffc032..0aa7e67a12b 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -31,8 +31,11 @@
 #include <ft_global.h>
 
 const char *join_type_str[]={ "UNKNOWN","system","const","eq_ref","ref",
-			      "MAYBE_REF","ALL","range","index","fulltext" };
+			      "MAYBE_REF","ALL","range","index","fulltext",
+			      "ref_or_null"
+};
 
+static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array);
 static bool make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
 				 DYNAMIC_ARRAY *keyuse);
 static bool update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,
@@ -106,6 +109,8 @@ static int join_read_prev_same(READ_RECORD *info);
 static int join_read_prev(READ_RECORD *info);
 static int join_ft_read_first(JOIN_TAB *tab);
 static int join_ft_read_next(READ_RECORD *info);
+static int join_read_always_key_or_null(JOIN_TAB *tab);
+static int join_read_next_same_or_null(READ_RECORD *info);
 static COND *make_cond_for_table(COND *cond,table_map table,
 				 table_map used_table);
 static Item* part_of_refkey(TABLE *form,Field *field);
@@ -137,6 +142,7 @@ static ORDER *create_distinct_group(THD *thd, ORDER *order,
 static bool test_if_subpart(ORDER *a,ORDER *b);
 static TABLE *get_sort_by_table(ORDER *a,ORDER *b,TABLE_LIST *tables);
 static void calc_group_buffer(JOIN *join,ORDER *group);
+static bool make_group_fields(JOIN *main_join, JOIN *curr_join);
 static bool alloc_group_fields(JOIN *join,ORDER *group);
 // Create list for using with tempory table
 static bool change_to_use_tmp_fields(THD *thd, Item **ref_pointer_array,
@@ -436,6 +442,9 @@ JOIN::optimize()
     DBUG_RETURN(0);
   optimized= 1;
 
+  // Ignore errors of execution if option IGNORE present
+  if (thd->lex.duplicates == DUP_IGNORE)
+    thd->lex.current_select->no_error= 1;
 #ifdef HAVE_REF_TO_FIELDS			// Not done yet
   /* Add HAVING to WHERE if possible */
   if (having && !group_list && !sum_func_count)
@@ -1114,12 +1123,12 @@ JOIN::exec()
 	  DBUG_VOID_RETURN;
 	curr_join->exec_tmp_table2= exec_tmp_table2;
       }
-      if (group_list)
+      if (curr_join->group_list)
       {
 	thd->proc_info= "Creating sort index";
 	if (create_sort_index(thd, curr_join->join_tab, curr_join->group_list,
 			      HA_POS_ERROR, HA_POS_ERROR) ||
-	    alloc_group_fields(curr_join, curr_join->group_list))
+	    make_group_fields(this, curr_join))
 	{
 	  DBUG_VOID_RETURN;
 	}
@@ -1128,6 +1137,20 @@ JOIN::exec()
       
       thd->proc_info="Copying to group table";
       tmp_error= -1;
+      if (curr_join != this)
+      {
+	if (sum_funcs2)
+	{
+	  curr_join->sum_funcs= sum_funcs2;
+	  curr_join->sum_funcs_end= sum_funcs_end2; 
+	}
+	else
+	{
+	  curr_join->alloc_func_list();
+	  sum_funcs2= curr_join->sum_funcs;
+	  sum_funcs_end2= curr_join->sum_funcs_end;
+	}
+      }
       if (curr_join->make_sum_func_list(*curr_all_fields, *curr_fields_list,
 					1) ||
 	  (tmp_error= do_select(curr_join, (List<Item> *) 0, curr_tmp_table,
@@ -1186,7 +1209,10 @@ JOIN::exec()
   if (curr_join->group || curr_join->tmp_table_param.sum_func_count ||
       (procedure && (procedure->flags & PROC_GROUP)))
   {
-    alloc_group_fields(curr_join, curr_join->group_list);
+    if (make_group_fields(this, curr_join))
+    {
+      DBUG_VOID_RETURN;
+    }
     if (!items3)
     {
       if (!items0)
@@ -1456,8 +1482,9 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
 		     DYNAMIC_ARRAY *keyuse_array)
 {
   int error;
-  uint i,table_count,const_count,found_ref,refs,key,const_ref,eq_part;
-  table_map found_const_table_map,all_table_map;
+  uint i,table_count,const_count,key;
+  table_map found_const_table_map, all_table_map, found_ref, refs;
+  key_map const_ref, eq_part;
   TABLE **table_vector;
   JOIN_TAB *stat,*stat_end,*s,**stat_ref;
   KEYUSE *keyuse,*start_keyuse;
@@ -1475,7 +1502,7 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
   join->best_ref=stat_vector;
 
   stat_end=stat+table_count;
-  found_const_table_map=all_table_map=0;
+  found_const_table_map= all_table_map=0;
   const_count=0;
 
   for (s=stat,i=0 ; tables ; s++,tables=tables->next,i++)
@@ -1632,16 +1659,17 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
 	  key=keyuse->key;
 	  s->keys|= (key_map) 1 << key;		// QQ: remove this ?
 
-	  refs=const_ref=eq_part=0;
+	  refs=const_ref=0;
+	  eq_part=0;
 	  do
 	  {
-	    if (keyuse->val->type() != Item::NULL_ITEM)
+	    if (keyuse->val->type() != Item::NULL_ITEM && !keyuse->optimize)
 	    {
 	      if (!((~found_const_table_map) & keyuse->used_tables))
 		const_ref|= (key_map) 1 << keyuse->keypart;
 	      else
 		refs|=keyuse->used_tables;
-	      eq_part|= (uint) 1 << keyuse->keypart;
+	      eq_part|= (key_map) 1 << keyuse->keypart;
 	    }
 	    keyuse++;
 	  } while (keyuse->table == table && keyuse->key == key);
@@ -1700,8 +1728,6 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
     if (s->worst_seeks < 2.0)			// Fix for small tables
       s->worst_seeks=2.0;
 
-    /* if (s->type == JT_EQ_REF)
-      continue; */
     if (s->const_keys)
     {
       ha_rows records;
@@ -1752,7 +1778,10 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
   join->found_const_table_map=found_const_table_map;
 
   if (join->const_tables != join->tables)
+  {
+    optimize_keyuse(join, keyuse_array);
     find_best_combination(join,all_table_map & ~join->const_table_map);
+  }
   else
   {
     memcpy((gptr) join->best_positions,(gptr) join->positions,
@@ -1774,13 +1803,26 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
 typedef struct key_field_t {		// Used when finding key fields
   Field		*field;
   Item		*val;			// May be empty if diff constant
-  uint		level,const_level;	// QQ: Remove const_level
+  uint		level;
+  uint		optimize;
   bool		eq_func;
-  bool		exists_optimize;
 } KEY_FIELD;
 
+/* Values in optimize */
+#define KEY_OPTIMIZE_EXISTS		1
+#define KEY_OPTIMIZE_REF_OR_NULL	2
 
-/* merge new key definitions to old ones, remove those not used in both */
+/*
+  Merge new key definitions to old ones, remove those not used in both
+
+  This is called for OR between different levels
+
+  To be able to do 'ref_or_null' we merge a comparison of a column
+  and 'column IS NULL' to one test.  This is useful for sub select queries
+  that are internally transformed to something like:
+
+  SELECT * FROM t1 WHERE t1.key=outer_ref_field or t1.key IS NULL 
+*/
 
 static KEY_FIELD *
 merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
@@ -1802,20 +1844,46 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
       {
 	if (new_fields->val->used_tables())
 	{
+	  /*
+	    If the value matches, we can use the key reference.
+	    If not, we keep it until we have examined all new values
+	  */
 	  if (old->val->eq(new_fields->val, old->field->binary()))
 	  {
-	    old->level=old->const_level=and_level;
-	    old->exists_optimize&=new_fields->exists_optimize;
+	    old->level= and_level;
+	    old->optimize= ((old->optimize & new_fields->optimize &
+			     KEY_OPTIMIZE_EXISTS) |
+			    ((old->optimize | new_fields->optimize) &
+			     KEY_OPTIMIZE_REF_OR_NULL));
 	  }
 	}
-	else if (old->val->eq(new_fields->val, old->field->binary()) &&
-		 old->eq_func && new_fields->eq_func)
+	else if (old->eq_func && new_fields->eq_func &&
+		 old->val->eq(new_fields->val, old->field->binary()))
+		 
 	{
-	  old->level=old->const_level=and_level;
-	  old->exists_optimize&=new_fields->exists_optimize;
+	  old->level= and_level;
+	  old->optimize= ((old->optimize & new_fields->optimize &
+			   KEY_OPTIMIZE_EXISTS) |
+			  ((old->optimize | new_fields->optimize) &
+			   KEY_OPTIMIZE_REF_OR_NULL));
 	}
-	else					// Impossible; remove it
+	else if (old->eq_func && new_fields->eq_func &&
+		 (old->val->is_null() || new_fields->val->is_null()))
 	{
+	  /* field = expression OR field IS NULL */
+	  old->level= and_level;
+	  old->optimize= KEY_OPTIMIZE_REF_OR_NULL;
+	  /* Remember the NOT NULL value */
+	  if (old->val->is_null())
+	    old->val= new_fields->val;
+	}
+	else
+	{
+	  /*
+	    We are comparing two different const.  In this case we can't
+	    use a key-lookup on this so it's better to remove the value
+	    and let the range optimzier handle it
+	  */
 	  if (old == --first_free)		// If last item
 	    break;
 	  *old= *first_free;			// Remove old value
@@ -1827,7 +1895,7 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
   /* Remove all not used items */
   for (KEY_FIELD *old=start ; old != first_free ;)
   {
-    if (old->level != and_level && old->const_level != and_level)
+    if (old->level != and_level)
     {						// Not used in all levels
       if (old == --first_free)
 	break;
@@ -1840,32 +1908,53 @@ merge_key_fields(KEY_FIELD *start,KEY_FIELD *new_fields,KEY_FIELD *end,
 }
 
 
+/*
+  Add a possible key to array of possible keys if it's usable as a key
+
+  SYNPOSIS
+    add_key_field()
+    key_fields			Pointer to add key, if usable
+    and_level			And level, to be stored in KEY_FIELD
+    field			Field used in comparision
+    eq_func			True if we used =, <=> or IS NULL
+    value			Value used for comparison with field
+				Is NULL for BETWEEN and IN    
+    usable_tables		Tables which can be used for key optimization
+
+  NOTES
+    If we are doing a NOT NULL comparison on a NOT NULL field in a outer join
+    table, we store this to be able to do not exists optimization later.
+
+  RETURN
+    *key_fields is incremented if we stored a key in the array
+*/
+
 static void
 add_key_field(KEY_FIELD **key_fields,uint and_level,
 	      Field *field,bool eq_func,Item *value,
 	      table_map usable_tables)
 {
-  bool exists_optimize=0;
+  uint exists_optimize= 0;
   if (!(field->flags & PART_KEY_FLAG))
   {
     // Don't remove column IS NULL on a LEFT JOIN table
     if (!eq_func || !value || value->type() != Item::NULL_ITEM ||
 	!field->table->maybe_null || field->null_ptr)
       return;					// Not a key. Skip it
-    exists_optimize=1;
+    exists_optimize= KEY_OPTIMIZE_EXISTS;
   }
   else
   {
     table_map used_tables=0;
-    if (value && (used_tables=value->used_tables()) &
-	(field->table->map | RAND_TABLE_BIT))
+    if (value && ((used_tables=value->used_tables()) &
+		  (field->table->map | RAND_TABLE_BIT)))
       return;
     if (!(usable_tables & field->table->map))
     {
       if (!eq_func || !value || value->type() != Item::NULL_ITEM ||
 	  !field->table->maybe_null || field->null_ptr)
 	return;					// Can't use left join optimize
-      exists_optimize=1;
+      exists_optimize= KEY_OPTIMIZE_EXISTS;
     }
     else
     {
@@ -1880,20 +1969,23 @@ add_key_field(KEY_FIELD **key_fields,uint and_level,
 	return;					// Can't be used as eq key
       }
 
-      /* Save the following cases:
-	 Field op constant
-	 Field LIKE constant where constant doesn't start with a wildcard
-	 Field = field2 where field2 is in a different table
-	 Field op formula
-	 Field IS NULL
-	 Field IS NOT NULL
+      /*
+	Save the following cases:
+	Field op constant
+	Field LIKE constant where constant doesn't start with a wildcard
+	Field = field2 where field2 is in a different table
+	Field op formula
+	Field IS NULL
+	Field IS NOT NULL
       */
       stat[0].key_dependent|=used_tables;
       if (value->const_item())
 	stat[0].const_keys |= possible_keys;
 
-      /* We can't always use indexes when comparing a string index to a
-	 number. cmp_type() is checked to allow compare of dates to numbers */
+      /*
+	We can't always use indexes when comparing a string index to a
+	number. cmp_type() is checked to allow compare of dates to numbers
+      */
       if (!eq_func ||
 	  field->result_type() == STRING_RESULT &&
 	  value->result_type() != STRING_RESULT &&
@@ -1902,11 +1994,11 @@ add_key_field(KEY_FIELD **key_fields,uint and_level,
     }
   }
   /* Store possible eq field */
-  (*key_fields)->field=field;
-  (*key_fields)->eq_func=eq_func;
-  (*key_fields)->val=value;
-  (*key_fields)->level=(*key_fields)->const_level=and_level;
-  (*key_fields)->exists_optimize=exists_optimize;
+  (*key_fields)->field=		field;
+  (*key_fields)->eq_func=	eq_func;
+  (*key_fields)->val=		value;
+  (*key_fields)->level=		and_level;
+  (*key_fields)->optimize=	exists_optimize;
   (*key_fields)++;
 }
 
@@ -1926,12 +2018,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
       while ((item=li++))
 	add_key_fields(stat,key_fields,and_level,item,usable_tables);
       for (; org_key_fields != *key_fields ; org_key_fields++)
-      {
-	if (org_key_fields->const_level == org_key_fields->level)
-	  org_key_fields->const_level=org_key_fields->level= *and_level;
-	else
-	  org_key_fields->const_level= *and_level;
-      }
+	org_key_fields->level= *and_level;
     }
     else
     {
@@ -1959,9 +2046,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
     break;
   case Item_func::OPTIMIZE_KEY:
     if (cond_func->key_item()->real_item()->type() == Item::FIELD_ITEM &&
-	// field from outer query can't be used as key
-	!((Item_field*) (cond_func->key_item()->real_item()))
-	->depended_from)
+	!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
       add_key_field(key_fields,*and_level,
 		    ((Item_field*) (cond_func->key_item()->real_item()))
 		    ->field,
@@ -1973,9 +2058,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
 		     cond_func->functype() == Item_func::EQUAL_FUNC);
 
     if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
-	// field from outer query can't be used as key
-	!((Item_field*) (cond_func->arguments()[0]->real_item()))
-	->depended_from)
+	!(cond_func->arguments()[0]->used_tables() & OUTER_REF_TABLE_BIT))
     {
       add_key_field(key_fields,*and_level,
 		    ((Item_field*) (cond_func->arguments()[0])->real_item())
@@ -1985,9 +2068,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
     }
     if (cond_func->arguments()[1]->real_item()->type() == Item::FIELD_ITEM &&
 	cond_func->functype() != Item_func::LIKE_FUNC &&
-	// field from outer query can't be used as key
-	!((Item_field*) (cond_func->arguments()[1]->real_item()))
-	->depended_from)
+	!(cond_func->arguments()[1]->used_tables() & OUTER_REF_TABLE_BIT))
     {
       add_key_field(key_fields,*and_level,
 		    ((Item_field*) (cond_func->arguments()[1])->real_item())
@@ -2000,9 +2081,7 @@ add_key_fields(JOIN_TAB *stat,KEY_FIELD **key_fields,uint *and_level,
   case Item_func::OPTIMIZE_NULL:
     /* column_name IS [NOT] NULL */
     if (cond_func->arguments()[0]->real_item()->type() == Item::FIELD_ITEM &&
-	// field from outer query can't be used as key
-	!((Item_field*) (cond_func->arguments()[0]->real_item()))
-	->depended_from)
+	!(cond_func->used_tables() & OUTER_REF_TABLE_BIT))
     {
       add_key_field(key_fields,*and_level,
 		    ((Item_field*) (cond_func->arguments()[0])->real_item())
@@ -2036,7 +2115,7 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field)
   TABLE *form= field->table;
   KEYUSE keyuse;
 
-  if (key_field->eq_func && !key_field->exists_optimize)
+  if (key_field->eq_func && !(key_field->optimize & KEY_OPTIMIZE_EXISTS))
   {
     for (uint key=0 ; key < form->keys ; key++)
     {
@@ -2054,7 +2133,9 @@ add_key_part(DYNAMIC_ARRAY *keyuse_array,KEY_FIELD *key_field)
 	  keyuse.val =  key_field->val;
 	  keyuse.key =  key;
 	  keyuse.keypart=part;
+	  keyuse.keypart_map= (key_part_map) 1 << part;
 	  keyuse.used_tables=key_field->val->used_tables();
+	  keyuse.optimize= key_field->optimize & KEY_OPTIMIZE_REF_OR_NULL;
 	  VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
 	}
       }
@@ -2138,16 +2219,23 @@ add_ft_keys(DYNAMIC_ARRAY *keyuse_array,
   VOID(insert_dynamic(keyuse_array,(gptr) &keyuse));
 }
 
+
 static int
 sort_keyuse(KEYUSE *a,KEYUSE *b)
 {
+  int res;
   if (a->table->tablenr != b->table->tablenr)
     return (int) (a->table->tablenr - b->table->tablenr);
   if (a->key != b->key)
     return (int) (a->key - b->key);
   if (a->keypart != b->keypart)
     return (int) (a->keypart - b->keypart);
-  return test(a->used_tables) - test(b->used_tables);	// Place const first
+  // Place const values before other ones
+  if ((res= test(a->used_tables) - test(b->used_tables)))
+    return res;
+  /* Place rows that are not 'OPTIMIZE_REF_OR_NULL' first */
+  return (int) ((a->optimize & KEY_OPTIMIZE_REF_OR_NULL) -
+		(b->optimize & KEY_OPTIMIZE_REF_OR_NULL));
 }
 
 
@@ -2163,31 +2251,28 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
 		    SELECT_LEX *select_lex)
 {
   uint	and_level,i,found_eq_constant;
+  KEY_FIELD *key_fields,*end;
 
+  if (!(key_fields=(KEY_FIELD*)
+	thd->alloc(sizeof(key_fields[0])*
+		   (thd->lex.current_select->cond_count+1)*2)))
+    return TRUE; /* purecov: inspected */
+  and_level=0; end=key_fields;
+  if (cond)
+    add_key_fields(join_tab,&end,&and_level,cond,normal_tables);
+  for (i=0 ; i < tables ; i++)
   {
-    KEY_FIELD *key_fields,*end;
-
-    if (!(key_fields=(KEY_FIELD*)
-	  thd->alloc(sizeof(key_fields[0])*
-		     (thd->lex.current_select->cond_count+1)*2)))
-      return TRUE; /* purecov: inspected */
-    and_level=0; end=key_fields;
-    if (cond)
-      add_key_fields(join_tab,&end,&and_level,cond,normal_tables);
-    for (i=0 ; i < tables ; i++)
+    if (join_tab[i].on_expr)
     {
-      if (join_tab[i].on_expr)
-      {
-	add_key_fields(join_tab,&end,&and_level,join_tab[i].on_expr,
-		       join_tab[i].table->map);
-      }
+      add_key_fields(join_tab,&end,&and_level,join_tab[i].on_expr,
+		     join_tab[i].table->map);
     }
-    if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64))
-      return TRUE;
-    /* fill keyuse with found key parts */
-    for (KEY_FIELD *field=key_fields ; field != end ; field++)
-      add_key_part(keyuse,field);
   }
+  if (my_init_dynamic_array(keyuse,sizeof(KEYUSE),20,64))
+    return TRUE;
+  /* fill keyuse with found key parts */
+  for (KEY_FIELD *field=key_fields ; field != end ; field++)
+    add_key_part(keyuse,field);
 
   if (select_lex->ftfunc_list->elements)
   {
@@ -2195,9 +2280,10 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
   }
 
   /*
-    Remove ref if there is a keypart which is a ref and a const.
-    Remove keyparts without previous keyparts.
     Special treatment for ft-keys.
+    Remove the following things from KEYUSE:
+    - ref if there is a keypart which is a ref and a const.
+    - keyparts without previous keyparts.
   */
   if (keyuse->elements)
   {
@@ -2215,8 +2301,7 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
     for (i=0 ; i < keyuse->elements-1 ; i++,use++)
     {
       if (!use->used_tables)
-	use->table->const_key_parts[use->key] |=
-	  (key_part_map) 1 << use->keypart;
+	use->table->const_key_parts[use->key]|= use->keypart_map;
       if (use->keypart != FT_KEYPART)
       {
 	if (use->key == prev->key && use->table == prev->table)
@@ -2245,6 +2330,41 @@ update_ref_and_keys(THD *thd, DYNAMIC_ARRAY *keyuse,JOIN_TAB *join_tab,
   return FALSE;
 }
 
+/*
+  Update some values in keyuse for faster find_best_combination() loop
+*/
+
+static void optimize_keyuse(JOIN *join, DYNAMIC_ARRAY *keyuse_array)
+{
+  KEYUSE *end,*keyuse= dynamic_element(keyuse_array, 0, KEYUSE*);
+
+  for (end= keyuse+ keyuse_array->elements ; keyuse < end ; keyuse++)
+  {
+    table_map map;
+    /*
+      If we find a ref, assume this table matches a proportional
+      part of this table.
+      For example 100 records matching a table with 5000 records
+      gives 5000/100 = 50 records per key
+      Constant tables are ignored.
+      To avoid bad matches, we don't make ref_table_rows less than 100.
+    */
+    keyuse->ref_table_rows= ~(table_map) 0;	// If no ref
+    if (keyuse->used_tables &
+	(map= (keyuse->used_tables & ~join->const_table_map &
+	       ~OUTER_REF_TABLE_BIT)))
+    {
+      uint tablenr;
+      for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ;
+      if (map == 1)			// Only one table
+      {
+	TABLE *tmp_table=join->all_tables[tablenr];
+	keyuse->ref_table_rows= max(tmp_table->file->records, 100);
+      }
+    }
+  }
+}
+
 
 /*****************************************************************************
   Go through all combinations of not marked tables and find the one
@@ -2331,7 +2451,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 	uint max_key_part=0;
 
 	/* Test how we can use keys */
-	rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE;  /* Assumed records/key */
+	rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE;  // Assumed records/key
 	for (keyuse=s->keyuse ; keyuse->table == table ;)
 	{
 	  key_map found_part=0;
@@ -2339,44 +2459,27 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 	  uint key=keyuse->key;
 	  KEY *keyinfo=table->key_info+key;
           bool ft_key=(keyuse->keypart == FT_KEYPART);
+	  uint found_ref_or_null= 0;
 
+	  /* Calculate how many key segments of the current key we can use */
 	  start_key=keyuse;
 	  do
 	  {
             uint keypart=keyuse->keypart;
+	    uint found_part_ref_or_null= KEY_OPTIMIZE_REF_OR_NULL;
 	    do
 	    {
-              if (!ft_key)
-              {
-		table_map map;
-		if (!(rest_tables & keyuse->used_tables))
-		{
-		  found_part|= (key_part_map) 1 << keypart;
-		  found_ref|= keyuse->used_tables;
-		}
-		/*
-		  If we find a ref, assume this table matches a proportional
-		  part of this table.
-		  For example 100 records matching a table with 5000 records
-		  gives 5000/100 = 50 records per key
-		  Constant tables are ignored and to avoid bad matches,
-		  we don't make rec less than 100.
-		*/
-		if (keyuse->used_tables &
-		    (map=(keyuse->used_tables & ~join->const_table_map &
-			  ~OUTER_REF_TABLE_BIT)))
-		{
-		  uint tablenr;
-		  for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ;
-		  if (map == 1)			// Only one table
-		  {
-		    TABLE *tmp_table=join->all_tables[tablenr];
-		    if (rec > tmp_table->file->records && rec > 100)
-		      rec=max(tmp_table->file->records,100);
-		  }
-		}
+	      if (!(rest_tables & keyuse->used_tables) &&
+		  !(found_ref_or_null & keyuse->optimize))
+	      {
+		found_part|=keyuse->keypart_map;
+		found_ref|= keyuse->used_tables;
+		if (rec > keyuse->ref_table_rows)
+		  rec= keyuse->ref_table_rows;
+		found_part_ref_or_null&= keyuse->optimize;
               }
 	      keyuse++;
+	      found_ref_or_null|= found_part_ref_or_null;
 	    } while (keyuse->table == table && keyuse->key == key &&
 		     keyuse->keypart == keypart);
 	  } while (keyuse->table == table && keyuse->key == key);
@@ -2386,8 +2489,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 	  */
           if (!found_part && !ft_key)
 	    continue;				// Nothing usable found
-	  if (rec == 0)
-	    rec=1L;				// Fix for small tables
+	  if (rec < MATCHING_ROWS_IN_OTHER_TABLE)
+	    rec= MATCHING_ROWS_IN_OTHER_TABLE;	// Fix for small tables
 
           /*
 	    ft-keys require special treatment
@@ -2406,7 +2509,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 	  /*
 	    Check if we found full key
 	  */
-	  if (found_part == PREV_BITS(uint,keyinfo->key_parts))
+	  if (found_part == PREV_BITS(uint,keyinfo->key_parts) &&
+	      !found_ref_or_null)
 	  {				/* use eq key */
 	    max_key_part= (uint) ~0;
 	    if ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)
@@ -2459,7 +2563,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 	      Set tmp to (previous record count) * (records / combination)
 	    */
 	    if ((found_part & 1) &&
-		!(table->file->index_flags(key) & HA_ONLY_WHOLE_INDEX))
+		(!(table->file->index_flags(key) & HA_ONLY_WHOLE_INDEX) ||
+		 found_part == PREV_BITS(uint,keyinfo->key_parts)))
 	    {
 	      max_key_part=max_part_bit(found_part);
 	      /*
@@ -2509,6 +2614,12 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 		  }
 		  records=(ulong) tmp;
 		}
+		if (found_ref_or_null)
+		{
+		  /* We need to do two key searches to find key */
+		  tmp*= 2.0;
+		  records*= 2.0;
+		}
 	      }
 	      if (table->used_keys & ((key_map) 1 << key))
 	      {
@@ -2758,9 +2869,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
   TABLE *table;
   KEY *keyinfo;
 
-  /*
-    Use best key from find_best
-  */
+  /*  Use best key from find_best */
   table=j->table;
   key=keyuse->key;
   keyinfo=table->key_info+key;
@@ -2776,14 +2885,22 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
   else
   {
     keyparts=length=0;
+    uint found_part_ref_or_null= 0;
+    /*
+      Calculate length for the used key
+      Stop if there is a missing key part or when we find second key_part
+      with KEY_OPTIMIZE_REF_OR_NULL
+    */
     do
     {
-      if (!((~used_tables) & keyuse->used_tables))
+      if (!(~used_tables & keyuse->used_tables))
       {
-	if (keyparts == keyuse->keypart)
+	if (keyparts == keyuse->keypart &&
+	    !(found_part_ref_or_null & keyuse->optimize))
 	{
 	  keyparts++;
-	  length+=keyinfo->key_part[keyuse->keypart].store_length;
+	  length+= keyinfo->key_part[keyuse->keypart].store_length;
+	  found_part_ref_or_null|= keyuse->optimize;
 	}
       }
       keyuse++;
@@ -2806,8 +2923,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
   j->ref.key_err=1;
   keyuse=org_keyuse;
 
-  store_key **ref_key=j->ref.key_copy;
-  byte *key_buff=j->ref.key_buff;
+  store_key **ref_key= j->ref.key_copy;
+  byte *key_buff=j->ref.key_buff, *null_ref_key= 0;
   if (ftkey)
   {
     j->ref.items[0]=((Item_func*)(keyuse->val))->key_item();
@@ -2835,9 +2952,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
                            maybe_null ?  (char*) key_buff : 0,
                            keyinfo->key_part[i].length, keyuse->val);
 	if (thd->is_fatal_error)
-	{
 	  return TRUE;
-	}
 	tmp.copy();
       }
       else
@@ -2845,17 +2960,25 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j, KEYUSE *org_keyuse,
 				  keyuse,join->const_table_map,
 				  &keyinfo->key_part[i],
 				  (char*) key_buff,maybe_null);
+      /* Remmeber if we are going to use REF_OR_NULL */
+      if (keyuse->optimize & KEY_OPTIMIZE_REF_OR_NULL)
+	null_ref_key= key_buff;
       key_buff+=keyinfo->key_part[i].store_length;
     }
   } /* not ftkey */
   *ref_key=0;				// end_marker
-  if (j->type == JT_FT)  /* no-op */;
-  else if (j->type == JT_CONST)
-    j->table->const_table=1;
+  if (j->type == JT_FT)
+    return 0;
+  if (j->type == JT_CONST)
+    j->table->const_table= 1;
   else if (((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY))
-	    != HA_NOSAME) ||
-	   keyparts != keyinfo->key_parts)
-    j->type=JT_REF;				/* Must read with repeat */
+	    != HA_NOSAME) || keyparts != keyinfo->key_parts ||
+	   null_ref_key)
+  {
+    /* Must read with repeat */
+    j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
+    j->null_ref_key= null_ref_key;
+  }
   else if (ref_key == j->ref.key_copy)
   {
     /*
@@ -3166,6 +3289,7 @@ make_join_readinfo(JOIN *join, uint options)
 	table->file->extra(HA_EXTRA_KEYREAD);
       }
       break;
+    case JT_REF_OR_NULL:
     case JT_REF:
       table->status=STATUS_NO_RECORD;
       if (tab->select)
@@ -3176,14 +3300,22 @@ make_join_readinfo(JOIN *join, uint options)
       delete tab->quick;
       tab->quick=0;
       table->file->index_init(tab->ref.key);
-      tab->read_first_record= join_read_always_key;
-      tab->read_record.read_record= join_read_next_same;
       if (table->used_keys & ((key_map) 1 << tab->ref.key) &&
 	  !table->no_keyread)
       {
 	table->key_read=1;
 	table->file->extra(HA_EXTRA_KEYREAD);
       }
+      if (tab->type == JT_REF)
+      {
+	tab->read_first_record= join_read_always_key;
+	tab->read_record.read_record= join_read_next_same;
+      }
+      else
+      {
+	tab->read_first_record= join_read_always_key_or_null;
+	tab->read_record.read_record= join_read_next_same_or_null;
+      }
       break;
     case JT_FT:
       table->status=STATUS_NO_RECORD;
@@ -5194,6 +5326,40 @@ flush_cached_records(JOIN *join,JOIN_TAB *join_tab,bool skipp_last)
   The different ways to read a record
   Returns -1 if row was not found, 0 if row was found and 1 on errors
 *****************************************************************************/
+
+/* Help function when we get some an error from the table handler */
+
+static int report_error(TABLE *table, int error)
+{
+  if (error == HA_ERR_END_OF_FILE || error == HA_ERR_KEY_NOT_FOUND)
+  {
+    table->status= STATUS_GARBAGE;
+    return -1;					// key not found; ok
+  }
+  /*
+    Locking reads can legally return also these errors, do not
+    print them to the .err log
+  */
+  if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
+    sql_print_error("Got error %d when reading table '%s'",
+		    error, table->path);
+  table->file->print_error(error,MYF(0));
+  return 1;
+}
+
+
+static int safe_index_read(JOIN_TAB *tab)
+{
+  int error;
+  TABLE *table= tab->table;
+  if ((error=table->file->index_read(table->record[0],
+				     tab->ref.key_buff,
+				     tab->ref.key_length, HA_READ_KEY_EXACT)))
+    return report_error(table, error);
+  return 0;
+}
+
+
 static int
 join_read_const_table(JOIN_TAB *tab, POSITION *pos)
 {
@@ -5248,10 +5414,7 @@ join_read_system(JOIN_TAB *tab)
 					   table->primary_key)))
     {
       if (error != HA_ERR_END_OF_FILE)
-      {
-	table->file->print_error(error,MYF(0));
-	return 1;
-      }
+	return report_error(table, error);
       table->null_row=1;			// This is ok.
       empty_record(table);			// Make empty record
       return -1;
@@ -5285,15 +5448,7 @@ join_read_const(JOIN_TAB *tab)
       table->null_row=1;
       empty_record(table);
       if (error != HA_ERR_KEY_NOT_FOUND)
-      {
-	/* Locking reads can legally return also these errors, do not
-	   print them to the .err log */
-	if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	  sql_print_error("read_const: Got error %d when reading table %s",
-			error, table->path);
-	table->file->print_error(error,MYF(0));
-	return 1;
-      }
+	return report_error(table, error);
       return -1;
     }
     store_record(table,record[1]);
@@ -5326,12 +5481,7 @@ join_read_key(JOIN_TAB *tab)
 				  tab->ref.key_buff,
 				  tab->ref.key_length,HA_READ_KEY_EXACT);
     if (error && error != HA_ERR_KEY_NOT_FOUND)
-    {
-      sql_print_error("read_key: Got error %d when reading table '%s'",error,
-		      table->path);
-      table->file->print_error(error,MYF(0));
-      return 1;
-    }
+      return report_error(table, error);
   }
   table->null_row=0;
   return table->status ? -1 : 0;
@@ -5351,18 +5501,13 @@ join_read_always_key(JOIN_TAB *tab)
 				     tab->ref.key_length,HA_READ_KEY_EXACT)))
   {
     if (error != HA_ERR_KEY_NOT_FOUND)
-    {
-      if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	sql_print_error("read_const: Got error %d when reading table %s",error,
-		      table->path);
-      table->file->print_error(error,MYF(0));
-      return 1;
-    }
+      return report_error(table, error);
     return -1; /* purecov: inspected */
   }
   return 0;
 }
 
+
 /*
   This function is used when optimizing away ORDER BY in 
   SELECT * FROM t1 WHERE a=1 ORDER BY a DESC,b DESC
@@ -5381,13 +5526,7 @@ join_read_last_key(JOIN_TAB *tab)
 					  tab->ref.key_length)))
   {
     if (error != HA_ERR_KEY_NOT_FOUND)
-    {
-      if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	sql_print_error("read_const: Got error %d when reading table %s",error,
-		      table->path);
-      table->file->print_error(error,MYF(0));
-      return 1;
-    }
+      return report_error(table, error);
     return -1; /* purecov: inspected */
   }
   return 0;
@@ -5414,19 +5553,14 @@ join_read_next_same(READ_RECORD *info)
 					  tab->ref.key_length)))
   {
     if (error != HA_ERR_END_OF_FILE)
-    {
-      if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	sql_print_error("read_next: Got error %d when reading table %s",error,
-		      table->path);
-      table->file->print_error(error,MYF(0));
-      return 1;
-    }
+      return report_error(table, error);
     table->status= STATUS_GARBAGE;
     return -1;
   }
   return 0;
 }
 
+
 static int
 join_read_prev_same(READ_RECORD *info)
 {
@@ -5435,23 +5569,9 @@ join_read_prev_same(READ_RECORD *info)
   JOIN_TAB *tab=table->reginfo.join_tab;
 
   if ((error=table->file->index_prev(table->record[0])))
-  {
-    if (error != HA_ERR_END_OF_FILE)
-    {
-      if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	sql_print_error("read_next: Got error %d when reading table %s",error,
-		      table->path);
-      table->file->print_error(error,MYF(0));
-      error= 1;
-    }
-    else
-    {
-      table->status= STATUS_GARBAGE;
-      error= -1;
-    }
-  }
-  else if (key_cmp(table, tab->ref.key_buff, tab->ref.key,
-		   tab->ref.key_length))
+    return report_error(table, error);
+  if (key_cmp(table, tab->ref.key_buff, tab->ref.key,
+	      tab->ref.key_length))
   {
     table->status=STATUS_NOT_FOUND;
     error= -1;
@@ -5488,6 +5608,7 @@ join_init_read_record(JOIN_TAB *tab)
   return (*tab->read_record.read_record)(&tab->read_record);
 }
 
+
 static int
 join_read_first(JOIN_TAB *tab)
 {
@@ -5505,17 +5626,10 @@ join_read_first(JOIN_TAB *tab)
   tab->read_record.file=table->file;
   tab->read_record.index=tab->index;
   tab->read_record.record=table->record[0];
-  error=tab->table->file->index_first(tab->table->record[0]);
-  if (error)
+  if ((error=tab->table->file->index_first(tab->table->record[0])))
   {
     if (error != HA_ERR_KEY_NOT_FOUND && error != HA_ERR_END_OF_FILE)
-    {
-      if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	sql_print_error("read_first_with_key: Got error %d when reading table",
-		      error);
-      table->file->print_error(error,MYF(0));
-      return 1;
-    }
+      report_error(table, error);
     return -1;
   }
   return 0;
@@ -5525,23 +5639,13 @@ join_read_first(JOIN_TAB *tab)
 static int
 join_read_next(READ_RECORD *info)
 {
-  int error=info->file->index_next(info->record);
-  if (error)
-  {
-    if (error != HA_ERR_END_OF_FILE)
-    {
-      if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	sql_print_error(
-		    "read_next_with_key: Got error %d when reading table %s",
-		      error, info->table->path);
-      info->file->print_error(error,MYF(0));
-      return 1;
-    }
-    return -1;
-  }
+  int error;
+  if ((error=info->file->index_next(info->record)))
+    return report_error(info->table, error);
   return 0;
 }
 
+
 static int
 join_read_last(JOIN_TAB *tab)
 {
@@ -5559,19 +5663,8 @@ join_read_last(JOIN_TAB *tab)
   tab->read_record.file=table->file;
   tab->read_record.index=tab->index;
   tab->read_record.record=table->record[0];
-  error=tab->table->file->index_last(tab->table->record[0]);
-  if (error)
-  {
-    if (error != HA_ERR_END_OF_FILE)
-    {
-      if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	sql_print_error("read_last_with_key: Got error %d when reading table",
-		      error, table->path);
-      table->file->print_error(error,MYF(0));
-      return 1;
-    }
-    return -1;
-  }
+  if ((error= tab->table->file->index_last(tab->table->record[0])))
+    return report_error(table, error);
   return 0;
 }
 
@@ -5579,20 +5672,9 @@ join_read_last(JOIN_TAB *tab)
 static int
 join_read_prev(READ_RECORD *info)
 {
-  int error=info->file->index_prev(info->record);
-  if (error)
-  {
-    if (error != HA_ERR_END_OF_FILE)
-    {
-      if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	sql_print_error(
-		  "read_prev_with_key: Got error %d when reading table: %s",
-		      error,info->table->path);
-      info->file->print_error(error,MYF(0));
-      return 1;
-    }
-    return -1;
-  }
+  int error;
+  if ((error= info->file->index_prev(info->record)))
+    return report_error(info->table, error);
   return 0;
 }
 
@@ -5609,42 +5691,57 @@ join_ft_read_first(JOIN_TAB *tab)
 #endif
   table->file->ft_init();
 
-  error=table->file->ft_read(table->record[0]);
-  if (error)
-  {
-    if (error != HA_ERR_END_OF_FILE)
-    {
-      if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	sql_print_error("ft_read_first: Got error %d when reading table %s",
-                      error, table->path);
-      table->file->print_error(error,MYF(0));
-      return 1;
-    }
-    return -1;
-  }
+  if ((error= table->file->ft_read(table->record[0])))
+    return report_error(table, error);
   return 0;
 }
 
 static int
 join_ft_read_next(READ_RECORD *info)
 {
-  int error=info->file->ft_read(info->table->record[0]);
-  if (error)
-  {
-    if (error != HA_ERR_END_OF_FILE)
-    {
-      if (error != HA_ERR_LOCK_DEADLOCK && error != HA_ERR_LOCK_WAIT_TIMEOUT)
-	sql_print_error("ft_read_next: Got error %d when reading table %s",
-                      error, info->table->path);
-      info->file->print_error(error,MYF(0));
-      return 1;
-    }
-    return -1;
-  }
+  int error;
+  if ((error= info->file->ft_read(info->table->record[0])))
+    return report_error(info->table, error);
   return 0;
 }
 
 
+/*
+  Reading of key with key reference and one part that may be NULL
+*/
+
+static int
+join_read_always_key_or_null(JOIN_TAB *tab)
+{
+  int res;
+
+  /* First read according to key which is NOT NULL */
+  *tab->null_ref_key=0;
+  if ((res= join_read_always_key(tab)) >= 0)
+    return res;
+
+  /* Then read key with null value */
+  *tab->null_ref_key= 1;
+  return safe_index_read(tab);
+}
+
+
+static int
+join_read_next_same_or_null(READ_RECORD *info)
+{
+  int error;
+  if ((error= join_read_next_same(info)) >= 0)
+    return error;
+  JOIN_TAB *tab= info->table->reginfo.join_tab;
+
+  /* Test if we have already done a read after null key */
+  if (*tab->null_ref_key)
+    return -1;					// All keys read
+  *tab->null_ref_key= 1;			// Read null key
+  return safe_index_read(tab);
+}
+
+
 /*****************************************************************************
   The different end of select functions
   These functions returns < 0 when end is reached, 0 on ok and > 0 if a
@@ -6371,10 +6468,13 @@ test_if_skip_sort_order(JOIN_TAB *tab,ORDER *order,ha_rows select_limit,
   }
 
   ref_key= -1;
-  if (tab->ref.key >= 0)			// Constant range in WHERE
+  /* Test if constant range in WHERE */
+  if (tab->ref.key >= 0)
   {
     ref_key=	   tab->ref.key;
     ref_key_parts= tab->ref.key_parts;
+    if (tab->type == JT_REF_OR_NULL)
+      DBUG_RETURN(0);
   }
   else if (select && select->quick)		// Range found by opt_range
   {
@@ -7561,6 +7661,37 @@ calc_group_buffer(JOIN *join,ORDER *group)
   join->tmp_table_param.group_null_parts=null_parts;
 }
 
+/*
+  alloc group fields or take prepared (chached)
+
+  SYNOPSYS
+    make_group_fields()
+    main_join - join of current select
+    curr_join - current join (join of current select or temporary copy of it)
+
+  RETURN
+    0 - ok
+    1 - failed
+*/
+
+static bool
+make_group_fields(JOIN *main_join, JOIN *curr_join)
+{
+    if (main_join->group_fields_cache.elements)
+    {
+      curr_join->group_fields= main_join->group_fields_cache;
+      curr_join->sort_and_group= 1;
+    }
+    else
+    {
+      if (alloc_group_fields(curr_join, curr_join->group_list))
+      {
+	return (1);
+      }
+      main_join->group_fields_cache= curr_join->group_fields;
+    }
+    return (0);
+}
 
 /*
   Get a list of buffers for saveing last group
@@ -7587,6 +7718,7 @@ alloc_group_fields(JOIN *join,ORDER *group)
 static int
 test_if_group_changed(List<Item_buff> &list)
 {
+  DBUG_ENTER("test_if_group_changed");
   List_iterator<Item_buff> li(list);
   int idx= -1,i;
   Item_buff *buff;
@@ -7596,7 +7728,8 @@ test_if_group_changed(List<Item_buff> &list)
     if (buff->cmp())
       idx=i;
   }
-  return idx;
+  DBUG_PRINT("info", ("idx: %d", idx));
+  DBUG_RETURN(idx);
 }
 
 
diff --git a/sql/sql_select.h b/sql/sql_select.h
index df21d337b54..7de9007369e 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -27,8 +27,10 @@
 typedef struct keyuse_t {
   TABLE *table;
   Item	*val;				/* or value if no field */
-  uint	key,keypart;
   table_map used_tables;
+  uint	key, keypart, optimize;
+  key_map   keypart_map;
+  ha_rows   ref_table_rows;
 } KEYUSE;
 
 class store_key;
@@ -73,7 +75,7 @@ typedef struct st_join_cache {
 */
 
 enum join_type { JT_UNKNOWN,JT_SYSTEM,JT_CONST,JT_EQ_REF,JT_REF,JT_MAYBE_REF,
-		 JT_ALL, JT_RANGE, JT_NEXT, JT_FT};
+		 JT_ALL, JT_RANGE, JT_NEXT, JT_FT, JT_REF_OR_NULL};
 
 class JOIN;
 
@@ -85,6 +87,7 @@ typedef struct st_join_table {
   QUICK_SELECT	*quick;
   Item		*on_expr;
   const char	*info;
+  byte		*null_ref_key;
   int		(*read_first_record)(struct st_join_table *tab);
   int		(*next_select)(JOIN *,struct st_join_table *,bool);
   READ_RECORD	read_record;
@@ -137,12 +140,14 @@ class JOIN :public Sql_alloc
   POSITION positions[MAX_TABLES+1],best_positions[MAX_TABLES+1];
   double   best_read;
   List<Item> *fields;
-  List<Item_buff> group_fields;
+  List<Item_buff> group_fields, group_fields_cache;
   TABLE    *tmp_table;
   // used to store 2 possible tmp table of SELECT
   TABLE    *exec_tmp_table1, *exec_tmp_table2;
   THD	   *thd;
   Item_sum  **sum_funcs, ***sum_funcs_end;
+  /* second copy of sumfuncs (for queries with 2 temporary tables */
+  Item_sum  **sum_funcs2, ***sum_funcs_end2;
   Procedure *procedure;
   Item	    *having;
   Item      *tmp_having; // To store Having when processed temporary table
@@ -196,7 +201,7 @@ class JOIN :public Sql_alloc
     send_records(0), found_records(0), examined_rows(0),
     exec_tmp_table1(0), exec_tmp_table2(0),
     thd(thd_arg),
-    sum_funcs(0),
+    sum_funcs(0),sum_funcs2(0),
     procedure(0),
     having(0), tmp_having(0),
     select_options(select_options_arg),
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index 1c4954e0276..e8cb59e387e 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -27,8 +27,6 @@
 #include "ha_berkeley.h"			// For berkeley_show_logs
 #endif
 
-/* extern "C" pthread_mutex_t THR_LOCK_keycache; */
-
 static const char *grant_names[]={
   "select","insert","update","delete","create","drop","reload","shutdown",
   "process","file","grant","references","index","alter"};
@@ -43,15 +41,11 @@ static int mysql_find_files(THD *thd,List<char> *files, const char *db,
 static int
 store_create_info(THD *thd, TABLE *table, String *packet);
 
-static void
-append_identifier(THD *thd, String *packet, const char *name);
-
-extern struct st_VioSSLAcceptorFd * ssl_acceptor_fd;
 
-/****************************************************************************
-** Send list of databases
-** A database is a directory in the mysql_data_home directory
-****************************************************************************/
+/*
+  Report list of databases
+  A database is a directory in the mysql_data_home directory
+*/
 
 int
 mysqld_show_dbs(THD *thd,const char *wild)
@@ -1002,8 +996,8 @@ mysqld_dump_create_info(THD *thd, TABLE *table, int fd)
 }
 
 
-static void
-append_identifier(THD *thd, String *packet, const char *name)
+void
+append_identifier(THD *thd, String *packet, const char *name, uint length)
 {
   char qtype;
   if (thd->variables.sql_mode & MODE_ANSI_QUOTES)
@@ -1014,12 +1008,12 @@ append_identifier(THD *thd, String *packet, const char *name)
   if (thd->options & OPTION_QUOTE_SHOW_CREATE)
   {
     packet->append(&qtype, 1);
-    packet->append(name, 0, system_charset_info);
+    packet->append(name, length, system_charset_info);
     packet->append(&qtype, 1);
   }
   else
   {
-    packet->append(name, 0, system_charset_info);
+    packet->append(name, length, system_charset_info);
   }
 }
 
@@ -1050,7 +1044,7 @@ store_create_info(THD *thd, TABLE *table, String *packet)
     packet->append("CREATE TEMPORARY TABLE ", 23);
   else
     packet->append("CREATE TABLE ", 13);
-  append_identifier(thd,packet,table->real_name);
+  append_identifier(thd,packet, table->real_name, strlen(table->real_name));
   packet->append(" (\n", 3);
 
   Field **ptr,*field;
@@ -1061,7 +1055,7 @@ store_create_info(THD *thd, TABLE *table, String *packet)
 
     uint flags = field->flags;
     packet->append("  ", 2);
-    append_identifier(thd,packet,field->field_name);
+    append_identifier(thd,packet,field->field_name, strlen(field->field_name));
     packet->append(' ');
     // check for surprises from the previous call to Field::sql_type()
     if (type.ptr() != tmp)
@@ -1152,7 +1146,7 @@ store_create_info(THD *thd, TABLE *table, String *packet)
     packet->append("KEY ", 4);
 
     if (!found_primary)
-     append_identifier(thd, packet, key_info->name);
+     append_identifier(thd, packet, key_info->name, strlen(key_info->name));
 
     if (!(thd->variables.sql_mode & MODE_NO_KEY_OPTIONS) &&
 	!limited_mysql_mode && !foreign_db_mode)
@@ -1174,7 +1168,8 @@ store_create_info(THD *thd, TABLE *table, String *packet)
         packet->append(',');
 
       if (key_part->field)
-        append_identifier(thd,packet,key_part->field->field_name);
+        append_identifier(thd,packet,key_part->field->field_name,
+			  strlen(key_part->field->field_name));
       if (!key_part->field ||
           (key_part->length !=
            table->field[key_part->fieldnr-1]->key_length() &&
@@ -1190,17 +1185,17 @@ store_create_info(THD *thd, TABLE *table, String *packet)
     packet->append(')');
   }
 
+  /*
+    Get possible foreign key definitions stored in InnoDB and append them
+    to the CREATE TABLE statement
+  */
   handler *file = table->file;
+  char* for_str= file->get_foreign_key_create_info();
 
-  /* Get possible foreign key definitions stored in InnoDB and append them
-  to the CREATE TABLE statement */
-
-  char* for_str = file->get_foreign_key_create_info();
-
-  if (for_str) {
-  	packet->append(for_str, strlen(for_str));
-
-  	file->free_foreign_key_create_info(for_str);
+  if (for_str)
+  {
+    packet->append(for_str, strlen(for_str));
+    file->free_foreign_key_create_info(for_str);
   }
 
   packet->append("\n)", 2);
@@ -1267,7 +1262,8 @@ store_create_info(THD *thd, TABLE *table, String *packet)
     {
       char buff[100];
       sprintf(buff," RAID_TYPE=%s RAID_CHUNKS=%d RAID_CHUNKSIZE=%ld",
-	      my_raid_type(file->raid_type), file->raid_chunks, file->raid_chunksize/RAID_BLOCK_SIZE);
+	      my_raid_type(file->raid_type), file->raid_chunks,
+	      file->raid_chunksize/RAID_BLOCK_SIZE);
       packet->append(buff);
     }
   }
@@ -1531,12 +1527,14 @@ int mysqld_show(THD *thd, const char *wild, show_var_st *variables,
   char buff[1024];
   List<Item> field_list;
   Protocol *protocol= thd->protocol;
+  LEX_STRING null_lex_str;
   DBUG_ENTER("mysqld_show");
 
   field_list.push_back(new Item_empty_string("Variable_name",30));
   field_list.push_back(new Item_empty_string("Value",256));
   if (protocol->send_fields(&field_list,1))
     DBUG_RETURN(1); /* purecov: inspected */
+  null_lex_str.str= 0;				// For sys_var->value_ptr()
 
   /* pthread_mutex_lock(&THR_LOCK_keycache); */
   pthread_mutex_lock(&LOCK_status);
@@ -1555,7 +1553,8 @@ int mysqld_show(THD *thd, const char *wild, show_var_st *variables,
       if (show_type == SHOW_SYS)
       {
 	show_type= ((sys_var*) value)->type();
-	value=     (char*) ((sys_var*) value)->value_ptr(thd, value_type);
+	value=     (char*) ((sys_var*) value)->value_ptr(thd, value_type,
+							 &null_lex_str);
       }
 
       pos= end= buff;
diff --git a/sql/sql_string.cc b/sql/sql_string.cc
index 0a3e8d0db9f..e7d7b08c93c 100644
--- a/sql/sql_string.cc
+++ b/sql/sql_string.cc
@@ -390,6 +390,23 @@ bool String::append(IO_CACHE* file, uint32 arg_length)
   return FALSE;
 }
 
+bool String::append_with_prefill(const char *s,uint32 arg_length,
+		 uint32 full_length, char fill_char)
+{
+  int t_length= arg_length > full_length ? arg_length : full_length;
+
+  if (realloc(str_length + t_length))
+    return TRUE;
+  t_length= full_length - arg_length;
+  if (t_length > 0)
+  {
+    bfill(Ptr+str_length, t_length, fill_char);
+    str_length=str_length + t_length;
+  }
+  append(s, arg_length);
+  return FALSE;
+}
+
 uint32 String::numchars()
 {
   return str_charset->cset->numchars(str_charset, Ptr, Ptr+str_length);
diff --git a/sql/sql_string.h b/sql/sql_string.h
index e88c9389589..d446d26298b 100644
--- a/sql/sql_string.h
+++ b/sql/sql_string.h
@@ -189,6 +189,8 @@ public:
   bool append(const char *s,uint32 arg_length=0);
   bool append(const char *s,uint32 arg_length, CHARSET_INFO *cs);
   bool append(IO_CACHE* file, uint32 arg_length);
+  bool append_with_prefill(const char *s, uint32 arg_length, 
+			   uint32 full_length, char fill_char);
   int strstr(const String &search,uint32 offset=0); // Returns offset to substring or -1
   int strstr_case(const String &s,uint32 offset=0);
   int strrstr(const String &search,uint32 offset=0); // Returns offset to substring or -1
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index a60de3e3e55..28208e4220f 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -2437,8 +2437,8 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
   query_cache_invalidate3(thd, table_list, 0);
 
 end_temporary:
-  sprintf(tmp_name,ER(ER_INSERT_INFO),(ulong) (copied+deleted),
-	  (ulong) deleted, thd->cuted_fields);
+  sprintf(tmp_name, ER(ER_INSERT_INFO), (ulong) (copied + deleted),
+	  (ulong) deleted, (ulong) thd->cuted_fields);
   send_ok(thd,copied+deleted,0L,tmp_name);
   thd->some_tables_deleted=0;
   DBUG_RETURN(0);
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 989d17b006b..e1c28dd0e4d 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -373,8 +373,8 @@ int mysql_update(THD *thd,
   else
   {
     char buff[80];
-    sprintf(buff,ER(ER_UPDATE_INFO), (long) found, (long) updated,
-	    (long) thd->cuted_fields);
+    sprintf(buff, ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated,
+	    (ulong) thd->cuted_fields);
     send_ok(thd,
 	    (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated,
 	    thd->insert_id_used ? thd->insert_id() : 0L,buff);
@@ -993,8 +993,8 @@ bool multi_update::send_eof()
   }
 
 
-  sprintf(buff,ER(ER_UPDATE_INFO), (long) found, (long) updated,
-	  (long) thd->cuted_fields);
+  sprintf(buff, ER(ER_UPDATE_INFO), (ulong) found, (ulong) updated,
+	  (ulong) thd->cuted_fields);
   ::send_ok(thd,
 	    (thd->client_capabilities & CLIENT_FOUND_ROWS) ? found : updated,
 	    thd->insert_id_used ? thd->insert_id() : 0L,buff);
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 6766f7cde43..3997ed1bd82 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -163,6 +163,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token  VARIANCE_SYM
 %token	STOP_SYM
 %token	SUM_SYM
+%token	ADDDATE_SYM
 %token	SUPER_SYM
 %token	TRUNCATE_SYM
 %token	UNLOCK_SYM
@@ -431,6 +432,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token	VARYING
 %token	ZEROFILL
 
+%token	ADDDATE_SYM
 %token	AGAINST
 %token	ATAN
 %token	BETWEEN_SYM
@@ -445,6 +447,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token	DATE_ADD_INTERVAL
 %token	DATE_SUB_INTERVAL
 %token	DAY_HOUR_SYM
+%token	DAY_MICROSECOND_SYM
 %token	DAY_MINUTE_SYM
 %token	DAY_SECOND_SYM
 %token	DAY_SYM
@@ -467,6 +470,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token  GEOMETRYCOLLECTION
 %token  GROUP_CONCAT_SYM
 %token	GROUP_UNIQUE_USERS
+%token	HOUR_MICROSECOND_SYM
 %token	HOUR_MINUTE_SYM
 %token	HOUR_SECOND_SYM
 %token	HOUR_SYM
@@ -481,6 +485,8 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token	LOCATE
 %token	MAKE_SET_SYM
 %token	MASTER_POS_WAIT
+%token  MICROSECOND_SYM
+%token	MINUTE_MICROSECOND_SYM
 %token	MINUTE_SECOND_SYM
 %token	MINUTE_SYM
 %token	MODE_SYM
@@ -505,7 +511,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token	RIGHT
 %token	ROUND
 %token	SECOND_SYM
+%token	SECOND_MICROSECOND_SYM
 %token	SHARE_SYM
+%token	SUBDATE_SYM
 %token	SUBSTRING
 %token	SUBSTRING_INDEX
 %token	TRIM
@@ -561,7 +569,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	IDENT TEXT_STRING REAL_NUM FLOAT_NUM NUM LONG_NUM HEX_NUM LEX_HOSTNAME
 	ULONGLONG_NUM field_ident select_alias ident ident_or_text
         UNDERSCORE_CHARSET IDENT_sys TEXT_STRING_sys TEXT_STRING_literal
-	NCHAR_STRING
+	NCHAR_STRING opt_component
 
 %type <lex_str_ptr>
 	opt_table_alias
@@ -1533,6 +1541,10 @@ opt_ident:
 	/* empty */	{ $$=(char*) 0; }	/* Defaultlength */
 	| field_ident	{ $$=$1.str; };
 
+opt_component:
+	/* empty */	 { $$.str= 0; $$.length= 0; }
+	| '.' ident	 { $$=$2; };
+	
 string_list:
 	text_string			{ Lex->interval_list.push_back($1); }
 	| string_list ',' text_string	{ Lex->interval_list.push_back($3); };
@@ -1598,6 +1610,7 @@ alter_list_item:
             LEX *lex=Lex;
             lex->length=lex->dec=0; lex->type=0; lex->interval=0;
             lex->default_value=lex->comment=0;
+	    lex->charset= NULL;
             lex->simple_alter=0;
           }
           type opt_attribute
@@ -2125,9 +2138,9 @@ all_or_any: ALL     { $$ = 1; }
 /* expressions that begin with 'expr' */
 expr_expr:
 	 expr IN_SYM '(' expr_list ')'
-	  { $$= new Item_func_in($1,*$4); }
+	  { $4->push_front($1); $$= new Item_func_in(*$4); }
 	| expr NOT IN_SYM '(' expr_list ')'
-	  { $$= new Item_func_not(new Item_func_in($1,*$5)); }
+	  { $5->push_front($1); $$= new Item_func_not(new Item_func_in(*$5)); }
         | expr IN_SYM in_subselect
           { $$= new Item_in_subselect(YYTHD, $1, $3); }
 	| expr NOT IN_SYM in_subselect
@@ -2227,9 +2240,9 @@ no_in_expr:
 /* expressions that begin with 'expr' that does NOT follow AND */
 no_and_expr:
 	  no_and_expr IN_SYM '(' expr_list ')'
-	  { $$= new Item_func_in($1,*$4); }
+	  { $4->push_front($1); $$= new Item_func_in(*$4); }
 	| no_and_expr NOT IN_SYM '(' expr_list ')'
-	  { $$= new Item_func_not(new Item_func_in($1,*$5)); }
+	  { $5->push_front($1); $$= new Item_func_not(new Item_func_in(*$5)); }
         | no_and_expr IN_SYM in_subselect
           { $$= new Item_in_subselect(YYTHD, $1, $3); }
 	| no_and_expr NOT IN_SYM in_subselect
@@ -2304,9 +2317,9 @@ simple_expr:
 	    $$= new Item_func_get_user_var($2);
 	    Lex->uncacheable();
 	  }
-	| '@' '@' opt_var_ident_type ident_or_text
+	| '@' '@' opt_var_ident_type ident_or_text opt_component
 	  {
-	    if (!($$= get_system_var((enum_var_type) $3, $4)))
+	    if (!($$= get_system_var(YYTHD, (enum_var_type) $3, $4, $5)))
 	      YYABORT;
 	  }
 	| sum_expr
@@ -2362,6 +2375,10 @@ simple_expr:
 	  { $$= ((Item*(*)(Item*,Item*))($1.symbol->create_func))($3,$5);}
 	| FUNC_ARG3 '(' expr ',' expr ',' expr ')'
 	  { $$= ((Item*(*)(Item*,Item*,Item*))($1.symbol->create_func))($3,$5,$7);}
+	| ADDDATE_SYM '(' expr ',' expr ')'
+	  { $$= new Item_date_add_interval($3, $5, INTERVAL_DAY, 0);}
+	| ADDDATE_SYM '(' expr ',' INTERVAL_SYM expr interval ')'
+	  { $$= new Item_date_add_interval($3, $6, $7, 0); }
 	| ATAN	'(' expr ')'
 	  { $$= new Item_func_atan($3); }
 	| ATAN	'(' expr ',' expr ')'
@@ -2396,8 +2413,12 @@ simple_expr:
 	    $$= new Item_func_database();
             Lex->safe_to_cache_query=0;
 	  }
+	| DATE_SYM '(' expr ')'
+	  { $$= new Item_date_typecast($3); }
+	| DAY_SYM '(' expr ')'
+	  { $$= new Item_func_dayofmonth($3); }
 	| ELT_FUNC '(' expr ',' expr_list ')'
-	  { $$= new Item_func_elt($3, *$5); }
+	  { $5->push_front($3); $$= new Item_func_elt(*$5); }
 	| MAKE_SET_SYM '(' expr ',' expr_list ')'
 	  { $$= new Item_func_make_set($3, *$5); }
 	| ENCRYPT '(' expr ')'
@@ -2435,7 +2456,7 @@ simple_expr:
 	    $$= new Item_func_date_format (new Item_func_from_unixtime($3),$5,0);
 	  }
 	| FIELD_FUNC '(' expr ',' expr_list ')'
-	  { $$= new Item_func_field($3, *$5); }
+	  { $5->push_front($3); $$= new Item_func_field(*$5); }
 	| GEOMFROMTEXT '(' expr ')'
 	  { $$= new Item_func_geometry_from_text($3); }
 	| GEOMFROMTEXT '(' expr ',' expr ')'
@@ -2468,7 +2489,7 @@ simple_expr:
           }
 	| LAST_INSERT_ID '(' ')'
 	  {
-	    $$= get_system_var(OPT_SESSION, "last_insert_id", 14,
+	    $$= get_system_var(YYTHD, OPT_SESSION, "last_insert_id", 14,
 			      "last_insert_id()");
 	    Lex->safe_to_cache_query= 0;
 	  }
@@ -2512,6 +2533,8 @@ simple_expr:
 	    $$= new Item_master_pos_wait($3, $5, $7);
 	    Lex->safe_to_cache_query=0; 
 	  }
+	| MICROSECOND_SYM '(' expr ')'
+	  { $$= new Item_func_microsecond($3); }
 	| MINUTE_SYM '(' expr ')'
 	  { $$= new Item_func_minute($3); }
 	| MOD_SYM '(' expr ',' expr ')'
@@ -2573,6 +2596,10 @@ simple_expr:
 	| ROUND '(' expr ')'
 	  { $$= new Item_func_round($3, new Item_int((char*)"0",0,1),0); }
 	| ROUND '(' expr ',' expr ')' { $$= new Item_func_round($3,$5,0); }
+	| SUBDATE_SYM '(' expr ',' expr ')'
+	  { $$= new Item_date_add_interval($3, $5, INTERVAL_DAY, 1);}
+	| SUBDATE_SYM '(' expr ',' INTERVAL_SYM expr interval ')'
+	  { $$= new Item_date_add_interval($3, $6, $7, 1); }
 	| SECOND_SYM '(' expr ')'
 	  { $$= new Item_func_second($3); }
 	| SUBSTRING '(' expr ',' expr ',' expr ')'
@@ -2585,6 +2612,12 @@ simple_expr:
 	  { $$= new Item_func_substr($3,$5); }
 	| SUBSTRING_INDEX '(' expr ',' expr ',' expr ')'
 	  { $$= new Item_func_substr_index($3,$5,$7); }
+	| TIME_SYM '(' expr ')'
+	  { $$= new Item_time_typecast($3); }
+	| TIMESTAMP '(' expr ')'
+	  { $$= new Item_datetime_typecast($3); }
+	| TIMESTAMP '(' expr ',' expr ')'
+	  { $$= new Item_func_add_time($3, $5, 1, 0); }
 	| TRIM '(' expr ')'
 	  { $$= new Item_func_trim($3); }
 	| TRIM '(' LEADING expr FROM expr ')'
@@ -3001,15 +3034,20 @@ using_list:
 
 interval:
 	 DAY_HOUR_SYM		{ $$=INTERVAL_DAY_HOUR; }
+	| DAY_MICROSECOND_SYM	{ $$=INTERVAL_DAY_MICROSECOND; }
 	| DAY_MINUTE_SYM	{ $$=INTERVAL_DAY_MINUTE; }
 	| DAY_SECOND_SYM	{ $$=INTERVAL_DAY_SECOND; }
 	| DAY_SYM		{ $$=INTERVAL_DAY; }
+	| HOUR_MICROSECOND_SYM	{ $$=INTERVAL_HOUR_MICROSECOND; }
 	| HOUR_MINUTE_SYM	{ $$=INTERVAL_HOUR_MINUTE; }
 	| HOUR_SECOND_SYM	{ $$=INTERVAL_HOUR_SECOND; }
 	| HOUR_SYM		{ $$=INTERVAL_HOUR; }
+	| MICROSECOND_SYM	{ $$=INTERVAL_MICROSECOND; }
+	| MINUTE_MICROSECOND_SYM	{ $$=INTERVAL_MINUTE_MICROSECOND; }
 	| MINUTE_SECOND_SYM	{ $$=INTERVAL_MINUTE_SECOND; }
 	| MINUTE_SYM		{ $$=INTERVAL_MINUTE; }
 	| MONTH_SYM		{ $$=INTERVAL_MONTH; }
+	| SECOND_MICROSECOND_SYM	{ $$=INTERVAL_SECOND_MICROSECOND; }
 	| SECOND_SYM		{ $$=INTERVAL_SECOND; }
 	| YEAR_MONTH_SYM	{ $$=INTERVAL_YEAR_MONTH; }
 	| YEAR_SYM		{ $$=INTERVAL_YEAR; };
@@ -3733,7 +3771,7 @@ show_param:
 	    Lex->mi.pos = $12;
 	    Lex->mi.server_id = $16;
           }
-        | BINARY LOGS_SYM
+        | master_or_binary LOGS_SYM
           {
 	    Lex->sql_command = SQLCOM_SHOW_BINLOGS;
           }
@@ -3793,6 +3831,8 @@ show_param:
 	  { Lex->sql_command= SQLCOM_SHOW_CHARSETS; }
 	| COLLATION_SYM wild
 	  { Lex->sql_command= SQLCOM_SHOW_COLLATIONS; }
+	| BERKELEY_DB_SYM LOGS_SYM
+	  { Lex->sql_command= SQLCOM_SHOW_LOGS; }
 	| LOGS_SYM
 	  { Lex->sql_command= SQLCOM_SHOW_LOGS; }
 	| GRANTS FOR_SYM user
@@ -3823,6 +3863,10 @@ show_param:
 	    Lex->sql_command = SQLCOM_SHOW_SLAVE_STAT;
           };
 
+master_or_binary:
+	MASTER_SYM
+	| BINARY;
+
 opt_db:
 	/* empty */  { $$= 0; }
 	| from_or_in ident { $$= $2.str; };
@@ -3940,8 +3984,7 @@ purge:
 	;
 
 purge_options:
-	LOGS_SYM purge_option
-	| MASTER_SYM LOGS_SYM purge_option
+	master_or_binary LOGS_SYM purge_option
 	;
 
 purge_option:
@@ -4328,6 +4371,7 @@ user:
 
 keyword:
 	ACTION			{}
+	| ADDDATE_SYM		{}
 	| AFTER_SYM		{}
 	| AGAINST		{}
 	| AGGREGATE_SYM		{}
@@ -4423,6 +4467,7 @@ keyword:
 	| MEDIUM_SYM		{}
 	| MERGE_SYM		{}
 	| MEMORY_SYM		{}
+	| MICROSECOND_SYM	{}
 	| MINUTE_SYM		{}
 	| MIN_ROWS		{}
 	| MODIFY_SYM		{}
@@ -4488,6 +4533,7 @@ keyword:
 	| STATUS_SYM		{}
 	| STOP_SYM		{}
 	| STRING_SYM		{}
+	| SUBDATE_SYM		{}
 	| SUBJECT_SYM		{}
 	| SUPER_SYM		{}
 	| TEMPORARY		{}
@@ -4618,6 +4664,27 @@ internal_variable_name:
 	    YYABORT;
 	  $$=tmp;
 	}
+	| ident '.' ident
+	  {
+	    sys_var *tmp=find_sys_var($3.str, $3.length);
+	    if (!tmp)
+	      YYABORT;
+	    if (!tmp->is_struct())
+	      net_printf(YYTHD, ER_VARIABLE_IS_NOT_STRUCT, $3.str);
+	    tmp->base_name= $1;
+	    $$=tmp;
+	  }
+	| DEFAULT '.' ident
+	  {
+	    sys_var *tmp=find_sys_var($3.str, $3.length);
+	    if (!tmp)
+	      YYABORT;
+	    if (!tmp->is_struct())
+	      net_printf(YYTHD, ER_VARIABLE_IS_NOT_STRUCT, $3.str);
+	    tmp->base_name.str= (char*) "default";
+	    tmp->base_name.length= 7;
+	    $$=tmp;
+	  }
         ;
 
 isolation_types:
diff --git a/sql/table.cc b/sql/table.cc
index 908d6807450..0fc2a09f749 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -395,7 +395,9 @@ int openfrm(const char *name, const char *alias, uint db_stat, uint prgflag,
       }
       else
       {
-        if (!(charset=get_charset((uint) strpos[14], MYF(0))))
+	if (!strpos[14])
+	  charset= &my_charset_bin;
+	else if (!(charset=get_charset((uint) strpos[14], MYF(0))))
 	  charset= (outparam->table_charset ? outparam->table_charset: 
 		  default_charset_info);
       }
diff --git a/sql/time.cc b/sql/time.cc
index b6ca306e523..70ae8dcd8ed 100644
--- a/sql/time.cc
+++ b/sql/time.cc
@@ -432,6 +432,7 @@ str_to_TIME(const char *str, uint length, TIME *l_time,bool fuzzy_date)
   l_time->minute=date[4];
   l_time->second=date[5];
   l_time->second_part=date[6];
+  l_time->neg= 0;
   DBUG_RETURN(l_time->time_type=
 	      (number_of_fields <= 3 ? TIMESTAMP_DATE : TIMESTAMP_FULL));
 }
@@ -581,7 +582,7 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
   /* Get fractional second part */
   if ((end-str) >= 2 && *str == '.' && my_isdigit(&my_charset_latin1,str[1]))
   {
-    uint field_length=3;
+    uint field_length=5;
     str++; value=(uint) (uchar) (*str - '0');
     while (++str != end && 
            my_isdigit(&my_charset_latin1,str[0]) && 
@@ -604,6 +605,7 @@ bool str_to_time(const char *str,uint length,TIME *l_time)
   l_time->minute=date[2];
   l_time->second=date[3];
   l_time->second_part=date[4];
+  l_time->time_type= TIMESTAMP_TIME;
 
   /* Check if there is garbage at end of the TIME specification */
   if (str != end && current_thd->count_cuted_fields)
@@ -636,3 +638,13 @@ void localtime_to_TIME(TIME *to, struct tm *from)
   to->minute=	(int) from->tm_min;
   to->second=   (int) from->tm_sec;
 }
+
+void calc_time_from_sec(TIME *to, long seconds, long microseconds)
+{
+  long t_seconds;
+  to->hour= seconds/3600L;
+  t_seconds= seconds%3600L;
+  to->minute= t_seconds/60L;
+  to->second= t_seconds%60L;
+  to->second_part= microseconds;
+}
diff --git a/sql/unireg.cc b/sql/unireg.cc
index 5e723281d3f..3e634f54b4f 100644
--- a/sql/unireg.cc
+++ b/sql/unireg.cc
@@ -460,8 +460,10 @@ static bool pack_fields(File file,List<create_field> &create_fields)
     buff[13]= (uchar) field->sql_type; 
     if (field->sql_type == FIELD_TYPE_GEOMETRY)
       buff[14]= (uchar) field->geom_type;
-    else
+    else if (field->charset) 
       buff[14]= (uchar) field->charset->number;
+    else
+      buff[14]= 0;				// Numerical
     int2store(buff+15, field->comment.length);
     comment_length+= field->comment.length;
     set_if_bigger(int_count,field->interval_id);