Description:
When calling SELECT FOUND_ROWS() after using SQL_CALC_FOUND_ROWS with LIMIT on a query surrounded by parentheses always returns the actual returned rows and not the found rows; The correct value is returned if the query is not surrounded by parentheses. Happens for both InnoDB and MyISAM tables.
CREATE TABLE dev.sql_calc_test (ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, FOO TINYINT NOT NULL DEFAULT 0);
mysql> INSERT INTO dev.sql_calc_test VALUES(NULL, 1),(NULL,2),(NULL,3),(NULL,4);
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM dev.sql_calc_test LIMIT 0;
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 4 |
+--------------+
mysql> (SELECT SQL_CALC_FOUND_ROWS * FROM dev.sql_calc_test) LIMIT 0;
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 0 |
+--------------+
mysql> (SELECT SQL_CALC_FOUND_ROWS * FROM dev.sql_calc_test) LIMIT 1;
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 1 |
+--------------+
When using the same syntax with a UNION query, it works correctly:
mysql> (SELECT SQL_CALC_FOUND_ROWS * FROM dev.sql_calc_test WHERE FOO<2) UNION
(SELECT * FROM dev.sql_calc_test WHERE FOO=3)
LIMIT 0;
mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 2 |
+--------------+
This was working correctly at least in 5.6.10.
How to repeat:
mysql> CREATE TABLE dev.sql_calc_test (ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, FOO TINYINT NOT NULL DEFAULT 0);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO dev.sql_calc_test VALUES(NULL, 1),(NULL,2),(NULL,3),(NULL,4);
Query OK, 4 rows affected (0.00 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM dev.sql_calc_test LIMIT 0;
Empty set (0.00 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 4 |
+--------------+
1 row in set (0.00 sec)
mysql> (SELECT SQL_CALC_FOUND_ROWS * FROM dev.sql_calc_test) LIMIT 0;
Empty set (0.00 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 0 |
+--------------+
1 row in set (0.00 sec)
mysql> (SELECT SQL_CALC_FOUND_ROWS * FROM dev.sql_calc_test WHERE FOO<2)
UNION
(SELECT * FROM dev.sql_calc_test WHERE FOO=3)
LIMIT 0;
Empty set (0.00 sec)
mysql> select FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM dev.sql_calc_test WHERE FOO<2
UNION
SELECT * FROM dev.sql_calc_test WHERE FOO=3
LIMIT 0;
Empty set (0.01 sec)
mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
| 2 |
+--------------+
1 row in set (0.00 sec)
Suggested fix:
Return the proper count of found rows.