build anti semi join for NOT EXISTS instead of left outer semi join with NOT selection

[eurekaka]

Feature Request

Is your feature request related to a problem? Please describe:

In current master branch, NOT EXISTS is implemented as left outer semi join with a NOT selection above it.

mysql> explain select * from t1 where not exists (select * from t2 where t1.a = t2.a);
+----------------------------+----------+------+------------------------------------------------------------------------------+
| id                         | count    | task | operator info                                                                |
+----------------------------+----------+------+------------------------------------------------------------------------------+
| Projection_8               | 8000.00  | root | test.t1.id, test.t1.a, test.t1.b                                             |
| └─Selection_9              | 8000.00  | root | not(6_aux_0)                                                                 |
|   └─HashLeftJoin_10        | 10000.00 | root | left outer semi join, inner:TableReader_14, equal:[eq(test.t1.a, test.t2.a)] |
|     ├─TableReader_12       | 10000.00 | root | data:TableScan_11                                                            |
|     │ └─TableScan_11       | 10000.00 | cop  | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo                  |
|     └─TableReader_14       | 10000.00 | root | data:TableScan_13                                                            |
|       └─TableScan_13       | 10000.00 | cop  | table:t2, range:[-inf,+inf], keep order:false, stats:pseudo                  |
+----------------------------+----------+------+------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

while NOT IN is implemented as anti semi join:

mysql> explain select * from t1 where t1.a not in (select a from t2);
+-----------------------+----------+------+------------------------------------------------------------------------+
| id                    | count    | task | operator info                                                          |
+-----------------------+----------+------+------------------------------------------------------------------------+
| HashLeftJoin_8        | 8000.00  | root | anti semi join, inner:TableReader_12, equal:[eq(test.t1.a, test.t2.a)] |
| ├─TableReader_10      | 10000.00 | root | data:TableScan_9                                                       |
| │ └─TableScan_9       | 10000.00 | cop  | table:t1, range:[-inf,+inf], keep order:false, stats:pseudo            |
| └─TableReader_12      | 10000.00 | root | data:TableScan_11                                                      |
|   └─TableScan_11      | 10000.00 | cop  | table:t2, range:[-inf,+inf], keep order:false, stats:pseudo            |
+-----------------------+----------+------+------------------------------------------------------------------------+
5 rows in set (0.00 sec)

These 2 are semantically equal, if we convert NOT EXISTS to anti semi join, we can apply more optimizations later such as constant propagation compared with outer join.

Describe the feature you'd like:

Implement NOT EXISTS as anti semi join.

Describe alternatives you've considered:

N/A

Teachability, Documentation, Adoption, Migration Strategy:

N/A