Description:
Using characterset utf8 with collation utf8_spanish2_ci, the varchar 'sr' ar equal to
'srr'.
I think that it's a Bug in collation rules.
NOTE:
I'm using utf8_spanish2_ci because utf8_spanish_ci also have a similar bug: vocals acuted
(by example "a" and "á") MySQL say equals! but they are distinct!
And latin1_spanish_ci also have a similar bug: n and n-tilde ("ñ"), MySQL say equals! but
they are distinct!
How to repeat:
My server:
server version: 4.1.10a-nt
My configuration (only this changes in my.ini):
[client]
default-character-set=utf8
[mysqld]
default-character-set=utf8
default-collation=utf8_spanish2_ci
Test my configuration:
mysql> SHOW VARIABLES LIKE 'char%';
+--------------------------+--------------------------------------------------------------
--+
| Variable_name | Value |
+--------------------------+--------------------------------------------------------------
--+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | C:\Archivos de programa\MySQL\MySQL Server
4.1\share\charsets/ |
+--------------------------+--------------------------------------------------------------
--+
mysql> SHOW VARIABLES LIKE 'coll%';
+----------------------+------------------+
| Variable_name | Value |
+----------------------+------------------+
| collation_connection | utf8_general_ci |
| collation_database | utf8_spanish2_ci |
| collation_server | utf8_spanish2_ci |
+----------------------+------------------+
The bug:
mysql> CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_spanish2_ci;
mysql> USE test;
mysql> CREATE TABLE t (c VARCHAR(4)) DEFAULT CHARACTER SET utf8 COLLATE utf8_spanish2_ci
TYPE=InnoDB;
mysql> INSERT INTO t VALUES('sr');
mysql> SELECT * FROM t WHERE c = 'srr';
+------+
| c |
+------+
| sr |
+------+
BUG: 'sr' <> 'srr' !!! the result must be empty!!!
Suggested fix:
I think that it's a Bug in collation rules.
Fix the spanish collation in utf8 and latin1.
The support of spanish charset/collation it's poor.
Description: Using characterset utf8 with collation utf8_spanish2_ci, the varchar 'sr' ar equal to 'srr'. I think that it's a Bug in collation rules. NOTE: I'm using utf8_spanish2_ci because utf8_spanish_ci also have a similar bug: vocals acuted (by example "a" and "á") MySQL say equals! but they are distinct! And latin1_spanish_ci also have a similar bug: n and n-tilde ("ñ"), MySQL say equals! but they are distinct! How to repeat: My server: server version: 4.1.10a-nt My configuration (only this changes in my.ini): [client] default-character-set=utf8 [mysqld] default-character-set=utf8 default-collation=utf8_spanish2_ci Test my configuration: mysql> SHOW VARIABLES LIKE 'char%'; +--------------------------+-------------------------------------------------------------- --+ | Variable_name | Value | +--------------------------+-------------------------------------------------------------- --+ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | utf8 | | character_set_results | utf8 | | character_set_server | utf8 | | character_set_system | utf8 | | character_sets_dir | C:\Archivos de programa\MySQL\MySQL Server 4.1\share\charsets/ | +--------------------------+-------------------------------------------------------------- --+ mysql> SHOW VARIABLES LIKE 'coll%'; +----------------------+------------------+ | Variable_name | Value | +----------------------+------------------+ | collation_connection | utf8_general_ci | | collation_database | utf8_spanish2_ci | | collation_server | utf8_spanish2_ci | +----------------------+------------------+ The bug: mysql> CREATE DATABASE test DEFAULT CHARACTER SET utf8 COLLATE utf8_spanish2_ci; mysql> USE test; mysql> CREATE TABLE t (c VARCHAR(4)) DEFAULT CHARACTER SET utf8 COLLATE utf8_spanish2_ci TYPE=InnoDB; mysql> INSERT INTO t VALUES('sr'); mysql> SELECT * FROM t WHERE c = 'srr'; +------+ | c | +------+ | sr | +------+ BUG: 'sr' <> 'srr' !!! the result must be empty!!! Suggested fix: I think that it's a Bug in collation rules. Fix the spanish collation in utf8 and latin1. The support of spanish charset/collation it's poor.