unix_timestamp() and from_unixtime() supports microseconds.

unix_timestamp() and time_to_sec() are hybrid items,
  returning integer or double depending on the argument.

9 files changed, 133 insertions, 66 deletions

diff --git a/mysql-test/r/func_time_hires.result b/mysql-test/r/func_time_hires.result
index 1e48c4e8905..0def9625b37 100644
--- a/mysql-test/r/func_time_hires.result
+++ b/mysql-test/r/func_time_hires.result
@@ -1,4 +1,4 @@
-set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456, time_zone='+03:00';
+set timestamp=unix_timestamp('2011-01-01 01:01:01.123456'), time_zone='+03:00';
 select sec_to_time(12345), sec_to_time(12345.6789), sec_to_time(1234567e-2);
 sec_to_time(12345)	03:25:45
 sec_to_time(12345.6789)	03:25:45.6789
@@ -31,7 +31,7 @@ create table t1 select sec_to_time(12345), sec_to_time(12345.6789),
 sec_to_time(1234567e-2), now(), curtime(0),
 utc_timestamp(1), utc_time(2), current_time(3),
 current_timestamp(4), localtime(5), localtimestamp(6),
-time_to_sec('12:34:56'), time_to_sec('12:34:56.789');
+time_to_sec(123456), time_to_sec('12:34:56.789');
 show create table t1;
 Table	Create Table
 t1	CREATE TABLE `t1` (
@@ -46,7 +46,7 @@ t1	CREATE TABLE `t1` (
   `current_timestamp(4)` datetime(4) NOT NULL DEFAULT '0000-00-00 00:00:00.0000',
   `localtime(5)` datetime(5) NOT NULL DEFAULT '0000-00-00 00:00:00.00000',
   `localtimestamp(6)` datetime(6) NOT NULL DEFAULT '0000-00-00 00:00:00.000000',
-  `time_to_sec('12:34:56')` double DEFAULT NULL,
+  `time_to_sec(123456)` bigint(17) DEFAULT NULL,
   `time_to_sec('12:34:56.789')` double DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 select * from t1;
@@ -61,9 +61,19 @@ current_time(3)	01:01:01.123
 current_timestamp(4)	2011-01-01 01:01:01.1234
 localtime(5)	2011-01-01 01:01:01.12345
 localtimestamp(6)	2011-01-01 01:01:01.123456
-time_to_sec('12:34:56')	45296
+time_to_sec(123456)	45296
 time_to_sec('12:34:56.789')	45296.789
 drop table t1;
+select unix_timestamp('2011-01-01 01:01:01'), unix_timestamp('2011-01-01 01:01:01.123456'), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0))), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4)));;
+unix_timestamp('2011-01-01 01:01:01')	1293832861
+unix_timestamp('2011-01-01 01:01:01.123456')	1293832861.12346
+unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0)))	1293832861
+unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4)))	1293832861.1235
+select from_unixtime(unix_timestamp('2011/1/1 1:1:1')), from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456')), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0)))), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4))));;
+from_unixtime(unix_timestamp('2011/1/1 1:1:1'))	2011-01-01 01:01:01
+from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456'))	2011-01-01 01:01:01.123456
+from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0))))	2011-01-01 01:01:01
+from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4))))	2011-01-01 01:01:01.1234
 select sec_to_time(3020399.99999), sec_to_time(3020399.999999), sec_to_time(3020399.9999999);
 sec_to_time(3020399.99999)	sec_to_time(3020399.999999)	sec_to_time(3020399.9999999)
 838:59:59.99998	838:59:59.999999	838:59:59.999999
diff --git a/mysql-test/r/ps_2myisam.result b/mysql-test/r/ps_2myisam.result
index 6df671de88c..2fa07c5de6e 100644
--- a/mysql-test/r/ps_2myisam.result
+++ b/mysql-test/r/ps_2myisam.result
@@ -1793,8 +1793,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` int(10) NOT NULL DEFAULT '0',
-  `param10` bigint(20) DEFAULT NULL,
+  `const10` double NOT NULL DEFAULT '0',
+  `param10` double DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -1823,8 +1823,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	3	10	9	N	32769	0	63
-def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
+def	test	t5	t5	const10	const10	5	49	9	N	32769	31	63
+def	test	t5	t5	param10	param10	5	23	9	Y	32768	31	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
diff --git a/mysql-test/r/ps_3innodb.result b/mysql-test/r/ps_3innodb.result
index 7563a9c223b..06062193951 100644
--- a/mysql-test/r/ps_3innodb.result
+++ b/mysql-test/r/ps_3innodb.result
@@ -1776,8 +1776,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` int(10) NOT NULL DEFAULT '0',
-  `param10` bigint(20) DEFAULT NULL,
+  `const10` double NOT NULL DEFAULT '0',
+  `param10` double DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -1806,8 +1806,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	3	10	9	N	32769	0	63
-def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
+def	test	t5	t5	const10	const10	5	49	9	N	32769	31	63
+def	test	t5	t5	param10	param10	5	23	9	Y	32768	31	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
diff --git a/mysql-test/r/ps_4heap.result b/mysql-test/r/ps_4heap.result
index 70bcc2d6d4d..1819a9e649b 100644
--- a/mysql-test/r/ps_4heap.result
+++ b/mysql-test/r/ps_4heap.result
@@ -1777,8 +1777,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` int(10) NOT NULL DEFAULT '0',
-  `param10` bigint(20) DEFAULT NULL,
+  `const10` double NOT NULL DEFAULT '0',
+  `param10` double DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -1807,8 +1807,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	3	10	9	N	32769	0	63
-def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
+def	test	t5	t5	const10	const10	5	49	9	N	32769	31	63
+def	test	t5	t5	param10	param10	5	23	9	Y	32768	31	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
diff --git a/mysql-test/r/ps_5merge.result b/mysql-test/r/ps_5merge.result
index 87d587b69da..4a8e0adf46f 100644
--- a/mysql-test/r/ps_5merge.result
+++ b/mysql-test/r/ps_5merge.result
@@ -1713,8 +1713,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` int(10) NOT NULL DEFAULT '0',
-  `param10` bigint(20) DEFAULT NULL,
+  `const10` double NOT NULL DEFAULT '0',
+  `param10` double DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -1743,8 +1743,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	3	10	9	N	32769	0	63
-def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
+def	test	t5	t5	const10	const10	5	49	9	N	32769	31	63
+def	test	t5	t5	param10	param10	5	23	9	Y	32768	31	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
@@ -5067,8 +5067,8 @@ t5	CREATE TABLE `t5` (
   `param08` longtext,
   `const09` datetime DEFAULT NULL,
   `param09` longblob,
-  `const10` int(10) NOT NULL DEFAULT '0',
-  `param10` bigint(20) DEFAULT NULL,
+  `const10` double NOT NULL DEFAULT '0',
+  `param10` double DEFAULT NULL,
   `const11` int(4) DEFAULT NULL,
   `param11` bigint(20) DEFAULT NULL,
   `const12` binary(0) DEFAULT NULL,
@@ -5097,8 +5097,8 @@ def	test	t5	t5	const08	const08	253	19	19	N	1	0	8
 def	test	t5	t5	param08	param08	252	4294967295	19	Y	16	0	8
 def	test	t5	t5	const09	const09	12	19	19	Y	128	0	63
 def	test	t5	t5	param09	param09	252	4294967295	19	Y	144	0	63
-def	test	t5	t5	const10	const10	3	10	9	N	32769	0	63
-def	test	t5	t5	param10	param10	8	20	9	Y	32768	0	63
+def	test	t5	t5	const10	const10	5	49	9	N	32769	31	63
+def	test	t5	t5	param10	param10	5	23	9	Y	32768	31	63
 def	test	t5	t5	const11	const11	3	4	4	Y	32768	0	63
 def	test	t5	t5	param11	param11	8	20	4	Y	32768	0	63
 def	test	t5	t5	const12	const12	254	0	0	Y	128	0	63
diff --git a/mysql-test/t/func_time_hires.test b/mysql-test/t/func_time_hires.test
index ce88f139d29..88834a6bee0 100644
--- a/mysql-test/t/func_time_hires.test
+++ b/mysql-test/t/func_time_hires.test
@@ -1,5 +1,5 @@
 
-set timestamp=unix_timestamp('2011-01-01 01:01:01') + 0.123456, time_zone='+03:00';
+set timestamp=unix_timestamp('2011-01-01 01:01:01.123456'), time_zone='+03:00';
 
 --vertical_results
 select sec_to_time(12345), sec_to_time(12345.6789), sec_to_time(1234567e-2);
@@ -22,11 +22,14 @@ create table t1 select sec_to_time(12345), sec_to_time(12345.6789),
                        sec_to_time(1234567e-2), now(), curtime(0),
                        utc_timestamp(1), utc_time(2), current_time(3),
                        current_timestamp(4), localtime(5), localtimestamp(6),
-                       time_to_sec('12:34:56'), time_to_sec('12:34:56.789');
+                       time_to_sec(123456), time_to_sec('12:34:56.789');
 show create table t1;
 --query_vertical select * from t1
 drop table t1;
 
+--query_vertical select unix_timestamp('2011-01-01 01:01:01'), unix_timestamp('2011-01-01 01:01:01.123456'), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(0))), unix_timestamp(cast('2011-01-01 01:01:01.123456' as datetime(4)));
+--query_vertical select from_unixtime(unix_timestamp('2011/1/1 1:1:1')), from_unixtime(unix_timestamp('2011/1/1 1:1:1.123456')), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(0)))), from_unixtime(unix_timestamp(cast('2011/1/1 1:1:1.123456' as datetime(4))));
+
 select sec_to_time(3020399.99999), sec_to_time(3020399.999999), sec_to_time(3020399.9999999);
 select sec_to_time(-3020399.99999), sec_to_time(-3020399.999999), sec_to_time(-3020399.9999999);
 select 20010101000203.000000004 + interval 1 day;
diff --git a/sql/item_func.h b/sql/item_func.h
index cd2829fb5a7..7a4fd742cf5 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -242,6 +242,8 @@ class Item_func_numhybrid: public Item_func
 protected:
   Item_result hybrid_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_numhybrid(Item *a,Item *b)
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index a32cfabcaa1..18fde7abce3 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1176,26 +1176,23 @@ longlong Item_func_year::val_int_endpoint(bool left_endp, bool *incl_endp)
 }
 
 
-longlong Item_func_unix_timestamp::val_int()
+bool Item_func_unix_timestamp::get_timestamp_value(my_time_t *seconds,
+                                                   ulong *second_part)
 {
-  MYSQL_TIME ltime;
-  uint not_used;
-  
   DBUG_ASSERT(fixed == 1);
-  if (arg_count == 0)
-    return (longlong) current_thd->query_start();
   if (args[0]->type() == FIELD_ITEM)
   {						// Optimize timestamp field
     Field *field=((Item_field*) args[0])->field;
     if (field->type() == MYSQL_TYPE_TIMESTAMP)
     {
       if ((null_value= field->is_null()))
-        return 0;
-      ulong unused;
-      return ((Field_timestamp*) field)->get_timestamp(&unused);
+        return 1;
+      *seconds= ((Field_timestamp*)field)->get_timestamp(second_part);
+      return 0;
     }
   }
-  
+
+  MYSQL_TIME ltime;
   if (get_arg0_date(&ltime, 0))
   {
     /*
@@ -1204,14 +1201,53 @@ longlong Item_func_unix_timestamp::val_int()
       this case).
     */
     null_value= args[0]->null_value;
-    return 0;
+    return 1;
   }
+
+  uint not_used;
+  *seconds= TIME_to_timestamp(current_thd, &ltime, &not_used);
+  *second_part= ltime.second_part;
+  return 0;
+}
+
+
+longlong Item_func_unix_timestamp::int_op()
+{
+  if (arg_count == 0)
+    return (longlong) current_thd->query_start();
   
-  return (longlong) TIME_to_timestamp(current_thd, &ltime, &not_used);
+  ulong second_part;
+  my_time_t seconds;
+  if (get_timestamp_value(&seconds, &second_part))
+    return 0;
+
+  return seconds;
 }
 
 
-double Item_func_time_to_sec::val_real()
+double Item_func_unix_timestamp::real_op()
+{
+  ulong second_part;
+  my_time_t seconds;
+  if (get_timestamp_value(&seconds, &second_part))
+    return 0;
+
+  return seconds + second_part/(double)TIME_SECOND_PART_FACTOR;
+}
+
+
+longlong Item_func_time_to_sec::int_op()
+{
+  DBUG_ASSERT(fixed == 1);
+  MYSQL_TIME ltime;
+  longlong seconds;
+  (void) get_arg0_time(&ltime);
+  seconds=ltime.hour*3600L+ltime.minute*60+ltime.second;
+  return ltime.neg ? -seconds : seconds;
+}
+
+
+double Item_func_time_to_sec::real_op()
 {
   DBUG_ASSERT(fixed == 1);
   MYSQL_TIME ltime;
@@ -1797,6 +1833,7 @@ void Item_func_from_unixtime::fix_length_and_dec()
   thd= current_thd;
   maybe_null= 1;
   thd->time_zone_used= 1;
+  decimals= args[0]->decimals;
   Item_temporal_func::fix_length_and_dec();
 }
 
@@ -1804,17 +1841,15 @@ void Item_func_from_unixtime::fix_length_and_dec()
 bool Item_func_from_unixtime::get_date(MYSQL_TIME *ltime,
 				       uint fuzzy_date __attribute__((unused)))
 {
-  ulonglong tmp= (ulonglong)(args[0]->val_int());
-  /*
-    "tmp > TIMESTAMP_MAX_VALUE" check also covers case of negative
-    from_unixtime() argument since tmp is unsigned.
-  */
-  if ((null_value= (args[0]->null_value || tmp > TIMESTAMP_MAX_VALUE)))
-    return 1;
+  double tmp= args[0]->val_real();
+  if (args[0]->null_value || tmp < 0 || tmp > TIMESTAMP_MAX_VALUE)
+    return (null_value= 1);
 
   thd->variables.time_zone->gmt_sec_to_TIME(ltime, (my_time_t)tmp);
 
-  return 0;
+  ltime->second_part= (ulong)((tmp - floor(tmp))*TIME_SECOND_PART_FACTOR);
+
+  return (null_value= 0);
 }
 
 
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 14275680d15..d50b0c20716 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -297,15 +297,37 @@ class Item_func_dayname :public Item_func_weekday
 };
 
 
-class Item_func_unix_timestamp :public Item_int_func
+class Item_func_seconds_hybrid: public Item_func_numhybrid
 {
-  String value;
 public:
-  Item_func_unix_timestamp() :Item_int_func() {}
-  Item_func_unix_timestamp(Item *a) :Item_int_func(a) {}
-  longlong val_int();
+  Item_func_seconds_hybrid() :Item_func_numhybrid() {}
+  Item_func_seconds_hybrid(Item *a) :Item_func_numhybrid(a) {}
+  void fix_num_length_and_dec()
+  {
+    if (arg_count)
+      decimals= args[0]->decimals;
+    if (decimals != NOT_FIXED_DEC)
+      set_if_smaller(decimals, TIME_SECOND_PART_DIGITS);
+    max_length=17 + (decimals ? decimals + 1 : 0);
+  }
+  void find_num_type() { hybrid_type= decimals ? REAL_RESULT : INT_RESULT; }
+  my_decimal *decimal_op(my_decimal* buf) { DBUG_ASSERT(0); return 0; }
+  String *str_op(String *str) { DBUG_ASSERT(0); return 0; }
+};
+
+class Item_func_unix_timestamp :public Item_func_seconds_hybrid
+{
+  bool get_timestamp_value(my_time_t *seconds, ulong *second_part);
+public:
+  Item_func_unix_timestamp() :Item_func_seconds_hybrid() {}
+  Item_func_unix_timestamp(Item *a) :Item_func_seconds_hybrid(a) {}
   const char *func_name() const { return "unix_timestamp"; }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  void fix_num_length_and_dec()
+  {
+    maybe_null= false;
+    Item_func_seconds_hybrid::fix_num_length_and_dec();
+  }
   /*
     UNIX_TIMESTAMP() depends on the current timezone
     (and thus may not be used as a partitioning function)
@@ -315,29 +337,24 @@ public:
   {
     return !has_timestamp_args();
   }
-  void fix_length_and_dec()
-  {
-    decimals=0;
-    max_length=10*MY_CHARSET_BIN_MB_MAXLEN;
-  }
+  longlong int_op();
+  double real_op();
 };
 
 
-class Item_func_time_to_sec :public Item_real_func
+class Item_func_time_to_sec :public Item_func_seconds_hybrid
 {
 public:
-  Item_func_time_to_sec(Item *item) :Item_real_func(item) {}
+  Item_func_time_to_sec(Item *item) :Item_func_seconds_hybrid(item) {}
   const char *func_name() const { return "time_to_sec"; }
-  double val_real();
-  void fix_length_and_dec()
+  void fix_num_length_and_dec()
   {
-    maybe_null= TRUE;
-    decimals= args[0]->decimals;
-    if (decimals != NOT_FIXED_DEC)
-      set_if_smaller(decimals, TIME_SECOND_PART_DIGITS);
-    max_length=17;
+    maybe_null= true;
+    Item_func_seconds_hybrid::fix_num_length_and_dec();
   }
   bool check_partition_func_processor(uchar *int_arg) {return FALSE;}
+  longlong int_op();
+  double real_op();
 };