Merge work:/my/mysql-4.0 into mashka.mysql.fi:/home/my/mysql-4.0

Docs/manual.texi:
  Auto merged
sql/mysqld.cc:
  Auto merged
sql/sql_table.cc:
  Auto merged

24 files changed, 130 insertions, 52 deletions

diff --git a/Docs/manual.texi b/Docs/manual.texi
index ffc7043f19a..27800f6e2ac 100644
--- a/Docs/manual.texi
+++ b/Docs/manual.texi
@@ -50364,6 +50364,9 @@ each individual 4.0.x release.
 
 @itemize @bullet
 @item
+Fixed bug with the @code{--slow-log} when logging an administrator command
+(like @code{FLUSH TABLES}).
+@item
 Fixed a bug that @code{OPTIMIZE} of locked and modified table,
 reported table corruption.
 @item
@@ -50372,6 +50375,12 @@ Fixed a bug in my_getopt in handling of special prefixes (--skip-, --enable-).
 similar options.
 @item
 Fixed bug in checking for output file name of the @code{tee} option.
+@item
+Added some more optimisation to use index for
+@code{SELECT ... FROM many_tables .. ORDER BY key limit #}
+@item
+Fixed problem in @code{SHOW OPEN TABLES} when a user didn't have access right
+to one of the opened tables.
 @end itemize
 
 @node News-4.0.3, News-4.0.2, News-4.0.4, News-4.0.x
diff --git a/client/mysql.cc b/client/mysql.cc
index 05e758dd5bd..eaf80a1014e 100644
--- a/client/mysql.cc
+++ b/client/mysql.cc
@@ -1857,7 +1857,7 @@ com_tee(String *buffer, char *line __attribute__((unused)))
   while (end > file_name && (isspace(end[-1]) || iscntrl(end[-1])))
     end--;
   end[0]= 0;
-  if (!strlen(file_name))
+  if (end == file_name)
   {
     printf("No outfile specified!\n");
     return 0;
diff --git a/libmysql/libmysql.c b/libmysql/libmysql.c
index b5e013c8e67..0c91ec11671 100644
--- a/libmysql/libmysql.c
+++ b/libmysql/libmysql.c
@@ -1351,13 +1351,12 @@ mysql_init(MYSQL *mysql)
     if (!(mysql=(MYSQL*) my_malloc(sizeof(*mysql),MYF(MY_WME | MY_ZEROFILL))))
       return 0;
     mysql->free_me=1;
-    mysql->net.vio = 0;
   }
   else
     bzero((char*) (mysql),sizeof(*(mysql)));
   mysql->options.connect_timeout=CONNECT_TIMEOUT;
   mysql->last_used_con = mysql->next_slave = mysql->master = mysql;
-  mysql->last_used_slave = 0;
+
   /*
     By default, we are a replication pivot. The caller must reset it
     after we return if this is not the case.
diff --git a/myisam/myisamchk.c b/myisam/myisamchk.c
index 33500da87dc..1239ad1ed9d 100644
--- a/myisam/myisamchk.c
+++ b/myisam/myisamchk.c
@@ -186,7 +186,7 @@ static struct my_option my_long_options[] =
    (gptr*) &check_param.max_data_file_length,
    0, GET_LL, REQUIRED_ARG, 0, 0, 0, 0, 0, 0},
   {"extend-check", 'e',
-   "Try to recover every possible row from the data file. Normally this will also find a lot of garbage rows; Don't use this option if you are not totally desperate.",
+   "If used when checking a table, ensure that the table is 100 percent consistent, which will take a long time. If used when repairing a table, try to recover every possible row from the data file. Normally this will also find a lot of garbage rows; Don't use this option with repair if you are not totally desperate.",
    0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0},
   {"fast", 'F',
    "Check only tables that haven't been closed properly.",
diff --git a/mysql-test/r/bdb-alter-table-1.result b/mysql-test/r/bdb-alter-table-1.result
index 3742f45eb7a..095d89355ad 100644
--- a/mysql-test/r/bdb-alter-table-1.result
+++ b/mysql-test/r/bdb-alter-table-1.result
@@ -1,4 +1,11 @@
+drop table if exists t1;
+create table t1(objid BIGINT not null, tablename  varchar(64), oid BIGINT not null, test BIGINT,  PRIMARY KEY (objid), UNIQUE(tablename))  type=BDB;
+insert into t1 values(1, 't1',4,9);
+insert into t1 values(2, 'metatable',1,9);
+insert into t1 values(3, 'metaindex',1,9 );
+select * from t1;
 objid	tablename	oid	test
 1	t1	4	9
 2	metatable	1	9
 3	metaindex	1	9
+alter table t1 drop column test;
diff --git a/mysql-test/r/bdb-alter-table-2.result b/mysql-test/r/bdb-alter-table-2.result
index 19c6d5e1ff9..c23b5ba0031 100644
--- a/mysql-test/r/bdb-alter-table-2.result
+++ b/mysql-test/r/bdb-alter-table-2.result
@@ -1,4 +1,6 @@
+select * from t1;
 objid	tablename	oid
 1	t1	4
 2	metatable	1
 3	metaindex	1
+drop table t1;
diff --git a/mysql-test/r/distinct.result b/mysql-test/r/distinct.result
index 312e7893949..da2bc3c7146 100644
--- a/mysql-test/r/distinct.result
+++ b/mysql-test/r/distinct.result
@@ -92,7 +92,8 @@ NULL	NULL	NULL
 0	0	
 select id >= 0 and id <= 5 as grp,count(*) from t1 group by grp;
 grp	count(*)
-0	7
+NULL	1
+0	6
 1	6
 SELECT DISTINCT FACILITY FROM t1;
 FACILITY
diff --git a/mysql-test/r/handler.result b/mysql-test/r/handler.result
index d495dde75bf..35765c48049 100644
--- a/mysql-test/r/handler.result
+++ b/mysql-test/r/handler.result
@@ -134,6 +134,6 @@ handler t2 read next;
 a	b
 19	fff
 handler t2 read last;
-You have an error in your SQL syntax near '' at line 1
+You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
 handler t2 close;
 drop table if exists t1;
diff --git a/mysql-test/r/innodb_handler.result b/mysql-test/r/innodb_handler.result
index 73368c932af..a55114a0e3d 100644
--- a/mysql-test/r/innodb_handler.result
+++ b/mysql-test/r/innodb_handler.result
@@ -134,6 +134,6 @@ handler t2 read next;
 a	b
 19	fff
 handler t2 read last;
-You have an error in your SQL syntax near '' at line 1
+You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1
 handler t2 close;
 drop table if exists t1;
diff --git a/mysql-test/r/select.result b/mysql-test/r/select.result
index 26c84af1618..88220b75591 100644
--- a/mysql-test/r/select.result
+++ b/mysql-test/r/select.result
@@ -1718,7 +1718,7 @@ insert into tmp select * from t3;
 insert into t3 select * from tmp;
 alter table t3 add t2nr int not null auto_increment primary key first;
 drop table tmp;
-SET OPTION SQL_BIG_TABLES=1;
+SET SQL_BIG_TABLES=1;
 select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
 namn
 Abraham Abraham
@@ -1731,7 +1731,7 @@ ammonium ammonium
 analyzable analyzable
 animals animals
 animized animized
-SET OPTION SQL_BIG_TABLES=0;
+SET SQL_BIG_TABLES=0;
 select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
 concat(fld3," ",fld3)
 Abraham Abraham
@@ -1768,7 +1768,7 @@ attendants	1
 bedlam	1
 bedpost	1
 boasted	1
-SET OPTION SQL_BIG_TABLES=1;
+SET SQL_BIG_TABLES=1;
 select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
 fld3	count(*)
 affixed	1
@@ -1781,7 +1781,7 @@ attendants	1
 bedlam	1
 bedpost	1
 boasted	1
-SET OPTION SQL_BIG_TABLES=0;
+SET SQL_BIG_TABLES=0;
 select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
 fld3	repeat("a",length(fld3))	count(*)
 circus	aaaaaa	1
@@ -1809,6 +1809,18 @@ explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2
 table	type	possible_keys	key	key_len	ref	rows	Extra
 t2	ALL	fld1	NULL	NULL	NULL	1199	where used; Using temporary; Using filesort
 t3	eq_ref	PRIMARY	PRIMARY	4	t2.fld1	1	where used; Using index
+explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	ALL	period	NULL	NULL	NULL	41810	Using temporary; Using filesort
+t3	ref	period	period	4	t1.period	4181	
+explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t3	index	period	period	4	NULL	41810	
+t1	ref	period	period	4	t3.period	4181	
+explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
+table	type	possible_keys	key	key_len	ref	rows	Extra
+t1	index	period	period	4	NULL	41810	
+t3	ref	period	period	4	t1.period	4181	
 select period from t1;
 period
 9410
diff --git a/mysql-test/r/varbinary.result b/mysql-test/r/varbinary.result
index 75ff9c3976b..4eb34ebfd63 100644
--- a/mysql-test/r/varbinary.result
+++ b/mysql-test/r/varbinary.result
@@ -16,7 +16,7 @@ table	type	possible_keys	key	key_len	ref	rows	Extra
 t1	const	UNIQ	UNIQ	8	const	1	
 drop table t1;
 select x'hello';
-You have an error in your SQL syntax near 'x'hello'' at line 1
+You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'x'hello'' at line 1
 select 0xfg;
 Unknown column '0xfg' in 'field list'
 create table t1 select 1 as x, 2 as xx;
diff --git a/mysql-test/t/bdb-alter-table-1.test b/mysql-test/t/bdb-alter-table-1.test
index 25a86cd92f6..5861c2fe0bf 100644
--- a/mysql-test/t/bdb-alter-table-1.test
+++ b/mysql-test/t/bdb-alter-table-1.test
@@ -1,8 +1,7 @@
--- source include/have_bdb.inc
-
 #
-# Small basic test for ALTER TABLE bug ..
+# Test of problem when shutting down mysqld at once after ALTER TABLE
 #
+-- source include/have_bdb.inc
 drop table if exists t1;
 create table t1(objid BIGINT not null, tablename  varchar(64), oid BIGINT not null, test BIGINT,  PRIMARY KEY (objid), UNIQUE(tablename))  type=BDB;
 insert into t1 values(1, 't1',4,9);
@@ -10,3 +9,5 @@ insert into t1 values(2, 'metatable',1,9);
 insert into t1 values(3, 'metaindex',1,9 );
 select * from t1;
 alter table t1 drop column test;
+
+# Now we do a reboot and continue with the next test
diff --git a/mysql-test/t/bdb-alter-table-2-master.opt b/mysql-test/t/bdb-alter-table-2-master.opt
new file mode 100644
index 00000000000..15ad73c500f
--- /dev/null
+++ b/mysql-test/t/bdb-alter-table-2-master.opt
@@ -0,0 +1,2 @@
+--skip-external-locking
+
diff --git a/mysql-test/t/bdb-alter-table-2.test b/mysql-test/t/bdb-alter-table-2.test
index 69ff04ee24a..a474efe42e1 100644
--- a/mysql-test/t/bdb-alter-table-2.test
+++ b/mysql-test/t/bdb-alter-table-2.test
@@ -1,3 +1,8 @@
+#
+# Note that this test uses tables from the previous test
+# This is to test that the table t1 survives a reboot of MySQL
+# The options in the -master.opt file are just there to force the reboot
+#
 -- source include/have_bdb.inc
 select * from t1;
-drop table t1;
\ No newline at end of file
+drop table t1;
diff --git a/mysql-test/t/select.test b/mysql-test/t/select.test
index 4dd833616d5..94806d44e37 100644
--- a/mysql-test/t/select.test
+++ b/mysql-test/t/select.test
@@ -1405,9 +1405,9 @@ drop table tmp;
 
 # big table done
 
-SET OPTION SQL_BIG_TABLES=1;
+SET SQL_BIG_TABLES=1;
 select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10;
-SET OPTION SQL_BIG_TABLES=0;
+SET SQL_BIG_TABLES=0;
 select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10;
 select distinct fld5 from t2 limit 10;
 
@@ -1416,9 +1416,9 @@ select distinct fld5 from t2 limit 10;
 #
 
 select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
-SET OPTION SQL_BIG_TABLES=1; # Force use of MyISAM
+SET SQL_BIG_TABLES=1; # Force use of MyISAM
 select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10;
-SET OPTION SQL_BIG_TABLES=0;
+SET SQL_BIG_TABLES=0;
 select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10;
 
 #
@@ -1440,6 +1440,14 @@ select distinct fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr orde
 explain select t3.t2nr,fld3 from t2,t3 where t2.companynr = 34 and t2.fld1=t3.t2nr order by t3.t2nr,fld3;
 
 #
+# Some test with ORDER BY and limit
+#
+
+explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period;
+explain select * from t3 as t1,t3 where t1.period=t3.period order by t3.period limit 10;
+explain select * from t3 as t1,t3 where t1.period=t3.period order by t1.period limit 10;
+
+#
 # Search with a constant table.
 #
 
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 923bbccde24..79d695eea1e 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -1120,6 +1120,8 @@ Item_cond::fix_fields(THD *thd,TABLE_LIST *tables)
     used_tables_cache|=item->used_tables();
     with_sum_func= with_sum_func || item->with_sum_func;
     const_item_cache&=item->const_item();
+    if (item->maybe_null)
+      maybe_null=1;
   }
   if (thd)
     thd->cond_count+=list.elements;
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index c1ec7d6bd5c..9895897ec0d 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -377,11 +377,10 @@ char mysql_real_data_home[FN_REFLEN],
      blob_newline,f_fyllchar,max_sort_char,*mysqld_user,*mysqld_chroot,
      *opt_init_file;
 char *language_ptr= language;
+char mysql_data_home_buff[2], *mysql_data_home=mysql_real_data_home;
 #ifndef EMBEDDED_LIBRARY
-char mysql_data_home_buff[2], *mysql_data_home=mysql_data_home_buff;
 bool mysql_embedded=0;
 #else
-char *mysql_data_home=mysql_real_data_home;
 bool mysql_embedded=1;
 #endif
 
@@ -1959,6 +1958,7 @@ int main(int argc, char **argv)
   {
     unireg_abort(1);				/* purecov: inspected */
   }
+  mysql_data_home= mysql_data_home_buff;
   mysql_data_home[0]=FN_CURLIB;		// all paths are relative from here
   mysql_data_home[1]=0;
   server_init();
diff --git a/sql/share/english/errmsg.txt b/sql/share/english/errmsg.txt
index 450b3453b01..0999dce5712 100644
--- a/sql/share/english/errmsg.txt
+++ b/sql/share/english/errmsg.txt
@@ -150,7 +150,7 @@
 "Table '%-.64s.%-.64s' doesn't exist",
 "There is no such grant defined for user '%-.32s' on host '%-.64s' on table '%-.64s'",
 "The used command is not allowed with this MySQL version",
-"You have an error in your SQL syntax",
+"You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use",
 "Delayed insert thread couldn't get requested lock for table %-.64s",
 "Too many delayed threads in use",
 "Aborted connection %ld to db: '%-.64s' user: '%-.32s' (%-.64s)",
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 307de1a319e..caed108f529 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -157,7 +157,7 @@ OPEN_TABLE_LIST *list_open_tables(THD *thd, const char *wild)
     table_list.db= (char*) entry->table_cache_key;
     table_list.real_name= entry->real_name;
     table_list.grant.privilege=0;
-    if (check_table_access(thd,SELECT_ACL | EXTRA_ACL,&table_list))
+    if (check_table_access(thd,SELECT_ACL | EXTRA_ACL,&table_list,1))
       continue;
 
     /* need to check if we haven't already listed it */
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 5c0d2b31d4e..7e03986f4bb 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -356,6 +356,18 @@ CHANGED_TABLE_LIST* THD::changed_table_dup(TABLE *table)
 }
 
 
+#ifdef SIGNAL_WITH_VIO_CLOSE
+void THD::close_active_vio()
+{
+  safe_mutex_assert_owner(&LOCK_delete); 
+  if (active_vio)
+  {
+    vio_close(active_vio);
+    active_vio = 0;
+  }
+}
+#endif
+
 /*****************************************************************************
 ** Functions to provide a interface to select results
 *****************************************************************************/
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 5b35b4e7ea0..d101300ba15 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -475,15 +475,6 @@ public:
     active_vio = 0;
     pthread_mutex_unlock(&LOCK_delete);
   }
-  inline void close_active_vio()
-  {
-    safe_mutex_assert_owner(&LOCK_delete); 
-    if (active_vio)
-    {
-      vio_close(active_vio);
-      active_vio = 0;
-    }
-  }
 #endif  
   void awake(bool prepare_to_die);
   inline const char* enter_cond(pthread_cond_t *cond, pthread_mutex_t* mutex,
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index e38a3a5d340..d356f6fa2e7 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -1937,14 +1937,10 @@ mysql_execute_command(void)
       goto error;
     // Set privilege for the WHERE clause
     tables->grant.want_privilege=(SELECT_ACL & ~tables->grant.privilege);
-    /* TRUNCATE ends previous transaction */
-    if (lex->sql_command == SQLCOM_TRUNCATE && end_active_trans(thd))
-      res= -1;
-    else
-      res = mysql_delete(thd,tables, select_lex->where,
-			 (ORDER*) select_lex->order_list.first,
-                         select_lex->select_limit, lex->lock_option,
-			 select_lex->options);
+    res = mysql_delete(thd,tables, select_lex->where,
+		       (ORDER*) select_lex->order_list.first,
+		       select_lex->select_limit, lex->lock_option,
+		       select_lex->options);
     break;
   }
   case SQLCOM_DELETE_MULTI:
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 11b89722017..d94661a9d64 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -620,8 +620,7 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds,
 	(join.const_tables == join.tables ||
 	 (simple_order &&
 	  test_if_skip_sort_order(&join.join_tab[join.const_tables], order,
-				  (join.const_tables != join.tables - 1 ||
-				   (join.select_options & OPTION_FOUND_ROWS)) ?
+				  (join.select_options & OPTION_FOUND_ROWS) ?
 				  HA_POS_ERROR : thd->select_limit,0))))
       order=0;
     select_describe(&join,need_tmp,
@@ -876,7 +875,6 @@ mysql_select(THD *thd,TABLE_LIST *tables,List<Item> &fields,COND *conds,
     if (create_sort_index(&join.join_tab[join.const_tables],
 			  group ? group : order,
 			  (having || group ||
-			   join.const_tables != join.tables - 1 ||
 			   (join.select_options & OPTION_FOUND_ROWS)) ?
 			  HA_POS_ERROR : thd->select_limit))
       goto err; /* purecov: inspected */
@@ -2018,7 +2016,8 @@ find_best(JOIN *join,table_map rest_tables,uint idx,double record_count,
       join->positions[idx].key=best_key;
       join->positions[idx].table= s;
       if (!best_key && idx == join->const_tables &&
-	  s->table == join->sort_by_table)
+	  s->table == join->sort_by_table &&
+	  join->thd->select_limit >= records)
 	join->sort_by_table= (TABLE*) 1;	// Must use temporary table
 
      /*
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 9194c3cd8bc..de4e285b128 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -20,6 +20,9 @@
 #include "mysql_priv.h"
 #include <hash.h>
 #include <myisam.h>
+#ifdef HAVE_BERKELEY_DB
+#include <ha_berkeley.h>
+#endif
 #include <assert.h>
 
 #ifdef __WIN__
@@ -258,10 +261,32 @@ static int sort_keys(KEY *a, KEY *b)
 }
 
 
-/*****************************************************************************
- * Create a table.
- * If one creates a temporary table, this is automaticly opened
- ****************************************************************************/
+/*
+  Create a table
+
+  SYNOPSIS
+    mysql_create_table()
+    thd			Thread object
+    db			Database
+    table_name		Table name
+    create_info		Create information (like MAX_ROWS)
+    fields		List of fields to create
+    keys		List of keys to create
+    tmp_table		Set to 1 if this is a temporary table
+    no_log		Don't log the query to binary log.
+
+  DESCRIPTION		       
+    If one creates a temporary table, this is automaticly opened
+
+    no_log is needed for the case of CREATE ... SELECT,
+    as the logging will be done later in sql_insert.cc
+    select_field_count is also used for CREATE ... SELECT,
+    and must be zero for standard create of table.
+
+  RETURN VALUES
+    0	ok
+    -1	error
+*/
 
 int mysql_create_table(THD *thd,const char *db, const char *table_name,
 		       HA_CREATE_INFO *create_info,
@@ -1885,9 +1910,16 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
 #ifdef HAVE_BERKELEY_DB
   if (old_db_type == DB_TYPE_BERKELEY_DB)
   {
-    extern bool berkeley_flush_logs(void);
-    (void)berkeley_flush_logs();
-    table=open_ltable(thd,table_list,TL_READ);
+    (void) berkeley_flush_logs();
+    /*
+      For the alter table to be properly flushed to the logs, we
+      have to open the new table.  If not, we get a problem on server
+      shutdown.
+    */
+    if (!open_tables(thd, table_list))		// Should always succeed
+    {
+      close_thread_table(thd, &table_list->table);
+    }
   }
 #endif
   table_list->table=0;				// For query cache