Bug #21335 UNIQUE index on TEXT columns allows trailing whitespace

• Submitted: 28 Jul 2006 23:08
• Modified: 5 Jul 2007 20:30
• Reporter: Dean Ellis
• Status: Not a Bug
• Category: MySQL Server: Documentation
• Severity: S2 (Serious)
• Version: 4.1.22 4.0.28
• OS: Any
• Assigned to: Paul DuBois
• CPU Architecture: Any

[28 Jul 2006 23:08] Dean Ellis

Description:
From the manual:

"If a TEXT column is indexed, index entry comparisons are space-padded at the end. This means that, if the index requires unique values, duplicate-key errors will occur for values that differ only in the number of trailing spaces. For example, if a table contains 'a', an attempt to store 'a ' causes a duplicate-key error."

This does not seem to be the case:

mysql> CREATE TABLE t1 ( a TEXT, UNIQUE (a(5)) );
Query OK, 0 rows affected (0.00 sec)

mysql> -- ERROR 1062 (23000): Duplicate entry 'a ' for key 'a' 
mysql> INSERT INTO t1 VALUES ('a'), ('a ');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT hex(a) FROM t1 WHERE a = 'a';
+--------+
| hex(a) |
+--------+
| 61     | 
| 6120   | 
+--------+
2 rows in set (0.00 sec)

How to repeat:
CREATE TABLE t1 ( a TEXT, UNIQUE (a(5)) );
-- ERROR 1062 (23000): Duplicate entry 'a ' for key 'a' 
INSERT INTO t1 VALUES ('a'), ('a ');

[5 Jul 2007 20:07] Paul DuBois

The behavior indicated in the quoted text is true for 5.0.3 and up.
However, the text quoted in the bug report appears to be from the
5.0 manual, for which the text is correct.  The corresponding
passage in the 4.1 manual is worded diffferently because the
end-space behavior differs, and the passage there is correct
for that manual.

Nothing to do here that I can see.