# [Know Issue] 标量子查询无法使用索引

## Key Words

标量子查询, 索引

## Description

对于 t2.col1=ifnull(t1.col1,t1.col2) 的关联条件，子查询 col1 列的数据过滤无法使用列上的索引，即期望 TableFullScan_15 算子是 IndexRangeScan，具体 Plan:

```sql
MySQL [test]> explain select t1.id,(select t2.id from t2 where t2.col1=ifnull(t1.col1,t1.col2)) from t1 where id=1 ;
+--------------------------------+---------+-----------+---------------+------------------------------------------------------+
| id                             | estRows | task      | access object | operator info                                        |
+--------------------------------+---------+-----------+---------------+------------------------------------------------------+
| Projection_10                  | 1.00    | root      |               | test.t1.id, test.t2.id                               |
| └─Apply_12                     | 1.00    | root      |               | CARTESIAN left outer join                            |
|   ├─Point_Get_13(Build)        | 1.00    | root      | table:t1      | handle:1                                             |
|   └─MaxOneRow_14(Probe)        | 1.00    | root      |               |                                                      |
|     └─TableReader_17           | 2.00    | root      |               | data:Selection_16                                    |
|       └─Selection_16           | 2.00    | cop[tikv] |               | eq(test.t2.col1, ifnull(test.t1.col1, test.t1.col2)) |
|         └─TableFullScan_15     | 2.50    | cop[tikv] | table:t2      | keep order:false, stats:pseudo                       |
+--------------------------------+---------+-----------+---------------+------------------------------------------------------+
7 rows in set (0.00 sec)

```

## Root Cause

目前 TiDB 优化器存在限制，对于标量子查询外表传入的列必须是个正常未被转换的列，即没有复杂的函数处理，比如: ifnull 函数或者隐式转换。[参考代码](https://github.com/pingcap/tidb/blob/master/planner/core/logical_plans.go#L1420)。

## Diagnostic Steps

N/A

## Resolution

N/A

## Workaround

改写 SQL 将标量子查询关联列的函数处理放到外表提前处理，类似如下方式：

```sql
explain select v.id,(select t2.id from t2 where t2.col1=v.col1) from (select t1.id,ifnull(t1.col1,t1.col2) col1 from t1) v where v.id=1;
+-------------------------------+---------+-----------+-----------------------------------+--------------------------------------------------------------------------------+
| id                            | estRows | task      | access object                     | operator info                                                                  |
+-------------------------------+---------+-----------+-----------------------------------+--------------------------------------------------------------------------------+
| Projection_11                 | 1.00    | root      |                                   | test.t1.id, test.t2.id                                                         |
| └─Apply_13                    | 1.00    | root      |                                   | CARTESIAN left outer join                                                      |
|   ├─Projection_14(Build)      | 1.00    | root      |                                   | test.t1.id, ifnull(test.t1.col1, test.t1.col2)->Column#4                       |
|   │ └─Point_Get_15            | 1.00    | root      | table:t1                          | handle:1                                                                       |
|   └─MaxOneRow_16(Probe)       | 1.00    | root      |                                   |                                                                                |
|     └─IndexReader_18          | 2.00    | root      |                                   | index:IndexRangeScan_17                                                        |
|       └─IndexRangeScan_17     | 2.00    | cop[tikv] | table:t2, index:idx_t2_col1(col1) | range: decided by [eq(test.t2.col1, Column#4)], keep order:false, stats:pseudo |
+-------------------------------+---------+-----------+-----------------------------------+--------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
```

## Affected Versions

ALL