MySQL Bugs: #103801: Can't use TimeSpan value with microseconds using prepared statement

Bug #103801	Can't use TimeSpan value with microseconds using prepared statement
Submitted:	25 May 2021 20:38	Modified:	29 Nov 2021 17:41
Reporter:	Bradley Grainger (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	8.0.25	OS:	Windows (10)
Assigned to:		CPU Architecture:	Any

Description:
When a TimeSpan is used as a MySqlParameter.Value value, and that TimeSpan has non-zero microseconds, the microseconds are lost if MySqlCommand.Prepare is called. This causes data loss if the parameter is used in an INSERT statement, and failure to find data if used as part of a SELECT ... WHERE.

This is similar to bug #91770 but that only fixed the serialization of milliseconds.

How to repeat:
Execute the following SQL:

create table times(value TIME(4));
insert into times(value) values(time '00:00:01.2345');

Run the following C# code:

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

using var command = connection.CreateCommand();
command.CommandText = "SELECT value FROM times WHERE value = @value;";
command.Parameters.AddWithValue("@value", new TimeSpan(0, 0, 0, 1, 234) + TimeSpan.FromTicks(5000));

// *** this causes the bug ***
command.Prepare();

using var reader = command.ExecuteReader();

// prints False; expected True
Console.WriteLine(reader.Read());

// throws MySqlException; expected 00:00:01.2345000
Console.WriteLine(reader.GetValue(0));

You can also reproduce the bug using an INSERT statement:

using var command = connection.CreateCommand();
command.CommandText = "insert into times(value) values(@value);";
command.Parameters.AddWithValue("@value", new TimeSpan(0, 0, 0, 1, 234) + TimeSpan.FromTicks(5000));
command.Prepare(); // this causes the bug
command.ExecuteNonQuery();

This inserts '00:00:01.2340' but it should be '00:00:01.2345'.

Hello Bradley,

Thank you for the report and test case.
Verified as described.

regards,
Umesh

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

Microseconds specified in a time-span value were calculated incorrectly 
for prepared statements.

Thank you for the bug report.