Bug #20987 str_to_date doesn't accept user variable for specification
Submitted: 12 Jul 2006 9:05 Modified: 13 Sep 2006 2:31
Reporter: Carsten Pedersen Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.22; 4.1.20 OS:Linux (Linux)
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any

[12 Jul 2006 9:05] Carsten Pedersen
Description:
select d, ds, 

str_to_date will work fine when using a constant string for the format, e.g. str_to_date(ds, "%M %D, %Y"). But when using a user-defined variable e.g. str_to_date(ds, @df), NULL is returned.

Oddly, date_format will happily accept a specification in a user-defined-variable.

How to repeat:
DROP TABLE IF EXISTS `dt`;
CREATE TABLE `dt` (
  d datetime,
  ds char(30)
);

INSERT INTO `dt` (d) VALUES ('2005-10-31'), ('2005-11-30');
set @df:="%M %D, %Y";
update dt set ds = date_format(d, @df);

SELECT * FROM dt;

# So far, so good - date_format accepts a variable 
# for format specifier. But:

select d, ds, str_to_date(ds, @df) from dt;

# However, this works as expected:

select d, ds, str_to_date(ds, "%M %D, %Y") from dt;
[13 Jul 2006 16:33] Calvin Sun
Reply from Konstantin:

"I'd agree it's a valid feature request - the problem report
describes an inconsistency and inconvenience."
[13 Jul 2006 20:55] Stefan Hinz
After quite some investigation using 5.0.21 and 5.0.22, we now know that the problem is different from what we initially thought. Also, it's version-specific (in 5.0.22 it's more or less the other way around from 5.0.21), and it shows up only for particular format strings (literal strings that contain space characters, but not variables that have values containing space characters).
[7 Aug 2006 5:35] 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/10097

ChangeSet@1.2537, 2006-08-07 07:35:28+02:00, tnurnberg@salvation.intern.azundris.com +3 -0
  Bug #20987: str_to_date doesn't accept user variable for specification
  
  str_to_date() would sometimes render NULL if %D was used as rule other than last.
  since this was due to two pointers getting mixed up in the server, this behaviour
  seemed somewhat non-deterministic at SQL level.
[22 Aug 2006 22:27] Chad MILLER
Available in 5.0.25 .
[24 Aug 2006 19:20] Iggy Galarza
Available in 5.1.12
[13 Sep 2006 2:31] Paul DuBois
Noted in 5.0.25, 5.1.12 changelogs.

STR_TO_DATE() sometimes would return NULL if the %D format specifier was not the last specifier in the format string.