Bug #54868 SQL_CALC_FOUND_ROWS and FOUND_ROWS() do not work in MySQL Workbench SQL Editor
Submitted: 28 Jun 2010 18:30 Modified: 29 Jul 2010 13:28
Reporter: Brian Kelley Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:5.2.24 CE RC Rev 6246 OS:Any
Assigned to: Sergei Tkachenko CPU Architecture:Any
Tags: found_rows, MySQL Workbench, SQL Query

[28 Jun 2010 18:30] Brian Kelley
Description:
See http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows for details on SQL_CALC_FOUND_ROWS and FOUND_ROWS()

When using the SQL Editor and issuing a query containing an SQL_CALC_FOUND_ROWS and subsequent FOUND_ROWS() call, FOUND_ROWS() returns zero instead of the number of matching rows without a LIMIT applied.

I believe the nature of this is cross-platform, so I left the OS as Any, but I am running Windows XP Pro, with a Solaris MySQL server (version 5.1.47).

How to repeat:
How to repeat:

1) Issue the following commands in a new SQL Query window (by pressing Ctrl+Shift+Enter)
"SELECT SQL_CALC_FOUND_ROWS * FROM `database`.`table` LIMIT 10;
SELECT FOUND_ROWS();"
2) Result (1) contains the results of the query as expected.
3) Result (2) contains the number zero. It is expected to contain the number of rows in the `database`.`table`.

It may be worth noting that the first Result contains the text "Fetched 10 records, more available" at the top, indicating, perhaps, that MySQL Workbench is fetching the SQL_CALC_FOUND_ROWS itself.

Suggested fix:
Perhaps make an option to have MySQL Workbench not select the SQL_CALC_FOUND_ROWS itself.

Or it could detect if FOUND_ROWS() is used before another SELECT statement and intercept the call and substitute its own value in.
[28 Jun 2010 18:49] Brian Kelley
A workaround is to create a temporary procedure to run the correct SQL. This is however a security risk and should only be used on a development box.

--- BEGIN WORKAROUND SQL ---
DELIMITER //
DROP PROCEDURE IF EXISTS workaround//
CREATE PROCEDURE workaround ()
BEGIN
    SELECT SQL_CALC_FOUND_ROWS * FROM `database`.`table` LIMIT 10;
    SELECT FOUND_ROWS();
END//

DELIMITER ;

CALL workaround;

DROP PROCEDURE IF EXISTS workaround;

--- END WORKAROUND SQL ---
[28 Jun 2010 19:17] Valeriy Kravchuk
Thank you for the problem report.
[27 Jul 2010 13:12] Johannes Taxacher
fix confirmed in repository
[29 Jul 2010 13:28] Tony Bedford
An entry has been added to the 5.2.26 changelog:

When using the SQL Editor and issuing a query containing SQL_CALC_FOUND_ROWS and a subsequent FOUND_ROWS() call, FOUND_ROWS() returned zero instead of the number of matching rows without a LIMIT applied.