Bug #28697 UNIQUE inded on VARCHAR(X) column disallowes proper values on insert.

• Submitted: 26 May 2007 3:39
• Modified: 26 May 2007 5:34
• Reporter: Tobias Asplund
• Status: Not a Bug
• Category: MySQL Server: Data Types
• Severity: S2 (Serious)
• Version: 5.0.41
• OS: Any
• CPU Architecture: Any

[26 May 2007 3:39] Tobias Asplund

Description:
Since VARCHAR() stores trailing spaces, it should make a difference between values with trailing spaces and not, when the non-space characters are the same.

How to repeat:
mysql> CREATE TABLE t1 (a VARCHAR(5));
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO t1 VALUES ('A');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t1 VALUES ('A ');
Query OK, 1 row affected (0.00 sec)

mysql> ALTER TABLE t1 ADD PRIMARY KEY (a);
ERROR 1582 (23000): Duplicate entry 'A ' for key 'PRIMARY'
mysql> SELECT CHAR_LENGTH(a) FROM t1;
+----------------+
| CHAR_LENGTH(a) |
+----------------+
|              1 | 
|              2 | 
+----------------+
2 rows in set (0.00 sec)

mysql>

[26 May 2007 5:34] Valeriy Kravchuk

Thank you for a problem report. Sorry, but this is a documented behaviour (http://dev.mysql.com/doc/refman/5.0/en/char.html):

"Note that all MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces. For example:

mysql> CREATE TABLE names (myname CHAR(10), yourname VARCHAR(10));
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO names VALUES ('Monty ', 'Monty ');
Query OK, 1 row affected (0.00 sec)

mysql> SELECT myname = 'Monty  ', yourname = 'Monty  ' FROM names;
+--------------------+----------------------+
| myname = 'Monty  ' | yourname = 'Monty  ' |
+--------------------+----------------------+
|                  1 |                    1 |
+--------------------+----------------------+
1 row in set (0.00 sec)

Note that this is true for all MySQL versions, and it makes no difference whether your version trims trailing spaces from VARCHAR values before storing them. Nor does the server SQL mode make any difference in this regard.

For those cases where trailing pad characters are stripped or comparisons ignore them, if a column has an index that requires unique values, inserting into the column values that differ only in number of trailing pad characters will result in a duplicate-key error. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error. "

Exactly your case. So, this is not a bug.