Bug #2678 BLOB columns comparations
Submitted: 9 Feb 2004 1:48 Modified: 10 Feb 2004 10:57
Reporter: Javier Tacón Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:4.1.1 & 5.0.0 OS:Any (All)
Assigned to: CPU Architecture:Any

[9 Feb 2004 1:48] Javier Tacón
Description:
In the manual says:

If you want to compare a BLOB case-insensitively you can always convert it to uppercase before doing the comparison: 

SELECT 'A' LIKE UPPER(blob_col) FROM table_name;

(http://www.mysql.com/doc/en/Case_Sensitivity_Operators.html)

But it doesn't work with 4.1.1 and 5.0.0

mysql> show variables like '%char%';
+--------------------------+----------------------------------------+
| Variable_name            | Value                                  |
+--------------------------+----------------------------------------+
| character_set_server     | latin1                                 |
| character_set_system     | utf8                                   |
| character_set_database   | latin1                                 |
| character_set_client     | latin1                                 |
| character_set_connection | latin1                                 |
| character-sets-dir       | /usr/local/mysql/share/mysql/charsets/ |
| character_set_results    | latin1                                 |
+--------------------------+----------------------------------------+

How to repeat:
create table test (blobcolumn blob);
insert into test values ('Today');
select * from test where upper(blobcolumn) like 'TODAY'; -- No results
select * from test where upper(blobcolumn)='TODAY'; -- No results
drop table if exists test;
[9 Feb 2004 4:50] Sergei Golubchik
It a documentation issue.

4.1.1 has much better charset support than 4.1, in particular it does not assume that all data are of the default server's charset. BLOB has a binary charset, so UPPER and LOWER are not applicable. Indeed, you cannot say whether '\213', e.g. is an upper-case or a lower-case letter or if it is a letter at all. In fact it is not - as charset is "binary".

For upper/lower case conversion to work you need to cast a blob to a charset, you want your data to be interpreted in.

E.g.

   SELECT 'A' LIKE CONVERT(blob_col using latin1) FROM table_name;

or

   SELECT 'A' LIKE CONVERT(blob_col using utf8) FROM table_name;
[10 Feb 2004 10:57] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).