Some trivial optimzations

Check if AND/OR expression can be NULL; Fixed bug in GROUP BY and-or-expression where expression could be NULL
Bug fix for SHOW OPEN TABLES when user didn't have privilege to access all open tables.
Better fix for ALTER TABLE on BDB tables.


Docs/manual.texi:
  Changelog
client/mysql.cc:
  Simple optimization
libmysql/libmysql.c:
  Removed initialization of varibles that are already set to zero
myisam/myisamchk.c:
  Fixed comment for extend-check
mysql-test/r/bdb-alter-table-1.result:
  Updated results
mysql-test/r/bdb-alter-table-2.result:
  Updated results
mysql-test/r/distinct.result:
  Updated results after bug fix
mysql-test/r/handler.result:
  Updated results
mysql-test/r/innodb_handler.result:
  Updated results
mysql-test/r/select.result:
  Updated results
mysql-test/r/varbinary.result:
  Updated results
mysql-test/t/bdb-alter-table-1.test:
  Added comments
mysql-test/t/bdb-alter-table-2.test:
  Added comments
mysql-test/t/select.test:
  Remove OPTION in SET OPTION
  Added tests for ORDER BY key LIMIT
sql/item_cmpfunc.cc:
  Check if AND/OR expression can be NULL
sql/mysqld.cc:
  mysqld --help now shows value of datadir
sql/share/english/errmsg.txt:
  Better error message for syntax error
sql/sql_base.cc:
  Bug fix for SHOW OPEN TABLES
sql/sql_class.cc:
  Moved virtual function to .cc file to avoid that we have to include assert.h everywhere.
sql/sql_class.h:
  Moved virtual function to .cc file to avoid that we have to include assert.h everywhere.
sql/sql_parse.cc:
  Removed old dead code from 3.23
sql/sql_select.cc:
  Improved optimization of ORDER BY key LIMIT
sql/sql_table.cc:
  More comments,
  Better fix for ALTER TABLE on BDB tables.

9 files changed, 64 insertions, 32 deletions

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 be703c894f2..6c4fe5047c0 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 0ff0a6b3ac8..95132111313 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,
@@ -1883,9 +1908,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