Bug #42404 substring_index results inconsistent
Submitted: 28 Jan 2009 9:08 Modified: 27 Mar 2012 17:31
Reporter: Priyanka Sangam Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.1.25, 4.1, 5.0, 5.1, 6.0 bzr OS:Linux
Assigned to:
Triage: Triaged: D2 (Serious)

[28 Jan 2009 9:08] Priyanka Sangam
Description:
The results of SUBSTRING_INDEX depend upon the contents of the previous row when 
- the string begins with the delimiter
- the no of occurrences of the delimiter in the string is less than the count
- the count parameter is negative

mysql> create table t(i int not null, c char(255) not null)engine=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t values(0, '.www.mysql.com'),(1, '.wwwmysqlcom');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select i, substring_index(c, '.', -2) from t where i = 1;
+---+-----------------------------+
| i | substring_index(c, '.', -2) |
+---+-----------------------------+
| 1 |                             |
+---+-----------------------------+
1 row in set (0.01 sec)

mysql> select i, substring_index(c, '.', -2) from t;
+---+-----------------------------+
| i | substring_index(c, '.', -2) |
+---+-----------------------------+
| 0 | mysql.com                   |
| 1 | ysqlcom                     |
+---+-----------------------------+
2 rows in set (0.00 sec)

Here the second result value should be an empty string but it contains garbage, presumably left over from the previous row.

How to repeat:
use test;
drop table if exists t;
create table t(i int not null, c char(255) not null)engine=myisam;
insert into t values(0, '.www.mysql.com'),(1, '.wwwmysqlcom');
select i, substring_index(c, '.', -2) from t where i = 1;
select i, substring_index(c, '.', -2) from t;
[28 Jan 2009 10:52] Sveta Smirnova
Thank you for the report.

Verified as described.
[27 Mar 2012 17:31] Paul Dubois
Noted in 5.6.5 changelog.

Under some circumstances, the result of SUBSTRING_INDEX() incorrectly
depended on the contents of the previous row.