FOUND_ROWS returning wrong record count

Yii 1.1 General Discussion
1

I have a very strange problem with getting the record count with FOUND_ROWS() using Yii and MySQL.

I have the following PHP code in Yii:


$sql = "SELECT DISTINCT SQL_CALC_FOUND_ROWS user.id FROM user, personal_info WHERE (personal_info.bio LIKE '%A%' ) AND personal_info.user_id = user.id AND user.role = 'F' LIMIT 0, 10";


$results = $this->findAllBySql($sql);

$result = $this->findBySql("SELECT FOUND_ROWS() as rowCount;");

$this->rowCount = $result->rowCount;

The query should be returning 15 records, but the FOUND_ROWS() call keeps returning 7.

After looking through the trace logs, I noticed the following SQL code was being executed right after my SQL statement is run:

First it shows my original SQL query:

Querying SQL: SELECT DISTINCT SQL_CALC_FOUND_ROWS user.id FROM user,

personal_info WHERE (personal_info.bio LIKE ‘%a%’ ) AND

personal_info.user_id = user.id AND user.role = ‘F’ LIMIT 0, 10

Next, instead of executing FOUND_ROWS(), Yii for some reason runs the below statement:

SHOW COLUMNS FROM user

Followed by:

SHOW CREATE TABLE user

And finally, my found_rows statement is run:

SELECT FOUND_ROWS() as rowCount;

It turns out that the FOUND_ROWS() call is counting the number of columns returned from the "SHOW COLUMNS FROM user" SQL code, instead of my SELECT statement.

I am not sure what to do in this situation. Why is Yii interrupting my FOUND_ROWS() call with the SHOW COLUMNS and SHOW CREATE TABLE sql calls? Is there any way I can force Yii to get the found_rows count for my query before executing the other sql statements? Help!

I’ve included the relevant section of the Trace log below:

Querying SQL: SELECT DISTINCT SQL_CALC_FOUND_ROWS user.id FROM user,

personal_info WHERE (personal_info.bio LIKE ‘%a%’ ) AND

personal_info.user_id = user.id AND user.role = ‘F’ LIMIT 0, 10

in /Applications/MAMP/htdocs/3rlegal/wwwroot/protected/models/Search.php

(573)

in

/Applications/MAMP/htdocs/3rlegal/wwwroot/protected/controllers/SearchController.php

(149)

in /Applications/MAMP/htdocs/3rlegal/wwwroot/index.php (13)

19:58:25.795504 trace system.db.CDbCommand

Querying SQL: SHOW COLUMNS FROM user

in /Applications/MAMP/htdocs/3rlegal/wwwroot/protected/models/Search.php

(573)

in

/Applications/MAMP/htdocs/3rlegal/wwwroot/protected/controllers/SearchController.php

(149)

in /Applications/MAMP/htdocs/3rlegal/wwwroot/index.php (13)

19:58:25.797741 trace system.db.CDbCommand

Querying SQL: SHOW CREATE TABLE user

in /Applications/MAMP/htdocs/3rlegal/wwwroot/protected/models/Search.php

(573)

in

/Applications/MAMP/htdocs/3rlegal/wwwroot/protected/controllers/SearchController.php

(149)

in /Applications/MAMP/htdocs/3rlegal/wwwroot/index.php (13)

19:58:25.798823 trace system.db.ar.CActiveRecord

Search.findBySql()

in /Applications/MAMP/htdocs/3rlegal/wwwroot/protected/models/Search.php

(575)

in

/Applications/MAMP/htdocs/3rlegal/wwwroot/protected/controllers/SearchController.php

(149)

in /Applications/MAMP/htdocs/3rlegal/wwwroot/index.php (13)

19:58:25.799015 trace system.db.CDbCommand

Querying SQL: SELECT FOUND_ROWS() as rowCount;

in /Applications/MAMP/htdocs/3rlegal/wwwroot/protected/models/Search.php

(575)

in

/Applications/MAMP/htdocs/3rlegal/wwwroot/protected/controllers/SearchController.php

(149)

in /Applications/MAMP/htdocs/3rlegal/wwwroot/index.php (13)

2

Its returning seven because you select DISTINCT WHERE (personal_info.bio LIKE ‘%A%’ ) AND personal_info.user_id = user.id AND user.role = ‘F’ LIMIT 0, 10.

The SQL Founds more the one querys that have same conditions.

SQL have bin released a querys witch are same.

Try with without DISTINCT and should be returning 15 querys

3

Thanks, but I already tried with and without DISTINCT and no matter what, it always returns 7. I’ve also tried a completely different SQL query that selects all records (there are over 500 altogether), and it still returns 7. The reason it’s returning 7 is because it is counting the results returned from the SHOW COLUMNS FROM user query, and the user table contains exactly 7 columns. For some reason, the SHOW COLUMNS query is running before Yii executes the FOUND_ROWS() query. Check out the log file I included above.

I also tried running the SELECT DISTINCT in combination with the FOUND_ROWS() query directly inside the MySQL command line and when I do that, FOUND_ROWS correctly returns 15 results. So I am pretty confident that this is an issue with Yii, not an issue with the SQL code.