MySQL Bugs: #30656: DATE_ADD interval of 99999 fails, but 9999 works.

Bug #30656	DATE_ADD interval of 99999 fails, but 9999 works.
Submitted:	27 Aug 2007 23:52	Modified:	31 Aug 2007 21:53
Reporter:	Daevid Vincent	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0.41	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
using 99999 as the DATE_ADD interval value will result in 000-00-00 but one less 9 will work.

root# mysql --version
mysql Ver 14.12 Distrib 5.0.41, for pc-linux-gnu (i686) using EditLine wrapper

CREATE TABLE `Users` ( 
	`CoreID` int(10) unsigned NOT NULL auto_increment,
	`Username` varchar(155) default NULL,
	`Password` varchar(64) default NULL, 
	`password_expire` timestamp NOT NULL default '0000-00-00 00:00:00',
	PRIMARY KEY (`CoreID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 9999 DAY) WHERE CoreID = 1 LIMIT 1;

UPDATE Users SET password_expire = DATE_ADD(CURRENT_DATE(),INTERVAL 99999 DAY) WHERE CoreID = 1 LIMIT 1;

How to repeat:
set the interval from 9999 to 99999

Suggested fix:
Either document there is an upper bound (this is the lame way to 'fix' this), or just let any integer value work. I should be able to put in 9999999999999 if I want to. it's just a function to calculate the date, why does it care what the number is.

burried in the plethora of bullet points here:
http://dev.mysql.com/doc/refman/5.0/en/datetime.html
"For example, TIMESTAMP values cannot be earlier than 1970 or later than 2038."

So that _is_ the root cause of the problem, but it's still a bug. 

There is no reason (from a mySQL user/PHP developer's perspective) that 2038 should be my upper year limit. I should be able to make any date up to "9999-12-31" 

We're making enterprise level tools that run at US
Government offices, The entire state of Alaska, Military, Colleges,
Fortune 500 companies.... In 21 years from now, all this will
just fail miserably because of some obscure 2038 limitation? This is Y2K
all over again -- unless this is upper bound is fixed.

The DATETIME type does not have this limitation.

Sorry, but this is not a bug. This limitation of TIMESTAMP data type is clearly described in the manual. Read also http://en.wikipedia.org/wiki/Year_2038_problem for some details. 

Just use proper data type, as already suggested.

So, I found out why we had to switch all of our datetimes to timestamps:

http://dev.mysql.com/doc/refman/5.0/en/time-zone-support.html

"The current time zone setting does not affect values in DATE, TIME, or DATETIME columns."

So it appears this is a catch 22... In order to store things in UTC and then use the user's local TZ, you need to use 'timestamps'. But then you can't use them for years past 2038... However 'datetime' will store any date, but you can't store in UTC and display via the TZ setting. 

:-\