bk commit into 4.1 tree (dlenev:1.2148) BUG#8068

[dlenev]

Below is the list of changes that have just been committed into a local
4.1 repository of dlenev. When dlenev does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet
  1.2148 05/03/28 16:20:55 dlenev@stripped +3 -0
  Fix for bug #8068 "TIMEDIFF with first negative argument gives wrong 
  result" (and similar bug in ADDTIME/SUBTIME).
  
  Both Item_func_add_time/Item_func_timediff::val_str() now use
  calc_time_diff() function which was backported from 5.0 (and which
  was was fixed to properly handle microseconds part of its second 
  argument). Also now we correctly set sign of result in case when
  first argument is negative and second is positive.  

  sql/item_timefunc.cc
    1.84 05/03/28 16:20:50 dlenev@stripped +103 -68
    - Backported calc_time_diff() function from 5.0 tree. Changed it to
      accept time value as its second argument when its first argument
      is datetime value. Fixed wrong handling of microsecond part of
      second argument.
    - Item_func_add_time::val_str()/Item_func_timediff::val_str()
      Removed similar pieces of code calculating difference 
      between two datetime values (or their sum) in microseconds.
      Now we use calc_time_diff() function instead. 
      Also now we correctly set sign of result in case when first 
      argument is negative and second is positive.

  mysql-test/t/func_sapdb.test
    1.7 05/03/28 16:20:50 dlenev@stripped +1 -0
    Added test for bug #8068 "TIMEDIFF with first negative argument gives wrong 
    result".

  mysql-test/r/func_sapdb.result
    1.8 05/03/28 16:20:50 dlenev@stripped +3 -0
    Added test for bug #8068 "TIMEDIFF with first negative argument gives wrong 
    result".

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	dlenev
# Host:	brandersnatch.localdomain
# Root:	/home/dlenev/src/mysql-4.1-bg8086

--- 1.83/sql/item_timefunc.cc	Fri Jan 28 09:32:34 2005
+++ 1.84/sql/item_timefunc.cc	Mon Mar 28 16:20:50 2005
@@ -761,6 +761,81 @@
 }
 
 
+/*
+  Calculate difference between two datetime values as seconds + microseconds.
+
+  SYNOPSIS
+    calc_time_diff()
+      l_time1         - TIME/DATE/DATETIME value
+      l_time2         - TIME/DATE/DATETIME value
+      l_sign          - 1 absolute values are substracted,
+                        -1 absolute values are added.
+      seconds_out     - Out parameter where difference between
+                        l_time1 and l_time2 in seconds is stored.
+      microseconds_out- Out parameter where microsecond part of difference
+                        between l_time1 and l_time2 is stored.
+
+  NOTE
+    This function calculates difference between l_time1 and l_time2 absolute
+    values. So one should set l_sign and correct result if he want to take
+    signs into account (i.e. for TIME values).
+
+  RETURN VALUES
+    Returns sign of difference.
+    1 means negative result
+    0 means positive result
+
+*/
+
+static bool calc_time_diff(TIME *l_time1, TIME *l_time2, int l_sign,
+                           longlong *seconds_out, long *microseconds_out)
+{
+  long days;
+  bool neg;
+  longlong microseconds;
+
+  /*
+    We suppose that if first argument is MYSQL_TIMESTAMP_TIME
+    the second argument should be TIMESTAMP_TIME also.
+    We should check it before calc_time_diff call.
+  */
+  if (l_time1->time_type == MYSQL_TIMESTAMP_TIME)  // Time value
+    days= l_time1->day - l_sign*l_time2->day;
+  else
+  {
+    days= calc_daynr((uint) l_time1->year,
+		     (uint) l_time1->month,
+		     (uint) l_time1->day);
+    if (l_time2->time_type == MYSQL_TIMESTAMP_TIME)
+      days-= l_sign*l_time2->day;
+    else
+      days-= l_sign*calc_daynr((uint) l_time2->year,
+			       (uint) l_time2->month,
+			       (uint) l_time2->day);
+  }
+
+  microseconds= ((longlong)days*LL(86400) +
+                 (longlong)(l_time1->hour*3600L +
+                            l_time1->minute*60L +
+                            l_time1->second) -
+                 l_sign*(longlong)(l_time2->hour*3600L +
+                                   l_time2->minute*60L +
+                                   l_time2->second)) * LL(1000000) +
+                (longlong)l_time1->second_part -
+                l_sign*(longlong)l_time2->second_part;
+
+  neg= 0;
+  if (microseconds < 0)
+  {
+    microseconds= -microseconds;
+    neg= 1;
+  }
+  *seconds_out= microseconds/1000000L;
+  *microseconds_out= (long) (microseconds%1000000L);
+  return neg;
+}
+
+
 longlong Item_func_period_add::val_int()
 {
   DBUG_ASSERT(fixed == 1);
@@ -2332,11 +2407,11 @@
   DBUG_ASSERT(fixed == 1);
   TIME l_time1, l_time2, l_time3;
   bool is_time= 0;
-  long microseconds, seconds, days= 0;
+  long days, microseconds;
+  longlong seconds;
   int l_sign= sign;
 
   null_value=0;
-  l_time3.neg= 0;
   if (is_date)                        // TIMESTAMP function
   {
     if (get_arg0_date(&l_time1,1) || 
@@ -2352,51 +2427,26 @@
         l_time2.time_type == MYSQL_TIMESTAMP_DATETIME)
       goto null_date;
     is_time= (l_time1.time_type == MYSQL_TIMESTAMP_TIME);
-    if (is_time && (l_time2.neg == l_time1.neg && l_time1.neg))
-      l_time3.neg= 1;
   }
   if (l_time1.neg != l_time2.neg)
     l_sign= -l_sign;
 
-  microseconds= l_time1.second_part + l_sign*l_time2.second_part;
-  seconds= (l_time1.hour*3600L + l_time1.minute*60L + l_time1.second +
-	    (l_time2.day*86400L + l_time2.hour*3600L +
-	     l_time2.minute*60L + l_time2.second)*l_sign);
-  if (is_time)
-    seconds+= l_time1.day*86400L;
-  else
-    days+= calc_daynr((uint) l_time1.year,(uint) l_time1.month,
-		      (uint) l_time1.day);
-  seconds= seconds + microseconds/1000000L;
-  microseconds= microseconds%1000000L;
-  days+= seconds/86400L;
-  seconds= seconds%86400L;
+  l_time3.neg= calc_time_diff(&l_time1, &l_time2, -l_sign,
+			      &seconds, &microseconds);
 
-  if (microseconds < 0)
-  {
-    microseconds+= 1000000L;
-    seconds--;
-  }
-  if (seconds < 0)
-  {
-    days+= seconds/86400L - 1;
-    seconds+= 86400L;
-  }
-  if (days < 0)
-  {
-    if (!is_time)
-      goto null_date;
-    if (microseconds)
-    {
-      microseconds= 1000000L - microseconds;
-      seconds++; 
-    }
-    seconds= 86400L - seconds;
-    days= -(++days);
-    l_time3.neg= 1;
-  }
+  /*
+    If first argument was negative and diff between arguments
+    is non-zero we need to swap sign to get proper result.
+  */
+  if (l_time1.neg && (seconds || microseconds))
+    l_time3.neg= 1-l_time3.neg;         // Swap sign of result
 
-  calc_time_from_sec(&l_time3, seconds, microseconds);
+  if (!is_time && l_time3.neg)
+    goto null_date;
+
+  days= (long)(seconds/86400L);
+
+  calc_time_from_sec(&l_time3, (long)(seconds%86400L), microseconds);
   if (!is_time)
   {
     get_date_from_daynr(days,&l_time3.year,&l_time3.month,&l_time3.day);
@@ -2452,8 +2502,8 @@
 String *Item_func_timediff::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
-  longlong microseconds;
-  long days;
+  longlong seconds;
+  long microseconds;
   int l_sign= 1;
   TIME l_time1 ,l_time2, l_time3;
 
@@ -2466,33 +2516,18 @@
   if (l_time1.neg != l_time2.neg)
     l_sign= -l_sign;
 
-  if (l_time1.time_type == MYSQL_TIMESTAMP_TIME) // Time value
-    days= l_time1.day - l_sign*l_time2.day;
-  else                                      // DateTime value
-    days= (calc_daynr((uint) l_time1.year,
-		      (uint) l_time1.month,
-		      (uint) l_time1.day) - 
-	   l_sign*calc_daynr((uint) l_time2.year,
-			     (uint) l_time2.month,
-			     (uint) l_time2.day));
-
-  microseconds= ((longlong)days*86400L +
-                 l_time1.hour*3600L + l_time1.minute*60L + l_time1.second -
-                 (longlong)l_sign*(l_time2.hour*3600L + l_time2.minute*60L +
-                                   l_time2.second))*1000000 +
-                 l_time1.second_part - l_sign*l_time2.second_part;
+  l_time3.neg= calc_time_diff(&l_time1, &l_time2, l_sign,
+			      &seconds, &microseconds);
 
-  l_time3.neg= 0;
-  if (microseconds < 0)
-  {
-    microseconds= -microseconds;
-    l_time3.neg= 1;
-  }
-  if ((l_time2.neg == l_time1.neg) && l_time1.neg && microseconds)
-    l_time3.neg= l_time3.neg ? 0 : 1;
+  /*
+    For MYSQL_TIMESTAMP_TIME only:
+      If first argument was negative and diff between arguments
+      is non-zero we need to swap sign to get proper result.
+  */
+  if (l_time1.neg && (seconds || microseconds))
+    l_time3.neg= 1-l_time3.neg;         // Swap sign of result
 
-  calc_time_from_sec(&l_time3, (long)(microseconds/1000000),
-                               (long)(microseconds%1000000));
+  calc_time_from_sec(&l_time3, (long) seconds, microseconds);
 
   if (!make_datetime(l_time1.second_part || l_time2.second_part ?
 		     TIME_MICROSECOND : TIME_ONLY,

--- 1.7/mysql-test/r/func_sapdb.result	Wed Jan 12 12:18:33 2005
+++ 1.8/mysql-test/r/func_sapdb.result	Mon Mar 28 16:20:50 2005
@@ -185,6 +185,7 @@
 ('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"),
 ('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'),
 ('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null),
+('2001-01-01 01:01:01', '-01:01:01', '1 01:01:01', '2001-01-01 01:01:01'),
 ('2001-01-01 01:01:01', null, '-1 01:01:01', null),
 (null, null, null, null),
 ('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01');
@@ -194,6 +195,7 @@
 2001-01-01 00:00:00	-25:01:00
 1997-12-31 00:00:00	-25:01:00
 2001-01-01 02:02:02	-24:00:00
[phone]:00:00	24:00:00
 NULL	NULL
 NULL	NULL
 2001-01-01 02:02:02	26:02:02
@@ -203,6 +205,7 @@
 26305:01:02	22:58:58
 -26305:01:02	-22:58:58
 NULL	26:02:02
+00:00:00	-26:02:02
 NULL	NULL
 NULL	NULL
 00:00:00	-24:00:00

--- 1.6/mysql-test/t/func_sapdb.test	Wed Jan 12 12:18:33 2005
+++ 1.7/mysql-test/t/func_sapdb.test	Mon Mar 28 16:20:50 2005
@@ -100,6 +100,7 @@
 ('2001-01-01 01:01:01', '-01:01:01', '-23:59:59', "1997-12-31 23:59:59.000001"),
 ('1997-12-31 23:59:59.000001', '-23:59:59', '-01:01:01', '2001-01-01 01:01:01'),
 ('2001-01-01 01:01:01', '01:01:01', '-1 01:01:01', null),
+('2001-01-01 01:01:01', '-01:01:01', '1 01:01:01', '2001-01-01 01:01:01'),
 ('2001-01-01 01:01:01', null, '-1 01:01:01', null),
 (null, null, null, null),
 ('2001-01-01 01:01:01', '01:01:01', '1 01:01:01', '2001-01-01 01:01:01');