Bug #13997 Incorrect result with SUBSTRING_INDEX

• Submitted: 13 Oct 2005 8:57
• Modified: 21 Nov 2005 19:53
• Reporter: Vadim Tkachenko
• Status: Can't repeat
• Category: MySQL Server: Stored Routines
• Severity: S2 (Serious)
• Version: 5.0.15-bk
• OS: Linux (Linux, Suse 9.3)
• Assigned to: Alexander Nozdrin
• CPU Architecture: Any

[13 Oct 2005 8:57] Vadim Tkachenko

Description:
SELECT SUBSTRING_INDEX(workstr,',',-4) INTO workstr;
inside SP returns incorrect result into workstr;

How to repeat:
1.sql:
connect test;
drop procedure if exists p3;
delimiter $
create procedure p3 (IN str VARCHAR(255), OUT stro VARCHAR(255))
BEGIN
 declare workstr VARCHAR(255);
 set workstr = str;
 SELECT SUBSTRING_INDEX(workstr,',',-4) INTO workstr;
 set stro= workstr;
END$

delimiter ;
call p3("11,12,13,14,15",@ss);
select @ss;

./mysql < 1.sql
@ss
\02,13,14,15

[13 Oct 2005 9:19] Hartmut Holzgraefe

i get complete nonsense back form it:

mysql> select @ss;
+-------------+
| @ss         |
+-------------+
| ����������� |
+-------------+
1 row in set (0.00 sec)

[21 Nov 2005 19:12] Alexander Nozdrin

mysql> delimiter |
mysql> 
mysql> CREATE PROCEDURE p1(IN in_arg VARCHAR(255), OUT out_arg VARCHAR(255))
    -> BEGIN
    ->  DECLARE var VARCHAR(255);
    -> 
    ->  SET var = in_arg;
    -> 
    ->  SELECT SUBSTRING_INDEX(var, ',', -4) INTO var;
    -> 
    ->  SET out_arg = var;
    -> END|
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> CREATE FUNCTION f1(arg VARCHAR(255)) RETURNS VARCHAR(255)
    -> BEGIN
    ->   DECLARE var VARCHAR(255);
    -> 
    ->   SELECT SUBSTRING_INDEX(arg, ',', -4) INTO var;
    -> 
    ->   RETURN var;
    -> END|
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> delimiter ;
mysql> 
mysql> CALL p1('11,12,13,14,15', @ss);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @ss;
+-------------+
| @ss         |
+-------------+
| 12,13,14,15 |
+-------------+
1 row in set (0.00 sec)

mysql> 
mysql> SELECT f1('11, 12, 13, 14, 15');
+--------------------------+
| f1('11, 12, 13, 14, 15') |
+--------------------------+
|  12, 13, 14, 15          |
+--------------------------+
1 row in set (0.00 sec)

[21 Nov 2005 19:13] Alexander Nozdrin

mysql> select version();
+-------------------------------+
| version()                     |
+-------------------------------+
| 5.0.17-valgrind-max-debug-log |
+-------------------------------+
1 row in set (0.00 sec)

[21 Nov 2005 19:53] Vadim Tkachenko

I cannot repeat it too in
mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.0.16-standard |
+-----------------+