Details
Description
CREATE TABLE t1 (pk INT PRIMARY KEY, i INT) ENGINE=MyISAM; |
INSERT INTO t1 VALUES (1,1),(2,2),(3,10); |
|
SELECT
|
i,
|
( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt |
FROM t1 AS outer_t1; |
|
SELECT
|
( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt, |
GROUP_CONCAT(i)
|
FROM t1 AS outer_t1 |
GROUP BY cnt; |
|
DROP TABLE t1; |
This test case is very similar to MDEV-12561, with the only difference in 3rd row inserted into the table. Results are different though, the first query also seems to work wrongly:
MariaDB [test]> SELECT |
-> i,
|
-> ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt |
-> FROM t1 AS outer_t1; |
+------+------+ |
| i | cnt |
|
+------+------+ |
| 1 | 3 |
|
| 2 | 3 |
|
| 10 | 0 |
|
+------+------+ |
3 rows in set (0.00 sec) |
|
MariaDB [test]>
|
MariaDB [test]> SELECT |
-> ( SELECT COUNT(*) FROM t1 WHERE outer_t1.i IN ( SELECT pk FROM t1 ) ) AS cnt, |
-> GROUP_CONCAT(i)
|
-> FROM t1 AS outer_t1 |
-> GROUP BY cnt; |
+------+-----------------+ |
| cnt | GROUP_CONCAT(i) |
|
+------+-----------------+ |
| 0 | 1,2,10 |
|
+------+-----------------+ |
1 row in set (0.00 sec) |
The rest is different on different versions. It should be re-checked after MDEV-12561 is fixed.
I can't remember why I said that the first result is incorrect, it seems okay.
The second result was fixed by the patch for
MDEV-10053commit 97fb1f26797828427ad850b0420aaafc74205e71
Author: Igor Babaev
Date: Fri Apr 21 14:34:24 2017 -0700
Fixed bug mdev-10053.
The implementation of the walk method for the class Item_in_subselect
was missing. As a result the method never traversed the left operand
of any IN subquery predicate.
Item_exists_subselect::exists2in_processor() that performs the
Exist-To-In transformation calls the walk method to collect info
on outer references. As the walk method did not traverse the
left operands of the IN subqueries the outer references there
were not taken into account and some subqueries that were actually
correlated were marked as uncorrelated. It could lead to an
attempt of the materialization of such a subquery.