MySQL Bugs: #14562: Java type conversion may be incorrect for mediumint

Bug #14562	Java type conversion may be incorrect for mediumint
Submitted:	2 Nov 2005 5:43	Modified:	10 Jan 2006 19:40
Reporter:	Lee Clemens
Status:	Closed
Category:	Connector/J	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:	Mark Matthews	Target Version:

Description:
I think I may have found a discrepency with the Java type conversions. I am using an
InnoDB database with MySQL 4.1.14, Connector/J version 3.1.11 and j2sdk1.4.2_08.

When I use ResultSet.getObject to get MEDIUMINT(5) UNSIGNED 
[ZEROFILL], it seems as though I am getting an instanceof Long...not Integer. Any advice
would be appreciated if it is I who is in error, but it really doesn't appear to be an
Integer in the DB.

How to repeat:
CREATE TABLE bug_list (  id MEDIUMINT(5) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
                         title VARCHAR(50) NOT NULL,
                         PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import com.mysql.jdbc.Connection;
...
 try {
            stmt = (Statement) con.createStatement();
            rs = (ResultSet) stmt.executeQuery(qry);
            String col = <<columnName>>; //really an ArrayList of names, remove for-loop
for example
            while (rs.next()) {
                   HashMap hm.put(col, rs.getObject(col));
                }
                results.add(hm);
            }
...

.jsp
call method to get HashMap of query results
Object obj = hm.get("id");
        if (obj instanceof Long) {
            System.out.println("Its a Long");
        }

Suggested fix:
not sure

Thanks for the bug report. I was able to reproduce the bug. MEDIUMINT is returned as
java.lang.Long. My test case is attached.

Test case

Attachment: Bug14562.java (text/java), 935 bytes.

The issue is that you have it marked as "unsigned". There is no type conversion specified
for unsigned values or for MEDIUM INT in the JDBC specification.

MEDIUM INT gets mapped to a "java.lang.Integer" as that's the smallest integral type that
can hold the maximum value of the type. If a value of any numeric type is marked as
"unsigned" when retrieved from the server, it is automatically converted to the next
larger type of the same class (integral vs. floating point), in this case
"java.lang.Long".

Thank you very much for your prompt response and explanation, I was hoping it was
something as simple as that!  However, I am still slightly confused why
http://dev.mysql.com/doc/refman/5.0/en/cj-type-conversions.html says that it MySQL types
MEDIUMINT are returned as java.lang.Integer "(regardless if UNSIGNED or not)". Perhaps the
documentation is misleading, if an unsigned mediumint is always returned as the larger
type, java.lang.Long? Thanks again, Lee

Indeed, a documentation bug, since we started handling UNSIGNED values yet. I need to do
some thinking about whether to revert the unsigned handling for MEDIUMINT to match the
documentation, or make the documentation match the behavior of the driver.

Checked behavior in driver, just need to fix docs now.

Mark indicates that he's updated the docs.