Bug #14677 replace() is case sensitive
Submitted: 6 Nov 2005 0:02 Modified: 22 Nov 2005 16:42
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.0.15-nt-max-log/4.0/4.1/5.0 BK OS:Windows (Windows 2000/Linux)
Assigned to: Alexander Barkov CPU Architecture:Any

[6 Nov 2005 0:02] Dave Pullin
Description:
10.4.1 of the documentation says:

(Also note: The REPLACE() function, unlike all other functions, ignores the collation of the string input and performs a case-insensitive comparison every time.)

which is good but it isn't true! 
( Bug also shows in 4.1.9-standard-log/Linux)

console log:
mysql> SELECT version(),replace('www.mysql.com', 'my', 'xx') as lower
    -> ,replace('www.mysql.com', 'MY', 'xx') as upper
    -> ,if(replace('www.mysql.com', 'my', 'xx')=replace('www.mysql.com', 'MY', '
xx'),'no','yes') as is_bug;
+-------------------+---------------+---------------+--------+
| version()         | lower         | upper         | is_bug |
+-------------------+---------------+---------------+--------+
| 5.0.15-nt-max-log | www.xxsql.com | www.mysql.com | yes    |
+-------------------+---------------+---------------+--------+
1 row in set (0.00 sec)

mysql>

How to repeat:
SELECT version(),replace('www.mysql.com', 'my', 'xx') as lower
,replace('www.mysql.com', 'MY', 'xx') as upper
,if(replace('www.mysql.com', 'my', 'xx')=replace('www.mysql.com', 'MY', 'xx'),'no','yes') as is_bug
[6 Nov 2005 3:22] MySQL Verification Team
miguel@hegel:~/dbs/5.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT version(),replace('www.mysql.com', 'my', 'xx') as lower
    -> ,replace('www.mysql.com', 'MY', 'xx') as upper
    -> ,if(replace('www.mysql.com', 'my', 'xx')=replace('www.mysql.com', 'MY',
    -> 'xx'),'no','yes') as is_bug;
+--------------+---------------+---------------+--------+
| version()    | lower         | upper         | is_bug |
+--------------+---------------+---------------+--------+
| 5.0.16-debug | www.xxsql.com | www.mysql.com | yes    |
+--------------+---------------+---------------+--------+
1 row in set (0.03 sec)
miguel@hegel:~/dbs/4.1> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.16-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT version(),replace('www.mysql.com', 'my', 'xx') as lower
    -> ,replace('www.mysql.com', 'MY', 'xx') as upper
    -> ,if(replace('www.mysql.com', 'my', 'xx')=replace('www.mysql.com', 'MY',
    -> 'xx'),'no','yes') as is_bug;
+------------------+---------------+---------------+--------+
| version()        | lower         | upper         | is_bug |
+------------------+---------------+---------------+--------+
| 4.1.16-debug-log | www.xxsql.com | www.mysql.com | yes    |
+------------------+---------------+---------------+--------+
1 row in set (0.03 sec)
miguel@hegel:~/dbs/4.0> bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.0.26-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT version(),replace('www.mysql.com', 'my', 'xx') as lower
    -> ,replace('www.mysql.com', 'MY', 'xx') as upper
    -> ,if(replace('www.mysql.com', 'my', 'xx')=replace('www.mysql.com', 'MY',
    -> 'xx'),'no','yes') as is_bug;
+------------------+---------------+---------------+--------+
| version()        | lower         | upper         | is_bug |
+------------------+---------------+---------------+--------+
| 4.0.26-debug-log | www.xxsql.com | www.mysql.com | yes    |
+------------------+---------------+---------------+--------+
1 row in set (0.02 sec)

mysql>
[7 Nov 2005 11:11] Sergei Golubchik
What behaviour, exactly, is wrong here ?
[7 Nov 2005 13:00] Dave Pullin
replace('www.mysql.com', 'my','xx') 
    is not equal to 
replace('www.mysql.com', 'MY','xx')

In the first line, 'my' matchs the 'mysql' and is replaced by xx

in the second, ' 'MY' does not match 'mysql' and is not replaced.

Therefore the match is performed case-sensitively.
But the documentation says the replace() always performs a case INsensitive match.
[7 Nov 2005 13:31] Sergei Golubchik
Ah, indeed. You're right.
Somehow I always knew that REPLACE works case-*sensitively*, and that's how I read the quote from the manual... Eyes see what they expect to see...
Apparently, it's a typo in the manual, we'll fix that.
[7 Nov 2005 13:41] Dave Pullin
I suggest you put a big warning in the REPLACE() function definitions (12.3) (rather than just fix 10.4) since it is unusual for MySQL string functions to be case sensitive.

You might want to consider an optional argument to make REPLACE()  case insensitive because there's no easy way to "fake" case insensitive replacement.
[7 Nov 2005 14:04] Sergei Golubchik
Agree.

(and personally, I would prefer REPLACE to use collation rules as every other function)
[22 Nov 2005 8:03] Alexander Barkov
REPLACE is case sensitive on Monty's request.

Dear doc team, The complainer says that REPLACE
is case insensitive according to our manual. If it's
true, please fix. Thanks!
[22 Nov 2005 16:42] Paul DuBois
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
product(s).