MySQL Bugs: #17226: Variable set in cursor on first iteration is assigned second iterations value

Bug #17226	Variable set in cursor on first iteration is assigned second iterations value
Submitted:	8 Feb 2006 4:31	Modified:	8 Aug 2006 3:54
Reporter:	Matt Borack	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0.20-BK	OS:
Assigned to:	Tomash Brechko	CPU Architecture:	Any

Description:

If variable A is set to variable B, where variable B got its value from a cursor fetch statement that is contained in a loop, then upon the second iteration of the loop, variable A will be reassigned the value from the first iteration. Subsequent iterations will work fine.

How to repeat:

Use script c_table.txt to create and fill a table.

Use script c_function.txt to create the test function.

Execute: select test_fcn(10) will result in the incorrect answer b|b|c|d

Change c_function.txt by commenting the line with #A but uncommenting the line with #B. Run this script to recreate the test function.

Execute: select test_fcn(10) will result in the correct answer a|b|c|d

Script to create and fill table

Attachment: c_table.txt (text/plain), 361 bytes.

Script to create function

Attachment: c_function.txt (text/plain), 934 bytes.

Actually, the problem here is in incorrect handling of stored variables of TEXT (BLOB) type.
Minimal test case is:

CREATE PROCEDURE p1()
BEGIN
  DECLARE v_char VARCHAR(255);
  DECLARE v_text TEXT DEFAULT '';

  SET v_char = 'abc';

  SET v_text = v_char;

  SET v_char = 'def';

  SET v_text = concat(v_text, '|', v_char);

  SELECT v_text;
END|

CALL p1();

Current output:
v_text
def|def

Expected output:
v_text
abc|def

Confirmed on MySQL 5.0.22.
Why is this bug classified as "non critical"?
It makes quite difficult to handle even a simple loop with assignment of the previous value to a variable.

A workaround would be the following:
instead of

SET text_value = char_value;

this:

SET text_value = CONCAT(char_value, "");

This way you will force a new copy of the text. Apparently, the engine is using a pointer to the char value.
I'd say that this bug is at least S2.

Cheers

Giuseppe

Just an administrative note on bug severity: This field is set by the bug reporter, is mainly used in the initial prioritization of the bug verification, and is generally not changed by MySQL staff. The prioritization of the bug is tracked separately, and not available publicly.

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/8558

Pushed into 5.0.24 and 5.1.12

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://www.mysql.com/doc/en/Installing_source_tree.html

Documented bugfix in 5.0.24 and 5.1.12 changelogs.

"Changes in release 5.0.24" incorrectly labels this change as Bug#17225. See below.

[Assignments of values to variables of type TEXT were handled incorrectly in stored routines. (Bug#17225)]

"Changes in release 5.1.12 (Not yet released)" also mislabels this bug fix as Bug#17225.

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-12.html