Why FOUND_ROWS() returns wrong result for 5.7.17 SQL version?

Question

I have run following query on local system

SELECT FOUND_ROWS() FROM table_name LIMIT 1;
SQL Version : 5.6.16

It returns number of rows.

when same query run on server(SQL Version : 5.7.17) it return 0.

After searching on internet people suggest to use

SQL_CALC_FOUND_ROWS

so i have use following query on local and server as well.

SELECT SQL_CALC_FOUND_ROWS * FROM users
SELECT FOUND_ROWS();

But results are same it works fine on local(SQL Version : 5.8.16) and return 0 on server(SQL Version : 5.7.17).

I've been testing it on MySQL 5.7.21 and I cannot reproduce your results. Running `SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT 1; SELECT FOUND_ROWS();` correctly calculates the number of rows in the table. Of course, running `SELECT COUNT(*) FROM table_name;` produces the same result more easily, so maybe you want to explain what you are trying to achieve here? — Wladimir Palant, Mar 26 '18 at 12:25
Actually i am using this for pagination in stored procedure.I haven't test it on 5.7.21 but was thinking about this to implement on 5.7.21. and want to confirm why it is not working on 5.7.17 which is strange for me. — hu7sy, Mar 26 '18 at 12:30
Let's discuss pagination, and why your approach may not be optimal. More: http://mysql.rjweb.org/doc.php/pagination — Rick James, Mar 26 '18 at 20:25
@AlexZen i have written i have check both queries on local and server as well, read question properly. — hu7sy, Mar 27 '18 at 07:17
@AlexZen my dear friend i already said both quires return number of records for count in local but on server with same query and same DB data except change in sql version return 0 number of rows for count. — hu7sy, Mar 27 '18 at 09:44
Could this be realted to MYSQL Bug 83110 https://bugs.mysql.com/bug.php?id=73283 which was fixed in 5.7.18? — Greg Viers, Mar 27 '18 at 13:14

score 1 · Answer 1 · edited Mar 29 '18 at 08:46

1

This is a mysql bug that can be responsible for this issue, depending on which version you use:

You can workaround it by using a GROUP BY clause in your query. In my case it is working well.

edited Mar 29 '18 at 08:46

Nico Haase

answered Mar 29 '18 at 08:00

Md Nazrul Islam

"The fix will be in 4.0.17" -- that version is out for more than fourteen years. Are you sure the OP is running such an old system? – Nico Haase Mar 29 '18 at 08:08
Its strange that the bug report even does not mention `GROUP BY` - so you fixed all that through upgrading your system to 4.0.17? – Nico Haase Mar 29 '18 at 08:19

1 Answers1