MySQL Bugs: #58936: Wrong Unique duplicate error

Bug #58936	Wrong Unique duplicate error
Submitted:	15 Dec 2010 5:11	Modified:	15 Dec 2010 6:14
Reporter:	Ian Lord	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.5.4-m3	OS:	FreeBSD (7.2)
Assigned to:		CPU Architecture:	Any
Tags:	duplicate, unique

Description:
For some reason an empty value in a varchar '' and a one space value ' ' are treated like equal during an unique constraint validation.

How to repeat:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 31
Server version: 5.5.7-rc-log FreeBSD port: mysql-server-5.5.7

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test;
Database changed
mysql> CREATE TABLE test (`foo` VARCHAR( 1 ) NOT NULL) ENGINE = InnoDB;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into test values (' '),('');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select CONCAT('!',foo,'!') from test;
+---------------------+
| CONCAT('!',foo,'!') |
+---------------------+
| ! !                 |
| !!                  |
+---------------------+
2 rows in set (0.00 sec)

mysql> ALTER TABLE `test` ADD UNIQUE (`foo`);
ERROR 1062 (23000): Duplicate entry ' ' for key 'foo'
mysql> 

Suggested fix:
None, sorry

By thw way, that bug existed in version 5.5.4-m3 also so it's now new to 5.5.7

By thw way, that bug existed in version 5.5.4-m3 also so it's not new to 5.5.7

Looks like expected behaviour:

mysql> select ''=' ';
+--------+
| ''=' ' |
+--------+
|      1 |
+--------+
1 row in set (2 min 51.52 sec)

http://dev.mysql.com/doc/refman/5.5/en/char.html

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

...

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