Bug #91770 TIME(n) column loses microseconds with prepared command

• Submitted: 24 Jul 2018 4:40
• Modified: 20 Apr 2020 15:53
• Reporter: Bradley Grainger (OCA)
• Status: Closed
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 8.0.11
• OS: Windows (10)
• CPU Architecture: Any (x64)

[24 Jul 2018 4:40] Bradley Grainger

Description:
If MySqlCommand.Prepare() is called for a statement that SELECTs a TIME(n) column, the "microseconds" part is set to zero on the returned result. 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))
	{
		command.Parameters.AddWithValue("@data", new TimeSpan(1, 2, 3, 4, 567));
		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, expected 1.02:03:04.56700000
		Console.WriteLine(result);
	}
}

[24 Jul 2018 4:54] Chiranjeevi Battula

Hello Bradley Grainger,

Thank you for the bug report and test case.
Verified this behavior on Visual Studio 2017 (C#.Net) and Connector/NET 8.0.11 version.

Thanks,
Chiranjeevi.

[24 Jul 2018 5:02] Chiranjeevi Battula

Screenshot

Attachment: Bug_91770.PNG (image/png, text), 69.86 KiB.

[16 Apr 2020 19:45] Gustavo Cuatepotzo

Posted by developer:
 
The problem was in file MySqlTime.cs, the method ReadValue always divide the milliseconds by 1000000, this produce a decimal result, after, the result was cast to integer, that's why zero was returned.

[20 Apr 2020 15:53] Christine Cole

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:

The microseconds value in the return results was set to zero consistently
when SqlCommand.Prepare() was called for a SELECT statement with a TIME(n)
column. This fix revises the way the value is produced to ensure accurate
results.

Thank you for the bug report.