# [KB]关于 TiDB 执行 SQL 报 index out of range

## Issue

在 Join 连接 子查询的场景下，子查询中包含 order by 和 limit 将报错 

如下

```sql
mysql> create table t (id int, value decimal(10,5));
Query OK, 0 rows affected (0.01 sec)

mysql> desc format = 'brief' select count(\*) from t join (select t.id, t.value v1 from t join t t1 on t.id = t1.id order by t.value limit 1) v on v.id = t.id and v.v1 = t.value;
ERROR 1105 (HY000): runtime error: index out of range [4] with length 4
```

## Root Cause

v5.0 引入的 TiDB 优化器 bug

```
for i, col := range p.Schema().Columns {
  if !col.Equal(nil, originalSchema.Columns\[i]) {
    schemaChanged = true
    break
  }
}
```

代码会判断 主表与外表是否有不相同的 colums，如果有则退出

## Diagnostic Steps

* 检查是否为 join 连接

* join 连接 主表与外表是否为同一个表，并且进行了改写

* 检查 TiDB log 相关栈信息中是否包含  **rule_join_reorder**  关键字

```golang
[2021/04/19 09:49:51.672 +08:00] [ERROR] [conn.go:802] ["connection running loop panic"] [conn=3] [lastSQL="desc format = 'brief' select count(\*) from t join (select t.id, t.value v1 from t join t t1 on t.id = t1.id order by t.value limit 1) v on v.id = t.id and v.v1 = t.value"] [err="runtime error: index out of range [4] with length 4"] [stack="goroutine 531 [running]:\ngithub.com/pingcap/tidb/server.(\*clientConn).Run.func1(0x6c23780, 0xc00f71bf50, 0xc000299000)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/server/conn.go:800 +0xee\npanic(0x63cea20, 0xc010dfcc60)\n\t/Users/xuhuaiyu/.gvm/gos/go1.13/src/runtime/panic.go:679 +0x1b2\ngithub.com/pingcap/tidb/planner/core.(\*joinReOrderSolver).optimizeRecursive(0x896ec60, 0x6c7c880, 0xc00fc5cb40, 0x6c77bc0, 0xc000447340, 0x0, 0x0, 0x0, 0x0)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/rule_join_reorder.go:95 +0xa4d\ngithub.com/pingcap/tidb/planner/core.(\*joinReOrderSolver).optimizeRecursive(0x896ec60, 0x6c7c880, 0xc00fc5cb40, 0x6c778c0, 0xc010e1d2c0, 0x0, 0x0, 0x0, 0x0)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/rule_join_reorder.go:112 +0x894\ngithub.com/pingcap/tidb/planner/core.(\*joinReOrderSolver).optimizeRecursive(0x896ec60, 0x6c7c880, 0xc00fc5cb40, 0x6c781c0, 0xc010baf880, 0xc010baf800, 0xc00f704360, 0x62f8340, 0xc00f704360)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/rule_join_reorder.go:112 +0x894\ngithub.com/pingcap/tidb/planner/core.(\*joinReOrderSolver).optimize(0x896ec60, 0x6c23780, 0xc010d5b7d0, 0x6c781c0, 0xc010baf880, 0x6c781c0, 0xc010baf880, 0x0, 0x0)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/rule_join_reorder.go:58 +0x5a\ngithub.com/pingcap/tidb/planner/core.logicalOptimize(0x6c23780, 0xc010d5b7d0, 0x3cf6, 0x6c781c0, 0xc010baf880, 0x607ce1bf, 0xc010777bf8, 0x408c806, 0x607ce1bf)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/optimizer.go:206 +0x116\ngithub.com/pingcap/tidb/planner/core.DoOptimize(0x6c23780, 0xc010d5b7d0, 0x6c7c880, 0xc00fc5cb40, 0x1cf6, 0x6c781c0, 0xc010baf880, 0x0, 0x0, 0x0, ...)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/optimizer.go:140 +0x77\ngithub.com/pingcap/tidb/planner.optimize(0x6c23780, 0xc010d5b7d0, 0x6c7c880, 0xc00fc5cb40, 0x6c42060, 0xc01087fa00, 0x6c6eba0, 0xc010ae3a40, 0x0, 0x0, ...)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/optimize.go:289 +0x66c\ngithub.com/pingcap/tidb/planner.Optimize(0x6c23780, 0xc010d5b7d0, 0x6c7c880, 0xc00fc5cb40, 0x6c42060, 0xc01087fa00, 0x6c6eba0, 0xc010ae3a40, 0x0, 0x0, ...)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/optimize.go:125 +0x494\ngithub.com/pingcap/tidb/planner/core.(\*PlanBuilder).buildExplain(0xc010bc1760, 0x6c23780, 0xc010d5b7d0, 0xc010b11e00, 0x100000001, 0xc0107783d0, 0x4055868, 0xf7ea041f7ca3)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/planbuilder.go:3525 +0x111\ngithub.com/pingcap/tidb/planner/core.(\*PlanBuilder).Build(0xc010bc1760, 0x6c23780, 0xc010d5b7d0, 0x6c40b60, 0xc010b11e00, 0xc010bc1760, 0xc010a4bb80, 0x1, 0x1)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/core/planbuilder.go:610 +0xe0b\ngithub.com/pingcap/tidb/planner.optimize(0x6c23780, 0xc010d5b7d0, 0x6c7c880, 0xc00fc5cb40, 0x6c40b60, 0xc010b11e00, 0x6c6eba0, 0xc010ae3a40, 0x0, 0x0, ...)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/optimize.go:235 +0x1e9\ngithub.com/pingcap/tidb/planner.Optimize(0x6c23780, 0xc010d5b7d0, 0x6c7c880, 0xc00fc5cb40, 0x6c40b60, 0xc010b11e00, 0x6c6eba0, 0xc010ae3a40, 0x0, 0x0, ...)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/planner/optimize.go:125 +0x494\ngithub.com/pingcap/tidb/executor.(\*Compiler).Compile(0xc010778d60, 0x6c23780, 0xc010d5b7d0, 0x6c481e0, 0xc010b11e00, 0x0, 0x0, 0x0)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/executor/compiler.go:62 +0x294\ngithub.com/pingcap/tidb/session.(\*session).ExecuteStmt(0xc00fc5cb40, 0x6c23780, 0xc010d5b7d0, 0x6c481e0, 0xc010b11e00, 0x0, 0x0, 0x0, 0x0)\n\t/Users/xuhuaiyu/Development/GOPATH/src/github.com/pingcap/tidb/session/session.go:1409 +0x2c4\ngithub.com/pingcap/tidb/server.(\*TiDBContext).ExecuteStmt(0xc00fbe7a70, 0x6c23780, 0xc010d5b7d0, 0x6c481e0, 0xc010b11e00, 0xc010dfc8c0, 0x6c23780, 0xc010d5b7d0, 0xc010779130)\n\t"]
```

## Resolution

* [升级](https://docs.pingcap.com/zh/tidb/stable/upgrade-tidb-using-tiup#%E4%BD%BF%E7%94%A8-tiup-%E5%8D%87%E7%BA%A7-tidb) 到v5.0.6 

## Workaround

* 业务逻辑拆分先查询子查询部分再查询 SQL 主体

```sql
select t.id, t.value v1 from t order by t.value limit 1;

select count(1) from t where id={t_id} and value={t_value};
```

* SQL 改写

```sql
select \* from t join (select [t.id](http://t.id), t.value v1 from t join t t1 on [t.id](http://t.id) = [t1.id](http://t1.id) ) v on [v.id](http://v.id) = [t.id](http://t.id) and v.v1 = t.value order by v.value limit 1;
```