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