bzr commit into mysql-5.0-bugteam branch (azundris:2736) Bug#42525

[Tatiana A. Nurnberg]

#At file:///misc/mysql/forest/42525/50-42525/ based on revid:azundris@stripped

 2736 Tatiana A. Nurnberg	2009-02-04
      Bug#42525: TIMEDIFF function
      
      Combination of time-arithmetic result-types led to string of form
      "12:34:56" being parsed as normal int-string, causing parsing to
      be aborted at first unexpected character ":".
      
      Make return-type declaration less broad; handle signedness correctly.
modified:
  mysql-test/r/func_sapdb.result
  mysql-test/t/func_sapdb.test
  sql/item_cmpfunc.cc
  sql/item_timefunc.h

per-file messages:
  mysql-test/r/func_sapdb.result
    More tests for casts of TIME() / TIMEDIFF() with negative results.
  mysql-test/t/func_sapdb.test
    More tests for casts of TIME() / TIMEDIFF() with negative results.
  sql/item_cmpfunc.cc
    Don't lose sign. You'd think there'd be a less ugly way to do this.
  sql/item_timefunc.h
    Code comments suggest longlong, but it actually breaks things. Bah.
=== modified file 'mysql-test/r/func_sapdb.result'
--- a/mysql-test/r/func_sapdb.result	2008-11-26 08:28:17 +0000
+++ b/mysql-test/r/func_sapdb.result	2009-02-04 18:59:34 +0000
@@ -268,3 +268,17 @@ timediff('2008-09-29 20:10:10','2008-09-
 SELECT CAST(time('-73:42:12') AS DECIMAL);
 CAST(time('-73:42:12') AS DECIMAL)
 -734212
+SELECT TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1Eq,
+TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1NEq1,
+TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1NEq2,
+TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=     '00:00:00'  AS 2Eq,
+TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=     '00:00:00'  AS 2NEq1,
+TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=     '00:00:00'  AS 2NEq2,
+TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME(0)          AS 3Eq,
+TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=TIME(0)          AS 3NEq1,
+TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=TIME(0)          AS 3NEq2,
+TIME(0) AS Time0, TIME('00:00:00') AS Time00, '00:00:00' AS Literal0000,
+TIMEDIFF(TIME('17:59:00'),TIME('17:00:00')),
+TIMEDIFF(TIME('17:00:00'),TIME('17:59:00'));
+1Eq	1NEq1	1NEq2	2Eq	2NEq1	2NEq2	3Eq	3NEq1	3NEq2	Time0	Time00	Literal0000	TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))	TIMEDIFF(TIME('17:00:00'),TIME('17:59:00'))
[phone]:00:00	00:00:00	00:00:00	00:59:00	-00:59:00

=== modified file 'mysql-test/t/func_sapdb.test'
--- a/mysql-test/t/func_sapdb.test	2008-11-26 08:28:17 +0000
+++ b/mysql-test/t/func_sapdb.test	2009-02-04 18:59:34 +0000
@@ -151,4 +151,22 @@ select timediff('2008-09-29 20:10:10','2
 SELECT CAST(time('-73:42:12') AS DECIMAL);
 
 
+
+#
+# Bug#42525 - TIMEDIFF function
+#
+
+SELECT TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1Eq,
+       TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1NEq1,
+       TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=TIME('00:00:00') AS 1NEq2,
+       TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=     '00:00:00'  AS 2Eq,
+       TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=     '00:00:00'  AS 2NEq1,
+       TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=     '00:00:00'  AS 2NEq2,
+       TIMEDIFF(TIME('17:00:00'),TIME('17:00:00'))=TIME(0)          AS 3Eq,
+       TIMEDIFF(TIME('17:59:00'),TIME('17:00:00'))=TIME(0)          AS 3NEq1,
+       TIMEDIFF(TIME('18:00:00'),TIME('17:00:00'))=TIME(0)          AS 3NEq2,
+       TIME(0) AS Time0, TIME('00:00:00') AS Time00, '00:00:00' AS Literal0000,
+       TIMEDIFF(TIME('17:59:00'),TIME('17:00:00')),
+       TIMEDIFF(TIME('17:00:00'),TIME('17:59:00'));
+
 # End of 5.0 tests

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2009-01-15 11:08:09 +0000
+++ b/sql/item_cmpfunc.cc	2009-02-04 18:59:34 +0000
@@ -761,7 +761,9 @@ get_time_value(THD *thd, Item ***item_ar
   else
   {
     *is_null= item->get_time(&ltime);
-    value= !*is_null ? (longlong) TIME_to_ulonglong_datetime(&ltime) : 0;
+    value= !*is_null ? (longlong) TIME_to_ulonglong_datetime(&ltime) *
+                                  (ltime.neg ? -1 : 1 )
+                     : 0;
   }
   /*
     Do not cache GET_USER_VAR() function as its const_item() may return TRUE

=== modified file 'sql/item_timefunc.h'
--- a/sql/item_timefunc.h	2008-12-23 14:08:04 +0000
+++ b/sql/item_timefunc.h	2009-02-04 18:59:34 +0000
@@ -398,7 +398,6 @@ public:
   {
     return save_time_in_field(field);
   }
-  bool result_as_longlong() { return TRUE; }
 };