[MDEV-5898] FOUND_ROWS() return incorrect value when using DISTINCT Created: 2014-03-18  Updated: 2014-11-27  Resolved: 2014-03-26

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.9
Fix Version/s: 10.0.10

Type: Bug Priority: Major
Reporter: Mario Palomo Torrero Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:
  • 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.

Attachments: File bugMariaDB.sql    
Issue Links:
Relates
relates to MDEV-6221 SQL_CALC_FOUND_ROWS yields wrong resu... Closed
relates to MDEV-7219 SQL_CALC_FOUND_ROWS yields wrong result Closed
relates to MDEV-5549 Wrong row counter in found_rows() result Closed

 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)



 Comments   
Comment by Elena Stepanova [ 2014-03-18 ]

Thank you for the detailed report.

The problem was introduced by the bugfix for MDEV-5549:

    revno: 3965.1.14
 
    revision-id: sergii@pisem.net-20140201083407-l6hg86u20byph3i7
 
    parent: sergii@pisem.net-20140201083326-wlzdm7dash8h58m8
 
    fixes bug: https://mariadb.atlassian.net/browse/MDEV-5549
 
    committer: Sergei Golubchik <sergii@pisem.net>
 
    branch nick: 10.0
 
    timestamp: Sat 2014-02-01 09:34:07 +0100
 
    message:
 
      MDEV-5549 Wrong row counter in found_rows() result
 
      
      only let filesort() count rows for SQL_CALC_ROWS if it's using priority queue

The complaint in MDEV-5549 was valid, because it was reproducible without LIMIT – the query would still return 9 rows, and FOUND_ROWS() would still return 700+.

In this report, however, LIMIT does affect the number of affected rows, so FOUND_ROWS() after SQL_CALC_FOUND_ROWS should return the original count, but it does not.

Here is the fragment of the test case from the description (no changes in data or structures, just put it together):

--source include/have_innodb.inc
 
 
 
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);
 
 
 
SELECT COUNT(*) FROM ( 
  SELECT 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
 
) alias;
 
 
 
--disable_result_log
 
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;
 
--enable_result_log
 
 
 
SELECT FOUND_ROWS();

result:

SELECT COUNT(*) FROM ( 
SELECT 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
 
) alias;
 
COUNT(*)
 
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();
 
FOUND_ROWS()
 
46

Generated at Thu Feb 08 07:07:52 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.