Bug #91752 00:00:00 is converted to NULL with prepared command

• Submitted: 22 Jul 2018 3:03
• Modified: 5 Mar 2019 19:23
• Reporter: Bradley Grainger (OCA)
• Status: Closed
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 8.0.11
• OS: Windows (10.0.17134.167)
• CPU Architecture: Any (x64)

[22 Jul 2018 3:03] Bradley Grainger

Description:
If MySqlCommand.Prepare() is called for a statement that SELECTs a TIME column, a zero TIME (00:00:00) is incorrectly returned as NULL.

The same value (i.e., TimeSpan.Zero) should be returned whether command.Prepare() is called or not.

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(tm TIME NOT NULL);
INSERT INTO test_time VALUES(0);
", connection))
		command.ExecuteNonQuery();
		
	using (var command = new MySqlCommand(@"SELECT tm FROM test_time", connection))
	{
		// *** this causes the bug ***
		command.Prepare();
		using (var reader = command.ExecuteReader())
		{
			reader.Read();
			
			// gets null, expected 00:00:00
			Console.WriteLine(reader.GetValue(0));
			
			// throws SqlNullValueException, expected 00:00:00
			Console.WriteLine(reader.GetTimeSpan(0));
		}
	}
}

[22 Jul 2018 14:05] MySQL Verification Team

Hello Bradley,

Thank you for the report and feedback.

Thanks,
Umesh

[5 Mar 2019 19:23] Christine Cole

Posted by developer:
 
Fixed as of the upcoming MySQL Connector/NET 6.10.9 and 8.0.16 releases, and here's the changelog entry:

Validation was added to ensure that when a column is of type TIME and the
value is 00:00:00, it takes the value instead of setting NULL.

Thank you for the bug report.

[13 Oct 2021 14:54] Ann Onymous

This is still a problem in 8.0.26.
It appears to happen with a TIME NOT NULL column, where the column contains a nonzero time on one row followed by a zero time on the next row. Please see the attached program, adapted from the previous example. the first two rows are read successfully, the third row produces a "Data is Null" exception.

static void Main(string[] args)
{
    using (var connection = new MySqlConnection("..."))
    {
        connection.Open();
        using (var command = new MySqlCommand(@"
            DROP TABLE IF EXISTS test_time;
            CREATE TABLE test_time(tm TIME NOT NULL);
            INSERT INTO test_time VALUES('00:00:00');
            INSERT INTO test_time VALUES('01:01:01');
            INSERT INTO test_time VALUES('00:00:00');
            ", connection))
        command.ExecuteNonQuery();

        using (var command = new MySqlCommand(@"SELECT tm FROM test_time", connection))
        {
            command.Prepare();
            using (var reader = command.ExecuteReader())
            {
                OutputRow(reader);
                OutputRow(reader);
                OutputRow(reader);
            }
        }
    }

    Console.ReadKey();
}

private static void OutputRow(MySqlDataReader reader)
{
    reader.Read();

    try
    {
        Console.WriteLine($"GetValue: {reader.GetValue(0)}, GetTimeSpan: {reader.GetTimeSpan(0)}");
    }
    catch (Exception ex)
    {
        Console.WriteLine(ex.Message);
    }
}