# [KB]关于 TiDB 的 SQL 包含 prefix index 并且执行计划选择 index join 时报错 index out of range [2] with length 1

## Issue

SQL 包含 prefix index(前缀索引) 并且执行计划选择 index join 时报错 index out of range \[2] with length 1

```sql
create table `t1` (
`id` int unsigned key,
`sid` varchar(50)
);
create table `t2` (
`id` int unsigned key,
`done` datetime,
`sid` varchar(50),
KEY `idx_sid` (`sid`(5))
);
insert into t1 values (1, '123');
insert into t2 values (1, '2021-10-2', '123');
select /\*+ TIDB_INLJ(t1, t2) \*/ \* from t1 left join t2 
on t2.sid = t1.sid
where t2.done between '2021-10-01' and '2021-10-31'
limit 1;
ERROR 1105 (HY000): runtime error: index out of range [2] with length 1
```

## Root Cause

Github Issue：[https://github.com/pingcap/tidb/issues/24547](https://github.com/pingcap/tidb/issues/24547)

使用 prefix index(前缀索引) 和 Index Join 导致的 panic

## Diagnostic Steps

1. 查看 SQL 语句的执行计划是否包含 index join，执行 explain SQL 查看

2. 查看 SQL 中是否使用到了 prefix index，结合表结构查看

3. 查看 tidb.log 日志，错误栈中是否包含关键字 constructLookupContent，具体日志类似如下日志

```golang
[err="runtime error: index out of range \[2] with length 1\ngithub.com/pingcap/tidb/executor.(\*innerWorker).run.func1
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:476\nruntime.gopanic
usr/local/go/src/runtime/panic.go:679\nruntime.goPanicIndex
usr/local/go/src/runtime/panic.go:75\ngithub.com/pingcap/tidb/executor.(\*innerWorker).constructLookupContent
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:556\ngithub.com/pingcap/tidb/executor.(\*innerWorker).handleTask
home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:508\ngithub.com/pingcap/tidb/executor.(\*innerWorker).run
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:491\nruntime.goexit
/usr/local/go/src/runtime/asm_amd64.s:1357"]
```

## Resolution

升级到 v4.0.16（预计） 和 v5.0.2 及之后版本

PR：[https://github.com/pingcap/tidb/pull/24568](https://github.com/pingcap/tidb/pull/24568)

## Workaround

可以尝试使用 [hash join](https://docs.pingcap.com/zh/tidb/v4.0/optimizer-hints#hash_joint1_name--tl_name-) 或者 [merge join](https://docs.pingcap.com/zh/tidb/v4.0/optimizer-hints#merge_joint1_name--tl_name-) 来暂时处理。

> **注意** 
> 
> 请结合业务数据量评估消耗，避免资源占用过多，影响业务