MySQL Bugs: #82084: YEAR data type returns incorrect value for JDBC getColumnType()

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

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.

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.

Screenshot

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

Screenshot

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

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".

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.

Hello Lisa Cabrera,

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

Thanks,
Chiranjeevi.

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 :-(.

Thank you Joshua,

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

Posted by developer:
 
It's not a bug. According to the JDBC API getColumnType(int column) retrieves the designated column's SQL type while getColumnClassName(int column) returns the fully-qualified name of the Java class whose instances are manufactured if the method ResultSet.getObject is called to retrieve a value from the column.

With getColumnType(int column) Connector/J returns the original type of the column as it is returned by MySQL server. The "yearIsDateType" does not change this original type, instead it changes the default conversion from this type to the Object.

Please note also that there is no standard java.sql.Types for the YEAR type, java.sql.Types.DATE is used here because of the default conversion. But the consistent behaviour is to leave it unchanged because the server reply doesn't change. You could also use getColumnTypeName(int) method which gives the consistent "YEAR" value.

Regarding TIME column, I also didn't find anything wrong with the returned type while using c/J 8.0.28:

    @Test
    public void testBug82084() throws Exception {
        createTable("testBug82084", "(col_int int, col_year year, col_time TIME(0))");
        this.stmt.executeUpdate("INSERT INTO testBug82084 VALUES (1,2006,'12:00:00')");
        Properties props = new Properties();
        boolean yearIsDate = false;
        do {
            props.setProperty(PropertyKey.yearIsDateType.getKeyName(), "" + yearIsDate);
            System.out.println("---- yearIsDateType=" + yearIsDate + " ----");
            Connection con = getConnectionWithProps(props);
            this.rs = con.createStatement().executeQuery("select * from testBug82084");
            ResultSetMetaData rsmd = this.rs.getMetaData();
            for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                System.out.println(rsmd.getColumnName(i)  //
                        + " | " + rsmd.getColumnType(i) + " | " //
                        + rsmd.getColumnTypeName(i) + " | " //
                        + rsmd.getColumnClassName(i));
            }
        } while (yearIsDate = !yearIsDate);
    }

Output:

---- yearIsDateType=false ----
col_int | 4 | INT | java.lang.Integer
col_year | 91 | YEAR | java.lang.Short
col_time | 92 | TIME | java.sql.Time
---- yearIsDateType=true ----
col_int | 4 | INT | java.lang.Integer
col_year | 91 | YEAR | java.sql.Date
col_time | 92 | TIME | java.sql.Time

Posted by developer:
 
Re-opened after internal discussion. We decided to fix it as requested.

Posted by developer:
 
Added the following entry to the Connector/J 8.0.29 changelog:

"ResultSetMetaData and DatabaseMetaData returned Types.DATE for a YEAR table column even when yearIsDateType=false. With this fix, Types.SMALLINT was returned correctly in the situation. "