Bug #22229 Bug in DATE_ADD()
Submitted: 11 Sep 2006 6:24 Modified: 11 Dec 2006 3:53
Reporter: Rajesh Sharma Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.1.19/5.0.25 OS:Linux (Linux/MS-Windows XP (SP2))
Assigned to: Ramil Kalimullin CPU Architecture:Any
Tags: + IN DATES, DATE_ADD

[11 Sep 2006 6:24] Rajesh Sharma
Description:
mysql> select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
+----------------------------------------------------------+
| str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE |
+----------------------------------------------------------+
| 2136-01-07 04:38:16                                      |
+----------------------------------------------------------+
1 row in set (0.00 sec)

How to repeat:
Just use Simple SELECT As:
select str_to_date('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE;
[11 Sep 2006 6:32] Alexander Keremidarski
Verified with 5.0 from:
ChangeSet@1.2263, 2006-09-08 16:16:39+05:00, gluh@mysql.com +4 -0

It is expected to get invalid result because:

mysql> SELECT str_to_date('10:00 PM', '%h:%i %p');
+-------------------------------------+
| str_to_date('10:00 PM', '%h:%i %p') |
+-------------------------------------+
| 22:00:00                            |
+-------------------------------------+

However:

SELECT '22:00:00' + INTERVAL 10 MINUTE;
+---------------------------------+
| '22:00:00' + INTERVAL 10 MINUTE |
+---------------------------------+
| NULL                            |
+---------------------------------+

So the result of STR_TO_DATE('10:00 PM', '%h:%i %p') + INTERVAL 10 MINUTE 
should be NULL as well.
[15 Sep 2006 19:41] Timothy Smith
The proper way to add 10 minutes to a TIME value si with addtime():

mysql> select addtime(str_to_date('10:00 PM', '%h:%i %p'), '00:10:00') t;
+----------+
| t        |
+----------+
| 22:10:00 | 
+----------+
1 row in set (0.00 sec)

Of course, the bug as described by Salle is another matter.
[6 Oct 2006 10:16] Andrey Hristov
Not reproducible with 5.1.12-bk
[17 Oct 2006 11:31] 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/13798

ChangeSet@1.2302, 2006-10-17 16:30:49+05:00, ramil@mysql.com +3 -0
  Fix for bug #22229: Bug in DATE_ADD()
  
  From the manual:
  date arithmetic operations require complete dates and do not work with
  incomplete dates such as '2006-07-00' or badly malformed dates.
[2 Nov 2006 15:14] Andrey Hristov
Please check bug#23653 Crash if last_day('0000-00-00')
when closing this one. LAST_DAY() reuses code of STR_TO_DATE() which is fixed in the current patch.
[11 Dec 2006 3:53] Paul DuBois
Noted in 5.0.32, 5.1.15 changelogs.

DATE_ADD() requires complete dates with no zero parts, but sometimes
did not return NULL when given such a date.