MySQL Bugs: #62621: "=" and "LIKE" operators inconsistent with trailing spaces.

Bug #62621	"=" and "LIKE" operators inconsistent with trailing spaces.
Submitted:	4 Oct 2011 18:55	Modified:	4 Apr 2013 15:22
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	qc

Description:
I already posted http://bugs.mysql.com/?id=62620
A little experimentation reveals the inconsistence - see test case below.

(depending on what conclusions you reach you may mark the two reports as duplicates of course)

How to repeat:
DROP TABLES IF EXISTS `names`;
CREATE TABLE `names` (myname CHAR(10) COLLATE utf8_bin);
INSERT INTO `names` VALUES ('Monty');
SELECT myname = 'Monty ' COLLATE utf8_bin FROM NAMES; -- 1
SELECT myname = 'Monty ' COLLATE utf8_bin FROM NAMES; -- 1
SELECT myname LIKE 'Monty' COLLATE utf8_bin FROM NAMES; -- 1
SELECT myname LIKE 'Monty ' COLLATE utf8_bin FROM NAMES; -- 0 

Suggested fix:
Last result triggers an "EERRHH ??" from here.

Fixed typo in sýnopsis.

-- a binary collation is not required to reproduce this
DROP TABLES IF EXISTS `names`;
CREATE TABLE `names` (myname CHAR(10) COLLATE utf8_general_ci);
INSERT INTO `names` VALUES ('Monty');
SELECT myname = 'Monty' FROM NAMES; -- 1
SELECT myname = 'Monty ' FROM NAMES; -- 1
SELECT myname LIKE 'Monty' FROM NAMES; -- 1
SELECT myname LIKE 'Monty ' FROM NAMES; -- 0
-- 2nd and 4th results makes no sense to me

-- let us try a VARBINARY type - makes sense - it is truly *binary* comparison in all cases
DROP TABLES IF EXISTS `names`;
CREATE TABLE `names` (myname VARBINARY(10));
INSERT INTO `names` VALUES ('Monty');
SELECT myname = 'Monty' FROM NAMES; -- 1
SELECT myname = 'Monty ' FROM NAMES; -- 0
SELECT myname LIKE 'Monty' FROM NAMES; -- 1
SELECT myname LIKE 'Monty ' FROM NAMES; -- 0

(updated synopsis)

Manual (http://dev.mysql.com/doc/refman/5.5/en/string-comparison-functions.html#operator_like) explains:

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

mysql> SELECT 'ä' LIKE 'ae' COLLATE latin1_german2_ci;
+-----------------------------------------+
| 'ä' LIKE 'ae' COLLATE latin1_german2_ci |
+-----------------------------------------+
|                                       0 |
+-----------------------------------------+
mysql> SELECT 'ä' = 'ae' COLLATE latin1_german2_ci;
+--------------------------------------+
| 'ä' = 'ae' COLLATE latin1_german2_ci |
+--------------------------------------+
|                                    1 |
+--------------------------------------+

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

Doesn't it explain your findings?

Yes .. it explains.

I am changing to docs category.  Because this statement "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." needs to be elaborated and cross-references need to be added.  Anyone reading the quoted statement (and not aware of what you quote) would be surprised as LIKE is also a comparison IMHO.

And one question then: In http://bugs.mysql.com/bug.php?id=62616 you could have? Using LIKE would solve his problem as I understand. I know this is not a support system (it is a bug reporting system), but that does not exclude a little helpfullness when it is 'free'.

I don't mind adding more clarifications and cross references to our manual. Never :)

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

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.