What is up with = and trailing spaces?

[Daevid Vincent]

 

________________________________

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
the record.
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 ?!!?