MySQL Bugs: #39275: Inserting negative time value through the use of MySqlParameter throws exception

Bug #39275	Inserting negative time value through the use of MySqlParameter throws exception
Submitted:	5 Sep 2008 14:04	Modified:	15 Sep 2008 14:34
Reporter:	Michal Minárik	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.3	OS:	Windows (Vista 32-bit)
Assigned to:		CPU Architecture:	Any
Tags:	time negative insert parameter exception

Description:
Inserting negative time value (negative TimeSpan) into Time column through the use of MySqlParameter throws MySqlException "Incorrect time value 'x -xx:xx:xx.x' ..."

How to repeat:
-- Table:

CREATE TABLE `test` (
  `id` int(11) NOT NULL auto_increment,
  `time` time NOT NULL default '00:00:00',
  `desc` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- Code:

MySqlConnection conn = new MySqlConnection(connectionString);
conn.Open();
MySqlCommand command = new MySqlCommand();
command.Connection = conn;
command.CommandText = @"INSERT INTO `my_db`.`test` (`time`, `desc`) VALUES (@time, 'Negative time value')";
command.CommandType = CommandType.Text;

MySqlParameter param = new MySqlParameter();
param = new MySqlParameter();
param.ParameterName = "@time";
param.DbType = DbType.Time;
param.MySqlDbType = MySqlDbType.Time;
param.IsNullable = true;
param.Value = new TimeSpan(-10, 0, 0);
command.Parameters.Add(param);

command.ExecuteNonQuery();

--- Result:

MySqlException: "Incorrect time value: '0 -10:00:00.0' for column 'time' at row 1"

Suggested fix:
The value format should be '-x xx:xx:x.x'
I hope this bug requires only little code change.

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/53383

fixed in 5.2.4

I think there's still a bug. The correct format for insert negative time value is '-x xx:xx:xx.xx'

The code in MySqlTime.WriteValue(...) could be:

  TimeSpan ts = (TimeSpan)val;
  bool negative = ts.TotalMilliseconds < 0 ? true : false;

  if (negative)
     ts = ts.Duration();

  if (binary)
  {
    stream.WriteByte(8);
    stream.WriteByte((byte)(negative ? 1 : 0));
    stream.WriteInteger(ts.Days, 4);
    stream.WriteByte((byte)ts.Hours);
    stream.WriteByte((byte)ts.Minutes);
    stream.WriteByte((byte)ts.Seconds);
  }
  else
  {
    string s;
    if (negative)
    {
      s = String.Format("'-{0} {1:00}:{2:00}:{3:00}.{4}'",
        ts.Days, ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds);
    }
    else
    {
      s = String.Format("'{0} {1:00}:{2:00}:{3:00}.{4}'",
        ts.Days, ts.Hours, ts.Minutes, ts.Seconds, ts.Milliseconds);
    }

    stream.WriteStringNoNull(s);
  }

Testing solution of this bug will give correct results only after fix Bug#39294

You are right.  There is a case that is not properly handled.  I've taken the code you included and optimized it a bit and added more test cases.  Thanks for catching it!

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/53541

It is now  :)

An entry was added to the 5.2.4 changelog:

Inserting a negative time value (negative TimeSpan) into a Time column through the use of MySqlParameter caused MySqlException to be thrown.