Bug #18587 Function that accepts and returns TEXT garbles data if longer than 766 chars

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.

Thank you for the bug report. I was able to repeat as reported.

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.

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;

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.

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

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

Fixed in 5.0.22 and 5.1.11

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.