Description:
When running UPPER() on several commands that return different data types, the output string is not returned in upper case without first using convert. I would expect that anything that returns a string shouldn't care about having to convert the data in order to use UPPER.
Case in point. Running UPPER(MD5(NOW())) should resonably return a upper string. Assuming that it's the binary return issue of MD5 then UPPER(CONVERT(MD5(NOW()))) would then be expected to return a string, as CONVERT should always return a string in the default character set (correct me if I'm wrong here). To complicate it a little more, when I do a CONCAT(MD5('xx'), 'yy') I would also expect this to be a string in the current character set as well. So, as to why UPPER would fail on an output of a function that always returns a string is what I think is a bug.
The bug was discovered when trying to compare data on a front end client whereas the MD5 checksum was all in UPPER and we had expected the UPPER(MD5()) to be honored, which it was not.
How to repeat:
On the 5.1.x series mysql client, run these statements.
[127.0.0.1] {root} (hsclients) >SELECT UPPER(MD5(NOW()));
+----------------------------------+
| UPPER(MD5(NOW())) |
+----------------------------------+
| 7f39cdb2d575aa808d531fe9a2fb2082 |
+----------------------------------+
[127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT(MD5(NOW()), ' is an MD5 string of NOW()'));
+------------------------------------------------------------+
| UPPER(CONCAT(MD5(NOW()), ' is an MD5 string of NOW()')) |
+------------------------------------------------------------+
| 58c84467c333d7b058f57d90458c29e1 is an MD5 string of NOW() |
+------------------------------------------------------------+
[127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT(NOW(), ' is an string of NOW()'));
+------------------------------------------------+
| UPPER(CONCAT(NOW(), ' is an string of NOW()')) |
+------------------------------------------------+
| 2009-10-09 13:23:59 is an string of NOW() |
+------------------------------------------------+
-- Examples of what does work.
[127.0.0.1] {root} (hsclients) >SELECT UPPER('lower');
+----------------+
| UPPER('lower') |
+----------------+
| LOWER |
+----------------+
[127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT('low', 'er')) ;
+----------------------------+
| UPPER(CONCAT('low', 'er')) |
+----------------------------+
| LOWER |
+----------------------------+
[127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT(SUBSTRING('lower',1,3), 'er')) ;
+---------------------------------------------+
| UPPER(CONCAT(SUBSTRING('lower',1,3), 'er')) |
+---------------------------------------------+
| LOWER |
+---------------------------------------------+
[127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT(CONVERT(MD5(NOW()) USING utf8), ' is an MD5 string of NOW()'));
+-----------------------------------------------------------------------------+
| UPPER(CONCAT(CONVERT(MD5(NOW()) USING utf8), ' is an MD5 string of NOW()')) |
+-----------------------------------------------------------------------------+
| 2634BF31CE6954A67714C6107247084D IS AN MD5 STRING OF NOW() |
+-----------------------------------------------------------------------------+
[127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT(CONVERT(NOW() USING utf8), ' is an string of NOW()'));
+------------------------------------------------------------------------+
| UPPER(CONCAT(CONVERT(NOW() USING utf8), ' is an MD5 string of NOW()')) |
+------------------------------------------------------------------------+
| 2009-10-09 13:44:58 IS AN STRING OF NOW() |
+------------------------------------------------------------------------+
Suggested fix:
Have UPPER upcase the result.
Description: When running UPPER() on several commands that return different data types, the output string is not returned in upper case without first using convert. I would expect that anything that returns a string shouldn't care about having to convert the data in order to use UPPER. Case in point. Running UPPER(MD5(NOW())) should resonably return a upper string. Assuming that it's the binary return issue of MD5 then UPPER(CONVERT(MD5(NOW()))) would then be expected to return a string, as CONVERT should always return a string in the default character set (correct me if I'm wrong here). To complicate it a little more, when I do a CONCAT(MD5('xx'), 'yy') I would also expect this to be a string in the current character set as well. So, as to why UPPER would fail on an output of a function that always returns a string is what I think is a bug. The bug was discovered when trying to compare data on a front end client whereas the MD5 checksum was all in UPPER and we had expected the UPPER(MD5()) to be honored, which it was not. How to repeat: On the 5.1.x series mysql client, run these statements. [127.0.0.1] {root} (hsclients) >SELECT UPPER(MD5(NOW())); +----------------------------------+ | UPPER(MD5(NOW())) | +----------------------------------+ | 7f39cdb2d575aa808d531fe9a2fb2082 | +----------------------------------+ [127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT(MD5(NOW()), ' is an MD5 string of NOW()')); +------------------------------------------------------------+ | UPPER(CONCAT(MD5(NOW()), ' is an MD5 string of NOW()')) | +------------------------------------------------------------+ | 58c84467c333d7b058f57d90458c29e1 is an MD5 string of NOW() | +------------------------------------------------------------+ [127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT(NOW(), ' is an string of NOW()')); +------------------------------------------------+ | UPPER(CONCAT(NOW(), ' is an string of NOW()')) | +------------------------------------------------+ | 2009-10-09 13:23:59 is an string of NOW() | +------------------------------------------------+ -- Examples of what does work. [127.0.0.1] {root} (hsclients) >SELECT UPPER('lower'); +----------------+ | UPPER('lower') | +----------------+ | LOWER | +----------------+ [127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT('low', 'er')) ; +----------------------------+ | UPPER(CONCAT('low', 'er')) | +----------------------------+ | LOWER | +----------------------------+ [127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT(SUBSTRING('lower',1,3), 'er')) ; +---------------------------------------------+ | UPPER(CONCAT(SUBSTRING('lower',1,3), 'er')) | +---------------------------------------------+ | LOWER | +---------------------------------------------+ [127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT(CONVERT(MD5(NOW()) USING utf8), ' is an MD5 string of NOW()')); +-----------------------------------------------------------------------------+ | UPPER(CONCAT(CONVERT(MD5(NOW()) USING utf8), ' is an MD5 string of NOW()')) | +-----------------------------------------------------------------------------+ | 2634BF31CE6954A67714C6107247084D IS AN MD5 STRING OF NOW() | +-----------------------------------------------------------------------------+ [127.0.0.1] {root} (hsclients) >SELECT UPPER(CONCAT(CONVERT(NOW() USING utf8), ' is an string of NOW()')); +------------------------------------------------------------------------+ | UPPER(CONCAT(CONVERT(NOW() USING utf8), ' is an MD5 string of NOW()')) | +------------------------------------------------------------------------+ | 2009-10-09 13:44:58 IS AN STRING OF NOW() | +------------------------------------------------------------------------+ Suggested fix: Have UPPER upcase the result.