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 4:43	Modified:	10 Jan 2006 18:40
Reporter:	Lee Clemens	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:		OS:
Assigned to:	Mark Matthews	CPU Architecture:	Any

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.