MySQL Bugs: #31324: str_to_date returns null

Bug #31324	str_to_date returns null
Submitted:	1 Oct 2007 18:49	Modified:	30 Oct 2007 16:57
Reporter:	Mr Wakazula	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Windows (2003 SP1)
Assigned to:	Tatiana Azundris Nuernberg	CPU Architecture:	Any

Description:
If the supplied format string contains characters that are not format specifiers, then STR_TO_DATE may return NULL.

CONTEXT
MySql Server:  5.0.45-community-nt
Server OS: Windows 2003 (SP1) 

How to repeat:
In the following examples I've introduced `a` and `t` which are not format specifiers. 

/*WORKS*/
select str_to_date('at 15:49:35', 'at %T:%i:%s');
select str_to_date('at Mon, 17 Sep 2007', 'at %a, %e %b %Y');
select str_to_date('at Mon, 17 Sep 2007 at', 'at %a, %e %b %Y at');
select str_to_date('at Mon, 17 Sep 2007 15:49:35', 'at %a, %e %b %Y %T:%i:%s');

/*FAILS WITH NULL*/
select str_to_date('15:49:35 at', '%T:%i:%s at');
select str_to_date('at Mon, 17 Sep 2007 15:49:35 at', 'at %a, %e %b %Y %T:%i:%s at');

Suggested fix:
STR_TO_DATE(input string, format string)

If the format string contains characters that are not format specifiers (e.g. the letter `a` and the letter `t`

AND 

these values (e.g. the letters `a` and `t`) are contained within the input string

THEN

str_to_date should parse the input data without error

Thank you for a bug report. Verified just as described.

Inconsistency: either both cases should be allowed, or both should return NULL.

Please check the documentation, and consider whether it is a bug or not.

I don't understand Timour's comment.  It shoulds like he is suggesting that the software should be written to match what the documentation says.

Since the str_to_date function does not process input data consistently, would this not be considered a bug?

select str_to_date('at Mon, 17 Sep 2007 15:49:35 at', 'at %a, %e %b %Y %T:%i:%s at');

Please note that %T matches the entirety of "15:49:35", so all that's left of the input is " at", while the pattern still expects ":%i:%s at".

This works:
select str_to_date('at Mon, 17 Sep 2007 15:49:35 at', 'at %a, %e %b %Y %H:%i:%s at');
=> 2007-09-17 15:49:35

Incidentally, this also works (but probably isn't what you want):
select str_to_date('at Mon, 17 Sep 2007 15:49:35:44:55', 'at %a, %e %b %Y %T:%i:%s at');
2007-09-17 15:44:55

select str_to_date('Mon, 17 Sep 2007 15 at', '%a, %e %b %Y %H at');
2007-09-17 15:00:00

Thank you for your posting Tatjana. I misinterpreted the MySql documentation
`%T Time, 24-hour (hh:mm:ss)` as `%T represents the hour (24 hours, military time) in a time string hh:mm:ss`

I should have used: `%H Hour (00..23)`

Out of curiosity, why does the following query work?

select str_to_date('at 15:49:35', 'at %T:%i:%s');

Should it not fail expecting

select str_to_date('at 15:49:35:49:35'', 'at %T:%i:%s');

"out of input" is subtly different from "invalid (unexpected) input" ;)