Bug #13941 replace() string fuction behaves badly inside stored procedure
Submitted: 11 Oct 2005 23:05 Modified: 9 Nov 2005 3:56
Reporter: Ward Harold Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.13 OS:Linux (linux (RH AS2.1))
Assigned to: Bugs System

[11 Oct 2005 23:05] Ward Harold
Description:
When used inside a stored procedure the replace() string function returns an empty string when the first parameter, str, contains no instances of the second parameter, from_str. I expected it to return the first parameter unchanged rather than an empty string. That is how it behaves when used from the command line. That is also how it behaves in release 5.0.2.

Here is a small storred procedure that illustrates the problem:

create function
  test_replace
( 
  p_input_str  text
)
returns
  text
begin

  declare p_output_str text;
  declare v_rs text;

  set p_output_str = p_input_str;

  set p_output_str = replace(p_output_str, 'xyzzy', 'plugh');
  set p_output_str = replace(p_output_str, 'test', 'prova');
  set p_output_str = replace(p_output_str, 'this', 'questo');
  set p_output_str = replace(p_output_str, ' a ', 'una ');
  set p_output_str = replace(p_output_str, 'is', '');

  return p_output_str;
end;

Running:

mysql>select test_replace('this is a test')

on 5.0.2 returns:

'questo una prova'

on 5.0.13 it returns the empty string.

Note that if the first call to replace, set p_output_str = replace(p_output_str, 'xyzzy', 'plugh'), is removed then the function works as expected on 5.0.13.

How to repeat:
On 5.0.13 create the following stored procedure:

create function
  test_replace
( 
  p_input_str  text
)
returns
  text
begin

  declare p_output_str text;
  declare v_rs text;

  set p_output_str = p_input_str;

  set p_output_str = replace(p_output_str, 'xyzzy', 'plugh');
  set p_output_str = replace(p_output_str, 'test', 'prova');
  set p_output_str = replace(p_output_str, 'this', 'questo');
  set p_output_str = replace(p_output_str, ' a ', 'una ');
  set p_output_str = replace(p_output_str, 'is', '');

  return p_output_str;
end;

then do:

select test_replace('this is a test');

the return value is the empty string rather than the string:

'questo una prova'
[12 Oct 2005 2:08] Jorge del Conde
Thanks for your bug report.  I was able to reproduce this using FC4 and mysql 5bk
[12 Oct 2005 2:08] Jorge del Conde
mysql> select test_replace('this is a test')//
+--------------------------------+
| test_replace('this is a test') |
+--------------------------------+
|                                |
+--------------------------------+
1 row in set (0.02 sec)

mysql>
[19 Oct 2005 13:32] 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/internals/31253
[21 Oct 2005 13:38] Per-Erik Martin
Pushed to bk 5.0.15
[21 Oct 2005 15:22] Per-Erik Martin
Sorry, a typo... pushed to bk 5.0.16 of course.
[9 Nov 2005 3:56] Paul Dubois
Noted in 5.0.16 changelog.