Bug #43986 Wrong values displayed
Submitted: 31 Mar 2009 13:18 Modified: 8 Sep 2009 20:03
Reporter: Jorge Bastos Email Updates:
Status: Not a Bug Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:5.2.5 OS:Any
Assigned to: Reggie Burnett CPU Architecture:Any

[31 Mar 2009 13:18] Jorge Bastos
Description:
Hi,

having a table:
create table aa values (a time not null);

if i have the value "25:00:00", the value is displayed as "1.01:00:00".
I'm retrieving the value like this:

How to repeat:
select * from aa;

myreader.item("a")

this should be working correct?
[6 Apr 2009 14:40] Tonci Grgin
Hi Jorge and thanks for your report.

You just dropped bomb on me with no actual info. Can you please state MySQL server version, SQL_MODE server is in and attach full test case containing the DDL statements too.
[7 Apr 2009 12:38] Jorge Bastos
Hi Tonci,
You're right, i forgot that, sorry!
So, MySQL version is 5.0.77-1 on debian SID.
Now i'm trying with .NET driver 6.0.1beta, and the same happens.

First, when retrieving the value, i have to do convert it to string, or i'll get a:
Argument 'Prompt' cannot be converted to type 'String'.

Only after that i can see the value.

Second, when the value on the time field is 25:00:00 or 24:00:00 it show's it wrong, but if the value is less than 24:00:00, for example 23:59:59, it is shown correctly.
I'm going to send the sample app made with vs2008 in vb.net.
[7 Apr 2009 12:40] Jorge Bastos
Table structure and one record here:
---
CREATE TABLE cba (
  a time NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO cba VALUES ('24:00:00');
---
[7 Apr 2009 12:42] Jorge Bastos
mysql> show global variables like '%mode%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> show global variables like '%version%';
+-------------------------+------------------+
| Variable_name           | Value            |
+-------------------------+------------------+
| protocol_version        | 10               |
| version                 | 5.0.77-1         |
| version_comment         | (Debian)         |
| version_compile_machine | i486             |
| version_compile_os      | debian-linux-gnu |
+-------------------------+------------------+
5 rows in set (0.00 sec)
[7 Apr 2009 12:47] Jorge Bastos
Sample project plus .net driver used on the root of the project

Attachment: bug-data-43986.zip.zip (application/x-zip-compressed, text), 308.93 KiB.

[7 Apr 2009 15:07] Tonci Grgin
Thank you Jorge.

Verified as described with following test case:
            MySqlConnection conn = new MySqlConnection();
            conn.ConnectionString = "DataSource=**;Database=test;UserID=**;Password=**;PORT=**;logging=True;pooling=false";
            //Allow Zero Datetime is of no relevance here
            conn.Open();

            MySqlCommand command = new MySqlCommand();
            command.Connection = (MySqlConnection)conn;
            command.CommandTimeout = 0;

            command.CommandText = "DROP TABLE IF EXISTS bug43986";
            command.CommandType = CommandType.Text;
            command.ExecuteNonQuery();

            command.CommandText = "CREATE TABLE bug43986 (a time NOT NULL) ENGINE=MyISAM;";
            command.CommandType = CommandType.Text;
            command.ExecuteNonQuery();

            command.CommandText = "INSERT INTO bug43986 VALUES ('24:00:00');";
            command.CommandType = CommandType.Text;
            command.ExecuteNonQuery();

            command.CommandText = "SELECT * FROM bug43986";
            command.CommandType = CommandType.Text;

            MySqlDataReader dr = command.ExecuteReader();
            dr.Read();
            Console.Out.WriteLine("Column is:" + dr.GetName(0));
            Console.Out.WriteLine("Time is:" + dr[0].ToString());

            dr.Close();
            command.Dispose();
            conn.Close();
Output:
[7.4.2009 17:03:04] - Executing command QUERY with text ='SET NAMES utf8;SET character_set_results=NULL'
[7.4.2009 17:03:05] - Executing command QUERY with text ='DROP TABLE IF EXISTS bug43986'
[7.4.2009 17:03:05] - Executing command QUERY with text ='CREATE TABLE bug43986 (a time NOT NULL) ENGINE=MyISAM'
[7.4.2009 17:03:05] - Executing command QUERY with text ='INSERT INTO bug43986 VALUES ('24:00:00')'
[7.4.2009 17:03:05] - Executing command QUERY with text ='SELECT * FROM bug43986'
Column is:a
Time is:1.00:00:00
[7 Apr 2009 15:08] Tonci Grgin
Server version: 5.1.31-log MySQL Community Server (GPL)

mysql> select * from bug43986;
+----------+
| a        |
+----------+
| 24:00:00 |
+----------+
1 row in set (0.00 sec)

mysql> select @@SQL_MODE;
+------------+
| @@SQL_MODE |
+------------+
|            |
+------------+

and

mysql> set @@SQL_MODE="strict_trans_tables";
Query OK, 0 rows affected (0.00 sec)

mysql> select @@SQL_MODE;
+---------------------+
| @@SQL_MODE          |
+---------------------+
| STRICT_TRANS_TABLES |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from bug43986;
+----------+
| a        |
+----------+
| 24:00:00 |
+----------+
[7 Apr 2009 15:23] Jorge Bastos
Cool,
I see that you were able to reproduce it!
How can i track this on the SVN changes?
[7 Apr 2009 16:41] Reggie Burnett
This is not really a bug.  I can see where the reporter would treat it as a bug though.  The issue is that we return time values using the TimeSpan structure.  This structure automatically returns hours > 24 as a multiple of days. However, you can still retrieve the total hours using the .TotalHours property.  So, if you want what appears in mysql for example, you could write this:

TimeSpan ts = (TimeSpan)reader["a"];
string s = String.Format("{0}:{1}:{2}", ts.TotalHours, ts.Minutes, ts.Seconds);

To change this behavior we would need to return a custom MySqlTime object.  We might consider doing this in 6.1.
[7 Apr 2009 17:19] Tonci Grgin
Thank you Reggie.
[7 Apr 2009 18:08] Jorge Bastos
Hi Reggie,
For this, why then a DATE or DATETIME field can be retrieved in, i can say, the "normal" way, and a TIME field cannot?
[7 Apr 2009 18:12] Reggie Burnett
Because a date or datetime field is retrieved into a DateTime structure or a MySqlDateTime object.  A time field is retrieved into a TimeSpan structure.
[7 Apr 2009 19:40] Jorge Bastos
Ok Reggie,
In 6.1x can we expect it to work then?

as a workarround for now i can do a:

select concat(a,'') as b from tblname;
[7 Apr 2009 19:48] Reggie Burnett
I will create a worklog to implement this in 6.1
[7 Apr 2009 19:57] Jorge Bastos
Thanks Reggie,
I'll stay tunned.
Jorge,
[8 Sep 2009 20:03] Jorge Bastos
Hi there Reggie,

Driver's on 6.1x already, when will be possible to take care of this matter?