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

SQL_CALC_FOUND_ROWS yields wrong result again

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 10.0.10
    • Fix Version/s: 10.0.12
    • Component/s: None
    • Labels:
      None
    • Environment:
      Debian 7-wheezy, (and jessie)
      Exact version: 10.0.10-MariaDB-1~wheezy-log

      Description

      So issues MDEV-4711 resp. MDEV-5549 have continuation.

      Challenge Nr. 1

      With "left join" "order by" and "sub criteria" over first table (not really will be affected - cause always False, but nevertheless as a result should traverse over whole table).
      Strange, FOUND_ROWS works correctly, if we use "inner join" instead "left join", or no "order by" clause or no "sub criteria" over first table.
      Example below:

      -- *** create test tables:
      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, `val` mediumint, PRIMARY KEY(`id`));
      INSERT INTO B VALUES (1,5),(2,5),(3,10);
      -- *** WRONG count, returns 3 instead of 2 :
      SELECT SQL_CALC_FOUND_ROWS 1 FROM A 
      LEFT JOIN B ON A.`id` = B.`id` 
      WHERE B.`val` = 5
        OR A.`value` = 5 OR A.`value` = 10
      ORDER BY `value` DESC 
      LIMIT 1; SELECT FOUND_ROWS() AS `count`;
      -- *** test - should returns 2:
      SELECT COUNT(*) FROM (
      SELECT A.id, B.val FROM A 
      LEFT JOIN B ON A.`id` = B.`id` 
      WHERE B.`val` = 5
        OR A.`value` = 5 OR A.`value` = 10
      ORDER BY `value` DESC 
      ) AS `x`;
       
      -- *** FOUND_ROWS works correct without second criteria "OR" (but this criteria found nothing - not really affected?!) 
      -- But I think this could would be a reason for wrong counting.
      SELECT SQL_CALC_FOUND_ROWS 1 FROM A 
      LEFT JOIN B ON A.`id` = B.`id` 
      WHERE B.`val` = 5
      --  OR A.`value` = 5 OR A.`value` = 10
      ORDER BY `value` DESC 
      LIMIT 1; SELECT FOUND_ROWS() AS `count`;
      -- *** FOUND_ROWS work correct without "ORDER BY":
      SELECT SQL_CALC_FOUND_ROWS 1 FROM A 
      LEFT JOIN B ON A.`id` = B.`id` 
      WHERE B.`val` = 5
        OR A.`value` = 5 OR A.`value` = 10
      -- ORDER BY `value` DESC 
      LIMIT 1; SELECT FOUND_ROWS() AS `count`;
      -- *** FOUND_ROWS work correct with "INNER JOIN" instead "LEFT JOIN":
      SELECT SQL_CALC_FOUND_ROWS 1 FROM A 
      -- LEFT JOIN B ON A.`id` = B.`id` 
      INNER JOIN B ON A.`id` = B.`id` 
      WHERE B.`val` = 5
        OR A.`value` = 5 OR A.`value` = 10
      -- ORDER BY `value` DESC 
      LIMIT 1; SELECT FOUND_ROWS() AS `count`;

      Result:

      count – here it is wrong
      3
      COUNT(*)
      2
      count
      2
      count
      2
      count
      2

      Challenge Nr. 2

      SQL statement below might possibly help with troubleshooting.
      If we remove primary key from second! (left joined) table, it works fine again.
      But why it is so?! Imho, the criteria, that produce this wrong result, is "OR A.`value` = 5".

      -- *** create test tables:
      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, `val` mediumint, PRIMARY KEY(`id`));
      INSERT INTO B VALUES (1,5),(2,5),(3,10);
      CREATE TEMPORARY TABLE C(`id` mediumint, `val` mediumint, PRIMARY KEY(`id`,`val`));
      INSERT INTO C VALUES (1,5),(2,5),(3,10);
      CREATE TEMPORARY TABLE D(`id` mediumint, `val` mediumint);
      INSERT INTO D VALUES (1,5),(2,5),(3,10);
      -- *** WRONG count, returns 3 instead of 2 :
      SELECT SQL_CALC_FOUND_ROWS 1 FROM A 
      LEFT JOIN B ON A.`id` = B.`id` 
      WHERE B.`val` = 5
        OR A.`value` = 5
      ORDER BY `value` DESC 
      LIMIT 1; SELECT FOUND_ROWS() AS `count`;
      -- *** WRONG count also, returns 3 instead of 2 :
      SELECT SQL_CALC_FOUND_ROWS 1 FROM A 
      LEFT JOIN C ON A.`id` = C.`id` 
      WHERE C.`val` = 5
        OR A.`value` = 5
      ORDER BY `value` DESC 
      LIMIT 1; SELECT FOUND_ROWS() AS `count`;
      -- *** CORRECT count, returns 2 :
      SELECT SQL_CALC_FOUND_ROWS 1 FROM A 
      LEFT JOIN D ON A.`id` = D.`id` 
      WHERE D.`val` = 5
        OR A.`value` = 5
      ORDER BY `value` DESC 
      LIMIT 1; SELECT FOUND_ROWS() AS `count`;

      Result:

      count – with primary – should return 2
      3
      count – with primary, but 'id' together with 'val', should return 2 also
      3
      count – without primary
      2

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                serg Sergei Golubchik
                Reporter:
                sebres Serg G. Brester
              • Votes:
                2 Vote for this issue
                Watchers:
                7 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: