| Bug #25454 | DATE_FORMAT(<...>) result is a localized character string but type is BINARY | ||
|---|---|---|---|
| Submitted: | 7 Jan 2007 18:02 | Modified: | 24 Nov 2008 14:36 |
| Reporter: | d di (Basic Quality Contributor) | ||
| Status: | Duplicate | ||
| Category: | Server: Types | Severity: | S4 (Feature request) |
| Version: | 5.0.22 | OS: | Linux (Linux) |
| Assigned to: | Target Version: | ||
| Tags: | qc, date_format, LOWER, upper, lcase, ucase | ||
| Triage: | Triaged: D5 (Feature request) | ||
[7 Jan 2007 20:04]
Victoria Reznichenko
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 11: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 12: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 12:09]
d di
Makes sense. Thanks
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: