Bug #104116 SQL_CALC_FOUND_ROWS with LIMIT returns invalid val when query in parentheses
Submitted: 25 Jun 2021 19:04 Modified: 25 Jun 2021 22:08
Reporter: Jean-Marc Marchand Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S2 (Serious)
Version:5.7 OS:Any
Assigned to: CPU Architecture:Any

[25 Jun 2021 19:04] Jean-Marc Marchand
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.
[25 Jun 2021 22:08] MySQL Verification Team
Thank you for the bug report. Version 8.0 not affected.