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