Fix Bug #9191 "TIMESTAMP/from_unixtime() no longer accepts 2^31-1"

(4.1 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,31)-1 are valid.
  
  This patch extends allowed TIMESTAMP range so, that max
  TIMESTAMP value is power(2,31)-1. It also corrects
  FROM_UNIXTIME() and UNIX_TIMESTAMP() functions, so that
  max allowed UNIX_TIMESTAMP() is power(2,31)-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).


acinclude.m4:
  Add new macro to check time_t range
configure.in:
  Call the macro to check time_t range
include/my_time.h:
  Move time-related defines to proper place.
  Add a function to perform a rough check if
  a TIMESTAMP value fits into the boundaries.
  Note: it is defined as "static inline", as
  otherwise libmysql won't compile (due to the
  way how gcc handles "inline" directive).
mysql-test/r/func_time.result:
  Update test result
mysql-test/r/timezone.result:
  Update test result
mysql-test/r/timezone2.result:
  Update test result
mysql-test/t/func_time.test:
  Add test for Bug#9191 and update test to be consistent
  with new TIMESTAMP boundaries
mysql-test/t/timezone.test:
  Update old tests to be consistent
  with new TIMESTAMP boundaries
mysql-test/t/timezone2.test:
  Update tests for convert_tz to be consistent with new
  TIMESTAMP boundaries
sql/item_timefunc.cc:
  Fix convert_tz to allow dates from the new (extended)
  TIMESTAMP range
sql/mysql_priv.h:
  Move time handling defaults to my_time.h
sql-common/my_time.c:
  Because of increased TIMESTAMP_MAX_VALUE overflows in my_system_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.
sql/time.cc:
  Update TIME_to_timestamp to allow conversion of
  extended date range
sql/tztime.cc:
  Fix new (4.1) implementation of broken-down time representation
  to time_t conversion routine to avoid overflows during conversion
  of boundary dates
mysql-test/r/timezone4.result:
  New BitKeeper file ``mysql-test/r/timezone4.result''
mysql-test/t/timezone4-master.opt:
  New BitKeeper file ``mysql-test/t/timezone4-master.opt''
mysql-test/t/timezone4.test:
  New BitKeeper file ``mysql-test/t/timezone4.test''

9 files changed, 129 insertions, 32 deletions

diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index 97dd8e243b2..6e3492f6744 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -483,12 +483,48 @@ 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)
+2038-01-19 06: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))
+2147483647
+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')
+2147331600
+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
 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;
diff --git a/mysql-test/r/timezone.result b/mysql-test/r/timezone.result
index 10944c3706e..1223fff36c6 100644
--- a/mysql-test/r/timezone.result
+++ b/mysql-test/r/timezone.result
@@ -41,7 +41,7 @@ Warning	1299	Invalid TIMESTAMP value in column 'ts' at row 2
 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
diff --git a/mysql-test/r/timezone2.result b/mysql-test/r/timezone2.result
index 199b48ef1aa..ad3ce40ebb1 100644
--- a/mysql-test/r/timezone2.result
+++ b/mysql-test/r/timezone2.result
@@ -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
+2038-01-19 03: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
+2038-01-19 04: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
+2038-01-19 04: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', 'UTC')
 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')
+2038-01-19 03:14:07
+select convert_tz('2038-01-19 04:14:08', 'MET', 'UTC');
+convert_tz('2038-01-19 04:14:08', 'MET', 'UTC')
+2038-01-19 04: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
diff --git a/mysql-test/r/timezone4.result b/mysql-test/r/timezone4.result
new file mode 100644
index 00000000000..28028bea657
--- /dev/null
+++ b/mysql-test/r/timezone4.result
@@ -0,0 +1,6 @@
+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
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 04bfc741d1c..c0284eab735 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -236,17 +236,57 @@ 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
+# users 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 "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');
+
+
+#
 # Test types from + INTERVAL
 #
 
diff --git a/mysql-test/t/timezone.test b/mysql-test/t/timezone.test
index 34bbb365c70..157b18f57fa 100644
--- a/mysql-test/t/timezone.test
+++ b/mysql-test/t/timezone.test
@@ -52,11 +52,12 @@ INSERT INTO t1 (ts) VALUES ('2003-03-30 01:59:59'),
 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
diff --git a/mysql-test/t/timezone2.test b/mysql-test/t/timezone2.test
index 523249a3a2c..43f9a944be1 100644
--- a/mysql-test/t/timezone2.test
+++ b/mysql-test/t/timezone2.test
@@ -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;
 # 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', 'MET', 'UTC');
 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
diff --git a/mysql-test/t/timezone4-master.opt b/mysql-test/t/timezone4-master.opt
new file mode 100644
index 00000000000..d1ab6207933
--- /dev/null
+++ b/mysql-test/t/timezone4-master.opt
@@ -0,0 +1 @@
+--timezone=GMT+10
diff --git a/mysql-test/t/timezone4.test b/mysql-test/t/timezone4.test
new file mode 100644
index 00000000000..d7372c75d5a
--- /dev/null
+++ b/mysql-test/t/timezone4.test
@@ -0,0 +1,13 @@
+#
+# 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');
+