Bug #27415 Text Variables in stored procedures
Submitted: 24 Mar 2007 5:58 Modified: 4 Oct 2007 20:35
Reporter: Igor Cherny Email Updates:
Status: Can't repeat Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.45 OS:Linux (Linux, Fedora 6)
Assigned to: Kristofer Pettersson CPU Architecture:Any
Tags: assingments, left side, regression, right side, stored procedures, text variables

[24 Mar 2007 5:58] Igor Cherny
Description:
In stored procedures when variable of type text is placed on both sides of the assignment - the garbage comes in instead of the proper value in a variable you are assigning a value to.

This is new in 5.0.37 and worked fine in 5.0.27.

Please see the example of a problem and work around below.

How to repeat:
Here is the skinnied-up stored proc that has an issue isolated:

DELIMITER $$

DROP PROCEDURE IF EXISTS text_test$$

CREATE PROCEDURE text_test(entity_id_str_in text)
BEGIN
    DECLARE str_remainder text;

    SET str_remainder = entity_id_str_in;

    select 'before substr', str_remainder;
    SET str_remainder = SUBSTRING(str_remainder, 3);
    select 'after substr', str_remainder;
END$$

DELIMITER ;

Then call it:

call text_test('a,b,c');

and check the value in str_reminder.

Suggested fix:
The work-around I had to come up with:

DELIMITER $$

DROP PROCEDURE IF EXISTS text_test2$$

CREATE PROCEDURE text_test2(entity_id_str_in text)
BEGIN
    DECLARE str_remainder text;
    DECLARE str_remainder2 text;
 
    SET str_remainder2 = entity_id_str_in;
    select 'before substr', str_remainder2;
    SET str_remainder = SUBSTRING(str_remainder2, 3);
    select 'after substr', str_remainder;
END$$

DELIMITER ;

Then call it to see that it works now: 

call text_test2('a,b,c');

Basically I just added another intermediate variable, so that the str_reminder wouldn't come up on the left side.

Again - this worked in 5.0.27 without any issues.
Also - if you have varchar variable instead of text - it also works fine.

Hope this helps!

Igor.
[24 Mar 2007 18:18] Valeriy Kravchuk
Thank you for a bug report. Verified just as described:

mysql> select version();

+-------------------------+
| version()               |
+-------------------------+
| 5.0.37-community-nt-log |
+-------------------------+
1 row in set (1.03 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE text_test(entity_id_str_in text)
    -> BEGIN
    ->     DECLARE str_remainder text;
    ->
    ->     SET str_remainder = entity_id_str_in;
    ->
    ->     select 'before substr', str_remainder;
    ->     SET str_remainder = SUBSTRING(str_remainder, 3);
    ->     select 'after substr', str_remainder;
    -> END//
Query OK, 0 rows affected (0.34 sec)

mysql> delimiter ;
mysql> call text_test('a,b,c');
+---------------+---------------+
| before substr | str_remainder |
+---------------+---------------+
| before substr | a,b,c         |
+---------------+---------------+
1 row in set (0.00 sec)

+--------------+---------------+
| after substr | str_remainder |
+--------------+---------------+
| after substr |   c           |
+--------------+---------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE text_test2(entity_id_str_in text)
    -> BEGIN
    ->     DECLARE str_remainder text;
    ->     DECLARE str_remainder2 text;
    ->
    ->     SET str_remainder2 = entity_id_str_in;
    ->     select 'before substr', str_remainder2;
    ->     SET str_remainder = SUBSTRING(str_remainder2, 3);
    ->     select 'after substr', str_remainder;
    -> END//
Query OK, 0 rows affected (0.14 sec)

mysql> call text_test2('a,b,c')//
+---------------+----------------+
| before substr | str_remainder2 |
+---------------+----------------+
| before substr | a,b,c          |
+---------------+----------------+
1 row in set (0.00 sec)

+--------------+---------------+
| after substr | str_remainder |
+--------------+---------------+
| after substr | b,c           |
+--------------+---------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.03 sec)
[16 May 2007 12:11] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26822

ChangeSet@1.2486, 2007-05-16 14:11:26+02:00, thek@adventure.(none) +2 -0
  Bug#27415 Text Variables in stored procedures
   - Problem was reported as a SP variable using itself as 
     right value inside SUBSTR caused corruption of data. 
   - This bug could not be verified in either 5.0bk or 5.1bk
   - Added test case to prevent future regressions.
[16 May 2007 12:25] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/26823

ChangeSet@1.2486, 2007-05-16 14:25:38+02:00, thek@adventure.(none) +2 -0
  Bug#27415 Text Variables in stored procedures
   - Problem was reported as a SP variable using itself as 
     right value inside SUBSTR caused corruption of data. 
   - This bug could not be verified in either 5.0bk or 5.1bk
   - Added test case to prevent future regressions.
[16 May 2007 12:26] Konstantin Osipov
Approved to push the test case into the test suite.
[18 May 2007 9:09] Bugs System
Pushed into 5.1.19-beta
[18 May 2007 9:11] Bugs System
Pushed into 5.0.44
[27 May 2007 16:17] Paul DuBois
Noted in 5.0.44, 5.1.19 changelogs.

Using a TEXT local variable in a stored routine in an expression
such as SET var = SUBSTRING(var, 3) produced an incorrect result.
[12 Sep 2007 20:43] Igor Cherny
This is still not fixed.

I use SQLYog 6.0.3 and it shows variable to be of Binary/Image type for the "after_substr" variable.  Yet the workaround proc I submitted earlier - shows proper value of "b,c" as you'd expect it.
Both use exactly the same data type - text.
Same happens to longtext variables as well.
[12 Sep 2007 21:37] ilia guenkin
Still not fixed
MySQL 5.0.45 community edition
[13 Sep 2007 4:34] Valeriy Kravchuk
Sorry, but original problem is not repeatable in 5.0.44 (and, I hope, in 5.0.45, that has the same code):

mysql> select version();
+------------------------------+
| version()                    |
+------------------------------+
| 5.0.44-enterprise-gpl-nt-log |
+------------------------------+
1 row in set (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE text_test2(entity_id_str_in text)
    -> BEGIN
    ->     DECLARE str_remainder text;
    ->     DECLARE str_remainder2 text;
    ->
    ->     SET str_remainder2 = entity_id_str_in;
    ->     select 'before substr', str_remainder2;
    ->     SET str_remainder = SUBSTRING(str_remainder2, 3);
    ->     select 'after substr', str_remainder;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call text_test2('a,b,c');
+---------------+----------------+
| before substr | str_remainder2 |
+---------------+----------------+
| before substr | a,b,c          |
+---------------+----------------+
1 row in set (0.02 sec)

+--------------+---------------+
| after substr | str_remainder |
+--------------+---------------+
| after substr | b,c           |
+--------------+---------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

mysql> DROP PROCEDURE IF EXISTS text_test;
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter //
mysql> CREATE PROCEDURE text_test(entity_id_str_in text)
    -> BEGIN
    ->     DECLARE str_remainder text;
    ->
    ->     SET str_remainder = entity_id_str_in;
    ->
    ->     select 'before substr', str_remainder;
    ->     SET str_remainder = SUBSTRING(str_remainder, 3);
    ->     select 'after substr', str_remainder;
    -> END//
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> call text_test('a,b,c');
+---------------+---------------+
| before substr | str_remainder |
+---------------+---------------+
| before substr | a,b,c         |
+---------------+---------------+
1 row in set (0.03 sec)

+--------------+---------------+
| after substr | str_remainder |
+--------------+---------------+
| after substr | b,c           |
+--------------+---------------+
1 row in set (0.03 sec)

Query OK, 0 rows affected (0.03 sec)

So, please, explain in more details what is the problem, and why do you think it is the same as this bug report.
[13 Sep 2007 5:30] Igor Cherny
This is what I get under 5.0.45:

Note normal execution for text_test2(), but NOTHING returned for text_test().
Perhaps something changed between 5.0.44 and 5.0.45?

I can definitely attest that the behavior is somewhat different - before it showed just garbage, now it doesn't show anything at all.

In SQLYog - it shows that the variable is of Text/Image type but doesn't show anything either.

So the only change I see from the 5.0.37 behavior is from garbage to nothing.  Obviously it's wrong in both cases.
Hope this helps you identify the issue.  For now - I am sticking to the same work around used in text_test2() procedure.

***********************************************************

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 227741
Server version: 5.0.45-log Source distribution

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

mysql> use repository
Database changed
mysql> call text_test2('a,b,c');
+---------------+----------------+
| before substr | str_remainder2 |
+---------------+----------------+
| before substr | a,b,c          |
+---------------+----------------+
1 row in set (0.02 sec)

+--------------+---------------+
| after substr | str_remainder |
+--------------+---------------+
| after substr | b,c           |
+--------------+---------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

mysql> call text_test('a,b,c');
+---------------+---------------+
| before substr | str_remainder |
+---------------+---------------+
| before substr | a,b,c         |
+---------------+---------------+
1 row in set (0.01 sec)

+--------------+---------------+
| after substr | str_remainder |
+--------------+---------------+
| after substr |               |
+--------------+---------------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql>
[4 Oct 2007 8:46] Kristofer Pettersson
Can't repeat with 5.0.50-bk-runtime.
[4 Oct 2007 16:30] Igor Cherny
It's funny how you say you can't repeat it in 5.0.50 while I am reporting the problem in 5.0.45.
You need to either try on the same version I have or tell me the community version it'll be fixed in.  As far as I understand 5.0.50 is not a community version.
[4 Oct 2007 20:31] Kristofer Pettersson
I believe you and I take it seriously! :) Quick explanation: We use a process to maintain or improve quality. Any change I make will be to 5.0.50-bk-runtime because the version numbers always goes up, never backwards. This will later be merged into the main tree and end up in a community version after appropriate quality assurance. Since I can't repeat it, chances are that the bug is gone already (by an independent patch) in either current community branch or the next one.

Thanks for the help in finding bugs and making good bug reports!
[4 Oct 2007 20:35] Igor Cherny
Hey, no problem, happy to help.
So any idea what version of community it'll be in?