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

SQL_CALC_FOUND_ROWS yields wrong result

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.3
    • Fix Version/s: 10.0.8
    • Component/s: None
    • Labels:
      None
    • Environment:
      Ubuntu 12.10, MariaDB 10.0.3 from the mariadb.org repository

      Description

      CREATE TEMPORARY TABLE A(`id` mediumint, `value` mediumint, PRIMARY KEY(`id`,`value`));
      INSERT INTO A VALUES (1,1),(2,2),(3,3);
      CREATE TEMPORARY TABLE B(`id` mediumint, PRIMARY KEY(`id`));
      INSERT INTO B VALUES (1),(2),(3);
      SELECT SQL_CALC_FOUND_ROWS 1 FROM A LEFT JOIN B ON A.`id` = B.`id` WHERE B.`id` IS NULL ORDER BY `value` DESC;
      SELECT FOUND_ROWS() AS `count`;
      SELECT COUNT(*) FROM (SELECT 1 FROM A LEFT JOIN B ON A.`id` = B.`id` WHERE B.`id` IS NULL ORDER BY `value` DESC) AS `x`;

      Expected result:
      0
      0
      (as the left join can never fail)

      Actual result:
      3
      0

      This means that SQL_CALC_FOUND_ROWS ignores the fact that all rows will be filtered.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              psergey Sergei Petrunia
              Reporter:
              rtreffer Rene Treffer
              Votes:
              1 Vote for this issue
              Watchers:
              7 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: