Merge moonbone.local:/home/evgen/bk-trees/mysql-4.1

into  moonbone.local:/work/tmp_merge-4.1-opt-mysql


mysql-test/r/date_formats.result:
  Auto merged
mysql-test/t/date_formats.test:
  Auto merged
sql/item_strfunc.cc:
  Auto merged
sql/sql_class.cc:
  Auto merged

7 files changed, 63 insertions, 24 deletions

diff --git a/sql/item.h b/sql/item.h
index eca8ee184a1..3eab695cb5e 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -275,9 +275,16 @@ public:
     Any new item which can be NULL must implement this call.
   */
   virtual bool is_null() { return 0; }
+
   /*
-    it is "top level" item of WHERE clause and we do not need correct NULL
-    handling
+    Inform the item that there will be no distinction between its result
+    being FALSE or NULL.
+
+    NOTE
+      This function will be called for eg. Items that are top-level AND-parts
+      of the WHERE clause. Items implementing this function (currently
+      Item_cond_and and subquery-related item) enable special optimizations
+      when they are "top level".
   */
   virtual void top_level_item() {}
   /*
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index ee585649f8c..7bc7956283b 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -709,44 +709,47 @@ String *Item_func_reverse::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
   String *res = args[0]->val_str(str);
-  char *ptr,*end;
+  char *ptr, *end, *tmp;
 
   if ((null_value=args[0]->null_value))
     return 0;
   /* An empty string is a special case as the string pointer may be null */
   if (!res->length())
     return &my_empty_string;
-  res=copy_if_not_alloced(str,res,res->length());
-  ptr = (char *) res->ptr();
-  end=ptr+res->length();
+  if (tmp_value.alloced_length() < res->length() &&
+      tmp_value.realloc(res->length()))
+  {
+    null_value= 1;
+    return 0;
+  }
+  tmp_value.length(res->length());
+  tmp_value.set_charset(res->charset());
+  ptr= (char *) res->ptr();
+  end= ptr + res->length();
+  tmp= (char *) tmp_value.ptr() + tmp_value.length();
 #ifdef USE_MB
   if (use_mb(res->charset()))
   {
-    String tmpstr;
-    tmpstr.copy(*res);
-    char *tmp = (char *) tmpstr.ptr() + tmpstr.length();
     register uint32 l;
     while (ptr < end)
     {
-      if ((l=my_ismbchar(res->charset(), ptr,end)))
-        tmp-=l, memcpy(tmp,ptr,l), ptr+=l;
+      if ((l= my_ismbchar(res->charset(),ptr,end)))
+      {
+        tmp-= l;
+        memcpy(tmp,ptr,l);
+        ptr+= l;
+      }
       else
-        *--tmp=*ptr++;
+        *--tmp= *ptr++;
     }
-    memcpy((char *) res->ptr(),(char *) tmpstr.ptr(), res->length());
   }
   else
 #endif /* USE_MB */
   {
-    char tmp;
     while (ptr < end)
-    {
-      tmp=*ptr;
-      *ptr++=*--end;
-      *end=tmp;
-    }
+      *--tmp= *ptr++;
   }
-  return res;
+  return &tmp_value;
 }
 
 
diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index 89bab4a909c..f800c17182b 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -100,6 +100,7 @@ public:
 
 class Item_func_reverse :public Item_str_func
 {
+  String tmp_value;
 public:
   Item_func_reverse(Item *a) :Item_str_func(a) {}
   String *val_str(String *);
diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc
index 8241a8e0402..f6f8eec9af5 100644
--- a/sql/item_subselect.cc
+++ b/sql/item_subselect.cc
@@ -705,7 +705,8 @@ Item_in_subselect::single_value_transformer(JOIN *join,
     if (!select_lex->group_list.elements &&
         !select_lex->having &&
 	!select_lex->with_sum_func &&
-	!(select_lex->next_select()))
+	!(select_lex->next_select()) &&
+        select_lex->table_list.elements)
     {
       Item_sum_hybrid *item;
       if (func->l_op())
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index da752977fcd..bd292814bfa 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -265,6 +265,7 @@ THD::THD()
     ulong tmp=sql_rnd_with_mutex();
     randominit(&rand, tmp + (ulong) &rand, tmp + (ulong) ::query_id);
   }
+  substitute_null_with_insert_id = FALSE;
 }
 
 
diff --git a/sql/sql_class.h b/sql/sql_class.h
index f1cf9c7b3e2..e8fe175cd7c 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -896,6 +896,8 @@ public:
   bool       last_cuted_field;
   bool	     no_errors, password, is_fatal_error;
   bool	     query_start_used,last_insert_id_used,insert_id_used,rand_used;
+  /* for IS NULL => = last_insert_id() fix in remove_eq_conds() */
+  bool       substitute_null_with_insert_id;
   bool	     time_zone_used;
   bool	     in_lock_tables;
   bool       query_error, bootstrap, cleanup_done;
@@ -991,6 +993,7 @@ public:
   {
     last_insert_id= id_arg;
     insert_id_used=1;
+    substitute_null_with_insert_id= TRUE;
   }
   inline ulonglong insert_id(void)
   {
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index cb23662f42c..fd8a5149edd 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -3845,6 +3845,7 @@ make_join_readinfo(JOIN *join, uint options)
 {
   uint i;
   bool statistics= test(!(join->select_options & SELECT_DESCRIBE));
+  bool ordered_set= 0;
   DBUG_ENTER("make_join_readinfo");
 
   for (i=join->const_tables ; i < join->tables ; i++)
@@ -3854,6 +3855,22 @@ make_join_readinfo(JOIN *join, uint options)
     tab->read_record.table= table;
     tab->read_record.file=table->file;
     tab->next_select=sub_select;		/* normal select */
+
+    /*
+      Determine if the set is already ordered for ORDER BY, so it can 
+      disable join cache because it will change the ordering of the results.
+      Code handles sort table that is at any location (not only first after 
+      the const tables) despite the fact that it's currently prohibited.
+    */
+    if (!ordered_set && 
+        (table == join->sort_by_table &&
+         (!join->order || join->skip_sort_order ||
+          test_if_skip_sort_order(tab, join->order, join->select_limit,
+                                  1))
+        ) ||
+        (join->sort_by_table == (TABLE *) 1 && i != join->const_tables))
+      ordered_set= 1;
+
     switch (tab->type) {
     case JT_SYSTEM:				// Only happens with left join
       table->status=STATUS_NO_RECORD;
@@ -3924,10 +3941,11 @@ make_join_readinfo(JOIN *join, uint options)
     case JT_ALL:
       /*
 	If previous table use cache
+        If the incoming data set is already sorted don't use cache.
       */
       table->status=STATUS_NO_RECORD;
       if (i != join->const_tables && !(options & SELECT_NO_JOIN_CACHE) &&
-	  tab->use_quick != 2 && !tab->on_expr)
+	  tab->use_quick != 2 && !tab->on_expr && !ordered_set)
       {
 	if ((options & SELECT_DESCRIBE) ||
 	    !join_init_cache(join->thd,join->join_tab+join->const_tables,
@@ -4754,7 +4772,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
       Field *field=((Item_field*) args[0])->field;
       if (field->flags & AUTO_INCREMENT_FLAG && !field->table->maybe_null &&
 	  (thd->options & OPTION_AUTO_IS_NULL) &&
-	  thd->insert_id())
+	  thd->insert_id() && thd->substitute_null_with_insert_id)
       {
 #ifdef HAVE_QUERY_CACHE
 	query_cache_abort(&thd->net);
@@ -4768,7 +4786,7 @@ remove_eq_conds(THD *thd, COND *cond, Item::cond_result *cond_value)
 	  cond=new_cond;
 	  cond->fix_fields(thd, 0, &cond);
 	}
-	thd->insert_id(0);		// Clear for next request
+	thd->substitute_null_with_insert_id= FALSE;   // Clear for next request
       }
       /* fix to replace 'NULL' dates with '0' (shreeve@uci.edu) */
       else if (((field->type() == FIELD_TYPE_DATE) ||
@@ -6780,8 +6798,13 @@ end_send_group(JOIN *join, JOIN_TAB *join_tab __attribute__((unused)),
 	{
 	  if (!join->first_record)
 	  {
+            List_iterator_fast<Item> it(*join->fields);
+            Item *item;
 	    /* No matching rows for group function */
 	    join->clear();
+
+            while ((item= it++))
+              item->no_rows_in_result();
 	  }
 	  if (join->having && join->having->val_int() == 0)
 	    error= -1;				// Didn't satisfy having