Bug #25454 DATE_FORMAT(<...>) result is a localized character string but type is BINARY
Submitted: 7 Jan 2007 17:02 Modified: 24 Nov 2008 13:36
Reporter: d di (Basic Quality Contributor) Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Data Types Severity:S4 (Feature request)
Version:5.0.22 OS:Linux (Linux)
Assigned to: CPU Architecture:Any
Tags: date_format, lcase, LOWER, qc, ucase, upper

[7 Jan 2007 17:02] d di
Description:
Executing DATE_FORMAT wrapped within LOWER or UPPER does not work, LOWER/UPPER seems to be ignored by the server.

How to repeat:
SELECT UPPER(DATE_FORMAT(NOW(), '%b'))
yields:
Jan

SELECT UPPER('Jan')
yields:
JAN

Suggested fix:
[7 Jan 2007 19:04] MySQL Verification Team
Hello David,

Thank you for the report but this is not a bug. Result of DATE_FORMAT() function is VARBINARY. LOWER()/UPPER() functions convert values according given character set but VARBINARY doesn't have a character set.

Use CAST() or CONVERT() functions to convert it to CHAR type first:

mysql> SELECT UPPER(CAST(DATE_FORMAT(NOW(), '%b') AS CHAR(10)));
+---------------------------------------------------+
| UPPER(CAST(DATE_FORMAT(NOW(), '%b') AS CHAR(10))) |
+---------------------------------------------------+
| JAN                                               |
+---------------------------------------------------+
1 row in set (0.00 sec)
[8 Jan 2007 10:33] d di
Yeah, I sort of guessed that ;-).
(Casting to CHAR also happens to be the workaround I've currently implemented.)

Do you not consider it a bug that DATE_FORMAT returns a VARBINARY instead of a string in the locale the server is using?  That would seem to make more sense, since one of the functions of DATE_FORMAT is to convert a time stamp to regional strings in a specific, known locale.

If you do not consider the binary result type containing clearly non-binary, localized data as being a bug, would you mind explaining how you reached that conclusion?
[8 Jan 2007 11:04] Sergei Golubchik
We do not consider it a bug in the sence of "unintentional programming mistake".
But you're right, DATE_FORMAT result isn't really a binary string, it has some character set which is discarded when result is casted to VARBINARY.
There're more functions affected - e.g. HEX or FORMAT. We plan to change it, it's in TODO.
[8 Jan 2007 11:09] d di
Makes sense.

Thanks
[24 Nov 2008 13:36] Alexander Barkov
This problem was previously fixed under terms of Bug#28875
in 5.0.48 and 5.1.22.

Marking this bug as duplicate for Bug#28875.