MDEV-8658 DATE(zerofill_column) and DATE(COALESCE(zerofill_column)) return different results

MDEV-8660 TIME(int_zerofill_column) returns a wrong result

1 files changed, 26 insertions, 30 deletions

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 a37213fdcaa..df57c83164e 100644
--- a/mysql-test/suite/funcs_1/r/myisam_func_view.result
+++ b/mysql-test/suite/funcs_1/r/myisam_func_view.result
@@ -3744,14 +3744,14 @@ my_bigint, id FROM t1_values
 WHERE select_id = 52 OR select_id IS NULL order by id;
 CAST(my_bigint AS TIME)	my_bigint	id
 NULL	NULL	1
-NULL	-9223372036854775808	2
-NULL	9223372036854775807	3
+-838:59:59	-9223372036854775808	2
+838:59:59	9223372036854775807	3
 00:00:00	0	4
 -00:00:01	-1	5
 00:17:58	1758	24
 Warnings:
-Warning	1292	Truncated incorrect time value: '-9223372036854775808'
-Warning	1292	Truncated incorrect time value: '9223372036854775807'
+Warning	1292	Incorrect time value: '-9223372036854775808' for column 'my_bigint' at row 2
+Warning	1292	Incorrect time value: '9223372036854775807' for column 'my_bigint' 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_bigint` as time) AS `CAST(my_bigint AS TIME)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -3760,14 +3760,14 @@ WHERE v1.id IN (SELECT id FROM t1_values
 WHERE select_id = 52 OR select_id IS NULL) order by id;
 CAST(my_bigint AS TIME)	my_bigint	id
 NULL	NULL	1
-NULL	-9223372036854775808	2
-NULL	9223372036854775807	3
+-838:59:59	-9223372036854775808	2
+838:59:59	9223372036854775807	3
 00:00:00	0	4
 -00:00:01	-1	5
 00:17:58	1758	24
 Warnings:
-Warning	1292	Truncated incorrect time value: '-9223372036854775808'
-Warning	1292	Truncated incorrect time value: '9223372036854775807'
+Warning	1292	Incorrect time value: '-9223372036854775808' for column 'my_bigint' at row 1
+Warning	1292	Incorrect time value: '9223372036854775807' for column 'my_bigint' at row 1
 DROP VIEW v1;
 
 
@@ -4096,15 +4096,14 @@ CAST(my_bigint AS DATETIME)	my_bigint	id
 NULL	NULL	1
 NULL	-9223372036854775808	2
 NULL	9223372036854775807	3
-NULL	0	4
+0000-00-00 00:00:00	0	4
 NULL	-1	5
 NULL	200506271758	18
 Warnings:
-Warning	1292	Incorrect datetime value: '-9223372036854775808'
-Warning	1292	Incorrect datetime value: '9223372036854775807'
-Warning	1292	Incorrect datetime value: '0'
-Warning	1292	Incorrect datetime value: '-1'
-Warning	1292	Incorrect datetime value: '200506271758'
+Warning	1292	Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 2
+Warning	1292	Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 3
+Warning	1292	Incorrect datetime value: '-1' for column 'my_bigint' at row 5
+Warning	1292	Incorrect datetime value: '200506271758' for column 'my_bigint' at row 6
 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_bigint` as datetime) AS `CAST(my_bigint AS DATETIME)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -4115,15 +4114,14 @@ CAST(my_bigint AS DATETIME)	my_bigint	id
 NULL	NULL	1
 NULL	-9223372036854775808	2
 NULL	9223372036854775807	3
-NULL	0	4
+0000-00-00 00:00:00	0	4
 NULL	-1	5
 NULL	200506271758	18
 Warnings:
-Warning	1292	Incorrect datetime value: '-9223372036854775808'
-Warning	1292	Incorrect datetime value: '9223372036854775807'
-Warning	1292	Incorrect datetime value: '0'
-Warning	1292	Incorrect datetime value: '-1'
-Warning	1292	Incorrect datetime value: '200506271758'
+Warning	1292	Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 1
+Warning	1292	Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 1
+Warning	1292	Incorrect datetime value: '-1' for column 'my_bigint' at row 1
+Warning	1292	Incorrect datetime value: '200506271758' for column 'my_bigint' at row 1
 DROP VIEW v1;
 
 
@@ -4456,14 +4454,13 @@ CAST(my_bigint AS DATE)	my_bigint	id
 NULL	NULL	1
 NULL	-9223372036854775808	2
 NULL	9223372036854775807	3
-NULL	0	4
+0000-00-00	0	4
 NULL	-1	5
 2005-06-27	20050627	12
 Warnings:
-Warning	1292	Incorrect datetime value: '-9223372036854775808'
-Warning	1292	Incorrect datetime value: '9223372036854775807'
-Warning	1292	Incorrect datetime value: '0'
-Warning	1292	Incorrect datetime value: '-1'
+Warning	1292	Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 2
+Warning	1292	Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 3
+Warning	1292	Incorrect datetime value: '-1' for column 'my_bigint' at row 5
 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_bigint` as date) AS `CAST(my_bigint AS DATE)`,`t1_values`.`my_bigint` AS `my_bigint`,`t1_values`.`id` AS `id` from `t1_values`	latin1	latin1_swedish_ci
@@ -4474,14 +4471,13 @@ CAST(my_bigint AS DATE)	my_bigint	id
 NULL	NULL	1
 NULL	-9223372036854775808	2
 NULL	9223372036854775807	3
-NULL	0	4
+0000-00-00	0	4
 NULL	-1	5
 2005-06-27	20050627	12
 Warnings:
-Warning	1292	Incorrect datetime value: '-9223372036854775808'
-Warning	1292	Incorrect datetime value: '9223372036854775807'
-Warning	1292	Incorrect datetime value: '0'
-Warning	1292	Incorrect datetime value: '-1'
+Warning	1292	Incorrect datetime value: '-9223372036854775808' for column 'my_bigint' at row 1
+Warning	1292	Incorrect datetime value: '9223372036854775807' for column 'my_bigint' at row 1
+Warning	1292	Incorrect datetime value: '-1' for column 'my_bigint' at row 1
 DROP VIEW v1;