bzr commit into mysql-5.0-bugteam branch (azundris:2725) Bug#37553

[Tatiana A. Nurnberg]

#At file:///misc/mysql/forest/37553/50-37553/

 2725 Tatiana A. Nurnberg	2008-11-26
      Bug#37553: MySql Error Compare TimeDiff & Time
      
      We pretended that TIMEDIFF() would always return positive results;
      this gave strange results in comparisons of the TIMEDIFF(low,hi)<TIME(0)
      type that rendered a negative result, but still gave false in comparison.
      We also inadvertantly dropped the sign when converting times to
      decimal.
      
      CAST(time AS DECIMAL) handles signs of the times correctly.
      TIMEDIFF() marked up as signed. Time/date comparison code switched to
      signed for clarity.
modified:
  mysql-test/r/func_sapdb.result
  mysql-test/t/func_sapdb.test
  sql/item_cmpfunc.cc
  sql/item_cmpfunc.h
  sql/item_func.cc
  sql/item_timefunc.h
  sql/my_decimal.cc
  sql/mysql_priv.h

per-file messages:
  mysql-test/r/func_sapdb.result
    show that time-related comparisons work with negative
    time values now.
    show that converting time to DECIMAL no longer drops sign.
  mysql-test/t/func_sapdb.test
    show that time-related comparisons work with negative
    time values now.
    show that converting time to DECIMAL no longer drops sign.
  sql/item_cmpfunc.cc
    signed returns
  sql/item_cmpfunc.h
    signed now (time/date < > =)
  sql/item_func.cc
    signed now
  sql/item_timefunc.h
    Functions such as TIMEDIFF() return signed results!
    The file-comments pretended we were doing that all along, anyway...
  sql/my_decimal.cc
    heed sign when converting time to my_decimal;
    times may actually be negative!
    Needed for SELECT CAST(time('-73:42:12') AS DECIMAL);
  sql/mysql_priv.h
    using signed for dates and times now
=== modified file 'mysql-test/r/func_sapdb.result'
--- a/mysql-test/r/func_sapdb.result	2007-10-23 13:51:43 +0000
+++ b/mysql-test/r/func_sapdb.result	2008-11-26 08:28:17 +0000
@@ -256,3 +256,15 @@ a
 select str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f");
 str_to_date("2003-01-02 10:11:12.0012", "%Y-%m-%d %H:%i:%S.%f")
 2003-01-02 10:11:12.001200
+select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10'),time('00:00:00');
+timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')	time('00:00:00')
+-24:00:00	00:00:00
+select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00');
+timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00')
+0
+select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00');
+timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00')
+1
+SELECT CAST(time('-73:42:12') AS DECIMAL);
+CAST(time('-73:42:12') AS DECIMAL)
+-734212

=== modified file 'mysql-test/t/func_sapdb.test'
--- a/mysql-test/t/func_sapdb.test	2007-10-12 09:46:48 +0000
+++ b/mysql-test/t/func_sapdb.test	2008-11-26 08:28:17 +0000
@@ -135,3 +135,20 @@ select str_to_date("2003-01-02 10:11:12.
 --enable_ps_protocol
 
 # End of 4.1 tests
+
+
+
+#
+# Bug#37553: MySql Error Compare TimeDiff & Time
+#
+
+# calculations involving negative time values ignored sign
+select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10'),time('00:00:00');
+select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')>time('00:00:00');
+select timediff('2008-09-29 20:10:10','2008-09-30 20:10:10')<time('00:00:00');
+
+# show that conversion to DECIMAL no longer drops sign
+SELECT CAST(time('-73:42:12') AS DECIMAL);
+
+
+# End of 5.0 tests

=== modified file 'sql/item_cmpfunc.cc'
--- a/sql/item_cmpfunc.cc	2008-11-21 12:39:59 +0000
+++ b/sql/item_cmpfunc.cc	2008-11-26 08:28:17 +0000
@@ -745,11 +745,11 @@ Arg_comparator::can_compare_as_dates(Ite
     obtained value
 */
 
-ulonglong
+longlong
 get_time_value(THD *thd, Item ***item_arg, Item **cache_arg,
                Item *warn_item, bool *is_null)
 {
-  ulonglong value;
+  longlong value;
   Item *item= **item_arg;
   MYSQL_TIME ltime;
 
@@ -761,7 +761,7 @@ get_time_value(THD *thd, Item ***item_ar
   else
   {
     *is_null= item->get_time(&ltime);
-    value= !*is_null ? TIME_to_ulonglong_datetime(&ltime) : 0;
+    value= !*is_null ? (longlong) TIME_to_ulonglong_datetime(&ltime) : 0;
   }
   /*
     Do not cache GET_USER_VAR() function as its const_item() may return TRUE
@@ -886,11 +886,11 @@ void Arg_comparator::set_datetime_cmp_fu
     obtained value
 */
 
-ulonglong
+longlong
 get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
                    Item *warn_item, bool *is_null)
 {
-  ulonglong value= 0;
+  longlong value= 0;
   String buf, *str= 0;
   Item *item= **item_arg;
 
@@ -925,7 +925,7 @@ get_datetime_value(THD *thd, Item ***ite
     enum_field_types f_type= warn_item->field_type();
     timestamp_type t_type= f_type ==
       MYSQL_TYPE_DATE ? MYSQL_TIMESTAMP_DATE : MYSQL_TIMESTAMP_DATETIME;
-    value= get_date_from_str(thd, str, t_type, warn_item->name, &error);
+    value= (longlong) get_date_from_str(thd, str, t_type, warn_item->name, &error);
     /*
       If str did not contain a valid date according to the current
       SQL_MODE, get_date_from_str() has already thrown a warning,
@@ -979,7 +979,7 @@ get_datetime_value(THD *thd, Item ***ite
 int Arg_comparator::compare_datetime()
 {
   bool a_is_null, b_is_null;
-  ulonglong a_value, b_value;
+  longlong a_value, b_value;
 
   /* Get DATE/DATETIME/TIME value of the 'a' item. */
   a_value= (*get_value_func)(thd, &a, &a_cache, *b, &a_is_null);

=== modified file 'sql/item_cmpfunc.h'
--- a/sql/item_cmpfunc.h	2008-09-05 08:30:01 +0000
+++ b/sql/item_cmpfunc.h	2008-11-26 08:28:17 +0000
@@ -42,8 +42,8 @@ class Arg_comparator: public Sql_alloc
   bool is_nulls_eq;                // TRUE <=> compare for the EQUAL_FUNC
   enum enum_date_cmp_type { CMP_DATE_DFLT= 0, CMP_DATE_WITH_DATE,
                             CMP_DATE_WITH_STR, CMP_STR_WITH_DATE };
-  ulonglong (*get_value_func)(THD *thd, Item ***item_arg, Item **cache_arg,
-                              Item *warn_item, bool *is_null);
+  longlong (*get_value_func)(THD *thd, Item ***item_arg, Item **cache_arg,
+                             Item *warn_item, bool *is_null);
 public:
   DTCollation cmp_collation;
 
@@ -1028,7 +1028,7 @@ public:
 */
 class cmp_item_datetime :public cmp_item
 {
-  ulonglong value;
+  longlong value;
 public:
   THD *thd;
   /* Item used for issuing warnings. */

=== modified file 'sql/item_func.cc'
--- a/sql/item_func.cc	2008-11-18 11:10:52 +0000
+++ b/sql/item_func.cc	2008-11-26 08:28:17 +0000
@@ -2283,7 +2283,7 @@ uint Item_func_min_max::cmp_datetimes(ul
   {
     Item **arg= args + i;
     bool is_null;
-    ulonglong res= get_datetime_value(thd, &arg, 0, datetime_item, &is_null);
+    longlong res= get_datetime_value(thd, &arg, 0, datetime_item, &is_null);
     if ((null_value= args[i]->null_value))
       return 0;
     if (i == 0 || (res < min_max ? cmp_sign : -cmp_sign) > 0)

=== modified file 'sql/item_timefunc.h'
--- a/sql/item_timefunc.h	2008-01-07 16:55:05 +0000
+++ b/sql/item_timefunc.h	2008-11-26 08:28:17 +0000
@@ -408,6 +408,7 @@ public:
   {
     return save_time_in_field(field);
   }
+  bool result_as_longlong() { return TRUE; }
 };
 
 

=== modified file 'sql/my_decimal.cc'
--- a/sql/my_decimal.cc	2007-10-05 07:38:57 +0000
+++ b/sql/my_decimal.cc	2008-11-26 08:28:17 +0000
@@ -216,7 +216,7 @@ my_decimal *date2my_decimal(MYSQL_TIME *
   date = (ltime->year*100L + ltime->month)*100L + ltime->day;
   if (ltime->time_type > MYSQL_TIMESTAMP_DATE)
     date= ((date*100L + ltime->hour)*100L+ ltime->minute)*100L + ltime->second;
-  if (int2my_decimal(E_DEC_FATAL_ERROR, date, FALSE, dec))
+  if (int2my_decimal(E_DEC_FATAL_ERROR, ltime->neg ? -date : date, FALSE, dec))
     return dec;
   if (ltime->second_part)
   {

=== modified file 'sql/mysql_priv.h'
--- a/sql/mysql_priv.h	2008-10-02 11:57:52 +0000
+++ b/sql/mysql_priv.h	2008-11-26 08:28:17 +0000
@@ -1556,8 +1556,8 @@ void make_date(const DATE_TIME_FORMAT *f
                String *str);
 void make_time(const DATE_TIME_FORMAT *format, const MYSQL_TIME *l_time,
                String *str);
-ulonglong get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
-                             Item *warn_item, bool *is_null);
+longlong get_datetime_value(THD *thd, Item ***item_arg, Item **cache_arg,
+                            Item *warn_item, bool *is_null);
 
 int test_if_number(char *str,int *res,bool allow_wildcards);
 void change_byte(byte *,uint,char,char);