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: | |
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
[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?