Bug #6206 ENUMs are not case sensitive even if declared BINARY

• Submitted: 22 Oct 2004 1:12
• Modified: 2 Nov 2004 5:16
• Reporter: Shuichi Tamagawa
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.1.6-gamma
• OS: Windows (Win XP / SP2)
• Assigned to: Alexander Barkov
• CPU Architecture: Any

[22 Oct 2004 1:12] Shuichi Tamagawa

Description:
2 sjis ENUM values are mixed up if:

- First two digits of each character in hex is the same
- Last two digits of each character in hex is between 41 and 7A (range of alphabet)
- Each last two digits stands for the same alphabet in different character (e.g. 'A 'and 'a')

How to repeat:
mysql>select 0x9353;
+--------+
| 0x9353 |
+--------+
| 鉄     |
+--------+
1 row in set (0.01 sec)

mysql>select 0x9373;
+--------+
| 0x9373 |
+--------+
| 都     |
+--------+
1 row in set (0.00 sec)

mysql>create table t1(c enum(0x9353,0x9373));
Query OK, 0 rows affected, 1 warning (0.24 sec)

mysql>insert into t1 values (0x9353);
Query OK, 1 row affected (0.01 sec)

mysql>insert into t1 values (0x9373);
Query OK, 1 row affected (0.00 sec)

root[test]>select c, hex(c) from t1;
+------+--------+
| c    | hex(c) |
+------+--------+
| 鉄   | 9353   |
| 鉄   | 9353   |
+------+--------+
2 rows in set (0.00 sec)

[22 Oct 2004 9:13] Hartmut Holzgraefe

this is not charset specific, it is also reproducible with plain ASCII

CREATE TABLE t1 (c enum('a', 'A'));
INSERT INTO t1 VALUES ('a'),('A');
SELECT * FROM t1;

+------+
| c    |
+------+
| a    |
| a    |
+------+

even declaring the ENUM as BINARY doesn't help

CREATE TABLE t2 (c enum('a', 'A') BINARY);
INSERT INTO t2 VALUES ('a'),('A');
SELECT * FROM t2;

+------+
| c    |
+------+
| a    |
| a    |
+------+

[22 Oct 2004 18:29] Shuichi Tamagawa

So, it looks like the combination of two problems.

1. One sjis character(0x9353) is interpreted as two different characters (0x93, 0x53)
2. ENUM values are not case sensitive