Bug #100218 TIME(n) column deserializes milliseconds incorrectly with prepared command

• Submitted: 14 Jul 2020 15:00
• Modified: 17 Jul 2020 19:55
• Reporter: Bradley Grainger (OCA)
• Status: Closed
• Category: Connector / NET
• Severity: S3 (Non-critical)
• Version: 8.0.21
• OS: Windows (10)
• CPU Architecture: Any

[14 Jul 2020 15:00] Bradley Grainger

Description:
This is a follow-up to bug #91770. A regression has been introduced in 8.0.21.

If MySqlCommand.Prepare() is called for a statement that SELECTs a TIME(n) column, trailing zeros in the "milliseconds" part (when it is written as a decimal integer) are lost on the returned result. For example, a milliseconds value of "500" will be returned as "5", and "560" as "56".

The microseconds should be set correctly whether or not MySqlCommand.Prepare is called.

How to repeat:
Run the following C# code:

// NOTE: MUST have IgnorePrepare=false in connection string
using (var connection = new MySqlConnection("...;IgnorePrepare=false"))
{
	connection.Open();
	using (var command = new MySqlCommand(@"
DROP TABLE IF EXISTS test_time;
CREATE TABLE test_time(data TIME(3) NOT NULL);
", connection))
		command.ExecuteNonQuery();

	using (var command = new MySqlCommand(@"INSERT INTO test_time VALUES(@data);", connection))
	{
		// prints: 1.02:03:04.5600000
		var ts = new TimeSpan(1, 2, 3, 4, 560);
		Console.WriteLine(ts);

		command.Parameters.AddWithValue("@data", ts);
		command.ExecuteNonQuery();
	}

	using (var command = new MySqlCommand(@"SELECT data FROM test_time;", connection))
	{
		// *** this causes the bug ***
		command.Prepare();

		var result = (TimeSpan)command.ExecuteScalar();

		// prints 1.02:03:04.0560000, expected 1.02:03:04.56000000
		Console.WriteLine(result);
	}
}

Suggested fix:
The bug was introduced here: https://github.com/mysql/mysql-connector-net/commit/810188dc14508b164b91a0ed71406a568070b6...

Instead of converting the microseconds to a string, it should multiply it by 10 (to convert to Ticks) then add it to the TimeSpan value.

[14 Jul 2020 15:02] Bradley Grainger

Here's a variant of the bug for TIME(6) that deserializes microseconds incorrectly:

using (var connection = new MySqlConnection("...;IgnorePrepare=false"))
{
	connection.Open();
	using (var command = new MySqlCommand(@"
DROP TABLE IF EXISTS test_time;
CREATE TABLE test_time(data TIME(6) NOT NULL);
", connection))
		command.ExecuteNonQuery();

	using (var command = new MySqlCommand(@"INSERT INTO test_time VALUES(@data);", connection))
	{
		// prints: 1.02:03:04.0005600
		var ts = new TimeSpan(1, 2, 3, 4) + TimeSpan.FromTicks(5600);
		Console.WriteLine(ts);

		command.Parameters.AddWithValue("@data", ts);
		command.ExecuteNonQuery();
	}

	using (var command = new MySqlCommand(@"SELECT data FROM test_time;", connection))
	{
		// *** this causes the bug ***
		command.Prepare();

		var result = (TimeSpan)command.ExecuteScalar();

		// prints 1.02:03:04.0560000, expected 1.02:03:04.0005600
		Console.WriteLine(result);
	}
}

[15 Jul 2020 8:18] MySQL Verification Team

Hello Bradley,

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

regards,
Umesh

[17 Jul 2020 19:55] Christine Cole

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

Microseconds were deserialized incorrectly when MySqlCommand.Prepare() was
called for a statement that selects a TIME(n) column, resulting in a loss
of trailing zeros in the returned result. Now, the MySqlTime class
calculates ticks, rather than converting the microseconds to a string.

Thank you for the bug report.