Bug #15084 re asigning value to variable in REPEAT STATEMENT

• Submitted: 20 Nov 2005 18:39
• Modified: 20 Nov 2005 19:26
• Reporter: Barak Mery
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: MySQL 5.0.13-rc-nt
• OS: Windows (windows xp)
• CPU Architecture: Any

[20 Nov 2005 18:39] Barak Mery

Description:
I had A very Weird problem with some SP.
I Found a workaroud for it.

I have a sp that manipulate strings. 
The sp Get some data in the format of Rows and Fields. 
For exsample : 
'111|222|333|444@555|666|777|888' 
The @ seperates Rows. 
The | seperates Fields. 

Each row always contain 4 fields. 
The number of row is not known. 

The sp Manipulate the string and rutrn each "field" Seperatly, 
using two REPEAT STATEMENTS.
1. for Seperating Rows
2. for Seperating Fields

When I call string_manipulation('111|222|333|444') 
Everythings work fine and the result is : 

111 
222 
333 
444 

The strange thing is that when i call string_manipulation('1|222|333|444') 
The result is : 
1 
o2 <--- Here is the problem. 
333 
444 

Instead of getting '222' i get some strange string in an un recognized charechter. 

I'm using mysql 5.0.13, innodb, windows xp

How to repeat:
DELIMITER $$ 

DROP PROCEDURE IF EXISTS `bcm`.`string_manipulation` $$ 
CREATE PROCEDURE `string_manipulation`(AllData VARCHAR(100)) 
BEGIN 

DECLARE RowPos INTEGER; 
DECLARE RowStr VARCHAR(100) DEFAULT ''; 
DECLARE FldPos INTEGER; 
DECLARE FldStr VARCHAR(100) DEFAULT ''; 
DECLARE ChckRowState INTEGER DEFAULT 1; 
DECLARE FldCount INTEGER; 

SET AllData = CONCAT(AllData,'@') ; 

REPEAT #rows Loop 

SET RowPos = INSTR(AllData,'@'); #finds the First Row End Position 

SET RowStr = LEFT(AllData,RowPos-1); #Put the first Row String into variable 

SET FldCount = 1; #Initialize the field counter . the loop will run 4 times 
#Ther are onlyqalways 4 fields 

SET RowStr = CONCAT(RowStr,'|'); 

#SELECT RowStr; 

REPEAT #fields Loop 

SET FldPos = INSTR(RowStr,'|'); #finds the First Field End Position 

SET FldStr = LEFT(RowStr,FldPos-1); #Put the first field String into variable 

SELECT FldStr; 

SET RowStr = RIGHT(RowStr,LENGTH(RowStr) - FldPos); # row string without the field already handled 

SET FldCount = FldCount + 1; 

UNTIL FldCount=5 END REPEAT; 

SET AllData = RIGHT(AllData,LENGTH(AllData)-RowPos); # Alldata string without the Row already handled 

IF INSTR(AllData,'@') > 0 THEN 

SET ChckRowState=1; 

ELSE 

SET ChckRowState=0; 

END IF; 

UNTIL ChckRowState=0 END REPEAT; 

END $$ 

DELIMITER ; 

call string_manipulation('1|222|333|444') 

Suggested fix:
I changed under the inside REPEAT: 

SET RowStr = RIGHT(RowStr,LENGTH(RowStr) - FldPos); 

to: 

SET TempStr = RowStr; 
SET RowStr = ''; 
SET RowStr = RIGHT(TempStr,LENGTH(TempStr) - FldPos); 

And its working i dont understand why but it does.

[20 Nov 2005 19:26] MySQL Verification Team

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

c:\mysql\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.16-nt

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

mysql> DELIMITER $$
mysql>
mysql> DROP PROCEDURE IF EXISTS `bcm`.`string_manipulation` $$
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE PROCEDURE `string_manipulation`(AllData VARCHAR(100))
    -> BEGIN
    ->
    -> DECLARE RowPos INTEGER;
    -> DECLARE RowStr VARCHAR(100) DEFAULT '';
    -> DECLARE FldPos INTEGER;
    -> DECLARE FldStr VARCHAR(100) DEFAULT '';
    -> DECLARE ChckRowState INTEGER DEFAULT 1;
    -> DECLARE FldCount INTEGER;
    ->
    -> SET AllData = CONCAT(AllData,'@') ;
    ->
    -> REPEAT
    ->
    -> SET RowPos = INSTR(AllData,'@');
    ->
    -> SET RowStr = LEFT(AllData,RowPos-1); #Put the first Row String into variable
    ->
    -> SET FldCount = 1;
    ->
    -> SET RowStr = CONCAT(RowStr,'|');
    ->
    -> REPEAT #fields Loop
    ->
    -> SET FldPos = INSTR(RowStr,'|');
    ->
    -> SET FldStr = LEFT(RowStr,FldPos-1);
    -> SELECT FldStr;
    ->
    -> SET RowStr = RIGHT(RowStr,LENGTH(RowStr) - FldPos);
    ->
    -> SET FldCount = FldCount + 1;
    ->
    -> UNTIL FldCount=5 END REPEAT;
    ->
    -> SET AllData = RIGHT(AllData,LENGTH(AllData)-RowPos);
    ->
    -> IF INSTR(AllData,'@') > 0 THEN
    ->
    -> SET ChckRowState=1;
    ->
    -> ELSE
    ->
    -> SET ChckRowState=0;
    ->
    -> END IF;
    ->
    -> UNTIL ChckRowState=0 END REPEAT;
    ->
    -> END $$
Query OK, 0 rows affected (0.01 sec)

mysql>
mysql> DELIMITER ;
mysql>
mysql> call string_manipulation('1|222|333|444')
    -> ;
+--------+
| FldStr |
+--------+
| 1      |
+--------+
1 row in set (0.03 sec)

+--------+
| FldStr |
+--------+
| 222    |
+--------+
1 row in set (0.03 sec)

+--------+
| FldStr |
+--------+
| 333    |
+--------+
1 row in set (0.03 sec)

+--------+
| FldStr |
+--------+
| 444    |
+--------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql>