Bug #14676 substring_index yields different results on same arguments
Submitted: 5 Nov 2005 23:41 Modified: 28 Feb 2006 1:00
Reporter: Dave Pullin (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.16-BK, 5.0.15-nt-max-log OS:Linux (Linux, Windows)
Assigned to: Jim Winstead CPU Architecture:Any

[5 Nov 2005 23:41] Dave Pullin
Description:
substring_index yields different results on same arguments when used on consecutive rows in a query.

substring_index(lcase(txt), @q:=',',-1)

is applied to the same value of txt in consecutive rows yields different results.
The first one is correct. The second one yields 
   (a) using the MySQL console:  the empty string
   (b) using the JDBC client: a truncation on the left of the correct result.("c" in the example below).

 Eliminating either the lcase() or the user variable assignment ("q:=") removes the bug.

 Bug also shows in 4.1.9-standard-log/Linux (with JDBC client)
   and 4.1.10-nt/Windows2000 (with JDBC client)

Console log:

mysql> select version();
+-------------------+
| version()         |
+-------------------+
| 5.0.15-nt-max-log |
+-------------------+
1 row in set (0.00 sec)

mysql> drop table if exists temp;
Query OK, 0 rows affected (0.00 sec)

mysql> create table temp select  'y,abc' as txt
    ->  union all
    ->  select 'y,abc' as txt
    -> ;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select txt,substring_index(lcase(txt), @q:=',',-1) as result
    -> ,if(substring_index(lcase(txt), @q:=',',-1)!='abc','yes','no') as is_bug
 from temp;
+-------+--------+--------+
| txt   | result | is_bug |
+-------+--------+--------+
| y,abc | abc    | no     |
| y,abc |        | yes    |
+-------+--------+--------+
2 rows in set (0.00 sec)

mysql>
mysql> select txt,substring_index(txt, @q:=',',-1) as result
    -> ,if(substring_index(txt, @q:=',',-1)!='abc','yes','no') as is_bug from te
mp
    -> /* removing lcase() eliminates problem */;
+-------+--------+--------+
| txt   | result | is_bug |
+-------+--------+--------+
| y,abc | abc    | no     |
| y,abc | abc    | no     |
+-------+--------+--------+
2 rows in set (0.00 sec)

mysql> select txt,substring_index(lcase(txt), ',',-1) as result
    -> ,if(substring_index(lcase(txt), ',',-1)!='abc','yes','no') as is_bug from
 temp
    -> /* or removing q:= eliminates problem */;
+-------+--------+--------+
| txt   | result | is_bug |
+-------+--------+--------+
| y,abc | abc    | no     |
| y,abc | abc    | no     |
+-------+--------+--------+
2 rows in set (0.00 sec)

mysql>

How to repeat:
select version();
drop table if exists temp;
create table temp select  'y,abc' as txt
 union all
 select 'y,abc' as txt 
;
select txt,substring_index(lcase(txt), @q:=',',-1) as result
,if(substring_index(lcase(txt), @q:=',',-1)!='abc','yes','no') as is_bug  from temp;

select txt,substring_index(txt, @q:=',',-1) as result
,if(substring_index(txt, @q:=',',-1)!='abc','yes','no') as is_bug from temp
/* removing lcase() eliminates problem */;
select txt,substring_index(lcase(txt), ',',-1) as result
,if(substring_index(lcase(txt), ',',-1)!='abc','yes','no') as is_bug from temp
/* or removing q:= eliminates problem */;
[6 Nov 2005 8:24] Valeriy Kravchuk
Thank you for a bug report. Verified just as described on XP with 5.0.15-nt and on Linux with 5.0.16-BK build (ChangeSet@1.1957.1.18, 2005-11-03 20:29:21+02:00, jani@ua141d10.elisa.omakaista.fi):

mysql> select version();
+-----------+
| version() |
+-----------+
| 5.0.16    |
+-----------+
1 row in set (0,00 sec)

mysql> drop table if exists temp;
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> create table temp select  'y,abc' as txt
    ->   union all
    ->   select 'y,abc' as txt
    -> ;
Query OK, 2 rows affected (0,01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select txt,substring_index(lcase(txt), @q:=',',-1) as result
    -> ,if(substring_index(lcase(txt), @q:=',',-1)!='abc','yes','no') as is_bug
 from
    -> temp;
+-------+--------+--------+
| txt   | result | is_bug |
+-------+--------+--------+
| y,abc | abc    | no     |
| y,abc |        | yes    |
+-------+--------+--------+
2 rows in set (0,01 sec)

mysql> select txt,substring_index(txt, @q:=',',-1) as result
    -> ,if(substring_index(txt, @q:=',',-1)!='abc','yes','no') as is_bug from temp;
+-------+--------+--------+
| txt   | result | is_bug |
+-------+--------+--------+
| y,abc | abc    | no     |
| y,abc | abc    | no     |
+-------+--------+--------+
2 rows in set (0,01 sec)

mysql> select txt,substring_index(lcase(txt), ',',-1) as result
    -> ,if(substring_index(lcase(txt), ',',-1)!='abc','yes','no') as is_bug from
 temp;
+-------+--------+--------+
| txt   | result | is_bug |
+-------+--------+--------+
| y,abc | abc    | no     |
| y,abc | abc    | no     |
+-------+--------+--------+
2 rows in set (0,00 sec)
[9 Nov 2005 1:14] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32093
[24 Nov 2005 0:44] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/internals/32627
[17 Feb 2006 22:45] Jim Winstead
Fixed in 5.0.19 and 5.1.8.
[28 Feb 2006 1:00] Paul DuBois
Noted in 5.0.19, 5.1.7 changelogs.

<literal>SUBSTRING_INDEX()</literal> could yield inconsistent
results when applied with the same arguments to consecutive
rows in a query. (Bug #14676)