Fix for bug #6914 "Problems using time()/date() output in expressions".

When we cast datetime value to DATE (TIME) type we should throw away its
time (date) part. This was not done properly if CAST() function was used
in datetime expressions.

2 files changed, 19 insertions, 0 deletions

diff --git a/mysql-test/r/cast.result b/mysql-test/r/cast.result
index ccf75f68e88..636e2603f9b 100644
--- a/mysql-test/r/cast.result
+++ b/mysql-test/r/cast.result
@@ -178,3 +178,12 @@ aaa	aa
 aab	aa
 aac	aa
 DROP TABLE t1;
+select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour);
+date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour)
+2004-12-30 00:00:00
+select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');
+timediff(cast('2004-12-30 12:00:00' as time), '12:00:00')
+00:00:00
+select timediff(cast('1 12:00:00' as time), '12:00:00');
+timediff(cast('1 12:00:00' as time), '12:00:00')
+24:00:00
diff --git a/mysql-test/t/cast.test b/mysql-test/t/cast.test
index e5681dedbac..23bba7d5aff 100644
--- a/mysql-test/t/cast.test
+++ b/mysql-test/t/cast.test
@@ -108,3 +108,13 @@ SELECT a, CAST(a AS CHAR(3)) FROM t1 ORDER BY CAST(a AS CHAR(2)), a;
 SELECT a, CAST(a AS UNSIGNED) FROM t1 ORDER BY CAST(a AS CHAR) ;
 SELECT a, CAST(a AS CHAR(2)) FROM t1 ORDER BY CAST(a AS CHAR(3)), a;
 DROP TABLE t1;
+
+#
+# Test for bug #6914 "Problems using time()/date() output in expressions".
+# When we are casting datetime value to DATE/TIME we should throw away
+# time/date parts (correspondingly).
+#
+select date_add(cast('2004-12-30 12:00:00' as date), interval 0 hour);
+select timediff(cast('2004-12-30 12:00:00' as time), '12:00:00');
+# Still we should not throw away "days" part of time value
+select timediff(cast('1 12:00:00' as time), '12:00:00');