MySQL Bugs: #67871: UDF argument crosses over between rows

Bug #67871	UDF argument crosses over between rows
Submitted:	11 Dec 2012 1:30	Modified:	13 Dec 2012 21:16
Reporter:	Tom Pledger	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: User-defined functions ( UDF )	Severity:	S3 (Non-critical)
Version:	5.1.60 and 5.5.24-log	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	argument, crosstalk, udf

Description:
When calling a deterministic C function within a SELECT statement, the results differ for two queries that select the same row by different means.  The query that uses an index gets the right result, but the other seems to pass an argument of "-" that belongs to a different row in place of "".

The following is copied and pasted from my shell; the wrong result is first, and the right one is second.

[tom@aphrodite ~]$ mysql < MySQL-C-parameter.sql
test_id replace_characters(p_string_in, p_character_list, p_replacement_character, "INCLUDE")
11      -a---ie----a--
test_id replace_characters(p_string_in, p_character_list, p_replacement_character, "INCLUDE")
11      aiea

How to repeat:
This involves more code than your guidelines recommend.  Please contact me if you'd like the 3 files concerned as attachments.  They are a .h and a .c file to define our replace_characters function, and a .sql file to exhibit the bug.

Here is an excerpt from the .sql file:

replace into etl_test_replace_characters
  (test_id, p_string_in, p_character_list, p_replacement_character, p_incl_excl)
values
  [...]
  ( 9, "Rant", "a", "-", "INCLUDE"),
  (10, "Rant", "a", "-", "EXCLUDE"),
  (11, "Mansfield Park", "aeiou", "", "INCLUDE"),
  (12, "Mansfield Park", "aeiou", "", "EXCLUDE");

select test_id, replace_characters(p_string_in, p_character_list, p_replacement_character, "INCLUDE")
  from etl_test_replace_characters
  where p_incl_excl = "INCLUDE"
  and p_character_list = "aeiou";
select test_id, replace_characters(p_string_in, p_character_list, p_replacement_character, "INCLUDE")
  from etl_test_replace_characters
  where test_id >= 11
  and p_incl_excl = "INCLUDE";

Suggested fix:
Find the place where the UDF_ARGS object is being prepared for passing to a user-defined function, and make sure that all the new values are put into it, overwriting any leftovers from a previously visited row, even if one of the new values is an empty string.

[Edit 12/Dec/2012 NZDT]  I've now seen how to attach files, so will upload the 3 I was talking about, instead of standing by to email them if asked.

Please disregard the part of C-function-tests.sql before 'create database datamine_arg2c;'.

Thank you for the report.

This is not a bug. You have to check length of third argument before assigning it to repl.

I added following check:

	  else if (*repl)
	    {
	    	fprintf(stderr, "Repl, i: %d, repl: %s, len: %d\n", i, repl, args->lengths[2]);
	      /* s[i] is to be replaced */
	      *resultp++ = *repl;
	    }

Then I saw in the error log file:

Repl, i: 0, repl: -^GINCLUDE, len: 0
i: 1, s[i]: s
Repl, i: 2, repl: -^GINCLUDE, len: 0
Repl, i: 3, repl: -^GINCLUDE, len: 0
Repl, i: 4, repl: -^GINCLUDE, len: 0
i: 5, s[i]: s
i: 6, s[i]: s
Repl, i: 7, repl: -^GINCLUDE, len: 0
Repl, i: 8, repl: -^GINCLUDE, len: 0
Repl, i: 9, repl: -^GINCLUDE, len: 0
Repl, i: 10, repl: -^GINCLUDE, len: 0
i: 11, s[i]: s
Repl, i: 12, repl: -^GINCLUDE, len: 0
Repl, i: 13, repl: -^GINCLUDE, len: 0

This clearly demonstrates source of the problem.

Thanks.  That's good news - it's easy to fix.  Apologies for troubling you with my null-terminated string habit!