# [Know Issue] 简单子查询无法被 TiDB Rewrite 成 Join

## Key Words

subquery, TiDB, optimizer, semi join, index join, inner join

## Description

TiDB server 运行简单子查询 select * from t1 where t1.a in (select t2.a from t2) 执行计划一直走的 semi join，并且使用 hint 也无法生效：

```sql
explain  select /*+ INL_JOIN(t1)*/* from t1 where t1.a in (select t2.a from t2) ;
+------------------------------+----------+-----------+--------------------------+----------------------------------------------------------+
| id                           | estRows  | task      | access object            | operator info                                            |
+------------------------------+----------+-----------+--------------------------+----------------------------------------------------------+
| HashJoin_25                  | 9990.00  | root      |                          | semi join, equal:[eq(test.t1.a, test.t2.a)]             |
| ├─HashAgg_40(Build)          | 7992.00  | root      |                          | group by:test.t2.a, funcs:firstrow(test.t2.a)->test.t2.a |
| │ └─IndexReader_47           | 9990.00  | root      |                          | index:IndexFullScan_46                                   |
| │   └─IndexFullScan_46       | 9990.00  | cop[tikv] | table:t2, index:idx_a(a) | keep order:false, stats:pseudo                           |
| └─TableReader_52(Probe)      | 9990.00  | root      |                          | data:Selection_51                                        |
|   └─Selection_51             | 9990.00  | cop[tikv] |                          | not(isnull(test.t1.a))                                   |
|     └─TableFullScan_50       | 10000.00 | cop[tikv] | table:t1                 | keep order:false, stats:pseudo                           |
+------------------------------+----------+-----------+--------------------------+----------------------------------------------------------+
7 rows in set (0.09 sec)

```

期望的执行计划：

```sql
+----------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------+
| id                               | estRows | task      | access object            | operator info                                                                                                   |
+----------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------+
| IndexJoin_14                     | 9990.00 | root      |                          | inner join, inner:IndexLookUp_13, outer key:test.t2.a, inner key:test.t1.a, equal cond:eq(test.t2.a, test.t1.a) |
| ├─HashAgg_26(Build)              | 7992.00 | root      |                          | group by:test.t2.a, funcs:firstrow(test.t2.a)->test.t2.a                                                        |
| │ └─IndexReader_33               | 9990.00 | root      |                          | index:IndexFullScan_32                                                                                          |
| │   └─IndexFullScan_32           | 9990.00 | cop[tikv] | table:t2, index:idx_a(a) | keep order:false, stats:pseudo                                                                                  |
| └─IndexLookUp_13(Probe)          | 1.25    | root      |                          |                                                                                                                 |
|   ├─Selection_12(Build)          | 1.25    | cop[tikv] |                          | not(isnull(test.t1.a))                                                                                          |
|   │ └─IndexRangeScan_10          | 1.25    | cop[tikv] | table:t1, index:idx_a(a) | range: decided by [eq(test.t1.a, test.t2.a)], keep order:false, stats:pseudo                                    |
|   └─TableRowIDScan_11(Probe)     | 1.25    | cop[tikv] | table:t1                 | keep order:false, stats:pseudo                                                                                  |
+----------------------------------+---------+-----------+--------------------------+-----------------------------------------------------------------------------------------------------------------+
8 rows in set (0.00 sec)
```

## Root Cause

关联列的排序类型不一致，TiDB 目前还不支持对不同排序规则的表或者列进行优化器的 rewrite。因此在做 rewrite 前会对关联列的排序规则做[判断](https://github.com/pingcap/tidb/blob/master/planner/core/expression_rewriter.go#L984-L999)。一旦发现关联列排序规则不一致，优化器就会拒绝将子查询改写为 join，最终执行计划无法选择更优的普通 inner join。

## Diagnostic Steps

N/A

## Resolution

N/A

## Workaround

N/A

## Affected Versions

ALL