-- SQL_CALC_FOUND_ROWS yields wrong result
--
-- found_rows_bug.sql.txt
--
-- Problem:
-- SQL_CALC_FOUND_ROWS returns different results for LIMIT 59,2 and LIMIT 60,2
--
-- Use:
-- $ mysql -u root -p < found_rows_bug.sql.txt

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 ;

SELECT VERSION();

-- Tests to reproduce Bug #1 ------------------------------------------------

-- Changes that will result in the correct count:
-- ORDER BY bugtable_int

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();
-- ERR: 61

SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
SELECT FOUND_ROWS();
-- ERR: 62

-- 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();
-- ERR: 150

SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
SELECT FOUND_ROWS();
-- ERR: 150

-- Tests to reproduce Bug #3 ------------------------------------------------

-- Changes that will result in the correct count:
-- Decrease size of bugtable_varchar to varchar(63)
-- ORDER BY bugtable_int

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();
-- OK: 75

SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
SELECT FOUND_ROWS();
-- ERR: 62

-- 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();
-- OK: 75

SELECT SQL_CALC_FOUND_ROWS * FROM bugtable WHERE `bugtable_int` = 0 ORDER BY bugtable_varchar LIMIT 60,2;
SELECT FOUND_ROWS();
-- ERR: 150