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: | |
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
[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).