MySQL Bugs: #31290: FOUND_ROWS() returns 1 for some queries

Bug #31290	FOUND_ROWS() returns 1 for some queries
Submitted:	28 Sep 2007 22:59	Modified:	28 Sep 2007 23:29
Reporter:	Matt Castetter	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.45	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	found_rows

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.

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.