MySQL Bugs: #102302: getObject returns LocalDateTime instead of Timestamp for DATETIME columns

Bug #102302	getObject returns LocalDateTime instead of Timestamp for DATETIME columns
Submitted:	20 Jan 2021 0:44	Modified:	20 Jan 2021 19:29
Reporter:	Andre Burgoyne	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	8.0.23	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Calling ResultSet.getObject(int colnum) returns a java.time.LocalDateTime object instead of the expected java.sql.Timestamp if the column is a DATETIME column.

This behavior changed with version 8.0.23

The output of the program below with the two different versions:

MySQL Connector/J version mysql-connector-java-8.0.22 (Revision: d64b664fa93e81296a377de031b8123a67e6def2)
DATE column 1 is expected type java.sql.Date
DATETIME column 2 is expected type java.sql.Timestamp
TIMESTAMP column 3 is expected type java.sql.Timestamp

MySQL Connector/J version mysql-connector-java-8.0.23 (Revision: 18bbd5e68195d0da083cbd5bd0d05d76320df7cd)
DATE column 1 is expected type java.sql.Date
ERROR: DATETIME column 2 is NOT expected type java.sql.Timestamp, it is java.time.LocalDateTime
TIMESTAMP column 3 is expected type java.sql.Timestamp

java version "1.8.0_241"
Java(TM) SE Runtime Environment (build 1.8.0_241-b07)
Java HotSpot(TM) 64-Bit Server VM (build 25.241-b07, mixed mode)

How to repeat:
import java.sql.*;

public class TimeBug {
    private static int executeUpdate(final Connection conn, final String sql) throws SQLException {
        try (final Statement st = conn.createStatement()) {
            return st.executeUpdate(sql);
        }
    }

    public static void main(final String[] args) throws Exception {
        Class.forName("com.mysql.cj.jdbc.Driver");
        try (final Connection conn = DriverManager.getConnection(args[0])) {
            final DatabaseMetaData dmd = conn.getMetaData();
            System.out.println(dmd.getDriverName() + " version " + dmd.getDriverVersion());
            executeUpdate(conn, "DROP TABLE IF EXISTS TimeBug");
            executeUpdate(conn, "CREATE TABLE TimeBug (dcol date NOT NULL, dtcol datetime NOT NULL, "
                + "ts timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)");
            executeUpdate(conn, "INSERT INTO TimeBug (dcol, dtcol) VALUES (now(), now())");
            try (final Statement st = conn.createStatement()) {
                final ResultSet rs = st.executeQuery("SELECT * FROM TimeBug");
                if (! rs.next()) throw new Error("BUG");
                final ResultSetMetaData rsm = rs.getMetaData();
                for (int colnum = 1; colnum <= rsm.getColumnCount(); colnum++) {
                    final Object ob     = rs.getObject(colnum);
                    final String cname  = ob.getClass().getName();
                    final String ctype  = rsm.getColumnTypeName(colnum);
                    final String ecname = rsm.getColumnClassName(colnum);
                    if (cname.equals(ecname)) {
                        System.out.println(ctype + " column " + colnum + " is expected type " + ecname);
                    } else {
                        System.out.println("ERROR: " + ctype + " column " + colnum + " is NOT expected type " + ecname
                            + ", it is " + cname);
                    }
                }
            }
        }
    }
}

Hello Andre,

Thank you for the report and test case.

regards,
Umesh

-- C/J 8.0.22

ant -f C:\\Work\\MySQLJava\\TimeBug -Dnb.internal.action.name=run run
init:
deps-jar:
Created dir: C:\Work\MySQLJava\TimeBug\build
Updating property file: C:\Work\MySQLJava\TimeBug\build\built-jar.properties
Created dir: C:\Work\MySQLJava\TimeBug\build\classes
Created dir: C:\Work\MySQLJava\TimeBug\build\empty
Created dir: C:\Work\MySQLJava\TimeBug\build\generated-sources\ap-source-output
Compiling 1 source file to C:\Work\MySQLJava\TimeBug\build\classes
compile:
run:
MySQL Connector/J version mysql-connector-java-8.0.22 (Revision: d64b664fa93e81296a377de031b8123a67e6def2)
DATE column 1 is expected type java.sql.Date
DATETIME column 2 is expected type java.sql.Timestamp
TIMESTAMP column 3 is expected type java.sql.Timestamp
BUILD SUCCESSFUL (total time: 3 seconds)

-- C/J 8.0.23

ant -f C:\\Work\\MySQLJava\\TimeBug -Dnb.internal.action.name=run run
init:
Deleting: C:\Work\MySQLJava\TimeBug\build\built-jar.properties
deps-jar:
Updating property file: C:\Work\MySQLJava\TimeBug\build\built-jar.properties
compile:
run:
MySQL Connector/J version mysql-connector-java-8.0.23 (Revision: 18bbd5e68195d0da083cbd5bd0d05d76320df7cd)
DATE column 1 is expected type java.sql.Date
ERROR: DATETIME column 2 is NOT expected type java.sql.Timestamp, it is java.time.LocalDateTime
TIMESTAMP column 3 is expected type java.sql.Timestamp
BUILD SUCCESSFUL (total time: 2 seconds)

Hi Andre,

It's not a bug, but the intended change in c/J 8.0.23.

Please check:
 https://dev.mysql.com/doc/connector-j/8.0/en/connector-j-date-time.html
 https://insidemysql.com/support-for-date-time-types-in-connector-j-8-0/

But in that case the bug is that getColumnClassName is returning the wrong class name.   There is a mismatch between the class that getObject returns and what getColumnClassName returns.

Right, we missed that. Could you create a bug report for this issue?

Will do.

https://bugs.mysql.com/bug.php?id=102321

Bug#106300 is marked as a duplicate of this one.

Bug #111832 marked as duplicate of this one