Bug #10680 Incorrect docs about space-padding in TEXT field sorting order
Submitted: 17 May 2005 0:24 Modified: 5 Dec 2005 18:21
Reporter: Ted Cui Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S2 (Serious)
Version:4.1.10 and 4.1.11 OS:FreeBSD (Freebsd4.10)
Assigned to: Paul DuBois CPU Architecture:Any

[17 May 2005 0:24] Ted Cui
Description:

mysql> create table t (u text binary, unique index u_uidx(u(255))) engine = InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t values('a ');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values('a  ');
ERROR 1062 (23000): Duplicate entry 'a  ' for key 1
mysql> insert into t values('a');
ERROR 1062 (23000): Duplicate entry 'a' for key 1

| version_comment                 | FreeBSD port: mysql-server-4.1.11_1                      |
| version_compile_machine         | i386                                                     |
| version_compile_os              | portbld-freebsd4.10                         

| version_comment                 | FreeBSD port: mysql-server-4.1.10                           |
| version_compile_machine         | i386                                                        |
| version_compile_os              | portbld-freebsd4.10                                         |

How to repeat:
See description

Suggested fix:
Trailing space should be indexed.
[17 May 2005 1:48] MySQL Verification Team
mysql> create table t (u text binary) engine = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values('a');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values('a ');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values('a  ');
Query OK, 1 row affected (0.01 sec)

mysql> select u, length(u) from t;
+------+-----------+
| u    | length(u) |
+------+-----------+
| a    |         1 |
| a    |         2 |
| a    |         3 |
+------+-----------+
3 rows in set (0.01 sec)

mysql> alter table t add unique index u_uidx(u(254));
ERROR 1062 (23000): Duplicate entry 'a ' for key 1
mysql> 

mysql> select version();
+------------------+
| version()        |
+------------------+
| 4.1.12-debug-log |
+------------------+
1 row in set (0.00 sec)
[17 May 2005 1:54] MySQL Verification Team
Notice when the binary is removed not presents the error. I will let Heikki
to comment about.

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql> create table t (u text) engine = InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t values('a');
Query OK, 1 row affected (0.01 sec)

mysql> insert into t values('a ');
Query OK, 1 row affected (0.00 sec)

mysql> insert into t values('a  ');
Query OK, 1 row affected (0.00 sec)

mysql> alter table t add unique index u_uidx(u(254));
Query OK, 3 rows affected (0.02 sec)
Records: 3  Duplicates: 0  Warnings: 0
[17 May 2005 11:58] Heikki Tuuri
Hi!

Actually, TEXT columns SHOULD be compared as space-padded at the end. Please see the MySQL manual. But the change was forgotten from 4.1, and the manual is inaccurate in that respect.

Thus, the bug really is that no duplicate key error is produced if the column type is TEXT without the keyword BINARY.

In 5.0.x, TEXT columns are compared as space-padded at the end.

I am assigning this bug to Paul DuBois, because the manual should state that TEXT columns are compared as space-padded starting from 5.0.x. Paul should find out what is x.

We cannot change the sorting order of columns in 4.1.xx, since it is a stable version.

Workaround: you probably want to use BLOB, which compares strings WITHOUT space-padding at the end.

Regards,

Heikki
[1 Jun 2005 22:16] Ted Cui
Thanks for the clarification!

My temporary workaround is to create a 'hash' column which contains the md5() for the string column to enforce the uniqueness.
[6 Aug 2005 23:17] Mike Hillyer
Changing status from Documenting. Documenting status is for items that need a changelog entry.
[5 Dec 2005 18:21] Paul DuBois
Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).