MySQL Bugs: #25912: selecting negative time values gets wrong results

Bug #25912	selecting negative time values gets wrong results
Submitted:	29 Jan 2007 12:09	Modified:	31 Jan 2007 16:21
Reporter:	Martin Karch	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	1.0.8, 5.0.3	OS:
Assigned to:		CPU Architecture:	Any
Tags:	negative, result, retrieve, SELECT, time, value, wrong

Description:
hi,

i have a view that calculates some time differences. there the results can be negative. 
when i'm loading this view into a DataSet, the negative time value are wrong. viewing it with the text client or other frontend tools, it is displayed correctly.

it seems that the .NET Connector subtracts the minute value from the hour values if the time value is negative; e.g. -07:24:00 becomes -06:36:00 or -05:17:00 becomes -04:43:00.

How to repeat:
mysql:

CREATE TABLE test (`t` time);
INSERT INTO test SET T='-07:24:00';
INSERT INTO test SET T='-05:17:00';
INSERT INTO test SET T='-06:09:00';
INSERT INTO test SET T='-11:01:00';
INSERT INTO test SET T='-13:00:00';

.net (create a form with a DataGridView 'grd' and a BindingSource 'src'):

Protected Overrides Sub OnLoad(ByVal e As System.EventArgs)
  conn = New MySqlConnection("server=mysqk;database=test;user id=root;password=secret")
  conn.Open()

  Dim da As New MySqlDataAdapter("SELECT * FROM test", conn)
  cb = New MySqlCommandBuilder(da)

  da.Fill(ds, "test")
  src.DataSource = ds
  src.DataMember = "test"

  grd.DataSource = src
End Sub

Hi Martin and thanks for your problem report. Verified as described on MySQL server 5.0.34BK on WinXP Pro SP2 localhost with NET FW 2.0 and latest c/NET 5.0 sources.

Output: 
t = -06:36:00
============================
t = -04:43:00
============================
t = -05:51:00
============================
t = -10:59:00
============================
t = -13:00:00
============================
Done.

Test case:
        private void bnBug25912_Click(object sender, EventArgs e)
        {
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=localhost;Database=test;UserID=root;Password=;PORT=3306";//Allow Zero Datetime=True - doesn't matter
            conn.Open();

            MySqlCommand cmdCreateTable = new MySqlCommand("DROP TABLE IF EXISTS testbug25912", conn);
            cmdCreateTable.CommandTimeout = 0;
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = "CREATE TABLE testbug25912 (`t` time)";
            cmdCreateTable.ExecuteNonQuery();
            cmdCreateTable.CommandText = "INSERT INTO testbug25912 VALUES ('-07:24:00'),('-05:17:00'),('-06:09:00'),('-11:01:00'),('-13:00:00')";
            cmdCreateTable.ExecuteNonQuery();

            MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM testbug25912", conn);
            MySqlCommandBuilder cb = new MySqlCommandBuilder(da);

            DataTable dt = new DataTable();
            da.Fill(dt);

            DisplayData(dt);
            Console.WriteLine("Done.");

        }

        private static void DisplayData(System.Data.DataTable table)
        {
            foreach (System.Data.DataRow row in table.Rows)
            {
                foreach (System.Data.DataColumn col in table.Columns)
                {
                    Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
                }
                Console.WriteLine("============================");
            }
        }

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19046

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/19047

Fixed in 5.0.4 and 1.0.9

A note has been added to the 1.0.9 and 5.0.4 changelogs.