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)