MySQL Bugs: #48309: Unexpected SELECT result looking for a space terminated string value

Bug #48309	Unexpected SELECT result looking for a space terminated string value
Submitted:	26 Oct 2009 13:43	Modified:	29 Oct 2009 16:11
Reporter:	Alessio Cecchin	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.0.51	OS:	Linux (Debian 4.0 (Etch))
Assigned to:		CPU Architecture:	Any
Tags:	Debian, MYSQL SERVER, query, SELECT, ubuntu, where statement

Description:
I write a SELECT query looking for a space terminated string value using an equal test (=). I found a result also if the field HAVE NOT the space at the end. I test it on a Debian Server and also on my Ubuntu 8.10 (Hardy Heron) with my personal MySQL server 5.0.67.

Also the mirror test (looking for a NOT space terminated string putting a space at the end of the string into the condition clause) give an unexpected result.

I put it as serious bug because there's not a lightweight way to avoid the problem.

I discover it into my PHP code, and I reproduce it on my mysql client.

No differences also switching from MyISAM to InnoDB.

A space at the BEGINNING of the string give the expected result (no bug).

How to repeat:
Using the mysql client shell:

create table test (id integer primary key auto_increment, `text` varchar(255));
create table test2 (id integer primary key auto_increment, `text` varchar(255)) ENGINE=InnoDB;

insert into test (`text`) values ('a string');
insert into test (`text`) values ('a blank term string ');

insert into test2 (`text`) values ('a string');
insert into test2 (`text`) values ('a blank term string ');

THE BUG:

select concat(`text`, '*'), id from test where `text`='a string ';
+---------------------+----+
| concat(`text`, '*') | id |
+---------------------+----+
| a string*           |  1 | 
+---------------------+----+

select concat(`text`, '*'), id from test where `text`='a blank term string';
+-----------------------+----+
| concat(`text`, '*')   | id |
+-----------------------+----+
| a blank term string * |  2 | 
+-----------------------+----+

The InnoDB table give same results:

select concat(`text`, '*'), id from test2 where `text`='a string  ';
+---------------------+----+
| concat(`text`, '*') | id |
+---------------------+----+
| a string*           |  1 | 
+---------------------+----+

select concat(`text`, '*'), id from test2 where `text`='a blank term string';
+-----------------------+----+
| concat(`text`, '*')   | id |
+-----------------------+----+
| a blank term string * |  2 | 
+-----------------------+----+

Suggested fix:
Maybe RTRIM is apply to the field value AND over the query value?

I think our manual, http://dev.mysql.com/doc/refman/5.0/en/char.html, explains this behaviour completely.

"VARCHAR values are not padded when they are stored. Handling of trailing spaces is version-dependent. As of MySQL 5.0.3, trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL." etc

So, you see space as it was stored. Also the following apply:

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

Please, check.

I see, yes.

I add a (dirty) workaround into my PHP code (I'm writing an import class and I may not trim the string BEFORE adding it to DB w/o making a conflict!).

BTW may be cool that DB admins will CHOICE what kind of character have to fill the varchar fields (I think to a directive into my.cnf file, for example). Using a \0 chr I may avoid the unexpected result. Don't you think?