MySQL Bugs: #25454: DATE_FORMAT(<...>) result is a localized character string but type is BINARY

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

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:

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)

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?

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.

Makes sense.

Thanks

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.