Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-5898

FOUND_ROWS() return incorrect value when using DISTINCT

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0.9
    • 10.0.10
    • 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)

      Attachments

        Issue Links

          Activity

            People

              serg Sergei Golubchik
              mpalomo Mario Palomo Torrero
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.