DROP DATABASE IF EXISTS bugtest;
CREATE DATABASE bugtest;
USE bugtest;
--delimiter //
CREATE PROCEDURE fill_bugtable (len INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= len DO
INSERT INTO bugtable (bugtable_varchar, bugtable_int) VALUES ("foo", i%2);
SET i = i + 1;
END WHILE;
END //
--delimiter ;
--echo # Tests to reproduce Bug #1 ------------------------------------------------
CREATE TABLE `bugtable` (
`bugtable_int` int,
`bugtable_varchar` varchar(64),
KEY (`bugtable_int`)
) ENGINE=MyISAM CHARSET=latin1 COLLATE=latin1_general_ci;
CALL fill_bugtable(150);
SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2;
SELECT FOUND_ROWS();
SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
SELECT FOUND_ROWS();
--echo # Tests to reproduce Bug #2 ------------------------------------------------
ALTER TABLE `bugtable` DROP INDEX `bugtable_int`;
SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2;
SELECT FOUND_ROWS();
SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
SELECT FOUND_ROWS();
--echo # Tests to reproduce Bug #3 ------------------------------------------------
DROP TABLE `bugtable`;
CREATE TABLE `bugtable` (
`bugtable_int` int,
`bugtable_varchar` varchar(64),
KEY (`bugtable_int`)
) ENGINE=MyISAM CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CALL fill_bugtable(150);
SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2;
SELECT FOUND_ROWS();
SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
SELECT FOUND_ROWS();
--echo # Tests to reproduce Bug #4 ------------------------------------------------
ALTER TABLE `bugtable` DROP INDEX `bugtable_int`;
SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2;
SELECT FOUND_ROWS();
SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
SELECT FOUND_ROWS();
drop database bugtest;
MDEV-6221 SQL_CALC_FOUND_ROWS yields wrong result again
The problem (re-?)appeared on the 10.0 tree with this revision:
Test case (exactly the same as in the attachment, just made it MTR-like). For all SELECT FOUND_ROWS() queries the expected result is 75.
DROP DATABASE IF EXISTS bugtest; CREATE DATABASE bugtest; USE bugtest; --delimiter // CREATE PROCEDURE fill_bugtable (len INT) BEGIN DECLARE i INT DEFAULT 1; WHILE i <= len DO INSERT INTO bugtable (bugtable_varchar, bugtable_int) VALUES ("foo", i%2); SET i = i + 1; END WHILE; END // --delimiter ; --echo # Tests to reproduce Bug #1 ------------------------------------------------ CREATE TABLE `bugtable` ( `bugtable_int` int, `bugtable_varchar` varchar(64), KEY (`bugtable_int`) ) ENGINE=MyISAM CHARSET=latin1 COLLATE=latin1_general_ci; CALL fill_bugtable(150); SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2; SELECT FOUND_ROWS(); SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2; SELECT FOUND_ROWS(); --echo # Tests to reproduce Bug #2 ------------------------------------------------ ALTER TABLE `bugtable` DROP INDEX `bugtable_int`; SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2; SELECT FOUND_ROWS(); SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2; SELECT FOUND_ROWS(); --echo # Tests to reproduce Bug #3 ------------------------------------------------ DROP TABLE `bugtable`; CREATE TABLE `bugtable` ( `bugtable_int` int, `bugtable_varchar` varchar(64), KEY (`bugtable_int`) ) ENGINE=MyISAM CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CALL fill_bugtable(150); SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2; SELECT FOUND_ROWS(); SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2; SELECT FOUND_ROWS(); --echo # Tests to reproduce Bug #4 ------------------------------------------------ ALTER TABLE `bugtable` DROP INDEX `bugtable_int`; SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 59,2; SELECT FOUND_ROWS(); SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2; SELECT FOUND_ROWS(); drop database bugtest;