Last week, a customer reported a problem with DC-X \u2013 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).<\/p>\n
This came as a surprise to us: We didn\u2019t expect InnoDB\u2019s 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 \u201c=\u201d!<\/p>\n
Here\u2019s a test case if you\u2019d like to reproduce it:<\/p>\n
create table T (V varchar(255) not null); insert into T (V) values (‚a‘); select * from T where V = ‚a ‚;<\/p>\n
On MySQL, the SELECT statement returns the row we just inserted. On Oracle, it doesn\u2019t \u2013 which seems to make a lot more sense.<\/p>\n
The first Stack Overflow post I found, MySQL disable Auto-Trim<\/a>, suggested that this was somehow acceptable, SQL-standardized behaviour. Weird. The SQL 92 standard seems to recommend MySQL\u2019s padding \/ trimming (PADSPACE) and describes a NO PAD opt-out (that MySQL doesn\u2019t offer).<\/p>\n