Description:
A JDBC query with a GROUP BY clause that SELECTs MAX or MIN on a MySQL datetime field returns a String rather than the expected Timestamp. A JDBC query with NO GROUP BY clause that SELECTs MAX or MIN on a MySQL datetime field returns the expected Timestamp.
How to repeat:
Using Java 1.4.2 and mysql-connector-java-3.1.8a or 3.0.15, Query a MySql table "sometable" containing a datetime "datetime1" field and some other field "otherfield" to which a group by clause can be applied, to return a result set.
Perform the following Java JDBC query:
String sql = "SELECT datetime1 FROM sometable";
ResultSet rs = mystatement.executeQuery(sql);
Check the object type of the column returned for the datetime field:
rs.next();
String class = rs.getObject("datetime1").getClass().getName();
class = "Timestamp" as expected
Now add a MAX aggregate to the query
sql = "SELECT MAX(datetime1) FROM sometable";
rs = mystatement.executeQuery(sql);
rs.next();
class = rs.getObject("datetime1").getClass().getName();
class = "Timestamp" as expected
Now add a GROUP BY clause to the query
sql = "SELECT MAX(datetime1) FROM sometable GROUP BY otherfield";
rs = mystatement.executeQuery(sql);
rs.next();
class = rs.getObject("datetime1").getClass().getName();
class = "String", NOT "Timestamp"
Suggested fix:
The statement with the group by clause should return a Timestamp, NOT a String
Description: A JDBC query with a GROUP BY clause that SELECTs MAX or MIN on a MySQL datetime field returns a String rather than the expected Timestamp. A JDBC query with NO GROUP BY clause that SELECTs MAX or MIN on a MySQL datetime field returns the expected Timestamp. How to repeat: Using Java 1.4.2 and mysql-connector-java-3.1.8a or 3.0.15, Query a MySql table "sometable" containing a datetime "datetime1" field and some other field "otherfield" to which a group by clause can be applied, to return a result set. Perform the following Java JDBC query: String sql = "SELECT datetime1 FROM sometable"; ResultSet rs = mystatement.executeQuery(sql); Check the object type of the column returned for the datetime field: rs.next(); String class = rs.getObject("datetime1").getClass().getName(); class = "Timestamp" as expected Now add a MAX aggregate to the query sql = "SELECT MAX(datetime1) FROM sometable"; rs = mystatement.executeQuery(sql); rs.next(); class = rs.getObject("datetime1").getClass().getName(); class = "Timestamp" as expected Now add a GROUP BY clause to the query sql = "SELECT MAX(datetime1) FROM sometable GROUP BY otherfield"; rs = mystatement.executeQuery(sql); rs.next(); class = rs.getObject("datetime1").getClass().getName(); class = "String", NOT "Timestamp" Suggested fix: The statement with the group by clause should return a Timestamp, NOT a String