MySQL Bugs: #8875: Connector not recognizing TINYINT UNSIGNED columns

Bug #8875	Connector not recognizing TINYINT UNSIGNED columns
Submitted:	1 Mar 2005 21:56	Modified:	1 Jul 2005 17:00
Reporter:	Matthew Schultz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S1 (Critical)
Version:	3.1.7	OS:	Linux (Linux 2.4.22)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
J Connector throws error when attempting to query an unsigned tinyint between the values 128 to 255 (the unsigned range)

Throws error:  '197' in column '1' is outside valid range for the datatype TINYINT
(Replace 197 with data in column and replace 1 with the number column from the select query)

This is my connection string: jdbc:mysql://localhost/someDatabaseName?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull

How to repeat:
Make a column unsigned tinyint.  Create a row and set the value to that column to a number between 128 and 255.  Do a simple select query on that column.

Suggested fix:
Should be able to query columns using the unsigned range of tinyint.

The bug fix for this will take a while, because we have to touch all of the accessors for numerical values to check for UNSIGNED flags in the truncation check.

There is currently a workaround though, if you configure the JDBC driver with the URL property "jdbcCompliantTruncation=false", you won't get this exception.

That's true but the workaround only suppress an exception, the fuction still returns a wrong value. For example, -16 is returned instead of 240. It can be temporarily solved on an application layer, though.

What result set accessor are you using to go after the value? ResultSet.getShort(), or ResultSet.getInt(), or ResultSet.getObject()?

This problem seems to be fixed in 3.2.0-alpha, which is older than 3.1.10! Was this the result of a real fix, or something else?

BTW, in 3.1.10, the problem is slightly different. Instead of wrapping results higher than 128 to -127, it actually starts counting 256 higher than it should, until it reaches 128. For instance, a value of '0' would be returned as '256', '1' as '257', etc. until '128'. After 128, the value is correct.

Mark: I use ResultSet.getInt() method.

This is fixed in 3.1.11, the bugfix for 3.1.9/.10 was erroneous.

You can grab a nightly snapshot with the fix from http://downloads.mysql.com/snapshots.php#connector-j

(from the CHANGES):

Fixed BUG#11552 - Server-side prepared statements return incorrect
	  values for unsigned TINYINT, SMALLINT, INT and Long.

This bug bit me too, I was using 3.1.10, and getInt() was returning 65537, while getString() was returning (correctly) 1. Moving to the nightly build fixed the problem.