Merge from 5.3

pending merges:
  Alexander Barkov 2013-09-09 MDEV-4863 COALESCE(time_or_datetime) returns...

7 files changed, 419 insertions, 177 deletions

diff --git a/sql/field.h b/sql/field.h
index 8df1c6572f4..3544444bf43 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -85,6 +85,34 @@ inline uint get_set_pack_length(int elements)
   return len > 4 ? 8 : len;
 }
 
+
+static inline enum enum_mysql_timestamp_type
+mysql_type_to_time_type(enum enum_field_types mysql_type)
+{
+  switch(mysql_type) {
+  case MYSQL_TYPE_TIME: return MYSQL_TIMESTAMP_TIME;
+  case MYSQL_TYPE_TIMESTAMP:
+  case MYSQL_TYPE_DATETIME: return MYSQL_TIMESTAMP_DATETIME;
+  case MYSQL_TYPE_NEWDATE:
+  case MYSQL_TYPE_DATE: return MYSQL_TIMESTAMP_DATE;
+  default: return MYSQL_TIMESTAMP_ERROR;
+  }
+}
+
+
+/**
+  Tests if field type is temporal, i.e. represents
+  DATE, TIME, DATETIME or TIMESTAMP types in SQL.
+     
+  @param type    Field type, as returned by field->type().
+  @retval true   If field type is temporal
+  @retval false  If field type is not temporal
+*/
+inline bool is_temporal_type(enum_field_types type)
+{
+  return mysql_type_to_time_type(type) != MYSQL_TIMESTAMP_ERROR;
+}
+
 /*
   Virtual_column_info is the class to contain additional
   characteristics that is specific for a virtual/computed
diff --git a/sql/item_cmpfunc.cc b/sql/item_cmpfunc.cc
index cd1881e2970..3760652d482 100644
--- a/sql/item_cmpfunc.cc
+++ b/sql/item_cmpfunc.cc
@@ -2376,12 +2376,13 @@ void
 Item_func_ifnull::fix_length_and_dec()
 {
   uint32 char_length;
-  agg_result_type(&hybrid_type, args, 2);
+  agg_result_type(&cached_result_type, args, 2);
+  cached_field_type= agg_field_type(args, 2);
   maybe_null=args[1]->maybe_null;
   decimals= max(args[0]->decimals, args[1]->decimals);
   unsigned_flag= args[0]->unsigned_flag && args[1]->unsigned_flag;
 
-  if (hybrid_type == DECIMAL_RESULT || hybrid_type == INT_RESULT) 
+  if (cached_result_type == DECIMAL_RESULT || cached_result_type == INT_RESULT) 
   {
     int len0= args[0]->max_char_length() - args[0]->decimals
       - (args[0]->unsigned_flag ? 0 : 1);
@@ -2394,9 +2395,9 @@ Item_func_ifnull::fix_length_and_dec()
   else
     char_length= max(args[0]->max_char_length(), args[1]->max_char_length());
 
-  switch (hybrid_type) {
+  switch (cached_result_type) {
   case STRING_RESULT:
-    if (agg_arg_charsets_for_comparison(collation, args, arg_count))
+    if (count_string_result_length(cached_field_type, args, arg_count))
       return;
     break;
   case DECIMAL_RESULT:
@@ -2411,7 +2412,6 @@ Item_func_ifnull::fix_length_and_dec()
     DBUG_ASSERT(0);
   }
   fix_char_length(char_length);
-  cached_field_type= agg_field_type(args, 2);
 }
 
 
@@ -2425,11 +2425,6 @@ uint Item_func_ifnull::decimal_precision() const
 }
 
 
-enum_field_types Item_func_ifnull::field_type() const 
-{
-  return cached_field_type;
-}
-
 Field *Item_func_ifnull::tmp_table_field(TABLE *table)
 {
   return tmp_table_field_from_field_type(table, 0);
@@ -2503,6 +2498,18 @@ Item_func_ifnull::str_op(String *str)
 }
 
 
+bool Item_func_ifnull::date_op(MYSQL_TIME *ltime, uint fuzzydate)
+{
+  DBUG_ASSERT(fixed == 1);
+  if (!args[0]->get_date(ltime, fuzzydate & ~TIME_FUZZY_DATES))
+    return (null_value= false);
+  if (!args[1]->get_date(ltime, fuzzydate & ~TIME_FUZZY_DATES))
+    return (null_value= false);
+  bzero((char*) ltime,sizeof(*ltime));
+  return null_value= !(fuzzydate & TIME_FUZZY_DATES);
+}
+
+
 /**
   Perform context analysis of an IF item tree.
 
@@ -2597,20 +2604,20 @@ Item_func_if::fix_length_and_dec()
   }
 
   agg_result_type(&cached_result_type, args + 1, 2);
+  cached_field_type= agg_field_type(args + 1, 2);
   maybe_null= args[1]->maybe_null || args[2]->maybe_null;
   decimals= max(args[1]->decimals, args[2]->decimals);
   unsigned_flag=args[1]->unsigned_flag && args[2]->unsigned_flag;
 
   if (cached_result_type == STRING_RESULT)
   {
-    if (agg_arg_charsets_for_string_result(collation, args + 1, 2))
-      return;
+    count_string_result_length(cached_field_type, args + 1, 2);
+    return;
   }
   else
   {
     collation.set_numeric(); // Number
   }
-  cached_field_type= agg_field_type(args + 1, 2);
 
   uint32 char_length;
   if ((cached_result_type == DECIMAL_RESULT )
@@ -2640,7 +2647,7 @@ uint Item_func_if::decimal_precision() const
 
 
 double
-Item_func_if::val_real()
+Item_func_if::real_op()
 {
   DBUG_ASSERT(fixed == 1);
   Item *arg= args[0]->val_bool() ? args[1] : args[2];
@@ -2650,7 +2657,7 @@ Item_func_if::val_real()
 }
 
 longlong
-Item_func_if::val_int()
+Item_func_if::int_op()
 {
   DBUG_ASSERT(fixed == 1);
   Item *arg= args[0]->val_bool() ? args[1] : args[2];
@@ -2660,7 +2667,7 @@ Item_func_if::val_int()
 }
 
 String *
-Item_func_if::val_str(String *str)
+Item_func_if::str_op(String *str)
 {
   DBUG_ASSERT(fixed == 1);
   Item *arg= args[0]->val_bool() ? args[1] : args[2];
@@ -2673,7 +2680,7 @@ Item_func_if::val_str(String *str)
 
 
 my_decimal *
-Item_func_if::val_decimal(my_decimal *decimal_value)
+Item_func_if::decimal_op(my_decimal *decimal_value)
 {
   DBUG_ASSERT(fixed == 1);
   Item *arg= args[0]->val_bool() ? args[1] : args[2];
@@ -2683,6 +2690,14 @@ Item_func_if::val_decimal(my_decimal *decimal_value)
 }
 
 
+bool Item_func_if::date_op(MYSQL_TIME *ltime, uint fuzzydate)
+{
+  DBUG_ASSERT(fixed == 1);
+  Item *arg= args[0]->val_bool() ? args[1] : args[2];
+  return (null_value= arg->get_date(ltime, fuzzydate));
+}
+
+
 void
 Item_func_nullif::fix_length_and_dec()
 {
@@ -2841,7 +2856,7 @@ Item *Item_func_case::find_item(String *str)
 }
 
 
-String *Item_func_case::val_str(String *str)
+String *Item_func_case::str_op(String *str)
 {
   DBUG_ASSERT(fixed == 1);
   String *res;
@@ -2859,7 +2874,7 @@ String *Item_func_case::val_str(String *str)
 }
 
 
-longlong Item_func_case::val_int()
+longlong Item_func_case::int_op()
 {
   DBUG_ASSERT(fixed == 1);
   char buff[MAX_FIELD_WIDTH];
@@ -2877,7 +2892,7 @@ longlong Item_func_case::val_int()
   return res;
 }
 
-double Item_func_case::val_real()
+double Item_func_case::real_op()
 {
   DBUG_ASSERT(fixed == 1);
   char buff[MAX_FIELD_WIDTH];
@@ -2896,7 +2911,7 @@ double Item_func_case::val_real()
 }
 
 
-my_decimal *Item_func_case::val_decimal(my_decimal *decimal_value)
+my_decimal *Item_func_case::decimal_op(my_decimal *decimal_value)
 {
   DBUG_ASSERT(fixed == 1);
   char buff[MAX_FIELD_WIDTH];
@@ -2916,6 +2931,18 @@ my_decimal *Item_func_case::val_decimal(my_decimal *decimal_value)
 }
 
 
+bool Item_func_case::date_op(MYSQL_TIME *ltime, uint fuzzydate)
+{
+  DBUG_ASSERT(fixed == 1);
+  char buff[MAX_FIELD_WIDTH];
+  String dummy_str(buff, sizeof(buff), default_charset());
+  Item *item= find_item(&dummy_str);
+  if (!item)
+    return (null_value= true);
+  return (null_value= item->get_date(ltime, fuzzydate));
+}
+
+
 bool Item_func_case::fix_fields(THD *thd, Item **ref)
 {
   /*
@@ -2983,7 +3010,10 @@ void Item_func_case::fix_length_and_dec()
 
   if (!(agg= (Item**) sql_alloc(sizeof(Item*)*(ncases+1))))
     return;
-  
+
+  if (else_expr_num == -1 || args[else_expr_num]->maybe_null)
+    maybe_null= 1;
+
   /*
     Aggregate all THEN and ELSE expression types
     and collations when string result
@@ -2996,9 +3026,11 @@ void Item_func_case::fix_length_and_dec()
     agg[nagg++]= args[else_expr_num];
   
   agg_result_type(&cached_result_type, agg, nagg);
+  cached_field_type= agg_field_type(agg, nagg);
+
   if (cached_result_type == STRING_RESULT)
   {
-    if (agg_arg_charsets_for_string_result(collation, agg, nagg))
+    if (count_string_result_length(cached_field_type, agg, nagg))
       return;
     /*
       Copy all THEN and ELSE items back to args[] array.
@@ -3011,11 +3043,22 @@ void Item_func_case::fix_length_and_dec()
       change_item_tree_if_needed(thd, &args[else_expr_num], agg[nagg++]);
   }
   else
+  {
     collation.set_numeric();
+    max_length=0;
+    decimals=0;
+    unsigned_flag= TRUE;
+    for (uint i= 0; i < ncases; i+= 2)
+      agg_num_lengths(args[i + 1]);
+    if (else_expr_num != -1) 
+      agg_num_lengths(args[else_expr_num]);
+    max_length= my_decimal_precision_to_length_no_truncation(max_length +
+                                                             decimals, decimals,
+                                               unsigned_flag);
+  }
   
-  cached_field_type= agg_field_type(agg, nagg);
   /*
-    Aggregate first expression and all THEN expression types
+    Aggregate first expression and all WHEN expression types
     and collations when string comparison
   */
   if (first_expr_num != -1)
@@ -3101,30 +3144,6 @@ void Item_func_case::fix_length_and_dec()
       args[i]->cmp_context= item_cmp_type(left_result_type,
                                           args[i]->result_type());
   }
-
-  if (else_expr_num == -1 || args[else_expr_num]->maybe_null)
-    maybe_null=1;
-  
-  max_length=0;
-  decimals=0;
-  unsigned_flag= TRUE;
-  if (cached_result_type == STRING_RESULT)
-  {
-    for (uint i= 0; i < ncases; i+= 2)
-      agg_str_lengths(args[i + 1]);
-    if (else_expr_num != -1)
-      agg_str_lengths(args[else_expr_num]);
-  }
-  else
-  {
-    for (uint i= 0; i < ncases; i+= 2)
-      agg_num_lengths(args[i + 1]);
-    if (else_expr_num != -1) 
-      agg_num_lengths(args[else_expr_num]);
-    max_length= my_decimal_precision_to_length_no_truncation(max_length +
-                                                             decimals, decimals,
-                                               unsigned_flag);
-  }
 }
 
 
@@ -3232,7 +3251,7 @@ double Item_func_coalesce::real_op()
 }
 
 
-bool Item_func_coalesce::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
+bool Item_func_coalesce::date_op(MYSQL_TIME *ltime,uint fuzzydate)
 {
   DBUG_ASSERT(fixed == 1);
   null_value= 0;
@@ -3265,21 +3284,11 @@ my_decimal *Item_func_coalesce::decimal_op(my_decimal *decimal_value)
 void Item_func_coalesce::fix_length_and_dec()
 {
   cached_field_type= agg_field_type(args, arg_count);
-  agg_result_type(&hybrid_type, args, arg_count);
-  Item_result cmp_type;
-  agg_cmp_type(&cmp_type, args, arg_count);
-  ///< @todo let result_type() return TIME_RESULT and remove this special case
-  if (cmp_type == TIME_RESULT)
-  {
-    count_real_length();
-    return;
-  }
-  switch (hybrid_type) {
+  agg_result_type(&cached_result_type, args, arg_count);
+  switch (cached_result_type) {
   case STRING_RESULT:
-    decimals= NOT_FIXED_DEC;
-    if (agg_arg_charsets_for_string_result(collation, args, arg_count))
-      return;
-    count_only_length();
+    if (count_string_result_length(cached_field_type, args, arg_count))
+      return;          
     break;
   case DECIMAL_RESULT:
     count_decimal_length();
@@ -3288,7 +3297,7 @@ void Item_func_coalesce::fix_length_and_dec()
     count_real_length();
     break;
   case INT_RESULT:
-    count_only_length();
+    count_only_length(args, arg_count);
     decimals= 0;
     break;
   case ROW_RESULT:
diff --git a/sql/item_cmpfunc.h b/sql/item_cmpfunc.h
index 251d06499bf..fc66b985925 100644
--- a/sql/item_cmpfunc.h
+++ b/sql/item_cmpfunc.h
@@ -734,24 +734,19 @@ public:
 };
 
 
-class Item_func_coalesce :public Item_func_numhybrid
+class Item_func_coalesce :public Item_func_hybrid_field_type
 {
-protected:
-  enum_field_types cached_field_type;
-  Item_func_coalesce(Item *a, Item *b) :Item_func_numhybrid(a, b) {}
 public:
-  Item_func_coalesce(List<Item> &list) :Item_func_numhybrid(list) {}
+  Item_func_coalesce(Item *a, Item *b) :Item_func_hybrid_field_type(a, b) {}
+  Item_func_coalesce(List<Item> &list) :Item_func_hybrid_field_type(list) {}
   double real_op();
   longlong int_op();
   String *str_op(String *);
   my_decimal *decimal_op(my_decimal *);
+  bool date_op(MYSQL_TIME *ltime,uint fuzzydate);
   void fix_length_and_dec();
-  void find_num_type() {}
-  enum Item_result result_type () const { return hybrid_type; }
   const char *func_name() const { return "coalesce"; }
   table_map not_null_tables() const { return 0; }
-  enum_field_types field_type() const { return cached_field_type; }
-  bool get_date(MYSQL_TIME *ltime, ulonglong fuzzydate);
 };
 
 
@@ -765,7 +760,7 @@ public:
   longlong int_op();
   String *str_op(String *str);
   my_decimal *decimal_op(my_decimal *);
-  enum_field_types field_type() const;
+  bool date_op(MYSQL_TIME *ltime,uint fuzzydate);
   void fix_length_and_dec();
   void update_used_tables()
   {
@@ -778,20 +773,17 @@ public:
 };
 
 
-class Item_func_if :public Item_func
+class Item_func_if :public Item_func_hybrid_field_type
 {
-  enum Item_result cached_result_type;
-  enum_field_types cached_field_type;
 public:
   Item_func_if(Item *a,Item *b,Item *c)
-    :Item_func(a,b,c), cached_result_type(INT_RESULT)
+    :Item_func_hybrid_field_type(a,b,c)
   {}
-  double val_real();
-  longlong val_int();
-  String *val_str(String *str);
-  my_decimal *val_decimal(my_decimal *);
-  enum Item_result result_type () const { return cached_result_type; }
-  enum_field_types field_type() const { return cached_field_type; }
+  bool date_op(MYSQL_TIME *ltime, uint fuzzydate);
+  longlong int_op();
+  double real_op();
+  my_decimal *decimal_op(my_decimal *);
+  String *str_op(String *);
   bool fix_fields(THD *, Item **);
   void fix_length_and_dec();
   void update_used_tables()
@@ -1228,21 +1220,20 @@ public:
   function and only comparators for there result types are used.
 */
 
-class Item_func_case :public Item_func
+class Item_func_case :public Item_func_hybrid_field_type
 {
   int first_expr_num, else_expr_num;
-  enum Item_result cached_result_type, left_result_type;
+  enum Item_result left_result_type;
   String tmp_value;
   uint ncases;
   Item_result cmp_type;
   DTCollation cmp_collation;
-  enum_field_types cached_field_type;
   cmp_item *cmp_items[6]; /* For all result types */
   cmp_item *case_item;
 public:
   Item_func_case(List<Item> &list, Item *first_expr_arg, Item *else_expr_arg)
-    :Item_func(), first_expr_num(-1), else_expr_num(-1),
-    cached_result_type(INT_RESULT), left_result_type(INT_RESULT), case_item(0)
+    :Item_func_hybrid_field_type(), first_expr_num(-1), else_expr_num(-1),
+    left_result_type(INT_RESULT), case_item(0)
   {
     ncases= list.elements;
     if (first_expr_arg)
@@ -1258,10 +1249,11 @@ public:
     set_arguments(list);
     bzero(&cmp_items, sizeof(cmp_items));
   }
-  double val_real();
-  longlong val_int();
-  String *val_str(String *);
-  my_decimal *val_decimal(my_decimal *);
+  double real_op();
+  longlong int_op();
+  String *str_op(String *);
+  my_decimal *decimal_op(my_decimal *);
+  bool date_op(MYSQL_TIME *ltime, uint fuzzydate);
   bool fix_fields(THD *thd, Item **ref);
   void fix_length_and_dec();
   void update_used_tables()
@@ -1272,8 +1264,6 @@ public:
   }
   uint decimal_precision() const;
   table_map not_null_tables() const { return 0; }
-  enum Item_result result_type () const { return cached_result_type; }
-  enum_field_types field_type() const { return cached_field_type; }
   const char *func_name() const { return "case"; }
   virtual void print(String *str, enum_query_type query_type);
   Item *find_item(String *str);
diff --git a/sql/item_func.cc b/sql/item_func.cc
index 2b94333272b..443530fc7e9 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -620,6 +620,30 @@ void Item_func_numhybrid::fix_num_length_and_dec()
 {}
 
 
+
+/**
+  Count max_length and decimals for temporal functions.
+
+  @param item    Argument array
+  @param nitems  Number of arguments in the array.
+
+  @retval        False on success, true on error.
+*/
+void Item_func::count_datetime_length(Item **item, uint nitems)
+{
+  unsigned_flag= 0;
+  decimals= 0;
+  if (field_type() != MYSQL_TYPE_DATE)
+  {
+    for (uint i= 0; i < nitems; i++)
+      set_if_bigger(decimals, item[i]->decimals);
+  }
+  set_if_smaller(decimals, TIME_SECOND_PART_DIGITS);
+  uint len= decimals ? (decimals + 1) : 0;
+  len+= mysql_temporal_int_part_length(field_type());
+  fix_char_length(len);
+}
+
 /**
   Set max_length/decimals of function if function is fixed point and
   result length/precision depends on argument ones.
@@ -647,14 +671,14 @@ void Item_func::count_decimal_length()
   Set max_length of if it is maximum length of its arguments.
 */
 
-void Item_func::count_only_length()
+void Item_func::count_only_length(Item **item, uint nitems)
 {
   uint32 char_length= 0;
   unsigned_flag= 0;
-  for (uint i=0 ; i < arg_count ; i++)
+  for (uint i= 0; i < nitems ; i++)
   {
-    set_if_bigger(char_length, args[i]->max_char_length());
-    set_if_bigger(unsigned_flag, args[i]->unsigned_flag);
+    set_if_bigger(char_length, item[i]->max_char_length());
+    set_if_bigger(unsigned_flag, item[i]->unsigned_flag);
   }
   fix_char_length(char_length);
 }
@@ -691,6 +715,30 @@ void Item_func::count_real_length()
 }
 
 
+/**
+  Calculate max_length and decimals for STRING_RESULT functions.
+
+  @param field_type  Field type.
+  @param items       Argument array.
+  @param nitems      Number of arguments.
+
+  @retval            False on success, true on error.
+*/
+bool Item_func::count_string_result_length(enum_field_types field_type,
+                                           Item **items, uint nitems)
+{
+  if (agg_arg_charsets(collation, items, nitems, MY_COLL_ALLOW_CONV, 1))
+    return true;
+  if (is_temporal_type(field_type))
+    count_datetime_length(items, nitems);
+  else
+  {
+    decimals= NOT_FIXED_DEC;
+    count_only_length(items, nitems);
+  }
+  return false;
+}
+
 
 void Item_func::signal_divide_by_null()
 {
@@ -763,26 +811,26 @@ void Item_num_op::find_num_type(void)
   {
     count_real_length();
     max_length= float_length(decimals);
-    hybrid_type= REAL_RESULT;
+    cached_result_type= REAL_RESULT;
   }
   else if (r0 == DECIMAL_RESULT || r1 == DECIMAL_RESULT ||
            r0 == TIME_RESULT || r1 == TIME_RESULT)
   {
-    hybrid_type= DECIMAL_RESULT;
+    cached_result_type= DECIMAL_RESULT;
     result_precision();
     fix_decimals();
   }
   else
   {
     DBUG_ASSERT(r0 == INT_RESULT && r1 == INT_RESULT);
-    hybrid_type=INT_RESULT;
+    cached_result_type=INT_RESULT;
     result_precision();
     decimals= 0;
   }
   DBUG_PRINT("info", ("Type: %s",
-             (hybrid_type == REAL_RESULT ? "REAL_RESULT" :
-              hybrid_type == DECIMAL_RESULT ? "DECIMAL_RESULT" :
-              hybrid_type == INT_RESULT ? "INT_RESULT" :
+             (cached_result_type == REAL_RESULT ? "REAL_RESULT" :
+              cached_result_type == DECIMAL_RESULT ? "DECIMAL_RESULT" :
+              cached_result_type == INT_RESULT ? "INT_RESULT" :
               "--ILLEGAL!!!--")));
   DBUG_VOID_RETURN;
 }
@@ -798,17 +846,17 @@ void Item_func_num1::find_num_type()
 {
   DBUG_ENTER("Item_func_num1::find_num_type");
   DBUG_PRINT("info", ("name %s", func_name()));
-  switch (hybrid_type= args[0]->cast_to_int_type()) {
+  switch (cached_result_type= args[0]->cast_to_int_type()) {
   case INT_RESULT:
     unsigned_flag= args[0]->unsigned_flag;
     break;
   case STRING_RESULT:
   case REAL_RESULT:
-    hybrid_type= REAL_RESULT;
+    cached_result_type= REAL_RESULT;
     max_length= float_length(decimals);
     break;
   case TIME_RESULT:
-    hybrid_type= DECIMAL_RESULT;
+    cached_result_type= DECIMAL_RESULT;
   case DECIMAL_RESULT:
     break;
   case ROW_RESULT:
@@ -816,9 +864,9 @@ void Item_func_num1::find_num_type()
     DBUG_ASSERT(0);
   }
   DBUG_PRINT("info", ("Type: %s",
-                      (hybrid_type == REAL_RESULT ? "REAL_RESULT" :
-                       hybrid_type == DECIMAL_RESULT ? "DECIMAL_RESULT" :
-                       hybrid_type == INT_RESULT ? "INT_RESULT" :
+                      (cached_result_type == REAL_RESULT ? "REAL_RESULT" :
+                       cached_result_type == DECIMAL_RESULT ? "DECIMAL_RESULT" :
+                       cached_result_type == INT_RESULT ? "INT_RESULT" :
                        "--ILLEGAL!!!--")));
   DBUG_VOID_RETURN;
 }
@@ -838,10 +886,10 @@ void Item_func_numhybrid::fix_length_and_dec()
 }
 
 
-String *Item_func_numhybrid::val_str(String *str)
+String *Item_func_hybrid_result_type::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
-  switch (hybrid_type) {
+  switch (cached_result_type) {
   case DECIMAL_RESULT:
   {
     my_decimal decimal_value, *val;
@@ -869,6 +917,21 @@ String *Item_func_numhybrid::val_str(String *str)
     break;
   }
   case STRING_RESULT:
+    if (is_temporal_type(field_type()))
+    {
+      MYSQL_TIME ltime;
+      if (date_op(&ltime,
+                  field_type() == MYSQL_TYPE_TIME ? TIME_TIME_ONLY : 0) ||
+          str->alloc(MAX_DATE_STRING_REP_LENGTH))
+      {
+        null_value= 1;
+        return (String *) 0;
+      }
+      ltime.time_type= mysql_type_to_time_type(field_type());
+      str->length(my_TIME_to_str(&ltime, const_cast<char*>(str->ptr()), decimals));
+      str->set_charset(&my_charset_bin);
+      return str;
+    }
     return str_op(&str_value);
   case TIME_RESULT:
   case ROW_RESULT:
@@ -879,10 +942,10 @@ String *Item_func_numhybrid::val_str(String *str)
 }
 
 
-double Item_func_numhybrid::val_real()
+double Item_func_hybrid_result_type::val_real()
 {
   DBUG_ASSERT(fixed == 1);
-  switch (hybrid_type) {
+  switch (cached_result_type) {
   case DECIMAL_RESULT:
   {
     my_decimal decimal_value, *val;
@@ -901,6 +964,18 @@ double Item_func_numhybrid::val_real()
     return real_op();
   case STRING_RESULT:
   {
+    if (is_temporal_type(field_type()))
+    {
+      MYSQL_TIME ltime;
+      if (date_op(&ltime,
+                  field_type() == MYSQL_TYPE_TIME ? TIME_TIME_ONLY : 0 ))
+      {
+        null_value= 1;
+        return 0;
+      }
+      ltime.time_type= mysql_type_to_time_type(field_type());
+      return TIME_to_double(&ltime);
+    }
     char *end_not_used;
     int err_not_used;
     String *res= str_op(&str_value);
@@ -916,10 +991,10 @@ double Item_func_numhybrid::val_real()
 }
 
 
-longlong Item_func_numhybrid::val_int()
+longlong Item_func_hybrid_result_type::val_int()
 {
   DBUG_ASSERT(fixed == 1);
-  switch (hybrid_type) {
+  switch (cached_result_type) {
   case DECIMAL_RESULT:
   {
     my_decimal decimal_value, *val;
@@ -935,6 +1010,18 @@ longlong Item_func_numhybrid::val_int()
     return (longlong) rint(real_op());
   case STRING_RESULT:
   {
+    if (is_temporal_type(field_type()))
+    {
+      MYSQL_TIME ltime;
+      if (date_op(&ltime,
+                  field_type() == MYSQL_TYPE_TIME ? TIME_TIME_ONLY : 0))
+      {
+        null_value= 1;
+        return 0;
+      }
+      ltime.time_type= mysql_type_to_time_type(field_type());
+      return TIME_to_ulonglong(&ltime);
+    }
     int err_not_used;
     String *res;
     if (!(res= str_op(&str_value)))
@@ -953,11 +1040,11 @@ longlong Item_func_numhybrid::val_int()
 }
 
 
-my_decimal *Item_func_numhybrid::val_decimal(my_decimal *decimal_value)
+my_decimal *Item_func_hybrid_result_type::val_decimal(my_decimal *decimal_value)
 {
   my_decimal *val= decimal_value;
   DBUG_ASSERT(fixed == 1);
-  switch (hybrid_type) {
+  switch (cached_result_type) {
   case DECIMAL_RESULT:
     val= decimal_op(decimal_value);
     break;
@@ -975,6 +1062,19 @@ my_decimal *Item_func_numhybrid::val_decimal(my_decimal *decimal_value)
   }
   case STRING_RESULT:
   {
+    if (is_temporal_type(field_type()))
+    {
+      MYSQL_TIME ltime;
+      if (date_op(&ltime,
+                  field_type() == MYSQL_TYPE_TIME ? TIME_TIME_ONLY : 0))
+      {
+        my_decimal_set_zero(decimal_value);
+        null_value= 1;
+        return 0;
+      }
+      ltime.time_type= mysql_type_to_time_type(field_type());
+      return date2my_decimal(&ltime, decimal_value);
+    }
     String *res;
     if (!(res= str_op(&str_value)))
       return NULL;
@@ -992,6 +1092,63 @@ my_decimal *Item_func_numhybrid::val_decimal(my_decimal *decimal_value)
 }
 
 
+bool Item_func_hybrid_result_type::get_date(MYSQL_TIME *ltime,
+                                            ulonglong fuzzydate)
+{
+  DBUG_ASSERT(fixed == 1);
+  switch (cached_result_type) {
+  case DECIMAL_RESULT:
+  {
+    my_decimal value, *res;
+    if (!(res= decimal_op(&value)) ||
+        decimal_to_datetime_with_warn(res, ltime, fuzzydate,
+                                      field_name_or_null()))
+      goto err;
+    break;
+  }
+  case INT_RESULT:
+  {
+    longlong value= int_op();
+    if (null_value || int_to_datetime_with_warn(value, ltime, fuzzydate,
+                                                field_name_or_null()))
+      goto err;
+    break;
+  }
+  case REAL_RESULT:
+  {
+    double value= real_op();
+    if (null_value || double_to_datetime_with_warn(value, ltime, fuzzydate,
+                                                   field_name_or_null()))
+      goto err;
+    break;
+  }
+  case STRING_RESULT:
+  {
+    if (is_temporal_type(field_type()))
+      return date_op(ltime, fuzzydate);
+    char buff[40];
+    String tmp(buff,sizeof(buff), &my_charset_bin),*res;
+    if (!(res= str_op(&tmp)) ||
+        str_to_datetime_with_warn(res->charset(), res->ptr(), res->length(),
+                                  ltime, fuzzydate) <= MYSQL_TIMESTAMP_ERROR)
+      goto err;
+    break;
+     break;
+  }
+  case ROW_RESULT:
+  case TIME_RESULT:
+  case IMPOSSIBLE_RESULT:
+    DBUG_ASSERT(0);
+  }
+
+  return (null_value= 0);
+
+err:
+  bzero(ltime, sizeof(*ltime));
+  return null_value|= !(fuzzydate & TIME_FUZZY_DATES);  
+}
+
+
 void Item_func_signed::print(String *str, enum_query_type query_type)
 {
   str->append(STRING_WITH_LEN("cast("));
@@ -1688,7 +1845,7 @@ void Item_func_div::fix_length_and_dec()
   DBUG_ENTER("Item_func_div::fix_length_and_dec");
   prec_increment= current_thd->variables.div_precincrement;
   Item_num_op::fix_length_and_dec();
-  switch (hybrid_type) {
+  switch (cached_result_type) {
   case REAL_RESULT:
   {
     decimals=max(args[0]->decimals,args[1]->decimals)+prec_increment;
@@ -1704,7 +1861,7 @@ void Item_func_div::fix_length_and_dec()
     break;
   }
   case INT_RESULT:
-    hybrid_type= DECIMAL_RESULT;
+    cached_result_type= DECIMAL_RESULT;
     DBUG_PRINT("info", ("Type changed: DECIMAL_RESULT"));
     result_precision();
     break;
@@ -1956,7 +2113,7 @@ void Item_func_neg::fix_length_and_dec()
     Use val() to get value as arg_type doesn't mean that item is
     Item_int or Item_real due to existence of Item_param.
   */
-  if (hybrid_type == INT_RESULT && args[0]->const_item())
+  if (cached_result_type == INT_RESULT && args[0]->const_item())
   {
     longlong val= args[0]->val_int();
     if ((ulonglong) val >= (ulonglong) LONGLONG_MIN &&
@@ -1967,7 +2124,7 @@ void Item_func_neg::fix_length_and_dec()
         Ensure that result is converted to DECIMAL, as longlong can't hold
         the negated number
       */
-      hybrid_type= DECIMAL_RESULT;
+      cached_result_type= DECIMAL_RESULT;
       DBUG_PRINT("info", ("Type changed: DECIMAL_RESULT"));
     }
   }
@@ -2271,11 +2428,11 @@ void Item_func_int_val::find_num_type()
 {
   DBUG_ENTER("Item_func_int_val::find_num_type");
   DBUG_PRINT("info", ("name %s", func_name()));
-  switch (hybrid_type= args[0]->cast_to_int_type())
+  switch (cached_result_type= args[0]->cast_to_int_type())
   {
   case STRING_RESULT:
   case REAL_RESULT:
-    hybrid_type= REAL_RESULT;
+    cached_result_type= REAL_RESULT;
     max_length= float_length(decimals);
     break;
   case INT_RESULT:
@@ -2288,12 +2445,12 @@ void Item_func_int_val::find_num_type()
     if ((args[0]->max_length - args[0]->decimals) >=
         (DECIMAL_LONGLONG_DIGITS - 2))
     {
-      hybrid_type= DECIMAL_RESULT;
+      cached_result_type= DECIMAL_RESULT;
     }
     else
     {
       unsigned_flag= args[0]->unsigned_flag;
-      hybrid_type= INT_RESULT;
+      cached_result_type= INT_RESULT;
     }
     break;
   case ROW_RESULT:
@@ -2301,9 +2458,9 @@ void Item_func_int_val::find_num_type()
     DBUG_ASSERT(0);
   }
   DBUG_PRINT("info", ("Type: %s",
-                      (hybrid_type == REAL_RESULT ? "REAL_RESULT" :
-                       hybrid_type == DECIMAL_RESULT ? "DECIMAL_RESULT" :
-                       hybrid_type == INT_RESULT ? "INT_RESULT" :
+                      (cached_result_type == REAL_RESULT ? "REAL_RESULT" :
+                       cached_result_type == DECIMAL_RESULT ? "DECIMAL_RESULT" :
+                       cached_result_type == INT_RESULT ? "INT_RESULT" :
                        "--ILLEGAL!!!--")));
 
   DBUG_VOID_RETURN;
@@ -2418,10 +2575,10 @@ void Item_func_round::fix_length_and_dec()
     if (args[0]->result_type() == DECIMAL_RESULT)
     {
       max_length++;
-      hybrid_type= DECIMAL_RESULT;
+      cached_result_type= DECIMAL_RESULT;
     }
     else
-      hybrid_type= REAL_RESULT;
+      cached_result_type= REAL_RESULT;
     return;
   }
 
@@ -2439,14 +2596,14 @@ void Item_func_round::fix_length_and_dec()
   {
     decimals= min(decimals_to_set, NOT_FIXED_DEC);
     max_length= float_length(decimals);
-    hybrid_type= REAL_RESULT;
+    cached_result_type= REAL_RESULT;
     return;
   }
   
   switch (args[0]->result_type()) {
   case REAL_RESULT:
   case STRING_RESULT:
-    hybrid_type= REAL_RESULT;
+    cached_result_type= REAL_RESULT;
     decimals= min(decimals_to_set, NOT_FIXED_DEC);
     max_length= float_length(decimals);
     break;
@@ -2456,14 +2613,14 @@ void Item_func_round::fix_length_and_dec()
       int length_can_increase= test(!truncate && (val1 < 0) && !val1_unsigned);
       max_length= args[0]->max_length + length_can_increase;
       /* Here we can keep INT_RESULT */
-      hybrid_type= INT_RESULT;
+      cached_result_type= INT_RESULT;
       decimals= 0;
       break;
     }
     /* fall through */
   case DECIMAL_RESULT:
   {
-    hybrid_type= DECIMAL_RESULT;
+    cached_result_type= DECIMAL_RESULT;
     decimals_to_set= min(DECIMAL_MAX_SCALE, decimals_to_set);
     int decimals_delta= args[0]->decimals - decimals_to_set;
     int precision= args[0]->decimal_precision();
diff --git a/sql/item_func.h b/sql/item_func.h
index d177562c2a5..fe08473060c 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -149,13 +149,16 @@ public:
   void print_op(String *str, enum_query_type query_type);
   void print_args(String *str, uint from, enum_query_type query_type);
   virtual void fix_num_length_and_dec();
-  void count_only_length();
+  void count_only_length(Item **item, uint nitems);
   void count_real_length();
   void count_decimal_length();
   inline bool get_arg0_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
   {
     return (null_value=args[0]->get_date(ltime, fuzzy_date));
   }
+  void count_datetime_length(Item **item, uint nitems);
+  bool count_string_result_length(enum_field_types field_type,
+                                  Item **item, uint nitems);
   inline bool get_arg0_time(MYSQL_TIME *ltime)
   {
     return (null_value=args[0]->get_time(ltime));
@@ -410,38 +413,33 @@ public:
 };
 
 
-class Item_func_numhybrid: public Item_func
+class Item_func_hybrid_result_type: public Item_func
 {
 protected:
-  Item_result hybrid_type;
+  Item_result cached_result_type;
+
 public:
-  Item_func_numhybrid() :Item_func(), hybrid_type(REAL_RESULT)
-  {}
-  Item_func_numhybrid(Item *a) :Item_func(a), hybrid_type(REAL_RESULT)
+  Item_func_hybrid_result_type() :Item_func(), cached_result_type(REAL_RESULT)
   { collation.set_numeric(); }
-  Item_func_numhybrid(Item *a,Item *b)
-    :Item_func(a,b), hybrid_type(REAL_RESULT)
+  Item_func_hybrid_result_type(Item *a) :Item_func(a), cached_result_type(REAL_RESULT)
   { collation.set_numeric(); }
-  Item_func_numhybrid(List<Item> &list)
-    :Item_func(list), hybrid_type(REAL_RESULT)
+  Item_func_hybrid_result_type(Item *a,Item *b)
+    :Item_func(a,b), cached_result_type(REAL_RESULT)
+  { collation.set_numeric(); }
+  Item_func_hybrid_result_type(Item *a,Item *b,Item *c)
+    :Item_func(a,b,c), cached_result_type(REAL_RESULT)
+  { collation.set_numeric(); }
+  Item_func_hybrid_result_type(List<Item> &list)
+    :Item_func(list), cached_result_type(REAL_RESULT)
   { collation.set_numeric(); }
 
-  enum Item_result result_type () const { return hybrid_type; }
-  void fix_length_and_dec();
-  void fix_num_length_and_dec();
-  virtual void find_num_type()= 0; /* To be called from fix_length_and_dec */
-
-  inline void fix_decimals()
-  {
-    DBUG_ASSERT(result_type() == DECIMAL_RESULT);
-    if (decimals == NOT_FIXED_DEC)
-      set_if_smaller(decimals, max_length - 1);
-  }
+  enum Item_result result_type () const { return cached_result_type; }
 
   double val_real();
   longlong val_int();
   my_decimal *val_decimal(my_decimal *);
   String *val_str(String*str);
+  bool get_date(MYSQL_TIME *res, ulonglong fuzzy_date);
 
   /**
      @brief Performs the operation that this functions implements when the
@@ -478,9 +476,75 @@ public:
      @return The result of the operation.
   */
   virtual String *str_op(String *)= 0;
-  bool is_null() { update_null_value(); return null_value; }
+
+  /**
+     @brief Performs the operation that this functions implements when
+     field type is a temporal type.
+     @return The result of the operation.
+  */
+  virtual bool date_op(MYSQL_TIME *res, uint fuzzy_date)= 0;
+
+};
+
+
+
+class Item_func_hybrid_field_type :public Item_func_hybrid_result_type
+{
+protected:
+  enum_field_types cached_field_type;
+public:
+  Item_func_hybrid_field_type()
+    :Item_func_hybrid_result_type(), cached_field_type(MYSQL_TYPE_DOUBLE)
+  {}
+  Item_func_hybrid_field_type(Item *a, Item *b)
+    :Item_func_hybrid_result_type(a, b), cached_field_type(MYSQL_TYPE_DOUBLE)
+  {}
+  Item_func_hybrid_field_type(Item *a, Item *b, Item *c)
+    :Item_func_hybrid_result_type(a, b, c),
+    cached_field_type(MYSQL_TYPE_DOUBLE)
+  {}
+  Item_func_hybrid_field_type(List<Item> &list)
+    :Item_func_hybrid_result_type(list),
+    cached_field_type(MYSQL_TYPE_DOUBLE)
+  {}
+  enum_field_types field_type() const { return cached_field_type; }
+};
+
+
+
+class Item_func_numhybrid: public Item_func_hybrid_result_type
+{
+protected:
+
+  inline void fix_decimals()
+  {
+    DBUG_ASSERT(result_type() == DECIMAL_RESULT);
+    if (decimals == NOT_FIXED_DEC)
+      set_if_smaller(decimals, max_length - 1);
+  }
+
+public:
+  Item_func_numhybrid() :Item_func_hybrid_result_type()
+  { }
+  Item_func_numhybrid(Item *a) :Item_func_hybrid_result_type(a)
+  { }
+  Item_func_numhybrid(Item *a,Item *b)
+    :Item_func_hybrid_result_type(a,b)
+  { }
+  Item_func_numhybrid(Item *a,Item *b,Item *c)
+    :Item_func_hybrid_result_type(a,b,c)
+  { }
+  Item_func_numhybrid(List<Item> &list)
+    :Item_func_hybrid_result_type(list)
+  { }
+  void fix_length_and_dec();
+  void fix_num_length_and_dec();
+  virtual void find_num_type()= 0; /* To be called from fix_length_and_dec */
+  String *str_op(String *str) { DBUG_ASSERT(0); return 0; }
+  bool date_op(MYSQL_TIME *ltime, uint fuzzydate) { DBUG_ASSERT(0); return true; }
 };
 
+
 /* function where type of result detected by first argument */
 class Item_func_num1: public Item_func_numhybrid
 {
@@ -490,7 +554,6 @@ public:
 
   void fix_num_length_and_dec();
   void find_num_type();
-  String *str_op(String *str) { DBUG_ASSERT(0); return 0; }
 };
 
 
@@ -507,7 +570,6 @@ class Item_num_op :public Item_func_numhybrid
   }
 
   void find_num_type();
-  String *str_op(String *str) { DBUG_ASSERT(0); return 0; }
 };
 
 
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index c8aaf9a7cb3..4e2b07fee08 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1435,12 +1435,8 @@ bool get_interval_value(Item *args,interval_type int_type, INTERVAL *interval)
 
 void Item_temporal_func::fix_length_and_dec()
 { 
-  static const uint max_time_type_width[5]=
-  { MAX_DATETIME_WIDTH, MAX_DATETIME_WIDTH, MAX_DATE_WIDTH,
-    MAX_DATETIME_WIDTH, MIN_TIME_WIDTH };
-
   set_persist_maybe_null(1);
-  max_length= max_time_type_width[mysql_type_to_time_type(field_type())+2];
+  max_length= mysql_temporal_int_part_length(field_type());
   if (decimals)
   {
     if (decimals == NOT_FIXED_DEC)
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 8bea068357b..8f7b65b253a 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -30,19 +30,17 @@ enum date_time_format_types
   TIME_ONLY= 0, TIME_MICROSECOND, DATE_ONLY, DATE_TIME, DATE_TIME_MICROSECOND
 };
 
-static inline enum enum_mysql_timestamp_type
-mysql_type_to_time_type(enum enum_field_types mysql_type)
+
+static inline uint
+mysql_temporal_int_part_length(enum enum_field_types mysql_type)
 {
-  switch(mysql_type) {
-  case MYSQL_TYPE_TIME: return MYSQL_TIMESTAMP_TIME;
-  case MYSQL_TYPE_TIMESTAMP:
-  case MYSQL_TYPE_DATETIME: return MYSQL_TIMESTAMP_DATETIME;
-  case MYSQL_TYPE_NEWDATE:
-  case MYSQL_TYPE_DATE: return MYSQL_TIMESTAMP_DATE;
-  default: return MYSQL_TIMESTAMP_ERROR;
-  }
+  static uint max_time_type_width[5]=
+  { MAX_DATETIME_WIDTH, MAX_DATETIME_WIDTH, MAX_DATE_WIDTH,
+    MAX_DATETIME_WIDTH, MIN_TIME_WIDTH };
+  return max_time_type_width[mysql_type_to_time_type(mysql_type)+2];
 }
 
+
 bool get_interval_value(Item *args,interval_type int_type, INTERVAL *interval);
 
 class Item_func_period_add :public Item_int_func
@@ -421,9 +419,11 @@ public:
     max_length=17 + (decimals ? decimals + 1 : 0);
     set_persist_maybe_null(1);
   }
-  void find_num_type() { hybrid_type= decimals ? DECIMAL_RESULT : INT_RESULT; }
+  void find_num_type()
+  { cached_result_type= decimals ? DECIMAL_RESULT : INT_RESULT; }
   double real_op() { DBUG_ASSERT(0); return 0; }
   String *str_op(String *str) { DBUG_ASSERT(0); return 0; }
+  bool date_op(MYSQL_TIME *ltime, uint fuzzydate) { DBUG_ASSERT(0); return true; }
 };