Bug #61982 case insensitive different behaviour: JOIN and SELECT
Submitted: 26 Jul 2011 11:58 Modified: 26 Sep 2011 23:21
Reporter: Ujin Ghost Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: Charsets Severity:S3 (Non-critical)
Version:5.5.13 OS:Microsoft Windows (Win 7)
Assigned to: CPU Architecture:Any
Tags: JOIN case insensitive SELECT

[26 Jul 2011 11:58] Ujin Ghost
Description:
Database CHARACTER SET: utf8
Database COLLATION : utf8_general_ci

LEFT JOIN seems to work different than select on string comparison.

It seems a problem with the character ò.

JOIN: -> ò = O (not ok)

SELECT: -> ò != O (ok)

How to repeat:
Two Tables
----------

CREATE TABLE table1 (Address VARCHAR(100));
CREATE TABLE table2 (Address VARCHAR(100));

INSERT INTO table1 VALUES('Universitat Autònoma de Barcelona');

INSERT INTO table2 VALUES('Universitat Autònoma de Barcelona');
INSERT INTO table2 VALUES('UNIVERISTAT AUTONOMA DE BARCELONA');

SELECT * FROM table1 LEFT JOIN table2 ON table1.Address = table2.Address;

# Result two records (i expect only one)

SELECT 'Universitat Autònoma de Barcelona' = 'UNIVERISTAT AUTONOMA DE BARCELONA'

# Result 0 (different strings, what i expect)

Suggested fix:
characters like ò must be compared in same manner on selects an joins
[26 Jul 2011 12:12] Valeriy Kravchuk
Please, check with a newer version, 5.5.14. I can not repeat this on Mac OS X:

mysql> create database db charset=utf8;Query OK, 1 row affected (0.00 sec)

mysql> use db
Database changed
mysql> CREATE TABLE table1 (Address VARCHAR(100));Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE table2 (Address VARCHAR(100));Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO table1 VALUES('Universitat Autònoma de Barcelona');Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO table2 VALUES('Universitat Autònoma de Barcelona');Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO table2 VALUES('UNIVERISTAT AUTONOMA DE BARCELONA');Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.Address = table2.Address;+------------------------------------+------------------------------------+
| Address                            | Address                            |
+------------------------------------+------------------------------------+
| Universitat Autònoma de Barcelona  | Universitat Autònoma de Barcelona  |
+------------------------------------+------------------------------------+
1 row in set (0.00 sec)

mysql> show variables like 'char%';
+--------------------------+---------------------------------------+
| Variable_name            | Value                                 |
+--------------------------+---------------------------------------+
| character_set_client     | utf8                                  |
| character_set_connection | utf8                                  |
| character_set_database   | utf8                                  |
| character_set_filesystem | binary                                |
| character_set_results    | utf8                                  |
| character_set_server     | latin1                                |
| character_set_system     | utf8                                  |
| character_sets_dir       | /Users/openxs/dbs/5.5/share/charsets/ |
+--------------------------+---------------------------------------+
8 rows in set (0.00 sec)

mysql> show variables like 'coll%';
+----------------------+-------------------+
| Variable_name        | Value             |
+----------------------+-------------------+
| collation_connection | utf8_general_ci   |
| collation_database   | utf8_general_ci   |
| collation_server     | latin1_swedish_ci |
+----------------------+-------------------+
3 rows in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.5.16-debug |
+--------------+
1 row in set (0.00 sec)
[26 Aug 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[27 Sep 2011 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".