Merge 5.3->5.5

24 files changed, 220 insertions, 75 deletions

diff --git a/include/my_global.h b/include/my_global.h
index 7607e02ce3f..09adc9989ef 100644
--- a/include/my_global.h
+++ b/include/my_global.h
@@ -483,6 +483,7 @@ typedef unsigned short ushort;
 
 #define swap_variables(t, a, b) { t dummy; dummy= a; a= b; b= dummy; }
 #define test(a)		((a) ? 1 : 0)
+#define MY_TEST(a) ((a) ? 1 : 0)
 #define set_if_bigger(a,b)  do { if ((a) < (b)) (a)=(b); } while(0)
 #define set_if_smaller(a,b) do { if ((a) > (b)) (a)=(b); } while(0)
 #define set_bits(type, bit_count) (sizeof(type)*8 <= (bit_count) ? ~(type) 0 : ((((type) 1) << (bit_count)) - (type) 1))
diff --git a/include/my_time.h b/include/my_time.h
index f9893fa2d01..bcf1a8cb215 100644
--- a/include/my_time.h
+++ b/include/my_time.h
@@ -79,6 +79,13 @@ typedef long my_time_t;
 
 #define MYSQL_TIME_WARN_TRUNCATED    1
 #define MYSQL_TIME_WARN_OUT_OF_RANGE 2
+#define MYSQL_TIME_NOTE_TRUNCATED    16
+
+#define MYSQL_TIME_WARN_WARNINGS (MYSQL_TIME_WARN_TRUNCATED|MYSQL_TIME_WARN_OUT_OF_RANGE)
+#define MYSQL_TIME_WARN_NOTES    (MYSQL_TIME_NOTE_TRUNCATED)
+
+#define MYSQL_TIME_WARN_HAVE_WARNINGS(x) MY_TEST((x) & MYSQL_TIME_WARN_WARNINGS)
+#define MYSQL_TIME_WARN_HAVE_NOTES(x) MY_TEST((x) & MYSQL_TIME_WARN_NOTES)
 
 /* Limits for the TIME data type */
 #define TIME_MAX_HOUR 838
@@ -112,7 +119,7 @@ longlong double_to_datetime(double nr, MYSQL_TIME *ltime, uint flags, int *cut)
                             ltime, flags, cut);
 }
 
-int number_to_time(my_bool neg, longlong nr, ulong sec_part,
+int number_to_time(my_bool neg, ulonglong nr, ulong sec_part,
                    MYSQL_TIME *ltime, int *was_cut);
 ulonglong TIME_to_ulonglong_datetime(const MYSQL_TIME *);
 ulonglong TIME_to_ulonglong_date(const MYSQL_TIME *);
diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index 6f6dde1e172..e50beb54014 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -77,7 +77,7 @@ cast(cast(20010203101112.121314 as double) as datetime)
 2001-02-03 10:11:12
 select cast(cast(010203101112.12 as double) as datetime);
 cast(cast(010203101112.12 as double) as datetime)
-0001-02-03 10:11:12
+2001-02-03 10:11:12
 select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime);
 cast(cast(20010203101112.121314 as decimal(32,6)) as datetime)
 2001-02-03 10:11:12
@@ -86,10 +86,10 @@ cast(20010203101112.121314 as datetime)
 2001-02-03 10:11:12
 select cast(110203101112.121314 as datetime);
 cast(110203101112.121314 as datetime)
-0011-02-03 10:11:12
+2011-02-03 10:11:12
 select cast(cast(010203101112.12 as double) as datetime);
 cast(cast(010203101112.12 as double) as datetime)
-0001-02-03 10:11:12
+2001-02-03 10:11:12
 select cast("2011-02-03 10:11:12.123456" as datetime);
 cast("2011-02-03 10:11:12.123456" as datetime)
 2011-02-03 10:11:12
@@ -110,7 +110,7 @@ cast(cast(20010203101112.5 as double) as datetime(1))
 2001-02-03 10:11:12.5
 select cast(cast(010203101112.12 as double) as datetime(2));
 cast(cast(010203101112.12 as double) as datetime(2))
-0001-02-03 10:11:12.12
+2001-02-03 10:11:12.12
 select cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6));
 cast(cast(20010203101112.121314 as decimal(32,6)) as datetime(6))
 2001-02-03 10:11:12.121314
@@ -119,10 +119,10 @@ cast(20010203101112.121314 as datetime(6))
 2001-02-03 10:11:12.121314
 select cast(110203101112.121314 as datetime(6));
 cast(110203101112.121314 as datetime(6))
-0011-02-03 10:11:12.121314
+2011-02-03 10:11:12.121314
 select cast(cast(010203101112.12 as double) as datetime(6));
 cast(cast(010203101112.12 as double) as datetime(6))
-0001-02-03 10:11:12.120000
+2001-02-03 10:11:12.120000
 select cast("2011-02-03 10:11:12.123456" as time);
 cast("2011-02-03 10:11:12.123456" as time)
 10:11:12
@@ -265,7 +265,7 @@ Warnings:
 Warning	1264	Out of range value for column 'cast(-1000 as double(5,2))' at row 1
 select cast(010203101112.121314 as datetime);
 cast(010203101112.121314 as datetime)
-0001-02-03 10:11:12
+2001-02-03 10:11:12
 select cast(120010203101112.121314 as datetime);
 cast(120010203101112.121314 as datetime)
 NULL
@@ -326,7 +326,7 @@ cast(cast(120010203101112.121314 as double) as datetime)
 NULL
 select cast(cast(1.1 as double) as datetime);
 cast(cast(1.1 as double) as datetime)
-0000-00-00 00:00:01
+NULL
 select cast(cast(-1.1 as double) as datetime);
 cast(cast(-1.1 as double) as datetime)
 NULL
diff --git a/mysql-test/r/dyncol.result b/mysql-test/r/dyncol.result
index 5e53c365dad..aacb5363717 100644
--- a/mysql-test/r/dyncol.result
+++ b/mysql-test/r/dyncol.result
@@ -1001,29 +1001,29 @@ Warnings:
 Warning	1292	Truncated incorrect time value: '1223.5aa'
 select column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time);
 column_get(column_create(1, 18446744073709551615 AS unsigned int), 1 as time)
-NULL
+838:59:59
 Warnings:
-Warning	1292	Incorrect datetime value: '1.8446744073709552e19'
+Warning	1292	Truncated incorrect time value: '1.8446744073709552e19'
 select column_get(column_create(1, 9223372036854775807 AS int), 1 as time);
 column_get(column_create(1, 9223372036854775807 AS int), 1 as time)
-NULL
+838:59:59
 Warnings:
-Warning	1292	Incorrect datetime value: '9223372036854775807'
+Warning	1292	Truncated incorrect time value: '9223372036854775807'
 select column_get(column_create(1, -9223372036854775808 AS int), 1 as time);
 column_get(column_create(1, -9223372036854775808 AS int), 1 as time)
-NULL
+-838:59:59
 Warnings:
-Warning	1292	Incorrect datetime value: '-9223372036854775808'
+Warning	1292	Truncated incorrect time value: '-9223372036854775808'
 select column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time);
 column_get(column_create(1, 99999999999999999999999999999 AS decimal(32,10)), 1 as time)
-NULL
+838:59:59
 Warnings:
-Warning	1292	Incorrect datetime value: '99999999999999999999999999999'
+Warning	1292	Truncated incorrect time value: '99999999999999999999999999999'
 select column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time);
 column_get(column_create(1, 99999999999999999999999999999 AS double), 1 as time)
-NULL
+838:59:59
 Warnings:
-Warning	1292	Incorrect datetime value: '1e29'
+Warning	1292	Truncated incorrect time value: '1e29'
 select column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time);
 column_get(column_create(1, "2011-02-32 8:46:06.23434" AS CHAR), 1 as time)
 NULL
@@ -1425,6 +1425,17 @@ Warning	1265	Data truncated for column 'dyncol' at row 1
 SELECT COLUMN_ADD( dyncol, 7, '22:22:22', 8, REPEAT('x',270) AS CHAR ) FROM t1;
 DROP table t1;
 #
+# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
+#
+SELECT
+column_get(column_create(1, -999999999999999 AS int), 1 AS TIME) AS t1,
+column_get(column_create(1, -9223372036854775808 AS int), 1 AS TIME) AS t2;
+t1	t2
+-838:59:59	-838:59:59
+Warnings:
+Warning	1292	Truncated incorrect time value: '-999999999999999'
+Warning	1292	Truncated incorrect time value: '-9223372036854775808'
+#
 # end of 5.3 tests
 #
 select column_get(column_create(1, "18446744073709552001" as char), 1 as int);
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 52c148d9046..f71b27d1a2a 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -1091,9 +1091,9 @@ NULL
 select isnull(week(now() + 0)), isnull(week(now() + 0.2)),
 week(20061108), week(20061108.01), week(20061108085411.000002);
 isnull(week(now() + 0))	isnull(week(now() + 0.2))	week(20061108)	week(20061108.01)	week(20061108085411.000002)
-0	0	45	NULL	45
+0	0	45	45	45
 Warnings:
-Warning	1292	Incorrect datetime value: '20061108.01'
+Warning	1292	Truncated incorrect datetime value: '20061108.01'
 End of 4.1 tests
 select time_format('100:00:00', '%H %k %h %I %l');
 time_format('100:00:00', '%H %k %h %I %l')
@@ -2408,3 +2408,11 @@ SELECT 1 FROM t1 GROUP BY MONTHNAME(0) WITH ROLLUP;
 1
 1
 DROP TABLE t1;
+#
+# MDEV-6099 Bad results for DATE_ADD(.., INTERVAL 2000000000000000000.0 SECOND)
+#
+SELECT DATE_ADD('2001-01-01 10:20:30',INTERVAL 250000000000.0 SECOND) AS c1, DATE_ADD('2001-01-01 10:20:30',INTERVAL 2000000000000000000.0 SECOND) AS c2;
+c1	c2
+9923-03-10 22:47:10.0	NULL
+Warnings:
+Warning	1292	Truncated incorrect DECIMAL value: '2000000000000000000.0'
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 091ed216252..4a29acb3155 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -724,6 +724,15 @@ call test5041();
 dt
 0000-01-02 08:20:30
 drop procedure test5041;
+#
+# MDEV-6097 Inconsistent results for CAST(int,decimal,double AS DATETIME)
+#
+SELECT
+CAST(010203101112 AS DATETIME(1)) AS c1,
+CAST(010203101112.2 AS DATETIME(1)) AS c2,
+CAST(010203101112.2+0e0 AS DATETIME(1)) AS c3;
+c1	c2	c3
+2001-02-03 10:11:12.0	2001-02-03 10:11:12.2	2001-02-03 10:11:12.2
 End of 5.3 tests
 #
 # Start of 5.5 tests
diff --git a/mysql-test/r/type_time.result b/mysql-test/r/type_time.result
index ef02368e375..ad2e857a8d3 100644
--- a/mysql-test/r/type_time.result
+++ b/mysql-test/r/type_time.result
@@ -299,6 +299,34 @@ Field	Type	Null	Key	Default	Extra
 MAX(t1)+1	decimal(22,1)	YES		NULL	
 DROP TABLE t2,t1;
 #
+# MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
+#
+SET sql_mode=traditional;
+CREATE TABLE t1 (a TIME(6));
+INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
+ERROR 22007: Incorrect time value: '18446744069414584320' for column 'a' at row 1
+SET sql_mode=DEFAULT;
+INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED));
+Warnings:
+Warning	1264	Out of range value for column 'a' at row 1
+SELECT * FROM t1;
+a
+838:59:59.999999
+DROP TABLE t1;
+SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED);
+TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED)
+0
+Warnings:
+Warning	1292	Truncated incorrect DOUBLE value: '00:00:00'
+#
+# MDEV-6100 No warning on CAST(9000000 AS TIME)
+#
+SELECT CAST(9000000 AS TIME);
+CAST(9000000 AS TIME)
+838:59:59
+Warnings:
+Warning	1292	Truncated incorrect time value: '9000000'
+#
 # End of 5.3 tests
 #
 CREATE TABLE t1 (f1 TIME);
diff --git a/mysql-test/r/type_time_hires.result b/mysql-test/r/type_time_hires.result
index bf54434a04c..7fbb5079d5a 100644
--- a/mysql-test/r/type_time_hires.result
+++ b/mysql-test/r/type_time_hires.result
@@ -16,46 +16,46 @@ Warnings:
 Warning	1265	Data truncated for column 'a' at row 1
 insert t1 values (99991231235959e1);
 Warnings:
-Warning	1265	Data truncated for column 'a' at row 1
+Warning	1264	Out of range value for column 'a' at row 1
 select * from t1;
 a
-00:00:00.000
 00:20:03.123
 01:02:03.456
 03:04:05.789
 15:47:11.123
+838:59:59.999
 select truncate(a, 6) from t1;
 truncate(a, 6)
-0.000000
 2003.123000
 10203.456000
 30405.789062
 154711.123000
+8385959.999000
 select a DIV 1 from t1;
 a DIV 1
-0
 2003
 10203
 30405
 154711
+8385959
 select group_concat(distinct a) from t1;
 group_concat(distinct a)
-00:00:00.000,00:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123
+00:20:03.123,01:02:03.456,03:04:05.789,15:47:11.123,838:59:59.999
 alter table t1 engine=innodb;
 select * from t1 order by a;
 a
-00:00:00.000
 00:20:03.123
 01:02:03.456
 03:04:05.789
 15:47:11.123
+838:59:59.999
 select * from t1 order by a+0;
 a
-00:00:00.000
 00:20:03.123
 01:02:03.456
 03:04:05.789
 15:47:11.123
+838:59:59.999
 drop table t1;
 create table t1 (a time(4)) engine=innodb;
 insert t1 values ('2010-12-11 01:02:03.456789');
diff --git a/mysql-test/suite/funcs_1/r/innodb_func_view.result b/mysql-test/suite/funcs_1/r/innodb_func_view.result
index feb8c0a983f..cff653c1990 100644
--- a/mysql-test/suite/funcs_1/r/innodb_func_view.result
+++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result
@@ -3694,14 +3694,14 @@ my_double, id FROM t1_values
 WHERE select_id = 53 OR select_id IS NULL order by id;
 CAST(my_double AS TIME)	my_double	id
 NULL	NULL	1
-NULL	-1.7976931348623e308	2
-NULL	1.7976931348623e308	3
+-838:59:59	-1.7976931348623e308	2
+838:59:59	1.7976931348623e308	3
 00:00:00	0	4
 -00:00:01	-1	5
 00:17:58	1758	25
 Warnings:
-Warning	1292	Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2
-Warning	1292	Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3
+Warning	1292	Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 2
+Warning	1292	Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 3
 SHOW CREATE VIEW v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as time) AS `CAST(my_double AS TIME)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -3710,14 +3710,14 @@ WHERE v1.id IN (SELECT id FROM t1_values
 WHERE select_id = 53 OR select_id IS NULL) order by id;
 CAST(my_double AS TIME)	my_double	id
 NULL	NULL	1
-NULL	-1.7976931348623e308	2
-NULL	1.7976931348623e308	3
+-838:59:59	-1.7976931348623e308	2
+838:59:59	1.7976931348623e308	3
 00:00:00	0	4
 -00:00:01	-1	5
 00:17:58	1758	25
 Warnings:
-Warning	1292	Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1
-Warning	1292	Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1
+Warning	1292	Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 1
+Warning	1292	Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 1
 DROP VIEW v1;
 
 
diff --git a/mysql-test/suite/funcs_1/r/memory_func_view.result b/mysql-test/suite/funcs_1/r/memory_func_view.result
index 43703aa7ead..f29de41cbbb 100644
--- a/mysql-test/suite/funcs_1/r/memory_func_view.result
+++ b/mysql-test/suite/funcs_1/r/memory_func_view.result
@@ -3695,14 +3695,14 @@ my_double, id FROM t1_values
 WHERE select_id = 53 OR select_id IS NULL order by id;
 CAST(my_double AS TIME)	my_double	id
 NULL	NULL	1
-NULL	-1.7976931348623e308	2
-NULL	1.7976931348623e308	3
+-838:59:59	-1.7976931348623e308	2
+838:59:59	1.7976931348623e308	3
 00:00:00	0	4
 -00:00:01	-1	5
 00:17:58	1758	25
 Warnings:
-Warning	1292	Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2
-Warning	1292	Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3
+Warning	1292	Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 2
+Warning	1292	Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 3
 SHOW CREATE VIEW v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as time) AS `CAST(my_double AS TIME)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -3711,14 +3711,14 @@ WHERE v1.id IN (SELECT id FROM t1_values
 WHERE select_id = 53 OR select_id IS NULL) order by id;
 CAST(my_double AS TIME)	my_double	id
 NULL	NULL	1
-NULL	-1.7976931348623e308	2
-NULL	1.7976931348623e308	3
+-838:59:59	-1.7976931348623e308	2
+838:59:59	1.7976931348623e308	3
 00:00:00	0	4
 -00:00:01	-1	5
 00:17:58	1758	25
 Warnings:
-Warning	1292	Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1
-Warning	1292	Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1
+Warning	1292	Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 1
+Warning	1292	Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 1
 DROP VIEW v1;
 
 
diff --git a/mysql-test/suite/funcs_1/r/myisam_func_view.result b/mysql-test/suite/funcs_1/r/myisam_func_view.result
index 43703aa7ead..f29de41cbbb 100644
--- a/mysql-test/suite/funcs_1/r/myisam_func_view.result
+++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result
@@ -3695,14 +3695,14 @@ my_double, id FROM t1_values
 WHERE select_id = 53 OR select_id IS NULL order by id;
 CAST(my_double AS TIME)	my_double	id
 NULL	NULL	1
-NULL	-1.7976931348623e308	2
-NULL	1.7976931348623e308	3
+-838:59:59	-1.7976931348623e308	2
+838:59:59	1.7976931348623e308	3
 00:00:00	0	4
 -00:00:01	-1	5
 00:17:58	1758	25
 Warnings:
-Warning	1292	Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 2
-Warning	1292	Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 3
+Warning	1292	Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 2
+Warning	1292	Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 3
 SHOW CREATE VIEW v1;
 View	Create View	character_set_client	collation_connection
 v1	CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v1` AS select cast(`t1_values`.`my_double` as time) AS `CAST(my_double AS TIME)`,`t1_values`.`my_double` AS `my_double`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -3711,14 +3711,14 @@ WHERE v1.id IN (SELECT id FROM t1_values
 WHERE select_id = 53 OR select_id IS NULL) order by id;
 CAST(my_double AS TIME)	my_double	id
 NULL	NULL	1
-NULL	-1.7976931348623e308	2
-NULL	1.7976931348623e308	3
+-838:59:59	-1.7976931348623e308	2
+838:59:59	1.7976931348623e308	3
 00:00:00	0	4
 -00:00:01	-1	5
 00:17:58	1758	25
 Warnings:
-Warning	1292	Incorrect datetime value: '-1.7976931348623e308' for column 'my_double' at row 1
-Warning	1292	Incorrect datetime value: '1.7976931348623e308' for column 'my_double' at row 1
+Warning	1292	Incorrect time value: '-1.7976931348623e308' for column 'my_double' at row 1
+Warning	1292	Incorrect time value: '1.7976931348623e308' for column 'my_double' at row 1
 DROP VIEW v1;
 
 
diff --git a/mysql-test/t/dyncol.test b/mysql-test/t/dyncol.test
index 158a47aeb3c..68e10a5fffe 100644
--- a/mysql-test/t/dyncol.test
+++ b/mysql-test/t/dyncol.test
@@ -627,6 +627,13 @@ SELECT COLUMN_ADD( dyncol, 7, '22:22:22', 8, REPEAT('x',270) AS CHAR ) FROM t1;
 DROP table t1;
 
 --echo #
+--echo # MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
+--echo #
+SELECT
+  column_get(column_create(1, -999999999999999 AS int), 1 AS TIME) AS t1,
+  column_get(column_create(1, -9223372036854775808 AS int), 1 AS TIME) AS t2; 
+
+--echo #
 --echo # end of 5.3 tests
 --echo #
 
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 7ac5220a190..d30300f90d3 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -1467,3 +1467,8 @@ CREATE TABLE t1 (i INT);
 INSERT INTO t1 VALUES (1),(2);
 SELECT 1 FROM t1 GROUP BY MONTHNAME(0) WITH ROLLUP;
 DROP TABLE t1;
+
+--echo #
+--echo # MDEV-6099 Bad results for DATE_ADD(.., INTERVAL 2000000000000000000.0 SECOND)
+--echo #
+SELECT DATE_ADD('2001-01-01 10:20:30',INTERVAL 250000000000.0 SECOND) AS c1, DATE_ADD('2001-01-01 10:20:30',INTERVAL 2000000000000000000.0 SECOND) AS c2;
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index 45aca47bd03..f69b44f9892 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -519,6 +519,14 @@ DELIMITER ;|
 call test5041();
 drop procedure test5041;
 
+--echo #
+--echo # MDEV-6097 Inconsistent results for CAST(int,decimal,double AS DATETIME)
+--echo #
+SELECT
+  CAST(010203101112 AS DATETIME(1)) AS c1,
+  CAST(010203101112.2 AS DATETIME(1)) AS c2,
+  CAST(010203101112.2+0e0 AS DATETIME(1)) AS c3;
+
 
 --echo End of 5.3 tests
 
diff --git a/mysql-test/t/type_time.test b/mysql-test/t/type_time.test
index 9ecd5d22a81..88840e9b679 100644
--- a/mysql-test/t/type_time.test
+++ b/mysql-test/t/type_time.test
@@ -201,6 +201,23 @@ SELECT * FROM t2;
 SHOW COLUMNS FROM t2;
 DROP TABLE t2,t1;
 
+--echo #
+--echo # MDEV-4858 Wrong results for a huge unsigned value inserted into a TIME column
+--echo #
+SET sql_mode=traditional;
+CREATE TABLE t1 (a TIME(6));
+--error ER_TRUNCATED_WRONG_VALUE
+INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); 
+SET sql_mode=DEFAULT;
+INSERT INTO t1 VALUES (CAST(0xFFFFFFFF00000000 AS UNSIGNED)); 
+SELECT * FROM t1;
+DROP TABLE t1;
+SELECT TIME'00:00:00'> CAST(0xFFFFFFFF00000000 AS UNSIGNED);
+
+--echo #
+--echo # MDEV-6100 No warning on CAST(9000000 AS TIME)
+--echo #
+SELECT CAST(9000000 AS TIME);
 
 --echo #
 --echo # End of 5.3 tests
diff --git a/sql-common/my_time.c b/sql-common/my_time.c
index b91028cc1ea..860f69a7f8e 100644
--- a/sql-common/my_time.c
+++ b/sql-common/my_time.c
@@ -1223,7 +1223,7 @@ longlong number_to_datetime(longlong nr, ulong sec_part, MYSQL_TIME *time_res,
   *was_cut= 0;
   time_res->time_type=MYSQL_TIMESTAMP_DATE;
 
-  if (nr == 0 || nr >= 10000101000000LL || sec_part)
+  if (nr == 0 || nr >= 10000101000000LL)
   {
     time_res->time_type=MYSQL_TIMESTAMP_DATETIME;
     goto ok;
@@ -1281,7 +1281,11 @@ longlong number_to_datetime(longlong nr, ulong sec_part, MYSQL_TIME *time_res,
       time_res->minute <= 59 && time_res->second <= 59 &&
       sec_part <= TIME_MAX_SECOND_PART &&
       !check_date(time_res, nr || sec_part, flags, was_cut))
+  {
+    if (time_res->time_type == MYSQL_TIMESTAMP_DATE && sec_part != 0)
+       *was_cut= MYSQL_TIME_NOTE_TRUNCATED;
     return nr;
+  }
 
   /* Don't want to have was_cut get set if NO_ZERO_DATE was violated. */
   if (nr || !(flags & TIME_NO_ZERO_DATE))
@@ -1315,10 +1319,10 @@ longlong number_to_datetime(longlong nr, ulong sec_part, MYSQL_TIME *time_res,
     0        time value is valid, but was possibly truncated
     -1       time value is invalid
 */
-int number_to_time(my_bool neg, longlong nr, ulong sec_part,
+int number_to_time(my_bool neg, ulonglong nr, ulong sec_part,
                    MYSQL_TIME *ltime, int *was_cut)
 {
-  if (nr > 9999999 && neg == 0)
+  if (nr > 9999999 && nr < 99991231235959ULL && neg == 0)
   {
     if (number_to_datetime(nr, sec_part, ltime,
                            TIME_INVALID_DATES, was_cut) < 0)
@@ -1326,7 +1330,7 @@ int number_to_time(my_bool neg, longlong nr, ulong sec_part,
 
     ltime->year= ltime->month= ltime->day= 0;
     ltime->time_type= MYSQL_TIMESTAMP_TIME;
-    *was_cut= MYSQL_TIME_WARN_TRUNCATED;
+    *was_cut= MYSQL_TIME_NOTE_TRUNCATED;
     return 0;
   }
 
diff --git a/sql/field.cc b/sql/field.cc
index 903c7a2653a..52a490921b8 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -4576,7 +4576,7 @@ int Field_timestamp::store(longlong nr, bool unsigned_val)
 {
   MYSQL_TIME l_time;
   int error;
-  ErrConvInteger str(nr);
+  ErrConvInteger str(nr, unsigned_val);
   THD *thd= table->in_use;
 
   /* We don't want to store invalid or fuzzy datetime values in TIMESTAMP */
@@ -4952,6 +4952,17 @@ int Field_temporal::store_TIME_with_warning(MYSQL_TIME *ltime,
   
   ASSERT_COLUMN_MARKED_FOR_WRITE_OR_COMPUTED;
 
+#if MARIADB_VERSION_ID < 1000000
+  /*
+    Check if the YYYYMMDD part was truncated.
+    Translate a note into a warning.
+    In MariaDB-10.0 we have a better warnings/notes handling,
+    so this code is not needed. 
+  */
+  if (was_cut & MYSQL_TIME_NOTE_TRUNCATED)
+    was_cut|= MYSQL_TIME_WARN_TRUNCATED;
+#endif
+
   if (was_cut == 0 &&
       have_smth_to_conv == 0 &&
       mysql_type_to_time_type(type()) != MYSQL_TIMESTAMP_TIME) // special case: zero date
@@ -5042,7 +5053,7 @@ int Field_temporal::store(longlong nr, bool unsigned_val)
   MYSQL_TIME ltime;
   longlong tmp;
   THD *thd= table->in_use;
-  ErrConvInteger str(nr);
+  ErrConvInteger str(nr, unsigned_val);
 
   tmp= number_to_datetime(nr, 0, &ltime, (thd->variables.sql_mode &
                                        (MODE_NO_ZERO_IN_DATE |
@@ -5140,7 +5151,7 @@ int Field_time::store(double nr)
   bool neg= nr < 0;
   if (neg)
     nr= -nr;
-  int have_smth_to_conv= !number_to_time(neg, (longlong)nr,
+  int have_smth_to_conv= !number_to_time(neg, (ulonglong) nr,
                                          (ulong)((nr - floor(nr)) * TIME_SECOND_PART_FACTOR),
                                          &ltime, &was_cut);
 
@@ -5151,9 +5162,12 @@ int Field_time::store(double nr)
 int Field_time::store(longlong nr, bool unsigned_val)
 {
   MYSQL_TIME ltime;
-  ErrConvInteger str(nr);
+  ErrConvInteger str(nr, unsigned_val);
   int was_cut;
-  int have_smth_to_conv= !number_to_time(nr < 0, nr < 0 ? -nr : nr,
+  if (nr < 0 && unsigned_val)
+    nr= 99991231235959LL + 1;
+  int have_smth_to_conv= !number_to_time(nr < 0,
+                                         (ulonglong) (nr < 0 ? -nr : nr),
                                          0, &ltime, &was_cut);
 
   return store_TIME_with_warning(&ltime, &str, was_cut, have_smth_to_conv);
@@ -5520,7 +5534,8 @@ bool Field_year::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
   int tmp= (int) ptr[0];
   if (tmp || field_length != 4)
     tmp+= 1900;
-  return int_to_datetime_with_warn(tmp * 10000, ltime, fuzzydate, field_name);
+  return int_to_datetime_with_warn(false, tmp * 10000,
+                                    ltime, fuzzydate, field_name);
 }
 
 
diff --git a/sql/item.cc b/sql/item.cc
index 7a650ca75c7..b4d7bffae7b 100644
--- a/sql/item.cc
+++ b/sql/item.cc
@@ -1302,6 +1302,7 @@ bool Item::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
   case INT_RESULT:
   {
     longlong value= val_int();
+    bool neg= !unsigned_flag && value < 0;
     if (field_type() == MYSQL_TYPE_YEAR)
     {
       if (max_length == 2)
@@ -1313,7 +1314,8 @@ bool Item::get_date(MYSQL_TIME *ltime,ulonglong fuzzydate)
       }
       value*= 10000; /* make it YYYYMMHH */
     }
-    if (null_value || int_to_datetime_with_warn(value, ltime, fuzzydate,
+    if (null_value || int_to_datetime_with_warn(neg, neg ? -value : value,
+                                                ltime, fuzzydate,
                                                 field_name_or_null()))
       goto err;
     break;
diff --git a/sql/item_func.cc b/sql/item_func.cc
index c53ed81d761..bf45b41b2d9 100644
--- a/sql/item_func.cc
+++ b/sql/item_func.cc
@@ -1118,7 +1118,9 @@ bool Item_func_hybrid_result_type::get_date(MYSQL_TIME *ltime,
   case INT_RESULT:
   {
     longlong value= int_op();
-    if (null_value || int_to_datetime_with_warn(value, ltime, fuzzydate,
+    bool neg= !unsigned_flag && value < 0;
+    if (null_value || int_to_datetime_with_warn(neg, neg ? -value : value,
+                                                ltime, fuzzydate,
                                                 field_name_or_null()))
       goto err;
     break;
diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 14f964b3b6d..861da69a9be 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -4424,8 +4424,10 @@ bool Item_dyncol_get::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
   case DYN_COL_UINT:
     if (signed_value || val.x.ulong_value <= LONGLONG_MAX)
     {
-      if (int_to_datetime_with_warn(val.x.ulong_value, ltime, fuzzy_date,
-                                   0 /* TODO */))
+      bool neg= val.x.ulong_value > LONGLONG_MAX;
+      if (int_to_datetime_with_warn(neg, neg ? -val.x.ulong_value :
+                                                val.x.ulong_value,
+                                    ltime, fuzzy_date, 0 /* TODO */))
         goto null;
       return 0;
     }
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index cb0e638046d..15701658015 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1298,6 +1298,18 @@ bool get_interval_value(Item *args,interval_type int_type, INTERVAL *interval)
     if (!(val= args->val_decimal(&decimal_value)))
       return true;
     interval->neg= my_decimal2seconds(val, &second, &second_part);
+    if (second == LONGLONG_MAX)
+    {
+      char buff[DECIMAL_MAX_STR_LENGTH];
+      int length= sizeof(buff);
+      decimal2string(val, buff, &length, 0, 0, 0);
+      push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                          ER_TRUNCATED_WRONG_VALUE,
+                          ER(ER_TRUNCATED_WRONG_VALUE), "DECIMAL",
+                          buff);
+      return true;
+    }
+
     interval->second= second;
     interval->second_part= second_part;
     return false;
diff --git a/sql/sql_error.h b/sql/sql_error.h
index 9efd77328ea..5f912ad8102 100644
--- a/sql/sql_error.h
+++ b/sql/sql_error.h
@@ -544,11 +544,16 @@ public:
 
 class ErrConvInteger : public ErrConv
 {
-  longlong num;
+  longlong m_value;
+  bool m_unsigned;
 public:
-  ErrConvInteger(longlong num_arg) : ErrConv(), num(num_arg) {}
+  ErrConvInteger(longlong num_arg, bool unsigned_flag= false) :
+    ErrConv(), m_value(num_arg), m_unsigned(unsigned_flag) {}
   const char *ptr() const
-  { return llstr(num, err_buffer); }
+  {
+    return m_unsigned ? ullstr(m_value, err_buffer) :
+                         llstr(m_value, err_buffer);
+  }
 };
 
 class ErrConvDouble: public ErrConv
diff --git a/sql/sql_time.cc b/sql/sql_time.cc
index c696fc1344a..66fd9f49cb6 100644
--- a/sql/sql_time.cc
+++ b/sql/sql_time.cc
@@ -360,20 +360,23 @@ static bool number_to_time_with_warn(bool neg, ulonglong nr, ulong sec_part,
   int was_cut;
   longlong res;
   enum_field_types f_type;
+  bool have_warnings;
 
   if (fuzzydate & TIME_TIME_ONLY)
   {
     fuzzydate= TIME_TIME_ONLY; // clear other flags
     f_type= MYSQL_TYPE_TIME;
     res= number_to_time(neg, nr, sec_part, ltime, &was_cut);
+    have_warnings= MYSQL_TIME_WARN_HAVE_WARNINGS(was_cut);
   }
   else
   {
     f_type= MYSQL_TYPE_DATETIME;
     res= neg ? -1 : number_to_datetime(nr, sec_part, ltime, fuzzydate, &was_cut);
+    have_warnings= was_cut && (fuzzydate & TIME_NO_ZERO_IN_DATE);
   }
 
-  if (res < 0 || (was_cut && (fuzzydate & TIME_NO_ZERO_IN_DATE)))
+  if (res < 0 || have_warnings)
   {
     make_truncated_value_warning(current_thd,
                                  MYSQL_ERROR::WARN_LEVEL_WARN, str,
@@ -416,12 +419,11 @@ bool decimal_to_datetime_with_warn(const my_decimal *value, MYSQL_TIME *ltime,
 }
 
 
-bool int_to_datetime_with_warn(longlong value, MYSQL_TIME *ltime,
+bool int_to_datetime_with_warn(bool neg, ulonglong value, MYSQL_TIME *ltime,
                                ulonglong fuzzydate, const char *field_name)
 {
-  const ErrConvInteger str(value);
-  bool neg= value < 0;
-  return number_to_time_with_warn(neg, neg ? -value : value, 0, ltime,
+  const ErrConvInteger str(neg ? -value : value, !neg);
+  return number_to_time_with_warn(neg, value, 0, ltime,
                                   fuzzydate, &str, field_name);
 }
 
diff --git a/sql/sql_time.h b/sql/sql_time.h
index aa81edbc105..443d22c5419 100644
--- a/sql/sql_time.h
+++ b/sql/sql_time.h
@@ -48,7 +48,7 @@ bool double_to_datetime_with_warn(double value, MYSQL_TIME *ltime,
 bool decimal_to_datetime_with_warn(const my_decimal *value, MYSQL_TIME *ltime,
                                    ulonglong fuzzydate,
                                    const char *name);
-bool int_to_datetime_with_warn(longlong value, MYSQL_TIME *ltime,
+bool int_to_datetime_with_warn(bool neg, ulonglong value, MYSQL_TIME *ltime,
                                ulonglong fuzzydate,
                                const char *name);