MySQL Bugs: #13613: substring function in stored procedure

Bug #13613	substring function in stored procedure
Submitted:	29 Sep 2005 17:17	Modified:	24 Nov 2005 14:21
Reporter:	Samir Seba	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.13	OS:	Windows (windows x86/Linux)
Assigned to:	Alexander Nozdrin	CPU Architecture:	Any

Description:
declare x varchar(50);
set x='abcdef';
x=substring(x,1,3);
select x;

the result is faulse. 
the value displayed is : empty.

you have to use and other variable to get the rigth result, like following :
declare x varchar(50);
declare y varchar(0)
set x='abcdef';
y=substring(x,1,3);
select y;

the result is: "abc".

but for the following test it's working fine :
declare x smallint;
set x=20;
x=x-4;
select x;

the result is 16

CREATE PROCEDURE test (x varchar(50) ) 
begin
	set x = substring(x,1,3);
	select x;
end;

How to repeat:
CREATE PROCEDURE test (x varchar(50) ) 
begin
	set x = substring(x,1,3);
	select x;
end;

miguel@hegel:~/dbs/5.0> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.14-rc-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> delimiter //
mysql> CREATE PROCEDURE test (x varchar(50) ) 
    -> begin
    -> set x = substring(x,1,3);
    -> select x;
    -> end//
Query OK, 0 rows affected (0.00 sec)

mysql> call test("abcdef")//
+-----+
| x   |
+-----+
|  |
+-----+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> set @myvar = "abcdef"//
Query OK, 0 rows affected (0.00 sec)

mysql> select substring(@myvar,1,3)//
+-----------------------+
| substring(@myvar,1,3) |
+-----------------------+
| abc                   |
+-----------------------+
1 row in set (0.00 sec)

mysql>

if you need more detail let me know

the issue is when you do a substring from a varibale and assign it to the same variable

the select substring(x,1,3) is working fine , but 
set x = substring(x,1,3) and i need that in my source. for the moment i used an other variable

i want to let know that i need the result of substring( x, 1,3) in the call of my stored procedure , i mean. my stored procedure could be like following:

create procedure test ( x varchar(50))
begin
        set x=substring(x,1,3);
        /* display the value of x */
        select x; 
        call second_proc(x);
end

the two tests you did are as following :
1- in console mode 
   mysql >set @x='abcde'
   mysql> select substring(@x,1,3)

in that mode it's working fine , i known that but my question was about stored procedure. i have the same thing in the sql server and i'm testing  to migrate stored procedure from mssql to mysql.

2- the way you did it in the stored procedure is not the way i want it ( as i explain it in the beginning of this comment.

mysql> delimiter |
mysql> 
mysql> CREATE PROCEDURE p1(x VARCHAR(50))
    -> BEGIN
    ->   SET x = SUBSTRING(x, 1, 3);
    ->   SELECT x;
    -> END|
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> delimiter ;
mysql> 
mysql> CALL p1('abcdef');
+-----+
| x   |
+-----+
| abc |
+-----+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

Alik, please add a test case for this bug to the test suite (sp.test), unless you're sure that this is covered already.

Added into sp-vars.test.