Bug #16464 Variables of type text in stored procedures act as pointers

• Submitted: 12 Jan 2006 18:50
• Modified: 8 Sep 2006 8:58
• Reporter: Dmitry Apresian
• Status: Closed
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.0.18/5.0.19 BK
• OS: Linux (Fedora Core 4)
• Assigned to: Georgi Kodinov
• CPU Architecture: Any

[12 Jan 2006 18:50] Dmitry Apresian

Description:
If I declare two variables of type text in a store procedure and issue an assignment statement, then change the second one, first changes as well. Doesn't happen if the type is varchar(n).

How to repeat:
delimiter $

drop procedure if exists VerifyVarChar$
drop procedure if exists VerifyText$

create procedure VerifyVarChar()
begin
  declare str1, str2 varchar(16);

  set str1 = 'Second string';
  set str2 = str1;
  set str1 = 'First string';

  select str1, str2;
end
$

create procedure VerifyText()
begin
  declare str1, str2 text;

  set str1 = 'Second string';
  set str2 = str1;
  set str1 = 'First string';

  select str1, str2;
end
$

delimiter ;

mysql> call VerifyVarChar();
+--------------+---------------+
| str1         | str2          |
+--------------+---------------+
| First string | Second string |
+--------------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call VerifyText();   
+--------------+---------------+
| str1         | str2          |
+--------------+---------------+
| First string | First string  |
+--------------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

I can work around by replacing set str2 = str1; with set str2 = substring(str1, 1); or using varchar(n) instead of text in my particular case.

Suggested fix:
It is working in 5.0.15 (don't have other versions between 5.0.15 and 5.0.18 to try).

[12 Jan 2006 19:27] MySQL Verification Team

Thank you for the bug report. I was able to repeat:

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create db8
miguel@hegel:~/dbs/5.0> bin/mysql -uroot db8
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.19-debug

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

mysql> delimiter $
mysql> 
mysql> drop procedure if exists VerifyVarChar$
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> drop procedure if exists VerifyText$
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> 
mysql> create procedure VerifyVarChar()
    -> begin
    ->   declare str1, str2 varchar(16);
    -> 
    ->   set str1 = 'Second string';
    ->   set str2 = str1;
    ->   set str1 = 'First string';
    -> 
    ->   select str1, str2;
    -> end
    -> $
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> create procedure VerifyText()
    -> begin
    ->   declare str1, str2 text;
    -> 
    ->   set str1 = 'Second string';
    ->   set str2 = str1;
    ->   set str1 = 'First string';
    -> 
    ->   select str1, str2;
    -> end
    -> $
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> delimiter ;
mysql> call VerifyVarChar();
+--------------+---------------+
| str1         | str2          |
+--------------+---------------+
| First string | Second string |
+--------------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call VerifyText();
+--------------+---------------+
| str1         | str2          |
+--------------+---------------+
| First string | First string  |
+--------------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

[8 Sep 2006 8:58] Georgi Kodinov

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Additional info: I've checked your test case using version 5.0.25.
Here's what I get with it:
+ create procedure VerifyVarChar()
+ begin
+ declare str1, str2 varchar(16);
+ set str1 = 'Second string';
+ set str2 = str1;
+ set str1 = 'First string';
+ select str1, str2;
+ end
+ $
+ create procedure VerifyText()
+ begin
+ declare str1, str2 text;
+ set str1 = 'Second string';
+ set str2 = str1;
+ set str1 = 'First string';
+ select str1, str2;
+ end
+ $
+ call VerifyVarChar();
+ str1  str2
+ First string  Second string
+ call VerifyText();
+ str1  str2
+ First string  Second string