Bug #49642 FormatException when returning empty string from a stored function
Submitted: 12 Dec 2009 21:13 Modified: 15 Jan 2010 10:47
Reporter: Pavel Bazanov Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:MySql 5.1.41 & Connector/Net 6.1.3 OS:Any
Assigned to: Vladislav Vaintroub CPU Architecture:Any
Tags: FormatException, Input string was not in a correct format, parsing empty string, stored function returns empty string

[12 Dec 2009 21:13] Pavel Bazanov
Description:
Hello,
And again, unfortunately, I have found a parsing bug.

See the How to repeat section for all the necessary details.

PS. No offense, but I already sent you 2 or more ( http://bugs.mysql.com/bug.php?id=45380 , http://bugs.mysql.com/bug.php?id=48440 and so on ) parsing bugs. Maybe you need to completely refactor the functions which parse stored function bodies and return values? Or maybe you need to talk to the guys who are responsible for that part of the project? Again, no offense please. 

How to repeat:
public void NextParsingBugTest_49642()
{
	MySqlCommand command = OpenConnectionAndCreateCommand();
	command.CommandText = "DROP FUNCTION IF EXISTS `TestFunction`";
	command.ExecuteNonQuery();
	command.CommandText =
		@"CREATE FUNCTION `TestFunction`() 
			RETURNS varchar(20)
			RETURN ''";
	command.ExecuteNonQuery();
	command.CommandType = CommandType.StoredProcedure;
	command.CommandText = "TestFunction";
	MySqlParameter returnParam = new MySqlParameter();
	returnParam.ParameterName = "?RetVal_";
	returnParam.Direction = ParameterDirection.ReturnValue;
	command.Parameters.Add(returnParam);
	command.ExecuteNonQuery(); // throws "Input string was not in a correct format." FormatException.
}

Suggested fix:
When parsing SHOW CREATE FUNCTION result, there is only 0x0A character between "RETURNS varchar(20) CHARSET utf8" and "RETURN ''".
You need to treat that 0x0A as a delimiter, maybe replace it with a whitespace, or 0x0A, 0x0D sequence. Or maybe you need to make the server use another delimiter instead of only 0x0A.
[14 Dec 2009 13:03] Tonci Grgin
Hi Pavel and thanks for your report, no offenses taken.

Verified just as described against c/NET trunk.
[13 Jan 2010 17:15] 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/96801

784 Reggie Burnett	2010-01-13
      - fixed problem where not specifying the data type of the return value of a function caused it to 
        mistakenly use Decimal as the return type (bug #49642)
[13 Jan 2010 17:33] 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/96804

801 Reggie Burnett	2010-01-13
      - fixed problem where not specifying the data type of the return value of a function caused it to 
        mistakenly use Decimal as the return type (bug #49642)
[13 Jan 2010 17:35] 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/96805

823 Reggie Burnett	2010-01-13 [merge]
      - fixed problem where not specifying the data type of the return value of a function caused it to 
        mistakenly use Decimal as the return type (bug #49642)
[13 Jan 2010 17:35] Reggie Burnett
fixed in 6.0.6, 6.1.4, and 6.2.3
[13 Jan 2010 17:35] 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/96806

820 Reggie Burnett	2010-01-13 [merge]
      - fixed problem where not specifying the data type of the return value of a function caused it to 
        mistakenly use Decimal as the return type (bug #49642)
[14 Jan 2010 7:48] Tonci Grgin
Bug#48440 was marked as duplicate of this report.
[15 Jan 2010 10:47] Tony Bedford
An entry has been added to the 6.0.6, 6.1.4, and 6.2.3 changelogs:

A FormatException was generated when an empty string was returned from a stored function.