MySQL Bugs: #20987: str_to_date doesn't accept user variable for specification

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

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;

Reply from Konstantin:

"I'd agree it's a valid feature request - the problem report
describes an inconsistency and inconvenience."

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).

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.

Available in 5.0.25 .

Available in 5.1.12

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.