Bug #10390 SELECT MAX(a_datetime_field) returns a String rather than a Timestamp

Submitted: 5 May 2005 14:56 Modified: 20 Jun 2005 7:07
Reporter: Richard Wood Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:MySQL Server 4.1 OS:Windows (Window XP)
Assigned to: Assigned Account CPU Architecture:Any

[5 May 2005 14:56] Richard Wood
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
[20 May 2005 7:07] Vasily Kishkin
I tried to repeat the bug but got right results for each request:

Class: java.sql.Timestamp
Class: java.sql.Timestamp
Class: java.sql.Timestamp

Maybe I created the table with other definition. Could you please  write here definition of your table.
I attached my test case to "Files".
[20 May 2005 7:08] Vasily Kishkin
Test case

Attachment: test.java (text/plain), 1.37 KiB.

[20 Jun 2005 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.051 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.