Bug #7739 UPPER(CONCAT(...)) lowercase when CONCAT() deals with mixed types
Submitted: 7 Jan 2005 23:04 Modified: 8 Jan 2005 13:54
Reporter: Daniel Grace Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.7/4.1.9 BK OS:Linux (Linux 2.2)
Assigned to: Sergei Golubchik

[7 Jan 2005 23:04] Daniel Grace
Description:
When passing the results of CONCAT() to UPPER(), they sometimes are not uppercased.

It appears that that CONCAT() returns a binary string instead of text string when dealing with input values of mixed types. UPPER() does not capitalize these binary strings.  This is a change of behavior from MySQL 4.0.x 

How to repeat:
CREATE TABLE bugreport (
  v1 CHAR(64) NOT NULL,
  v2 CHAR(64) NOT NULL
);
CREATE TABLE bugreport2 (
  id INT NOT NULL
);

INSERT INTO bugreport VALUES ("this", "isbuggy");
INSERT INTO bugreport2 VALUES (6);

SELECT UPPER(CONCAT(v1, v2)) FROM bugreport;
/* correctly returns THISISBUGGY */

SELECT UPPER(CONCAT(br.v1, "-", br2.id, "-", br.v2)) FROM bugreport br, bugreport2 br2;
/* incorrectly returns this-6-isbuggy.  Should return THIS-6-ISBUGGY */

SELECT UPPER("foo"), UPPER(CAST("foo" AS CHAR(255))), UPPER(CAST("foo" AS BINARY));
/* returns FOO FOO foo */

DROP TABLE bugreport;
DROP TABLE bugreport2;

Suggested fix:
Either CONCAT() should not return binary strings unless one of its inputs is one, or UPPER() should treat binary strings as text strings of whatever locale seems intelligent to use at the time.
[7 Jan 2005 23:41] Miguel Solorzano
Thank you for the bug report.
[8 Jan 2005 13:54] Sergei Golubchik
This is expected behaviour. Indeed, you write "CONCAT() should not return binary strings unless one of its inputs is one". But when you cast a number to a string you get a binary string. Thus, CONCAT() correctly returns binary string as a result, because one of the arguments is binary string.
[9 Jan 2005 2:07] Daniel Grace
In that case, is it a problem with MySQL 4.0.x, which works like I expected/wanted it to?  The behavior changed from 4.0.x to 4.1.x.

Is UPPER() not modify the case of a binary string then?
[9 Jan 2005 3:06] Paul Dubois
See the discussion regarding UPPER() under the discussion
of BINARY on this page:

http://dev.mysql.com/doc/mysql/en/Cast_Functions.html
[23 Feb 2005 15:30] Ingo Welling
I just experienced the same. It would have saved me a lot of time if you'd mentioned on the man-page that those functions don't work on binary-strings.