bk commit into 4.0 tree (petr:1.2193) BUG#9191

[Petr Chardin]

Below is the list of changes that have just been committed into a local
4.0 repository of cps. When cps 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@stripped, 2006-09-22 22:29:58+04:00, petr@stripped +11 -0
  Fix Bug #9191 "TIMESTAMP/from_unixtime() no longer accepts 2^31-1"
  (4.0 version. with post-review fixes)
  
  The fix for another Bug (6439) limited FROM_UNIXTIME() to
  TIMESTAMP_MAX_VALUE which is 2145916799 or 2037-12-01 23:59:59 GMT,
  however unix timestamp in general is not considered to be limited 
  by this value. All dates up to power(2,32)-1 are valid.
  
  This patch extends allowed range for TIMESTAMP data type.
  It also fixes FROM_UNIXTIME() and UNIX_TIMESTAMP() functions,
  so that max UNIX_TIMESTAMP() is power(2,32)-1. FROM_UNIXTIME()
  is fixed accordingly to allow conversion of dates up to
  2038-01-19 03:14:07 UTC.
  
  The main problem solved in the patch is possible overflows
  of variables, used in broken-time representation to time_t
  conversion (required for UNIX_TIMESTAMP).

  acinclude.m4@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +26 -0
    Add a new macro to check for time_t range

  configure.in@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +6 -0
    call the macro to check for time_t range in configure.in

  mysql-test/r/func_time.result@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +38 -2
    Update result file

  mysql-test/r/func_time2.result@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +6 -0
    New BitKeeper file ``mysql-test/r/func_time2.result''

  mysql-test/r/func_time2.result@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +0 -0

  mysql-test/r/timezone.result@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +4 -4
    Update result file

  mysql-test/t/func_time.test@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +46 -6
    add test for the bug

  mysql-test/t/func_time2-master.opt@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +1 -0
    New BitKeeper file ``mysql-test/t/func_time2-master.opt''

  mysql-test/t/func_time2-master.opt@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +0 -0

  mysql-test/t/func_time2.test@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +12 -0
    New BitKeeper file ``mysql-test/t/func_time2.test''

  mysql-test/t/func_time2.test@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +0 -0

  mysql-test/t/timezone.test@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +3 -3
    change test to check (updated) boundary dates

  sql/mysql_priv.h@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +1 -1
    change TIMESTAMP_MAX_VALUE to reflect the bugfix:
    now all timestamp values, which from_unixtime
    representation <= than INT_MAX32 are valid

  sql/time.cc@stripped, 2006-09-22 22:29:57+04:00, petr@stripped +122 -12
    Because of increased TIMESTAMP_MAX_VALUE overflows in my_gmt_sec()
    became possible. Here we make it safe against the overflows by stepping
    back from the boundary dates which are likely to trigger them.

# 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:	petr
# Host:	owlet.local
# Root:	/home/cps/mysql/trees/mysql-4.0-bug

--- 1.102/acinclude.m4	2006-09-22 22:30:01 +04:00
+++ 1.103/acinclude.m4	2006-09-22 22:30:01 +04:00
@@ -1367,6 +1367,32 @@ AC_DEFUN(AC_SYS_LARGEFILE_SPACE_APPEND,
      esac ;;
    esac])
 
+
+dnl
+dnl  Macro to check time_t range
+dnl
+
+AC_DEFUN([AC_CHECK_TIME_T],[
+    AC_MSG_CHECKING(if time_t is unsigned)
+AC_RUN_IFELSE([AC_LANG_PROGRAM(
+      [[
+#include <time.h>
+      ]],
+      [[
+time_t t= -1;
+if (t > 0)
+  return 0;
+else
+  return 1;
+      ]] )
+    ], [
+    AC_DEFINE([TIME_T_UNSIGNED], 1, [Define to 1 if time_t is unsigned])
+    AC_MSG_RESULT(yes)
+    ],
+    [AC_MSG_RESULT(no)]
+    )
+])
+
 dnl Internal subroutine of AC_SYS_LARGEFILE.
 dnl AC_SYS_LARGEFILE_MACRO_VALUE(C-MACRO, CACHE-VAR, COMMENT, CODE-TO-SET-DEFAULT)
 AC_DEFUN(AC_SYS_LARGEFILE_MACRO_VALUE,

--- 1.338/configure.in	2006-09-22 22:30:01 +04:00
+++ 1.339/configure.in	2006-09-22 22:30:01 +04:00
@@ -2539,6 +2539,12 @@ fi
 
 CLIENT_LIBS="$CLIENT_LIBS $STATIC_NSS_FLAGS"
 
+dnl
+dnl check if time_t is unsigned
+dnl
+
+AC_CHECK_TIME_T
+
 AC_SUBST(CLIENT_LIBS)
 AC_SUBST(sql_client_dirs)
 AC_SUBST(linked_client_targets)

--- 1.239/sql/mysql_priv.h	2006-09-22 22:30:01 +04:00
+++ 1.240/sql/mysql_priv.h	2006-09-22 22:30:01 +04:00
@@ -118,7 +118,7 @@ char* query_table_status(THD *thd,const 
 #define TIMESTAMP_MAX_YEAR 2038
 #define YY_PART_YEAR	   70
 #define TIMESTAMP_MIN_YEAR (1900 + YY_PART_YEAR - 1)
-#define TIMESTAMP_MAX_VALUE 2145916799
+#define TIMESTAMP_MAX_VALUE INT_MAX32
 #define TIMESTAMP_MIN_VALUE 1
 #define PRECISION_FOR_DOUBLE 53
 #define PRECISION_FOR_FLOAT  24

--- 1.30/sql/time.cc	2006-09-22 22:30:01 +04:00
+++ 1.31/sql/time.cc	2006-09-22 22:30:01 +04:00
@@ -56,16 +56,20 @@ void init_time(void)
 
 */
 
-long my_gmt_sec(TIME *t, long *my_timezone)
+long my_gmt_sec(TIME *t_src, long *my_timezone)
 {
   uint loop;
-  time_t tmp;
+  time_t tmp= 0;
+  int shift= 0;
+  TIME *t= t_src, tmp_time;
   struct tm *l_time,tm_tmp;
   long diff, current_timezone;
 
-  if (t->year > TIMESTAMP_MAX_YEAR || t->year < TIMESTAMP_MIN_YEAR)
+  if ((t->year > TIMESTAMP_MAX_YEAR || t->year < TIMESTAMP_MIN_YEAR) ||
+      (t->year == TIMESTAMP_MAX_YEAR && (t->month > 1 || t->day >
19)) ||
+      (t->year == TIMESTAMP_MIN_YEAR && (t->month < 12 || t->day <
31)))
     return 0;
-    
+
   if (t->hour >= 24)
   {					/* Fix for time-loop */
     t->day+=t->hour/24;
@@ -85,15 +89,107 @@ long my_gmt_sec(TIME *t, long *my_timezo
     Note: this code assumes that our time_t estimation is not too far away
     from real value (we assume that localtime_r(tmp) will return something
     within 24 hrs from t) which is probably true for all current time zones.
+
+    Note2: For the dates, which have time_t representation close to
+    MAX_INT32 (efficient time_t limit for supported platforms), we should
+    do a small trick to avoid overflow. That is, convert the date, which is
+    two days earlier, and then add these days to the final value.
+
+    The same trick is done for the values close to 0 in time_t
+    representation for platfroms with unsigned time_t (QNX).
+
+    To be more verbose, here is a sample:
+    (calc_daynr(2038, 1, 19) - (long) days_at_timestart)*86400L + 4*3600L
+    would return -2147480896 because of the long type overflow. In result
+    we would get 1901 year in localtime_r(), which is an obvious error.
+
+    Alike problem raises with the dates close to Epoch. E.g.
+    (calc_daynr(1969, 12, 31) - (long) days_at_timestart)*86400L + 23*3600L
+    will give -3600.
+
+    On some platforms, (E.g. on QNX) time_t is unsigned and localtime(-3600)
+    wil give us a date around 2106 year. Which is no good.
+
+    Theoreticaly, there could be problems with the latter conversion:
+    there are at least two timezones, which had time switches near 1 Jan
+    of 1970 (because of political reasons). These are America/Hermosillo and
+    America/Mazatlan time zones. They changed their offset on
+    1970-01-01 08:00:00 UTC from UTC-8 to UTC-7. For these zones
+    the code below will give incorrect results for dates close to
+    1970-01-01, in the case OS takes into account these historical switches.
+    Luckily, it seems that we support only one platform with unsigned
+    time_t. It's QNX. And QNX does not support historical timezone data at all.
+    E.g. there are no /usr/share/zoneinfo/ files or any other mean to supply
+    historical information for localtime_r() etc. That is, the problem is not
+    relevant to QNX.
+
+    We are safe with shifts close to MAX_INT32, as there are no known
+    time switches on Jan 2038 yet :)
   */
-  tmp=(time_t) (((calc_daynr((uint) t->year,(uint) t->month,(uint) t->day) -
-		  (long) days_at_timestart)*86400L + (long) t->hour*3600L +
-		 (long) (t->minute*60 + t->second)) + (time_t) my_time_zone -
-		3600);
-  current_timezone= my_time_zone;
+  if ((t->year == TIMESTAMP_MAX_YEAR) && (t->month == 1))
+  {
+    /*
+      Below we will pass (uint) (t->day - shift) to calc_daynr.
+      As we don't want to get an overflow here, we will shift
+      only safe dates.
+    */
+    if (t->day > 4)
+    {
+      /* use temp variable to avoid trashing input data */
+      memcpy(&tmp_time, t_src, sizeof(TIME));
+      t= &tmp_time;
+
+      t->day-= 2;
+      shift= 2;
+    }
+  }
+#ifdef TIME_T_UNSIGNED
+  else
+  {
+    /*
+      We can get 0 in time_t representaion only on 1969, 31 of Dec or on
+      1970, 1 of Jan. For both dates we use shift, which is added
+      to t->day in order to step out a bit from the border.
+      This is required for platforms, where time_t is unsigned.
+      As far as I know, among the platforms we support it's only QNX.
+      Note: the order of below if-statements is significant.
+    */
+
+    if ((t->year == TIMESTAMP_MIN_YEAR + 1) && (t->month == 1)
+        && (t->day <= 10))
+    {
+      /* use temp variable to avoid trashing input data */
+      memcpy(&tmp_time, t_src, sizeof(TIME));
+      t= &tmp_time;
+      t->day+= 2;
+      shift= -2;
+    }
+
+    if ((t->year == TIMESTAMP_MIN_YEAR) && (t->month == 12)
+        && (t->day == 31))
+    {
+      /* use temp variable to avoid trashing input data */
+      memcpy(&tmp_time, t_src, sizeof(TIME));
+      t= &tmp_time;
+      t->year++;
+      t->month= 1;
+      t->day= 2;
+      shift= -2;
+    }
+  }
+#endif
 
+  /* Compute an estimation of time_t value for the date */
+  tmp= (time_t) (((calc_daynr((uint) t->year, (uint) t->month,
+                              (uint) t->day) -
+                   (long) days_at_timestart)*86400L + (long) t->hour*3600L +
+                  (long) (t->minute*60 + t->second)) + (time_t) my_time_zone -
+                 3600);
+
+  current_timezone= my_time_zone;
   localtime_r(&tmp,&tm_tmp);
   l_time=&tm_tmp;
+
   for (loop=0;
        loop < 2 &&
 	 (t->hour != (uint) l_time->tm_hour ||
@@ -141,10 +237,24 @@ long my_gmt_sec(TIME *t, long *my_timezo
       tmp-=t->minute*60 + t->second;		// Move to previous hour
   }
   *my_timezone= current_timezone;
-  
-  if (tmp < TIMESTAMP_MIN_VALUE || tmp > TIMESTAMP_MAX_VALUE)
+
+  /* shift back, if we were dealing with boundary dates */
+  tmp+= shift*86400L;
+
+  /*
+    This is possible for dates, which slightly exceed boundaries.
+    Conversion will pass ok for them, but we don't allow them.
+    First check will pass for platforms with signed time_t.
+    instruction above (tmp+= shift*86400L) could exceed
+    MAX_INT32 (== TIMESTAMP_MAX_VALUE) and overflow will happen.
+    So, tmp < TIMESTAMP_MIN_VALUE will be triggered. On platfroms
+    with unsigned time_t tmp+= shift*86400L might result in a number,
+    larger then TIMESTAMP_MAX_VALUE, so another check will work.
+  */
+  if ((tmp < TIMESTAMP_MIN_VALUE) || (tmp > TIMESTAMP_MAX_VALUE))
     tmp= 0;
-  
+
+end:
   return (long) tmp;
 } /* my_gmt_sec */
 

--- 1.24/mysql-test/r/func_time.result	2006-09-22 22:30:01 +04:00
+++ 1.25/mysql-test/r/func_time.result	2006-09-22 22:30:01 +04:00
@@ -473,9 +473,45 @@ unix_timestamp('1969-12-01 19:00:01')
 select from_unixtime(-1);
 from_unixtime(-1)
 NULL
-select from_unixtime(2145916800);
-from_unixtime(2145916800)
+select from_unixtime(2147483647);
+from_unixtime(2147483647)
[phone]:14:07
+select from_unixtime(2147483648);
+from_unixtime(2147483648)
 NULL
 select from_unixtime(0);
 from_unixtime(0)
 1970-01-01 03:00:00
+select unix_timestamp(from_unixtime(2147483647));
+unix_timestamp(from_unixtime(2147483647))
[phone]
+select unix_timestamp(from_unixtime(2147483648));
+unix_timestamp(from_unixtime(2147483648))
+NULL
+select unix_timestamp('2039-01-20 01:00:00');
+unix_timestamp('2039-01-20 01:00:00')
+0
+select unix_timestamp('1968-01-20 01:00:00');
+unix_timestamp('1968-01-20 01:00:00')
+0
+select unix_timestamp('2038-02-10 01:00:00');
+unix_timestamp('2038-02-10 01:00:00')
+0
+select unix_timestamp('1969-11-20 01:00:00');
+unix_timestamp('1969-11-20 01:00:00')
+0
+select unix_timestamp('2038-01-20 01:00:00');
+unix_timestamp('2038-01-20 01:00:00')
+0
+select unix_timestamp('1969-12-30 01:00:00');
+unix_timestamp('1969-12-30 01:00:00')
+0
+select unix_timestamp('2038-01-17 12:00:00');
+unix_timestamp('2038-01-17 12:00:00')
[phone]
+select unix_timestamp('1970-01-01 03:00:01');
+unix_timestamp('1970-01-01 03:00:01')
+1
+select unix_timestamp('2038-01-19 07:14:07');
+unix_timestamp('2038-01-19 07:14:07')
+0

--- 1.21/mysql-test/t/func_time.test	2006-09-22 22:30:01 +04:00
+++ 1.22/mysql-test/t/func_time.test	2006-09-22 22:30:01 +04:00
@@ -227,12 +227,52 @@ select unix_timestamp(@a);
 select unix_timestamp('1969-12-01 19:00:01');
 
 #
-# Test for bug #6439 "unix_timestamp() function returns wrong datetime 
-# values for too big argument" and bug #7515 "from_unixtime(0) now
-# returns NULL instead of the epoch". unix_timestamp() should return error
-# for too big or negative argument. It should return Epoch value for zero
-# argument since it seems that many user's rely on this fact.
+# Tests for bug #6439 "unix_timestamp() function returns wrong datetime 
+# values for too big argument", bug #7515 "from_unixtime(0) now
+# returns NULL instead of the epoch" and bug #9191
+# "TIMESTAMP/from_unixtime() no longer accepts 2^31-1"
+# unix_timestamp() should return error for too big or negative argument.
+# It should return Epoch value for zero argument since it seems that many
+# user's rely on this fact, from_unixtime() should work with values
+# up to INT_MAX32 because of the same reason.
 #
 select from_unixtime(-1);
-select from_unixtime(2145916800);
+# the following is in fact check for argument equal to 2^31-1 and 2^31
+select from_unixtime(2147483647);
+select from_unixtime(2147483648);
 select from_unixtime(0);
+
+#
+# Some more tests for bug #9191 "TIMESTAMP/from_unixtime() no longer accepts
+# 2^31-1" Here we test that from_unixtime and unix_timestamp
+# are consistent, when working with boundary dates
+#
+select unix_timestamp(from_unixtime(2147483647));
+select unix_timestamp(from_unixtime(2147483648));
+
+# check for invalid dates
+
+# bad year
+select unix_timestamp('2039-01-20 01:00:00');
+select unix_timestamp('1968-01-20 01:00:00');
+# bad month
+select unix_timestamp('2038-02-10 01:00:00');
+select unix_timestamp('1969-11-20 01:00:00');
+# bad day
+select unix_timestamp('2038-01-20 01:00:00');
+select unix_timestamp('1969-12-30 01:00:00');
+
+#
+# Check negative shift (we subtract several days for boundary dates during
+# conversion).
+select unix_timestamp('2038-01-17 12:00:00');
+
+#
+# Check positive shift. (it happens only on
+# platfroms with unsigned time_t, such as QNX)
+#
+select unix_timestamp('1970-01-01 03:00:01');
+
+# check bad date, close to the boundary (we cut them off in the very end)
+select unix_timestamp('2038-01-19 07:14:07');
+

--- 1.4/mysql-test/r/timezone.result	2006-09-22 22:30:01 +04:00
+++ 1.5/mysql-test/r/timezone.result	2006-09-22 22:30:01 +04:00
@@ -34,7 +34,7 @@ ts	from_unixtime(ts)
 DROP TABLE t1;
 select unix_timestamp('1970-01-01 01:00:00'), 
 unix_timestamp('1970-01-01 01:00:01'),
-unix_timestamp('2038-01-01 00:59:59'),
-unix_timestamp('2038-01-01 01:00:00');
-unix_timestamp('1970-01-01 01:00:00')	unix_timestamp('1970-01-01
01:00:01')	unix_timestamp('2038-01-01 00:59:59')	unix_timestamp('2038-01-01 01:00:00')
-0	1	2145916799	0
+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

--- 1.6/mysql-test/t/timezone.test	2006-09-22 22:30:01 +04:00
+++ 1.7/mysql-test/t/timezone.test	2006-09-22 22:30:01 +04:00
@@ -41,9 +41,9 @@ SELECT ts,from_unixtime(ts) FROM t1;
 DROP TABLE t1;
 
 #
-# Test for fix for Bug#2523
+# Test fix for Bug#2523. Check that boundary dates are processed correctly
 #
 select unix_timestamp('1970-01-01 01:00:00'), 
        unix_timestamp('1970-01-01 01:00:01'),
-       unix_timestamp('2038-01-01 00:59:59'),
-       unix_timestamp('2038-01-01 01:00:00');
+       unix_timestamp('2038-01-19 04:14:07'),
+       unix_timestamp('2038-01-19 04:14:08');
--- New file ---
+++ mysql-test/r/func_time2.result	06/09/22 22:29:57
select from_unixtime(0);
from_unixtime(0)
1969-12-31 14:00:00
select unix_timestamp('1969-12-31 14:00:01');
unix_timestamp('1969-12-31 14:00:01')
1

--- New file ---
+++ mysql-test/t/func_time2-master.opt	06/09/22 22:29:57
--timezone=GMT+10

--- New file ---
+++ mysql-test/t/func_time2.test	06/09/22 22:29:57
#
# Tests for time functions. The difference from func_time test is the
# timezone. In func_time it's GMT-3. In our case it's GMT+10
#

#
# Test for bug bug #9191 "TIMESTAMP/from_unixtime() no longer accepts 2^31-1"
#

select from_unixtime(0);
# check 0 boundary
select unix_timestamp('1969-12-31 14:00:01');