Bug #19412 UPPER, & UCASE on CONCAT expression of mixed type fail
Submitted: 27 Apr 2006 20:33 Modified: 27 Apr 2006 23:34
Reporter: Philip Sbrogna Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.13-3 OS:Linux (Suse OSS 10.0)
Assigned to: CPU Architecture:Any

[27 Apr 2006 20:33] Philip Sbrogna
Description:
Case change string functions (upper, ucase, lower, lcase) do not work on an expression returned by concat where the arguments of concat include a numeric value.  The type coercion works (ie. CONCAT('wumpus', 42) returns string 'wumpus42', but the case change does not.

How to repeat:
SELECT UPPER(CONCAT('wumpus', 42));

Suggested fix:
SELECT UPPER(CONCAT('wumpus', 42));

should return 'WUMPUS42'. Likewise for other case change string functions with nested concats.
[27 Apr 2006 23:34] MySQL Verification Team
Thank you for the bug report. Please read:

http://dev.mysql.com/doc/refman/4.1/en/string-functions.html

CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. May have one or
more arguments. If all arguments are non-binary strings, the result is a non-binary
string. If the arguments include any binary strings, the result is a binary string.
A numeric argument is converted to its equivalent binary string form; if you want to
avoid that, you can use an explicit type cast, as in this example:

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

<CUT>

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.19-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT UPPER(CONCAT('wumpus', CAST(42 AS CHAR)));
+-------------------------------------------+
| UPPER(CONCAT('wumpus', CAST(42 AS CHAR))) |
+-------------------------------------------+
| WUMPUS42                                  |
+-------------------------------------------+
1 row in set (0.00 sec)

mysql>