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