[MDEV-5898] FOUND_ROWS() return incorrect value when using DISTINCT - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.0.9
Fix Version/s: 10.0.10
Component/s: None
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.

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)

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

bugMariaDB.sql
2 kB
2014-03-18 19:03

Issue Links

relates to

MDEV-6221 SQL_CALC_FOUND_ROWS yields wrong result again

Closed

MDEV-7219 SQL_CALC_FOUND_ROWS yields wrong result

Closed

MDEV-5549 Wrong row counter in found_rows() result

Closed

Activity

Elena Stepanova added a comment - 2014-03-18 21: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(*)

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()

Elena Stepanova added a comment - 2014-03-18 21: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

MariaDB Server

FOUND_ROWS() return incorrect value when using DISTINCT

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration