# [Known Issues] SQL 包含子查询时，执行计划无法解关联，只能使用 Apply 算子

## Key Words

apply, 解关联

## Description

1.建表语句

```sql
CREATE TABLE `t1` (
  `a` varchar(32) NOT NULL ,
  `b` varchar(32) DEFAULT NULL ,
  `c` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`a`) /*T![clustered_index] NONCLUSTERED */,
  KEY `INDEX_B` (`b`),
  KEY `IDX_C` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*T! SHARD_ROW_ID_BITS=4 */;

CREATE TABLE `t2` (
  `ID_A` varchar(32) NOT NULL,
  `NAME` varchar(200) DEFAULT NULL,
  PRIMARY KEY (`ID_A`) /*T![clustered_index] NONCLUSTERED */,
  KEY `IDX_NAME` (`NAME`)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
```

2.查询语句

```sql
explain SELECT count(0) from  (SELECT t1.b,(SELECT t2.NAME FROM t2 WHERE t2.ID_A = t1.c) AS cname FROM t1) a;
```

3.在 v4.0.14 中查看执行计划可以解关联，走 merge join

```sql
+-------------------------------+----------+-----------+-------------------------------+-------------------------------------------------------------+
| id                            | estRows  | task      | access object                 | operator info                                               |
+-------------------------------+----------+-----------+-------------------------------+-------------------------------------------------------------+
| HashAgg_12                    | 1.00     | root      |                               | funcs:count(0)->Column#11                                   |
| └─MergeJoin_14                | 12500.00 | root      |                               | left outer join, left key:test.t1.c, right key:test.t2.id_a |
|   ├─IndexReader_25(Build)     | 10000.00 | root      |                               | index:IndexFullScan_24                                      |
|   │ └─IndexFullScan_24        | 10000.00 | cop[tikv] | table:t2, index:PRIMARY(ID_A) | keep order:true, stats:pseudo                               |
|   └─IndexReader_23(Probe)     | 10000.00 | root      |                               | index:IndexFullScan_22                                      |
|     └─IndexFullScan_22        | 10000.00 | cop[tikv] | table:t1, index:IDX_C(c)      | keep order:true, stats:pseudo                               |
+-------------------------------+----------+-----------+-------------------------------+-------------------------------------------------------------+
```

4.在 v5.4.1 中无法解关联，只能走 apply 算子

```sql
+-------------------------------+----------+-----------+-------------------------------+---------------------------------------------------------------------------------+
| id                            | estRows  | task      | access object                 | operator info                                                                   |
+-------------------------------+----------+-----------+-------------------------------+------------------------------------------------------------------
| HashAgg_12                    | 1.00     | root      |                               | funcs:count(0)->Column#11                                                       |
| └─Apply_15                    | 10000.00 | root      |                               | CARTESIAN left outer join                                                       |
|   ├─IndexReader_19(Build)     | 10000.00 | root      |                               | index:IndexFullScan_18                                                          |
|   │ └─IndexFullScan_18        | 10000.00 | cop[tikv] | table:t1, index:IDX_C(c)      | keep order:false, stats:pseudo                                                  |
|   └─IndexReader_21(Probe)     | 10.00    | root      |                               | index:IndexRangeScan_20                                                         |
|     └─IndexRangeScan_20       | 10.00    | cop[tikv] | table:t2, index:PRIMARY(ID_A) | range: decided by [eq(test.t2.id_a, test.t1.c)], keep order:false, stats:pseudo |
+-------------------------------+----------+-----------+-------------------------------+-----------------------------------------------------------------
```

## Root Cause

[PR 32370](https://github.com/pingcap/tidb/pull/32370) 的修改导致, 遗漏了一些场景, 导致一些可以解关联的情况被禁止了  
问题记录在 [Issue 35985](https://github.com/pingcap/tidb/issues/35985)

## Diagnostic Steps

1.SQL 执行计划无法解关联,即使加 hint 也无法成功

## Resolution

在 [PR 36073](https://github.com/pingcap/tidb/pull/36073) 中修复

## Workaround

改写查询, 使得 apply 算子能够顺利解关联.  
核心改写的方式是使得表 t2 外层的部分能用上 a.cname 列。给出的改写方案是加上和这列无关的 where 条件，比如

```sql
explain SELECT count(0) from (SELECT t1.b,(SELECT t2.NAME FROM t2 WHERE t2.ID_A = t1.c) AS cname FROM t1) a where a.cname is null and a.cname is not null;

+--------------------------------+----------+-----------+--------------------------+-------------------------------------------------+
| id                             | estRows  | task      | access object            | operator info                                   |
+--------------------------------+----------+-----------+--------------------------+-------------------------------------------------+
| HashAgg_13                     | 1.00     | root      |                          | funcs:count(0)->Column#11                       |
| └─HashJoin_41                  | 12487.50 | root      |                          | inner join, equal:[eq(test.t1.c, test.t2.id_a)] |
|   ├─TableReader_54(Build)      | 9990.00  | root      |                          | data:Selection_53                               |
|   │ └─Selection_53             | 9990.00  | cop[tikv] |                          | not(isnull(test.t2.name))                       |
|   │   └─TableFullScan_52       | 10000.00 | cop[tikv] | table:t2                 | keep order:false, stats:pseudo                  |
|   └─IndexReader_51(Probe)      | 9990.00  | root      |                          | index:IndexFullScan_50                          |
|     └─IndexFullScan_50         | 9990.00  | cop[tikv] | table:t1, index:IDX_C(c) | keep order:false, stats:pseudo                  |
+--------------------------------+----------+-----------+--------------------------+-------------------------------------------------+
```

## Affected Versions

[5.2.4,5.2.x],[5.3.2,5.3.x],[5.4.1,5.4.x],6.0.x,6.1.x