MySQL Bugs: #73095: Select ... where unexpectedly returns a result when it shouldnt

Bug #73095	Select ... where unexpectedly returns a result when it shouldnt
Submitted:	24 Jun 2014 15:00	Modified:	25 Jun 2014 10:05
Reporter:	Emmanuel Merliot	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.5.37	OS:	Windows
Assigned to:		CPU Architecture:	Any

Description:
The following requests return the same result, but the test data is not the same:

SELECT * FROM mytable m WHERE m.MYCOLUMN='test space: ';

SELECT * FROM mytable m WHERE m.MYCOLUMN='test space:';

Note the difference between 'test space: ' and 'test space:': there is a space character at the end of the value.
The test is an equality, and not a LIKE thing. So it should be compared exactly.

Server Mysql community 5.5.37
Clients:
1.Mysql Workbench 5.2.38 CE Revision 8753
2.mysql.exe
reproduced in both clients and in trigger either.
The database is full-UTF-8 (this is the server default character set too), the concerned table has text column and the clause 'where' is on this text column.

How to repeat:
insert into mytable (ID,MYCOLUMN) values (21000,'test space: ');
--> the value in the table is actually 'test space: '
SELECT * FROM mytable m WHERE m.MYCOLUMN='test space: ';
SELECT * FROM mytable m WHERE m.MYCOLUMN='test space:';

Suggested fix:
Test binary equality between values.

Please, read the manual, http://dev.mysql.com/doc/refman/5.5/en/char.html:

"All MySQL collations are of type PADSPACE. This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces. “Comparison” in this context does not include the LIKE pattern-matching operator, for which trailing spaces are significant."

Please  see Valery prior comment. Thanks.

Hello Valeriy,

thank you for the quick answer.

In the document you pointed me to:
VARCHAR values are not padded when they are stored. Trailing spaces are retained when values are stored and retrieved, in conformance with standard SQL.
--> This is the behavior I expected actually.

On the other hand, in the same doc:
This means that all CHAR, VARCHAR, and TEXT values in MySQL are compared without regard to any trailing spaces

One can be confused by the apparent contradiction between these two sentences.

Trailing spaces can be understood as 'the characters the engine fill my string with, at the right'.
These are not retrieved when you select the value. This is OK.
These are not retrieved when you compare the value. This is OK.

In the documentation, it seems trailing spaces are also 'the characters which are at the end of my string value when I inserted it, for example 'test space '.
These are retrieved when you select the value. This is OK too.
These are not retrieved when you compare the value. This is not OK in my case.

Please could you consider explain more precisely the difference in the document?

The final work around is actually not to use '=' operator but 'like' operator when one want to compare two strings binary-equality.