Bug #68646 SQL_CALC_FOUND_ROWS and FOUND_ROWS reporting incorrect values
Submitted: 12 Mar 2013 2:52 Modified: 12 Mar 2013 7:47
Reporter: Phil Allen Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S3 (Non-critical)
Version:5.6.10 OS:Windows (Server 2008 x64 SP2)
Assigned to: CPU Architecture:Any
Tags: found_rows, regression, sql_calc_found_rows

[12 Mar 2013 2:52] Phil Allen
Description:
FOUND_ROWS() appears to be returning the number of rows in the first query of the query plan rather than the number of rows in the final result set.

Server 
MySQL Version: 5.6.10 (64-bit)
OS Version: Windows Server 2008 Enterprise

How to repeat:
Execute the following:

DELIMITER $$
DROP PROCEDURE IF EXISTS populateTemp2$$
CREATE PROCEDURE populateTemp2()
BEGIN
  DECLARE i INT DEFAULT 0;

  WHILE i < 500
  DO
    INSERT
    INTO
      temp2 (q_type, q_text, datetime_created, datetime_last_updated, last_updated_by, deleted_flag)
    VALUES
      ('m_list', CONCAT('Text ', i), now(), now(), 1, MOD(i,2));
    SET i = i + 1;
  END WHILE;
END$$

DELIMITER ;

DROP TABLE IF EXISTS temp1;
CREATE TABLE temp1(
  qid INT(11) NOT NULL,
  pid INT(11) NOT NULL,
  display_order INT(11) NOT NULL,
  mandatory TINYINT(4) NOT NULL DEFAULT 0,
  INDEX idx_temp1_pid (pid)
);

DROP TABLE IF EXISTS temp2;
CREATE TABLE temp2(
  qid INT(11) NOT NULL AUTO_INCREMENT,
  q_type VARCHAR(20) NOT NULL,
  q_text VARCHAR(10000) NOT NULL,
  datetime_created DATETIME NOT NULL,
  datetime_last_updated DATETIME NOT NULL,
  last_updated_by VARCHAR(50) NOT NULL,
  deleted_flag TINYINT(1) NOT NULL DEFAULT 0,
  PRIMARY KEY (qid),
  INDEX idx_temp2_qid (qid)
);

INSERT
INTO
  temp1 (qid, pid, display_order, mandatory)
  VALUES(45, 1, 1, 0),
  (52, 1, 2, 0),
  (165, 1, 3, 0),
  (190, 1, 4, 0),
  (201, 1, 5, 0);

CALL populateTemp2();
DROP PROCEDURE IF EXISTS populateTemp2;

SELECT SQL_CALC_FOUND_ROWS q.qid
       , q.q_type
       , q.q_text
       , l.display_order AS the_order
       , l.mandatory AS mandatory
       , 1 AS has_links
  , q.deleted_flag
FROM
  temp2 q, temp1 l
WHERE
  l.pid = 1
  AND q.deleted_flag = 0
  AND q.qid = l.qid
ORDER BY
  l.display_order
LIMIT
  10;

SELECT FOUND_ROWS() AS 'row_count';
-- Returns 5

SELECT count(q.qid)
FROM
  temp2 q, temp1 l
WHERE
  l.pid = 1
  AND q.deleted_flag = 0
  AND q.qid = l.qid
ORDER BY
  l.display_order;
-- Returns 3 (Correct)
[12 Mar 2013 7:05] MySQL Verification Team
Hello Phil Allen,

Thank you for the report.

Verified as described.

Thanks,
Umesh
[12 Mar 2013 7:47] Tor Didriksen
http://bugs.mysql.com/bug.php?id=68458