Description:
The MySQL manual for str_to_date states:
If str contains an illegal date, time, or datetime value, STR_TO_DATE() returns NULL. An illegal value also produces a warning. 

Surely "I'm_not_a_valid_date" is not a valid date, time or datetime value?

How to repeat:
mysql> select str_to_date("I'm_not_a_valid_date","I'm_not_a_valid_date");
+------------------------------------------------------------+
| str_to_date("I'm_not_a_valid_date","I'm_not_a_valid_date") |
+------------------------------------------------------------+
| 0000-00-00                                                 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Return NULL.

Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Second argument is format. Format "I'm_not_a_valid_date" should produce 0000-00-00 for any date. This is why you get such result. If specify format which produce meaningful output result is correct:

mysql>  select str_to_date("I'm_not_a_valid_date","%m");
+------------------------------------------+
| str_to_date("I'm_not_a_valid_date","%m") |
+------------------------------------------+
| NULL                                     |
+------------------------------------------+
1 row in set (0.03 sec)

You are incorrect.

The example I gave is only a special case.

Any constant char in the format string (not a % format code) is treated as a literal which is compared against the input string.

For example:
select str_to_date('2008-01-01', 'xyz') returns NULL
while
select str_to_date('xyz', 'xyz') returns 0000-00-00
likewise
select str_to_date('2001-01-01','2001-01-01') returns 0000-00-00
but
select str_to_date('2001-01-01','%Y-%d-%m') returns 2001-01-01

you see, the non % characters are compared in the two strings.

Current behavior: 

mysql> select str_to_date('a', 'a');
+-----------------------+
| str_to_date('a', 'a') |
+-----------------------+
| 0000-00-00            |
+-----------------------+
1 row in set (0.00 sec)

mysql> select str_to_date('2007-01-01', 'a');
+--------------------------------+
| str_to_date('2007-01-01', 'a') |
+--------------------------------+
| NULL                           |
+--------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select str_to_date('a2007-01-01', 'a');
+---------------------------------+
| str_to_date('a2007-01-01', 'a') |
+---------------------------------+
| 0000-00-00                      |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> select str_to_date('2007-01-01a', 'a');
+---------------------------------+
| str_to_date('2007-01-01a', 'a') |
+---------------------------------+
| NULL                            |
+---------------------------------+
1 row in set, 1 warning (0.00 sec)

looks intended (and consistent, if we assume that literals in format are just matched), but it definitely should be documented in more details.

my point is, that, "I'm_not_a_valid_date" isn't a valid date string, and no matter how it is parsed it can never yield a valid date.  It seems to me that this is an "invalid date" as described in the documentation, but a valid date 0000-00-00 is returned instead of NULL.

But the first argument of str_to_date doesn't have to be a valid date. For example

mysql> select str_to_date("I'm_not_a_valid_date 20080102","I'm_not_a_valid_date %Y%m%d");
+----------------------------------------------------------------------------+
| str_to_date("I'm_not_a_valid_date 20080102","I'm_not_a_valid_date %Y%m%d") |
+----------------------------------------------------------------------------+
| 2008-01-02                                                                 | 
+----------------------------------------------------------------------------+

or

mysql> select str_to_date("I'm 1 not 2 a 3 valid_date","I'm %d not %m a %y valid_date");
+---------------------------------------------------------------------------+
| str_to_date("I'm 1 not 2 a 3 valid_date","I'm %d not %m a %y valid_date") |
+---------------------------------------------------------------------------+
| 2003-02-01                                                                | 
+---------------------------------------------------------------------------+

True, but stated another way:
If there are no format characters in the format string, then no date may be extracted from the source string.  If no date can be extracted then NULL should be returned.  Returning 0000-00-00, which isn't documented anywhere may lead to unexpected errors when a SQL_MODE is used that does not allow zero dates.

See also: http://swanhart.livejournal.com/126619.html

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

This is the inverse of the DATE_FORMAT() function. It takes a string
str and a format string format. STR_TO_DATE() returns a DATETIME
value if the format string contains both date and time parts, or a
DATE or TIME value if the string contains only date or time parts. If
the date, time, or datetime value extracted from str is illegal,
STR_TO_DATE() returns NULL and produces a warning.

The server scans str attempting to match format to it. The format
string can contain literal characters and format specifiers beginning
with %. Literal characters in format must match literally in str.
Format specifiers in format must match a date or time part in str.
For the specifiers that can be used in format, see the DATE_FORMAT()
function description.

mysql> SELECT STR_TO_DATE('01,5,2013','%d,%m,%Y'); 
        -> '2013-05-01'
mysql> SELECT STR_TO_DATE('May 1, 2013','%M %d,%Y'); 
        -> '2013-05-01'

Scanning starts at the beginning of str and fails if format is found
not to match. Extra characters at the end of str are ignored.

mysql> SELECT STR_TO_DATE('a09:30:17','a%h:%i:%s'); 
        -> '09:30:17'
mysql> SELECT STR_TO_DATE('a09:30:17','%h:%i:%s'); 
        -> NULL
mysql> SELECT STR_TO_DATE('09:30:17a','%h:%i:%s'); 
        -> '09:30:17'

Unspecified date or time parts have a value of 0, so incompletely
specified values in str produce a result with some or all parts set
to 0:   

mysql> SELECT STR_TO_DATE('abc','abc'); 
        -> '0000-00-00'
mysql> SELECT STR_TO_DATE('9','%m'); 
        -> '0000-09-00'
mysql> SELECT STR_TO_DATE('9','%s'); 
        -> '00:00:09'