[MDEV-6221] SQL_CALC_FOUND_ROWS yields wrong result again Created: 2014-05-09  Updated: 2014-11-27  Resolved: 2014-06-06

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 10.0.10
Fix Version/s: 10.0.12

Type: Bug Priority: Critical
Reporter: Serg G. Brester Assignee: Sergei Golubchik
Resolution: Fixed Votes: 2
Labels: None
Environment:

Debian 7-wheezy, (and jessie)
Exact version: 10.0.10-MariaDB-1~wheezy-log

Issue Links:
Duplicate
duplicates MDEV-6230 Error (too many FOUND_ROWS) for query... Closed
Relates
relates to MDEV-7219 SQL_CALC_FOUND_ROWS yields wrong result Closed
relates to MDEV-4711 SQL_CALC_FOUND_ROWS yields wrong result Closed
relates to MDEV-5549 Wrong row counter in found_rows() result Closed
relates to MDEV-5898 FOUND_ROWS() return incorrect value w... Closed

 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


 Comments   
Comment by Serg G. Brester [ 2014-05-09 ]

Thanks elenst for clarifying this issue. In addition, she had verified it in previously fixed versions, see comment-49514:
Version 10.0.8 and 10.0.9 seems to be working.

Comment by Serg G. Brester [ 2014-05-09 ]

I think the next time this bug will be fixed, I volunteer to write a new test case(s) to coverage this сode to close this theme forever.
I mean, 3 reports are enough

Comment by Elena Stepanova [ 2014-05-09 ]

Hi Serg,
Thanks a lot for the thorough test case(s) and the offer.

One note on a part of the test case "Challenge Nr. 1":

--echo # *** FOUND_ROWS works correct without second criteria "OR" (but this criteria found nothing - not really affected?!) 
--echo # 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`;

It works with InnoDB, but still fails with MyISAM (returns 3).

Reappearance of the bug was caused by our operational inaccuracy – MDEV-4711 was closed as a "duplicate of" / "fixed with" MDEV-5549, which was true at the time, but the test case from MDEV-4711 was not added to the regression suite, only the one from MDEV-5549 was. But then the following commit

revno: 4090
 
revision-id: sergii@pisem.net-20140319193312-w2xror087po3mx1u
 
parent: sergii@pisem.net-20140319085818-41k7l0hwualm6qt1
 
committer: Sergei Golubchik <sergii@pisem.net>
 
branch nick: 10.0
 
timestamp: Wed 2014-03-19 20:33:12 +0100
 
message:
 
  MDEV-5898 FOUND_ROWS() return incorrect value when using DISTINCT
 
  
  revert the fix for MDEV-5549, use a different approach.

caused a regression which was only visible on the test case from MDEV-4711, while the test case from MDEV-5549 still passes. Thus it failed to be caught by regression tests.

Comment by Serg G. Brester [ 2014-05-09 ]

It works with InnoDB, but still fails with MyISAM (returns 3).

It's not like for me - I have tested with the MyISAM, Aria and InnoDB - same wrong result on version "10.0.10-MariaDB-1~wheezy-log": returns 3.

Comment by Elena Stepanova [ 2014-05-09 ]

Actually, as your comment in the test case suggests, this particular fragment returns a correct result to you.

-- *** 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.

I can see it too, with InnoDB. But with MyISAM and with Aria it returns 3.
The rest of the test behaves identically on InnoDB and MyISAM.

It doesn't change the meaning of the report, just something to take into account (probably add the regression test cases both for InnoDB and MyISAM).

Generated at Thu Feb 08 07:10:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.