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: | |
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
[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.