| Bug #39734 | Serial converts to int32 as datatype in datatable-column | ||
|---|---|---|---|
| Submitted: | 29 Sep 2008 18:16 | Modified: | 2 Oct 2008 6:39 |
| Reporter: | jaap slootweg | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | Connector / NET | Severity: | S2 (Serious) |
| Version: | 5.2.3 | OS: | Windows (XP) |
| Assigned to: | CPU Architecture: | Any | |
[1 Oct 2008 6:44]
Tonci Grgin
Hi Jaap and thanks for your report. I will think about it a bit more but I don't see a bug here according to http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html: All arithmetic is done using signed BIGINT or DOUBLE values, so you should not use unsigned big integers larger than 9223372036854775807 (63 bits) except with bit functions! If you do that, some of the last digits in the result may be wrong because of rounding errors when converting a BIGINT value to a DOUBLE.
[1 Oct 2008 7:29]
jaap slootweg
Dear Tonci, Thank you for looking into it. Can I stress that this is not doing any math, one unsigned bigint is interpreted as uint64 the other as int32. I want to relate the two, and for that they need to be the same datatype in the dataset. Good luck! Jaap
[2 Oct 2008 6:39]
Tonci Grgin
Japp:
Can I stress that this is not doing any math, one unsigned bigint is interpreted as uint64 the other as int32. I want to relate the two, and for that they need to be the same datatype in the dataset."
If you take a look at debugger you'll see that even on Ln. 535 in datareader.cs type is correct (UINT64), but after ds.Load it isn't. Now, what could be different in your sample ("relate the two")? The difference is in AUTO_INCREMENT attribute. VS can not handle UNIT64 being autoincrement as MSSQL server doesn't support that. This is a common problem with MS tools, they only support what they have with no regards to the rest of the world.
This can be proven very easy, just put BIGINT filed only in test table like this:
CREATE TABLE bug39734 (TestID BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); and you'll see it also miraculously changing type to UINT32.
In .xsd you will see that DataType of TestID *is* System.UInt64 but that's only true as long as AutoIncrement is FALSE. Click on AutoIncrement property and you'll see description I wrote above.
Closing the report as this is not a bug in MySQL software.
Description: After creating a table with a primary key with type SERIAL the table is loaded into a dataset. Yes, it's empty; I'm interested in converting it into a xsd. The datatype is ok in the schema, but NOT in the dataset. It's reported as an int32 in stead of the expected uint64. Bigint works fine! See code in the 'repeat' section. How to repeat: Imports MySql.Data Module Module1 Sub Main() Console.WriteLine("Enter database") Dim db As String = Console.ReadLine Console.WriteLine("Enter user") Dim usr As String = Console.ReadLine Console.WriteLine("Enter password") Dim pw As String = Console.ReadLine Dim MyConnStr As String = "database=" & db & ";username=" & usr & ";password=" & pw Dim MyConn As MySqlClient.MySqlConnection = New MySqlClient.MySqlConnection(MyConnStr) Dim ToExecute As MySqlClient.MySqlCommand ToExecute = New MySqlClient.MySqlCommand("Create Table TestType (" & _ "TestID SERIAL PRIMARY KEY, " & _ "ABigInt BIGINT UNSIGNED);") Dim DS As DataSet = New DataSet MyConn.Open() ToExecute.Connection = MyConn ToExecute.ExecuteNonQuery() ToExecute.CommandText = "Select * from TestType;" Dim TableReader As Data.Common.DbDataReader = ToExecute.ExecuteReader() DS.Load(TableReader, LoadOption.OverwriteChanges, "TestTable") Console.WriteLine("Datatype TestID: " & DS.Tables("TestTable").Columns("TestID").DataType.ToString) Console.WriteLine("Datatype ABgInt: " & DS.Tables("TestTable").Columns("ABigInt").DataType.ToString) ToExecute.CommandText = "Drop Table TestType;" ToExecute.ExecuteNonQuery() Console.WriteLine("Press enter to close") Console.ReadLine() End Sub End Module Suggested fix: I hope you know! ;-)