Merge from 5.3

7 files changed, 167 insertions, 12 deletions

diff --git a/mysql-test/r/type_date.result b/mysql-test/r/type_date.result
index 03b942be3f6..4e299ecf0b1 100644
--- a/mysql-test/r/type_date.result
+++ b/mysql-test/r/type_date.result
@@ -349,6 +349,52 @@ Warning	1292	Incorrect datetime value: '0'
 Warning	1292	Incorrect datetime value: '0'
 SET @@timestamp=DEFAULT;
 #
+# MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value
+#
+SELECT CAST(TIME('-800:20:30') AS DATE);
+CAST(TIME('-800:20:30') AS DATE)
+NULL
+Warnings:
+Warning	1292	Truncated incorrect date value: '-800:20:30'
+SELECT CAST(TIME('800:20:30') AS DATE);
+CAST(TIME('800:20:30') AS DATE)
+0000-01-02
+SELECT CAST(TIME('33 08:20:30') AS DATE);
+CAST(TIME('33 08:20:30') AS DATE)
+0000-01-02
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES (TIME('800:20:30'));
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+INSERT INTO t1 VALUES (TIME('33 08:20:30'));
+Warnings:
+Note	1265	Data truncated for column 'a' at row 1
+SET SQL_MODE=NO_ZERO_IN_DATE;
+INSERT INTO t1 VALUES (TIME('48:20:30'));
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+SET SQL_MODE=DEFAULT;
+SELECT * FROM t1;
+a
+0000-01-02
+0000-01-02
+0000-00-00
+DROP TABLE t1;
+CREATE PROCEDURE test5041()
+BEGIN
+DECLARE t TIME;
+DECLARE d DATE;
+SET t= TIME('800:00:00');
+SET d= t;
+SELECT d;
+END;|
+call test5041();
+d
+0000-01-02
+Warnings:
+Note	1265	Data truncated for column 'd' at row 1
+drop procedure test5041;
+#
 # End of 5.3 tests
 #
 #
diff --git a/mysql-test/r/type_datetime.result b/mysql-test/r/type_datetime.result
index 51966147bdd..091ed216252 100644
--- a/mysql-test/r/type_datetime.result
+++ b/mysql-test/r/type_datetime.result
@@ -684,6 +684,46 @@ CONVERT_TZ(GREATEST(TIMESTAMP('2021-00-00'),TIMESTAMP('2022-00-00')),'+00:00','+
 NULL
 Warnings:
 Warning	1292	Incorrect datetime value: '2022-00-00 00:00:00'
+#
+# MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value
+#
+SELECT CAST(TIME('-800:20:30') AS DATETIME);
+CAST(TIME('-800:20:30') AS DATETIME)
+NULL
+Warnings:
+Warning	1292	Truncated incorrect datetime value: '-800:20:30'
+SELECT CAST(TIME('800:20:30') AS DATETIME);
+CAST(TIME('800:20:30') AS DATETIME)
+0000-01-02 08:20:30
+SELECT CAST(TIME('33 08:20:30') AS DATETIME);
+CAST(TIME('33 08:20:30') AS DATETIME)
+0000-01-02 08:20:30
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES (TIME('800:20:30'));
+INSERT INTO t1 VALUES (TIME('33 08:20:30'));
+SET SQL_MODE=NO_ZERO_IN_DATE;
+INSERT INTO t1 VALUES (TIME('48:20:30'));
+Warnings:
+Warning	1265	Data truncated for column 'a' at row 1
+SET SQL_MODE=DEFAULT;
+SELECT * FROM t1;
+a
+0000-01-02 08:20:30
+0000-01-02 08:20:30
+0000-00-00 00:00:00
+DROP TABLE t1;
+CREATE PROCEDURE test5041()
+BEGIN
+DECLARE t TIME;
+DECLARE dt DATETIME;
+SET t= TIME('800:20:30');
+SET dt= t;
+SELECT dt;
+END;|
+call test5041();
+dt
+0000-01-02 08:20:30
+drop procedure test5041;
 End of 5.3 tests
 #
 # Start of 5.5 tests
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 b8958d0e88c..feb8c0a983f 100644
--- a/mysql-test/suite/funcs_1/r/innodb_func_view.result
+++ b/mysql-test/suite/funcs_1/r/innodb_func_view.result
@@ -4302,10 +4302,12 @@ my_time, id FROM t1_values
 WHERE select_id = 35 OR select_id IS NULL order by id;
 CAST(my_time AS DATE)	my_time	id
 NULL	NULL	1
-0000-00-00	-838:59:59	2
-0000-00-00	838:59:59	3
+NULL	-838:59:59	2
+0000-01-03	838:59:59	3
 0000-00-00	13:00:00	4
 0000-00-00	10:00:00	5
+Warnings:
+Warning	1292	Truncated incorrect date value: '-838:59:59'
 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_time` as date) AS `CAST(my_time AS DATE)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -4314,10 +4316,12 @@ WHERE v1.id IN (SELECT id FROM t1_values
 WHERE select_id = 35 OR select_id IS NULL) order by id;
 CAST(my_time AS DATE)	my_time	id
 NULL	NULL	1
-0000-00-00	-838:59:59	2
-0000-00-00	838:59:59	3
+NULL	-838:59:59	2
+0000-01-03	838:59:59	3
 0000-00-00	13:00:00	4
 0000-00-00	10:00:00	5
+Warnings:
+Warning	1292	Truncated incorrect date value: '-838:59:59'
 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 9fa90797497..43703aa7ead 100644
--- a/mysql-test/suite/funcs_1/r/memory_func_view.result
+++ b/mysql-test/suite/funcs_1/r/memory_func_view.result
@@ -4303,10 +4303,12 @@ my_time, id FROM t1_values
 WHERE select_id = 35 OR select_id IS NULL order by id;
 CAST(my_time AS DATE)	my_time	id
 NULL	NULL	1
-0000-00-00	-838:59:59	2
-0000-00-00	838:59:59	3
+NULL	-838:59:59	2
+0000-01-03	838:59:59	3
 0000-00-00	13:00:00	4
 0000-00-00	10:00:00	5
+Warnings:
+Warning	1292	Truncated incorrect date value: '-838:59:59'
 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_time` as date) AS `CAST(my_time AS DATE)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -4315,10 +4317,12 @@ WHERE v1.id IN (SELECT id FROM t1_values
 WHERE select_id = 35 OR select_id IS NULL) order by id;
 CAST(my_time AS DATE)	my_time	id
 NULL	NULL	1
-0000-00-00	-838:59:59	2
-0000-00-00	838:59:59	3
+NULL	-838:59:59	2
+0000-01-03	838:59:59	3
 0000-00-00	13:00:00	4
 0000-00-00	10:00:00	5
+Warnings:
+Warning	1292	Truncated incorrect date value: '-838:59:59'
 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 9fa90797497..43703aa7ead 100644
--- a/mysql-test/suite/funcs_1/r/myisam_func_view.result
+++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result
@@ -4303,10 +4303,12 @@ my_time, id FROM t1_values
 WHERE select_id = 35 OR select_id IS NULL order by id;
 CAST(my_time AS DATE)	my_time	id
 NULL	NULL	1
-0000-00-00	-838:59:59	2
-0000-00-00	838:59:59	3
+NULL	-838:59:59	2
+0000-01-03	838:59:59	3
 0000-00-00	13:00:00	4
 0000-00-00	10:00:00	5
+Warnings:
+Warning	1292	Truncated incorrect date value: '-838:59:59'
 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_time` as date) AS `CAST(my_time AS DATE)`,`t1_values`.`my_time` AS `my_time`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -4315,10 +4317,12 @@ WHERE v1.id IN (SELECT id FROM t1_values
 WHERE select_id = 35 OR select_id IS NULL) order by id;
 CAST(my_time AS DATE)	my_time	id
 NULL	NULL	1
-0000-00-00	-838:59:59	2
-0000-00-00	838:59:59	3
+NULL	-838:59:59	2
+0000-01-03	838:59:59	3
 0000-00-00	13:00:00	4
 0000-00-00	10:00:00	5
+Warnings:
+Warning	1292	Truncated incorrect date value: '-838:59:59'
 DROP VIEW v1;
 
 
diff --git a/mysql-test/t/type_date.test b/mysql-test/t/type_date.test
index 6cfd9711c3e..0b2ce16b7cc 100644
--- a/mysql-test/t/type_date.test
+++ b/mysql-test/t/type_date.test
@@ -322,6 +322,34 @@ SELECT
 SET @@timestamp=DEFAULT;
 
 --echo #
+--echo # MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value
+--echo #
+SELECT CAST(TIME('-800:20:30') AS DATE);
+SELECT CAST(TIME('800:20:30') AS DATE);
+SELECT CAST(TIME('33 08:20:30') AS DATE);
+CREATE TABLE t1 (a DATE);
+INSERT INTO t1 VALUES (TIME('800:20:30'));
+INSERT INTO t1 VALUES (TIME('33 08:20:30'));
+SET SQL_MODE=NO_ZERO_IN_DATE;
+INSERT INTO t1 VALUES (TIME('48:20:30'));
+SET SQL_MODE=DEFAULT;
+SELECT * FROM t1;
+DROP TABLE t1;
+DELIMITER |;
+CREATE PROCEDURE test5041()
+BEGIN
+  DECLARE t TIME;
+  DECLARE d DATE;
+  SET t= TIME('800:00:00');
+  SET d= t;
+  SELECT d;
+END;|
+DELIMITER ;|
+call test5041();
+drop procedure test5041;
+
+
+--echo #
 --echo # End of 5.3 tests
 --echo #
 
diff --git a/mysql-test/t/type_datetime.test b/mysql-test/t/type_datetime.test
index a1c0509666a..45aca47bd03 100644
--- a/mysql-test/t/type_datetime.test
+++ b/mysql-test/t/type_datetime.test
@@ -491,6 +491,35 @@ drop table t1,t2;
 --echo #
 SELECT CONVERT_TZ(GREATEST(TIMESTAMP('2021-00-00'),TIMESTAMP('2022-00-00')),'+00:00','+7:5');
 
+
+--echo #
+--echo # MDEV-5041 Inserting a TIME with hour>24 into a DATETIME column produces a wrong value
+--echo #
+SELECT CAST(TIME('-800:20:30') AS DATETIME);
+SELECT CAST(TIME('800:20:30') AS DATETIME);
+SELECT CAST(TIME('33 08:20:30') AS DATETIME);
+CREATE TABLE t1 (a DATETIME);
+INSERT INTO t1 VALUES (TIME('800:20:30'));
+INSERT INTO t1 VALUES (TIME('33 08:20:30'));
+SET SQL_MODE=NO_ZERO_IN_DATE;
+INSERT INTO t1 VALUES (TIME('48:20:30'));
+SET SQL_MODE=DEFAULT;
+SELECT * FROM t1;
+DROP TABLE t1;
+DELIMITER |;
+CREATE PROCEDURE test5041()
+BEGIN
+  DECLARE t TIME;
+  DECLARE dt DATETIME;
+  SET t= TIME('800:20:30');
+  SET dt= t;
+  SELECT dt;
+END;|
+DELIMITER ;|
+call test5041();
+drop procedure test5041;
+
+
 --echo End of 5.3 tests
 
 --echo #