Bug #14900 substrings in SP return NULL values when assigned to self

• Submitted: 12 Nov 2005 14:38
• Modified: 12 Nov 2005 16:29
• Reporter: Giuseppe Maxia
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.15
• CPU Architecture: Any

[12 Nov 2005 14:38] Giuseppe Maxia

Description:
Within a stored routine, assigning to a variable a substring of itself returns a NULL value.

This bug was already reported as "non critical"  (bug #13613).
However, it was 40 days ago, and in the meantime MySQL 5 has become GA.
This bug is serious, because a feature reported in the manual fails to deliver its correct result, and will lead SP developers to hours of fruitless attempts.

How to repeat:
delimiter //
drop function if exists testsp //
create function testsp ( q varchar(50) )
returns varchar(50)
deterministic
begin
    declare final_q varchar(50);
    declare q1 varchar(50);
    set q1 = q ;
    if ( q like '%SENTINEL%') then
        set q1 = substring(q, 1, 20 );
    end if;
    set final_q = concat('<', q1, '>');
    return final_q;
end //

delimiter ;

select testsp('blah blah blah blah');
select testsp('blah blah blah blah SENTINEL xy ');

-- this is correct (outside stored procedures)
set @q = 'blah blah blah blah SENTINEL xy ';
select testsp(@q);

select substring(@q , 1, 20 );

Suggested fix:
Use a temporary variable to hold the result of the substring.

[12 Nov 2005 16:29] Valeriy Kravchuk

Thank you for a problem report. As you can see in bug #13613, developers are working on this issue already. I've just tested on newer 5.0.17-BK build (ChangeSet@1.1967, 2005-11-11 13:39:46+04:00,...) on Linux and got the following results:

mysql> delimiter //
mysql> drop function if exists testsp //
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> create function testsp ( q varchar(50) )
    -> returns varchar(50)
    -> deterministic
    -> begin
    ->     declare final_q varchar(50);
    ->     declare q1 varchar(50);
    ->     set q1 = q ;
    ->     if ( q like '%SENTINEL%') then
    ->         set q1 = substring(q, 1, 20 );
    ->     end if;
    ->     set final_q = concat('<', q1, '>');
    ->     return final_q;
    -> end //
Query OK, 0 rows affected (0,03 sec)

mysql> delimiter ;
mysql> select testsp('blah blah blah blah');
+-------------------------------+
| testsp('blah blah blah blah') |
+-------------------------------+
| <blah blah blah blah>         |
+-------------------------------+
1 row in set (0,00 sec)

mysql> select testsp('blah blah blah blah SENTINEL xy');
+-------------------------------------------+
| testsp('blah blah blah blah SENTINEL xy') |
+-------------------------------------------+
| <blah blah blah blah >                    |
+-------------------------------------------+
1 row in set (0,00 sec)

mysql> set @q='blah blah blah blah SENTINEL xy';
Query OK, 0 rows affected (0,01 sec)

mysql> select substring(@q, 1, 20);
+----------------------+
| substring(@q, 1, 20) |
+----------------------+
| blah blah blah blah  |
+----------------------+
1 row in set (0,00 sec)

mysql> select testsp(@q);
+------------------------+
| testsp(@q)             |
+------------------------+
| <blah blah blah blah > |
+------------------------+
1 row in set (0,00 sec)

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

I do not know exactly when it was fixed and how, but, please, wait for 5.0.16 to be released soon and check. If the bug is still there, please, add you comment to the original bug report. Looks like it is already fixed in current sources.