From: Daevid Vincent
Date: September 15 2007 2:00am
Subject: What is up with = and trailing spaces?
List-Archive: http://lists.mysql.com/mysql/209112
Message-Id: <BRAKAeHdzXT6hW1Aqxb0000083c@mx1.lockdownnetworks.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit

 

________________________________

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 ?!!?