| 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: | |
| 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 |
[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)
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 */;