From: Matt Gordon
Sent: Friday, September 14, 2007 6:37 PM
To: Daevid Vincent
Subject: Trailing space thing to post to mysql list
I searched Google and didn't find anything obvious.
Here is minimal SQL to reproduce the problem.
# Running mysql 5.0.41
CREATE TABLE test (
id int(10) unsigned NOT NULL auto_increment,
name varchar(255) default NULL,
PRIMARY KEY (id)
) CHARSET=utf8 ENGINE=MyISAM;
# Note the trailing spaces in the name.
INSERT INTO test( id, name ) VALUES( 1, 'TEST ' );
# Note there are no trailing spaces in the name but this query still returns
SELECT * FROM test where name = 'TEST';
# Note the leading spaces in the name.
INSERT INTO test( id, name ) VALUES( 2, ' FOO' );
# Note there are no leading spaces in the name and this query fails.
SELECT * FROM test where name = 'FOO';
Also, related to this, we could use LIKE instead of = and that had almost
the opposite results as we expected, not even using a % sign ?!!?