Bug #82084 YEAR data type returns incorrect value for JDBC getColumnType()
Submitted: 1 Jul 2016 16:05 Modified: 5 Jul 2016 14:26
Reporter: Lisa Cabrera Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / J Severity:S3 (Non-critical)
Version:5.1.39 OS:Any
Assigned to: CPU Architecture:Any

[1 Jul 2016 16:05] Lisa Cabrera
Description:
When calling ResultSetMetaData> getColumnType(i) on a YEAR data type column, the driver returns "91" even though ResultSetMetaData> getColumnClassName() correctly returns "java.lang.Short".

According to MySQL documentation - "If yearIsDateType configuration property is set to false, then the returned object type is java.sql.Short. If set to true (the default), then the returned object is of type java.sql.Date with the date set to January 1st, at midnight."

http://dev.mysql.com/doc/connector-j/5.1/en/connector-j-reference-type-conversions.html

When this flag is set to 'false' the above behavior is seen.

How to repeat:
1 - Create a table with MySQL YEAR data type
2 - Retrieve metadata for table, with JDBC URL config property 'yearIsDateType=false'
3 - Observe the values returned for getColumnType(i) and getColumnClassName(i). They are inconsistent.

Suggested fix:
Fix the columnType value to SMALLINT when when the class type is java.sql.Short.
[4 Jul 2016 11:46] Chiranjeevi Battula
Hello Lisa Cabrera,

Thank you for the bug report.
I tried to reproduce the issue at my end using MySQL Connector / J 5.1.39 but not seeing any issues and it is working as document.
Could you please provide repeatable test case (create table statements, MySQL version, sample test case, etc. - please make it as private if you prefer) to confirm this issue at our end?

Thanks,
Chiranjeevi.
[4 Jul 2016 11:47] Chiranjeevi Battula
Screenshot

Attachment: 82084.JPG (image/jpeg, text), 306.13 KiB.

[4 Jul 2016 11:48] Chiranjeevi Battula
Screenshot

Attachment: 82084_yearIsDateType_false.JPG (image/jpeg, text), 305.15 KiB.

[5 Jul 2016 13:49] Lisa Cabrera
Hi Chiranjeevi,

I used the following DDL to create the table:

create table testTable (col_int int, col2_year year)
insert into testTable values (1,2006)

Query used to retrieve data : select col2_year from testTable

Behavior is seen using the GA release of the connector (v 5.1.39) against MySQL 5.6.21-enterprise-commercial-advanced (5.6) server.

Method getColumnType(i) is returning "91" instead of "5".
[5 Jul 2016 13:59] Lisa Cabrera
Based on the screen shots attached, you reproduced the issue. When the object classType is "java.lang.Short" it should be returning SMALLINT ("5") not DATE ("91") as the column type. "91" represents "DATE" column type, not "SMALLINT". That is the issue I'm reporting.
[5 Jul 2016 14:25] Chiranjeevi Battula
Hello Lisa Cabrera,

Thank you for your feedback.
Verified this behavior on MySQL Connector / J 5.1.39.

Thanks,
Chiranjeevi.
[21 Sep 2016 6:51] Joshua Klein
I met the same problem with TIME(0) fields. They are consistently identified as VARCHAR "12" by the getColumnType and getColumnTypeName routines. I properly get other fields categorization, VARCHAR, DATE, DECIMAL and INT. I used to work with version 5.1.23 but having moved to 5.1.39 did not help.
I will try to work out a compact example ASAP: I am late on schedule because this bug :-(.
[21 Sep 2016 9:28] Filipe Silva
Thank you Joshua,

When building this example please describe what you are observing and what do you expect.