Bug #31290 FOUND_ROWS() returns 1 for some queries

• Submitted: 28 Sep 2007 22:59
• Modified: 28 Sep 2007 23:29
• Reporter: Matt Castetter
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.45
• OS: Linux
• CPU Architecture: Any
• Tags: found_rows

[28 Sep 2007 22:59] Matt Castetter

Description:
When running a queries against a small table, some queries like:

SELECT SQL_CALC_FOUND_ROWS * FROM photos WHERE userid='2' ORDER BY `viewstoday` DESC LIMIT 0, 5

FOUND_ROWS() Will return accurate total row numbers - in this case, '6'.

However a query like:

SELECT SQL_CALC_FOUND_ROWS * FROM photos ORDER BY `viewstoday` DESC LIMIT 0, 5

FOUND_ROWS() will return '1' instead of the anticipated '11'.

I'm running these queries back to back from php on a very under-utilized server, so i can't see any other queries getting in the middle and messing it up...

How to repeat:
CREATE TABLE `photos` (
  `photoid` bigint(20) unsigned NOT NULL auto_increment,
  `userid` bigint(20) unsigned NOT NULL,
  `description` varchar(200) NOT NULL,
  `created` datetime NOT NULL,
  `views` bigint(20) unsigned NOT NULL,
  `viewstoday` bigint(20) unsigned NOT NULL,
  `keywords` varchar(255) NOT NULL,
  `perm` tinyint(3) unsigned NOT NULL default '15' COMMENT 'bitwise view permissions',
  PRIMARY KEY  (`photoid`),
  KEY `userid` (`userid`),
  KEY `perm` (`perm`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

and the queries above on some appropriately made-up rows.

Suggested fix:
not sure.

[28 Sep 2007 23:29] Matt Castetter

The key to resolving this was to do: 

SELECT FOUND_ROWS() as mycount

and then reference mycount instead of using mysql_result and assuming it would be the first variable returned.