MySQL Bugs: #41343: Can't tell the difference between 000C84023FãA and 000C84023FaA

Bug #41343	Can't tell the difference between 000C84023FãA and 000C84023FaA
Submitted:	9 Dec 2008 22:30	Modified:	12 Dec 2008 14:36
Reporter:	F Guo	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Charsets	Severity:	S2 (Serious)
Version:	5.0.45-5.0.67	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
We have a table "user" have a column "name", which has records with name 000C84023FãA and 000C84023FaA. 
Query: select * from user where name='000C84023FãA' will return both record.

How to repeat:
CREATE TABLE test (
  id int NOT NULL auto_increment,
  name varchar(20) NOT NULL,
  PRIMARY KEY (id)
);
insert into test values (null,'000C84023FãA'),(null,'000C84023FaA');
select * from test where name='000C84023FãA';

Suggested fix:
No

Query "select ascii(mid(name,11,1)) from test;" return different ascii value for 'a' and 'ã'.

Thank you for the report.

Please provide output of SHOW VARIABLES LIKE 'char%', SHOW VARIABLES LIKE 'coll%' and SHOW CREATE TABLE test (latest one to check which default character set is used for the table).

Mysql 5.0.67 was freshly installed on a windows 2003 server.

mysql> show variables like 'char%';
+--------------------------+---------------------------------------------------------+
| Variable_name            | Value                                                   |
+--------------------------+---------------------------------------------------------+
| character_set_client     | utf8                                                    |
| character_set_connection | utf8                                                    |
| character_set_database   | latin1                                                  |
| character_set_filesystem | binary                                                  |
| character_set_results    | utf8                                                    |
| character_set_server     | latin1                                                  |
| character_set_system     | utf8                                                    |
| character_sets_dir       | C:\Program Files\MySQL\MySQL Server 5.0\share\charsets\ |
+--------------------------+---------------------------------------------------------+
8 rows in set (0.00 sec)

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

mysql> show create table test;
+-------+--------------------------------------------------------------------------------
-------------------------------+
| Table | Create Table
                               |
+-------+--------------------------------------------------------------------------------
-------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------
-------------------------------+
1 row in set (0.00 sec)

Thank you for the feedback.

You indicated character_set_client is utf8. How do you insert into the table? Do you use MySQL command line client?

I uese MySQL Administrator.

Many thanks for writing a bug report.

In latin1_swedish_ci collation:

ã=a which also means a=ã. So the behaviour is an expected behaviour.

When you want that ã != a then you should use a binary collation.