MySQL Bugs: #27415: Text Variables in stored procedures

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

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.

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)

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.

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.

Approved to push the test case into the test suite.

Pushed into 5.1.19-beta

Pushed into 5.0.44

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.

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.

Still not fixed
MySQL 5.0.45 community edition

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.

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>

Can't repeat with 5.0.50-bk-runtime.

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.

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!

Hey, no problem, happy to help.
So any idea what version of community it'll be in?