wl#173 - temporal types with sub-second resolution

         and collateral changes.

* introduce my_hrtime_t, my_timediff_t, and conversion macros
* inroduce TIME_RESULT, but it can only be returned from Item::cmp_type(),
  never from Item::result_type()
* pack_time/unpack_time function for "packed" representation of
  MYSQL_TIME in a longlong that can be compared
* ADDTIME()/SUBTIME()/+- INTERVAL now work with TIME values
* numbers aren't quoted in EXPLAIN EXTENDED
* new column I_S.COLUMNS.DATETIME_PRECISION
* date/time values are compares to anything as date/time, not as strings or numbers.
* old timestamp(X) is no longer supported
* MYSQL_TIME to string conversion functions take precision as an argument
* unified the warnings from Field_timestamp/datetime/time/date/newdate store methods
* Field_timestamp_hires, Field_datetime_hires, Field_time_hires
* Field_temporal
* Lazy_string class to pass a value (string, number, time) polymorphically down the stack
* make_truncated_value_warning and Field::set_datetime_warning use Lazy_string as an argument, removed char*/int/double variants
* removed Field::can_be_compared_as_longlong(). Use Field::cmp_type() == INT_RESULT instead
* introduced Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong()
* in many cases date/time types are treated like other types, not as special cases
* greatly simplified Arg_comparator (regarding date/time/year code)
* SEC_TO_TIME is real function, not integer.
* microsecond precision in NOW, CURTIME, etc
* Item_temporal. All items derived from it only provide get_date, but no val* methods
* replication of NOW(6)
* Protocol::store(time) now takes the precision as an argument
* @@TIMESTAMP is a double

client/mysqlbinlog.cc:
  remove unneded casts
include/my_sys.h:
  introduce my_hrtime_t, my_timediff_t, and conversion macros
include/my_time.h:
  pack_time/unpack_time, etc.
  convenience functions to work with MYSQL_TIME::second_part
libmysql/libmysql.c:
  str_to_time() is gone. str_to_datetime() does it now.
  my_TIME_to_str() takes the precision as an argument
mysql-test/include/ps_conv.inc:
  time is not equal to datetime anymore
mysql-test/r/distinct.result:
  a test for an old MySQL bug
mysql-test/r/explain.result:
  numbers aren't quoted in EXPLAIN EXTENDED
mysql-test/r/func_default.result:
  numbers aren't quoted in EXPLAIN EXTENDED
mysql-test/r/func_sapdb.result:
  when decimals=NOT_FIXED_DEC it means "not fixed" indeed
mysql-test/r/func_test.result:
  numbers aren't quoted in EXPLAIN EXTENDED
mysql-test/r/func_time.result:
  ADDTIME()/SUBTIME()/+- INTERVAL now work with TIME values
mysql-test/r/having.result:
  numbers aren't quoted in EXPLAIN EXTENDED
mysql-test/r/information_schema.result:
  new column I_S.COLUMNS.DATETIME_PRECISION
mysql-test/r/join_outer.result:
  numbers aren't quoted in EXPLAIN EXTENDED
mysql-test/r/metadata.result:
  TIMESTAMP no longer has zerofill flag
mysql-test/r/range.result:
  invalid datetime is not compared with as a string
mysql-test/r/select.result:
  NO_ZERO_IN_DATE, etc only affect storage - according to the manual
  numbers aren't quoted in EXPLAIN EXTENDED
mysql-test/r/subselect.result:
  numbers aren't quoted in EXPLAIN EXTENDED
mysql-test/r/sysdate_is_now.result:
  when decimals=NOT_FIXED_DEC it means "not fixed" indeed
mysql-test/r/type_blob.result:
  TIMESTAMP(N) is not deprecated
mysql-test/r/type_timestamp.result:
  old TIMESTAMP(X) semantics is not supported anymore
mysql-test/r/union.result:
  numbers aren't quoted in EXPLAIN EXTENDED
mysql-test/r/varbinary.result:
  numbers aren't quoted in EXPLAIN EXTENDED
mysql-test/t/distinct.test:
  test for an old MySQL bug
mysql-test/t/func_time.test:
  +- INTERVAL now works with TIME values
mysql-test/t/select.test:
  typo
mysql-test/t/subselect.test:
  only one error per statement, please
mysql-test/t/system_mysql_db_fix40123.test:
  old timestamp(X) is no longer supported
mysql-test/t/system_mysql_db_fix50030.test:
  old timestamp(X) is no longer supported
mysql-test/t/system_mysql_db_fix50117.test:
  old timestamp(X) is no longer supported
mysql-test/t/type_blob.test:
  old timestamp(X) is no longer supported
mysql-test/t/type_timestamp.test:
  old timestamp(X) is no longer supported
mysys/my_getsystime.c:
  functions to get the time with microsecond precision
mysys/my_init.c:
  move the my_getsystime.c initialization code to my_getsystime.c
mysys/my_static.c:
  no need to make these variables extern
mysys/my_static.h:
  no need to make these variables extern
scripts/mysql_system_tables.sql:
  old timestamp(X) is no longer supported
scripts/mysql_system_tables_fix.sql:
  old timestamp(X) is no longer supported
scripts/mysqlhotcopy.sh:
  old timestamp(X) is no longer supported
sql-common/my_time.c:
  * call str_to_time from str_to_datetime, as appropriate
  * date/time to string conversions take precision as an argument
  * number_to_time()
  * TIME_to_double()
  * pack_time() and unpack_time()
sql/event_data_objects.cc:
  cast is not needed
  my_datetime_to_str() takes precision as an argument
sql/event_db_repository.cc:
  avoid dangerous downcast (because the pointer is
  not always Field_timestamp, see events_1.test)
sql/event_queue.cc:
  avoid silly double-work for cond_wait
  (having an endpoint of wait, subtract the current time to get the timeout,
  and use set_timespec() macro to fill in struct timespec, by adding the current
  time to the timeout)
sql/field.cc:
  * remove virtual Field::get_time(), everyone should use only Field::get_date()
  * remove lots of #ifdef WORDS_BIGENDIAN
  * unified the warnings from Field_timestamp/datetime/time/date/newdate store methods
  * Field_timestamp_hires, Field_datetime_hires, Field_time_hires
  * Field_temporal
  * make_truncated_value_warning and Field::set_datetime_warning use Lazy_string as an argument, removed char*/int/double variants
sql/field.h:
  * remove virtual Field::get_time(), everyone should use only Field::get_date()
  * remove lots of #ifdef WORDS_BIGENDIAN
  * unified the warnings from Field_timestamp/datetime/time/date/newdate store methods
  * Field_timestamp_hires, Field_datetime_hires, Field_time_hires
  * Field_temporal
  * make_truncated_value_warning and Field::set_datetime_warning use Lazy_string as an argument, removed char*/int/double variants
  * removed Field::can_be_compared_as_longlong(). Use Field::cmp_type() == INT_RESULT instead
sql/filesort.cc:
  TIME_RESULT, cmp_time()
sql/item.cc:
  * numbers aren't quoted in EXPLAIN EXTENDED
  * Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong()
  * virtual Item::get_time() is gone
  * Item_param::field_type() is set correctly
  * Item_datetime, for a datetime constant
  * time to anything is compared as a time
  * Item_cache::print() prints the value is available
  * bug fixed in Item_cache_int::val_str()
sql/item.h:
  * Item::print_value(), to be used from Item_xxx::print() when needed
  * Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong()
  * virtual Item::get_time() is gone
  * Item_datetime, for a datetime constant
  * better default for cast_to_int_type()
  * Item_cache objects now *always* have the field_type() set
sql/item_cmpfunc.cc:
  * get_year_value, get_time_value are gone. get_datetime_value does it all
  * get_value_a_func, get_value_b_func are gone
  * can_compare_as_dates() is gone too, TIME_RESULT is used instead
  * cmp_type() instead or result_type() when doing a comparison
  * compare_datetime and compate_e_datetime in the comparator_matrix, is_nulls_eq is gone
  * Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong()
sql/item_cmpfunc.h:
  greatly simplified Arg_comparator
sql/item_create.cc:
  * fix a bug in error messages in CAST
sql/item_func.cc:
  Item::cmp_result() instead of Item::is_datetime() and Item::result_as_longlong()
  mention all possibitiles in switch over Item_result values, or use default:
sql/item_row.h:
  overwrite the default cmp_type() for Item_row,
  as no MYSQL_TYPE_xxx value corresponds to ROW_RESULT
sql/item_timefunc.cc:
  rewrite make_datetime to support precision argument
  SEC_TO_TIME is real function, not integer.
  many functions that returned temporal values had duplicate code in val_* methods,
  some of them did not have get_date() which resulted in unnecessary date->str->date conversions. 
  Now they all are derived from Item_temporal_func and *only* provide get_date, not val* methods.
  many fixes to set decimals (datetime precision) correctly.
sql/item_timefunc.h:
  SEC_TO_TIME is real function, not integer.
  many functions that returned temporal values had duplicate code in val_* methods,
  some of them did not have get_date() which resulted in unnecessary date->str->date conversions. 
  Now they all are derived from Item_temporal_func and *only* provide get_date, not val* methods.
  many fixes to set decimals (datetime precision) correctly.
sql/log_event.cc:
  replication of NOW(6)
sql/log_event.h:
  replication of NOW(6)
sql/mysql_priv.h:
  Lazy_string class to pass a value (string, number, time) polymorphically down the stack.
  make_truncated_value_warning() that uses it.
sql/mysqld.cc:
  datetime in Arg_comparator::comparator_matrix
sql/opt_range.cc:
  cleanup: don't disable warnings before calling save_in_field_no_warnings()
sql/protocol.cc:
  Protocol::store(time) now takes the precision as an argument
sql/protocol.h:
  Protocol::store(time) now takes the precision as an argument
sql/rpl_rli.cc:
  small cleanup
sql/set_var.cc:
  SET TIMESTAMP=double
sql/set_var.h:
  @@TIMESTAMP is a double
sql/share/errmsg.txt:
  precision and scale are unsigned
sql/slave.cc:
  replication of NOW(6)
sql/sp_head.cc:
  cleanup
sql/sql_class.cc:
  support for NOW(6)
sql/sql_class.h:
  support for NOW(6)
sql/sql_insert.cc:
  support for NOW(6)
sql/sql_select.cc:
  use item->cmp_type().
  move a comment where it belongs
sql/sql_show.cc:
  new column I_S.COLUMNS.DATETIME_PRECISION
sql/sql_yacc.yy:
  TIME(X), DATETIME(X), cast, NOW(X), CURTIME(X), etc
sql/time.cc:
  fix date_add_interval() to support MYSQL_TIMESTAMP_TIME argument
storage/myisam/ha_myisam.cc:
  TIMESTAMP no longer carries ZEROFIELD flag, still we keep MYI file compatible.
strings/my_vsnprintf.c:
  warnings
tests/mysql_client_test.c:
  old timestamp(X) does not work anymore
  datetime is no longer equal to time

1 files changed, 135 insertions, 25 deletions

diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index c4e917801d5..ae1e4b7aa89 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -19,6 +19,9 @@
 /* Windows version of localtime_r() is declared in my_ptrhead.h */
 #include <my_pthread.h>
 
+static enum enum_mysql_timestamp_type str_to_time(const char *, uint,
+                                                  MYSQL_TIME *, int *);
+
 ulonglong log_10_int[20]=
 {
   1, 10, 100, 1000, 10000UL, 100000UL, 1000000UL, 10000000UL,
@@ -175,6 +178,13 @@ str_to_datetime(const char *str, uint length, MYSQL_TIME *l_time,
 
   LINT_INIT(field_length);
 
+  if (flags & TIME_TIME_ONLY)
+  {
+    enum enum_mysql_timestamp_type ret=
+      str_to_time(str, length, l_time, was_cut);
+    DBUG_RETURN(ret);
+  }
+
   *was_cut= 0;
 
   /* Skip space at start */
@@ -477,12 +487,12 @@ err:
      work with times where the time arguments are in the above order.
 
    RETURN
-     0  ok
-     1  error
+     MYSQL_TIMESTAMP_TIME
+     MYSQL_TIMESTAMP_ERROR
 */
 
-my_bool str_to_time(const char *str, uint length, MYSQL_TIME *l_time,
-                    int *warning)
+static enum enum_mysql_timestamp_type
+str_to_time(const char *str, uint length, MYSQL_TIME *l_time, int *warning)
 {
   ulong date[5];
   ulonglong value;
@@ -501,7 +511,7 @@ my_bool str_to_time(const char *str, uint length, MYSQL_TIME *l_time,
     length--;
   }
   if (str == end)
-    return 1;
+    return MYSQL_TIMESTAMP_ERROR;
 
   /* Check first if this is a full TIMESTAMP */
   if (length >= 12)
@@ -514,7 +524,7 @@ my_bool str_to_time(const char *str, uint length, MYSQL_TIME *l_time,
     {
       if (was_cut)
         *warning|= MYSQL_TIME_WARN_TRUNCATED;
-      return res == MYSQL_TIMESTAMP_ERROR;
+      return res;
     }
   }
 
@@ -609,7 +619,7 @@ fractional:
        ((str[1] == '-' || str[1] == '+') &&
         (end - str) > 2 &&
         my_isdigit(&my_charset_latin1, str[2]))))
-    return 1;
+    return MYSQL_TIMESTAMP_ERROR;
 
   if (internal_format_positions[7] != 255)
   {
@@ -632,7 +642,7 @@ fractional:
   if (date[0] > UINT_MAX || date[1] > UINT_MAX ||
       date[2] > UINT_MAX || date[3] > UINT_MAX ||
       date[4] > UINT_MAX)
-    return 1;
+    return MYSQL_TIMESTAMP_ERROR;
   
   l_time->year=         0;                      /* For protocol::store_time */
   l_time->month=        0;
@@ -645,7 +655,7 @@ fractional:
 
   /* Check if the value is valid and fits into MYSQL_TIME range */
   if (check_time_range(l_time, warning))
-    return 1;
+    return MYSQL_TIMESTAMP_ERROR;
   
   /* Check if there is garbage at end of the MYSQL_TIME specification */
   if (str != end)
@@ -659,7 +669,7 @@ fractional:
       }
     } while (++str != end);
   }
-  return 0;
+  return MYSQL_TIMESTAMP_TIME;
 }
 
 
@@ -698,7 +708,7 @@ int check_time_range(struct st_mysql_time *my_time, int *warning)
   my_time->hour= TIME_MAX_HOUR;
   my_time->minute= TIME_MAX_MINUTE;
   my_time->second= TIME_MAX_SECOND;
-  my_time->second_part= 0;
+  my_time->second_part= TIME_MAX_SECOND_PART;
   *warning|= MYSQL_TIME_WARN_OUT_OF_RANGE;
   return 0;
 }
@@ -1013,19 +1023,27 @@ void set_zero_time(MYSQL_TIME *tm, enum enum_mysql_timestamp_type time_type)
   using default format.
   This functions don't check that given MYSQL_TIME structure members are
   in valid range. If they are not, return value won't reflect any
-  valid date either. Additionally, make_time doesn't take into
-  account time->day member: it's assumed that days have been converted
-  to hours already.
+  valid date either.
 
   RETURN
     number of characters written to 'to'
 */
 
-int my_time_to_str(const MYSQL_TIME *l_time, char *to)
+int my_time_to_str(const MYSQL_TIME *l_time, char *to, int digits)
 {
-  uint extra_hours= 0;
-  return sprintf(to, "%s%02u:%02u:%02u", (l_time->neg ? "-" : ""),
-                 extra_hours+ l_time->hour, l_time->minute, l_time->second);
+  ulong day= (l_time->year || l_time->month) ? 0 : l_time->day;
+
+  if (digits == AUTO_SEC_PART_DIGITS)
+    digits= l_time->second_part ? MAX_SEC_PART_DIGITS : 0;
+
+  DBUG_ASSERT(digits >= 0 && digits <= MAX_SEC_PART_DIGITS);
+
+  return sprintf(to,
+                 digits ? "%s%02lu:%02u:%02u.%0*lu"
+                        : "%s%02lu:%02u:%02u",
+                 (l_time->neg ? "-" : ""),
+                 day * 24L + l_time->hour, l_time->minute, l_time->second,
+                 digits, (ulong)sec_part_shift(l_time->second_part, digits));
 }
 
 int my_date_to_str(const MYSQL_TIME *l_time, char *to)
@@ -1034,11 +1052,19 @@ int my_date_to_str(const MYSQL_TIME *l_time, char *to)
                  l_time->year, l_time->month, l_time->day);
 }
 
-int my_datetime_to_str(const MYSQL_TIME *l_time, char *to)
+int my_datetime_to_str(const MYSQL_TIME *l_time, char *to, int digits)
 {
-  return sprintf(to, "%04u-%02u-%02u %02u:%02u:%02u",
+  if (digits == AUTO_SEC_PART_DIGITS)
+    digits= l_time->second_part ? MAX_SEC_PART_DIGITS : 0;
+
+  DBUG_ASSERT(digits >= 0 && digits <= MAX_SEC_PART_DIGITS);
+
+  return sprintf(to,
+                 digits ? "%04u-%02u-%02u %02u:%02u:%02u.%0*lu"
+                        : "%04u-%02u-%02u %02u:%02u:%02u",
                  l_time->year, l_time->month, l_time->day,
-                 l_time->hour, l_time->minute, l_time->second);
+                 l_time->hour, l_time->minute, l_time->second,
+                 digits, (ulong)sec_part_shift(l_time->second_part, digits));
 }
 
 
@@ -1053,15 +1079,15 @@ int my_datetime_to_str(const MYSQL_TIME *l_time, char *to)
     The string must have at least MAX_DATE_STRING_REP_LENGTH bytes reserved.
 */
 
-int my_TIME_to_str(const MYSQL_TIME *l_time, char *to)
+int my_TIME_to_str(const MYSQL_TIME *l_time, char *to, int digits)
 {
   switch (l_time->time_type) {
   case MYSQL_TIMESTAMP_DATETIME:
-    return my_datetime_to_str(l_time, to);
+    return my_datetime_to_str(l_time, to, digits);
   case MYSQL_TIMESTAMP_DATE:
     return my_date_to_str(l_time, to);
   case MYSQL_TIMESTAMP_TIME:
-    return my_time_to_str(l_time, to);
+    return my_time_to_str(l_time, to, digits);
   case MYSQL_TIMESTAMP_NONE:
   case MYSQL_TIMESTAMP_ERROR:
     to[0]='\0';
@@ -1174,6 +1200,52 @@ longlong number_to_datetime(longlong nr, MYSQL_TIME *time_res,
   return LL(-1);
 }
 
+/*
+  Convert a double to a MYSQL_TIME struct.
+
+  @param[in]  nr             a number to convert
+  @param[out] ltime          Date to check.
+  @param[out] was_cut        MYSQL_TIME_WARN_OUT_OF_RANGE if the value was
+                             modified to fit in the valid range. Otherwise 0.
+
+  @details
+    Takes a number in the [-]HHHMMSS.uuuuuu format.
+ 
+    number_to_datetime() cannot take a double, because double precision is not
+    enough for YYYYMMDDHHMMSS.uuuuuu
+ 
+  @return
+    0        time value is valid, but was possibly truncated
+    1        time value is invalid
+*/
+int number_to_time(double nr, MYSQL_TIME *ltime, int *was_cut)
+{
+  ulong tmp;
+  *was_cut= 0;
+  ltime->year= ltime->month= ltime->day= 0;
+  ltime->time_type= MYSQL_TIMESTAMP_TIME;
+
+  if ((ltime->neg= nr < 0))
+    nr= -nr;
+
+  if (nr > TIME_MAX_VALUE)
+  {
+    nr= TIME_MAX_VALUE;
+    *was_cut= MYSQL_TIME_WARN_OUT_OF_RANGE;
+  }
+  tmp=(ulong)floor(nr);
+  ltime->hour  = tmp/100/100;
+  ltime->minute= tmp/100%100;
+  ltime->second= tmp%100;
+  ltime->second_part= (nr-tmp)*1e6;
+
+  if (ltime->minute < 60 && ltime->second < 60)
+    return 0;
+
+  *was_cut= MYSQL_TIME_WARN_TRUNCATED;
+  return 1;
+}
+
 
 /* Convert time value to integer in YYYYMMDDHHMMSS format */
 
@@ -1222,7 +1294,7 @@ ulonglong TIME_to_ulonglong_time(const MYSQL_TIME *my_time)
   DESCRIPTION
     The function is used when we need to convert value of time item
     to a number if it's used in numeric context, i. e.:
-    SELECT NOW()+1, CURDATE()+0, CURTIMIE()+0;
+    SELECT NOW()+1, CURDATE()+0, CURTIME()+0;
     SELECT ?+1;
 
   NOTE
@@ -1249,3 +1321,41 @@ ulonglong TIME_to_ulonglong(const MYSQL_TIME *my_time)
   return 0;
 }
 
+double TIME_to_double(const MYSQL_TIME *my_time)
+{
+  double d= (double)TIME_to_ulonglong(my_time);
+
+  if (my_time->time_type == MYSQL_TIMESTAMP_DATE)
+    return d;
+
+  d+= my_time->second_part/1e6;
+  return my_time->neg ? -d : d;
+}
+
+longlong pack_time(MYSQL_TIME *my_time)
+{
+  return  ((((((my_time->year     * 13ULL +
+               my_time->month)    * 32ULL +
+               my_time->day)      * 24ULL +
+               my_time->hour)     * 60ULL +
+               my_time->minute)   * 60ULL +
+               my_time->second)   * 1000000ULL +
+               my_time->second_part) * (my_time->neg ? -1 : 1);
+}
+
+#define get_one(WHERE, FACTOR) WHERE= packed % FACTOR; packed/= FACTOR
+
+MYSQL_TIME *unpack_time(longlong packed, MYSQL_TIME *my_time)
+{
+  if ((my_time->neg= packed < 0))
+    packed= -packed;
+  get_one(my_time->second_part, 1000000ULL);
+  get_one(my_time->second,           60ULL);
+  get_one(my_time->minute,           60ULL);
+  get_one(my_time->hour,             24ULL);
+  get_one(my_time->day,              32ULL);
+  get_one(my_time->month,            13ULL);
+  my_time->year= packed;
+  my_time->time_type= MYSQL_TIMESTAMP_DATETIME;
+  return my_time;
+}