Bug #48440 DateTime return type of a stored function is wrongly determined as Decimal
Submitted: 30 Oct 2009 16:19 Modified: 13 Jan 2010 17:12
Reporter: Pavel Bazanov Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:Con 6.0.3, 6.1.1, 6.1.2, Server 5.1.37 OS:Any
Assigned to: CPU Architecture:Any
Tags: Input string was not in a correct format, return DateTime, stored function

[30 Oct 2009 16:19] Pavel Bazanov 
Description:
Hello,
Look at the test case in How to repeat section. It throws format exception, because data type of a return parameter is not explicitly specified and Connector/Net incorrectly decides that parameter's data type is decimal. It results to a FormatException when trying to parse DateTime string with Decimal.Parse() method (in MySqlDecimal.cs -> IMySqlValue.WriteValue() -> return new MySqlDecimal(Decimal.Parse(s, CultureInfo.InvariantCulture));

How to repeat:
public void ParsingBugTest_48440()
{
	MySqlCommand command = OpenConnectionAndCreateCommand();
	command.CommandText = "DROP FUNCTION IF EXISTS `TestFunction`";
	command.ExecuteNonQuery();
	command.CommandText =
		@"CREATE FUNCTION `TestFunction`() 
			RETURNS datetime
			RETURN NOW()";
	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 FormatException
}
[30 Oct 2009 19:31] Tonci Grgin 
Hi Pavel and thanks for your report.

This is not a bug according to http://dev.mysql.com/doc/refman/5.1/en/create-function-udf.html:
CREATE [AGGREGATE] FUNCTION function_name RETURNS {STRING|INTEGER|REAL|DECIMAL}

However, checking from server side, I see result returned as DATETIME:
mysql> select fbug48440();
Field   1:  `fbug48440()`
Catalog:    `def`
Database:   ``
Table:      ``
Org_table:  ``
Type:       DATETIME
Collation:  binary (63)
Length:     19
Max_length: 19
Decimals:   0
Flags:      BINARY

+---------------------+
| fbug48440()         |
+---------------------+
| 2009-10-30 20:28:35 |
+---------------------+
1 row in set (0.04 sec)

so I'll contact Reggie regarding this regardless of my ruling.
[30 Oct 2009 19:47] Tonci Grgin 
My bad, should have been http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html...
[30 Oct 2009 20:17] Tonci Grgin 
Due to unforeseen troubles, passing to Wlad for checking.
[13 Jan 2010 17:12] Reggie Burnett 
This is a duplicate of 49642