Bug #12356 DATE_SUB or DATE_ADD incorrectly returns null
Submitted: 3 Aug 2005 20:04 Modified: 4 Aug 2006 17:19
Reporter: Steven Choi Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:4.1.12/5.0 BK OS:Microsoft Windows (XP Pro/Linux)
Assigned to: Tomash Brechko CPU Architecture:Any

[3 Aug 2005 20:04] Steven Choi
Description:
When performing DATE_SUB/ADD where the resulting date is between '9999-12-31 00:00:00' and '9999-12-31 23:59:59', the date returned is null.

How to repeat:
These return correct datetime:

SELECT DATE_SUB(str_to_date('9999-12-31 00:00:59','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE)
SELECT DATE_ADD(str_to_date('9999-12-30 23:58:59','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE)

whereas these return nulls:

SELECT DATE_SUB(str_to_date('9999-12-31 00:01:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE)
SELECT DATE_ADD(str_to_date('9999-12-30 23:59:00','%Y-%m-%d %H:%i:%s'), INTERVAL 1 MINUTE)

the difference is that the former returns a date in 9999-12-30 and the latter returns a date in 9999-12-31

Suggested fix:
I'm not sure.  I haven't looked at the implementation code.
[10 Apr 2006 22:34] Bugs System
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/4751
[13 Apr 2006 21:32] Konstantin Osipov
Approved by email.
[27 Jun 2006 12:55] Konstantin Osipov
Hartmut, thanks for your work, Tomash will take over and push your patch.
[27 Jun 2006 15:33] Bugs System
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/8326
[28 Jun 2006 13:39] Konstantin Osipov
Pushed into 4.1 tree tagged 4.1.21
[29 Jun 2006 15:56] Konstantin Osipov
Merged into 5.0-runtime
[8 Jul 2006 17:51] Ingo Strüwing
Pushed to 5.1.12 and 5.0.24.
[4 Aug 2006 17:19] Paul Dubois
Noted in 4.1.21, 5.0.24, 5.1.12 changelogs.

DATE_ADD() and DATE_SUB() returned NULL when the result date was on the day
'9999-12-31'.