Semi join can be eliminated and transformed to normal join in some cases.

## Enhancement

Suppose that we have the SQL `select * from t1 where exists(select 1 from t2 where t1.a=t2.a and exists(select 1 from t3 where t2.b=t3.b))`.

The current execution plan is like
```
mysql> explain select * from t1 where exists(select 1 from t2 where t1.a=t2.a and exists(select 1 from t3 where t2.b=t3.b));
+---------------------------------+----------+-----------+------------------------+------------------------------------------------+
| id | estRows | task | access object | operator info |
+---------------------------------+----------+-----------+------------------------+------------------------------------------------+
| HashJoin_14 | 7992.00 | root | | semi join, equal:[eq(test.t1.a, test.t2.a)] |
| ├─HashJoin_45(Build) | 7984.01 | root | | semi join, equal:[eq(test.t2.b, test.t3.b)] |
| │ ├─IndexReader_54(Build) | 9990.00 | root | | index:IndexFullScan_53 |
| │ │ └─IndexFullScan_53 | 9990.00 | cop[tikv] | table:t3, index:idx(b) | keep order:false, stats:pseudo |
| │ └─TableReader_48(Probe) | 9980.01 | root | | data:Selection_47 |
| │ └─Selection_47 | 9980.01 | cop[tikv] | | not(isnull(test.t2.a)), not(isnull(test.t2.b)) |
| │ └─TableFullScan_46 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─IndexReader_33(Probe) | 9990.00 | root | | index:IndexFullScan_32 |
| └─IndexFullScan_32 | 9990.00 | cop[tikv] | table:t1, index:idx(a) | keep order:false, stats:pseudo |
+---------------------------------+----------+-----------+------------------------+------------------------------------------------+
9 rows in set (0.01 sec)
```

As you can see, we need first to join (t2, t3), then let them be joined with t1.

Suppose that there's a filter on t1, like the following example:
```
mysql> explain select * from t1 where exists(select 1 from t2 where t1.a=t2.a and exists(select 1 from t3 where t2.b=t3.b)) and t1.b=10 and t1.c=10;
+----------------------------------+----------+-----------+---------------------------+-----------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------+----------+-----------+---------------------------+-----------------------------------------------------+
| HashJoin_13 | 0.08 | root | | semi join, equal:[eq(test.t1.a, test.t2.a)] |
| ├─HashJoin_32(Build) | 7984.01 | root | | semi join, equal:[eq(test.t2.b, test.t3.b)] |
| │ ├─IndexReader_41(Build) | 9990.00 | root | | index:IndexFullScan_40 |
| │ │ └─IndexFullScan_40 | 9990.00 | cop[tikv] | table:t3, index:idx(b) | keep order:false, stats:pseudo |
| │ └─TableReader_35(Probe) | 9980.01 | root | | data:Selection_34 |
| │ └─Selection_34 | 9980.01 | cop[tikv] | | not(isnull(test.t2.a)), not(isnull(test.t2.b)) |
| │ └─TableFullScan_33 | 10000.00 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─IndexLookUp_20(Probe) | 0.10 | root | | |
| ├─IndexRangeScan_17(Build) | 0.10 | cop[tikv] | table:t1, index:idx(b, c) | range:[10 10,10 10], keep order:false, stats:pseudo |
| └─Selection_19(Probe) | 0.10 | cop[tikv] | | not(isnull(test.t1.a)) |
| └─TableRowIDScan_18 | 0.10 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
+----------------------------------+----------+-----------+---------------------------+-----------------------------------------------------+
11 rows in set (0.00 sec)
```

The row number of t1 after filtering is relatively small. And t1 can use index join to join t2 then index join t3. If so, the scanned rows will be reduced significantly.

But since this is semi-join. We always need to keep t1's data not increasing.

If we first join t1 and t2, to prevent t1's data from being enlarged, we need to group the result by t1.a(t2.a). But t2.a and t2.b have no functional dependency between them. So group by will lose data for t2.b.

If we don't group by t1.a(t2.a). After the join finished, we cannot guarantee that t1's data have not been increased.

Compared with others and did some tries.
We come up with the idea that we can append an extra row for t1. Then do a final grouping on (t1.a, extra row).
The following is a rewrite example for `select * from t1 where exists(select 1 from t2 where t1.a=t2.a and exists(select 1 from t3 where t2.b=t3.b)) and t1.b=10 and t1.c=10`.

```
mysql> explain select t1.* from (select *, row_number() over() as rn from t1 where t1.b=10 and t1.c=10) t1, t2, t3 where t1.a=t2.a and t3.b=t2.b group by t1.rn;
+----------------------------------------------+---------+-----------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| id | estRows | task | access object | operator info |
+----------------------------------------------+---------+-----------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| HashAgg_18 | 1.00 | root | | group by:Column#6, funcs:firstrow(test.t1.a)->test.t1.a, funcs:firstrow(test.t1.b)->test.t1.b, funcs:firstrow(test.t1.c)->test.t1.c, funcs:firstrow(Column#6)->Column#6 |
| └─IndexHashJoin_24 | 0.13 | root | | inner join, inner:IndexReader_21, outer key:test.t2.b, inner key:test.t3.b, equal cond:eq(test.t2.b, test.t3.b) |
| ├─IndexHashJoin_39(Build) | 0.10 | root | | inner join, inner:IndexLookUp_36, outer key:test.t1.a, inner key:test.t2.a, equal cond:eq(test.t1.a, test.t2.a) |
| │ ├─Selection_50(Build) | 0.08 | root | | not(isnull(test.t1.a)) |
| │ │ └─Window_51 | 0.10 | root | | row_number()->Column#6 over(rows between current row and current row) |
| │ │ └─IndexLookUp_57 | 0.10 | root | | |
| │ │ ├─IndexRangeScan_55(Build) | 0.10 | cop[tikv] | table:t1, index:idx(b, c) | range:[10 10,10 10], keep order:false, stats:pseudo |
| │ │ └─TableRowIDScan_56(Probe) | 0.10 | cop[tikv] | table:t1 | keep order:false, stats:pseudo |
| │ └─IndexLookUp_36(Probe) | 0.10 | root | | |
| │ ├─Selection_34(Build) | 0.10 | cop[tikv] | | not(isnull(test.t2.a)) |
| │ │ └─IndexRangeScan_32 | 0.10 | cop[tikv] | table:t2, index:idx(a) | range: decided by [eq(test.t2.a, test.t1.a)], keep order:false, stats:pseudo |
| │ └─Selection_35(Probe) | 0.10 | cop[tikv] | | not(isnull(test.t2.b)) |
| │ └─TableRowIDScan_33 | 0.10 | cop[tikv] | table:t2 | keep order:false, stats:pseudo |
| └─IndexReader_21(Probe) | 0.13 | root | | index:Selection_20 |
| └─Selection_20 | 0.13 | cop[tikv] | | not(isnull(test.t3.b)) |
| └─IndexRangeScan_19 | 0.13 | cop[tikv] | table:t3, index:idx(b) | range: decided by [eq(test.t3.b, test.t2.b)], keep order:false, stats:pseudo |
+----------------------------------------------+---------+-----------+---------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
16 rows in set (0.01 sec)
```

In this way, we can prevent the data explosion of the t1 side and fully use the index to seek the data in t2 and t3.