MySQL Bugs: #61982: case insensitive different behaviour: JOIN and SELECT

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:	Windows (Win 7)
Assigned to:		CPU Architecture:	Any
Tags:	JOIN case insensitive SELECT

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

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)

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".

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".