MySQL Bugs: #9191: TIMESTAMP/from_unixtime() no longer accepts 2^31-1

Bug #9191	TIMESTAMP/from_unixtime() no longer accepts 2^31-1
Submitted:	15 Mar 2005 11:03	Modified:	10 Nov 2006 20:55
Reporter:	Christian Hammers (Silver Quality Contributor) (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S2 (Serious)
Version:	4.0.22 -> 4.0.23/24	OS:
Assigned to:	Petr Chardin	CPU Architecture:	Any

Description:
"select  from_unixtime(power(2,31)-1);" used to return "2038-01-19 04:14:07" but now
returns NULL although the timerange is valid (positive signed int32).

(It's used where you have a fixed "date between from_unixtime(a) and from_unixtime(b)" query and have to insert something as b that in every case is larger than date...)

The behaviour changed between 4.0.22 and 4.0.23 and ist still present in 4.0.24.

Maybe bug report #6439 is relevant here.

bye,

-christian-

How to repeat:
-

Suggested fix:
-

The fix for #6439 limits FROM_UNIXTIME() to TIMESTAMP_MAX_VALUE
which is 2145916799 or 2037-12-01 23:59:59 GMT

Returning NULL for invalid dates is ok but please keep in mind that you called the function from_unixtime() and a unix timestamp is not considered to be limited at
    sql/mysql_priv.h:#define TIMESTAMP_MAX_VALUE 2145916799
but at
    /usr/include/limits.h:        #  define INT_MAX       2147483647
and worked quite well up to this range. 

I know that it is documented only up to the year 2037 but the corresponding timestamp is AFAIK not available as SQL "constant" so comparations with power(2,32)-1 are still
used in production (esp. by C programmers, I guess..)

bye,

-christian-

Patch is ready and submitted for review. Though not commited, as patches for 4.0 and 4.1 versions are significantly different and commit would result in merge difficulties when post-review fixes are done.

Version 4.0.25 has just been released. Has this bug been closed there? I cannot find the bug number in the changelog but would be quite surprised if you break such an important  function in a stable branch and not fix it in three following minor releases...

Hi Christian,

Sorry, but the bug is still in the patch pending state. Reviewers are busy with bugfixing
and some bugs stall in "patch pending"  because of the bugs with higher priority. I'll look what I can do to speed up the review process, but I'm afraid that it is not much.

Petr

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9947

ChangeSet@1.2193, 2006-08-02 17:51:55+04:00, petr@mysql.com +6 -0
  Fix Bug #9191 from_unixtime(power(2,31)-1) now epoch instead of 2038-01-19
  (4.0 version)

Thanks a lot for fixing, I had already given up hope :))

-christian-

Sorry to say this, cristian, but this is not the end. The commited fix is 4.0-only. 4.1 version is yet to come. That's because timezones were added in 4.1, and the code is significantly different.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10003

ChangeSet@1.2193, 2006-08-03 15:35:01+04:00, petr@mysql.com +6 -0
  Fix Bug #9191 from_unixtime(power(2,31)-1) now epoch instead of 2038-01-19
  (4.0 version, recommited to fix a typo in a test file)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10407

ChangeSet@1.2193, 2006-08-15 09:29:07+04:00, petr@mysql.com +6 -0
  Fix Bug #9191 from_unixtime(power(2,31)-1) now epoch instead of 2038-01-19
  (4.0 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 range for 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).
  
  Note: 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 (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. 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)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/10586

ChangeSet@1.2533, 2006-08-17 18:37:04+04:00, petr@mysql.com +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).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12427

ChangeSet@1.2193, 2006-09-22 22:29:58+04:00, petr@mysql.com +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).

Sent code review comments by email (as a second reviewer)

Changing to "patch approved" with comments,
with the option to the author to ask for another review if implementing the
changes turns out to be a significant diff.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/14060

ChangeSet@1.2533, 2006-10-20 16:19:12+04:00, petr@mysql.com +17 -0
  Fix Bug #9191 from_unixtime(power(2,31)-1) now epoch instead of 2038-01-19
  (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).

Pushed in 4.1.22, 5.0.29, 5.1.13 trees.

Noted in 4.1.22, 5.0.30 (not 5.0.29), 5.1.13 changelogs.

FROM_UNIXTIME() did not accept arguments up to POWER(2,31)-1, which
it had previously.