Bug #25978 found_rows() returns different value on 4.x vs 5.x
Submitted: 31 Jan 2007 14:58 Modified: 24 Jun 2007 23:45
Reporter: Per Kleven Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version: 5.0.27 OS:Any (any)
Assigned to: Sveta Smirnova CPU Architecture:Any

[31 Jan 2007 14:58] Per Kleven
Description:
Using sql_calc_found_rows / found_rows() returns different value on 4.x / 5.x when a show full columns command is executed in between.

The main problem is that JBDC sometimes appends "SHOW FULL COLUMNS" to the querybuffer, so it's impossible to get the correct found_rows(), because on 5.x it's re-calculated on a SHOW FULL COLUMNS command.

How to repeat:
Run this query (on a installation with more than one user):

select sql_calc_found_rows * 
from mysql.user 
limit 1;
show full columns from mysql.user;
select found_rows();

found_rows() will differ on 4.x vs 5.x.

Tested on 4.1.20 AND 5.0.27
[31 Jan 2007 15:05] Per Kleven
Confirmed on gentoo as well.
[31 Jan 2007 16:50] Sveta Smirnova
Thank you for the report.

But according to our manual 5.x behaviour is not a bug:

In the absence of the SQL_CALC_FOUND_ROWS  option in the most recent SELECT  statement, FOUND_ROWS() returns the number of rows in the result set returned by that statement.

(http://dev.mysql.com/doc/refman/5.0/en/information-functions.html)
[31 Jan 2007 21:17] Per Kleven
I had a sneaking suspicion that was the case... Just have to find a workaround somehow. Thanks ;)
[5 Feb 2007 9:15] Sveta Smirnova
We internally discussed this problem and decided examine this problem deeper.

Please, provide example of JDBC application which appends "SHOW FULL COLUMNS" to the buffer.

Do you use additional engines like Hybernate or such?
[6 Feb 2007 3:11] Per Kleven
This behavior was shown using ColdFusionMX 7.02 (any os) with connector/J 3.x + 5.x. But I have not could confirm if it's the JDBC driver itself or ColdFusion that appends these "metadata" queries.

Examining the JDBC source code, the "Field" class seems to append a "SHOW FULL COLUMNS" query to one of its methods.
[6 Feb 2007 8:47] Sveta Smirnova
Yes, Connector/J itself issues SHOW FULL COLUMNS query in the method com.mysql.jdbc.Field.getCollation() But I can not find when it issues this query unexpected. So I close this report as "Not a bug" again.
[21 Jun 2007 13:25] Mark Matthews
It appears that ColdFusion actually calls ResultSetMetadata.isCaseSensitive() for many result set columns. 

5.0.7 (not yet released) has a fix for this, adding "useDynamicCharsetInfo=false" to your JDBC URL will cause the driver to use a static, cached version of character set/collation information to fulfil requests for things like .isCaseSensitive().

You can test with a nightly snapshot from http://downloads.mysql.com/snapshots.php#connector-j before 5.0.7 is released.
[24 Jun 2007 23:45] Per Kleven
Perfect ;)