--
-- bugMariaDB.sql
--
-- Use:
-- $ mysql -uroot -p < bugMariaDB.sql
--

DROP DATABASE IF EXISTS bugtest;
CREATE DATABASE bugtest;
USE bugtest;


CREATE TABLE tbltestA (
  numA   INTEGER PRIMARY KEY
, dated  DATETIME
, parity INTEGER
);

CREATE TABLE tbltestB (
  id_numA INTEGER
, id_part INTEGER
, status  CHAR(16) DEFAULT ''

, PRIMARY KEY (id_numA, id_part)
);

delimiter //

  CREATE PROCEDURE seqInsert (len INT)
  BEGIN
    DECLARE i INT DEFAULT 1;
    WHILE i <= len DO
      INSERT INTO tbltestA (numA, dated, parity) VALUES (i, UTC_TIMESTAMP(), i%2);
      INSERT INTO tbltestB (id_numA, id_part, status) VALUES (i, 0, 'ok');
      INSERT INTO tbltestB (id_numA, id_part, status) VALUES (i, 1, 'ok');
      INSERT INTO tbltestB (id_numA, id_part, status) VALUES (i, 2, 'ko');
      INSERT INTO tbltestB (id_numA, id_part, status) VALUES (i, 3, 'ko');
      INSERT INTO tbltestB (id_numA, id_part, status) VALUES (i, 4, 'ok');
      INSERT INTO tbltestB (id_numA, id_part, status) VALUES (i, 5, 'ok');
      SET i = i + 1;
    END WHILE;
  END //

delimiter ;

CALL seqInsert(512);


-- Tests to reproduce the bug ------------------------------------------------
-- NOTE: DISTINCT is necessary to reproduce the bug

-- TEST 1 --------------------------------------------------------------------
SELECT SQL_CALC_FOUND_ROWS DISTINCT numA,dated,status FROM tbltestA JOIN tbltestB ON id_numA=numA WHERE numA <= 256 AND parity=0 AND status='ok' ORDER BY dated DESC, numA DESC LIMIT 45;
SELECT FOUND_ROWS();
-- OK: 128

SELECT SQL_CALC_FOUND_ROWS DISTINCT numA,dated,status FROM tbltestA JOIN tbltestB ON id_numA=numA WHERE numA <= 256 AND parity=0 AND status='ok' ORDER BY dated DESC, numA DESC LIMIT 46;
SELECT FOUND_ROWS();
-- ERR: 46 (46 in MariaDB, 128 in MySQL)


-- TEST 2 --------------------------------------------------------------------
SELECT SQL_CALC_FOUND_ROWS DISTINCT numA,dated,status FROM tbltestA JOIN tbltestB ON id_numA=numA WHERE numA <= 256 AND parity=0 AND status='ok' ORDER BY dated DESC, numA DESC LIMIT 50 OFFSET 0;
SELECT FOUND_ROWS();
-- ERR: 50 (50 in MariaDB, 128 in MySQL)

SELECT SQL_CALC_FOUND_ROWS DISTINCT numA,dated,status FROM tbltestA JOIN tbltestB ON id_numA=numA WHERE numA <= 256 AND parity=0 AND status='ok' ORDER BY dated DESC, numA DESC LIMIT 50 OFFSET 50;
SELECT FOUND_ROWS();
-- ERR: 100 (100 in MariaDB, 128 in MySQL)
