RLIKE seems to be partially ignoring collation sequence

[Thomas Capote]

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 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).

What gives? Have I been up too long or is this a bug? How can RLIKE obey a collation sequence *partially*?

Any help is genuinely appreciated.
--Thomas C.



Edited 1 time(s). Last edit at 08/24/2007 05:51PM by Thomas Capote.