Bug #536 Select returns empty set if when value has a trailing space

• Submitted: 29 May 2003 9:03
• Modified: 29 May 2003 12:03
• Reporter: Harry Clauson
• Status: Not a Bug
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 4.0.12
• OS: Other (SCO OSR5.0.6)
• CPU Architecture: Any

[29 May 2003 9:03] Harry Clauson

Description:
Select returns the empty set when the "where" value contains a trailing space.  This is a myisam table and the value is char(4) and padded with spaces internally anyway (e.g. "ABC ").  But "select my_field from my_table where my_field='ABC '" returns the empty set while "select my_field from my_table where my_field='ABC'" works as expected.  My api always inserts the trailing space(s) in the query and this returns the empty set rather than the matching rows.  This does not involve any indexing.

How to repeat:
create table my_table (
  my_field char(4) binary not null;
);
insert into my_table values ('ABC ');
select * from my_table where my_field='ABC ';

(empty set returned)

select * from my_table where my_field='ABC';

(correct row returned)

Suggested fix:
I believe both select statements are logically identical and should return the same result set.

[29 May 2003 12:03] Alexander Keremidarski

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

This is how CHAR and VARCHAR are imnplemented in MySQL. Trailing spaces are always stripped when strings are inserted.

This differs from ANSI Standard and in future version will be changed.

As you say internally CHAR column values are padded with spaces. But when retrieving them there must be unfied rule how to extract them. Obviuosly there is no way to detetmine how many traling spaces were entered that's why MySQL always strips them upon extracting values i.e. it is assumed that trailing spaces are not significant. 

This is well docummented behaviour therefore not a bug

http://www.mysql.com/doc/en/CHAR.html
6.2.3.1 The CHAR and VARCHAR Types

...

When CHAR values are stored, they are right-padded with spaces to the specified length. When CHAR values are retrieved, trailing spaces are removed.