|
I've testet the same SQL against mysql 5.5 (latest percona server) and it yields the correct result.
|
|
The problem occurs only if the ORDER BY is used. The same query without an order by works as expected.
|
|
Works correctly in MariaDB 5.5
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;
|
1
|
SELECT FOUND_ROWS() AS `count`;
|
count
|
0
|
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`;
|
COUNT(*)
|
0
|
Returns wrong result in 10.0 tree and 10.0-base tree.
|
|
The difference is caused by these lines in JOIN::exec_inner():
if (curr_join->order &&
curr_join->sortorder)
{
/* Use info provided by filesort. */
DBUG_ASSERT(curr_join->table_count > curr_join->const_tables);
JOIN_TAB *tab= curr_join->join_tab + curr_join->const_tables;
thd->limit_found_rows= tab->records;
}
the lines were added by igor@askmonty.org-20120901212159-ldmzn4wxuoejumj4m,
MDEV-415: Back-port of the WL task #1393 from the mysql-5.6 code line.
The task adds a more efficient handling of the queries with
ORDER BY order LIMIT n, such that n is small enough and
no indexes are used for order.
|
|
Seems to be no longer repeatable in 10.0.8.
|
|
It was fixed by the following revision:
revno: 3965.1.14
|
revision-id: sergii@pisem.net-20140201083407-l6hg86u20byph3i7
|
parent: sergii@pisem.net-20140201083326-wlzdm7dash8h58m8
|
fixes bug: https://mariadb.atlassian.net/browse/MDEV-5549
|
committer: Sergei Golubchik <sergii@pisem.net>
|
branch nick: 10.0
|
timestamp: Sat 2014-02-01 09:34:07 +0100
|
message:
|
MDEV-5549 Wrong row counter in found_rows() result
|
|
only let filesort() count rows for SQL_CALC_ROWS if it's using priority queue
|
|
|
Ok, so fixing this bug is not an accident. Closing as duplicate.
|
|
Re-opening for a moment to modify resolution.
|
|
Fixed along with MDEV-5549.
|
|
Great job, but not really or more specifically not everywhere fixed. I found another case, FOUND_ROWS works wrong.
10.0.10-MariaDB-1~wheezy-log
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 |
|
|
Thanks. Would you mind creating a new bug report?
|
|
Thanks. Would you mind creating a new bug report?
You're welcome. New bug report for same bug? Could this closed ticket not be re-opened? Sorry, I am new here...
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 |
|
New bug report for same bug? Could this closed ticket not be re-opened?
Yes, in this case a new report is preferable.
the bug was fixed at some point, if you try your test case on 10.0.8 or 10.0.9, it should work all right.
The problem was re-introduced in 10.0.10 by the fix for MDEV-5898 (revision-id: sergii@pisem.net-20140319193312-w2xror087po3mx1u).
It will be quite confusing if a bug fix for the same bug appears in the bzr history and in release change logs for different revisions.
You don't have to do it if you don't want to, I can create the bug report; it's just that the original reporter always has first dibs on filing one.
|
the bug was fixed at some point, if you try your test case on 10.0.8 or 10.0.9, it should work all right
I don't think so... The original test case from description works still good on 10.0.10... I has as result 0/0 (not 3/0 as before 10.0.8). So I think, the bug was not fixed everywhere (with other words not all possible variants of this). I wrote even:
I found another case, where FOUND_ROWS works wrong.
At the moment is unfortunately not possible to test 10.0.8 or 10.0.9 (I'm in the middle of moving/upgrading of my mashines), and don't want to test in production, whether MDEV-5898 really makes it broken. Had someone opportunity to check it on mentioned versions?
If it should be nevertheless reported as a new bug, so I do.
|
The original test case from description works still good on 10.0.10... I has as result 0/0 (not 3/0 as before 10.0.8)
Actually, it doesn't:
MariaDB [test]> CREATE TEMPORARY TABLE A(`id` mediumint, `value` mediumint, PRIMARY KEY(`id`,`value`));
|
Query OK, 0 rows affected (0.23 sec)
|
|
MariaDB [test]> INSERT INTO A VALUES (1,1),(2,2),(3,3);
|
Query OK, 3 rows affected (0.00 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> CREATE TEMPORARY TABLE B(`id` mediumint, PRIMARY KEY(`id`));
|
Query OK, 0 rows affected (0.17 sec)
|
|
MariaDB [test]> INSERT INTO B VALUES (1),(2),(3);
|
Query OK, 3 rows affected (0.01 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> 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;
|
Empty set (0.02 sec)
|
|
MariaDB [test]> SELECT FOUND_ROWS() AS `count`;
|
+-------+
|
| count |
|
+-------+
|
| 3 |
|
+-------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> 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`;
|
+----------+
|
| COUNT(*) |
|
+----------+
|
| 0 |
|
+----------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select @@version;
|
+-----------------+
|
| @@version |
|
+-----------------+
|
| 10.0.10-MariaDB |
|
+-----------------+
|
1 row in set (0.00 sec)
|
It's rather interesting that it works for you, maybe you have something in your configuration that prevents it from failing.
Answering your other question,
Had someone opportunity to check it on mentioned versions?
here you go:
MariaDB [test]> CREATE TEMPORARY TABLE A(`id` mediumint, `value` mediumint, PRIMARY KEY(`id`,`value`));
|
Query OK, 0 rows affected (0.19 sec)
|
|
MariaDB [test]> INSERT INTO A VALUES (1,1),(2,2),(3,3);
|
Query OK, 3 rows affected (0.01 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> CREATE TEMPORARY TABLE B(`id` mediumint, `val` mediumint, PRIMARY KEY(`id`));
|
Query OK, 0 rows affected (0.15 sec)
|
|
MariaDB [test]> INSERT INTO B VALUES (1,5),(2,5),(3,10);
|
Query OK, 3 rows affected (0.01 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> CREATE TEMPORARY TABLE C(`id` mediumint, `val` mediumint, PRIMARY KEY(`id`,`val`));
|
Query OK, 0 rows affected (0.17 sec)
|
|
MariaDB [test]> INSERT INTO C VALUES (1,5),(2,5),(3,10);
|
Query OK, 3 rows affected (0.00 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> CREATE TEMPORARY TABLE D(`id` mediumint, `val` mediumint);
|
Query OK, 0 rows affected (0.19 sec)
|
|
MariaDB [test]> INSERT INTO D VALUES (1,5),(2,5),(3,10);
|
Query OK, 3 rows affected (0.00 sec)
|
Records: 3 Duplicates: 0 Warnings: 0
|
|
MariaDB [test]> -- *** WRONG count, returns 3 instead of 2 :
|
MariaDB [test]> 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`;
|
+---+
|
| 1 |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.00 sec)
|
|
+-------+
|
| count |
|
+-------+
|
| 2 |
|
+-------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> -- *** WRONG count also, returns 3 instead of 2 :
|
MariaDB [test]> 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`;
|
+---+
|
| 1 |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.00 sec)
|
|
+-------+
|
| count |
|
+-------+
|
| 2 |
|
+-------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> -- *** CORRECT count, returns 2 :
|
MariaDB [test]> 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`;
|
+---+
|
| 1 |
|
+---+
|
| 1 |
|
+---+
|
1 row in set (0.00 sec)
|
|
+-------+
|
| count |
|
+-------+
|
| 2 |
|
+-------+
|
1 row in set (0.00 sec)
|
|
MariaDB [test]> select @@version;
|
+----------------+
|
| @@version |
|
+----------------+
|
| 10.0.9-MariaDB |
|
+----------------+
|
1 row in set (0.00 sec)
|
And yes, even then it's still possible that the original fix was not complete, but it doesn't change anything. The new report should be created anyway.
|