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

[25 May 20:38] Bradley Grainger
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'.
[27 May 7:56] MySQL Verification Team
Hello Bradley,

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

regards,
Umesh