MySQL Bugs: #48440: DateTime return type of a stored function is wrongly determined as Decimal

Bug #48440	DateTime return type of a stored function is wrongly determined as Decimal
Submitted:	30 Oct 17:19	Modified:	30 Oct 20:47
Reporter:	Pavel Bazanov
Status:	Analyzing
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:	Vladislav Vaintroub	Target Version:
Tags:	Input string was not in a correct format, return DateTime, stored function

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
}

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.

My bad, should have been http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html...

Due to unforeseen troubles, passing to Wlad for checking.