MySQL Bugs: #14677: replace() is case sensitive

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

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

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>

What behaviour, exactly, is wrong here ?

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.

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.

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.

Agree.

(and personally, I would prefer REPLACE to use collation rules as every other function)

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!

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