MySQL Bugs: #7739: UPPER(CONCAT(...)) lowercase when CONCAT() deals with mixed types

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	CPU Architecture:	Any

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.

Thank you for the bug report.

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.

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?

See the discussion regarding UPPER() under the discussion
of BINARY on this page:

http://dev.mysql.com/doc/mysql/en/Cast_Functions.html

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.