MySQL Bugs: #3088: upper() does not work as expected in where with varchar binary fields

Bug #3088	upper() does not work as expected in where with varchar binary fields
Submitted:	7 Mar 2004 2:43	Modified:	10 Mar 2004 1:40
Reporter:	Christian Kirsch	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1.1	OS:	Linux (Linux)
Assigned to:	Alexander Barkov	CPU Architecture:	Any

Description:
select foo from bar where upper(foo) like "%BLA%"

doesn't find anything, though a matching record exists. foo is defined as varchar(10) binary. If you remove the binary, everything works as expected. This seems to be an incompatible change to MySQL 3.23 (I didn't try 4.0.x)

I have already reported this bug via mysqlbug. Since I couldn't see it neither in bugs.mysql.com nor in the bugs mailinglist, I'm re-reporting it here.

How to repeat:
create table test (a varchar(10) binary);
insert into test values ('bla');
select a from test where upper(a) like '%BLA%';

the last statement doesn't return anything at all, although it should find the single record of table 'test'.

Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

This is not a bug. This is incompatible change since 4.0.
"a varchar(10) binary" is an array of bytes, and UPPER()
does not change it anymore.

Thank you for reporting! I wrote a letter to
our doc team to check the manual, and fix
according to the latest changes.

If you need possible workarounds please write to 
mysql@lists.mysql.com with a copy to bar@mysql.com.

Addition: several workarounds are possible depending on the reason
why you use a binary field type with case insensitive search.

One of them is:

create table test (a varchar(10) character set latin1 collate latin1_bin);
insert into test values ('bla');
select a from test where upper(a) like '%BLA%';

If it does not work for you, please write to mysql@lists.mysql.com with
a copy to bar@mysql.com.

I happen to disagree with Alexander's point of view. Why should the semantic of VARCHAR BINARY differ completely from that of VARCHAR? Why should a VARCHAR BINARY be an array of bytes despite its name? There are already byte data types available in MySQL.