| Bug #25894 | J/Connector metadata incorrect/inconsistent for mediumint/int unsigned | ||
|---|---|---|---|
| Submitted: | 27 Jan 2007 16:09 | Modified: | 28 May 2009 18:19 |
| Reporter: | David Gaskin | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / J | Severity: | S2 (Serious) |
| Version: | 5.0.4 | OS: | Any (All) |
| Assigned to: | Mark Matthews | CPU Architecture: | Any |
[1 Feb 2007 16:27]
Tonci Grgin
Hi David. I am unable to repeat your problems with the latest sources of c/J on JDK 1.5.0_10 on WinXP Pro SP2 running MySQL server 5.0.34BK. Can you provide output from my test case? Test case output: 1 .fld: tinyInt_type, MD: java.lang.Integer TINYINT , getObj: class java.lang.Integer 2 .fld: tinyIntU_type, MD: java.lang.Integer TINYINT UNSIGNED , getObj: class java.lang.Integer 3 .fld: smallInt_type, MD: java.lang.Integer SMALLINT , getObj: class java.lang.Integer 4 .fld: smallIntU_type, MD: java.lang.Integer SMALLINT UNSIGNED , getObj: class java.lang.Integer 5 .fld: mediumInt_type, MD: java.lang.Integer MEDIUMINT , getObj: class java.lang.Integer 6 .fld: mediumIntU_type, MD: java.lang.Integer MEDIUMINT UNSIGNED , getObj: class java.lang.Integer 7 .fld: int_type, MD: java.lang.Integer INTEGER , getObj: class java.lang.Integer 8 .fld: intU_type, MD: java.lang.Long INTEGER UNSIGNED , getObj: class java.lang.Long 9 .fld: bigInt_type, MD: java.lang.Long BIGINT , getObj: class java.lang.Long 10 .fld: bigIntU_type, MD: java.math.BigInteger BIGINT UNSIGNED , getObj: class java.math.BigInteger
[1 Feb 2007 16:28]
Tonci Grgin
Test case
Attachment: TestBug25894.java (text/x-java), 1.58 KiB.
[2 Feb 2007 10:43]
David Gaskin
Hi Tonci,
I specifically wrote
java.sql.Types.INTEGER
which is a java int constant (with a value of 4).
Try extending the following statement in your tests case:
System.out.println(i + " .fld: " + tblMD.getColumnName(i) + ", MD: " +
tblMD.getColumnClassName(i) + " " + tblMD.getColumnTypeName(i) + " " +
", getObj: " + this.rs.getObject(i).getClass());
with
tblMD.getColumnType(i)
i.e the full statement:
System.out.println(i + " .fld: " + tblMD.getColumnName(i) + ", MD: " +
tblMD.getColumnClassName(i) + " " + tblMD.getColumnTypeName(i) + " " +
tblMD.getColumnTypeName(i) + " " + // THE EXTENSIOM
", getObj: " + this.rs.getObject(i).getClass());
Do you actually write code like:
if (tlbMD.getColumnTypeName(i).equals("INTEGER")) {
.....
}
or do you write
if (tlbMD.getColumnType(i) == java.sql.INTEGER) {
.....
}
The bug I reported was against the result returned from
tlbMD.getColumnType(i)
and I used the term java.sql.Types.INTEGER
to make that (I thaught) clear.
ADDITIONAL POINT:
"http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-type-conversions.html"
MEDIUMINT[(M)] [UNSIGNED] MEDIUMINT [UNSIGNED] java.lang.Integer, if UNSIGNED java.lang.Long
does not seem to agree with your test case!!!
Hope the above helps.
Regards
Dave
[2 Feb 2007 10:51]
David Gaskin
Hi Tonci,
PLEASE DELETE THE PREVIOUS COMMENT IT HAS AN ERROR IN IT:
THE REPLACEMENT FOLLOWS
SORRY ABOUT THAT:
I specifically wrote
java.sql.Types.INTEGER
which is a java int constant (with a value of 4).
Try extending the following statement in your tests case:
System.out.println(i + " .fld: " + tblMD.getColumnName(i) + ", MD: " +
tblMD.getColumnClassName(i) + " " + tblMD.getColumnTypeName(i) + " " +
", getObj: " + this.rs.getObject(i).getClass());
with
tblMD.getColumnType(i)
i.e the full statement:
System.out.println(i + " .fld: " + tblMD.getColumnName(i) + ", MD: " +
tblMD.getColumnClassName(i) + " " + tblMD.getColumnTypeName(i) + " " +
tblMD.getColumnType(i) + " " + // THE EXTENSIOM Now as it should be
", getObj: " + this.rs.getObject(i).getClass());
Do you actually write code like:
if (tlbMD.getColumnTypeName(i).equals("INTEGER")) {
.....
}
or do you write
if (tlbMD.getColumnType(i) == java.sql.INTEGER) {
.....
}
The bug I reported was against the result returned from
tlbMD.getColumnType(i)
and I used the term java.sql.Types.INTEGER
to make that (I thaught) clear.
ADDITIONAL POINT:
"http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-type-conversions.html"
MEDIUMINT[(M)] [UNSIGNED] MEDIUMINT [UNSIGNED] java.lang.Integer, if UNSIGNED java.lang.Long
does not seem to agree with your test case!!!
Hope the above helps.
Regards
Dave
[2 Feb 2007 12:34]
Tonci Grgin
Hi David and thanks for clarification. Indeed we have two bugs here: - INT unsigned, tblMD.getColumnType(i) returns 4 which is obviously wrong... - And error in code/error in docs regarding MEDIUMINT Thanks again for your report.
[2 Feb 2007 18:29]
David Gaskin
Hi Tonci, OK we are now talking about the same objects and functions. I suggest that the implementation for TINYINT and SMALLINT is at least inexact if not incorrect. TINYINT ======= The Sun JDBC documentation states: =================== start of quote ====================== 8.3.4 TINYINT The JDBC type TINYINT represents an 8-bit integer value between 0 and 255 that may be signed or unsigned. The corresponding SQL type, TINYINT, is currently supported by only a subset of the major databases. Portable code may therefore prefer to use the JDBC SMALLINT type, which is widely supported. The recommended Java mapping for the JDBC TINYINT type is as either a Java byte or a Java short. The 8-bit Java byte type represents a signed value from -128 to 127, so it may not always be appropriate for larger TINYINT values, whereas the 16-bit Java short will always be able to hold all TINYINT values. =================== end of quote ====================== Is there any good reason for NOT implementing the Sun Recommendations? i.e. returning TINYINT columns as a java.lang.Byte and TINYINT UNSIGNED columns as a java.lang.Short Similarly with SMALLINT (java.lang.Short when signed) Regards Dave
[2 Feb 2007 20:14]
Mark Matthews
I took a look at the JDBC-3.0 specification, and it seems there's a disconnect with section "8.3.4" of the "Getting Started guide", so the answer to your question "Is there any good reason for NOT implementing the Sun Recommendations?" is that we implemented to the JDBC specification not the "Getting started Guide", Take a look at table B-3 on page 179 of the JDBC-3.0 Specification: "TABLE B-3 JDBC Types Mapped to Java Object Types ResultSet.getObject and CallableStatement.getObject use the mapping shown in this table for standard mappings." If you work through the table, the driver is returning the correct class for ResultSet.getObject(): (abridged) JDBC Type Java Type ---------------------- TINYINT Integer SMALLINT Integer INTEGER Integer BIGINT Long So as far as ResultSet.getObject() goes, it's doing the correct thing as far as the _specification_ is concerned. Our implementation is using the mapping given in Table B-3, ResultSetMetaData.getColumnClassName() is returning class names that correlate with what ResultSet.getObject() is returning, and in the case where a column is unsigned and the range of the unsigned type exceeds what's specified above, the driver picks the next "larger" integral type, as there is no treatment of "unsigned" integral types in the JDBC specification itself. Therefore the type mapping, as far as I can tell is correct (even though the JDBC specification itself seems inconsistent, as these are not the same mappings used for PreparedStatement.setObject(), for instance, and they're not the ones spelled out in section "8.3.4"). The question then at hand, is what _should_ ResultSetMetaData.getColumnType() return, I'm guessing. As implemented now, it returns the "direct" mapping between a SQL Type and java.sql.Types, and the driver assumes that a client could deduce the actual signedness (and thus required storage size) by calling ResultSetMetaData.isSigned(int), or revert to calling ResultSet.getObject() or ResultSetMetaData.getColumnClassName() to determine what _actual_ class the driver would return. I'm going to ask the spec lead for JDBC-4.0 what the behavior for ResultSetMetadata.getColumnType() should be, because I can't find a reference to it in the JDBC specifications themselves, and the APIDOCs are kind of terse: "Retrieves the designated column's SQL type."
[10 Apr 2007 0:04]
Jeffrey Blake
This problem seems to be what I am experiencing after shifting from 3.1.10 to 5.0.5 Connector/J. My Unsigned Mediumint columns are being return from ResultSet.getObject(..) as an Integer instead of a Long as specified in the Connector/J documentation. Does this mean that if the stored number gets large enough to require the use of the sign bit - then getObject will return a Long at that time to accommodate the bigger number?
[16 May 2007 10:05]
Tonci Grgin
Mark, the bug is still present in latest 5.0 sources. Any news?
[16 May 2007 13:02]
Mark Matthews
Tonci, To which bug do you refer? The first posting of this issue is not a bug, it's Sun not being consistent between their specification and their "getting started" guide. The last user comment to this bug is a different bug (if it exists), they're different types, after all.
[16 May 2007 13:08]
Mark Matthews
The case of UNSIGNED MEDIUMINT mapping to java.lang.Long appears to be a documentation bug, as we changed this behavior in 3.1 somewhere, because the maximum value of an UNSIGNED MEDIUMINT is still within the range of a java.lang.Integer. The error is in the second table at http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-type-conversions.html
[31 Dec 2007 18:00]
MC Brown
I've updated the documentation and the table in question. I've made it clear that in 3.1 the return type was different. Setting back to Mark for further investigation.
[28 May 2009 18:19]
Mark Matthews
The behavior change was documented.
Description: The java type returned from a getObject(columnName) on a ResultSet should be the same type as reported by the metadata of the result set. This is not the case with at least the following type: . integer unsigned returns a java.lang.Long but meta data reports it as java.sql.Types.INTEGER!!! Additionally mediumint unsigned returns a java.lang.Long (Why? a java.lang.Integer is enough), meta data reports a java.sql.BIGINT which is consistant at least. How to repeat: create table UnsignedTest ( tinyint1 tinyint, tinyUnsignedint1 tinyint unsigned, smallInt1 smallint, smallUnsignedInt1 smallint unsigned, mediumInt1 mediumint, mediumUnsignedInt1 mediumint unsigned, integer1 integer, unsignedInt1 integer unsigned, bigInt1 bigint, bigUnsignedInt1 bigint unsigned ); and list the the table meta data with J/Connector Suggested fix: Meta data must NOT report a shorter type than that what is returned by getObject. As an example where I encountered the problem. An IPV4 address as a "integer unsigned" and using Apache DdlUtils to unload data. All IP addresses beyond 127.255.255.255 are reported as out of range because they do not fit in a java.sql.INTEGER.