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
-
