MySQL Bugs: #13941: replace() string fuction behaves badly inside stored procedure

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:	Per-Erik Martin	CPU Architecture:	Any

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'

Thanks for your bug report.  I was able to reproduce this using FC4 and mysql 5bk

mysql> select test_replace('this is a test')//
+--------------------------------+
| test_replace('this is a test') |
+--------------------------------+
|                                |
+--------------------------------+
1 row in set (0.02 sec)

mysql>

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

Pushed to bk 5.0.15

Sorry, a typo... pushed to bk 5.0.16 of course.

Noted in 5.0.16 changelog.