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;
