MySQL Bugs: #42382: Handling of trailing spaces in VARCHAR seems broken.

Bug #42382	Handling of trailing spaces in VARCHAR seems broken.
Submitted:	27 Jan 2009 17:01	Modified:	27 Jan 2009 22:12
Reporter:	Jon Leichter	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.1.30	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
It seems that trailing spaces ARE stored in VARCHAR (as they should be), but results of various queries do NOT produce consistent results.

How to repeat:
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.1.30    |
+-----------+
1 row in set (0.00 sec)

mysql> create table table1 (v varchar(20));
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> select v, length(v) from table1;
+--------+-----------+
| v      | length(v) |
+--------+-----------+
| hello  |         5 |
| hello  |         6 |
+--------+-----------+
2 rows in set (0.00 sec)

mysql> select concat('x', v, 'x') from table1;
+---------------------+
| concat('x', v, 'x') |
+---------------------+
| xhellox             |
| xhello x            |
+---------------------+
2 rows in set (0.00 sec)

mysql> select distinct v from table1;
+-------+
| v     |
+-------+
| hello |
+-------+
1 row in set (0.00 sec)

mysql> select v from table1 where v = 'hello';
+--------+
| v      |
+--------+
| hello  |
| hello  |
+--------+
2 rows in set (0.00 sec)

mysql> select v from table1 where v = 'hello ';
+--------+
| v      |
+--------+
| hello  |
| hello  |
+--------+
2 rows in set (0.00 sec)

mysql> select v from table1 where v = 'hello               ';
+--------+
| v      |
+--------+
| hello  |
| hello  |
+--------+
2 rows in set (0.00 sec)

Suggested fix:
Trailing spaces should be considered as significant: 'hello' != 'hello '

The comparison behavior that you observe is as documented:

http://dev.mysql.com/doc/refman/5.1/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."

I appreciate to no end your willingness to give me feedback to the issue.  I truly sructinized the bugs and the doc and managed to miss this tidbit.

I'll look into using a collation that does not include PADSPACE (as I'm guessing that's the solution to my problem).

Will MySQL ever support any collations with the "NO PAD" characteristic?

I agree: are later versions of 5.1 or 6.0 going to support 'NOPAD'?  Looking through the bug system, this question seems to crop up periodically.  It is definitely unexpected behavior for those who come from other databases.  It doesn't matter if it's documented; I would wager a great number of database developers would be surprised to find that length(x) != length(y), yet x = y?!

Thank you for the feedback.

Please use VARBINARY or binary collations or BINARY() operator.

yes, i am very surprised to see this behavior.  it's rather dumb.  it's preventing me from cleaning up rows in my database that have spaces at the end as i have to check for the existence of other rows that do not have spaces at the end before i make any changes.  using BINARY() takes the query from a nominal 0.01s to 3.77s.  and there are several hundred thousand of them.  thank you,  mysql, for once again providing us with such an outstanding feature.  /me vomits.