Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.9
-
None
-
None
-
- Intel Core i3-3220 CPU @ 3.30 GHz
- Debian Wheeze GNU/Linux amd64 (x86_64) 7.4, Kernel 3.2.41.
- MariaDB Debian packages: 10.0.9+maria-1~wheezy from "repository configuration tool" in mariadb.org.
Description
This example database & SQL allows to reproduce the bug:
--
|
-- 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)
|