Merge mysql.com:/d2/hf/mrg/mysql-5.0-opt

into  mysql.com:/d2/hf/mrg/mysql-5.1-opt


mysql-test/r/alter_table.result:
  Auto merged
mysql-test/r/distinct.result:
  Auto merged
mysql-test/r/heap.result:
  Auto merged
mysql-test/r/ps_2myisam.result:
  Auto merged
mysql-test/r/ps_3innodb.result:
  Auto merged
mysql-test/r/ps_4heap.result:
  Auto merged
mysql-test/r/ps_5merge.result:
  Auto merged
mysql-test/r/ps_7ndb.result:
  Auto merged
mysql-test/r/query_cache.result:
  Auto merged
BitKeeper/deleted/.del-CMakeLists.txt~1:
  Auto merged
BitKeeper/deleted/.del-ps_6bdb.result:
  Auto merged
mysql-test/t/alter_table.test:
  Auto merged
mysys/my_error.c:
  Auto merged
sql/field.cc:
  Auto merged
sql/field.h:
  Auto merged
sql/item.cc:
  Auto merged
sql/item.h:
  Auto merged
sql/item_cmpfunc.cc:
  Auto merged
sql/item_cmpfunc.h:
  Auto merged
sql/item_sum.cc:
  Auto merged
sql/key.cc:
  Auto merged
sql/sql_select.cc:
  Auto merged
sql/table.cc:
  Auto merged
tests/mysql_client_test.c:
  Auto merged
mysql-test/include/mix1.inc:
  merging
mysql-test/r/group_by.result:
  SCCS merged
mysql-test/r/innodb_mysql.result:
  merging
mysql-test/r/join.result:
  merging
mysql-test/r/subselect.result:
  merging
mysql-test/r/type_datetime.result:
  SCCS merged
mysql-test/r/windows.result:
  SCCS merged
mysql-test/t/group_by.test:
  SCCS merged
mysql-test/t/join.test:
  merging
mysql-test/t/subselect.test:
  merging
mysql-test/t/type_datetime.test:
  merging
mysql-test/t/windows.test:
  SCCS merged
sql/item_timefunc.cc:
  merging
sql/sql_base.cc:
  SCCS merged
storage/innobase/handler/ha_innodb.cc:
  merging

11 files changed, 567 insertions, 85 deletions

diff --git a/sql/field.cc b/sql/field.cc
index 82f8283ba56..db2a45af726 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -6395,6 +6395,15 @@ uint Field_string::max_packed_col_length(uint max_length)
   return (max_length > 255 ? 2 : 1)+max_length;
 }
 
+uint Field_string::get_key_image(char *buff, uint length, imagetype type_arg)
+{
+  uint bytes = my_charpos(field_charset, ptr, ptr + field_length,
+                          length / field_charset->mbmaxlen);
+  memcpy(buff, ptr, bytes);
+  if (bytes < length)
+    bzero(buff + bytes, length - bytes);
+  return bytes;
+}
 
 Field *Field_string::new_field(MEM_ROOT *root, struct st_table *new_table,
                                bool keep_type)
@@ -6853,9 +6862,7 @@ uint Field_varstring::max_packed_col_length(uint max_length)
   return (max_length > 255 ? 2 : 1)+max_length;
 }
 
-
-void Field_varstring::get_key_image(char *buff, uint length,
-                                    imagetype type_arg)
+uint Field_varstring::get_key_image(char *buff, uint length, imagetype type)
 {
   uint f_length=  length_bytes == 1 ? (uint) (uchar) *ptr : uint2korr(ptr);
   uint local_char_length= length / field_charset->mbmaxlen;
@@ -6874,6 +6881,7 @@ void Field_varstring::get_key_image(char *buff, uint length,
     */
     bzero(buff+HA_KEY_BLOB_LENGTH+f_length, (length-f_length));
   }
+  return HA_KEY_BLOB_LENGTH+f_length;
 }
 
 
@@ -7278,7 +7286,7 @@ int Field_blob::cmp_binary(const char *a_ptr, const char *b_ptr,
 
 /* The following is used only when comparing a key */
 
-void Field_blob::get_key_image(char *buff, uint length, imagetype type_arg)
+uint Field_blob::get_key_image(char *buff,uint length, imagetype type_arg)
 {
   uint32 blob_length= get_length(ptr);
   char *blob;
@@ -7290,16 +7298,17 @@ void Field_blob::get_key_image(char *buff, uint length, imagetype type_arg)
     MBR mbr;
     Geometry_buffer buffer;
     Geometry *gobj;
+    const uint image_length= SIZEOF_STORED_DOUBLE*4;
 
     if (blob_length < SRID_SIZE)
     {
-      bzero(buff, SIZEOF_STORED_DOUBLE*4);
-      return;
+      bzero(buff, image_length);
+      return image_length;
     }
     get_ptr(&blob);
     gobj= Geometry::construct(&buffer, blob, blob_length);
     if (!gobj || gobj->get_mbr(&mbr, &dummy))
-      bzero(buff, SIZEOF_STORED_DOUBLE*4);
+      bzero(buff, image_length);
     else
     {
       float8store(buff,    mbr.xmin);
@@ -7307,7 +7316,7 @@ void Field_blob::get_key_image(char *buff, uint length, imagetype type_arg)
       float8store(buff+16, mbr.ymin);
       float8store(buff+24, mbr.ymax);
     }
-    return;
+    return image_length;
   }
 #endif /*HAVE_SPATIAL*/
 
@@ -7328,6 +7337,7 @@ void Field_blob::get_key_image(char *buff, uint length, imagetype type_arg)
   }
   int2store(buff,length);
   memcpy(buff+HA_KEY_BLOB_LENGTH, blob, length);
+  return HA_KEY_BLOB_LENGTH+length;
 }
 
 
@@ -7613,7 +7623,7 @@ uint Field_blob::max_packed_col_length(uint max_length)
 
 #ifdef HAVE_SPATIAL
 
-void Field_geom::get_key_image(char *buff, uint length, imagetype type_arg)
+uint Field_geom::get_key_image(char *buff, uint length, imagetype type)
 {
   char *blob;
   const char *dummy;
@@ -7621,16 +7631,17 @@ void Field_geom::get_key_image(char *buff, uint length, imagetype type_arg)
   ulong blob_length= get_length(ptr);
   Geometry_buffer buffer;
   Geometry *gobj;
+  const uint image_length= SIZEOF_STORED_DOUBLE*4;
 
   if (blob_length < SRID_SIZE)
   {
-    bzero(buff, SIZEOF_STORED_DOUBLE*4);
-    return;
+    bzero(buff, image_length);
+    return image_length;
   }
   get_ptr(&blob);
   gobj= Geometry::construct(&buffer, blob, blob_length);
   if (!gobj || gobj->get_mbr(&mbr, &dummy))
-    bzero(buff, SIZEOF_STORED_DOUBLE*4);
+    bzero(buff, image_length);
   else
   {
     float8store(buff, mbr.xmin);
@@ -7638,6 +7649,7 @@ void Field_geom::get_key_image(char *buff, uint length, imagetype type_arg)
     float8store(buff + 16, mbr.ymin);
     float8store(buff + 24, mbr.ymax);
   }
+  return image_length;
 }
 
 
@@ -8421,7 +8433,7 @@ int Field_bit::cmp_offset(uint row_offset)
 }
 
 
-void Field_bit::get_key_image(char *buff, uint length, imagetype type_arg)
+uint Field_bit::get_key_image(char *buff, uint length, imagetype type_arg)
 {
   if (bit_len)
   {
@@ -8429,7 +8441,9 @@ void Field_bit::get_key_image(char *buff, uint length, imagetype type_arg)
     *buff++= bits;
     length--;
   }
-  memcpy(buff, ptr, min(length, bytes_in_rec));
+  uint data_length = min(length, bytes_in_rec);
+  memcpy(buff, ptr, data_length);
+  return data_length + 1;
 }
 
 
diff --git a/sql/field.h b/sql/field.h
index 441ff9079c1..b1be522e260 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -276,8 +276,39 @@ public:
     { memcpy(buff,ptr,length); }
   inline void set_image(char *buff,uint length, CHARSET_INFO *cs)
     { memcpy(ptr,buff,length); }
-  virtual void get_key_image(char *buff, uint length, imagetype type_arg)
-    { get_image(buff,length, &my_charset_bin); }
+
+
+  /*
+    Copy a field part into an output buffer.
+
+    SYNOPSIS
+      Field::get_key_image()
+      buff   [out] output buffer
+      length       output buffer size
+      type         itMBR for geometry blobs, otherwise itRAW
+
+    DESCRIPTION
+      This function makes a copy of field part of size equal to or
+      less than "length" parameter value.
+      For fields of string types (CHAR, VARCHAR, TEXT) the rest of buffer
+      is padded by zero byte.
+
+    NOTES
+      For variable length character fields (i.e. UTF-8) the "length"
+      parameter means a number of output buffer bytes as if all field
+      characters have maximal possible size (mbmaxlen). In the other words,
+      "length" parameter is a number of characters multiplied by
+      field_charset->mbmaxlen.
+
+    RETURN
+      Number of copied bytes (excluding padded zero bytes -- see above).
+  */
+
+  virtual uint get_key_image(char *buff, uint length, imagetype type)
+  {
+    get_image(buff, length, &my_charset_bin);
+    return length;
+  }
   virtual void set_key_image(char *buff,uint length)
     { set_image(buff,length, &my_charset_bin); }
   inline longlong val_int_offset(uint row_offset)
@@ -1133,6 +1164,7 @@ public:
   bool has_charset(void) const
   { return charset() == &my_charset_bin ? FALSE : TRUE; }
   Field *new_field(MEM_ROOT *root, struct st_table *new_table, bool keep_type);
+  virtual uint get_key_image(char *buff,uint length, imagetype type);
 };
 
 
@@ -1185,7 +1217,7 @@ public:
     return cmp_max(a, b, ~0L);
   }
   void sort_string(char *buff,uint length);
-  void get_key_image(char *buff,uint length, imagetype type);
+  uint get_key_image(char *buff,uint length, imagetype type);
   void set_key_image(char *buff,uint length);
   void sql_type(String &str) const;
   char *pack(char *to, const char *from, uint max_length=~(uint) 0);
@@ -1297,7 +1329,7 @@ public:
       store_length(length);
       memcpy_fixed(ptr+packlength,&data,sizeof(char*));
     }
-  void get_key_image(char *buff,uint length, imagetype type);
+  uint get_key_image(char *buff,uint length, imagetype type);
   void set_key_image(char *buff,uint length);
   void sql_type(String &str) const;
   inline bool copy()
@@ -1354,7 +1386,7 @@ public:
   int  store(double nr);
   int  store(longlong nr, bool unsigned_val);
   int  store_decimal(const my_decimal *);
-  void get_key_image(char *buff,uint length,imagetype type);
+  uint get_key_image(char *buff,uint length,imagetype type);
   uint size_of() const { return sizeof(*this); }
   int  reset(void) { return !maybe_null() || Field_blob::reset(); }
 };
@@ -1478,7 +1510,7 @@ public:
   { return cmp_binary((char *) a, (char *) b); }
   int key_cmp(const byte *str, uint length);
   int cmp_offset(uint row_offset);
-  void get_key_image(char *buff, uint length, imagetype type);
+  uint get_key_image(char *buff, uint length, imagetype type);
   void set_key_image(char *buff, uint length)
   { Field_bit::store(buff, length, &my_charset_bin); }
   void sort_string(char *buff, uint length)
diff --git a/sql/item.cc b/sql/item.cc
index bd5e0ae1a8f..f339bad78e4 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -3576,9 +3576,13 @@ Item_field::fix_outer_field(THD *thd, Field **from_field, Item **reference)
             select->inner_refs_list.push_back(rf);
             rf->in_sum_func= thd->lex->in_sum_func;
           }
+          /*
+            A reference is resolved to a nest level that's outer or the same as
+            the nest level of the enclosing set function : adjust the value of
+            max_arg_level for the function if it's needed.
+          */
           if (thd->lex->in_sum_func &&
-              thd->lex->in_sum_func->nest_level == 
-              thd->lex->current_select->nest_level)
+              thd->lex->in_sum_func->nest_level >= select->nest_level)
           {
             Item::Type ref_type= (*reference)->type();
             set_if_bigger(thd->lex->in_sum_func->max_arg_level,
@@ -4237,6 +4241,21 @@ enum_field_types Item::field_type() const
 }
 
 
+bool Item::is_datetime()
+{
+  switch (field_type())
+  {
+    case MYSQL_TYPE_DATE:
+    case MYSQL_TYPE_DATETIME:
+    case MYSQL_TYPE_TIMESTAMP:
+      return TRUE;
+    default:
+      break;
+  }
+  return FALSE;
+}
+
+
 /*
   Create a field to hold a string value from an item
 
@@ -5283,6 +5302,16 @@ bool Item_ref::fix_fields(THD *thd, Item **reference)
         thd->change_item_tree(reference, fld);
         mark_as_dependent(thd, last_checked_context->select_lex,
                           thd->lex->current_select, this, fld);
+        /*
+          A reference is resolved to a nest level that's outer or the same as
+          the nest level of the enclosing set function : adjust the value of
+          max_arg_level for the function if it's needed.
+        */
+        if (thd->lex->in_sum_func &&
+            thd->lex->in_sum_func->nest_level >= 
+            last_checked_context->select_lex->nest_level)
+          set_if_bigger(thd->lex->in_sum_func->max_arg_level,
+                        last_checked_context->select_lex->nest_level);
         return FALSE;
       }
       if (ref == 0)
@@ -5296,6 +5325,16 @@ bool Item_ref::fix_fields(THD *thd, Item **reference)
       DBUG_ASSERT(*ref && (*ref)->fixed);
       mark_as_dependent(thd, last_checked_context->select_lex,
                         context->select_lex, this, this);
+      /*
+        A reference is resolved to a nest level that's outer or the same as
+        the nest level of the enclosing set function : adjust the value of
+        max_arg_level for the function if it's needed.
+      */
+      if (thd->lex->in_sum_func &&
+          thd->lex->in_sum_func->nest_level >= 
+          last_checked_context->select_lex->nest_level)
+        set_if_bigger(thd->lex->in_sum_func->max_arg_level,
+                      last_checked_context->select_lex->nest_level);
     }
   }
 
@@ -6228,6 +6267,14 @@ void Item_cache_int::store(Item *item)
 }
 
 
+void Item_cache_int::store(Item *item, longlong val_arg)
+{
+  value= val_arg;
+  null_value= item->null_value;
+  unsigned_flag= item->unsigned_flag;
+}
+
+
 String *Item_cache_int::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
diff --git a/sql/item.h b/sql/item.h
index 747d79afda9..8b57f831cbd 100644
--- a/sql/item.h
+++ b/sql/item.h
@@ -942,6 +942,7 @@ public:
     representation is more precise than the string one).
   */
   virtual bool result_as_longlong() { return FALSE; }
+  bool is_datetime();
 };
 
 
@@ -2553,11 +2554,13 @@ public:
   Item_cache_int(): Item_cache(), value(0) {}
 
   void store(Item *item);
+  void store(Item *item, longlong val_arg);
   double val_real() { DBUG_ASSERT(fixed == 1); return (double) value; }
   longlong val_int() { DBUG_ASSERT(fixed == 1); return value; }
   String* val_str(String *str);
   my_decimal *val_decimal(my_decimal *);
   enum Item_result result_type() const { return INT_RESULT; }
+  bool result_as_longlong() { return TRUE; }
 };
 
 
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index 54b78cdf51c..982179a3bb2 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -436,7 +436,9 @@ void Item_bool_func2::fix_length_and_dec()
     if (arg_real_item->type() == FIELD_ITEM)
     {
       Field *field=((Item_field*) arg_real_item)->field;
-      if (field->can_be_compared_as_longlong())
+      if (field->can_be_compared_as_longlong() &&
+          !(arg_real_item->is_datetime() &&
+            args[1]->result_type() == STRING_RESULT))
       {
         if (convert_constant_item(thd, field,&args[1]))
         {
@@ -451,7 +453,9 @@ void Item_bool_func2::fix_length_and_dec()
     if (arg_real_item->type() == FIELD_ITEM)
     {
       Field *field=((Item_field*) arg_real_item)->field;
-      if (field->can_be_compared_as_longlong())
+      if (field->can_be_compared_as_longlong() &&
+          !(arg_real_item->is_datetime() &&
+            args[0]->result_type() == STRING_RESULT))
       {
         if (convert_constant_item(thd, field,&args[0]))
         {
@@ -570,6 +574,334 @@ int Arg_comparator::set_compare_func(Item_bool_func2 *item, Item_result type)
 }
 
 
+/*
+  Convert date provided in a string to the int representation.
+
+  SYNOPSIS
+    get_date_from_str()
+    thd              Thread handle
+    str              a string to convert
+    warn_type        type of the timestamp for issuing the warning
+    warn_name        field name for issuing the warning
+    error_arg  [out] TRUE if string isn't a DATETIME or clipping occur
+
+  DESCRIPTION
+    Convert date provided in the string str to the int representation.
+    if the string contains wrong date or doesn't contain it at all
+    then the warning is issued and TRUE returned in the error_arg argument.
+    The warn_type and the warn_name arguments are used as the name and the
+    type of the field when issuing the warning.
+
+  RETURN
+    converted value.
+*/
+
+static ulonglong
+get_date_from_str(THD *thd, String *str, timestamp_type warn_type,
+                  char *warn_name, bool *error_arg)
+{
+  ulonglong value= 0;
+  int error;
+  MYSQL_TIME l_time;
+  enum_mysql_timestamp_type ret;
+  *error_arg= TRUE;
+
+  ret= str_to_datetime(str->ptr(), str->length(), &l_time,
+                       (TIME_FUZZY_DATE | MODE_INVALID_DATES |
+                        (thd->variables.sql_mode &
+                         (MODE_NO_ZERO_IN_DATE | MODE_NO_ZERO_DATE))),
+                       &error);
+  if ((ret == MYSQL_TIMESTAMP_DATETIME || ret == MYSQL_TIMESTAMP_DATE))
+  {
+    value= TIME_to_ulonglong_datetime(&l_time);
+    *error_arg= FALSE;
+  }
+
+  if (error || *error_arg)
+  {
+    make_truncated_value_warning(thd, str->ptr(), str->length(), warn_type,
+                                 warn_name);
+    *error_arg= TRUE;
+  }
+  return value;
+}
+
+
+/*
+  Check whether compare_datetime() can be used to compare items.
+
+  SYNOPSIS
+    Arg_comparator::can_compare_as_dates()
+    a, b          [in]  items to be compared
+    const_value   [out] converted value of the string constant, if any
+
+  DESCRIPTION
+    Check several cases when the DATE/DATETIME comparator should be used.
+    The following cases are checked:
+      1. Both a and b is a DATE/DATETIME field/function returning string or
+         int result.
+      2. Only a or b is a DATE/DATETIME field/function returning string or
+         int result and the other item (b or a) is an item with string result.
+         If the second item is a constant one then it's checked to be
+         convertible to the DATE/DATETIME type. If the constant can't be
+         converted to a DATE/DATETIME then the compare_datetime() comparator
+         isn't used and the warning about wrong DATE/DATETIME value is issued.
+      In all other cases (date-[int|real|decimal]/[int|real|decimal]-date)
+      the comparison is handled by other comparators.
+    If the datetime comparator can be used and one the operands of the
+    comparison is a string constant that was successfully converted to a
+    DATE/DATETIME type then the result of the conversion is returned in the
+    const_value if it is provided.  If there is no constant or
+    compare_datetime() isn't applicable then the *const_value remains
+    unchanged.
+
+  RETURN
+    the found type of date comparison
+*/
+
+enum Arg_comparator::enum_date_cmp_type
+Arg_comparator::can_compare_as_dates(Item *a, Item *b, ulonglong *const_value)
+{
+  enum enum_date_cmp_type cmp_type= CMP_DATE_DFLT;
+  Item *str_arg= 0, *date_arg= 0;
+
+  if (a->type() == Item::ROW_ITEM || b->type() == Item::ROW_ITEM)
+    return CMP_DATE_DFLT;
+
+  if (a->is_datetime())
+  {
+    if (b->is_datetime())
+      cmp_type= CMP_DATE_WITH_DATE;
+    else if (b->result_type() == STRING_RESULT)
+    {
+      cmp_type= CMP_DATE_WITH_STR;
+      date_arg= a;
+      str_arg= b;
+    }
+  }
+  else if (b->is_datetime() && a->result_type() == STRING_RESULT)
+  {
+    cmp_type= CMP_STR_WITH_DATE;
+    date_arg= b;
+    str_arg= a;
+  }
+
+  if (cmp_type != CMP_DATE_DFLT)
+  {
+    if (cmp_type != CMP_DATE_WITH_DATE && str_arg->const_item())
+    {
+      THD *thd= current_thd;
+      ulonglong value;
+      bool error;
+      String tmp, *str_val= 0;
+      timestamp_type t_type= (date_arg->field_type() == MYSQL_TYPE_DATE ?
+                              MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME);
+
+      str_val= str_arg->val_str(&tmp);
+      if (str_arg->null_value)
+        return CMP_DATE_DFLT;
+      value= get_date_from_str(thd, str_val, t_type, date_arg->name, &error);
+      if (error)
+        return CMP_DATE_DFLT;
+      if (const_value)
+        *const_value= value;
+    }
+  }
+  return cmp_type;
+}
+
+
+int Arg_comparator::set_cmp_func(Item_bool_func2 *owner_arg,
+                                        Item **a1, Item **a2,
+                                        Item_result type)
+{
+  enum enum_date_cmp_type cmp_type;
+  ulonglong const_value;
+  a= a1;
+  b= a2;
+
+  if ((cmp_type= can_compare_as_dates(*a, *b, &const_value)))
+  {
+    thd= current_thd;
+    owner= owner_arg;
+    a_type= (*a)->field_type();
+    b_type= (*b)->field_type();
+    a_cache= 0;
+    b_cache= 0;
+
+    if (cmp_type != CMP_DATE_WITH_DATE &&
+        ((*b)->const_item() || (*a)->const_item()))
+    {
+      Item_cache_int *cache= new Item_cache_int();
+      /* Mark the cache as non-const to prevent re-caching. */
+      cache->set_used_tables(1);
+      if (!(*a)->is_datetime())
+      {
+        cache->store((*a), const_value);
+        a_cache= cache;
+        a= (Item **)&a_cache;
+      }
+      else
+      {
+        cache->store((*b), const_value);
+        b_cache= cache;
+        b= (Item **)&b_cache;
+      }
+    }
+    is_nulls_eq= test(owner && owner->functype() == Item_func::EQUAL_FUNC);
+    func= &Arg_comparator::compare_datetime;
+    return 0;
+  }
+  return set_compare_func(owner_arg, type);
+}
+
+
+void Arg_comparator::set_datetime_cmp_func(Item **a1, Item **b1)
+{
+  thd= current_thd;
+  /* A caller will handle null values by itself. */
+  owner= NULL;
+  a= a1;
+  b= b1;
+  a_type= (*a)->field_type();
+  b_type= (*b)->field_type();
+  a_cache= 0;
+  b_cache= 0;
+  is_nulls_eq= FALSE;
+  func= &Arg_comparator::compare_datetime;
+}
+
+/*
+  Retrieves correct DATETIME value from given item.
+
+  SYNOPSIS
+    get_datetime_value()
+    thd                 thread handle
+    item_arg   [in/out] item to retrieve DATETIME value from
+    cache_arg  [in/out] pointer to place to store the caching item to
+    warn_item  [in]     item for issuing the conversion warning
+    is_null    [out]    TRUE <=> the item_arg is null
+
+  DESCRIPTION
+    Retrieves the correct DATETIME value from given item for comparison by the
+    compare_datetime() function.
+    If item's result can be compared as longlong then its int value is used
+    and its string value is used otherwise. Strings are always parsed and
+    converted to int values by the get_date_from_str() function.
+    This allows us to compare correctly string dates with missed insignificant
+    zeros. If an item is a constant one then its value is cached and it isn't
+    get parsed again. An Item_cache_int object is used for caching values. It
+    seamlessly substitutes the original item.  The cache item is marked as
+    non-constant to prevent re-caching it again.  In order to compare
+    correctly DATE and DATETIME items the result of the former are treated as
+    a DATETIME with zero time (00:00:00).
+
+  RETURN
+    obtained value
+*/
+
+static ulonglong
+get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
+                   Item *warn_item, bool *is_null)
+{
+  ulonglong value= 0;
+  String buf, *str= 0;
+  Item *item= **item_arg;
+
+  if (item->result_as_longlong())
+  {
+    value= item->val_int();
+    *is_null= item->null_value;
+    if (item->field_type() == MYSQL_TYPE_DATE)
+      value*= 1000000L;
+  }
+  else
+  {
+    str= item->val_str(&buf);
+    *is_null= item->null_value;
+  }
+  if (*is_null)
+    return -1;
+  /*
+    Convert strings to the integer DATE/DATETIME representation.
+    Even if both dates provided in strings we can't compare them directly as
+    strings as there is no warranty that they are correct and do not miss
+    some insignificant zeros.
+  */
+  if (str)
+  {
+    bool error;
+    enum_field_types f_type= warn_item->field_type();
+    timestamp_type t_type= f_type ==
+      MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
+    value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
+  }
+  if (item->const_item())
+  {
+    Item_cache_int *cache= new Item_cache_int();
+    /* Mark the cache as non-const to prevent re-caching. */
+    cache->set_used_tables(1);
+    cache->store(item, value);
+    *cache_arg= cache;
+    *item_arg= cache_arg;
+  }
+  return value;
+}
+
+/*
+  Compare items values as dates.
+
+  SYNOPSIS
+    Arg_comparator::compare_datetime()
+
+  DESCRIPTION
+    Compare items values as DATE/DATETIME for both EQUAL_FUNC and from other
+    comparison functions. The correct DATETIME values are obtained
+    with help of the get_datetime_value() function.
+
+  RETURN
+    If is_nulls_eq is TRUE:
+       1    if items are equal or both are null
+       0    otherwise
+    If is_nulls_eq is FALSE:
+      -1   a < b or one of items is null
+       0   a == b
+       1   a > b
+*/
+
+int Arg_comparator::compare_datetime()
+{
+  bool is_null= FALSE;
+  ulonglong a_value, b_value;
+
+  /* Get DATE/DATETIME value of the 'a' item. */
+  a_value= get_datetime_value(thd, &a, &a_cache, *b, &is_null);
+  if (!is_nulls_eq && is_null)
+  {
+    if (owner)
+      owner->null_value= 1;
+    return -1;
+  }
+
+  /* Get DATE/DATETIME value of the 'b' item. */
+  b_value= get_datetime_value(thd, &b, &b_cache, *a, &is_null);
+  if (is_null)
+  {
+    if (owner)
+      owner->null_value= is_nulls_eq ? 0 : 1;
+    return is_nulls_eq ? 1 : -1;
+  }
+
+  if (owner)
+    owner->null_value= 0;
+
+  /* Compare values. */
+  if (is_nulls_eq)
+    return (a_value == b_value);
+  return a_value < b_value ? -1 : (a_value > b_value ? 1 : 0);
+}
+
+
 int Arg_comparator::compare_string()
 {
   String *res1,*res2;
@@ -1419,8 +1751,11 @@ bool Item_func_between::fix_fields(THD *thd, Item **ref)
 
 void Item_func_between::fix_length_and_dec()
 {
-   max_length= 1;
-   THD *thd= current_thd;
+  max_length= 1;
+  THD *thd= current_thd;
+  int i;
+  bool datetime_found= FALSE;
+  compare_as_dates= TRUE;
 
   /*
     As some compare functions are generated after sql_yacc,
@@ -1435,26 +1770,29 @@ void Item_func_between::fix_length_and_dec()
    return;
 
   /*
-    Make a special case of compare with date/time and longlong fields.
-    They are compared as integers, so for const item this time-consuming
-    conversion can be done only once, not for every single comparison
+    Detect the comparison of DATE/DATETIME items.
+    At least one of items should be a DATE/DATETIME item and other items
+    should return the STRING result.
   */
-  if (args[0]->real_item()->type() == FIELD_ITEM &&
-      thd->lex->sql_command != SQLCOM_CREATE_VIEW &&
-      thd->lex->sql_command != SQLCOM_SHOW_CREATE)
+  for (i= 0; i < 3; i++)
   {
-    Field *field=((Item_field*) (args[0]->real_item()))->field;
-    if (field->can_be_compared_as_longlong())
+    if (args[i]->is_datetime())
     {
-      /*
-        The following can't be recoded with || as convert_constant_item
-        changes the argument
-      */
-      if (convert_constant_item(thd, field,&args[1]))
-	cmp_type=INT_RESULT;			// Works for all types.
-      if (convert_constant_item(thd, field,&args[2]))
-	cmp_type=INT_RESULT;			// Works for all types.
+      datetime_found= TRUE;
+      continue;
     }
+    if (args[i]->result_type() == STRING_RESULT)
+      continue;
+    compare_as_dates= FALSE;
+    break;
+  }
+  if (!datetime_found)
+    compare_as_dates= FALSE;
+
+  if (compare_as_dates)
+  {
+    ge_cmp.set_datetime_cmp_func(args, args + 1);
+    le_cmp.set_datetime_cmp_func(args, args + 2);
   }
 }
 
@@ -1462,7 +1800,27 @@ void Item_func_between::fix_length_and_dec()
 longlong Item_func_between::val_int()
 {						// ANSI BETWEEN
   DBUG_ASSERT(fixed == 1);
-  if (cmp_type == STRING_RESULT)
+  if (compare_as_dates)
+  {
+    int ge_res, le_res;
+
+    ge_res= ge_cmp.compare();
+    if ((null_value= args[0]->null_value))
+      return 0;
+    le_res= le_cmp.compare();
+
+    if (!args[1]->null_value && !args[2]->null_value)
+      return (longlong) ((ge_res >= 0 && le_res <=0) != negated);
+    else if (args[1]->null_value)
+    {
+      null_value= le_res > 0;			// not null if false range.
+    }
+    else
+    {
+      null_value= ge_res < 0;
+    }
+  }
+  else if (cmp_type == STRING_RESULT)
   {
     String *value,*a,*b;
     value=args[0]->val_str(&value0);
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index f35b6a126ed..7aede7d2954 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -35,12 +35,19 @@ class Arg_comparator: public Sql_alloc
   Item_bool_func2 *owner;
   Arg_comparator *comparators;   // used only for compare_row()
   double precision;
-
+  /* Fields used in DATE/DATETIME comparison. */
+  THD *thd;
+  enum_field_types a_type, b_type; // Types of a and b items
+  Item *a_cache, *b_cache;         // Cached values of a and b items
+  bool is_nulls_eq;                // TRUE <=> compare for the EQUAL_FUNC
+  enum enum_date_cmp_type { CMP_DATE_DFLT= 0, CMP_DATE_WITH_DATE,
+                            CMP_DATE_WITH_STR, CMP_STR_WITH_DATE };
 public:
   DTCollation cmp_collation;
 
-  Arg_comparator() {};
-  Arg_comparator(Item **a1, Item **a2): a(a1), b(a2) {};
+  Arg_comparator(): thd(0), a_cache(0), b_cache(0) {};
+  Arg_comparator(Item **a1, Item **a2): a(a1), b(a2), thd(0),
+    a_cache(0), b_cache(0) {};
 
   int set_compare_func(Item_bool_func2 *owner, Item_result type);
   inline int set_compare_func(Item_bool_func2 *owner_arg)
@@ -48,14 +55,10 @@ public:
     return set_compare_func(owner_arg, item_cmp_type((*a)->result_type(),
                                                      (*b)->result_type()));
   }
-  inline int set_cmp_func(Item_bool_func2 *owner_arg,
+  int set_cmp_func(Item_bool_func2 *owner_arg,
 			  Item **a1, Item **a2,
-			  Item_result type)
-  {
-    a= a1;
-    b= a2;
-    return set_compare_func(owner_arg, type);
-  }
+			  Item_result type);
+
   inline int set_cmp_func(Item_bool_func2 *owner_arg,
 			  Item **a1, Item **a2)
   {
@@ -83,7 +86,12 @@ public:
   int compare_e_row();           // compare args[0] & args[1]
   int compare_real_fixed();
   int compare_e_real_fixed();
+  int compare_datetime();        // compare args[0] & args[1] as DATETIMEs
+
+  static enum enum_date_cmp_type can_compare_as_dates(Item *a, Item *b,
+                                                      ulonglong *const_val_arg);
 
+  void set_datetime_cmp_func(Item **a1, Item **b1);
   static arg_cmp_func comparator_matrix [5][2];
 
   friend class Item_func;
@@ -574,8 +582,12 @@ class Item_func_between :public Item_func_opt_neg
 public:
   Item_result cmp_type;
   String value0,value1,value2;
+  /* TRUE <=> arguments will be compared as dates. */
+  bool compare_as_dates;
+  /* Comparators used for DATE/DATETIME comparison. */
+  Arg_comparator ge_cmp, le_cmp;
   Item_func_between(Item *a, Item *b, Item *c)
-    :Item_func_opt_neg(a, b, c) {}
+    :Item_func_opt_neg(a, b, c), compare_as_dates(FALSE) {}
   longlong val_int();
   optimize_type select_optimize() const { return OPTIMIZE_KEY; }
   enum Functype functype() const   { return BETWEEN; }
diff --git a/sql/item_sum.cc b/sql/item_sum.cc
index f217a6ea953..2eced347650 100644
--- a/sql/item_sum.cc
+++ b/sql/item_sum.cc
@@ -175,13 +175,25 @@ bool Item_sum::check_sum_func(THD *thd, Item **ref)
                MYF(0));
     return TRUE;
   }
-  if (in_sum_func && in_sum_func->nest_level == nest_level)
+  if (in_sum_func)
   {
     /*
       If the set function is nested adjust the value of
       max_sum_func_level for the nesting set function.
+      We take into account only enclosed set functions that are to be 
+      aggregated on the same level or above of the nest level of 
+      the enclosing set function.
+      But we must always pass up the max_sum_func_level because it is
+      the maximum nested level of all directly and indirectly enclosed
+      set functions. We must do that even for set functions that are
+      aggregated inside of their enclosing set function's nest level
+      because the enclosing function may contain another enclosing
+      function that is to be aggregated outside or on the same level
+      as its parent's nest level.
     */
-    set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
+    if (in_sum_func->nest_level >= aggr_level)
+      set_if_bigger(in_sum_func->max_sum_func_level, aggr_level);
+    set_if_bigger(in_sum_func->max_sum_func_level, max_sum_func_level);
   }
   update_used_tables();
   thd->lex->in_sum_func= in_sum_func;
diff --git a/sql/key.cc b/sql/key.cc
index faa7bf1f04b..19861cee134 100644
--- a/sql/key.cc
+++ b/sql/key.cc
@@ -139,29 +139,22 @@ void key_copy(byte *to_key, byte *from_record, KEY *key_info, uint key_length)
         key_length--;
       }
     }
-    if (key_part->key_part_flag & HA_BLOB_PART)
-    {
-      char *pos;
-      ulong blob_length= ((Field_blob*) key_part->field)->get_length();
-      key_length-= HA_KEY_BLOB_LENGTH;
-      ((Field_blob*) key_part->field)->get_ptr(&pos);
-      length=min(key_length, key_part->length);
-      set_if_smaller(blob_length, length);
-      int2store(to_key, (uint) blob_length);
-      to_key+= HA_KEY_BLOB_LENGTH;			// Skip length info
-      memcpy(to_key, pos, blob_length);
-    }
-    else if (key_part->key_part_flag & HA_VAR_LENGTH_PART)
+    if (key_part->key_part_flag & HA_BLOB_PART ||
+        key_part->key_part_flag & HA_VAR_LENGTH_PART)
     {
       key_length-= HA_KEY_BLOB_LENGTH;
       length= min(key_length, key_part->length);
-      key_part->field->get_key_image((char *) to_key, length, Field::itRAW);
+      key_part->field->get_key_image((char*) to_key, length, Field::itRAW);
       to_key+= HA_KEY_BLOB_LENGTH;
     }
     else
     {
       length= min(key_length, key_part->length);
-      memcpy(to_key, from_record + key_part->offset, (size_t) length);
+      Field *field= key_part->field;
+      CHARSET_INFO *cs= field->charset();
+      uint bytes= field->get_key_image((char*) to_key, length, Field::itRAW);
+      if (bytes < length)
+        cs->cset->fill(cs, (char*) to_key + bytes, length - bytes, ' ');
     }
     to_key+= length;
     key_length-= length;
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 0f428da1fdb..f26d6e5777b 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -5575,6 +5575,7 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
   */
   arena= thd->activate_stmt_arena_if_needed(&backup);
 
+  thd->lex->current_select->cur_pos_in_select_list= 0;
   while (wild_num && (item= it++))
   {
     if (item->type() == Item::FIELD_ITEM &&
@@ -5616,7 +5617,10 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields,
       }
       wild_num--;
     }
+    else
+      thd->lex->current_select->cur_pos_in_select_list++;
   }
+  thd->lex->current_select->cur_pos_in_select_list= UNDEF_POS;
   if (arena)
   {
     /* make * substituting permanent */
@@ -6099,6 +6103,7 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name,
       }
       else
         thd->used_tables|= item->used_tables();
+      thd->lex->current_select->cur_pos_in_select_list++;
     }
     /*
       In case of stored tables, all fields are considered as used,
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 14423c949e5..ca127297481 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -5615,8 +5615,9 @@ static void add_not_null_conds(JOIN *join)
   for (uint i=join->const_tables ; i < join->tables ; i++)
   {
     JOIN_TAB *tab=join->join_tab+i;
-    if ((tab->type == JT_REF || tab->type == JT_REF_OR_NULL) &&
-         !tab->table->maybe_null)
+    if ((tab->type == JT_REF || tab->type == JT_EQ_REF || 
+         tab->type == JT_REF_OR_NULL) &&
+        !tab->table->maybe_null)
     {
       for (uint keypart= 0; keypart < tab->ref.key_parts; keypart++)
       {
@@ -8857,17 +8858,13 @@ static bool
 test_if_equality_guarantees_uniqueness(Item *l, Item *r)
 {
   return r->const_item() &&
-    /* elements must be of the same result type */
-    (r->result_type() == l->result_type() ||
-    /* or dates compared to longs */
-     (((l->type() == Item::FIELD_ITEM &&
-        ((Item_field *)l)->field->can_be_compared_as_longlong()) ||
-       (l->type() == Item::FUNC_ITEM &&
-        ((Item_func *)l)->result_as_longlong())) &&
-      r->result_type() == INT_RESULT))
-    /* and must have the same collation if compared as strings */
-    && (l->result_type() != STRING_RESULT ||
-        l->collation.collation == r->collation.collation);
+    /* elements must be compared as dates */
+     (Arg_comparator::can_compare_as_dates(l, r, 0) ||
+      /* or of the same result type */
+      (r->result_type() == l->result_type() &&
+       /* and must have the same collation if compared as strings */
+       (l->result_type() != STRING_RESULT ||
+        l->collation.collation == r->collation.collation)));
 }
 
 /*
diff --git a/sql/table.cc b/sql/table.cc
index 39bdbb4cbb9..9be98eb14b9 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -3552,7 +3552,16 @@ const char *Field_iterator_table::name()
 
 Item *Field_iterator_table::create_item(THD *thd)
 {
-  return new Item_field(thd, &thd->lex->current_select->context, *ptr);
+  SELECT_LEX *select= thd->lex->current_select;
+
+  Item_field *item= new Item_field(thd, &select->context, *ptr);
+  if (item && thd->variables.sql_mode & MODE_ONLY_FULL_GROUP_BY &&
+      !thd->lex->in_sum_func && select->cur_pos_in_select_list != UNDEF_POS)
+  {
+    select->non_agg_fields.push_back(item);
+    item->marker= select->cur_pos_in_select_list;
+  }
+  return item;
 }