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

SQL_CALC_FOUND_ROWS yields wrong result

    XMLWordPrintable

Details

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

              psergei Sergei Petrunia
              rtreffer Rene Treffer
              Votes:
              1 Vote for this issue
              Watchers:
              7 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.