planner: cannot use IndexJoin for nested subqueries
- Dominant language
- Go
- Stars
- 40.5k
- Forks
- 6.2k
- PR merge metrics
- PR metrics pending
Description
## Enhancement
```
mysql> create table a(id int unique key, fk_id int not null);
Query OK, 0 rows affected (0.01 sec)
mysql> create table b(id int unique key, fk_id int not null);
Query OK, 0 rows affected (0.00 sec)
mysql> create table c(id int unique key, fk_id int not null);
Query OK, 0 rows affected (0.01 sec)
mysql> explain select a.fk_id from a where id=1 and exists (
-> select 1 from b where b.id=a.fk_id and exists (
-> select 1 from c where c.id=b.fk_id
-> ));
+---------------------------------+----------+-----------+-----------------------+------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+----------+-----------+-----------------------+------------------------------------------------+
| HashJoin_13 | 0.80 | root | | semi join, equal:[eq(test.a.fk_id, test.b.id)] |
| ├─HashJoin_26(Build) | 7992.00 | root | | semi join, equal:[eq(test.b.fk_id, test.c.id)] |
| │ ├─IndexReader_34(Build) | 9990.00 | root | | index:IndexFullScan_33 |
| │ │ └─IndexFullScan_33 | 9990.00 | cop[tikv] | table:c, index:id(id) | keep order:false, stats:pseudo |
| │ └─TableReader_29(Probe) | 9990.00 | root | | data:Selection_28 |
| │ └─Selection_28 | 9990.00 | cop[tikv] | | not(isnull(test.b.id)) |
| │ └─TableFullScan_27 | 10000.00 | cop[tikv] | table:b | keep order:false, stats:pseudo |
| └─Point_Get_14(Probe) | 1.00 | root | table:a, index:id(id) | |
+---------------------------------+----------+-----------+-----------------------+------------------------------------------------+
8 rows in set (0.00 sec)
```
A better and expected plan is
```
IndexJoin
Scan(a) (build)
IndexJoin (probe)
Scan(b) (build)
Scan(c) (probe)
```
Two-level nested queries can use `IndexJoin`:
```
mysql> explain select a.fk_id from a where id=1 and exists (
-> select 1 from b where b.id=a.fk_id);
+-----------------------------+---------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+-----------------------------+---------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------+
| IndexJoin_12 | 0.80 | root | | semi join, inner:IndexReader_11, outer key:test.a.fk_id, inner key:test.b.id, equal cond:eq(test.a.fk_id, test.b.id) |
| ├─Point_Get_21(Build) | 1.00 | root | table:a, index:id(id) | |
| └─IndexReader_11(Probe) | 1.00 | root | | index:Selection_10 |
| └─Selection_10 | 1.00 | cop[tikv] | | not(isnull(test.b.id)) |
| └─IndexRangeScan_9 | 1.00 | cop[tikv] | table:b, index:id(id) | range: decided by [eq(test.b.id, test.a.fk_id)], keep order:false, stats:pseudo |
+-----------------------------+---------+-----------+-----------------------+----------------------------------------------------------------------------------------------------------------------+
```
Contributor guide
Assessment
This issue has not been assessed yet.