Bug #16725 CAST AS BINARY with utf-8 strings gives wrong results
Submitted: 23 Jan 2006 14:21 Modified: 8 Feb 2006 15:37
Reporter: Hans M?ller Email Updates:
Status: Can't repeat Impact on me:
Category:MySQL Server Severity:S3 (Non-critical)
Version:mysql Ver 14.12 Distrib 5.0.16, for pc- OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[23 Jan 2006 14:21] Hans M?ller
1. a mysql database with character encoding utf-8
2. one record with an umlaut ( e.g. "für" / "f\xc3\xbcr" )

3a. using the sql query SELECT table1.column1 FROM table1 WHERE (INSTR(CAST(LOWER(table1.column1) AS BINARY), CAST('für' AS BINARY)) > 0);
4a. result: no rows returned; expected result: one row returned

3b. using the sql query SELECT table1.column1 FROM table1 WHERE (INSTR(LOWER(table1.column1), 'für') > 0);
4b. result as expected: one row returned

Without the umlaut in the query the results are as expected, so the problem lies somewhere with the utf-8 encoded char.

The problem occured with the bugzilla bug tracking system. See the relevant bug https://bugzilla.mozilla.org/show_bug.cgi?id=324413 

How to repeat:
see above
[8 Feb 2006 15:37] Valeriy Kravchuk
Thank you for a problem report. Sorry, but I was not able to repeat the behaviour you described with 5.0.19-BK on Linux. Gives me 1 row with the following character_set_% variables:

mysql> show variables like 'char%'\G
*************************** 1. row ***************************
Variable_name: character_set_client
        Value: utf8
*************************** 2. row ***************************
Variable_name: character_set_connection
        Value: utf8
*************************** 3. row ***************************
Variable_name: character_set_database
        Value: utf8
*************************** 4. row ***************************
Variable_name: character_set_results
        Value: utf8
*************************** 5. row ***************************
Variable_name: character_set_server
        Value: latin1
*************************** 6. row ***************************
Variable_name: character_set_system
        Value: utf8
*************************** 7. row ***************************
Variable_name: character_sets_dir
        Value: /home/openxs/dbs/5.0/share/mysql/charsets/
7 rows in set (0.01 sec)

So, please, try to use newer version, 5.0.18.