MySQL Bugs: #97300: GetSchemaTable returns unexpected table when sproc has output parameters

Bug #97300	GetSchemaTable returns unexpected table when sproc has output parameters
Submitted:	20 Oct 2019 16:16	Modified:	1 May 2020 16:12
Reporter:	Bradley Grainger (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	8.0.18	OS:	Windows (10)
Assigned to:		CPU Architecture:	Any

Description:
When executing a MySqlDataReader for a stored procedure that has no result set but only output parameters, GetSchemaTable returns a DataTable with unexpected results, specifically a column named "@_cnet_param_value".

Since there is no result set, it is expected that GetSchemaTable() would return null.

How to repeat:
Create the following stored procedure:

CREATE PROCEDURE out_int(
	OUT value INT
)
BEGIN
	SELECT 1 INTO value;
END;

Execute the following C# code:

using (var connection = new MySqlConnection("..."))
{
	connection.Open();

	using (var cmd = connection.CreateCommand())
	{
		cmd.CommandText = "out_int";
		cmd.CommandType = CommandType.StoredProcedure;
		cmd.Parameters.Add(new MySqlParameter
		{
			ParameterName = "@value",
			DbType = DbType.Int32,
			Direction = ParameterDirection.Output,
		});

		using (var reader = await cmd.ExecuteReaderAsync())
		{
			reader.Read();
			var schema = reader.GetSchemaTable();

			// schema has one row: ColumnName = @_cnet_param_value, 
		}
	}
}

There are additional unexpected behaviours:

using (var reader = await cmd.ExecuteReaderAsync())
{
	reader.Read(); // returns false

	Console.WriteLine(reader.GetFieldType(0)); // prints System.Int32
	Console.WriteLine(reader.GetName(0)); // prints @_cnet_param_value
	Console.WriteLine(reader.GetInt32(0)); // prints 1
}

Since MySqlDataReader.Read() returned false, it is expected that all data access functions should fail because there are no more rows.

Hello Bradley,

Thank you for the report and test case.

regards,
Umesh

Additionally:

Console.WriteLine(reader.HasRows); // prints true; expected false
Console.WriteLine(reader.FieldCount); // prints 1; expected 0

Posted by developer:
 
Added code to clean up the internal ResulSet in DataReader

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 8.0.21 release, and here's the proposed changelog entry from the documentation team:

An expected empty result set generated by executing MySQLDataReader for a
stored procedure instead returned a data table containing the
@_cnet_param_value column. This fix eliminates an internal error that
affected the result set and now GetSchemaTable() returns a null value as
expected.

Thank you for the bug report.