Bug #15117 FOUND_ROWS() does not work if getColumnDisplaySize() was called before

• Submitted: 21 Nov 2005 22:56
• Modified: 11 Nov 2009 2:36
• Reporter: Thomas Kellerer
• Status: Won't fix
• Category: Connector / J
• Severity: S3 (Non-critical)
• Version: 3.1.11
• OS: Windows (Windows 2000)
• Assigned to: Mark Matthews
• CPU Architecture: Any

[21 Nov 2005 22:56] Thomas Kellerer

Description:
When running a SELECT statement containing SQL_CALC_FOUND_ROWS and obtaining a column's size during reading of the ResultSet using ResultSetMetaData.getColumnDisplaySize() the result of a subsequent SELECT found_rows() will report an incorrect size

How to repeat:
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/dbname", "user", "password");

// Table person has three rows....
String sql = "SELECT SQL_CALC_FOUND_ROWS * FROM person LIMIT 1";
Statment stmt = con.createStatement();
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData metaData = rs.getMetaData();
int size = metaData.getColumnDisplaySize(1);

// of course in the real program, the ResultSet is used and processed
rs.close(); 
stmt.close(); 

stmt = con.createStatement();
sql = "SELECT FOUND_ROWS()";		
rs = stmt.executeQuery(sql);

// Should return 3 in my case, but does return 36
// if you comment out the call to getColumnDisplaySize() 
// the correct result is displayed
if (rs.next()) System.out.println("result=" + rs.getObject(1));

[22 Nov 2005 4:45] Mark Matthews

Not sure there's an easy work-around. Calling ResultSetMetaData.getColumnDisplaySize() requires a query to the database the first time a column with a given character set needs this information (and is then cached per-connection). This query clears out the value for FOUND_ROWS(). 

The workaround is to issue your query for FOUND_ROWS() before calling RSMD.getColumnDisplaySize(). You'll of course want to do this on a different Statement from the same connection so that the result set isn't implicitly closed by the driver.