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: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'.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.043 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.