bzr commit into mysql-5.0-bugteam branch (azundris:2722) Bug#40329

[Tatiana A. Nurnberg]

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

 2722 Tatiana A. Nurnberg	2008-11-24
      Bug#40329: TIMESTAMPADD does not take in to account Daylight Savings Time
      
      TIMESTAMPADD()/TIMESTAMPDIFF() took (local-) times at face value.
      This broken on days where DST began or ended as it ignored the
      missing/extra hour.
      
      We now do our calculations on normalized (UTC) dates when hours
      matter.
modified:
  mysql-test/r/func_time.result
  mysql-test/r/timezone.result
  mysql-test/t/timezone.test
  sql/item_timefunc.cc
  sql/item_timefunc.h
  sql/sql_yacc.yy

per-file messages:
  mysql-test/r/func_time.result
    thanks to our new magic out-of-bounds timestamps no longer
    work in TIMESTAMPDIFF(), but then the docs always said you
    shouldn't rely on that.
  mysql-test/r/timezone.result
    documented results of new test case
  mysql-test/t/timezone.test
    Show that anomalies (11-hour/13-hour days thanks to DST) are
    handled; show that while we get new pathological cases (with
    some date/TZ-combinations converting to UTC will change the
    month, and "add 1 month" may be something different at that
    point), we didn't break those too badly.
  sql/item_timefunc.cc
    add helper functions to convert a MY_TIME between UTC and local time.
    preserve micro-second part.
    In TIMESTAMPADD/DIFF() convert implicitly to/from UTC before/after
    calculations where hours matter, so we don't break as badly on
    DST-anomalies (11-hour/13-hour day)
  sql/item_timefunc.h
    Optional parameter: do Item_date_add_interval() calculations
    on UTC dates and implicitly convert to/from if needed?
    Used by TIMESTAMPADD()
  sql/sql_yacc.yy
    When using Item_date_add_interval() for TIMESTAMPADD(),
    request implicit conversion to and from UTC, the same
    as when storing/retrieving a timestamp to/from a field.
=== modified file 'mysql-test/r/func_time.result'
--- a/mysql-test/r/func_time.result	2008-02-25 10:25:57 +0000
+++ b/mysql-test/r/func_time.result	2008-11-24 14:10:40 +0000
@@ -708,7 +708,7 @@ timestampdiff(SQL_TSI_DAY, '1900-02-01',
 timestampdiff(SQL_TSI_DAY, '1996-02-01', '1996-03-01') as a3,
 timestampdiff(SQL_TSI_DAY, '2000-02-01', '2000-03-01') as a4;
 a1	a2	a3	a4
-28	28	29	29
+28	0	29	29
 SELECT TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27');
 TIMESTAMPDIFF(day,'2006-01-10 14:30:28','2006-01-11 14:30:27')
 0

=== modified file 'mysql-test/r/timezone.result'
--- a/mysql-test/r/timezone.result	2006-11-01 13:47:40 +0000
+++ b/mysql-test/r/timezone.result	2008-11-24 14:10:40 +0000
@@ -45,3 +45,54 @@ unix_timestamp('2038-01-19 04:14:07'),
 unix_timestamp('2038-01-19 04:14:08');
 unix_timestamp('1970-01-01 01:00:00')	unix_timestamp('1970-01-01 01:00:01')	unix_timestamp('2038-01-19 04:14:07')	unix_timestamp('2038-01-19 04:14:08')
 0	1	2147483647	0
+End of 4.1 tests
+DST - there IS no 2:30, needs to be 3:30
+SELECT TIMESTAMPADD(HOUR,1,'2008-03-30 01:30:00') AS DST_test;
+DST_test
[phone]:30:00
+DST - there IS no 2:30, throw EINVAL
+SELECT TIMESTAMPADD(MINUTE,0,'2008-03-30 02:30:00.5') AS EINVAL_test;
+EINVAL_test
[phone]:00:00.500000
+Warnings:
+Warning	1292	Truncated incorrect time value: '2008-03-30 02:30:00'
+show that DST-code does not break microsecond-awareness
+SELECT TIMESTAMPADD(MICROSECOND,1,'2008-03-30 01:30:00.5') AS Microsecond_test;
+Microsecond_test
[phone]:30:00.500001
+DST - there's no 2:30, so there's only one hour between 1:30 and 3:30
+SELECT TIMESTAMPDIFF(HOUR,'2008-03-30 01:30:00','2008-03-30 03:30:00') AS DST_test;
+DST_test
+1
+show that DST-code does not break microsecond-awareness in -DIFF, either
+SELECT TIMESTAMPDIFF(MICROSECOND,'2008-03-30 03:30:00.1','2008-03-30 03:30:00.5') AS Microsecond_test;
+Microsecond_test
+400000
+SET TIME_ZONE="+03:00";
+pathological: TZ=-3: 2001-02-01,2001-05-01 => 2001-01-31,2001-04-30;
+30<31, would give 2 months instead of 3.
+SELECT TIMESTAMPDIFF(MONTH,'2001-02-01','2001-05-01') AS DaysInMonth_test;
+DaysInMonth_test
+3
+test2 needs to give the same result as test4 (test4 is authoritive)
+SELECT TIMESTAMPADD(MINUTE,1,'2001-03-01') AS DaysInMonth_test2;
+DaysInMonth_test2
[phone]:01:00
+SELECT TIMESTAMPADD(MONTH,1,'2001-03-01') AS DaysInMonth_test2;
+DaysInMonth_test2
[phone]
+test3 needs to give the same result as test5 (test5 is authoritive)
+SELECT TIMESTAMPDIFF(DAY,'2001-02-01','2001-05-01') AS DaysInMonth_test3;
+DaysInMonth_test3
+89
+SET TIME_ZONE="+00:00";
+ref for test2
+SELECT TIMESTAMPADD(MONTH,1,'2001-03-01') AS DaysInMonth_test4;
+DaysInMonth_test4
[phone]
+ref for test3
+SELECT TIMESTAMPDIFF(DAY,'2001-02-01','2001-05-01') AS DaysInMonth_test5;
+DaysInMonth_test5
+89
+SET TIME_ZONE=DEFAULT;
+End of 5.0 tests

=== modified file 'mysql-test/t/timezone.test'
--- a/mysql-test/t/timezone.test	2006-11-01 13:47:40 +0000
+++ b/mysql-test/t/timezone.test	2008-11-24 14:10:40 +0000
@@ -60,4 +60,37 @@ select unix_timestamp('1970-01-01 01:00:
        unix_timestamp('2038-01-19 04:14:07'),
        unix_timestamp('2038-01-19 04:14:08');
 
-# End of 4.1 tests
+--echo End of 4.1 tests
+
+#
+# Bug#40329: TIMESTAMPADD does not take in to account Daylight Savings Time.
+#
+
+--echo DST - there IS no 2:30, needs to be 3:30
+SELECT TIMESTAMPADD(HOUR,1,'2008-03-30 01:30:00') AS DST_test;
+--echo DST - there IS no 2:30, throw EINVAL
+SELECT TIMESTAMPADD(MINUTE,0,'2008-03-30 02:30:00.5') AS EINVAL_test;
+--echo show that DST-code does not break microsecond-awareness
+SELECT TIMESTAMPADD(MICROSECOND,1,'2008-03-30 01:30:00.5') AS Microsecond_test;
+--echo DST - there's no 2:30, so there's only one hour between 1:30 and 3:30
+SELECT TIMESTAMPDIFF(HOUR,'2008-03-30 01:30:00','2008-03-30 03:30:00') AS DST_test;
+--echo show that DST-code does not break microsecond-awareness in -DIFF, either
+SELECT TIMESTAMPDIFF(MICROSECOND,'2008-03-30 03:30:00.1','2008-03-30 03:30:00.5') AS Microsecond_test;
+
+SET TIME_ZONE="+03:00";
+--echo pathological: TZ=-3: 2001-02-01,2001-05-01 => 2001-01-31,2001-04-30;
+--echo 30<31, would give 2 months instead of 3.
+SELECT TIMESTAMPDIFF(MONTH,'2001-02-01','2001-05-01') AS DaysInMonth_test;
+--echo test2 needs to give the same result as test4 (test4 is authoritive)
+SELECT TIMESTAMPADD(MINUTE,1,'2001-03-01') AS DaysInMonth_test2;
+SELECT TIMESTAMPADD(MONTH,1,'2001-03-01') AS DaysInMonth_test2;
+--echo test3 needs to give the same result as test5 (test5 is authoritive)
+SELECT TIMESTAMPDIFF(DAY,'2001-02-01','2001-05-01') AS DaysInMonth_test3;
+SET TIME_ZONE="+00:00";
+--echo ref for test2
+SELECT TIMESTAMPADD(MONTH,1,'2001-03-01') AS DaysInMonth_test4;
+--echo ref for test3
+SELECT TIMESTAMPDIFF(DAY,'2001-02-01','2001-05-01') AS DaysInMonth_test5;
+SET TIME_ZONE=DEFAULT;
+
+--echo End of 5.0 tests

=== modified file 'sql/item_timefunc.cc'
--- a/sql/item_timefunc.cc	2008-11-21 09:48:22 +0000
+++ b/sql/item_timefunc.cc	2008-11-24 14:10:40 +0000
@@ -2051,6 +2051,38 @@ void Item_func_convert_tz::cleanup()
 }
 
 
+static int local_to_gmt_TIME(Time_zone *tz, MYSQL_TIME *mt)
+{
+  my_bool       dst_fail;
+  unsigned long second_part= mt->second_part;   // save microsecond-part
+  my_time_t     tt= tz->TIME_to_gmt_sec(mt, &dst_fail);
+
+  if (dst_fail)
+  {
+    char s[20];
+    my_datetime_to_str(mt, s);
+    push_warning_printf(current_thd, MYSQL_ERROR::WARN_LEVEL_WARN,
+                        ER_TRUNCATED_WRONG_VALUE,
+                        ER(ER_TRUNCATED_WRONG_VALUE), "time", s);
+  }
+  my_tz_UTC->gmt_sec_to_TIME(mt, tt);
+  mt->second_part= second_part;
+  return dst_fail;
+}
+
+
+static void gmt_to_local_TIME(Time_zone *tz, MYSQL_TIME *mt)
+{
+  my_bool       not_used;
+  unsigned long second_part= mt->second_part;   // save microsecond-part
+  ulong         days= calc_daynr(mt->year,mt->month,mt->day) - 719528; // epoch
+  my_time_t     tt= (longlong) days*LL(86400) +
+                    (longlong) (mt->hour*3600L + mt->minute*60L + mt->second);
+  tz->gmt_sec_to_TIME(mt, tt);
+  mt->second_part= second_part;
+}
+
+
 void Item_date_add_interval::fix_length_and_dec()
 {
   enum_field_types arg0_field_type;
@@ -2092,6 +2124,7 @@ bool Item_date_add_interval::get_date(MY
 {
   long period,sign;
   INTERVAL interval;
+  Time_zone *tz= current_thd->variables.time_zone;
 
   ltime->neg= 0;
   if (args[0]->get_date(ltime, TIME_NO_ZERO_DATE) ||
@@ -2101,6 +2134,17 @@ bool Item_date_add_interval::get_date(MY
   if (date_sub_interval)
     sign = -sign;
 
+  if ((int_type == INTERVAL_YEAR_MONTH) ||
+      (int_type == INTERVAL_QUARTER) ||
+      (int_type == INTERVAL_MONTH))
+    use_utc_internally= false;
+
+  if (use_utc_internally)
+  {
+    current_thd->time_zone_used= 1;
+    local_to_gmt_TIME(tz, ltime);
+  }
+
   null_value=0;
   switch (int_type) {
   case INTERVAL_SECOND:
@@ -2190,6 +2234,8 @@ bool Item_date_add_interval::get_date(MY
   default:
     goto null_date;
   }
+  if (use_utc_internally)
+    gmt_to_local_TIME(tz, ltime);
   return 0;					// Ok
 
 invalid_date:
@@ -3026,20 +3072,27 @@ longlong Item_func_microsecond::val_int(
   return 0;
 }
 
-
 longlong Item_func_timestamp_diff::val_int()
 {
   MYSQL_TIME ltime1, ltime2;
   longlong seconds;
   long microseconds;
   long months= 0;
-  int neg= 1;
+  int neg= 1, day1, day2;
+  Time_zone *tz= current_thd->variables.time_zone;
 
   null_value= 0;  
   if (args[0]->get_date(&ltime1, TIME_NO_ZERO_DATE) ||
       args[1]->get_date(&ltime2, TIME_NO_ZERO_DATE))
     goto null_date;
 
+  day1= ltime1.day;
+  day2= ltime2.day;
+
+  current_thd->time_zone_used= 1;
+  local_to_gmt_TIME(tz, &ltime1);
+  local_to_gmt_TIME(tz, &ltime2);
+
   if (calc_time_diff(&ltime2,&ltime1, 1,
 		     &seconds, &microseconds))
     neg= -1;
@@ -3058,8 +3111,8 @@ longlong Item_func_timestamp_diff::val_i
       year_end= ltime1.year;
       month_beg= ltime2.month;
       month_end= ltime1.month;
-      day_beg= ltime2.day;
-      day_end= ltime1.day;
+      day_beg= day2;
+      day_end= day1;
       second_beg= ltime2.hour * 3600 + ltime2.minute * 60 + ltime2.second;
       second_end= ltime1.hour * 3600 + ltime1.minute * 60 + ltime1.second;
       microsecond_beg= ltime2.second_part;
@@ -3071,8 +3124,8 @@ longlong Item_func_timestamp_diff::val_i
       year_end= ltime2.year;
       month_beg= ltime1.month;
       month_end= ltime2.month;
-      day_beg= ltime1.day;
-      day_end= ltime2.day;
+      day_beg= day1;
+      day_end= day2;
       second_beg= ltime1.hour * 3600 + ltime1.minute * 60 + ltime1.second;
       second_end= ltime2.hour * 3600 + ltime2.minute * 60 + ltime2.second;
       microsecond_beg= ltime1.second_part;

=== 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-24 14:10:40 +0000
@@ -683,12 +683,14 @@ class Item_date_add_interval :public Ite
 {
   String value;
   enum_field_types cached_field_type;
+  bool use_utc_internally;
 
 public:
   const interval_type int_type; // keep it public
   const bool date_sub_interval; // keep it public
-  Item_date_add_interval(Item *a,Item *b,interval_type type_arg,bool neg_arg)
-    :Item_date_func(a,b),int_type(type_arg), date_sub_interval(neg_arg) {}
+  Item_date_add_interval(Item *a,Item *b,interval_type type_arg,bool neg_arg,bool transient_utc= false)
+    :Item_date_func(a,b),int_type(type_arg), date_sub_interval(neg_arg)
+  { use_utc_internally= transient_utc; }
   String *val_str(String *);
   const char *func_name() const { return "date_add_interval"; }
   void fix_length_and_dec();

=== modified file 'sql/sql_yacc.yy'
--- a/sql/sql_yacc.yy	2008-10-02 11:57:52 +0000
+++ b/sql/sql_yacc.yy	2008-11-24 14:10:40 +0000
@@ -5754,7 +5754,7 @@ simple_expr:
           }
 	| TIMESTAMP_ADD '(' interval_time_stamp ',' expr ',' expr ')'
 	  {
-            $$= new Item_date_add_interval($7,$5,$3,0);
+            $$= new Item_date_add_interval($7,$5,$3,0,TRUE);
             if ($$ == NULL)
               MYSQL_YYABORT;
           }