Bug #14670 substring_index is case sensitive
Submitted: 5 Nov 2005 15:40 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:Microsoft Windows (Windows 2000/Linux)
Assigned to: Alexander Barkov CPU Architecture:Any

[5 Nov 2005 15:40] Dave Pullin
Description:
SUBSTRING_INDEX(str,delim,count) is case sensitive in finding 'delim' within 'str'.

Since most or all other string comparisions within MySQL default to case insensitive comparisons this is odd (and not documented).

Console log:
mysql> SELECT version(),SUBSTRING_INDEX('www.mysql.com', 'my', 1) as lower,SUBSTRING_INDEX('www.mysql.com', 'MY', 1) as upper;
+-------------------+-------+---------------+
| version()         | lower | upper         |
+-------------------+-------+---------------+
| 5.0.15-nt-max-log | www.  | www.mysql.com |
+-------------------+-------+---------------+
1 row in set (0.00 sec)

How to repeat:
SELECT version(),SUBSTRING_INDEX('www.mysql.com', 'my', 1) as lower,SUBSTRING_INDEX('www.mysql.com', 'MY', 1) as upper

Suggested fix:
Make the comparison case insensitive.

Alternative but odd: docuement that in is case sensitive.
[5 Nov 2005 16:01] 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(),SUBSTRING_INDEX('www.mysql.com', 'my', 1) as
    -> lower,SUBSTRING_INDEX('www.mysql.com', 'MY', 1) as upper
    -> ;
+--------------+-------+---------------+
| version()    | lower | upper         |
+--------------+-------+---------------+
| 5.0.16-debug | www.  | www.mysql.com |
+--------------+-------+---------------+
1 row in set (0.08 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

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

mysql> SELECT version(),SUBSTRING_INDEX('www.mysql.com', 'my', 1) as
    -> lower,SUBSTRING_INDEX('www.mysql.com', 'MY', 1) as upper
    -> ;
+--------------+-------+---------------+
| version()    | lower | upper         |
+--------------+-------+---------------+
| 4.1.16-debug | www.  | www.mysql.com |
+--------------+-------+---------------+
1 row in set (0.01 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.27-debug-log

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

mysql> SELECT version(),SUBSTRING_INDEX('www.mysql.com', 'my', 1) as
    -> lower,SUBSTRING_INDEX('www.mysql.com', 'MY', 1) as upper
    -> ;
+------------------+-------+---------------+
| version()        | lower | upper         |
+------------------+-------+---------------+
| 4.0.27-debug-log | www.  | www.mysql.com |
+------------------+-------+---------------+
1 row in set (0.03 sec)
[22 Nov 2005 8:06] Alexander Barkov
SUBSTRING_INDEX was kept case-sensitive on Monty's request.

Dear doc team, The complainer says that our manual
doesn't mention this behaviour. If it's true, please add
a note.

Thanks!

P.S.
Perhaps it's a good idea to have a special article
about functions and case sensitivity. A kind of table
containing function names and their case sensitivity
attribute.
[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).