MySQL Bugs: #64772: Trailing white spaces are ignored in where clause

Bug #64772	Trailing white spaces are ignored in where clause
Submitted:	27 Mar 2012 6:11	Modified:	27 Mar 2012 6:23
Reporter:	Srinivasa Vasu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	MySQL Server 5.0	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Trailing white spaces are ignored in where clause comparison when using EQUALS.

How to repeat:
select 'x' = 'x'; -- 1 
select 'x' like 'x'; -- 1

// ignores trailing white spaces. 'x' is certainly different from 'x  '.
select 'x' = 'x    '; -- 1 
select 'x' like 'x    '; -- 0

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

"Values in CHAR and VARCHAR columns are sorted and compared according to the character set collation assigned to the column.

All MySQL collations are of type PADSPACE. This means that all CHAR and VARCHAR values in MySQL are compared without regard to any trailing spaces."

...

and http://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html#operator_like:

"Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator:"

and then:

"In particular, trailing spaces are significant, which is not true for CHAR or VARCHAR comparisons performed with the = operator:

mysql> SELECT 'a' = 'a ', 'a' LIKE 'a ';
+------------+---------------+
| 'a' = 'a ' | 'a' LIKE 'a ' |
+------------+---------------+
|          1 |             0 |
+------------+---------------+
1 row in set (0.00 sec)"

This is NOT a bug but intended and properly documented behavior.

LOL! This is a properly documented bug.

Some behaviours in MySQL are special but this behaviour is good for nothing.

Quite unexpected, hidden behaviour. I'd call it even mean :)

This needs to be reopened.  It's a clear bug.  Calling it a design decision is non-sense.  varchar can not behave like that.

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=81200bf6fd04b8cfb9f546bb57069b47

varchar values with different length can not under any circumstance compare equal.

Sorry.  I'll take back my comments.  But I think I'll leave them in case someone would like to see the mess I made. :)

From the SQL Standard:

"A <standard character set name> specifies ... The default collation of the character set is defined by the order of the characters in the standard and has the PAD SPACE characteristic."