Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
10.0.10
-
None
-
None
-
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
- duplicates
-
-
- Closed
-
Activity
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
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.
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.
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).
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.