ERROR 1105 (HY000): runtime error: index out of range [1] with length 1

db_user · 2021 年5 月 19 日 06:46

为提高效率，请提供以下信息，问题描述清晰能够更快得到解决：

【TiDB 版本】
v5.0.1
【问题描述】
一条select group by查询报错
ERROR 1105 (HY000): runtime error: index out of range [1] with length 1

若提问为性能优化、故障排查类问题，请下载脚本运行。终端输出的打印结果，请务必全选并复制粘贴上传。

Kongdom · 2021 年5 月 19 日 07:03

请提供完整的sql语句，最好能提供建表及查询语句

spc_monkey · 2021 年5 月 19 日 07:16

1、这个咱们是生产环境吗，现在的影响大吗，这个应该是产品问题，可能需要先看看能不能找到临时解决办法，最终解决办法，可能需要先好好分析一下了
2、除了提供表结构，语句，最好这个表多大，这个语句的结果集多大都提供一下

Kongdom · 2021 年5 月 19 日 07:21

根据我痛苦的兼容5.0版本的心路历程，大多数是语法问题，和表大小关系不大。
感觉5.0对语法规则突然就严格起来了

spc_monkey · 2021 年5 月 19 日 07:28

咱们是测试 5.0 了？感觉咱们有一些体验，这个能帮整理反馈一下不（可以发在这个 asktug 上），比如上面说的语法规则、兼容问题等，给你加积分咋样

spc_monkey · 2021 年5 月 19 日 08:01

另外，帮提供一下，tidb的日志，尤其是 tidb_stderr.log

db_user · 2021 年5 月 19 日 08:24

稍等，我稍微整理下，因为是我司有机密性要求，所以我自己弄张表测试下

db_user · 2021 年5 月 19 日 08:24

稍等，我自己弄张表，然后把完整过程发下

spc_monkey · 2021 年5 月 19 日 08:26

好的，感谢

Kongdom · 2021 年5 月 19 日 08:44

好的，感谢

spc_monkey · 2021 年5 月 19 日 10:25

整理出来后，可以提示我一下哈，我这边有结果也会尽快在这里同步

yilong · 2021 年5 月 19 日 10:25

麻烦上传错误栈信息吧，可以排除下是否已知问题。 tidb.log 中的error 信息。

db_user · 2021 年5 月 19 日 10:32

错误是这个
[“command dispatched failed”] [conn=295297] [connInfo=“id:295297, addr:********:56438 status:10, collation:utf8_general_ci, user:root”] [command=Query] [status=“inTxn:0, autocommit:1”] [sql=“select m1.b,m1.a, sum(m1.amount) as net_amount from test_1 m1 left join test_2 m2 on m1.a=m2.a and m2.b=date(m1.b) group by 1,2”] [txn_mode=PESSIMISTIC] [err=“runtime error: index out of range [1] with length 1\ngithub.com/pingcap/tidb/executor.(*innerWorker).run.func1\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:454\ runtime.gopanic\ \t/usr/local/go/src/runtime/panic.go:679\ runtime.goPanicIndex\ \t/usr/local/go/src/runtime/panic.go:75\ngithub.com/pingcap/tidb/executor.buildKvRangesForIndexJoin\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3639\ github.com/pingcap/tidb/executor.(*dataReaderBuilder).buildIndexReaderForIndexJoin\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3468\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoinInternal\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3209\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoin\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3200\ngithub.com/pingcap/tidb/executor.(*innerWorker).fetchInnerResults\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:647\ngithub.com/pingcap/tidb/executor.(*innerWorker).handleTask\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:491\ngithub.com/pingcap/tidb/executor.(*innerWorker).run\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:469\ runtime.goexit\ \t/usr/local/go/src/runtime/asm_amd64.s:1357”]

db_user · 2021 年5 月 19 日 10:33

复现涉及到数据的问题，我把表结构先给你们，你们可以加些数据看看
CREATE TABLE test_1 (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
a varchar(50) NOT NULL,
amount varchar(50) NOT NULL,
b varchar(50), PRIMARY KEY (id)
)

CREATE TABLE test_2 (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
b date DEFAULT NULL,
a varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL,
c varchar(30) COLLATE utf8mb4_general_ci DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uni_idx_bac (b,a,c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

查询报错：select
m1.b,m1.a,
sum(m1.amount) as net_amount
from test_1 m1
left join test_2 m2 on m1.a=m2.a and m2.b=date(m1.b)
group by 1,2

db_user · 2021 年5 月 19 日 10:37

复现需要数据，我用原来的数据就会一直报错，要是少量数据就是偶发报错，比较奇怪，原始数据test_1表172行，test_2表23419行

db_user · 2021 年5 月 19 日 10:47

1.inner join不会报错，left join报错
2.用left join，去掉and m2.b=date(m1.b)不会报错
3.用left join，去掉sum(m1.amount)不会报错

db_user · 2021 年5 月 19 日 10:51

[2021/05/19 10:21:26.878 +00:00] [ERROR] [index_lookup_join.go:452] [“innerWorker panicked”] [conn=295297] [stack=“goroutine 96059941 [running]:\ngithub.com/pingcap/tidb/executor.(*innerWorker).run.func1(0x40045a0, 0xc00af0cbc0, 0xc00119bf38, 0xc03c1807b0)\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:450 +0xd3\ panic(0x3799860, 0xc00a18a160)\ \t/usr/local/go/src/runtime/panic.go:679 +0x1b2\ngithub.com/pingcap/tidb/executor.buildKvRangesForIndexJoin(0x405dc80, 0xc00b866f00, 0xb1e, 0x1, 0xc00081c500, 0x2c, 0x4c, 0xc0000f35c8, 0x1, 0x1, …)\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3639 +0xfca\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildIndexReaderForIndexJoin(0xc000dddb00, 0x40045a0, 0xc00af0cbc0, 0xc004c71320, 0xc00081c500, 0x2c, 0x4c, 0xc0000f35c8, 0x1, 0x1, …)\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3468 +0x17a\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoinInternal(0xc000dddb00, 0x40045a0, 0xc00af0cbc0, 0x403f1c0, 0xc004c71320, 0xc00081c500, 0x2c, 0x4c, 0xc0000f35c8, 0x1, …)\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3209 +0xadf\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoin(0xc000dddb00, 0x40045a0, 0xc00af0cbc0, 0xc00081c500, 0x2c, 0x4c, 0xc0000f35c8, 0x1, 0x1, 0xc03c180520, …)\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3200 +0xf9\ngithub.com/pingcap/tidb/executor.(*innerWorker).fetchInnerResults(0xc00e880640, 0x40045a0, 0xc00af0cbc0, 0xc0361bce80, 0xc00081c500, 0x2c, 0x4c, 0x0, 0x0)\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:647 +0xf9\ngithub.com/pingcap/tidb/executor.(*innerWorker).handleTask(0xc00e880640, 0x40045a0, 0xc00af0cbc0, 0xc0361bce80, 0x0, 0x0)\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:491 +0xcf\ngithub.com/pingcap/tidb/executor.(*innerWorker).run(0xc00e880640, 0x40045a0, 0xc00af0cbc0, 0xc03c1807b0)\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:469 +0x155\ created by github.com/pingcap/tidb/executor.(*IndexLookUpJoin).startWorkers\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:182 +0x1fe\ ”]

db_user · 2021 年5 月 19 日 10:55

[ERROR] [misc.go:90] [“panic in the recoverable goroutine”] [r={}] [“stack trace”=“github.com/pingcap/tidb/util.WithRecovery.func1\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/util/misc.go:92\ runtime.gopanic\ \t/usr/local/go/src/runtime/panic.go:679\ runtime.goPanicIndex\ \t/usr/local/go/src/runtime/panic.go:75\ github.com/pingcap/tidb/executor.buildKvRangesForIndexJoin\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3639\ github.com/pingcap/tidb/executor.(*dataReaderBuilder).buildIndexReaderForIndexJoin\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3468\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoinInternal\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3209\ngithub.com/pingcap/tidb/executor.(*dataReaderBuilder).buildExecutorForIndexJoin\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/builder.go:3200\ngithub.com/pingcap/tidb/executor.(*innerWorker).fetchInnerResults\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_join.go:647\ngithub.com/pingcap/tidb/executor.(*indexHashJoinInnerWorker).fetchInnerResults\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_hash_join.go:575\ngithub.com/pingcap/tidb/executor.(*indexHashJoinInnerWorker).handleTask\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_hash_join.go:600\ngithub.com/pingcap/tidb/executor.(*indexHashJoinInnerWorker).run\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_hash_join.go:483\ngithub.com/pingcap/tidb/executor.(*IndexNestedLoopHashJoin).startWorkers.func2\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/executor/index_lookup_hash_join.go:196\ngithub.com/pingcap/tidb/util.WithRecovery\ \t/home/jenkins/agent/workspace/optimization-build-tidb-linux-amd/go/src/github.com/pingcap/tidb/util/misc.go:95”]

也有这个错误，感觉要被搞死

spc_monkey · 2021 年5 月 19 日 11:00

再确认2个信息哈，由于这个咱们牵扯数据量多少，我们这边可能会想复现一下原因，所以想先确认2个信息：
1、咱们这个影响范围是啥，着急吗？（担心这边准备环境，可能需要点时间）
2、看上面提到了 left join 等几个情况会导致报错，而有的没有，想咱们这边能不能先提供一下这些情况下的执行计划

spc_monkey · 2021 年5 月 19 日 11:21

我这边没模拟出来，最好还是需要咱们提供一下执行计划