Bug #18587 Function that accepts and returns TEXT garbles data if longer than 766 chars
Submitted: 28 Mar 2006 21:52 Modified: 24 May 2006 18:37
Reporter: Nathan Tanner Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.19/5.1BK OS:
Assigned to: Alexander Nozdrin CPU Architecture:Any

[28 Mar 2006 21:52] Nathan Tanner 
Description:
If you create a function that accepts a TEXT input and returns TEXT data type, and the data you pass it is longer than 766 characters, the return text is corrupted.

How to repeat:
CREATE FUNCTION Test(mytext text) RETURNS TEXT DETERMINISTIC 
RETURN CONCAT(mytext, "");

SELECT Test(REPEAT("a", 767));

Note that if you simply change to:

SELECT Test(REPEAT("a", 766));

the data returns unscathed... also works with values lower than 766, but anything higher than 766 corrupts the data.

Suggested fix:
Make it not corrupt the return data.
[28 Mar 2006 22:26] MySQL Verification Team 
Thank you for the bug report. I was able to repeat as reported.
[28 Mar 2006 22:41] Nathan Tanner 
UPDATE: It seems if you use a declared variable like the following, there is no corruption:

DELIMITER //

CREATE FUNCTION Test(mytext text) RETURNS TEXT DETERMINISTIC 
BEGIN
DECLARE myreturn TEXT;
SET myreturn=CONCAT(mytext, "");
RETURN myreturn;
END
//

DELIMITER ;

SELECT Test(REPEAT("a", 767));

Because I have found a work-around, I have lowered the severity.
[29 Mar 2006 19:08] Nathan Tanner 
As an interesting twist, the work-around fails when you subsequently use the function in a TRIGGER:

drop table test; drop function test;

CREATE TABLE test (test TEXT NOT NULL);

DELIMITER //

CREATE FUNCTION Test(mytext text) RETURNS TEXT DETERMINISTIC 
BEGIN
  DECLARE myreturn TEXT;
  SET myreturn=CONCAT(mytext, "b");
  RETURN myreturn;
END;
//

CREATE TRIGGER test BEFORE INSERT ON test
FOR EACH ROW BEGIN
  SET NEW.test=test(NEW.test);
END;
//

DELIMITER ;

INSERT INTO test VALUES (REPEAT("a",767));

SELECT * FROM test;
[7 Apr 2006 11:37] Alexander Nozdrin 
In 5.0.21 I can reproduce this bug only with the following case:

CREATE FUNCTION f1(arg TEXT) RETURNS TEXT DETERMINISTIC
BEGIN
  RETURN CONCAT(arg, "");
END|

SELECT f1(REPEAT("a", 767));

The most interesting thing that if the second argument of CONCAT()
is not an empty string, everything works well.
[21 Apr 2006 11:22] 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/5289
[10 May 2006 19:16] 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/6219
[18 May 2006 21:14] Dmitry Lenev 
Fixed in 5.0.22 and 5.1.11
[24 May 2006 18:37] Paul DuBois 
Noted in 5.0.23, 5.1.11 changelogs.

<literal>BLOB</literal> or <literal>TEXT</literal> arguments
to or values returned from stored functions were not copied
properly if too long and could become garbled.