# [Known Issues] TiDB SQL 检查 only_full_group_by 时报错 index out of range [ 1]

## Key Words

index out of range [ 1], only_full_group_by

## Description

在 TiDB 默认的 sql_mode 中包含 `only_full_group_by`，在 MySQL 环境上对应 SQL 会报错如下

```sql
mysql> CREATE TABLE `BB` (
    -> `pk` int(11) NOT NULL AUTO_INCREMENT,
    -> `col_int_not_null` int NOT NULL,
    -> PRIMARY KEY (`pk`)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> EXPLAIN
    -> SELECT OUTR.col2 AS X
    ->   FROM
    ->  BB AS OUTR2
    ->  INNER JOIN
    -> (SELECT col_int_not_null AS col1,
    ->      pk AS col2
    ->    FROM BB) AS OUTR ON OUTR2.col_int_not_null = OUTR.col1
    -> GROUP BY OUTR2.col_int_not_null;
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'OUTR.col2' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
```

但是在 TiDB 某些早期版本下，会报错 `index out of range`，如下

```sql
mysql> CREATE TABLE `BB` (
    ->   `pk` int(11) NOT NULL AUTO_INCREMENT,
    ->   `col_int_not_null` int NOT NULL,
    ->   PRIMARY KEY (`pk`)
    -> );
Query OK, 0 rows affected (0.01 sec)
 
mysql> EXPLAIN
    -> SELECT OUTR . col2 AS X
    -> FROM
    ->   BB AS OUTR2
    -> INNER JOIN
    ->   (SELECT col_int_not_null AS col1,
    ->           pk AS col2
    ->    FROM BB) AS OUTR ON OUTR2.col_int_not_null = OUTR.col1
    -> GROUP BY OUTR2.col_int_not_null;
ERROR 1105 (HY000): runtime error: index out of range [-1]
```

同时在 tidb.log 能够找到下面的报错，匹配关键字 `checkColFuncDepend, checkOnlyFullGroupByWithGroupClause, checkOnlyFullGroupBy`

```golang
[err="runtime error: index out of range \[-1]"]
[stack="goroutine 1717811 \[running]:\ngithub.com/pingcap/tidb/server.(\*clientConn).Run.func1(0x4004660, 0xc0032e3680, 0xc002ef3e00)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/server/conn.go:734 +0xee\npanic(0x3799860, 0xc002b83900)
/usr/local/go/src/runtime/panic.go:679 +0x1b2\ngithub.com/pingcap/tidb/planner/core.checkColFuncDepend(0x40595a0, 0xc002c1c600, 0xc002bd3810, 0xc005c9e180, 0xc002c3d5a0, 0xc002c03b00, 0xc002c03b30, 0xc002c3d5a0)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/logical\_plan\_builder.go:2683 +0x7af\ngithub.com/pingcap/tidb/planner/core.(\*PlanBuilder).checkOnlyFullGroupByWithGroupClause(0xc002afb4a0, 0x40595a0, 0xc002c1c600, 0xc000807d00, 0xc005681ea0, 0xc002046f50)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/logical\_plan\_builder.go:2840 +0x906\ngithub.com/pingcap/tidb/planner/core.(\*PlanBuilder).checkOnlyFullGroupBy(0xc002afb4a0, 0x40595a0, 0xc002c1c600, 0xc000807d00, 0xc002c1c600, 0xc002c15ad0)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/logical\_plan\_builder.go:2746 +0x54\ngithub.com/pingcap/tidb/planner/core.(\*PlanBuilder).buildSelect(0xc002afb4a0, 0x4004660, 0xc002c15b00, 0xc000807d00, 0x40595a0, 0xc002c1c600, 0x0, 0x0)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/logical\_plan\_builder.go:3334 +0x1c13\ngithub.com/pingcap/tidb/planner/core.(\*PlanBuilder).Build(0xc002afb4a0, 0x4004660, 0xc002c15b00, 0x40233e0, 0xc000807d00, 0xc002afb4a0, 0xc000e31680, 0x3, 0x3)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/planbuilder.go:626 +0xb37\ngithub.com/pingcap/tidb/planner.optimize(0x4004660, 0xc002c15b00, 0x405dc80, 0xc00319edc0, 0x40233e0, 0xc000807d00, 0x404fae0, 0xc0048b2aa0, 0x0, 0x0, ...)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/optimize.go:234 +0x1e9\ngithub.com/pingcap/tidb/planner.Optimize(0x4004660, 0xc002c15b00, 0x405dc80, 0xc00319edc0, 0x40233e0, 0xc000807d00, 0x404fae0, 0xc0048b2aa0, 0x0, 0x0, ...)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/optimize.go:124 +0x491\ngithub.com/pingcap/tidb/planner/core.(\*PlanBuilder).buildExplain(0xc002afb340, 0x4004660, 0xc002c15b00, 0xc002c321c0, 0x0, 0x5f96f80, 0x13a0328, 0x1a2453d3f0f8e)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/planbuilder.go:3501 +0x111\ngithub.com/pingcap/tidb/planner/core.(\*PlanBuilder).Build(0xc002afb340, 0x4004660, 0xc002c15b00, 0x4021ee0, 0xc002c321c0, 0xc002afb340, 0xc004531200, 0x1, 0x1)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/core/planbuilder.go:607 +0xe0b\ngithub.com/pingcap/tidb/planner.optimize(0x4004660, 0xc002c15b00, 0x405dc80, 0xc00319edc0, 0x4021ee0, 0xc002c321c0, 0x404fae0, 0xc0048b2aa0, 0x0, 0x0, ...)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/optimize.go:234 +0x1e9\ngithub.com/pingcap/tidb/planner.Optimize(0x4004660, 0xc002c15b00, 0x405dc80, 0xc00319edc0, 0x4021ee0, 0xc002c321c0, 0x404fae0, 0xc0048b2aa0, 0x0, 0x0, ...)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/planner/optimize.go:124 +0x491\ngithub.com/pingcap/tidb/executor.(\*Compiler).Compile(0xc002c3ed60, 0x4004660, 0xc002c15b00, 0x402aac0, 0xc002c321c0, 0x0, 0x0, 0x0)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/compiler.go:62 +0x294\ngithub.com/pingcap/tidb/session.(\*session).ExecuteStmt(0xc00319edc0, 0x4004660, 0xc002c15b00, 0x402aac0, 0xc002c321c0, 0x0, 0x0, 0x0, 0x0)
/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/session/session.go:1404 +0x2c4\ngithub.co"]
```

## Root Cause

It's a bug, TiDB throws index-out-of-range error when checking only_full_group_by for a complex sql. 

Please refer this pr, <https://github.com/pingcap/tidb/pull/23844>

## Diagnostic Steps

Please refer to the description.

## Resolution

Upgrade to the v5.0.3 and newer version.

## Workaround

Remove the "only_full_group_by" sql_mode.

## Affected Versions

v5.0.0 - v5.0.2

v4.0.0 - v4.0.16