Bug #93220 Can't call FUNCTION when parameter name contains parentheses
Submitted: 16 Nov 2018 6:22 Modified: 17 Nov 2018 6:28
Reporter: Bradley Grainger (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:8.0.13 OS:Microsoft Windows (10)
Assigned to: CPU Architecture:Other (x64)

[16 Nov 2018 6:22] Bradley Grainger
Description:
When a MySQL stored routine has a parameter whose name contains parentheses, it's not possible to execute the routine with MySqlCommand.

If the parameter is named "TEST(1)" and you add a MySqlParameter named "@TEST(1)" you get the following exception:

MySql.Data.MySqlClient.MySqlException (0x80004005): Fatal error encountered during command execution. ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Parameter '@TEST' must be defined.
   at MySql.Data.MySqlClient.Statement.SerializeParameter(MySqlParameterCollection parameters, MySqlPacket packet, String parmName, Int32 parameterIndex)
   at MySql.Data.MySqlClient.Statement.InternalBindParameters(String sql, MySqlParameterCollection parameters, MySqlPacket packet)
   at MySql.Data.MySqlClient.Statement.BindParameters()
   at MySql.Data.MySqlClient.PreparableStatement.Execute()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()

If you add a parameter named "@TEST" instead, you get:
System.ArgumentException: Parameter 'TEST(1)' not found in the collection.
   at MySql.Data.MySqlClient.MySqlParameterCollection.GetParameterFlexible(String parameterName, Boolean throwOnNotFound)
   at MySql.Data.MySqlClient.StoredProcedure.GetAndFixParameter(String spName, MySqlSchemaRow param, Boolean realAsFloat, MySqlParameter returnParameter)
   at MySql.Data.MySqlClient.StoredProcedure.CheckParameters(String spName)
   at MySql.Data.MySqlClient.StoredProcedure.Resolve(Boolean preparing)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()

If you add parameters named "@TEST" and "@TEST(1)" you get:
MySql.Data.MySqlClient.MySqlException (0x80004005): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(1));SELECT @_cnet_param_Parameter3' at line 1
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteScalar()

How to repeat:
Execute the following C# code:

using (var connection = new MySqlConnection("..."))
{
	connection.Open();
	using (var cmd = new MySqlCommand(@"DROP FUNCTION IF EXISTS echo;
CREATE FUNCTION echo(`TEST(1)` VARCHAR(20)) RETURNS VARCHAR(20)
BEGIN
	RETURN `TEST(1)`;
END", connection))
	{
		cmd.ExecuteNonQuery();
	}
	
	using (var cmd = new MySqlCommand("echo", connection))
	{
		cmd.CommandType = CommandType.StoredProcedure;

		// uncomment one or both lines to get different behaviour
		cmd.Parameters.AddWithValue("@TEST(1)", "a test");
		// cmd.Parameters.AddWithValue("@TEST", "a test 2");

		var returnValue = cmd.Parameters.Add(new MySqlParameter { Direction = ParameterDirection.ReturnValue });
		cmd.ExecuteScalar();
		Console.WriteLine(returnValue.Value); // expected: "a test"
	}
}
[17 Nov 2018 6:28] Umesh Shastry
Hello Bradley,

Thank you for the report and test case.
Observed with VS 2017 (C#.Net) and Connector/NET 8.0.13 version.

regards,
Umesh