MySQL Bugs: #52007: MySQL ODBC connector returns the wrong datatype for unsigned integers

Bug #52007	MySQL ODBC connector returns the wrong datatype for unsigned integers
Submitted:	12 Mar 2010 17:36	Modified:	22 Mar 2010 13:22
Reporter:	Ed Macauley	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.6 and 3.51.27	OS:	Any (Windows Xp, Centos, Redhat and others)
Assigned to:		CPU Architecture:	Any
Tags:	ODBC Connector, UNSIGNED

Description:
The MySQL ODBC connector returns the wrong datatype for unsigned integers, it instead returns them as long integers.  Under .NET on Windows, this causes exceptions.  On Linux platforms using unixODBC, this causes data truncation in precisely the same way as Bug# 32171.  Analysis of ODBC trace logs indicates that the MySQL ODBC provider is also returning unsigned INTs as LONGs under Linux.

Will attach a sample schema, unixODBC trace, and a sample app in C# I used to reproduce the problem under Windows and can provide a C for Linux if necessary.

How to repeat:
Create a table with an unsigned integer and use the MySQL ODBC provider to get data from it.

Suggested fix:
Return an unsigned integer instead of a long.

Forgot to mention, work around under Linux is to cast as unsigned.  i.e.; "SELECT CAST(someuint AS unsigned) FROM sometable"  MySQL returns this as a DECIMAL type, so there's no data truncation.

C# example, using 6.2.2 of the .NET provider

Attachment: Program.cs (text/plain), 1.83 KiB.

sample schema

Attachment: test-schema.sql (application/octet-stream, text), 2.04 KiB.

unixODBC trace with a CAST to UNSIGNED, this does not truncate data

Attachment: trace with cast.log (application/octet-stream, text), 7.06 KiB.

unixODBC trace with no CAST, data is truncated

Attachment: trace no cast.log (application/octet-stream, text), 7.26 KiB.

Hi Ed and thanks for your report.

I am a bit confused so I need more info from you...
First of all, .NET framework, does *not* support unsigned types! It is the way MS engineered it so I do not see problem there. Please check on .NET specs. Next, using ODBC bridge with .NET is not the recommended way to go. Please use c/NET for this.
Then we have UnixODBC problem... Did you tried test case Bob attached to Bug#32171? If not, please do. Bob reported his problems to be gone after the patch. Are you using latest version of UnixODBC? Did you tried iODBC?

The .NET framework does certainly support unsigned integers.  System.UInt32 is not CLS compliant, that's all.  Perfectly acceptable for a C# project.  That's completely irrelevant to this bug, however.

Let me be perfectly clear: The .NET code is only to provide a sample application.  Do you need a sample application in C as well?  I can build one, but it's going to be basically the same thing as the .NET app.

If unixODBC were actually the problem, I wouldn't be able to reproduce this on multiple platforms, (i.e.: Windows and Unix) no?

I am using v 2.2.14 of unixODBC and have tried both v 5.1.6 and 3.51.27 of the Mysql ODBC drivers.

Hi Ed.
i agree it doesn't look like platform/DM specific problem. thus i don't see  need to test against iodbc. tonci asked that because often problems on *nix are DM-specific.
According http://msdn.microsoft.com/en-us/library/ms716289(VS.85).aspx
"DataTypePtr[Output] Pointer to a buffer in which to return the SQL data type of the column. This value is read from the SQL_DESC_CONCISE_TYPE field of the IRD. This will be one of the values in SQL Data Types, or a driver-specific SQL data type. If the data type cannot be determined, the driver returns SQL_UNKNOWN_TYPE."
Types can be seen at http://msdn.microsoft.com/en-us/library/ms710150(VS.85).aspx
As you can see - no "unsigned" types there. According to the log your .Net test application binds to SQL_C_SLONG using some internal logic, and gets signed value in result. oh, you said it's unixodbc trace.
Anyway, everything looks correct so far for me. It's your turn to prove me wrong :)

So you're saying that the data truncation when dealing with unsigned ints is expected?

Hi Ed,
I'm saying it is expected when one binds it to the variable type that can't accommodate the whole value. According to your log c/odbc acted adequately. Unless  I missed something, and i think i did not.
You gave .NET example - we probably can't take it as a justification for the bug verification. Since we have native driver for it.

I'll see if I can get a pure C example to you.  I don't normally work in C so this will take some time.  All of our code here uses OTL (http://otl.sourceforge.net/), so I don't have anything handy that uses a straight ODBC implementation.

Just take into consideration that it's not a problem to write pure C example, that will result in data truncation. The log you provided us earlier can be taken as some representation of C code. But from the c/ODBC point of view there is nothing wrong in it.
I suspect that your problem is interaction between 3rd party software(OTL?) and c/odbc. And ODBC calls sequence that that 3rd party software generates behind the scene is erroneous. So bug is there, not in c/odbc.
but we will only be happy if you prove bug is ours - that will make c/odbc better.
p.s. as for the CAST workaround - it works because MySQL server, and then c/odbc report different from type for the column. and based on that information your application bind it differently and no truncation happens.

Closing as the standalone test case does not exhibit such behavior.