Bug #21214 SP returns garbage in out-parameter of type TEXT

• Submitted: 21 Jul 2006 14:29
• Modified: 21 Jul 2006 14:52
• Reporter: Paul Lucassen
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.22 pro gpl
• OS: Linux (SLES9)
• CPU Architecture: Any
• Tags: out-parameter, return value, stored procedure

[21 Jul 2006 14:29] Paul Lucassen

Description:
When returning a value from a TEXT field in a TEXT-type out-parameter of a SP, garbage is returned.
When a VARCHAR-type variable is used as OUT-parameter, the correct value is returned. This can be used as a work-around.

An example of Mysql statements to repeat this are given below.

Note:
This bug resembles Bug #18587, but persists in 5.0.22 while #18587 is resolved.

How to repeat:
DELIMITER //

DROP PROCEDURE IF EXISTS proc_search_text
//
CREATE PROCEDURE proc_search_text()
BEGIN
  DECLARE xtext TEXT DEFAULT "";
  DECLARE xvarchar VARCHAR(100) DEFAULT "";

  DROP TABLE IF EXISTS test;
  CREATE TABLE IF NOT EXISTS test ( fld_text TEXT );
  INSERT INTO test (fld_text) VALUES ("Text data 1 2 3 4 5");

  SET xtext := "";
  CALL proc_get_text( xtext );

  SET xvarchar := "";
  CALL proc_get_text( xvarchar );

  SELECT xvarchar, xtext;
END
//

DROP PROCEDURE IF EXISTS proc_get_text
//
CREATE PROCEDURE proc_get_text (OUT out_text TEXT)
BEGIN
  SELECT fld_text INTO out_text FROM test LIMIT 1;
END
//

DELIMITER ;

CALL proc_search_text();

[21 Jul 2006 14:52] MySQL Verification Team

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

mysql> CALL proc_search_text();
+---------------------+---------------------+
| xvarchar            | xtext               |
+---------------------+---------------------+
| Text data 1 2 3 4 5 | Text data 1 2 3 4 5 | 
+---------------------+---------------------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

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

mysql>