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

[2 Nov 2005 4:43] Lee Clemens
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
[2 Nov 2005 9:07] Vasily Kishkin
Thanks for the bug report. I was able to reproduce the bug. MEDIUMINT is returned as java.lang.Long. My test case is attached.
[2 Nov 2005 9:08] Vasily Kishkin
Test case

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

[2 Nov 2005 12:55] Mark Matthews
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".
[2 Nov 2005 15:08] Lee Clemens
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
[2 Nov 2005 15:24] Mark Matthews
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.
[28 Nov 2005 18:34] Mark Matthews
Checked behavior in driver, just need to fix docs now.
[10 Jan 2006 18:40] Paul Dubois
Mark indicates that he's updated the docs.