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: | |
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
[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?