Task #835: additional changes fot str_to_date

include/mysqld_error.h:
  Task #835: additional changes fot str_to_date
  New error message
mysql-test/r/date_formats.result:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/r/func_sapdb.result:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/r/func_time.result:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/r/type_time.result:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/t/date_formats.test:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/t/func_sapdb.test:
  Task #835: additional changes fot str_to_date
  tests
mysql-test/t/func_time.test:
  Task #835: additional changes fot str_to_date
  tests
sql/share/czech/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/danish/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/dutch/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/english/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/estonian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/french/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/german/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/greek/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/hungarian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/italian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/japanese/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/korean/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/norwegian-ny/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/norwegian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/polish/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/portuguese/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/romanian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/russian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/serbian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/slovak/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/spanish/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/swedish/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message
sql/share/ukrainian/errmsg.txt:
  Task #835: additional changes fot str_to_date
  New error message

1 files changed, 220 insertions, 67 deletions

diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 32fbe192d8f..d0674411fcf 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -48,11 +48,6 @@ TYPELIB day_names_typelib=
 { array_elements(day_names)-1,"", day_names};
 
 
-enum date_time_format_types 
-{ 
-  TIME_ONLY= 0, TIME_MICROSECOND, DATE_ONLY, DATE_TIME, DATE_TIME_MICROSECOND
-};
-
 /*
   OPTIMIZATION TODO:
    - Replace the switch with a function that should be called for each
@@ -128,6 +123,9 @@ static bool make_datetime(date_time_format_types format, TIME *ltime,
     val			String to decode
     length		Length of string
     l_time		Store result here
+    cached_timestamp_type 
+                       It uses to get an appropriate warning
+                       in the case when the value is truncated.
 
     RETURN
       0	ok
@@ -135,7 +133,8 @@ static bool make_datetime(date_time_format_types format, TIME *ltime,
 */
 
 static bool extract_date_time(DATE_TIME_FORMAT *format,
-			      const char *val, uint length, TIME *l_time)
+			      const char *val, uint length, TIME *l_time,
+			      timestamp_type cached_timestamp_type)
 {
   int weekday= 0, yearday= 0, daypart= 0;
   int week_number= -1;
@@ -143,9 +142,11 @@ static bool extract_date_time(DATE_TIME_FORMAT *format,
   int error= 0;
   bool usa_time= 0;
   bool sunday_first= 0;
+  int frac_part;
+  const char *val_begin= val;
   const char *val_end= val + length;
   const char *ptr= format->format.str;
-  const char *end= ptr+ format->format.length;
+  const char *end= ptr + format->format.length;
   DBUG_ENTER("extract_date_time");
 
   bzero((char*) l_time, sizeof(*l_time));
@@ -235,7 +236,12 @@ static bool extract_date_time(DATE_TIME_FORMAT *format,
 	/* Second part */
       case 'f':
 	tmp= (char*) val_end;
+	if (tmp - val > 6)
+	  tmp= (char*) val + 6;
 	l_time->second_part= (int) my_strtoll10(val, &tmp, &error);
+	frac_part= 6 - (tmp - val);
+	if (frac_part > 0)
+	  l_time->second_part*= (ulong) log_10_int[frac_part];
 	val= tmp;
 	break;
 
@@ -251,6 +257,7 @@ static bool extract_date_time(DATE_TIME_FORMAT *format,
 			      (const uchar *) val, 2, 
 			      (const uchar *) "AM", 2))
 	  goto err;
+	val+= 2;
 	break;
 
 	/* Exotic things */
@@ -281,6 +288,18 @@ static bool extract_date_time(DATE_TIME_FORMAT *format,
 	val= tmp;
 	break;
 
+      case '.':
+	while (my_ispunct(cs, *val) && val != val_end)
+	  val++;
+	break;
+      case '@':
+	while (my_isalpha(cs, *val) && val != val_end)
+	  val++;
+	break;
+      case '#':
+	while (my_isdigit(cs, *val) && val != val_end)
+	  val++;
+	break;
       default:
 	goto err;
       }
@@ -348,6 +367,18 @@ static bool extract_date_time(DATE_TIME_FORMAT *format,
       l_time->minute > 59 || l_time->second > 59)
     goto err;
 
+  if (val != val_end)
+  {
+    do
+    {
+      if (!my_isspace(&my_charset_latin1,*val))
+      {
+	make_truncated_value_warning(current_thd, val_begin, length,
+				     cached_timestamp_type);
+	break;
+      }
+    } while (++val != val_end);
+  }
   DBUG_RETURN(0);
 
 err:
@@ -584,16 +615,27 @@ bool make_date_time(DATE_TIME_FORMAT *format, TIME *l_time,
 
 
 /*
-** Get a array of positive numbers from a string object.
-** Each number is separated by 1 non digit character
-** Return error if there is too many numbers.
-** If there is too few numbers, assume that the numbers are left out
-** from the high end. This allows one to give:
-** DAY_TO_SECOND as "D MM:HH:SS", "MM:HH:SS" "HH:SS" or as seconds.
+  Get a array of positive numbers from a string object.
+  Each number is separated by 1 non digit character
+  Return error if there is too many numbers.
+  If there is too few numbers, assume that the numbers are left out
+  from the high end. This allows one to give:
+  DAY_TO_SECOND as "D MM:HH:SS", "MM:HH:SS" "HH:SS" or as seconds.
+
+  SYNOPSIS
+    str:            string value
+    length:         length of str
+    cs:             charset of str
+    values:         array of results
+    count:          count of elements in result array
+    transform_msec: if value is true we suppose
+                    that the last part of string value is microseconds
+                    and we should transform value to six digit value.
+                    For example, '1.1' -> '1.100000'
 */
 
 bool get_interval_info(const char *str,uint length,CHARSET_INFO *cs,
-			uint count, long *values)
+                       uint count, long *values, bool transform_msec)
 {
   const char *end=str+length;
   uint i;
@@ -603,8 +645,15 @@ bool get_interval_info(const char *str,uint length,CHARSET_INFO *cs,
   for (i=0 ; i < count ; i++)
   {
     long value;
+    const char *start= str;
     for (value=0; str != end && my_isdigit(cs,*str) ; str++)
       value=value*10L + (long) (*str - '0');
+    if (transform_msec && i == count - 1) // microseconds always last
+    {
+      long msec_length= 6 - (str - start);
+      if (msec_length > 0)
+	value*= (long) log_10_int[msec_length];
+    }
     values[i]= value;
     while (str != end && !my_isdigit(cs,*str))
       str++;
@@ -925,19 +974,19 @@ static bool get_interval_value(Item *args,interval_type int_type,
     interval->second=value;
     break;
   case INTERVAL_YEAR_MONTH:			// Allow YEAR-MONTH YYYYYMM
-    if (get_interval_info(str,length,cs,2,array))
+    if (get_interval_info(str,length,cs,2,array,0))
       return (1);
     interval->year=array[0];
     interval->month=array[1];
     break;
   case INTERVAL_DAY_HOUR:
-    if (get_interval_info(str,length,cs,2,array))
+    if (get_interval_info(str,length,cs,2,array,0))
       return (1);
     interval->day=array[0];
     interval->hour=array[1];
     break;
   case INTERVAL_DAY_MICROSECOND:
-    if (get_interval_info(str,length,cs,5,array))
+    if (get_interval_info(str,length,cs,5,array,1))
       return (1);
     interval->day=array[0];
     interval->hour=array[1];
@@ -946,14 +995,14 @@ static bool get_interval_value(Item *args,interval_type int_type,
     interval->second_part=array[4];
     break;
   case INTERVAL_DAY_MINUTE:
-    if (get_interval_info(str,length,cs,3,array))
+    if (get_interval_info(str,length,cs,3,array,0))
       return (1);
     interval->day=array[0];
     interval->hour=array[1];
     interval->minute=array[2];
     break;
   case INTERVAL_DAY_SECOND:
-    if (get_interval_info(str,length,cs,4,array))
+    if (get_interval_info(str,length,cs,4,array,0))
       return (1);
     interval->day=array[0];
     interval->hour=array[1];
@@ -961,7 +1010,7 @@ static bool get_interval_value(Item *args,interval_type int_type,
     interval->second=array[3];
     break;
   case INTERVAL_HOUR_MICROSECOND:
-    if (get_interval_info(str,length,cs,4,array))
+    if (get_interval_info(str,length,cs,4,array,1))
       return (1);
     interval->hour=array[0];
     interval->minute=array[1];
@@ -969,33 +1018,33 @@ static bool get_interval_value(Item *args,interval_type int_type,
     interval->second_part=array[3];
     break;
   case INTERVAL_HOUR_MINUTE:
-    if (get_interval_info(str,length,cs,2,array))
+    if (get_interval_info(str,length,cs,2,array,0))
       return (1);
     interval->hour=array[0];
     interval->minute=array[1];
     break;
   case INTERVAL_HOUR_SECOND:
-    if (get_interval_info(str,length,cs,3,array))
+    if (get_interval_info(str,length,cs,3,array,0))
       return (1);
     interval->hour=array[0];
     interval->minute=array[1];
     interval->second=array[2];
     break;
   case INTERVAL_MINUTE_MICROSECOND:
-    if (get_interval_info(str,length,cs,3,array))
+    if (get_interval_info(str,length,cs,3,array,1))
       return (1);
     interval->minute=array[0];
     interval->second=array[1];
     interval->second_part=array[2];
     break;
   case INTERVAL_MINUTE_SECOND:
-    if (get_interval_info(str,length,cs,2,array))
+    if (get_interval_info(str,length,cs,2,array,0))
       return (1);
     interval->minute=array[0];
     interval->second=array[1];
     break;
   case INTERVAL_SECOND_MICROSECOND:
-    if (get_interval_info(str,length,cs,2,array))
+    if (get_interval_info(str,length,cs,2,array,1))
       return (1);
     interval->second=array[0];
     interval->second_part=array[1];
@@ -1008,22 +1057,13 @@ static bool get_interval_value(Item *args,interval_type int_type,
 String *Item_date::val_str(String *str)
 {
   TIME ltime;
-  ulong value=(ulong) val_int();
-  if (null_value)
-    return (String*) 0;
-
+  if (get_date(&ltime, TIME_FUZZY_DATE))
+    return (String *) 0;
   if (str->alloc(11))
   {
     null_value= 1;
     return (String *) 0;
   }
-
-  ltime.year=	(value/10000L) % 10000;
-  ltime.month=	(value/100)%100;
-  ltime.day=	(value%100);
-  ltime.neg= 0;
-  ltime.time_type=TIMESTAMP_DATE;
-
   make_date((DATE_TIME_FORMAT *) 0, &ltime, str);
   return str;
 }
@@ -1032,28 +1072,31 @@ String *Item_date::val_str(String *str)
 int Item_date::save_in_field(Field *field, bool no_conversions)
 {
   TIME ltime;
-  timestamp_type t_type=TIMESTAMP_DATETIME;
   if (get_date(&ltime, TIME_FUZZY_DATE))
-  {
-    if (null_value)
-      return set_field_to_null(field);
-    t_type=TIMESTAMP_NONE;			// Error
-  }
+    return set_field_to_null(field);
   field->set_notnull();
-  field->store_time(&ltime,t_type);
+  field->store_time(&ltime, TIMESTAMP_DATE);
   return 0;
 }
 
 
-longlong Item_func_from_days::val_int()
+longlong Item_date::val_int()
+{
+  TIME ltime;
+  if (get_date(&ltime, TIME_FUZZY_DATE))
+    return 0;
+  return (longlong) (ltime.year*10000L+ltime.month*100+ltime.day);
+}
+
+
+bool Item_func_from_days::get_date(TIME *ltime, uint fuzzy_date)
 {
   longlong value=args[0]->val_int();
   if ((null_value=args[0]->null_value))
-    return 0; /* purecov: inspected */
-
-  uint year,month,day;
-  get_date_from_daynr((long) value,&year,&month,&day);
-  return (longlong) (year*10000L+month*100+day);
+    return 1;
+  get_date_from_daynr((long) value, &ltime->year, &ltime->month, &ltime->day);
+  ltime->time_type= TIMESTAMP_DATE;
+  return 0;
 }
 
 
@@ -1082,6 +1125,16 @@ void Item_func_curdate::fix_length_and_dec()
   ltime.time_type=TIMESTAMP_DATE;
 }
 
+String *Item_func_curdate::val_str(String *str)
+{
+  if (str->alloc(11))
+  {
+    null_value= 1;
+    return (String *) 0;
+  }
+  make_date((DATE_TIME_FORMAT *) 0, &ltime, str);
+  return str;
+}
 
 bool Item_func_curdate::get_date(TIME *res,
 				 uint fuzzy_date __attribute__((unused)))
@@ -2311,6 +2364,103 @@ void Item_func_get_format::print(String *str)
 }
 
 
+/*
+  check_result_type(s, l) returns DATE/TIME type
+  according to format string
+
+  s: DATE/TIME format string
+  l: length of s
+  Result: date_time_format_types value:
+          DATE_TIME_MICROSECOND, DATE_TIME,
+          TIME_MICROSECOND, TIME_ONLY
+
+  We don't process day format's characters('D', 'd', 'e')
+  because day may be a member of all date/time types.
+  If only day format's character and no time part present
+  the result type is MYSQL_TYPE_DATE
+*/
+
+date_time_format_types  check_result_type(const char *format, uint length)
+{
+  const char *time_part_frms= "HISThiklrs";
+  const char *date_part_frms= "MUYWabcjmuyw";
+  bool date_part_used= 0, time_part_used= 0, frac_second_used= 0;
+  
+  const char *val= format;
+  const char *end= format + length;
+
+  for (; val != end && val != end; val++)
+  {
+    if (*val == '%' && val+1 != end)
+    {
+      val++;
+      if ((frac_second_used= (*val == 'f')) ||
+	  (!time_part_used && strchr(time_part_frms, *val)))
+	time_part_used= 1;
+      else if (!date_part_used && strchr(date_part_frms, *val))
+	date_part_used= 1;
+      if (time_part_used && date_part_used && frac_second_used)
+	return DATE_TIME_MICROSECOND;
+    }
+  }
+
+  if (time_part_used)
+  {
+    if (date_part_used)
+      return DATE_TIME;
+    if (frac_second_used)
+      return TIME_MICROSECOND;
+    return TIME_ONLY;
+  }
+  return DATE_ONLY;
+}
+
+
+Field *Item_func_str_to_date::tmp_table_field(TABLE *t_arg)
+{
+  if (cached_field_type == MYSQL_TYPE_TIME)
+    return (new Field_time(maybe_null, name, t_arg, &my_charset_bin));
+  if (cached_field_type == MYSQL_TYPE_DATE)
+    return (new Field_date(maybe_null, name, t_arg, &my_charset_bin));
+  if (cached_field_type == MYSQL_TYPE_DATETIME)
+    return (new Field_datetime(maybe_null, name, t_arg, &my_charset_bin));
+  return (new Field_string(max_length, maybe_null, name, t_arg, &my_charset_bin));
+}
+
+
+void Item_func_str_to_date::fix_length_and_dec()
+{
+  char format_buff[64];
+  String format_str(format_buff, sizeof(format_buff), &my_charset_bin), *format;
+  maybe_null= 1;
+  decimals=0;
+  cached_field_type= MYSQL_TYPE_STRING;
+  max_length= MAX_DATETIME_FULL_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+  cached_timestamp_type= TIMESTAMP_NONE;
+  if ((const_item= args[1]->const_item()))
+  {
+    format= args[1]->val_str(&format_str);
+    cached_format_type= check_result_type(format->ptr(), format->length());
+    switch (cached_format_type) {
+    case DATE_ONLY:
+      cached_timestamp_type= TIMESTAMP_DATE;
+      cached_field_type= MYSQL_TYPE_DATE; 
+      max_length= MAX_DATE_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+      break;
+    case TIME_ONLY:
+    case TIME_MICROSECOND:
+      cached_timestamp_type= TIMESTAMP_TIME;
+      cached_field_type= MYSQL_TYPE_TIME; 
+      max_length= MAX_TIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+      break;
+    default:
+      cached_timestamp_type= TIMESTAMP_DATETIME;
+      cached_field_type= MYSQL_TYPE_DATETIME; 
+      break;
+    }
+  }
+}
+
 bool Item_func_str_to_date::get_date(TIME *ltime, uint fuzzy_date)
 {
   DATE_TIME_FORMAT date_time_format;
@@ -2328,8 +2478,18 @@ bool Item_func_str_to_date::get_date(TIME *ltime, uint fuzzy_date)
   date_time_format.format.str=    (char*) format->ptr();
   date_time_format.format.length= format->length();
   if (extract_date_time(&date_time_format, val->ptr(), val->length(),
-			ltime))
+			ltime, cached_timestamp_type))
     goto null_date;
+  if (cached_timestamp_type == TIMESTAMP_TIME && ltime->day)
+  {
+    /*
+      Day part for time type can be nonzero value and so 
+      we should add hours from day part to hour part to
+      keep valid time value.
+    */
+    ltime->hour+= ltime->day*24;
+    ltime->day= 0;
+  }
   return 0;
 
 null_date:
@@ -2344,29 +2504,22 @@ String *Item_func_str_to_date::val_str(String *str)
   if (Item_func_str_to_date::get_date(&ltime, TIME_FUZZY_DATE))
     return 0;
 
-  /*
-    The following DATE_TIME should be done dynamicly based on the
-    format string (wen it's a constant). For example, we should only return
-    microseconds if there was an %f in the format
-  */
-  if (!make_datetime(ltime.second_part ? DATE_TIME_MICROSECOND : DATE_TIME,
+  if (!make_datetime((const_item ? cached_format_type :
+		     (ltime.second_part ? DATE_TIME_MICROSECOND : DATE_TIME)),
 		     &ltime, str))
     return str;
   return 0;
 }
 
 
-String *Item_func_last_day::val_str(String *str)
+bool Item_func_last_day::get_date(TIME *ltime, uint fuzzy_date)
 {
-  TIME ltime;
-  if (!get_arg0_date(&ltime,0))
-  {
-    uint month_idx= ltime.month-1;
-    ltime.day= days_in_month[month_idx];
-    if ( month_idx == 1 && calc_days_in_year(ltime.year) == 366)
-      ltime.day+= 1;
-    if (!make_datetime(DATE_ONLY, &ltime, str))
-      return str;      
-  }
+  if (get_arg0_date(ltime,fuzzy_date))
+    return 1;
+  uint month_idx= ltime->month-1;
+  ltime->day= days_in_month[month_idx];
+  if ( month_idx == 1 && calc_days_in_year(ltime->year) == 366)
+    ltime->day= 29;
+  ltime->time_type= TIMESTAMP_DATE;
   return 0;
 }