Bug #22229 Bug in DATE_ADD()

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;

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.

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.

Not reproducible with 5.1.12-bk

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.

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.

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.