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)
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)