MDEV-4724 Some temporal functions do not preserve microseconds

1 files changed, 160 insertions, 15 deletions

diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 8bbc692aa4a..99104136f2b 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -11,17 +11,17 @@ now()-now()	weekday(curdate())-weekday(now())	unix_timestamp()-unix_timestamp(no
 0	0	0
 select from_unixtime(unix_timestamp("1994-03-02 10:11:12")),from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s"),from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0;
 from_unixtime(unix_timestamp("1994-03-02 10:11:12"))	from_unixtime(unix_timestamp("1994-03-02 10:11:12"),"%Y-%m-%d %h:%i:%s")	from_unixtime(unix_timestamp("1994-03-02 10:11:12"))+0
-1994-03-02 10:11:12.000000	1994-03-02 10:11:12	19940302101112.000000
+1994-03-02 10:11:12	1994-03-02 10:11:12	19940302101112
 select sec_to_time(9001),sec_to_time(9001)+0,time_to_sec("15:12:22"),
 sec_to_time(time_to_sec("0:30:47")/6.21);
 sec_to_time(9001)	sec_to_time(9001)+0	time_to_sec("15:12:22")	sec_to_time(time_to_sec("0:30:47")/6.21)
-02:30:01	23001	54742.000000	00:04:57.423510
+02:30:01	23001	54742	00:04:57.4235
 select sec_to_time(9001.1), time_to_sec('15:12:22.123456'), time_to_sec(15.5566778899);
 sec_to_time(9001.1)	time_to_sec('15:12:22.123456')	time_to_sec(15.5566778899)
 02:30:01.1	54742.123456	15.556677
 select sec_to_time(time_to_sec('-838:59:59'));
 sec_to_time(time_to_sec('-838:59:59'))
--838:59:59.000000
+-838:59:59
 select sec_to_time('9001.1'), sec_to_time('1234567890123.123');
 sec_to_time('9001.1')	sec_to_time('1234567890123.123')
 02:30:01.100000	838:59:59.999999
@@ -77,7 +77,7 @@ HOUR("1997-03-03 23:03:22")	MINUTE("23:03:22")	SECOND(230322)
 23	3	22
 select TIME(230322), TIME(230322.33), TIME("230322.33");
 TIME(230322)	TIME(230322.33)	TIME("230322.33")
-23:03:22	23:03:22.33	23:03:22.330000
+23:03:22	23:03:22.33	23:03:22.33
 select week(19980101),week(19970101),week(19980101,1),week(19970101,1);
 week(19980101)	week(19970101)	week(19980101,1)	week(19970101,1)
 0	0	1	1
@@ -569,7 +569,7 @@ select @a:=FROM_UNIXTIME(1);
 1970-01-01 03:00:01
 select unix_timestamp(@a);
 unix_timestamp(@a)
-1.000000
+1
 select unix_timestamp('1969-12-01 19:00:01');
 unix_timestamp('1969-12-01 19:00:01')
 NULL
@@ -611,10 +611,10 @@ unix_timestamp('1969-12-30 01:00:00')
 NULL
 select unix_timestamp('2038-01-17 12:00:00');
 unix_timestamp('2038-01-17 12:00:00')
-2147331600.000000
+2147331600
 select unix_timestamp('1970-01-01 03:00:01');
 unix_timestamp('1970-01-01 03:00:01')
-1.000000
+1
 select unix_timestamp('2038-01-19 07:14:07');
 unix_timestamp('2038-01-19 07:14:07')
 NULL
@@ -1947,7 +1947,7 @@ TIME(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
 DATE(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
 TIMESTAMP(COALESCE(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00.000000	00:00:00.000000	2001-01-01	2001-01-01 00:00:00.000000
+20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00	00:00:00	2001-01-01	2001-01-01 00:00:00
 SELECT
 CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1,
 CAST(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2,
@@ -1957,7 +1957,7 @@ TIME(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5,
 DATE(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6,
 TIMESTAMP(COALESCE(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30.000000	10:20:30.000000	2001-01-02	2001-01-02 10:20:30.000000
+20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30	10:20:30	2001-01-02	2001-01-02 10:20:30
 SELECT
 CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
 CAST(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
@@ -1967,7 +1967,7 @@ TIME(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
 DATE(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
 TIMESTAMP(IFNULL(DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00.000000	00:00:00.000000	2001-01-01	2001-01-01 00:00:00.000000
+20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00	00:00:00	2001-01-01	2001-01-01 00:00:00
 SELECT
 CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS SIGNED) AS c1,
 CAST(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01')) AS DECIMAL(25,4)) AS c2,
@@ -1977,7 +1977,7 @@ TIME(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c5,
 DATE(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c6,
 TIMESTAMP(IFNULL(TIMESTAMP('2001-01-02 10:20:30'),DATE('2001-01-01'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30.000000	10:20:30.000000	2001-01-02	2001-01-02 10:20:30.000000
+20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30	10:20:30	2001-01-02	2001-01-02 10:20:30
 SELECT
 CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
 CAST(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
@@ -1987,7 +1987,7 @@ TIME(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
 DATE(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
 TIMESTAMP(IF(1,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00.000000	00:00:00.000000	2001-01-01	2001-01-01 00:00:00.000000
+20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00	00:00:00	2001-01-01	2001-01-01 00:00:00
 SELECT
 CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS SIGNED) AS c1,
 CAST(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30')) AS DECIMAL(25,4)) AS c2,
@@ -1997,7 +1997,7 @@ TIME(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c5,
 DATE(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c6,
 TIMESTAMP(IF(0,DATE('2001-01-01'),TIMESTAMP('2001-01-02 10:20:30'))) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30.000000	10:20:30.000000	2001-01-02	2001-01-02 10:20:30.000000
+20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30	10:20:30	2001-01-02	2001-01-02 10:20:30
 SELECT
 CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1,
 CAST(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2,
@@ -2007,7 +2007,7 @@ TIME(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') E
 DATE(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6,
 TIMESTAMP(CASE WHEN 1 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00.000000	00:00:00.000000	2001-01-01	2001-01-01 00:00:00.000000
+20010101000000	20010101000000.0000	20010101000000	2001-01-01 00:00:00	00:00:00	2001-01-01	2001-01-01 00:00:00
 SELECT
 CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS SIGNED) AS c1,
 CAST(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END AS DECIMAL(25,4)) AS c2,
@@ -2017,7 +2017,7 @@ TIME(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') E
 DATE(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c6,
 TIMESTAMP(CASE WHEN 0 THEN DATE('2001-01-01') ELSE TIMESTAMP('2001-01-02 10:20:30') END) AS c7;
 c1	c2	c3	c4	c5	c6	c7
-20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30.000000	10:20:30.000000	2001-01-02	2001-01-02 10:20:30.000000
+20010102102030	20010102102030.0000	20010102102030	2001-01-02 10:20:30	10:20:30	2001-01-02	2001-01-02 10:20:30
 CREATE TABLE t1 AS SELECT
 CONCAT(COALESCE(TIME(101010),TIME(101010))) AS c1,
 CONCAT(IF(0,TIME(101010),TIME(101010))) AS c2,
@@ -2032,3 +2032,148 @@ t1	CREATE TABLE `t1` (
   `c4` varbinary(10) DEFAULT NULL
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1
 DROP TABLE t1;
+#
+# MDEV-4724 Some temporal functions do not preserve microseconds
+#
+SELECT MAKETIME(10,10,10.231);
+MAKETIME(10,10,10.231)
+10:10:10.231
+SELECT MAKETIME(0, 0, 59.9);
+MAKETIME(0, 0, 59.9)
+00:00:59.9
+CREATE TABLE t1 AS SELECT
+MAKETIME(10,00,00),
+MAKETIME(10,00,00.1),
+MAKETIME(10,00,00.12),
+MAKETIME(10,00,00.123),
+MAKETIME(10,00,00.1234),
+MAKETIME(10,00,00.12345),
+MAKETIME(10,00,00.123456);
+SHOW COLUMNS FROM t1;
+Field	Type	Null	Key	Default	Extra
+MAKETIME(10,00,00)	time	YES		NULL	
+MAKETIME(10,00,00.1)	time(1)	YES		NULL	
+MAKETIME(10,00,00.12)	time(2)	YES		NULL	
+MAKETIME(10,00,00.123)	time(3)	YES		NULL	
+MAKETIME(10,00,00.1234)	time(4)	YES		NULL	
+MAKETIME(10,00,00.12345)	time(5)	YES		NULL	
+MAKETIME(10,00,00.123456)	time(6)	YES		NULL	
+DROP TABLE t1;
+CREATE TABLE t1 AS SELECT
+TIME('10:00:00'),
+TIME('10:00:00.1'),
+TIME('10:00:00.12'),
+TIME('10:00:00.123'),
+TIME('10:00:00.1234'),
+TIME('10:00:00.12345'),
+TIME('10:00:00.12346');
+SHOW COLUMNS FROM t1;
+Field	Type	Null	Key	Default	Extra
+TIME('10:00:00')	time	YES		NULL	
+TIME('10:00:00.1')	time(1)	YES		NULL	
+TIME('10:00:00.12')	time(2)	YES		NULL	
+TIME('10:00:00.123')	time(3)	YES		NULL	
+TIME('10:00:00.1234')	time(4)	YES		NULL	
+TIME('10:00:00.12345')	time(5)	YES		NULL	
+TIME('10:00:00.12346')	time(5)	YES		NULL	
+DROP TABLE t1;
+SET TIME_ZONE='+00:00';
+SET TIMESTAMP=UNIX_TIMESTAMP('2012-10-16 22:46:17');
+SELECT NOW(), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(NOW()),UNIX_TIMESTAMP('2012-10-16 22:46:17');
+NOW()	UNIX_TIMESTAMP()	UNIX_TIMESTAMP(NOW())	UNIX_TIMESTAMP('2012-10-16 22:46:17')
+2012-10-16 22:46:17	1350427577	1350427577	1350427577
+SET TIMESTAMP=UNIX_TIMESTAMP('1970-01-02 03:04:05.123456');
+SELECT @@timestamp, FROM_UNIXTIME(@@timestamp);
+@@timestamp	FROM_UNIXTIME(@@timestamp)
+97445.123456	1970-01-02 03:04:05.123456
+SET TIME_ZONE=DEFAULT;
+SET TIMESTAMP=DEFAULT;
+SELECT TIME('2012-10-16 15:54:16.12');
+TIME('2012-10-16 15:54:16.12')
+15:54:16.12
+SELECT TIMESTAMP('2012-10-16 15:54:16.12');
+TIMESTAMP('2012-10-16 15:54:16.12')
+2012-10-16 15:54:16.12
+SELECT TIMEDIFF('10:10:10.1','00:00:00');
+TIMEDIFF('10:10:10.1','00:00:00')
+10:10:10.1
+SELECT TIME_TO_SEC('10:10:10');
+TIME_TO_SEC('10:10:10')
+36610
+SELECT ADDTIME(TIME('10:10:10.1'),'10:10:10.12');
+ADDTIME(TIME('10:10:10.1'),'10:10:10.12')
+20:20:20.22
+SELECT ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12');
+ADDTIME(TIMESTAMP('2001-01-01 10:10:10.1'),'10:10:10.12')
+2001-01-01 20:20:20.22
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND)
+2001-01-01 00:00:01
+SELECT DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.1', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.1
+SELECT DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.12', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.12
+SELECT DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.123', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.123
+SELECT DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.1234', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.1234
+SELECT DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.12345', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.12345
+SELECT DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00.123456', INTERVAL 1 SECOND)
+2001-01-01 00:00:01.123456
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1 SECOND)
+2001-01-01 00:00:01
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1 SECOND)
+2001-01-01 00:00:01.1
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12 SECOND)
+2001-01-01 00:00:01.12
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123 SECOND)
+2001-01-01 00:00:01.123
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.1234 SECOND)
+2001-01-01 00:00:01.1234
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.12345 SECOND)
+2001-01-01 00:00:01.12345
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 1.123456 SECOND)
+2001-01-01 00:00:01.123456
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1 SECOND)
+2000-12-31 23:59:59
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1 SECOND)
+2000-12-31 23:59:58.9
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12 SECOND)
+2000-12-31 23:59:58.88
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123 SECOND)
+2000-12-31 23:59:58.877
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.1234 SECOND)
+2000-12-31 23:59:58.8766
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.12345 SECOND)
+2000-12-31 23:59:58.87655
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL -1.123456 SECOND)
+2000-12-31 23:59:58.876544
+SELECT DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND);
+DATE_ADD('2001-01-01 00:00:00', INTERVAL 'xxx' SECOND)
+2001-01-01 00:00:00.000000
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: 'xxx'
+SELECT CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00');
+CONVERT_TZ('2001-01-01 10:20:30.12','+00:00','+01:00')
+2001-01-01 11:20:30.12