SQL_CALC_FOUND_ROWS with FOUND_ROWS always returns 1

Question

select SQL_CALC_FOUND_ROWS, col1 , mytable.id from mytable group by col1;

select found_rows();

select found_rows() , col1  , mytable.id from mytable group by col1;

This above query always results 1 for found rows. I am using php 7.1 and maria db 10.1 .

My backend is laravel 5.5 and I am making DB::raw request on mysql. Server apache lamp server.

Is it a bug or there is some way around for this ?

because I have a group by which changes the count. I have to use found_rows(). So please try to give solution for this questions not , I cannot replace it because of the extra conditions which I have not posted to keep the query precise. — sai sanath, Nov 21 '17 at 07:17
@SebastianBrosch pls check https://stackoverflow.com/questions/8222417/what-is-the-difference-of-using-count-and-found-rows-if-i-dont-use-a-limit — Subhajit, Nov 21 '17 at 07:18
@RickJames count(distinct col1) gives the count of col1 not the count of rows in the output table. found_rows finds the no of rows in output table. — Subhajit, Nov 22 '17 at 05:18
This is a bug posted in mysql [not in mariadb] here: https://bugs.mysql.com/bug.php?id=68458 Please check the mysql version. This bug exists in 5.6.* and is solved in 5.6.11 release. Verify that your server has version different from [5.6.* < 5.6.11] to make this work. — Akash M. Pai, Nov 22 '17 at 07:29

score 1 · Answer 1 · answered Nov 22 '17 at 07:42

Solution 1:

Please check the mysql version. This bug exists in 5.6.* and is solved in 5.6.11 release. Verify that your server has version different from [5.6.* < 5.6.11] to make this work.

This is a bug posted in mysql [not in mariadb] here: http://bugs.mysql.com/bug.php?id=68458.

If you have verified the version of mysql, then check below explanation:

NOTE: It is mentioned in the bug link that "MySQL server compiled from "5.6.11-log Source distribution" still has this bug."

Solution 2:

found_rows() and limit in the same query will not result in total count but will give count for data with limit. If there is a limit in the select query, you will have to use

 "select SQL_CALC_FOUND_ROWS, ....... limit x;"

and immediately next execute

"select found_rows();"

OR

If there is no limit in the select statement then you can directly execute

"select found_rows(), .......;"

Explanation here: https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows

using debian jessie , php 7.1.11 , phpmyadmin 4.7.4 , mariadb 10.2. Still my query fails: select SQL_CALC_FOUND_ROWS, col1 , mytable.id from mytable group by col1 order by mytable.col2 desc limit 10 offset 10; select found_rows(); — Subhajit, Nov 22 '17 at 08:25

Tata · Answer 2 · 2017-11-21T09:31:26.167

The example query seems to be wrong, as you should have the col1 to be a part of selected fields

found_rows returns the number of rows found in the previous query. Have you checked the result of the first query? how many rows it returns? If it returns more than 1 rows - meaning that some query is being executed between your query and select found_rows() at the same session. I didn't succeed to reproduce this problem, but you can try using transaction here.

On the other part -

From the query it seems that you are trying to get a distinct count from a table, but using a group by for some reason.

did you try using

select count( disctinct col1) from mytable

If this does not work for you, your second best option is:

select count(1) from (<your query goes here>)

I think he has not posted the actual query . but he wants to use found_rows() function to get the no of rows along with the condition. But its not working . And count and found_rows works for different reasons. please check the this he link he has provided. https://stackoverflow.com/questions/8222417/what-is-the-difference-of-using-count-and-found-rows-if-i-dont-use-a-limit — Subhajit, Nov 21 '17 at 07:53

SQL_CALC_FOUND_ROWS with FOUND_ROWS always returns 1

2 Answers2