Bug #39547 .NET Connector returns Exception on 0000-00-00 date

• Submitted: 19 Sep 2008 18:56
• Modified: 29 Oct 2008 14:23
• Reporter: Justin Zak
• Status: No Feedback
• Category: Connector / NET
• Severity: S2 (Serious)
• Version: 5.2
• OS: Windows (XP)
• CPU Architecture: Any
• Tags: date, exception, zero

[19 Sep 2008 18:56] Justin Zak

Description:
When querying a table that contains a date field and any row contains 0000-00-00 in that field, the MySQLReader will return an exception when trying to convert to a C# DateTime.  This does not affect the datetime date type, only the date.

How to repeat:
Create a table containing a date (not datetime).  
Put 0000-00-00 as the value.
Using the .NET connector using MySqlReader, run a select statement to retrieve this value.
Result will be an exception converting to System.DateTime.

Suggested fix:
Change the functionality to work the same with 0 dates as it does with 0 datetimes.

[29 Sep 2008 14:23] Tonci Grgin

Hi Justin and thanks for your report.

I am unable to repeat it using this test case:
            string s = "Server=localhost;Database=test;Uid=root;pwd=****;port=****;allow zero datetime=True;convert zero datetime=True;";
            MySqlConnection conn = new MySqlConnection(s);
            conn.Open();
            
            MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS bug39547", conn);
            cmdCreateTable.CommandTimeout = 0;
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = "CREATE TABLE bug39547 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, `DateCol` DATE default '0000-00-00')ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8";
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = "INSERT INTO bug39547 VALUES(null,'0000-00-00')";
            cmdCreateTable.ExecuteNonQuery();

            MySqlCommand command = new MySqlCommand();
            command.Connection = conn;
            command.CommandType = CommandType.Text;
            command.CommandText = "SELECT DateCol FROM bug39547";
            using (MySqlDataReader reader = command.ExecuteReader())
            {
                reader.Read();
                DateTime var = reader.GetDateTime(reader.GetOrdinal("DateCol"));
                Console.WriteLine(var);
            }
            cmdCreateTable.Dispose();
            command.Dispose();
            conn.Close();
            Console.WriteLine("Done");

Output is as expected:
01.01.01 00:00:00
Done

If you still feel this is a bug, please attach full test case based on one above.

[30 Oct 2008 0:00] Bugs System

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".