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
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: mysql Ver 14.12 Distrib 5.0.16, for pc-
• OS: Linux (Linux)
• CPU Architecture: Any

[23 Jan 2006 14:21] Hans M?ller

Description:
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.