Bug #110285 When using a CachedRowSet with a DATETIME field an exception is thrown
Submitted: 6 Mar 2023 21:33 Modified: 7 Mar 2023 4:47
Reporter: Moses Lecce Email Updates:
Status: Analyzing Impact on me:
None 
Category:Connector / J Severity:S2 (Serious)
Version:8.0.32 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[6 Mar 2023 21:33] Moses Lecce
Description:
Starting with version 8.0.23 and present in the latest version 8.0.32, Using a CachedRowSet (com.sun.rowset.CachedRowSetImpl) and calling getTimestamp() or getDate() will cause the following exception to be thrown:

Exception in thread "main" java.lang.ClassCastException: class java.time.LocalDateTime cannot be cast to class java.sql.Timestamp (java.time.LocalDateTime is in module java.base of loader 'bootstrap'; java.sql.Timestamp is in module java.sql of loader 'platform')        at java.sql.rowset/com.sun.rowset.CachedRowSetImpl.getDate(CachedRowSetImpl.java:2156)        at java.sql.rowset/com.sun.rowset.CachedRowSetImpl.getDate(CachedRowSetImpl.java:2697)

Note: This problem does not occur when using ResultSet directly.

How to repeat:
Consider the following code:

String mysql_host = "your_db_hostname";
String mysql_username = "your_mysql_username";
String mysql_password = "your_mysql_password";

String jdbcUrlString = "jdbc:mysql://"+mysql_host+"/aberrant?user="+mysql_username+"&password="+mysql_password+"&default_time_zone='America/Toronto'&connectionTimeZone=LOCAL&preserveInstants=true";

MysqlDataSource ds = new MysqlDataSource();
ds.setURL(jdbcUrlString);

String tmpTableCreate = "CREATE TEMPORARY TABLE IF NOT EXISTS tmp_table (`my_datecol` datetime DEFAULT NULL,`my_dateonly` date DEFAULT NULL, `my_ts` TIMESTAMP DEFAULT CURRENT_TIMESTAMP);";
String tmpTablePoplulate = "insert into tmp_table (my_datecol,my_dateonly) values (?,?)";
String query = "select my_datecol,my_dateonly,my_ts from tmp_table";

try (Connection conn = ds.getConnection();) {
   Statement stmt = conn.createStatement();
   stmt.executeUpdate(tmpTableCreate);

   try (PreparedStatement ps = conn.prepareStatement(tmpTablePoplulate);) {
	ps.setTimestamp(1,new Timestamp(date.getTime()));
	ps.setDate(2, new java.sql.Date(date.getTime()));
	ps.execute();
   }

   try (PreparedStatement ps = conn.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);) {

       ps.execute();
       try (ResultSet rs = ps.getResultSet(); CachedRowSet crs = RowSetProvider.newFactory().createCachedRowSet();)
       {
            crs.populate(rs);
            if(crs.next())
            {
                Timestamp myTS = crs.getTimestamp("my_ts"); // this will throw an exception
            }
       }
   }
}

Suggested fix:
You can use crs.getObject("my_ts") instead of crs.getTimestamp() and there will not be a cast exception, though you will have an Object returned that you will need to cast or parse into the proper type
[6 Mar 2023 21:35] Moses Lecce
Added current version of Connector / J (though this issue began in 8.0.23)