MySQL Bugs: #49642: FormatException when returning empty string from a stored function

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

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.

Hi Pavel and thanks for your report, no offenses taken.

Verified just as described against c/NET trunk.

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)

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)

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)

fixed in 6.0.6, 6.1.4, and 6.2.3

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)

Bug#48440 was marked as duplicate of this report.

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.