MySQL Bugs: #30635: RLIKE is collating incorrectly

Bug #30635	RLIKE is collating incorrectly
Submitted:	25 Aug 2007 0:00	Modified:	5 Nov 2007 18:43
Reporter:	Thomas Capote	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1.18	OS:	Linux (RedHat)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
I am using version 4.1.18 of MySQL. Collations are set at EVERY LEVEL (server, database, table, column, connection) to latin1_swedish_ci. Why are the following two queries (using the mysql command line client) returning DIFFERENT result sets? 

mysql> SELECT fullname FROM name WHERE fullname LIKE '%josé%'; 
+-----------------------------+ 
| fullname | 
+-----------------------------+ 
| José López Anguiano | 
| Jose Ignacio Sarabia Ponce | 
| Joseph Lee Payne | 
| José Edgar Esquivel Mendoza | 
+-----------------------------+ 

mysql> SELECT fullname FROM name WHERE fullname RLIKE 'josé'; 
+-----------------------------+ 
| fullname | 
+-----------------------------+ 
| José López Anguiano | 
| José Edgar Esquivel Mendoza | 
+-----------------------------+ 

Notice that the LIKE operator is functioning as you would expect, treating 'e' and 'é' as equivalent (due to collation). It seems RLIKE is mis-behaving with respect to the equivalence of e/é, but it seems to be working with respect to j/J (case insensitivity). 

How to repeat:
Populate a VARCHAR column with two rows; one containing an accented character, the other not. Default collations to latin1_swedish_ci, latin1_spanish_ci or similar. Attempt LIKE and RLIKE queries similar to above...

Suggested fix:
Sorry. No idea.

Thank you for a problem report. Please, try to repeat with a newer version, 4.1.23, and inform about the results.

the regular expression library that we use knows very little of character sets. in particular, it compares "characters" by their byte values, and cannot handle accented characters correctly. The manual has a warning about it, but it's not strong enough.

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.