Fix of LEFT JOIN optimizer bug, problem with key cache on Windows and

a lot of portability issues.


Docs/manual.texi:
  Changed FOREIGN KEY to FOREIGN KEY constraint
client/mysqladmin.c:
  Don't flush MASTER or SLAVE on refresh
configure.in:
  Fix for hpux 11.0
extra/perror.c:
  New error message
include/my_pthread.h:
  Portability fix for windows
myisam/mi_locking.c:
  Ensure that locking doesn't interfere with pread/pwrite on windows
myisam/sort.c:
  checked with purecover
mysys/mf_tempfile.c:
  Fix for windows
mysys/my_lock.c:
  Ensure that locking doesn't interfere with pread/pwrite on windows
mysys/my_winthread.c:
  Portability fix
sql-bench/Results/ATIS-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/RUN-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/alter-table-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/big-tables-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/connect-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/create-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/insert-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/select-mysql-NT_4.0:
  New benchmark runs
sql-bench/Results/wisconsin-mysql-NT_4.0:
  New benchmark runs
sql-bench/crash-me.sh:
  Fixed things for PostgreSQL
sql-bench/limits/mysql-3.23.cfg:
  Update for new crash-me
sql-bench/limits/mysql.cfg:
  Update for new crash-me
sql-bench/print-limit-table:
  Fixed position for alter table rename
sql-bench/test-insert.sh:
  Fix for PostgreSQL
sql/field.cc:
  Fix for default values in CREATE ... SELECT
sql/field.h:
  Fix for default values in CREATE ... SELECT
sql/log.cc:
  Fixed typo
sql/log_event.cc:
  Portability fix
sql/mysqlbinlog.cc:
  Portability fix
sql/mysqld.cc:
  Don't turn of concurrent insert with --skip-new or --safe
sql/sql_base.cc:
  Portability fix
sql/sql_class.cc:
  Portability fix
sql/sql_class.h:
  Portability fix
sql/sql_parse.cc:
  Fix for --log-slow-queries
sql/sql_repl.cc:
  Portability fixes
sql/sql_select.cc:
  Fixed optimizer bug for LEFT JOIN
sql/sql_select.h:
  Fixed optimizer bug for LEFT JOIN
sql/sql_table.cc:
  Fix for default values in CREATE ... SELECT
sql/sql_yacc.yy:
  Added optional AS to: CREATE TABLE foo [ AS ] SELECT ...

15 files changed, 145 insertions, 83 deletions

diff --git a/sql/field.cc b/sql/field.cc
index 4ef95d6e102..67bc84c0e3b 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -4550,7 +4550,7 @@ Field *make_field(char *ptr, uint32 field_length,
 
 /* Create a field suitable for create of table */
 
-create_field::create_field(Field *old_field,bool ignore_default)
+create_field::create_field(Field *old_field,Field *orig_field)
 {
   field=      old_field;
   field_name=change=old_field->field_name;
@@ -4574,15 +4574,24 @@ create_field::create_field(Field *old_field,bool ignore_default)
     interval= ((Field_enum*) old_field)->typelib;
   else
     interval=0;
-  if (!ignore_default && !old_field->is_real_null() && ! (flags & BLOB_FLAG) &&
-      old_field->type() != FIELD_TYPE_TIMESTAMP && old_field->ptr)
+  if (!old_field->is_real_null() && ! (flags & BLOB_FLAG) &&
+      old_field->type() != FIELD_TYPE_TIMESTAMP && old_field->ptr &&
+      orig_field)
   {
     char buff[MAX_FIELD_WIDTH],*pos;
-    String tmp(buff,sizeof(buff));
-    field->val_str(&tmp,&tmp);
-    pos= (char*) sql_memdup(tmp.ptr(),tmp.length()+1);
-    pos[tmp.length()]=0;
-    def=new Item_string(pos,tmp.length());
+    String tmp(buff,sizeof(buff)),*res;
+
+    /* Get the value from record[2] (the default value row) */
+    my_ptrdiff_t diff= (my_ptrdiff_t) (orig_field->table->rec_buff_length*2);
+    orig_field->move_field(diff);		// Points now at record[2]
+    res=orig_field->val_str(&tmp,&tmp);
+    orig_field->move_field(-diff);		// Back to record[0]
+    if (res)					// If not NULL value
+    {
+      pos= (char*) sql_memdup(tmp.ptr(),tmp.length()+1);
+      pos[tmp.length()]=0;
+      def=new Item_string(pos,tmp.length());
+    }
   }
   else
     def=0;
diff --git a/sql/field.h b/sql/field.h
index 075e0feea96..a50c6efa5d6 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -986,7 +986,7 @@ public:
   uint8 row,col,sc_length,interval_id;		// For rea_create_table
   uint	offset,pack_flag;
   create_field() :after(0) {}
-  create_field(Field *field,bool ignore_default=0);
+  create_field(Field *field, Field *orig_field);
 };
 
 
diff --git a/sql/log.cc b/sql/log.cc
index c5e5c8ff7a1..d245f49bf7d 100644
--- a/sql/log.cc
+++ b/sql/log.cc
@@ -578,7 +578,7 @@ void MYSQL_LOG::write(THD *thd,const char *query, uint query_length,
 	/* For slow query log */
 	if (!(specialflag & SPECIAL_LONG_LOG_FORMAT))
 	  current_time=time(NULL);
-	fprintf(file,"# Time: %lu  Lock_time: %lu  Rows_sent %lu\n",
+	fprintf(file,"# Time: %lu  Lock_time: %lu  Rows_sent: %lu\n",
 		(ulong) (current_time - query_start),
 		(ulong) (thd->time_after_lock - query_start),
 		(ulong) thd->sent_row_count);
diff --git a/sql/log_event.cc b/sql/log_event.cc
index 5d1aad66954..3e717ce4d6c 100644
--- a/sql/log_event.cc
+++ b/sql/log_event.cc
@@ -264,22 +264,22 @@ void Log_event::print_header(FILE* file)
   fprintf(file, " server id  %ld ", server_id); 
 }
 
-void Log_event::print_timestamp(FILE* file, time_t* ts = 0)
+void Log_event::print_timestamp(FILE* file, time_t* ts)
 {
-    struct tm tm_tmp;
-    if(!ts)
-      {
-        ts = &when;
-      }
-    localtime_r(ts,&tm_tmp);
-
-    fprintf(file,"%02d%02d%02d %2d:%02d:%02d",
-	    tm_tmp.tm_year % 100,
-	    tm_tmp.tm_mon+1,
-	    tm_tmp.tm_mday,
-	    tm_tmp.tm_hour,
-	    tm_tmp.tm_min,
-	    tm_tmp.tm_sec);
+  struct tm tm_tmp;
+  if (!ts)
+  {
+    ts = &when;
+  }
+  localtime_r(ts,&tm_tmp);
+
+  fprintf(file,"%02d%02d%02d %2d:%02d:%02d",
+	  tm_tmp.tm_year % 100,
+	  tm_tmp.tm_mon+1,
+	  tm_tmp.tm_mday,
+	  tm_tmp.tm_hour,
+	  tm_tmp.tm_min,
+	  tm_tmp.tm_sec);
 }
 
 
diff --git a/sql/mysqlbinlog.cc b/sql/mysqlbinlog.cc
index 505604ea7d4..5c94d5d5a57 100644
--- a/sql/mysqlbinlog.cc
+++ b/sql/mysqlbinlog.cc
@@ -327,7 +327,7 @@ static void dump_local_log_entries(const char* logname)
  if(!position)
    {
      char magic[4];
-     if(my_fread(file, magic, sizeof(magic), MYF(MY_NABP|MY_WME)))
+     if(my_fread(file, (byte*) magic, sizeof(magic), MYF(MY_NABP|MY_WME)))
 	die("I/O error reading binlog magic number");
      if(memcmp(magic, BINLOG_MAGIC, 4))
        die("Bad magic number");
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index b794bff4075..aaeb056e30b 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -2915,12 +2915,11 @@ static void get_options(int argc,char **argv)
       default_table_type=DB_TYPE_ISAM;
       myisam_delay_key_write=0;
       myisam_concurrent_insert=0;
-      myisam_recover_options= 0;
+      myisam_recover_options= HA_RECOVER_NONE;
       break;
     case (int) OPT_SAFE:
       opt_specialflag|= SPECIAL_SAFE_MODE;
       myisam_delay_key_write=0;
-      myisam_concurrent_insert=0;
       myisam_recover_options= HA_RECOVER_NONE;	// To be changed
       break;
     case (int) OPT_SKIP_CONCURRENT_INSERT:
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 03b799aee7d..ab42d393925 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -33,8 +33,8 @@ TABLE *unused_tables;				/* Used by mysql_test */
 HASH open_cache;				/* Used by mysql_test */
 
 
-static int open_unireg_entry(TABLE *entry,const char *db,const char *name,
-			     const char *alias, bool locked);
+static int open_unireg_entry(THD *thd,TABLE *entry,const char *db,
+			     const char *name, const char *alias, bool locked);
 static bool insert_fields(THD *thd,TABLE_LIST *tables, const char *table_name,
 			  List_iterator<Item> *it);
 static void free_cache_entry(TABLE *entry);
@@ -572,7 +572,7 @@ TABLE *reopen_name_locked_table(THD* thd, TABLE_LIST* table_list)
   key_length=(uint) (strmov(strmov(key,db)+1,table_name)-key)+1;
 
   pthread_mutex_lock(&LOCK_open);
-  if (open_unireg_entry(table, db, table_name, table_name,0) ||
+  if (open_unireg_entry(thd, table, db, table_name, table_name,0) ||
       !(table->table_cache_key =memdup_root(&table->mem_root,(char*) key,
 					    key_length)))
     {
@@ -706,7 +706,7 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
     /* make a new table */
     if (!(table=(TABLE*) my_malloc(sizeof(*table),MYF(MY_WME))))
       DBUG_RETURN(NULL);
-    if (open_unireg_entry(table,db,table_name,alias,0) ||
+    if (open_unireg_entry(thd, table,db,table_name,alias,0) ||
 	!(table->table_cache_key=memdup_root(&table->mem_root,(char*) key,
 					     key_length)))
     {
@@ -816,7 +816,8 @@ bool reopen_table(TABLE *table,bool locked)
   if (!locked)
     VOID(pthread_mutex_lock(&LOCK_open));
 
-  if (open_unireg_entry(&tmp,db,table_name,table->table_name,locked))
+  if (open_unireg_entry(current_thd,&tmp,db,table_name,table->table_name,
+			locked))
     goto end;
   free_io_cache(table);
 
@@ -1110,10 +1111,11 @@ void abort_locked_tables(THD *thd,const char *db, const char *table_name)
 **	Purpose : Load a table definition from file and open unireg table
 **	Args	: entry with DB and table given
 **	Returns : 0 if ok
+**	Note that the extra argument for open is taken from thd->open_options
 */
 
-static int open_unireg_entry(TABLE *entry,const char *db,const char *name,
-			     const char *alias, bool locked)
+static int open_unireg_entry(THD *thd, TABLE *entry, const char *db,
+			     const char *name, const char *alias, bool locked)
 {
   char path[FN_REFLEN];
   int error;
@@ -1124,10 +1126,8 @@ static int open_unireg_entry(TABLE *entry,const char *db,const char *name,
 	       (uint) (HA_OPEN_KEYFILE | HA_OPEN_RNDFILE | HA_GET_INDEX |
 		       HA_TRY_READ_ONLY),
 	       READ_KEYINFO | COMPUTE_TYPES | EXTRA_RECORD,
-		 ha_open_options,
-		 entry))
+	      thd->open_options, entry))
   {
-    THD *thd=current_thd;
     if (!entry->crashed)
       goto err;					// Can't repair the table
 
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 31d54a621ac..b65c5d1978f 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -96,6 +96,7 @@ THD::THD()
   start_time=(time_t) 0;
   last_nx_table = last_nx_db = 0;
   inactive_timeout=net_wait_timeout;
+  open_options=ha_open_options;
   cond_count=0;
   command=COM_CONNECT;
   set_query_id=1;
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 751fdfc1a0d..4ba3ee87144 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -287,7 +287,7 @@ public:
   long  dbug_thread_id;
   pthread_t  real_id;
   uint	current_tablenr,tmp_table,cond_count,col_access,query_length;
-  uint  server_status;
+  uint  server_status,open_options;
   char	     scramble[9];
   bool	     set_query_id,locked,count_cuted_fields,some_tables_deleted;
   bool	     no_errors, allow_sum_func, password, fatal_error;
@@ -300,6 +300,7 @@ public:
   bool store_globals();
   inline time_t query_start() { query_start_used=1; return start_time; }
   inline void	set_time()    { if (!user_time) time_after_lock=time(&start_time); }
+  inline void	end_time()    { time(&start_time); }
   inline void	set_time(time_t t) { time_after_lock=start_time=t; user_time=1; }
   inline void	lock_time()   { time(&time_after_lock); }
   inline void	insert_id(ulonglong id)
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index eecd33ccf5f..ca8a7b5fe14 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -807,13 +807,15 @@ bool do_command(THD *thd)
     send_error(net,0);				// End of memory ?
 
   time_t start_of_query=thd->start_time;
-  thd->set_time();
+  thd->end_time();				// Set start time
   /* If not reading from backup and if the query took too long */
-  if (!thd->user_time &&
-      (ulong) (thd->start_time - start_of_query) > long_query_time)
+  if (!thd->user_time)
   {
-    long_query_count++;
-    mysql_slow_log.write(thd, thd->query, thd->query_length, start_of_query);
+    if ((ulong) (thd->start_time - thd->time_after_lock) > long_query_time)
+    {
+      long_query_count++;
+      mysql_slow_log.write(thd, thd->query, thd->query_length, start_of_query);
+    }
   }
   VOID(pthread_mutex_lock(&LOCK_thread_count)); // For process list
   thd->proc_info=0;
diff --git a/sql/sql_repl.cc b/sql/sql_repl.cc
index 0b955f43f1e..53a5b00d084 100644
--- a/sql/sql_repl.cc
+++ b/sql/sql_repl.cc
@@ -131,7 +131,7 @@ void mysql_binlog_send(THD* thd, char* log_ident, ulong pos, ushort flags)
       goto err;
     }
   
-  if(my_fread(log, magic, sizeof(magic), MYF(MY_NABP|MY_WME)))
+  if(my_fread(log, (byte*) magic, sizeof(magic), MYF(MY_NABP|MY_WME)))
     {
       errmsg = "I/O error reading binlog magic number";
       goto err;
@@ -321,7 +321,7 @@ sweepstakes if you report the bug";
 	    }
 
 	  //check the magic
-	  if(my_fread(log, magic, sizeof(magic), MYF(MY_NABP|MY_WME)))
+	  if(my_fread(log, (byte*) magic, sizeof(magic), MYF(MY_NABP|MY_WME)))
 	    {
 	      errmsg = "I/O error reading binlog magic number";
 	      goto err;
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index a804870a69e..7fcecb8b24d 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -399,6 +399,11 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds,
   }
   if (join.const_tables && !thd->locked_tables)
     mysql_unlock_some_tables(thd, join.table,join.const_tables);
+  if (!conds && join.outer_join)
+  {
+    /* Handle the case where we have an OUTER JOIN without a WHERE */
+    conds=new Item_int((longlong) 1,1);	// Always true
+  }
   select=make_select(*join.table, join.const_table_map,
 		     join.const_table_map,conds,&error);
   if (error)
@@ -856,6 +861,7 @@ make_join_statistics(JOIN *join,TABLE_LIST *tables,COND *conds,
     }
   }
   stat_vector[i]=0;
+  join->outer_join=outer_join;
 
   /*
   ** If outer join: Re-arrange tables in stat_vector so that outer join
@@ -1553,12 +1559,14 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
       double best,best_time,records;
       best=best_time=records=DBL_MAX;
       KEYUSE *best_key=0;
+      uint best_max_key_part=0;
 
       if (s->keyuse)
       {						/* Use key if possible */
 	TABLE *table=s->table;
 	KEYUSE *keyuse,*start_key=0;
 	double best_records=DBL_MAX;
+	uint max_key_part=0;
 
 	/* Test how we can use keys */
 	rec= s->records/MATCHING_ROWS_IN_OTHER_TABLE;  /* Assumed records/key */
@@ -1576,34 +1584,34 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
             uint keypart=keyuse->keypart;
 	    do
 	    {
-              if(!ft_key)
+              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 this 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)))
-	      {
-		uint tablenr;
-		for (tablenr=0 ; ! (map & 1) ; map>>=1, tablenr++) ;
-		if (map == 1)			// Only one table
+		table_map map;
+		if (!(rest_tables & keyuse->used_tables))
 		{
-		  TABLE *tmp_table=join->all_tables[tablenr];
-		  if (rec > tmp_table->file->records && rec > 100)
-		    rec=max(tmp_table->file->records,100);
+		  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)))
+		{
+		  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);
+		  }
 		}
-	      }
               }
 	      keyuse++;
 	    } while (keyuse->table == table && keyuse->key == key &&
@@ -1637,6 +1645,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 	  */
 	  if (found_part == PREV_BITS(uint,keyinfo->key_parts))
 	  {				/* use eq key */
+	    max_key_part= (uint) ~0;
 	    if ((keyinfo->flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)
 	    {
 	      tmp=prev_record_reads(join,found_ref);
@@ -1686,7 +1695,7 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 	    if ((found_part & 1) &&
 		!(table->file->option_flag() & HA_ONLY_WHOLE_INDEX))
 	    {
-	      uint max_key_part=max_part_bit(found_part);
+	      max_key_part=max_part_bit(found_part);
 	      /* Check if quick_range could determinate how many rows we
 		 will match */
 
@@ -1754,11 +1763,19 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 	    best=tmp;
 	    best_records=records;
 	    best_key=start_key;
+	    best_max_key_part=max_key_part;
 	  }
 	}
 	records=best_records;
       }
-      if (records >= s->found_records || best > s->read_time)
+
+      /*
+	Don't test table scan if it can't be better.
+	Prefer key lookup if we would use the same key for scanning.
+      */
+      if ((records >= s->found_records || best > s->read_time) &&
+	  !(s->quick && best_key && s->quick->index == best_key->key &&
+	    best_max_key_part >= s->table->quick_key_parts[best_key->key]))
       {						// Check full join
 	if (s->on_expr)
 	{
@@ -1775,6 +1792,10 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
 	    (tmp  + record_count/(double) TIME_FOR_COMPARE*s->found_records <
 	     best + record_count/(double) TIME_FOR_COMPARE*records))
 	{
+	  /*
+	    If the table has a range (s->quick is set) make_join_select()
+	    will ensure that this will be used
+	  */
 	  best=tmp;
 	  records=s->found_records;
 	  best_key=0;
@@ -2193,6 +2214,14 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
       table_map current_map= tab->table->map;
       used_tables|=current_map;
       COND *tmp=make_cond_for_table(cond,used_tables,current_map);
+      if (!tmp && tab->quick)
+      {						// Outer join
+	/*
+	  Hack to handle the case where we only refer to a table
+	  in the ON part of an OUTER JOIN.
+	*/
+	tmp=new Item_int((longlong) 1,1);	// Always true
+      }
       if (tmp)
       {
 	DBUG_EXECUTE("where",print_where(tmp,tab->table->table_name););
@@ -2204,6 +2233,8 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
 	sel->head=tab->table;
 	if (tab->quick)
 	{
+	  /* Use quick key read if it's a constant and it's not used
+	     with key reading */
 	  if (tab->needed_reg == 0 && tab->type != JT_EQ_REF &&
 	      (tab->type != JT_REF ||
 	       (uint) tab->ref.key == tab->quick->index))
@@ -2231,19 +2262,23 @@ make_join_select(JOIN *join,SQL_SELECT *select,COND *cond)
 	    DBUG_RETURN(1);				// Impossible range
 	  /*
 	    We plan to scan all rows.
-	    Check again if we should use an index instead if
-	    we could have used an column from a previous table in
-	    the index or if we are using limit and this is the first table
+	    Check again if we should use an index.
+	    We could have used an column from a previous table in
+	    the index if we are using limit and this is the first table
 	  */
 
 	  if ((tab->keys & ~ tab->const_keys && i > 0) ||
 	      tab->const_keys && i == join->const_tables &&
 	      join->thd->select_limit < join->best_positions[i].records_read)
 	  {
+	    /* Join with outer join condition */
+	    COND *orig_cond=sel->cond;
+	    sel->cond=and_conds(sel->cond,tab->on_expr);
 	    if (sel->test_quick_select(tab->keys,
 				       used_tables & ~ current_map,
 				       join->thd->select_limit) < 0)
-	    DBUG_RETURN(1);				// Impossible range
+	      DBUG_RETURN(1);				// Impossible range
+	    sel->cond=orig_cond;
 	  }
 	  else
 	  {
diff --git a/sql/sql_select.h b/sql/sql_select.h
index 4bee0bf2c3d..8ac00dde4c5 100644
--- a/sql/sql_select.h
+++ b/sql/sql_select.h
@@ -147,7 +147,7 @@ class JOIN {
   uint	   tables,const_tables;
   uint	   send_group_parts;
   bool	   sort_and_group,first_record,full_join,group, no_field_update;
-  table_map const_table_map;
+  table_map const_table_map,outer_join;
   ha_rows  send_records,found_records;
   POSITION positions[MAX_TABLES+1],best_positions[MAX_TABLES+1];
   double   best_read;
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 2343e06b12a..23c0f1d05ec 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -620,7 +620,10 @@ TABLE *create_table_from_items(THD *thd, HA_CREATE_INFO *create_info,
 
     Field *field=create_tmp_field(&tmp_table,item,item->type(),
 				  (Item_result_field***) 0, &tmp_field,0,0);
-    if (!field || !(cr_field=new create_field(field,1)))
+    if (!field ||
+	!(cr_field=new create_field(field,(item->type() == Item::FIELD_ITEM ?
+					   ((Item_field *)item)->field : NULL)
+				    )))
       DBUG_RETURN(0);
     extra_fields->push_back(cr_field);
   }
@@ -833,9 +836,13 @@ static int mysql_admin_table(THD* thd, TABLE_LIST* tables,
   {
     char table_name[NAME_LEN*2+2];
     char* db = (table->db) ? table->db : thd->db;
+    bool fatal_error=0;
     strxmov(table_name,db ? db : "",".",table->name,NullS);
 
+    if (operator_func == &handler::repair || operator_func == &handler::check)
+      thd->open_options|= HA_OPEN_FOR_REPAIR;
     table->table = open_ltable(thd, table, lock_type);
+    thd->open_options&= ~HA_OPEN_FOR_REPAIR;
     packet->length(0);
     if (operator_func == &handler::restore)
     {
@@ -909,6 +916,7 @@ static int mysql_admin_table(THD* thd, TABLE_LIST* tables,
     case HA_ADMIN_CORRUPT:
       net_store_data(packet, "error");
       net_store_data(packet, "Corrupt");
+      fatal_error=1;
       break;
 
     case HA_ADMIN_INVALID:
@@ -919,8 +927,11 @@ static int mysql_admin_table(THD* thd, TABLE_LIST* tables,
     default:				// Probably HA_ADMIN_INTERNAL_ERROR
       net_store_data(packet, "error");
       net_store_data(packet, "Unknown - internal error during operation");
+      fatal_error=1;
       break;
     }
+    if (fatal_error)
+      table->table->flush_version=0;	// Force close of table
     close_thread_tables(thd);
     if (my_net_write(&thd->net, (char*) packet->ptr(),
 		     packet->length()))
@@ -1150,7 +1161,7 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
     }
     else
     {						// Use old field value
-      create_list.push_back(def=new create_field(field));
+      create_list.push_back(def=new create_field(field,field));
       if (def->sql_type == FIELD_TYPE_TIMESTAMP)
 	use_timestamp=1;
 
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index ae1296153e5..6e4d8a3d279 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -509,7 +509,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	flush_options flush_option insert_lock_option replace_lock_option
 	equal optional_braces opt_key_definition key_usage_list2
 	opt_mi_check_type opt_to mi_check_types normal_join
-	table_to_table_list table_to_table opt_table_list
+	table_to_table_list table_to_table opt_table_list opt_as
 	END_OF_INPUT
 
 %type <NONE>
@@ -673,13 +673,17 @@ create2:
 	| opt_create_table_options create3 {}
 
 create3:
-	/* empty*/ {}
-	| opt_duplicate SELECT_SYM
+	/* empty */ {}
+	| opt_duplicate opt_as SELECT_SYM
           {
 	    mysql_init_select(Lex);
           }
           select_options select_item_list opt_select_from {}
 
+opt_as:
+	/* empty */ {}
+	| AS	    {}
+
 opt_table_options:
 	/* empty */	 { $$= 0; }
 	| table_options  { $$= $1;}