bk commit into 4.1 tree (petr:1.2533) BUG#9191

[Petr Chardin]

Below is the list of changes that have just been committed into a local
4.1 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-08-17 18:37:04+04:00, petr@stripped +12 -0
    Fix Bug #9191 from_unixtime(power(2,31)-1) now epoch instead of 2038-01-19
    (4.1 version)
    
    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 TIMESTAMP range so, that max
    TIMESTAMP value is power(2,32)-1. It also corrects
    FROM_UNIXTIME() and UNIX_TIMESTAMP() functions, so that
    max allowed 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 patch also fixes CONVERT_TZ()
    function to allow extended range of dates.
    
    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).
    
    Note (for SYSTEM timezone only!): for platforms where time_t is
    unsigned we should also make a trick for conversion of dates,
    close to 0. However there are at least two timezones, which had
    time switches near 1 Jan of 1970. 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. The patch does not take into account these switches and 
    will give incorrect results for dates close to 1970-01-01 
    in the timezones mentioned above on QNX (the only platform
    we support, which has unsigned time_t).

  include/my_time.h@stripped, 2006-08-17 18:36:56+04:00, petr@stripped +8 -0
    move time-related defines to proper place

  mysql-test/r/func_time.result@stripped, 2006-08-17 18:36:56+04:00, petr@stripped +11 -2
    update test result

  mysql-test/r/timezone.result@stripped, 2006-08-17 18:36:56+04:00, petr@stripped +4 -4
    update result

  mysql-test/r/timezone2.result@stripped, 2006-08-17 18:36:56+04:00, petr@stripped +12 -12
    update result

  mysql-test/t/func_time.test@stripped, 2006-08-17 18:36:57+04:00, petr@stripped +19 -6
    add test for Bug#9191 and update test to be consistent
    with new TIMESTAMP boundaries

  mysql-test/t/timezone.test@stripped, 2006-08-17 18:36:57+04:00, petr@stripped +4 -3
    update old tests to be consistent
    with new TIMESTAMP boundaries

  mysql-test/t/timezone2.test@stripped, 2006-08-17 18:36:57+04:00, petr@stripped +6 -6
    update tests for convert_tz to be consistent with new
    TIMESTAMP boundaries

  sql-common/my_time.c@stripped, 2006-08-17 18:36:58+04:00, petr@stripped +98 -10
    merge fix of the old conversion routine (used for SYSTEM timezone)
    from 4.0 version of the bugfix.

  sql/item_timefunc.cc@stripped, 2006-08-17 18:36:57+04:00, petr@stripped +1 -1
    fix convert_tz to allow dates from the new (extended)
    TIMESTAMP range

  sql/mysql_priv.h@stripped, 2006-08-17 18:36:57+04:00, petr@stripped +0 -6
    move time handling defaults to my_time.h

  sql/time.cc@stripped, 2006-08-17 18:36:57+04:00, petr@stripped +1 -1
    update TIME_to_timestamp to allow conversion of
    extended date range

  sql/tztime.cc@stripped, 2006-08-17 18:36:58+04:00, petr@stripped +39 -4
    Fix new (4.1) implementation of broken-down time representation
    to time_t conversion routine to avoid overflows during conversion
    of boundary dates

# 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
# Root:	/home/cps/mysql/trees/mysql-4.1-bug

--- 1.105/sql/item_timefunc.cc	2006-08-17 18:37:16 +04:00
+++ 1.106/sql/item_timefunc.cc	2006-08-17 18:37:16 +04:00
@@ -1827,7 +1827,7 @@ bool Item_func_convert_tz::get_date(TIME
 
   /* Check if we in range where we treat datetime values as non-UTC */
   if (ltime->year < TIMESTAMP_MAX_YEAR && ltime->year >
TIMESTAMP_MIN_YEAR ||
-      ltime->year==TIMESTAMP_MAX_YEAR && ltime->month==1 &&
ltime->day==1 ||
+      ltime->year==TIMESTAMP_MAX_YEAR && ltime->month==1 &&
ltime->day<=19 ||
       ltime->year==TIMESTAMP_MIN_YEAR && ltime->month==12 &&
ltime->day==31)
   {
     my_time_tmp= from_tz->TIME_to_gmt_sec(ltime, &not_used);

--- 1.379/sql/mysql_priv.h	2006-08-17 18:37:16 +04:00
+++ 1.380/sql/mysql_priv.h	2006-08-17 18:37:16 +04:00
@@ -142,12 +142,6 @@ MY_LOCALE *my_locale_by_name(const char 
 /* Characters shown for the command in 'show processlist' */
 #define PROCESS_LIST_WIDTH 100
 
-/* Time handling defaults */
-#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_MIN_VALUE 1
 #define PRECISION_FOR_DOUBLE 53
 #define PRECISION_FOR_FLOAT  24
 

--- 1.53/sql/time.cc	2006-08-17 18:37:16 +04:00
+++ 1.54/sql/time.cc	2006-08-17 18:37:16 +04:00
@@ -231,7 +231,7 @@ my_time_t TIME_to_timestamp(THD *thd, co
   *in_dst_time_gap= 0;
 
   if (t->year < TIMESTAMP_MAX_YEAR && t->year > TIMESTAMP_MIN_YEAR ||
-      t->year == TIMESTAMP_MAX_YEAR && t->month == 1 && t->day
== 1 ||
+      t->year == TIMESTAMP_MAX_YEAR && t->month == 1 && t->day
<= 19 ||
       t->year == TIMESTAMP_MIN_YEAR && t->month == 12 && t->day
== 31)
   {
     thd->time_zone_used= 1;

--- 1.7/mysql-test/r/timezone2.result	2006-08-17 18:37:16 +04:00
+++ 1.8/mysql-test/r/timezone2.result	2006-08-17 18:37:16 +04:00
@@ -106,7 +106,7 @@ create table t1 (ts timestamp);
 set time_zone='UTC';
 insert into t1 values ('0000-00-00 00:00:00'),('1969-12-31 23:59:59'),
 ('1970-01-01 00:00:00'),('1970-01-01 00:00:01'),
-('2037-12-31 23:59:59'),('2038-01-01 00:00:00');
+('2038-01-19 03:14:07'),('2038-01-19 03:14:08');
 Warnings:
 Warning	1264	Data truncated; out of range for column 'ts' at row 2
 Warning	1264	Data truncated; out of range for column 'ts' at row 3
@@ -117,13 +117,13 @@ ts
 0000-00-00 00:00:00
 0000-00-00 00:00:00
 1970-01-01 00:00:01
-2037-12-31 23:59:59
[phone]:14:07
 0000-00-00 00:00:00
 delete from t1;
 set time_zone='MET';
 insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 00:30:00'),
 ('1970-01-01 01:00:00'),('1970-01-01 01:00:01'),
-('2038-01-01 00:59:59'),('2038-01-01 01:00:00');
+('2038-01-19 04:14:07'),('2038-01-19 04:14:08');
 Warnings:
 Warning	1264	Data truncated; out of range for column 'ts' at row 2
 Warning	1264	Data truncated; out of range for column 'ts' at row 3
@@ -134,13 +134,13 @@ ts
 0000-00-00 00:00:00
 0000-00-00 00:00:00
 1970-01-01 01:00:01
-2038-01-01 00:59:59
[phone]:14:07
 0000-00-00 00:00:00
 delete from t1;
 set time_zone='+01:30';
 insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 01:00:00'),
 ('1970-01-01 01:30:00'),('1970-01-01 01:30:01'),
-('2038-01-01 01:29:59'),('2038-01-01 01:30:00');
+('2038-01-19 04:44:07'),('2038-01-19 04:44:08');
 Warnings:
 Warning	1264	Data truncated; out of range for column 'ts' at row 2
 Warning	1264	Data truncated; out of range for column 'ts' at row 3
@@ -151,7 +151,7 @@ ts
 0000-00-00 00:00:00
 0000-00-00 00:00:00
 1970-01-01 01:30:01
-2038-01-01 01:29:59
[phone]:44:07
 0000-00-00 00:00:00
 drop table t1;
 show variables like 'time_zone';
@@ -213,12 +213,12 @@ convert_tz('2003-10-26 02:59:59', 'MET',
 select convert_tz('2003-10-26 04:00:00', 'MET', 'UTC');
 convert_tz('2003-10-26 04:00:00', 'MET', 'UTC')
 2003-10-26 03:00:00
-select convert_tz('2038-01-01 00:59:59', 'MET', 'UTC');
-convert_tz('2038-01-01 00:59:59', 'MET', 'UTC')
-2037-12-31 23:59:59
-select convert_tz('2038-01-01 01:00:00', 'MET', 'UTC');
-convert_tz('2038-01-01 01:00:00', 'MET', 'UTC')
-2038-01-01 01:00:00
+select convert_tz('2038-01-19 04:14:07', 'MET', 'UTC');
+convert_tz('2038-01-19 04:14:07', 'MET', 'UTC')
[phone]:14:07
+select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC');
+convert_tz('2038-01-19 04:14:08', 'MET', 'UTC')
[phone]:14:08
 select convert_tz('2103-01-01 04:00:00', 'MET', 'UTC');
 convert_tz('2103-01-01 04:00:00', 'MET', 'UTC')
 2103-01-01 04:00:00

--- 1.7/mysql-test/t/timezone2.test	2006-08-17 18:37:16 +04:00
+++ 1.8/mysql-test/t/timezone2.test	2006-08-17 18:37:16 +04:00
@@ -107,21 +107,21 @@ create table t1 (ts timestamp);
 set time_zone='UTC';
 insert into t1 values ('0000-00-00 00:00:00'),('1969-12-31 23:59:59'),
                       ('1970-01-01 00:00:00'),('1970-01-01 00:00:01'),
-                      ('2037-12-31 23:59:59'),('2038-01-01 00:00:00');
+                      ('2038-01-19 03:14:07'),('2038-01-19 03:14:08');
 select * from t1;
-delete from t1;
+delete from t1; 
 # MET time zone has range shifted by one hour
 set time_zone='MET';
 insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 00:30:00'),
                       ('1970-01-01 01:00:00'),('1970-01-01 01:00:01'),
-                      ('2038-01-01 00:59:59'),('2038-01-01 01:00:00');
+                      ('2038-01-19 04:14:07'),('2038-01-19 04:14:08');
 select * from t1;
 delete from t1;
 # same for +01:30 time zone
 set time_zone='+01:30';
 insert into t1 values ('0000-00-00 00:00:00'),('1970-01-01 01:00:00'),
                       ('1970-01-01 01:30:00'),('1970-01-01 01:30:01'),
-                      ('2038-01-01 01:29:59'),('2038-01-01 01:30:00');
+                      ('2038-01-19 04:44:07'),('2038-01-19 04:44:08');
 select * from t1;
 
 drop table t1;
@@ -171,8 +171,8 @@ select convert_tz('2003-10-26 01:00:00',
 select convert_tz('2003-10-26 02:00:00', 'MET', 'UTC');
 select convert_tz('2003-10-26 02:59:59', 'MET', 'UTC');
 select convert_tz('2003-10-26 04:00:00', 'MET', 'UTC');
-select convert_tz('2038-01-01 00:59:59', 'MET', 'UTC');
-select convert_tz('2038-01-01 01:00:00', 'MET', 'UTC');
+select convert_tz('2038-01-19 04:14:07', 'MET', 'UTC');
+select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC');
 select convert_tz('2103-01-01 04:00:00', 'MET', 'UTC');
 
 # Let us test variable time zone argument

--- 1.21/sql/tztime.cc	2006-08-17 18:37:16 +04:00
+++ 1.22/sql/tztime.cc	2006-08-17 18:37:16 +04:00
@@ -882,9 +882,10 @@ sec_since_epoch(int year, int mon, int m
 static my_time_t
 TIME_to_gmt_sec(const TIME *t, const TIME_ZONE_INFO *sp, bool *in_dst_time_gap)
 {
-  my_time_t local_t;
+  longlong local_t;
   uint saved_seconds;
   uint i;
+  int shift= 0;
 
   DBUG_ENTER("TIME_to_gmt_sec");
 
@@ -895,11 +896,35 @@ TIME_to_gmt_sec(const TIME *t, const TIM
     saved_seconds= t->second;
 
   /*
-    NOTE If we want to convert full my_time_t range without MySQL
-    restrictions we should catch overflow here somehow.
+    NOTE: to convert full my_time_t range we do a shift of the
+    boundary dates here to avoid overflow of my_time_t.
+    We use alike approach in my_system_gmt_sec().
+
+    However in that function we also have to take into account
+    overflow near 0 on some platforms. That's because my_system_gmt_sec
+    uses localtime_r(), which doesn't work with negative values correctly
+    on platforms with unsigned time_t (QNX). Here we don't use localtime()
+    => we negative values of local_t are ok.
   */
 
-  local_t= sec_since_epoch(t->year, t->month, t->day,
+  if ((t->year == TIMESTAMP_MAX_YEAR) && (t->month == 1))
+  {
+    /* 2038, 19 of Jan is the max possible date within time_t range */
+    if (t->day > 19)
+      DBUG_RETURN(0);
+
+    /*
+      We will pass (t->day - shift) to sec_since_epoch(), and
+      want this value to be a positive number
+      As we don't want to get an owerflow here, we will shift
+      only safe dates.
+    */
+    if (t->day > 4)
+      shift= 2;
+  }
+
+
+  local_t= sec_since_epoch(t->year, t->month, (t->day - shift),
                            t->hour, t->minute,
                            saved_seconds ? 0 : t->second);
 
@@ -917,6 +942,16 @@ TIME_to_gmt_sec(const TIME *t, const TIM
 
   /* binary search for our range */
   i= find_time_range(local_t, sp->revts, sp->revcnt);
+
+  if (shift)
+  {
+    local_t+= shift*86400L;
+
+    /* my_time_t overflow */
+    if (local_t - sp->revtis[i].rt_offset + saved_seconds >
+        TIMESTAMP_MAX_VALUE)
+      DBUG_RETURN(0);
+  }
 
   if (sp->revtis[i].rt_type)
   {

--- 1.7/include/my_time.h	2006-08-17 18:37:16 +04:00
+++ 1.8/include/my_time.h	2006-08-17 18:37:16 +04:00
@@ -38,6 +38,14 @@ typedef long my_time_t;
 #define MY_TIME_T_MAX LONG_MAX
 #define MY_TIME_T_MIN LONG_MIN
 
+
+/* Time handling defaults */
+#define TIMESTAMP_MAX_YEAR 2038
+#define YY_PART_YEAR	   70
+#define TIMESTAMP_MIN_YEAR (1900 + YY_PART_YEAR - 1)
+#define TIMESTAMP_MAX_VALUE INT_MAX32
+#define TIMESTAMP_MIN_VALUE 1
+
 #define YY_PART_YEAR	   70
 
 /* Flags to str_to_datetime */

--- 1.15/sql-common/my_time.c	2006-08-17 18:37:16 +04:00
+++ 1.16/sql-common/my_time.c	2006-08-17 18:37:16 +04:00
@@ -667,11 +667,12 @@ long calc_daynr(uint year,uint month,uin
   RETURN VALUE
     Time in UTC seconds since Unix Epoch representation.
 */
-my_time_t 
+my_time_t
 my_system_gmt_sec(const MYSQL_TIME *t, long *my_timezone, bool *in_dst_time_gap)
 {
   uint loop;
-  time_t tmp;
+  time_t tmp= 0;
+  int shift= 0;
   struct tm *l_time,tm_tmp;
   long diff, current_timezone;
 
@@ -688,13 +689,89 @@ my_system_gmt_sec(const MYSQL_TIME *t, l
     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).
+
+    However there are 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 on 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))
+  {
+    /* 2038, 19 of Jan is the max possible date within time_t range */
+    if (t->day > 19)
+      goto end;
+
+    /*
+      Below we will pass (uint) (t->day - shift) to calc_daynr.
+      As we don't want to get an owerflow here, we will shift
+      only safe dates.
+    */
+    if (t->day > 4)
+      shift= 2;
+  }
+#ifdef __QNX__
+  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.
+    */
+    if ((t->year == TIMESTAMP_MIN_YEAR) && (t->month == 12))
+    {
+      if ((t->day == 31))
+        shift= -2;
+      else
+        /* 1969, 31 of Dec is min possible date within time_t range */
+        goto end;
+    }
+
+    if ((t->year == TIMESTAMP_MIN_YEAR + 1) && (t->month == 1)
+        && (t->day <= 10))
+      shift= -2;
+  }
+#endif
+
+  /*
+    Compute an estimation of time_t value for the date. Shift several days
+    for boundary dates.
 
+    Without a shift we would get an overflow during tmp computation near
+    boundaries. E.g.
+    (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(-360)
+    wil give us a date around 2106 year. Which is no good.
+  */
+  tmp= (time_t) (((calc_daynr((uint) t->year, (uint) t->month,
+                              (uint) (t->day - shift)) -
+                   (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;
@@ -705,7 +782,7 @@ my_system_gmt_sec(const MYSQL_TIME *t, l
        loop++)
   {					/* One check should be enough ? */
     /* Get difference in days */
-    int days= t->day - l_time->tm_mday;
+    int days= (t->day - shift) - l_time->tm_mday;
     if (days < -1)
       days= 1;					/* Month has wrapped */
     else if (days > 1)
@@ -730,7 +807,7 @@ my_system_gmt_sec(const MYSQL_TIME *t, l
   */
   if (loop == 2 && t->hour != (uint) l_time->tm_hour)
   {
-    int days= t->day - l_time->tm_mday;
+    int days= (t->day - shift) - l_time->tm_mday;
     if (days < -1)
       days=1;					/* Month has wrapped */
     else if (days > 1)
@@ -746,7 +823,18 @@ my_system_gmt_sec(const MYSQL_TIME *t, l
     *in_dst_time_gap= 1;
   }
   *my_timezone= current_timezone;
-  
+
+
+  /* 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.
+  */
+  if ((tmp < TIMESTAMP_MIN_VALUE) || (tmp > TIMESTAMP_MAX_VALUE))
+     tmp= 0;
+end:
   return (my_time_t) tmp;
 } /* my_system_gmt_sec */
 

--- 1.40/mysql-test/r/func_time.result	2006-08-17 18:37:16 +04:00
+++ 1.41/mysql-test/r/func_time.result	2006-08-17 18:37:16 +04:00
@@ -483,12 +483,21 @@ 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
 CREATE TABLE t1 (datetime datetime, timestamp timestamp, date date, time time);
 INSERT INTO t1 values ("2001-01-02 03:04:05", "2002-01-02 03:04:05", "2003-01-02",
"06:07:08");
 SELECT * from t1;

--- 1.35/mysql-test/t/func_time.test	2006-08-17 18:37:16 +04:00
+++ 1.36/mysql-test/t/func_time.test	2006-08-17 18:37:16 +04:00
@@ -236,15 +236,28 @@ 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
+# "from_unixtime(power(2,31)-1) now epoch instead of 2038-01-19.
+# 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);
+# check for from_unixtime(2^31-1) and from_unixtime(2^31)
+select from_unixtime(2147483647);
+select from_unixtime(2147483648);
 select from_unixtime(0);
+
+#
+# Some more tests for bug #9191 "from_unixtime(power(2,31)-1) now epoch
+# instead of 2038-01-19. 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));
 
 #
 # Test types from + INTERVAL

--- 1.6/mysql-test/r/timezone.result	2006-08-17 18:37:16 +04:00
+++ 1.7/mysql-test/r/timezone.result	2006-08-17 18:37:16 +04:00
@@ -41,7 +41,7 @@ Warning	1299	Invalid TIMESTAMP value in 
 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.9/mysql-test/t/timezone.test	2006-08-17 18:37:16 +04:00
+++ 1.10/mysql-test/t/timezone.test	2006-08-17 18:37:16 +04:00
@@ -52,11 +52,12 @@ INSERT INTO t1 (ts) VALUES ('2003-03-30 
 DROP TABLE t1;
 
 #
-# Test for fix for Bug#2523
+# Test for 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');
 
 # End of 4.1 tests