MySQL Bugs: #4323: getObject typing breaks in group by with timestamp and date manipulation

Bug #4323	getObject typing breaks in group by with timestamp and date manipulation
Submitted:	29 Jun 2004 13:16	Modified:	30 Jun 2004 22:54
Reporter:	Paolo Campanella	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	3.0.14 and below	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:

Hi

getObject() calls incorrectly return timestamps as a String when group and date_add are both used.

Not even SQL casting can correct the return type.

I'm also using MySQL 4.0.15.

Thanks

Paolo

How to repeat:

Here's a reproducible code portion (just need to set your con variable, and have create table privileges):

try {
	Statement cstmt = con.createStatement();
	cstmt.executeUpdate("create table timestampbug(created_time timestamp)");
	// NB - table must have at least 2 rows for bug to show up):
	cstmt.executeUpdate("insert into timestampbug values(now())");
	cstmt.executeUpdate("insert into timestampbug values(now())");
	ResultSet rs = cstmt.executeQuery("select created_time as x from timestampbug");
	if (rs.next()) System.out.println("Class is " + rs.getObject("x").getClass());

	cstmt = con.createStatement();
	String query = "select date_add(created_time, interval 1 hour) as x from timestampbug";
	query += " group by created_time";
	rs = cstmt.executeQuery(query);
	if (rs.next()) System.out.println("Class is " + rs.getObject("x").getClass());
	cstmt.executeUpdate("drop table timestampbug");
}
catch (Exception e) { e.printStackTrace(); }

// Output is:
// Class is class java.sql.Timestamp
// Class is class java.lang.String

Suggested fix:
none.

This is not a Connector/J bug, but a server bug (it's changing the return type of the operands when a 'group by' is issued), so I'm changing the category.

Hi again

Typing is also broken when GROUPing by on a SUMmed INT column (outputs a floating point number). I'm not adding this as a separate bug because I suspect that there are many related typing issues with various column types in aggregate functions.

Ho to reproduce:

try {
	Statement cstmt = con.createStatement();
	cstmt.executeUpdate("create table intbug(x int, y int)");
	cstmt.executeUpdate("insert into intbug values(1,1)");
	cstmt.executeUpdate("insert into intbug values(1,1)");
	ResultSet rs = cstmt.executeQuery("select x from intbug");
	if (rs.next()) System.out.println("Class is " + rs.getObject("x").getClass());

	cstmt = con.createStatement();
	String query = "select sum(x) as x from intbug";
	query += " group by y";
	rs = cstmt.executeQuery(query);
	if (rs.next()) System.out.println("Class is " + rs.getObject("x").getClass());
	cstmt.executeUpdate("drop table intbug");
}
catch (Exception e) { e.printStackTrace(); }

Output is:
Class is class java.lang.Integer
Class is class java.lang.Double

Verified against 4.0.21.  It is reporting FIELD_TYPE_STRING with the combination of DATE_ADD() and the GROUP BY.

Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

see http://bugs.mysql.com/bug.php?id=4176