Bug #25911 Matching strings ending with spaces fails
Submitted: 29 Jan 2007 10:50 Modified: 29 Jan 2007 15:13
Reporter: Sander Bouwhuis Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Data Types Severity:S1 (Critical)
Version:v5.0.27 OS:Windows (Windows XP SP2)
Assigned to: CPU Architecture:Any

[29 Jan 2007 10:50] Sander Bouwhuis
Description:
If found a very serious bug when matching strings which end with one or more spaces fails.

How to repeat:
MyId MyVarcharCol
1    'Test'
2    'Test '
3    'Test  '

-----

SELECT
  *
FROM
  MyTable
WHERE
  MyVarcharCol = 'Test'

This returns all 3 rows instead of only where MyId == 1

-----

SELECT
  *
FROM
  MyTable
WHERE
  MyVarcharCol = 'Test '

This returns all 3 rows instead of only where MyId == 2

-----

SELECT
  *
FROM
  MyTable
WHERE
  MyVarcharCol = 'Test  '

This returns all 3 rows instead of only where MyId == 3

-----

SELECT
  *
FROM
  MyTable
WHERE
  MyVarcharCol = 'Test          '

This returns all 3 rows instead of no rows
[29 Jan 2007 11:09] Sander Bouwhuis
Also:

MyId MyVarcharCol
1    'Test'
2    'Test '
3    'Test  '

SELECT
  DISTINCT MyVarcharCol
FROM
  MyTable

This returns:
MyVarcharCol
'Test'

It should have returned:
MyVarcharCol
'Test'
'Test '
'Test  '

-----

MyId MyVarcharCol
2    'Test '
3    'Test  '

SELECT
  DISTINCT MyVarcharCol
FROM
  MyTable

This returns:
MyVarcharCol
'Test'

It should have returned:
MyVarcharCol
'Test '
'Test  '

Note that it returns a value that is not even in the database!
[29 Jan 2007 11:12] Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

Please read carefully how MySQL handles trailing spaces in CHAR and VARCHAR columns at http://dev.mysql.com/doc/refman/5.0/en/char.html
[29 Jan 2007 15:07] Sander Bouwhuis
I have read the article in the documentation and am quite upset by it. It is, in the least, a grave inconsistency / undefined behaviour.

In what cases are the trailing spaces relevant, and in what cases aren't they? It is very inconsistent that for instance a 'SELECT' contains trailing spaces, while a '=' comparison does not!
Also, why are trailing spaces ignored?!? Spaces are valid ANSI and UNICODE characters.
[29 Jan 2007 15:13] Sander Bouwhuis
Is there a server variable I can set so trailing spaces are never removed/ignored?
Conversely, is there a server variable I can set so trailing spaces are always removed/ignored?