MySQL Bugs: #54868: SQL_CALC_FOUND_ROWS and FOUND_ROWS() do not work in MySQL Workbench SQL Editor

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

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.

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 ---

Thank you for the problem report.

fix confirmed in repository

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.