Last week, a customer reported a problem with DC-X – some linked metadata seemed broken. It turned out that slightly buggy custom code had written DCX_PUBINFO.PUB_DOC_ID = 'doc123 ' (note the trailing space) into the MySQL database, while the referenced column DCX_DOCUMENT.DOC_ID contained 'doc123' (without the space).

This came as a surprise to us: We didn’t expect InnoDB’s referential integrity to allow different values in a foreign key relation. But experiments showed that MySQL in fact ignores appended spaces (rtrim) when comparing values with “=”!

Here’s a test case if you’d like to reproduce it:

create table T (V varchar(255) not null); insert into T (V) values ('a'); select * from T where V = 'a ';

On MySQL, the SELECT statement returns the row we just inserted. On Oracle, it doesn’t – which seems to make a lot more sense.

The first Stack Overflow post I found, MySQL disable Auto-Trim, suggested that this was somehow acceptable, SQL-standardized behaviour. Weird. The SQL 92 standard seems to recommend MySQL’s padding / trimming (PADSPACE) and describes a NO PAD opt-out (that MySQL doesn’t offer).

Another post, MySQL treatment of ' ', was more informative – apparently LIKE behaves differently:

select * from T where V like 'a ';

And MySQL has a “binary” workaround for SELECT with “=”:

select * from T where binary V = 'a ';

For the full background, and a comparison of different RDBMS, read the PostgreSQL discussion String comparison and the SQL standard. According to this, MySQL and SQL Server always ignore appended spaces as described above. Oracle and PostgreSQL, on the other hand, do what we’d expect the database to do and don’t ignore them – as long as you use VARCHAR not CHAR.

We’re learning something new every day…