Bug #22260 Unexpected behaviour when concatenating a TEXT variable in a loop

• Submitted: 12 Sep 2006 10:33
• Modified: 12 Sep 2006 12:03
• Reporter: Martin Blenk
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.24a
• OS: Windows (Windows)
• CPU Architecture: Any
• Tags: concat, loop, routine, stored, text

[12 Sep 2006 10:33] Martin Blenk

Description:
When continuously concatenating a TEXT variable in a loop within a stored function, the string from the first iteration somehow gets overwritten by the second.

This does not happen, when the return value is a VARCHAR (line (A), referring to the comments in the function code). It does not happen, when using the iterator variable i directly in lines C and D, instead of assignig it to t (line B).

And it does not happen with MySQL 5.0.16. I have not tested the versions between 5.0.16 and 5.0.24a.

This seems to be similar to Bug#17226, altough at http://bugs.mysql.com/bug.php?id=17226 is says, that this would be fixed in 5.0.24...

How to repeat:
1. Create this Function:

CREATE FUNCTION `TEST`() RETURNS TEXT 
  LANGUAGE SQL 
  DETERMINISTIC 
  CONTAINS SQL 
  SQL SECURITY DEFINER 

BEGIN 
  
  DECLARE r TEXT        DEFAULT NULL; -- (A)
  DECLARE i SMALLINT    DEFAULT 0; 
  DECLARE t VARCHAR(2)  DEFAULT ''; 
  
  WHILE (i < 5) DO 
    
    SET t = i; -- (B)
    
    IF (i = 0) THEN 
      SET r = t; -- (C)
    ELSE 
      SET r = CONCAT(r, '-', t); -- (D)
    END IF; 
    
    SET i = i + 1; 
    
  END WHILE; 
  
  RETURN r; 
  
END 

2. SELECT TEST() produces: "1-1-2-3-4" instead of the expected "0-1-2-3-4"

[12 Sep 2006 12:03] Valeriy Kravchuk

Thank you for a problem report. Sorry, but I can not repeat the behaviour described with latest 5.0.26-BK on Linux:

mysql> delimiter ||
mysql> CREATE FUNCTION `TEST`() RETURNS TEXT
    ->   LANGUAGE SQL
    ->   DETERMINISTIC
    ->   CONTAINS SQL
    ->   SQL SECURITY DEFINER
    ->
    -> BEGIN
    ->
    ->   DECLARE r TEXT        DEFAULT NULL; -- (A)
    ->   DECLARE i SMALLINT    DEFAULT 0;
    ->   DECLARE t VARCHAR(2)  DEFAULT '';
    ->
    ->   WHILE (i < 5) DO
    ->
    ->     SET t = i; -- (B)
    ->
    ->     IF (i = 0) THEN
    ->       SET r = t; -- (C)
    ->     ELSE
    ->       SET r = CONCAT(r, '-', t); -- (D)
    ->     END IF;
    ->
    ->     SET i = i + 1;
    ->
    ->   END WHILE;
    ->
    ->   RETURN r;
    ->
    -> END ||
Query OK, 0 rows affected (0.02 sec)

mysql> select `TEST`()||
+-----------+
| `TEST`()  |
+-----------+
| 0-1-2-3-4 |
+-----------+
1 row in set (0.04 sec)

mysql> select version()||
+--------------+
| version()    |
+--------------+
| 5.0.26-debug |
+--------------+
1 row in set (0.00 sec)

So, check with current sources or just wait for 5.0.25 to be released officially and check with it.

[12 Sep 2006 12:12] Hartmut Holzgraefe

I can reproduce it with 5.0.24 release sources but not with current 5.0 source,
looks like one of those fixes shifted from 5.0.24 to .25 due to .24 begin a pure security fix release.